Stacking MultiIndex DataFrame and merging indices

Question

My DataFrame looks like this:

                       00:00  01:00  02:00  03:00  04:00  05:00  06:00  07:00
Code Alias Date                                                                 
RO   FI    05.07.2010  53.97  52.11  52.11  52.11  52.11  52.11  51.85  51.55   
     JY    05.07.2010  53.97  52.11  52.11  52.11  52.11  52.11  51.85  65.85   
     SE    05.07.2010  53.97  52.11  52.11  52.11  52.11  52.11  51.85  51.55   
     SJ    05.07.2010  53.97  52.11  52.11  52.11  51.49  52.11  51.85  51.55   

When using df.stack(), i get the columns (hours) as a seperate index. How do I merge the 'Date' index and the new 'hours' index to form a DateTime index?

Like this:

                             Value
Code Alias Date                                                                 
RO   FI    05.07.2010 00:00  53.97  
           05.07.2010 01:00  52.11
           05.07.2010 02:00  52.11

etc

Answer 1

It's not pretty, but it'll do

• use strftime to turn Date into a string and concatenate with Hour
• then pass to pd.to_datetime

---

s = df.rename_axis('Hour', 1).stack()

d1 = s.reset_index(['Date', 'Hour'], name='Value')

d1.set_index(
    pd.to_datetime(
        d1.Date.dt.strftime('%Y-%m-%d ') +
        d1.Hour
    ), append=True
).Value

Code  Alias                     
RO    FI     2010-05-07 00:00:00    53.97
             2010-05-07 01:00:00    52.11
             2010-05-07 02:00:00    52.11
             2010-05-07 03:00:00    52.11
             2010-05-07 04:00:00    52.11
             2010-05-07 05:00:00    52.11
             2010-05-07 06:00:00    51.85
             2010-05-07 07:00:00    51.55
      JY     2010-05-07 00:00:00    53.97
             2010-05-07 01:00:00    52.11
             2010-05-07 02:00:00    52.11
             2010-05-07 03:00:00    52.11
             2010-05-07 04:00:00    52.11
             2010-05-07 05:00:00    52.11
             2010-05-07 06:00:00    51.85
             2010-05-07 07:00:00    65.85
      SE     2010-05-07 00:00:00    53.97
             2010-05-07 01:00:00    52.11
             2010-05-07 02:00:00    52.11
             2010-05-07 03:00:00    52.11
             2010-05-07 04:00:00    52.11
             2010-05-07 05:00:00    52.11
             2010-05-07 06:00:00    51.85
             2010-05-07 07:00:00    51.55
      SJ     2010-05-07 00:00:00    53.97
             2010-05-07 01:00:00    52.11
             2010-05-07 02:00:00    52.11
             2010-05-07 03:00:00    52.11
             2010-05-07 04:00:00    51.49
             2010-05-07 05:00:00    52.11
             2010-05-07 06:00:00    51.85
             2010-05-07 07:00:00    51.55
Name: Value, dtype: object