Python tool: datetime written to workflow as date

Hi all,

I'm using a Python tool in my workflow to do some cleaning and calculations on time series data.

I use pandas dataframes to work with the data and then write the dataframe back to the workflow/

However, the timestamp field (yyyy-mm-dd hh:mm:ss, datetime64[ns]) is truncated to only the date when writing back to the workflow. The datetime field is fine just before, so it looks like the Alteryx.write function truncates the datetime to date.

Any ideas how to solve this?

Thanks for your consideration.

example.png

Workflow

Python

Time Series

Accepted answers

ToPlo

Thanks for your offer to help Sntrada!

I have been dabbling with the workflow... Apparently, the datetime gets truncated when the time of the first record in the table is 00:00:00. I guess this is the default time given to a datestamp, so the column is interpreted as a date.

I tested a few different starting datetimes:

2020-09-13 00:00:00 only date column is retuned

2020-09-13 00:00:01 no problem

2020-09-13 00:10:00 no problem

2020-09-13 12:00:00 no problem

In the first case, the data type indicated by the 'Browse' tool is 'date', while in rest of the cases it is 'datetime'

So it seems like a bug with the interpretation of the datetime column by the Alteryx.write function when the first record is at 00:00:00.

I will circumvent this by making sure that the query does not start at this time. Perhaps this can be raised as a bug with Alteryx.

All comments

Sntrada

I can try to help, but I will take trial and error, so I will need your workflow. You can provide a skeleton version with at least one row of data and the part of the code relating to the date.

ToPlo

Thanks for your offer to help Sntrada!

I tested a few different starting datetimes:

2020-09-13 00:00:00 only date column is retuned

2020-09-13 00:00:01 no problem

2020-09-13 00:10:00 no problem

2020-09-13 12:00:00 no problem

In the first case, the data type indicated by the 'Browse' tool is 'date', while in rest of the cases it is 'datetime'

So it seems like a bug with the interpretation of the datetime column by the Alteryx.write function when the first record is at 00:00:00.

I will circumvent this by making sure that the query does not start at this time. Perhaps this can be raised as a bug with Alteryx.

Ross_K

Hey,

Encountered the same problem and took longer than it should have to track it down 😋

With the help of the docs here, I added a little extra code to my Python script right before Alteryx.write to explicitly set the datatypes.

This was complicated a little as Alteryx doesn't understand the Pandas datatypes, so had to make a little lookup table too.

All in all it seemed to resolve the issue.

I hope it helps and let me know if you make any improvements!

# Start the dictionary
output_metadata = {}
# Now loop through the columns looking up matching types
# Each item in the match list has the Pandas dtype as the key and Alteryx dtype as the value
dtype_match_list = {'object':'v_wstring',
                    'int64':'int64',
                    'float64':'float',
                    'bool':'bool',
                    'datetime64':'datetime'}
for column in df.columns:
    # Get the datatype of the column
    pandas_dtype = str(df[column].dtype)
    # Some fields have additional info, e.g. datetime64[ns, UTC], so strip that off
    pandas_dtype = pandas_dtype.split("[")[0]
    output_metadata[column] = {'type':dtype_match_list[pandas_dtype]}
# Write the df to the output
Alteryx.write(pandas_df=df, columns=output_metadata, outgoing_connection_number=1)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703