CODEWITHSUNDEEP
pythonpandasdataframemulti-index
ArOLi
ArOLi

Reputation: 23

Stacking MultiIndex DataFrame and merging indices

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

Upvotes: 2

Views: 63

Answers (1)

piRSquared
piRSquared

Reputation: 294278

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

Upvotes: 1

Related Questions