I have a data frame
0 1
A B A B
1 4 6 5 6
2 4 8 15 3
3 8 10 10 2
I wanted to create an index so all of the columns are divided by the total of the A column for their persprective level 0 i.e. 0 or 1
so
0=16
1=30
so the output is
0 1
A B A B
1 4/16 6/16 5/30 6/30
2 4/16 8/16 15/30 3/30
3 8 /16 10/16 10/30 2/30
so far I have
df_in.divide(df_in.iloc[:, df_in.columns.get_level_values(1)=='A'].sum(axis=0),level=0,axis=1)
But its not working
Use DataFrame.xs
for select level A
with sum
and pass to DataFrame.div
:
df = df.div(df.xs('A', axis=1, level=1).sum(), axis=1, level=0)
print (df)
0 1
A B A B
1 0.25 0.375 0.166667 0.200000
2 0.25 0.500 0.500000 0.100000
3 0.50 0.625 0.333333 0.066667