将数据传输到 excel 时如何处理"old"日期

Question

我有一个数据帧，其中一列包含日期字符串。我首先用将其转换为日期时间

mydf['Desk Date'] = pd.to_datetime(mydf['Desk Date'])`

然后用将数据帧丢弃到excel

Range('A1').value = mydf`

我得到以下错误：

Traceback (most recent call last):
File "C:Program Files (x86)Python271libsite-packagesIPythoncoreinteractiveshell.py", line 3035, in run_code
exec(code_obj, self.user_global_ns, self.user_ns)
File "<ipython-input-111-6c6f5ea1ff17>", line 1, in <module>
Import.ImportFWD(test_path)
File "C:UsersjastrzemDownloadspyWFPImport.py", line 42, in ImportFWD
Range('A1').value = mydf
File "C:Program Files (x86)Python271libsite-packagesxlwingsmain.py", line 818, in value
self.row1, self.col1, row2, col2), data)
File "C:Program Files (x86)Python271libsite-packagesxlwings_xlwindows.py", line 151, in set_value
xl_range.Value = data
File "C:Program Files (x86)Python271libsite-packageswin32comclientdynamic.py", line 560, in __setattr__
self._oleobj_.Invoke(entry.dispid, 0, invoke_type, 0, value)
com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

其中一个日期是Timestamp('1899-01-31 00:00:00')我认为这就是错误的原因。

我试着用np.where将2000年之前的所有值替换为NaN，但没有成功。

f = lambda x: x.year
mydf['Desk Date'] = np.where(pd.DataFrame(mydf['Desk Date']).applymap(f) > 2000, pd.to_datetime(mydf['Desk Date'], format='%D/%M/%Y'),np.nan)

我如何修复上述命令，或者我应该如何处理"不可转移"到excel的日期？

谢谢！

[编辑]：我尝试使用to_excel方法，但也没有成功。我把代码放在我的功能末尾：

writer = pd.ExcelWriter('test7.xlsx', engine='xlsxwriter')
mydf.to_excel(writer, sheet_name = 'Sheet1')
writer.close()

它创建了文件，但它是空的。我得到以下错误：

Traceback (most recent call last):
File "C:Program Files (x86)Python271libsite-packagesIPythoncoreinteractiveshell.py", line 3035, in run_code
exec(code_obj, self.user_global_ns, self.user_ns)
File "<ipython-input-26-6c6f5ea1ff17>", line 1, in <module>
Import.ImportFWD(test_path)
File "C:UsersjastrzemDownloadspyWFPImport.py", line 44, in ImportFWD
writer.close()
File "C:Program Files (x86)Python271libsite-packagespandasioexcel.py", line 623, in close
return self.save()
File "C:Program Files (x86)Python271libsite-packagespandasioexcel.py", line 1298, in save
return self.book.close()
File "C:Program Files (x86)Python271libsite-packagesxlsxwriterworkbook.py", line 295, in close
self._store_workbook()
File "C:Program Files (x86)Python271libsite-packagesxlsxwriterworkbook.py", line 518, in _store_workbook
xml_files = packager._create_package()
File "C:Program Files (x86)Python271libsite-packagesxlsxwriterpackager.py", line 140, in _create_package
self._write_shared_strings_file()
File "C:Program Files (x86)Python271libsite-packagesxlsxwriterpackager.py", line 280, in _write_shared_strings_file
sst._assemble_xml_file()
File "C:Program Files (x86)Python271libsite-packagesxlsxwritersharedstrings.py", line 53, in _assemble_xml_file
self._write_sst_strings()
File "C:Program Files (x86)Python271libsite-packagesxlsxwritersharedstrings.py", line 83, in _write_sst_strings
self._write_si(string)
File "C:Program Files (x86)Python271libsite-packagesxlsxwritersharedstrings.py", line 110, in _write_si
self._xml_si_element(string, attributes)
File "C:Program Files (x86)Python271libsite-packagesxlsxwriterxmlwriter.py", line 122, in _xml_si_element
self.fh.write("""<si><t%s>%s</t></si>""" % (attr, string))
File "C:Program Files (x86)Python271libcodecs.py", line 694, in write
return self.writer.write(data)
File "C:Program Files (x86)Python271libcodecs.py", line 357, in write
data, consumed = self.encode(object, self.errors)
UnicodeDecodeError: 'ascii' codec can't decode byte 0x94 in position 26: ordinal not in range(128)

Answer 1

错误不是因为旧日期，而是因为您试图在单个单元格中抛出整个数据帧。

相反，请使用to_excel方法。

Answer 2

Excel不接受1900年之前的日期。我的解决方法是用np.nan替换"旧"日期，因为我知道它们无论如何都是数据错误。

mydf['Desk Date'] = pd.to_datetime(mydf['Desk Date'])
dates_list = list(mydf['Desk Date'])
dates_list = [x if x.year > 1900 else np.nan for x in dates_list ]
mydf['Desk Date'] = dates_list