Convert pandas multi-index to pandas timestamp

Question

I am trying to convert a frame < with multiple indices to a single pandas datetime index.

The index of my original frame frame, i.e. before multi-indexing and unpacking, it looks like this:

In [1]: df1_season.index Out [1]: <class 'pandas.tseries.index.DatetimeIndex'> [2013-05-01 02:00:00, ..., 2014-07-31 23:00:00] Length: 1472, Freq: None, Timezone: None 

then I apply multi-indexing and deployment so that I can display annual data on top of each other as follows:

 df_sort = df1_season.groupby(lambda x: (x.year, x.month, x.day, x.hour)).agg(lambda s: s[-1]) df_sort.index = pd.MultiIndex.from_tuples(df_sort.index, names=['Y','M','D','H']) unstacked = df_sort.unstack('Y') 

My new data frame for the first two days of May is as follows:

 In [2]: unstacked Out [2]: temp season Y 2013 2014 2013 2014 MDH 5 1 2 24.2 22.3 Summer Summer 8 24.1 22.3 Summer Summer 14 24.3 23.2 Summer Summer 20 24.6 23.2 Summer Summer 2 2 24.2 22.5 Summer Summer 8 24.8 22.2 Summer Summer 14 24.9 22.4 Summer Summer 20 24.9 22.8 Summer Summer 736 rows × 4 columns 

The index for the data frame new , shown above, now looks like this:

 In [2]: unstacked.index.values[0:8] Out [2]: array([(5, 1, 2), (5, 1, 8), (5, 1, 14), (5, 1, 20), (5, 2, 2), (5, 2, 8), (5, 2, 14), (5, 2, 20], dtype=object) 

which does not create a very pleasant plot regarding xticks (major and minor). If I can convert this multi-index back to a single pandas datetime index using only the data of the month, day and hour, then the main / minor ticks will be automatically constructed as I would like (I think). For instance:

current solution:

 xticks = (5, 1, 2), (5, 1, 8) … (5, 2, 20) 

Solution Required:

 xticks(major) = Day, Month (displayed as MAY 01, MAY 02 etc etc) xticks(minor) = Hour (displayed as 02h 08h … 20h) 

Answer 1

Converting data back and forth in pandas becomes very dirty, as you seem to have experienced. My overall recommendation regarding pandas and indexing is to never set the index, but copy it first. Make sure you have a column with an index, since pandas does not allow all operations on the index, and intensive tuning and resetting the index can cause the columns to disappear.

TL; DR; Do not convert the index back. Save a copy.

Answer 2

 import pandas as pd import matplotlib.pyplot as plt from numpy.random import randn ts = pd.Series(randn(1000), index=pd.date_range('1/1/2000', periods=1000)) ts = ts.cumsum() plt.figure() for year in set(ts.index.year): tmp = ts[str(year)].values plt.plot(tmp, label = year) plt.legend() plt.show() 

I think this is a better way to achieve your goal than re-indexing. What do you think?

Answer 3

Answered here: Pandas multi index to datetime .

df1_season.index = df1_season.index.to_frame ()