Pandas Dataframe - multilevel index /column

Question

I am having some difficulty in organizing my dataframe. I think it's pretty simple, but I have been stuck with this for too long:

This is df1:

     Output Energy, (Wh/h)  Lights (Wh)  Lights+Media (Wh)  Total Usage (h)  \
Hour                                                                           
1                       0.0          0.0                0.0              0.0   
2                       0.0          0.0                0.0              0.0   
3                       0.0          0.0                0.0              0.0   
4                       0.0          0.0                0.0              0.0   
5                       0.0          0.0                0.0              0.0

I'd prefer this to be transposed for ease of use:

df2 =df1.T

gives me:

    Hour                     1    2    3    4   
    Output Energy, (Wh/h)  0.0  0.0  0.0  0.0     
    Lights (Wh)            0.0  0.0  0.0  0.0     
    Lights+Media (Wh)      0.0  0.0  0.0  0.0     
    Total Usage (h)        0.0  0.0  0.0  0.0    
    Lights (h)             0.0  0.0  0.0  0.0  
    Light+Media (h)        0.0  0.0  0.0  0.0

But ultimately, I would like it to look like this:

                                       Hour
                                  1    2    3    4   
          Output Energy, (Wh/h)  0.0  0.0  0.0  0.0     
          Lights (Wh)            0.0  0.0  0.0  0.0     
CU-101    Lights+Media (Wh)      0.0  0.0  0.0  0.0     
          Total Usage (h)        0.0  0.0  0.0  0.0    
          Lights (h)             0.0  0.0  0.0  0.0  
          Light+Media (h)        0.0  0.0  0.0  0.0

I have been trying to add the 'Cu-101' as a multilevel column, but to no avail. Should I add this before or after it is transposed?

Also, moving the 'Hour' - I set this column as the index, but how do I move it to a new level?

I am not sure if understand your question - do you need add new string to first level of MultiIndex? Or something missing? Can you explain more if answer is not what you want? — jezrael
– jezrael, Commented Feb 4, 2017 at 21:33

jezrael · Accepted Answer · 2017-02-04 21:18:47Z

5

You can use MultiIndex.from_arrays:

df.index = pd.MultiIndex.from_arrays([['Hour'] * len(df.index),
                                      df.index], 
                                      names=(None,None))
df.columns = pd.MultiIndex.from_arrays([['CU-101'] * len(df.columns),
                                        df.columns], 
                                        names=(None,None))

print (df.T)
                             Hour                    
                                1    2    3    4    5
CU-101 Output Energy, (Wh/h)  0.0  0.0  0.0  0.0  0.0
       Lights (Wh)            0.0  0.0  0.0  0.0  0.0
       Lights+Media (Wh)      0.0  0.0  0.0  0.0  0.0
       Total Usage (h)        0.0  0.0  0.0  0.0  0.0

answered Feb 4, 2017 at 21:18

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

warrenfitzhenry Over a year ago

Thanks @jezrael. When saving this to csv using df.to_csv, is it possible to carry this multiindex through? at the moment the hour is above each and every 1, 2, 3, same with cu-101

jezrael Over a year ago

It is correct, check docs In [17]: df and In [21]: df .It is same DataFrame, there are “sparsified” the higher levels of the indexes to make the console output a bit easier on the eyes.

jezrael Over a year ago

So if after to_csv need read_csv add parameter ìndex_col=[0,1] and it correct read MultiIndex

piRSquared · Accepted Answer · 2017-02-04 23:28:44Z

2

@jezrael's answer is the correct way of doing it and the way I'd do it if I was writing code someone else was going to read.

But here are some creative quick and dirty ways

1

df = df.T
df.index = [['CU-101'] * len(df), df.index]
df.columns = [['Hour'] * len(df.columns), df.columns]

2

pd.concat([pd.concat([df.T], keys=['CU-101'])], axis=1, keys=['Hour'])

edited Feb 4, 2017 at 23:28

answered Feb 4, 2017 at 23:12

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

Pandas Dataframe - multilevel index /column

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related