Group by a boolean variable and create a new column with the result for each group pandas

user_13783882 0
Viewed 124

This may be a litte confusing, but I have the following dataframe:

exporter assets   liabilities

False      5          1
True       10         8
False      3          1
False      24         20
False      40         2
True       12         11

I want to calculate a ratio with this formula df['liabilieties'].sum()/df['assets'].sum())*100

And I expect to create a new column where the values are the ratio but calculated for each boolean value, like this:

exporter assets   liabilities   ratio

False      5          1         33.3
True       10         8         86.3 
False      3          1         33.3
False      24         20        33.3
False      40         2         33.3
True       12         11        86.3
pandas formula dataframe python
1 Answers

Use DataFrame.groupby on column exporter and transform the datafarme using sum, then use Series.div to divide liabilities by assets and use Series.mul to multiply by 100:

d = df.groupby('exporter').transform('sum')
df['ratio'] = d['liabilities'].div(d['assets']).mul(100).round(2)

Result:

print(df)
   exporter  assets  liabilities  ratio
0     False       5            1  33.33
1      True      10            8  86.36
2     False       3            1  33.33
3     False      24           20  33.33
4     False      40            2  33.33
5      True      12           11  86.36
user_128331660
Related