Calculate percentages in groupby, based on another column

user_16232648 0
Viewed 298

I have a table like:

Month Binary Value_missing Total_value
1 N 40 120
1 Y 5 50
2 N 30 200
2 Y 10 20

I want to calculate in pandas a groupby that gives me a percentage of the column Value_missing based on the Total_value. I expected to get:

Month Binary Value_missing Total_value %_Value_missing
1 N 40 120 0,235
1 Y 5 50 0,029
2 N 30 200 0,1363
2 Y 10 20 0,045

For each row/ cell in the column Value_missing, I want to divide by the sum of Total_Value aggregated by month

An example of the calculus off the first row: 40 / (120 + 50) = 0,235

Thank you!

pandas-groupby pandas percentage aggregate-functions python
1 Answers

Here's one way:

df['%_Value_missing'] = df['Value_missing'].div(df.groupby('Month')['Total_value'].transform(sum))

Alternative:

df['%_Value_missing'] = df.groupby('Month').apply(lambda x: x['Value_missing'] / x['Total_value'].sum()).values

OUTPUT:

   Month Binary  Value_missing  Total_value  %_Value_missing
0      1      N             40          120         0.235294
1      1      Y              5           50         0.029412
2      2      N             30          200         0.136364
3      2      Y             10           20         0.045455

Some performance comparisons:

%%timeit
df['Value_missing'].div(df.groupby('Month')['Total_value'].transform(sum))
541 µs ± 19.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

%%timeit
df.groupby('Month').apply(lambda x: x['Value_missing'] / x['Total_value'].sum()).values
1.55 ms ± 4.61 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
user_154380330
Related