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?

Solution 1

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

Solution 2

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')

Solution 3

this work fine

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

Comments

[user40780]

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.

[Adam Bethke]

Seconded! This worked really well in our use case, too. Thanks for documenting how to work around that quirk!

[ak3191]

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

[ZF007]

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]

Once you question is updated accordingly my previous comment I will remove the down-vote and this comment.