I'm trying to save pandas data-frame with timezone data into an excel workbook. However i get this error.
pd.__version__
1.0.5
Python 3.6.5
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)]
Appreciate it if someone help me to resolve this issuepd.__version__
1.0.5
Python 3.6.5