Change timezone of date-time column in pandas and add as hierarchical index

If you set it as the index, it’s automatically converted to an Index:

In [11]: dat.index = pd.to_datetime(dat.pop('datetime'), utc=True)

In [12]: dat
Out[12]:
                    label  value
datetime
2011-07-19 07:00:00     a      0
2011-07-19 08:00:00     a      1
2011-07-19 09:00:00     a      2
2011-07-19 07:00:00     b      3
2011-07-19 08:00:00     b      4
2011-07-19 09:00:00     b      5

Then do the tz_localize:

In [12]: dat.index = dat.index.tz_localize('UTC').tz_convert('US/Pacific')

In [13]: dat
Out[13]:
                          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

And then you can append the label column to the index:

Hmmm this is definitely a bug!

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

A hacky workaround is to convert the (datetime) level directly (when it’s already a MultiIndex):

In [15]: dat.index.levels[1] = dat.index.get_level_values(1).tz_localize('UTC').tz_convert('US/Pacific')

In [16]: dat1
Out[16]:
                                 value
label datetime
a     2011-07-19 00:00:00-07:00      0
      2011-07-19 01:00:00-07:00      1
      2011-07-19 02:00:00-07:00      2
b     2011-07-19 00:00:00-07:00      3
      2011-07-19 01:00:00-07:00      4
      2011-07-19 02:00:00-07:00      5