How to add Multilevel Columns and create new column?

user_4048123 0
Viewed 259

I am trying to create a "total" column in my dataframe

idx = pd.MultiIndex.from_product([['Room 1','Room 2', 'Room 3'],['on','off']])
df = pd.DataFrame([[1,4,3,6,5,15], [3,2,1,5,1,7]], columns=idx)

My dataframe

    Room 1  Room 2  Room 3
    on  off on  off on  off
0   1   4   3   6   5   15
1   3   2   1   5   1   7

For each room, I want to create a total column and then a on% column.

I have tried the following, however, it does not work.

df.loc[:, slice(None), "total" ] = df.xs('on', axis=1,level=1) + df.xs('off', axis=1,level=1)
pandas dataframe
3 Answers

Let us try something fancy ~

df.stack(0).eval('total=on + off \n on_pct=on / total').stack().unstack([1, 2])

     Room 1                   Room 2                      Room 3                  
     off   on total on_pct    off   on total    on_pct    off   on total on_pct
0    4.0  1.0   5.0    0.2    6.0  3.0   9.0  0.333333   15.0  5.0  20.0  0.250
1    2.0  3.0   5.0    0.6    5.0  1.0   6.0  0.166667    7.0  1.0   8.0  0.125
user_128331660

Oof this was a roughie, but you can do it like this if you want to avoid loops. Worth noting it redefines your df twice because i need the total columns. Sorry about that, but is the best i could do. Also if you have any questions just comment.

df = pd.concat([y.assign(**{'Total {0}'.format(x+1): y.iloc[:,0] + y.iloc[:,1]})for x , y in df.groupby(np.arange(df.shape[1])//2,axis=1)],axis=1)


df = pd.concat([y.assign(**{'Percentage_Total{0}'.format(x+1): (y.iloc[:,0] / y.iloc[:,2])*100})for x , y in df.groupby(np.arange(df.shape[1])//3,axis=1)],axis=1)
print(df)
user_128451990

This groups by the column's first index (rooms) and then loops through each group to add the total and percent on. The final step is to reindex using the unique rooms:

import pandas as pd

idx = pd.MultiIndex.from_product([['Room 1','Room 2', 'Room 3'],['on','off']])
df = pd.DataFrame([[1,4,3,6,5,15], [3,2,1,5,1,7]], columns=idx)

for room, group in df.groupby(level=0, axis=1):
    df[(room, 'total')] = group.sum(axis=1)
    df[(room, 'pct_on')] = group[(room, 'on')] / df[(room, 'total')]

result = df.reindex(columns=df.columns.get_level_values(0).unique(), level=0)

Output:

      Room 1                  Room 2                     Room 3                 
      on off total pct_on     on off total    pct_on     on off total pct_on
0      1   4     5    0.2      3   6     9  0.333333      5  15    20  0.250
1      3   2     5    0.6      1   5     6  0.166667      1   7     8  0.125
user_36694650
Related