TypeError: Timestamp subtraction

Question

I have a script that goes and collects data. I am running into the TypeError: Timestamp subtraction must have the same timezones or no timezones error. I have looked at other postings on this error, but had trouble finding a solution for me.

How can I bypass this error. Once the data is collected, I don't manipulate it and I don't quite understand why I cannot save this dataframe into an excel document. Can anyone offer help?

import pandas as pd
import numpy as np
import os
import datetime
import pvlib
from pvlib.forecast import GFS, NAM

#directories and filepaths
barnwell_dir = r'D:\Saurabh\Production Forecasting\Machine Learning\Sites\Barnwell'
barnwell_training = r'8760_barnwell.xlsx'

#constants
writer = pd.ExcelWriter('test' + '_PythonExport.xlsx', engine='xlsxwriter')    
time_zone = 'Etc/GMT+5'
barnwell_list = [r'8760_barnwell.xlsx', 33.2376, -81.3510] 

def get_gfs_processed_data1():
    start = pd.Timestamp(datetime.date.today(), tz=time_zone) #used for testing last week
    end = start + pd.Timedelta(days=6)
    gfs = GFS(resolution='quarter')
    #get processed data for lat/long point
    forecasted_data = gfs.get_processed_data(barnwell_list[1], barnwell_list[2], start, end)
    forecasted_data.to_excel(writer, sheet_name='Sheet1')


get_gfs_processed_data1()

Answer 1

When I run your sample code I get the following warning from XlsxWriter at the end of the stacktrace:

"Excel doesn't support timezones in datetimes. "
TypeError: Excel doesn't support timezones in datetimes. 
Set the tzinfo in the datetime/time object to None or use the
'remove_timezone' Workbook() option

I think that is reasonably self-explanatory. To strip the timezones from the timestamps pass the remove_timezone option as recommended:

writer = pd.ExcelWriter('test' + '_PythonExport.xlsx',
                        engine='xlsxwriter',
                        options={'remove_timezone': True})

When I make this change the sample runs and produces an xlsx file. Note, the remove_timezone option requires XlsxWriter >= 0.9.5.

Answer 2

You can delete timezone from all your datetime columns like that:

for col in df.select_dtypes(['datetimetz']).columns:
    df[col] = df[col].dt.tz_convert(None)

df.to_excel('test' + '_PythonExport.xlsx')

after that you save excel without any problem

Note:

To select Pandas datetimetz dtypes, use 'datetimetz' (new in 0.20.0) or 'datetime64[ns, tz]'