tz info lost when creating multiindex

[hayd]

see http://stackoverflow.com/questions/17159207/change-timezone-of-date-time-column-in-pandas-and-add-as-hierarchical-index/17159276#17159276

dat = pd.DataFrame({'label':['a', 'a', 'a', 'b', 'b', 'b'], 'datetime':['2011-07-19 07:00:00', '2011-07-19 08:00:00', '2011-07-19 09:00:00', '2011-07-19 07:00:00', '2011-07-19 08:00:00', '2011-07-19 09:00:00'], 'value':range(6)})
dat.index = pd.to_datetime(dat.pop('datetime'), utc=True)
dat.index = dat.index.tz_localize('UTC').tz_convert('US/Pacific')

dat
                          label  value
datetime
2011-07-19 00:00:00-07:00     a      0
2011-07-19 01:00:00-07:00     a      1
2011-07-19 02:00:00-07:00     a      2
2011-07-19 00:00:00-07:00     b      3
2011-07-19 01:00:00-07:00     b      4
2011-07-19 02:00:00-07:00     b      5

If we add another index (we lose the tz):

In [14]: dat.set_index('label', append=True).swaplevel(0, 1)
Out[14]:
                           value
label datetime
a     2011-07-19 07:00:00      0
      2011-07-19 08:00:00      1
      2011-07-19 09:00:00      2
b     2011-07-19 07:00:00      3
      2011-07-19 08:00:00      4
      2011-07-19 09:00:00      5

[cpcloud]

that is a gnarly one-liner there. i get an exception on the index assignment

[hayd]

Ah, you're right. Tried to be too clever there. (fixed)

[ciamac]

This problem also occurs with groupby operations that create multiple indexes with time zones. For example:

dat = pd.DataFrame({'label':['a', 'a', 'a', 'b', 'b', 'b'], 'datetime':['2011-07-19 07:00:00', '2011-07-19 08:00:00', '2011-07-19 09:00:00', '2011-07-19 07:00:00', '2011-07-19 08:00:00', '2011-07-19 09:00:00'], 'value':range(6)})
dat['datetime'] = dat['datetime'].apply(lambda d: pandas.Timestamp(d, tz='US/Pacific'))

We start out with timezones:

In [386]: dat.head()
Out[386]: 
                    datetime label  value
0  2011-07-19 07:00:00-07:00     a      0
1  2011-07-19 08:00:00-07:00     a      1
2  2011-07-19 09:00:00-07:00     a      2
3  2011-07-19 07:00:00-07:00     b      3
4  2011-07-19 08:00:00-07:00     b      4

However, if we do a groupby on multiple columns, we will lose the timezones:

In [387]: dat.groupby(['datetime', 'label'])['value'].sum()
Out[387]: 
datetime             label
2011-07-19 14:00:00  a        0
                     b        3
2011-07-19 15:00:00  a        1
                     b        4
2011-07-19 16:00:00  a        2
                     b        5

[andrewchou34]

We just upgraded to pandas 0.14, and the groupby/pivot bugs related to losing tz-info have been fixed. However, reset_index of a MultiIndex still loses tz-info.

In [7]: ts = pd.date_range('1/1/2011', periods=5, freq='10s', tz = 'US/Eastern')

In [8]: foo = pd.DataFrame({'c' : range(5)}, index = pd.MultiIndex([range(5), ts], [range(5), range(5)], names = [ 'a' , 'b' ]))

In [9]: print foo                                                                                                
                             c
a b                           
0 2011-01-01 00:00:00-05:00  0
1 2011-01-01 00:00:10-05:00  1
2 2011-01-01 00:00:20-05:00  2
3 2011-01-01 00:00:30-05:00  3
4 2011-01-01 00:00:40-05:00  4

In [10]: foo2 = foo.reset_index()                                                                                                

In [11]: print foo2                                                                                                              
   a                   b  c
0  0 2011-01-01 05:00:00  0
1  1 2011-01-01 05:00:10  1
2  2 2011-01-01 05:00:20  2
3  3 2011-01-01 05:00:30  3
4  4 2011-01-01 05:00:40  4

And, column assignment still loses tz-info.

In [13]: bar = pd.DataFrame( {'a' : ts, 'b':range(5)} )

In [14]: print bar
                    a  b
0 2011-01-01 05:00:00  0
1 2011-01-01 05:00:10  1
2 2011-01-01 05:00:20  2
3 2011-01-01 05:00:30  3
4 2011-01-01 05:00:40  4