Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.

Problem with datetime conversion from pandas to Alteryx

5 - Atom

Hi all,


I have only recently started using Alteryx and really enjoy how much it has to offer. I'm currently working with an interactive Jupyter notebook and my code runs beautifully smoothly until I try to write back to Alteryx. The trouble seems to be my date column but since it leaves out my index (which is the date), I needed to save a copy of it to bring it back to Alteryx.


Here's the error I get ('ds' is the date column):


[CachedData.write] couldn't find conversion for ds ("datetime64[ns]") from pandas to yxdb

followed by some Python error, but this seems to be a conversion rather than python problem.



For full disclosure, here's my code ('first_listing_Date' is the datetime column, which later becomes the index):


from ayx import Alteryx
import pandas as pd
df_grandavg ="#1")

# set datetime for time series
df_grandavg['first_listing_Date'] = pd.to_datetime(df_grandavg['first_listing_Date'])
gr = df_grandavg['Office_name'].unique()

# create rolling means
df_grandavg = df_grandavg.set_index('first_listing_Date')
pivoted_df = pd.pivot_table(df_grandavg, index='first_listing_Date', columns='Office_name', values='Count', aggfunc='first')


for g in gr:
pivoted_df[[g+'_7day']] = pivoted_df[[g]].rolling(7,center=True).mean()
pivoted_df[[g+'_14day']] = pivoted_df[[g]].rolling(14,center=True).mean()

pivoted_df['ds'] = pd.to_datetime(pivoted_df.index)




I would be very grateful for any sort of input.


All the best,


Alteryx Certified Partner
Alteryx Certified Partner

Edit: scratch that... could you share a few records inputted to the Python tool?




I couldn't really replicate your error, but you could try replacing your last line with,



Alteryx.write(pivoted_df.reset_index(), 1)



This just resets the index to numbers, and by default adds the current index as a column. Alteryx doesn't do indexes like Python Dataframes anyway.

5 - Atom

Strange that you weren't able to replicate the error, but thanks so much for that little change, it's worked!

Alteryx Certified Partner
Alteryx Certified Partner

That worked? Brilliant! I was actually close to deleting the reply all together I was so sure it wasn't correct.


When I have a date as an index and write an output the index just resets automatically. Maybe that's an update in Alteryx 2020.1... Anyway, glad it worked.