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
#2
Please (always) show complete unaltered error traceback.
It contains very valuable debugging information.
klllmmm likes this post
Reply
#3
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]
Reply
#4
Thanks for sharing solution.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Problem in saving .xlsm (excel) file using pandas dataframe in python shantanu97 2 249 Aug-29-2021, 12:39 PM
Last Post: snippsat
  JSON Decode error when using API to create dataframe Rubstiano7 4 758 Jan-11-2021, 07:52 PM
Last Post: buran
  Saving Excel workbook file with dataframe names Biplab1985 0 772 Jun-07-2020, 12:25 PM
Last Post: Biplab1985
  I need to get only string datatype and report in excel file. akshay3210 3 1,002 Dec-12-2019, 09:53 AM
Last Post: akshay3210
  Unable to read decimal datatype using pandasql geethchi 0 868 Oct-25-2019, 05:56 PM
Last Post: geethchi
  TypeError: unsupported operand type(s) for -: 'datetime.datetime' and 'str' findbikash 2 3,566 Sep-18-2019, 08:32 AM
Last Post: buran
  Add two number and variable datatype is int8 ift38375 3 1,491 Aug-05-2019, 05:41 PM
Last Post: DeaD_EyE
  Openpyxl - while saving excel file getting error shubhamjainj 1 2,409 Apr-09-2019, 12:05 PM
Last Post: Larz60+
  datetime replace error kbrummert 2 3,805 Jan-21-2019, 01:07 PM
Last Post: kbrummert
  Value Error and Saving Data to .csv caroline_d_124 1 1,022 Jan-06-2019, 10:53 PM
Last Post: stullis

Forum Jump:

User Panel Messages

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