How to add another subcolumn to dataframe with multi-index-CodePudding

I'm trying to add a third column "Productivity" so every role like Admin would have three sub columns produktiv, unproduktiv and Productivity.

Productivity would be calculated as follows:

Productivity = Produktiv / (Produktiv Unproduktiv) * 100

(don't mind the s, I had to anonymize the data)

Here is the output of df.columns

Any help would be greatly appreciated. Thank you.

CodePudding user response：

If there are only Produktiv and Unproduktiv level for sum is possible aggregate by first level of MultiIndex, divide by Produktiv and after rename second level of MultiIndex append to originam Dataframe by concat:

df1 = (df.xs('Produktiv', axis=1, level=1, drop_level=False)
         .div(df.groupby(level=0, axis=1).sum(), level=0).mul(100))

df = (pd.concat([df, df1.rename(columns={'Produktiv':'Productivity'}, level=1)], axis=1)
        .sort_index(axis=1)
        .reindex(['Produktiv','Unproduktiv','Productivity'], level=1, axis=1))
print (df)

Another idea is get both slices by Produktiv, Unproduktiv and add level by pd.concat first:

df1 = df.xs('Produktiv', axis=1, level=1)
df2 = df.xs('Unproduktiv', axis=1, level=1)

df11 = (pd.concat({'Productivity':df1.div(df1.add(df2)).mul(100)}, axis=1)
          .swaplevel(0,1,axis=1))
    
df = (pd.concat([df, df11], axis=1)
        .sort_index(axis=1)
        .reindex(['Produktiv','Unproduktiv','Productivity'], level=1, axis=1))