Alteryx Designer

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

Problem with datetime conversion from pandas to Alteryx

Highlighted
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 = Alteryx.read("#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)

 

Alteryx.write(pivoted_df,1)

 

I would be very grateful for any sort of input.

 

All the best,

Carolin

Highlighted
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.

Highlighted
5 - Atom

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

Highlighted
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.

Labels