Python Forum
Error when Excelwriter saving a dataframe with datetime datatype with timezone
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Error when Excelwriter saving a dataframe with datetime datatype with timezone
#1
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
Reply


Messages In This Thread
Error when Excelwriter saving a dataframe with datetime datatype with timezone - by klllmmm - Dec-03-2020, 02:19 PM

Possibly Related Threads…
Thread Author Replies Views Last Post
  discrepancy with datetime with timezone XJia 3 786 Sep-03-2023, 02:58 PM
Last Post: deanhystad
  How to change the datatype of list elements? mHosseinDS86 9 2,001 Aug-24-2022, 05:26 PM
Last Post: deanhystad
  Need help formatting dataframe data before saving to CSV cubangt 16 5,875 Jul-01-2022, 12:54 PM
Last Post: cubangt
  2-dataframe, datetime lookup problem Mark17 0 1,240 Jan-27-2022, 01:02 AM
Last Post: Mark17
  datatype check arkiboys 1 1,184 Jan-18-2022, 12:46 PM
Last Post: ndc85430
  Filter dataframe by datetime.date column glidecode 2 5,164 Dec-05-2021, 12:51 AM
Last Post: glidecode
  Problem in saving .xlsm (excel) file using pandas dataframe in python shantanu97 2 4,311 Aug-29-2021, 12:39 PM
Last Post: snippsat
  JSON Decode error when using API to create dataframe Rubstiano7 4 2,950 Jan-11-2021, 07:52 PM
Last Post: buran
  Saving Excel workbook file with dataframe names Biplab1985 0 2,037 Jun-07-2020, 12:25 PM
Last Post: Biplab1985
  I need to get only string datatype and report in excel file. akshay3210 3 2,368 Dec-12-2019, 09:53 AM
Last Post: akshay3210

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020