How to add another subcolumn to dataframe with multi-index

Question

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.

Answer 1

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))