+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Error when Excelwriter saving a dataframe with datetime datatype with timezone (/thread-31300.html)
Error when Excelwriter saving a dataframe with datetime datatype with timezone - klllmmm - Dec-03-2020
I'm trying to save pandas data-frame with timezone data into an excel workbook. However i get this error.
import pandas as pd
from pandas import ExcelWriter
data = pd.DataFrame(data={"ID":[1,2],
'LocalDateTime':['2020-06-19 12:38:48+05:30','2020-06-30 12:38:48+05:30']})
data['LocalDateTime'] = pd.to_datetime(data['LocalDateTime'])
# Save into excelworkbook
writer = pd.ExcelWriter(r"C:\Users\Desktop\testData.xlsx",
engine='xlsxwriter', options = {'remove_timezone': True})
data.to_excel(writer, sheet_name='Sheet1')
writer.save()Error:Traceback (most recent call last):
File "<ipython-input-30-513fce18c9d9>", line 5, in <module>
data.to_excel(writer, sheet_name='Sheet1')
File "C:\Users\Desktop\AppData\Roaming\Python\Python36\site-packages\pandas\core\generic.py", line 2181, in to_excel
engine=engine,
File "C:\Users\Desktop\AppData\Roaming\Python\Python36\site-packages\pandas\io\formats\excel.py", line 735, in write
freeze_panes=freeze_panes,
File "C:\Users\Desktop\AppData\Roaming\Python\Python36\site-packages\pandas\io\excel\_xlsxwriter.py", line 214, in write_cells
for cell in cells:
File "C:\Users\Desktop\AppData\Roaming\Python\Python36\site-packages\pandas\io\formats\excel.py", line 685, in get_formatted_cells
cell.val = self._format_value(cell.val)
File "C:\Users\Desktop\AppData\Roaming\Python\Python36\site-packages\pandas\io\formats\excel.py", line 438, in _format_value
"Excel does not support datetimes with "
ValueError: Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware before writing to Excel.data.info()
Output: # Column Non-Null Count Dtype
--- ------ -------------- -----
0 ID 2 non-null int64
1 LocalDateTime 2 non-null datetime64[ns, pytz.FixedOffset(330)]pd.__version__
1.0.5
Python 3.6.5
RE: Error when Excelwriter saving a dataframe with datetime datatype withtimezone - Larz60+ - Dec-03-2020
Please (always) show complete unaltered error traceback.
It contains very valuable debugging information.
RE: Error when Excelwriter saving a dataframe with datetime datatype with timezone - klllmmm - Dec-08-2020
Managed to solve this using strftime into text format excluding time zone details and then convert back to datetime format.
import pandas as pd
from pandas import ExcelWriter
import pytz
from datetime import datetime
data = pd.DataFrame(data={"ID":[1,2],
'LocalDateTime':['2020-06-19 12:38:48+05:30','2020-06-30 12:38:48+05:30']})
data['LocalDateTime'] = pd.to_datetime(data['LocalDateTime'])
data['LocalDateTime'] = data['LocalDateTime'].apply(lambda a: datetime.strftime(a,"%Y-%m-%d %H:%M:%S"))
data['LocalDateTime'] = pd.to_datetime(data['LocalDateTime'])
writer = pd.ExcelWriter(rC:\Users\Desktop\testData.xlsx",
engine='xlsxwriter'
,datetime_format='dd-mmm-yyyy')
data.to_excel(writer, sheet_name='Sheet1')
writer.save()
data.info()Output: # Column Non-Null Count Dtype
--- ------ -------------- -----
0 ID 2 non-null int64
1 LocalDateTime 2 non-null datetime64[ns]RE: Error when Excelwriter saving a dataframe with datetime datatype with timezone - Larz60+ - Dec-08-2020
Thanks for sharing solution.