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

Fraley

I also just started receiving this error for code that was previously working. I've confirmed that the problem started when we upgraded our environments to use pandas-0.25.3 (previously using 0.24.2). If I downgrade pandas (and no other changes), things start working again. I'm now trying to figure out if it's easier to update our code to get the previous behavior back with the new version, or if we'll need to pin the dependency version until we have more time to sort out the changes. – Secluded

I didn't try to downgrade pandas, as I thought it would be the xlsxwriter version. At least that was what I found in some other questions similar to this one. I will try to downgrade and come back here to update. @Secluded – Fraley

@Secluded I downgraded to the same version as you and it still not working. Strangely, in my personal computer has 0.25.3 version and it works. Looking for dependencies for ExcelWriter does not return anything. I'm really lost here. – Fraley

The remove_timezone option should work with xlsxwriter >= 0.9.4. However it look like a change was added to Pandas to handle dates with timezones as an error (Issue #27129). This change was added in Pandas v0.25.0. So the error is raised before it gets to xlsxwriter (and the remove_timezone option). – Infuscate

@Infuscate I have checked this version problem for xlsxwriter, and I did upgrade as I stated in my question. But even when downgrading pandas as jweaver pointed, it did not work. I will keep an way on this Issue on git to check if they have any updates. But sincerely I don't know what I can do. – Fraley

.date arguments – Observation

Answer 1

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.

Leavelle

Answer 2

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')

Otolaryngology

Gives me AttributeError: Can only use .dt accessor with datetimelike values – Theoretician

tz_convert(None) is generally a better option. If the date is stored with no timezone information, then it should be converted first to UTC for portability. tz_convert does that, while tz_localize just drops the timezone information. – Reinareinald

Answer 3

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')

Senega

Answer 4

The Accepted Answer works only if you need the Date without the Time in the corresponding Timezone. If your Time is in UTC as a Epoch you need to convert it to Striftime and then again to Datetime to conserve the Time in the Timezone.

Reference: https://python-forum.io/thread-31300.html

Example: the field ts is a timestamp in UTC as Epoch in millisecond.

df['ts']
OUT:
0      1619801902867
1      1619765681594
2      1619712291984
3      1619680298648
4      1619629032109
5      1619593388626
6      1619531314509
7      1619509338368
8      1619449287828
9      1619433411243
10     1619103667781
11     1619078244871
12     1619021782951
13     1618990214111
14     1618931135540
15     1618903774632

Then you need to convert it into the desired Timezone:

df['ts'] = pd.to_datetime(df['ts'],unit='ms').dt.tz_localize('utc').dt.tz_convert('Europe/Vatican')
df['ts'] = df['ts'].apply(lambda a: datetime.datetime.strftime(a,"%Y-%m-%d %H:%M:%S"))
df['ts'] = pd.to_datetime(df['ts'])

The Result will look like:

df['ts']
OUT:
0     2021-04-30 18:58:22
1     2021-04-30 08:54:41
2     2021-04-29 18:04:51
3     2021-04-29 09:11:38
4     2021-04-28 18:57:12
5     2021-04-28 09:03:08
6     2021-04-27 15:48:34
7     2021-04-27 09:42:18
8     2021-04-26 17:01:27
9     2021-04-26 12:36:51
10    2021-04-22 17:01:07
11    2021-04-22 09:57:24
12    2021-04-21 18:16:22
13    2021-04-21 09:30:14
14    2021-04-20 17:05:35
15    2021-04-20 09:29:34

After this, the xlsxwriter will accept it and write the excel without the error Message.

Jotham

Answer 5

Simply, convert the column to str:

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

Jonis

Answer 6

I combined the code Erick Hernández with NLauriks to identify date-time columns having time zone and remove the time zones prior to writing them to excel:

dt_cols = df.select_dtypes(include=['datetime64[ns, UTC]']).columns
for col in dt_cols:
        df[col] = df[col].dt.tz_localize(None)
df.to_excel(f'{table_name}.xlsx', engine="xlsxwriter", index=False)

Carline

Answer 7

I use the following to replace any columns that might be datetimes with a non-timezone version.

df.assign(
   **{
      col: df[col].dt.tz_localize(None)
      for col in df.columns
      if hasattr(df[col], "dt")
   }
)

This method can be used to write the excel file without altering the original dataframe.

df.assign(
   **{col: df[col].dt.tz_localize(None) for col in df.columns if hasattr(df[col], "dt")}
).to_excel(...)

Davilman

Answer 8

There is also another way to use UTC parameter in Pandas

import pandas as pd
# Adjust time zone from columns
df['date'] = pd.to_datetime( df['date'], errors='coerce',utc=True)
# Export to excel
df.to_excel('filename.xlsx')

Cristacristabel

Answer 9

I encountered the same issue and did some documentation search and found a solution for pandas

The below change (added- options={'remove_timezone': True}) worked for me.

exwriter = pd.ExcelWriter(fullpath, engine='xlsxwriter', options={'remove_timezone': True})

Polka

This is specific to the xlsxwriter engine, and you are now supposed to use engine_kwargs instead of options – Auburn

Answer 10

If you are oke with the values in the sheet being a string, you can use the following code to transform the datetime

date_columns = df.select_dtypes(include=['datetime64[ns, UTC]']).columns
for date_column in date_columns:
    df[date_column] = df[date_column].apply(str)

Desouza

Answer 11

You can clean your df.index rows using:

index_renaming = {}
for idx in df.index:
    for x in idx:
        if getattr(x, "tz", None) is not None:
            index_renaming[x] = x.tz_localize(None)
if index_renaming:
    df.rename(index=index_renaming, inplace=True)

All other answers are about cleaning df.values, which can be done using applymap:

df.applymap(lambda x: x if getattr(x, "tz", None) is None else x.tz_localize(None))

Tysontyumen

Answer 12

I had a similar problem. In my case the dates were the index. In case anyone else encounters that very problem (oftentimes with stock/currency/crypto price data) you can use the following:

df.index = df.index.tz_localize(None)
df.to_excel(path)

Sanitize