Error when Excelwriter saving a dataframe with datetime datatype with timezone

[klllmmm]

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)]

Appreciate it if someone help me to resolve this issue
pd.__version__
1.0.5
Python 3.6.5

[Larz60+]

Please (always) show complete unaltered error traceback.
It contains very valuable debugging information.

[klllmmm]

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]

[Larz60+]

Thanks for sharing solution.