python - KeyError: 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

标签： pythonexcelpython-3.xpandasdataframe

Answer 1

我在我的项目中遇到了同样的问题。我不明白为什么会发生此错误，但我找到了解决方案。

我相信这个错误与模块有关openpyxl。Pandas 使用它作为引擎将数据导出为 excel 文件。当您使用.xlsx扩展名命名文件时，该类会自动ExcelWritter用作openpyxl默认引擎。

我所做的是改变这个引擎。您可以将参数传递给ExcelWritter调用新引擎 ( xlsxwriter) 以导出数据帧。

所以我的代码从：

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

对此：

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

如果您的环境中没有安装该模块xlsxwriter，只需使用pip install xlsxwriter并执行您的代码。

那也应该解决你的问题。