KeyError: <class 'pandas._libs.tslibs.timestamps.Timestamp'> when saving dataframe to excel

Question

Good Morning, I have been using python for about a year and a half and I find myself in front of a basic issue that I can't get to solve.

I have a simple dataframe (df), not big (about 12k lines and 10 columns) that includes one column that is "datetime64[ns]" format, one "float64" and all others are "object". I debugged and can say that the error comes from the datetime column.

When I save this df to Excel, I get the following message:

File "test.py", line 16, in test.to_excel(writer,'test') File "C:\Users\renaud.viot\AppData\Local\Programs\Python\Python36\lib\site-packages\pandas\core\frame.py", line 1766, in to_excel engine=engine) File "C:\Users\renaud.viot\AppData\Local\Programs\Python\Python36\lib\site-packages\pandas\io\formats\excel.py", line 652, in write freeze_panes=freeze_panes) File "C:\Users\renaud.viot\AppData\Local\Programs\Python\Python36\lib\site-packages\pandas\io\excel.py", line 1395, in write_cells xcell.value, fmt = self._value_with_fmt(cell.val) File "C:\Users\renaud.viot\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\cell\cell.py", line 291, in value self._bind_value(value) File "C:\Users\renaud.viot\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\cell\cell.py", line 193, in _bind_value self._set_time_format(value) File "C:\Users\renaud.viot\AppData\Local\Programs\Python\Python36\lib\site-packages\openpyxl\cell\cell.py", line 277, in _set_time_format self.number_format = fmts[type(value)] KeyError:

The piece of code I am using is the following:

import pandas as pd
import datetime
from pandas import ExcelWriter

test = pd.read_excel("test_in.xlsx")
test["CaseDate"] = pd.to_datetime(test["CaseDate"])
writer = ExcelWriter("test_out.xlsx")
test.to_excel(writer,'test')
writer.save()

Please see below the sample of the data:

>    A   CaseDate 
> 0  A 2018-08-30 
> 1  A 2018-08-30 
> 2  A 2018-08-30 
> 3  A 2018-08-30 
> 4  A 2018-08-30 
> 5  A 2018-08-30 
> 6  A 2018-08-30 
> 7  A 2018-08-30 
> 8  A 2018-08-30 
> 9  A 2018-08-30

There must be something obvious... Thank you for your help. BR, Renaud

Answer 1

I was having the same problem in my project. I couldn't understand why this error is happening but I found a solution.

I believe this error is related to the module openpyxl. It is used by Pandas as an engine to export the data as an excel file. As you are naming the file with a .xlsx extension, automatically the class ExcelWritter uses openpyxl as the default engine.

What I've done is to change this engine. You can pass a parameter to ExcelWritter calling a new engine (xlsxwriter) to export the dataframe.

So my code changed from:

writer = pd.ExcelWriter('output.xlsx')
df.to_excel(writer, 'data')
writer.save()

to this one:

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='data')
writer.save()

If you don't have the module xlsxwriter installed in your environment, just use pip install xlsxwriter and after that execute your code.

That should solve your problem too.

Answer 2

I had the same problem with pandas 0.23.4 and openpyxl 2.5.6. I updated (using conda update openpyxl) to 2.5.8 and the problem went away.