Apply pandas groupby aggregation on multiindex

Question

I have a pandas DataFrame

                    A

   foo   one        3
         two        2
         three      4
   bar   one        1
         two        5
         three      2

I want to add a column with relative values for each row, based on the sum of all rows from the first index level. The sum for all values in foo would be 3+2+4=9, so the relative value for the first row would be 3/9 = 0.33.

The resulting DataFrame would look like that:

                    A    rel

   foo   one        3     0.33
         two        2     0.22
         three      4     0.44
   bar   one        1     0.125
         two        5     0.625
         three      2     0.25

I already tried to use groupby on the DataFrame, but i can only figure out how to apply a method to one groupby dimension.

Answer 1

You can use groupby with transform sum:

df['rel'] = df.A / df.groupby(level=0)['A'].transform(sum)
print df
           A       rel
foo one    3  0.333333
    two    2  0.222222
    three  4  0.444444
bar one    1  0.125000
    two    5  0.625000
    three  2  0.250000