Problem with datetime conversion from pandas to Alteryx

[sieversc]

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

[PhilipMannering · Accepted solution]

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.

[sieversc]

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

[PhilipMannering]

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.

[GusGarofalo]

I had the same error, but in my case the proposed solution here did not work. Maybe because my Date column is not the index. If there are any news on this please I would love to know.

[GusGarofalo]

Btw...problem solved by casting different data type to the date column

df['columName'] = df['columName'] .astype(str)