Error using pandas.to_datetime()

This code worked yesterday, so it appears something changed overnight. Does anybody have an idea what is going on here?
I have a simple table

Friday Monday Count
12/3/2021 12/6/2021 1
12/10/2021 12/13/2021 1
12/17/2021 12/20/2021 5
12/24/2021 12/27/2021 0
and reading the data into a DataFrame to do a calculation I need to first convert the two date columns to datetime. This fails this morning. Below is the code:
df = to_df(REF(“Table4[#All]”))
df.Friday = pd.to_datetime(df.Friday, format=“%Y-%m-%d”)
df.Monday = pd.to_datetime(df.Monday, format=“%Y-%m-%d”)
mean2022 = df[df.Monday.dt.year == 2022].Count.mean()

The following error message is spilled in the log:

Traceback (most recent call last):
File “‘Completed’!M6”, line 2, in
df.Friday = pd.to_datetime(df.Friday, format=“%Y-%m-%d”)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/lib/python3.11/site-packages/pandas/core/tools/datetimes.py”, line 1068, in to_datetime
values = convert_listlike(arg._values, format)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File “/lib/python3.11/site-packages/pandas/core/tools/datetimes.py”, line 438, in _convert_listlike_datetimes
result, tz_parsed = objects_to_datetime64ns(
^^^^^^^^^^^^^^^^^^^^^^^^
File “/lib/python3.11/site-packages/pandas/core/arrays/datetimes.py”, line 2177, in objects_to_datetime64ns
result, tz_parsed = tslib.array_to_datetime(
^^^^^^^^^^^^^^^^^^^^^^^^
File “pandas/_libs/tslib.pyx”, line 427, in pandas._libs.tslib.array_to_datetime
File “pandas/_libs/tslib.pyx”, line 683, in pandas._libs.tslib.array_to_datetime
File “pandas/_libs/tslib.pyx”, line 833, in pandas._libs.tslib._array_to_datetime_object
File “pandas/_libs/tslib.pyx”, line 655, in pandas._libs.tslib.array_to_datetime
TypeError: <class ‘pyodide.ffi.JsProxy’> is not convertible to datetime

Hi hennie and thanks for reporting this. That looks like a problem with the pyodide environment (that’s how we run Python in the add-in). I took your data and code just now and ran it in Anaconda Code without issue. However, we did make a release yesterday so that may explain what’s happening. I know it’s not ideal, but could I ask that you uninstall and reinstall the add-in and re-test to see if the problem persists?

Let me know how you get on.

Owen

OK, I uninstalled the add-in, closed and re-opened Excel and re-loaded. Unfortunately I get the same error.

I then repeated with the additional step of rebooting my laptop, but still no luck. Is there a cache somewhere that should also be cleaned?

Hennie

I just copied the table as values to a fresh Excel document.
When I do a simple:
df = to_df(REF(“Table4[#All]”))
and set the Output to Excel Values, I do see the table in the output, however when I set Output to Python Object I get a #VALUE! error in the Excel sheet with no error shown in the log.

If I read with Output to Excel values and then look at the type of an element in one of the date columns,
df = to_df(REF(“Table4[#All]”))
v = df.Friday[0]
print(type(v))
this is <class ‘pyodide.ffi.JsProxy’>. This does not seem right?

I see the data values in the above class is represented as:
“Thu Dec 02 2021 18:00:00 GMT-0600 (Central Standard Time)”
This appears the same/similar to what the .toString() method in javascript would produce from a JavaScript date value, if I’m Googling this correctly.

@hennie Thank you for finding this. It is definitely a bug in the new release and the updated datetime handling. We will work to get a fix out soon.

Just so you know, you shouldn’t need to call to_datetime anymore. Excel dates are automatically converted into Python datetime object, which pandas knows to convert to a timestamp column.

Thanks Jim. It now works fine.