How to remove timezone from a Timestamp column in pandas

ghz 1years ago ⋅ 8069 views

Question

I read [Pandas change timezone for forex DataFrame](https://stackoverflow.com/questions/40533647/pandas-change- timezone-for-forex-dataframe) but I'd like to make the time column of my dataframe timezone naive for interoperability with an sqlite3 database.

The data in my pandas dataframe is already converted to UTC data, but I do not want to have to maintain this UTC timezone information in the database.

Given a sample of the data derived from other sources, it looks like this:

print(type(testdata))
print(testdata)
print(testdata.applymap(type))

gives:

<class 'pandas.core.frame.DataFrame'>
                        time  navd88_ft  station_id  new
0  2018-03-07 01:31:02+00:00  -0.030332          13    5
1  2018-03-07 01:21:02+00:00  -0.121653          13    5
2  2018-03-07 01:26:02+00:00  -0.072945          13    5
3  2018-03-07 01:16:02+00:00  -0.139917          13    5
4  2018-03-07 01:11:02+00:00  -0.152085          13    5
                                     time        navd88_ft     station_id  \
0  <class 'pandas._libs.tslib.Timestamp'>  <class 'float'>  <class 'int'>   
1  <class 'pandas._libs.tslib.Timestamp'>  <class 'float'>  <class 'int'>   
2  <class 'pandas._libs.tslib.Timestamp'>  <class 'float'>  <class 'int'>   
3  <class 'pandas._libs.tslib.Timestamp'>  <class 'float'>  <class 'int'>   
4  <class 'pandas._libs.tslib.Timestamp'>  <class 'float'>  <class 'int'>   

             new  
0  <class 'int'>  
1  <class 'int'>  
2  <class 'int'>  
3  <class 'int'>  
4  <class 'int'>  

but

newstamp = testdata['time'].tz_convert(None)

gives an eventual error:

TypeError: index is not a valid DatetimeIndex or PeriodIndex

What do I do to replace the column with a timezone naive timestamp?


Answer

The column must be a datetime dtype, for example after using [pd.to_datetime](https://pandas.pydata.org/pandas- docs/stable/reference/api/pandas.to_datetime.html). Then, you can use [tz_localize](https://pandas.pydata.org/pandas- docs/stable/reference/api/pandas.Series.tz_localize.html) to change the time zone, a naive timestamp corresponds to time zone None:

testdata['time'].dt.tz_localize(None)

Unless the column is an index ([DatetimeIndex](https://pandas.pydata.org/pandas- docs/stable/reference/api/pandas.DatetimeIndex.html)), the [.dt accessor](https://pandas.pydata.org/pandas- docs/stable/user_guide/basics.html#dt-accessor) must be used to access [pandas datetime functions](https://pandas.pydata.org/pandas- docs/stable/user_guide/timeseries.html).