ExcelWriter ValueError: Excel does not support datetime with timezone when saving df to Excel

Question

I'm running on this issue for quite a while now.

I set the writer as follows:

writer = pd.ExcelWriter(arquivo+'.xlsx', engine = 'xlsxwriter', options = {'remove_timezone': True})
df.to_excel(writer, header = True, index = True)

This code is inside s function. The problem is every time I run the code, it gets information from the database, which contains two columns datetime64[ns, UTC] object with time zone info. But when the code to save to Excel runs I receive:

ValueError: Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel.

I have already tried several things like 'dt.tz_convert', replace(tzinfo=None) and other solutions I have found here and around.

The code runs without problem in my personal computer, my colleague at work with the same machine specs can run the code. Only in my machine it doesn't. I already reinstalled python and all the packages, including formatting the machine and nothing, the error persists.

xlrd v1.1.0

xlsxwriter v1.0.4

python 3.7.4

pandas v0.25.1

If someone could bring some light into this issue I would much appreciate it.

Thanks

Answer 1

I found this way easier and more dynamic. This solution you select the columns by the type and applied the desire conversion.

date_columns = df.select_dtypes(include=['datetime64[ns, UTC]']).columns
for date_column in date_columns:
    df[date_column] = df[date_column].dt.date
    
df.to_excel('anbima_feed.xlsx',engine='xlsxwriter')

Answer 2

What format is your timestamps in?

I just had a similar problem.

I was trying to save a data frame to Excel. However I was getting:

I checked my date format which was in this format '2019-09-01T00:00:00.000Z'

This is a timestamp pandas._libs.tslibs.timestamps.Timestamp from pandas.to_datetime

which includes a method date() that converted the date into a format "%Y-%m-%d" that was acceptable by excel

So my code was something like:

#Pseudo
df['date'] = old_dates
df['date'] = df['date'].apply(lambda a: pd.to_datetime(a).date()) 
# .date() removes timezone

...df.to_excel etc.

Answer 3

This should do the job, remove timezone from columns before exporting to excel (using tz_localize(None)).

# Check which columns have timezones datetime64[ns, UTC] 
df.dtypes

# Remove timezone from columns
df['date'] = df['date'].dt.tz_localize(None)

# Export to excel
df.to_excel('filename.xlsx')