Pandas: Handling offset timezone value when using 'DatetimeIndex'

Question

I have a datetime column EST

EST
2017-01-01 19:00:00-05:00
2017-01-01 19:01:00-05:00
2017-01-01 19:02:00-05:00
2017-01-01 19:03:00-05:00
2017-01-01 19:05:00-05:00
2017-01-01 19:06:00-05:00
....
2017-12-31 23:01:00-05:00

Here you can see that there is trailing -05:00 on every time. This trailing -05:00 was added when I converted from GMT to EST.

How do I remove -05:00?

The reason is because when I do:

df['EST'] = pd.DatetimeIndex(df['EST']')

I get this error:

TypeError: [datetime.datetime(2017, 12, 31, 19, 0, tzinfo=tzoffset(None, -18000))

Could you please help me solve this?

Erfan · Accepted Answer · 2019-07-04 20:06:44Z

2

We can remove the -05:00 by using Series.dt.tz_localize and set it to None. Then we can use pd.to_datetime:

df['EST'] = pd.to_datetime(df['EST']).dt.tz_localize(None)

                    EST
0   2017-01-01 19:00:00
1   2017-01-01 19:01:00
2   2017-01-01 19:02:00
3   2017-01-01 19:03:00
4   2017-01-01 19:05:00
5   2017-01-01 19:06:00
6   2017-12-31 23:01:00

If EST is your index, use:

df.index = pd.DatetimeIndex(df['EST']).dt.tz_localize(None)

edited Jul 4, 2019 at 20:06

answered Jul 4, 2019 at 19:21

Erfan

43.4k10 gold badges75 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

floss Over a year ago

This works but is this optimal solution in such cases?

floss Over a year ago

what is the difference between pd.DatetimeIndex(df['EST']') and pd.to_datetime(df['EST'])? since I have been using pd.DatetimeIndex

Erfan Over a year ago

I assume that 'EST' is a column in this case and not your index. If it is your index, you are correct we have to use pd.DatetimeIndex, see my edit. @floss

floss Over a year ago

I used pd.to_datetime(df['EST']).dt.tz_localize(None) and I got this error: ValueError: Array must be all same time zone ( and ) ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True

Erfan Over a year ago

I can confirm that for the example dataset your provided. If that generalizes to your whole dataset, then it works correctly. @floss

|

Andy L. · Accepted Answer · 2019-07-04 19:26:41Z

1

I think you may use .dt.tz_localize with option None assume your EST column is already datetime dtype

Sample data:

df['EST']:
0   2017-01-01 19:00:00-05:00
1   2017-01-01 19:01:00-05:00
2   2017-01-01 19:02:00-05:00
3   2017-01-01 19:03:00-05:00
4   2017-01-01 19:05:00-05:00
5   2017-01-01 19:06:00-05:00
Name: EST, dtype: datetime64[ns, US/Eastern]

Output with .dt.tz_localize

df['EST'].dt.tz_localize(None)

Out[2386]:
0   2017-01-01 19:00:00
1   2017-01-01 19:01:00
2   2017-01-01 19:02:00
3   2017-01-01 19:03:00
4   2017-01-01 19:05:00
5   2017-01-01 19:06:00
Name: EST, dtype: datetime64[ns]

answered Jul 4, 2019 at 19:26

Andy L.

25.3k4 gold badges20 silver badges30 bronze badges

3 Comments

floss Over a year ago

When I do df['EST'].dt.tz_localize(None), I get this error AttributeError: Can only use .dt accessor with datetimelike values

Andy L. Over a year ago

@floss: it means your df['EST'] is not datetime dtype. You need convert it to datetime using pd.to_datetime or construct direct datetimeindex using pd.DatetimeIndex and specify tz info. However, the final output will reflect the -05:00 into the time. It means it will be 00:00:xx and date increases 1 day

floss Over a year ago

my df['EST'] is in the proper format but when I converted from GMT to EST, I got the trailing offset value -05:00.

Collectives™ on Stack Overflow

Pandas: Handling offset timezone value when using 'DatetimeIndex'

2 Answers 2

10 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related