Is there a way to apply a function on a MultiIndex column?

user_12777044 0
Viewed 50

I have a dataframe that looks like this:

   id sex  isActive  score
0   1   M         1     10
1   2   F         0     20
2   2   F         1     30
3   2   M         0     40
4   3   M         1     50

I want to pivot the dataframe on the index id and columns sex and isActive (the value should be score). I want each id to have their score be a percentage of their total score associated with the sex group.

In the end, my dataframe should look like this:

sex          F           M      
isActive     0     1     0     1
id                              
1          NaN   NaN   NaN   1.0
2          0.4   0.6   1.0   NaN
3          NaN   NaN   NaN   1.0

I tried pivoting first:

p = df.pivot_table(index='id', columns=['sex', 'isActive'], values='score')
print(p)

sex          F           M      
isActive     0     1     0     1
id                              
1          NaN   NaN   NaN  10.0
2         20.0  30.0  40.0   NaN
3          NaN   NaN   NaN  50.0

Then, I summed up the scores for each group:

row_sum = p.sum(axis=1, level=[0])
print(row_sum)

sex     F     M
id             
1     0.0  10.0
2    50.0  40.0
3     0.0  50.0

This is where I'm getting stuck. I'm trying to use DataFrame.apply to perform a column-wise sum based on the second dataframe. However, I keep getting errors following this format:

p.apply(lambda col: col/row_sum)

I may be overthinking this problem. Is there some better approach out there?

pandas dataframe python-3.x group-by python
1 Answers

I think just a simple division of p by row_sum would work like:

print (p/row_sum)
sex         F         M     
isActive    0    1    0    1
id                          
1         NaN  NaN  NaN  1.0
2         0.4  0.6  1.0  NaN
3         NaN  NaN  NaN  1.0
user_92747320
Related