Python Forum

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

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

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]

Thanks for sharing solution.