Unable to apply methods on timestamps using Series built-ins

Question

On the following series:

0    1411161507178
1    1411138436009
2    1411123732180
3    1411167606146
4    1411124780140
5    1411159331327
6    1411131745474
7    1411151831454
8    1411152487758
9    1411137160544
Name: my_series, dtype: int64

This command (convert to timestamp, localize and convert to EST) works:

pd.to_datetime(my_series, unit='ms').apply(lambda x: x.tz_localize('UTC').tz_convert('US/Eastern'))

but this one fails:

pd.to_datetime(my_series, unit='ms').tz_localize('UTC').tz_convert('US/Eastern')

with:

TypeError                                 Traceback (most recent call last)
<ipython-input-3-58187a4b60f8> in <module>()
----> 1 lua = pd.to_datetime(df[column], unit='ms').tz_localize('UTC').tz_convert('US/Eastern')

/Users/josh/anaconda/envs/py34/lib/python3.4/site-packages/pandas/core/generic.py in tz_localize(self, tz, axis, copy, infer_dst)
   3492                 ax_name = self._get_axis_name(axis)
   3493                 raise TypeError('%s is not a valid DatetimeIndex or PeriodIndex' %
-> 3494                                 ax_name)
   3495             else:
   3496                 ax = DatetimeIndex([],tz=tz)

TypeError: index is not a valid DatetimeIndex or PeriodIndex

and so does this one:

my_series.tz_localize('UTC').tz_convert('US/Eastern')

with:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-4-0a7cb1e94e1e> in <module>()
----> 1 lua = df[column].tz_localize('UTC').tz_convert('US/Eastern')

/Users/josh/anaconda/envs/py34/lib/python3.4/site-packages/pandas/core/generic.py in tz_localize(self, tz, axis, copy, infer_dst)
   3492                 ax_name = self._get_axis_name(axis)
   3493                 raise TypeError('%s is not a valid DatetimeIndex or PeriodIndex' %
-> 3494                                 ax_name)
   3495             else:
   3496                 ax = DatetimeIndex([],tz=tz)

TypeError: index is not a valid DatetimeIndex or PeriodIndex

As far as I understand, the second approach above (the first one that fails) should work. Why does it fail?

I'm not sure why your first method works in fact, as for your second the error statement is quite clear, if in fact your index was your int64 values then the second approach works. — EdChum, Sep 28 '14 at 21:33

score 115 · Answer 1 · answered Jul 20 '16 at 19:10

115

As Jeff's answer mentions, tz_localize() and tz_convert() act on the index, not the data. This was a huge surprise to me too.

Since Jeff's answer was written, Pandas 0.15 added a new Series.dt accessor that helps your use case. You can now do this:

pd.to_datetime(my_series, unit='ms').dt.tz_localize('UTC').dt.tz_convert('US/Eastern')

answered Jul 20 '16 at 19:10

John Zwinck

239,568
38
324
436

2

This is Great! I don't want to set TimeStamp as the Index, sometimes, we could have two TimeStamp as it is really frustrating that we have to convert it to index. – user40780 Jun 26 '17 at 01:48
2

Seconded! This worked really well in our use case, too. Thanks for documenting how to work around that quirk! – Adam Bethke Aug 15 '18 at 10:41
how we will compare this with general timestamp value suppose if i am takign current time and will compare it ... I am getting error TypeError: Cannot compare tz-naive and tz-aware datetime-like objects – ak3191 Oct 17 '18 at 16:36

score 32 · Accepted Answer · answered Sep 28 '14 at 22:02

tz_localize/tz_convert act on the INDEX of the object, not on the values. Easiest to simply turn it into an index then localize and convert. If you then want a Series back you can use to_series()

In [47]: pd.DatetimeIndex(pd.to_datetime(s,unit='ms')).tz_localize('UTC').tz_convert('US/Eastern')
Out[47]: 
<class 'pandas.tseries.index.DatetimeIndex'>
[2014-09-19 17:18:27.178000-04:00, ..., 2014-09-19 10:32:40.544000-04:00]
Length: 10, Freq: None, Timezone: US/Eastern

score 3 · Answer 3 · answered Apr 08 '19 at 08:42

3

this work fine

pd.to_datetime(my_series,unit='ms', utc=True).dt.tz_convert('US/Eastern')

answered Apr 08 '19 at 08:42

mocobk

41
1

2

You need to specify your version of panda's used is different from previous answer posted by John Zwink who states this answer earlier. You're answer is a derivative of his. Now as it is written its non-valid and I assume you want to have it "valid". Als explain why you wrote it like you did. This will gives your answer validity. – ZF007 Apr 08 '19 at 08:50
Once you question is updated accordingly my previous comment I will remove the down-vote and this comment. – ZF007 Apr 08 '19 at 08:52

Unable to apply methods on timestamps using Series built-ins

3 Answers3

Linked