Getting % Rate using Pandas Group By and .sum()

user_9571463 0
Viewed 134

I'd like to get some % rates based on a .groupby() in pandas. My goal is to take an indicator column Ind and get the Rate of A (numerator) divided by the total (A+B) in that year

Example Data:

import pandas as pd
import numpy as np
df: pd.DataFrame = pd.DataFrame([['2011','A',1,2,3], ['2011','B',4,5,6],['2012','A',15,20,4],['2012','B',17,12,12]], columns=["Year","Ind","X", "Y", "Z"])
print(df)
   Year Ind   X   Y   Z
0  2011   A   1   2   3
1  2011   B   4   5   6
2  2012   A  15  20   4
3  2012   B  17  12  12

Example for year 2011: XRate would be summing up the A indicators for X (which would be 1) and dividing byt the total (A+B) which would be 5 thus I would receive an Xrate of 0.20.

I would like to do this for all columns X, Y, Z to get the rates. I've tried doing lambda applys but can't quite get the desired results.

Desired Results:

   Year XRate YRate  ZRate
0  2011  0.20  0.29   0.33 
1  2012  0.47  0.63   0.25
pandas-groupby pandas dataframe python
1 Answers

You can group the dataframe on Year and aggregate using sum:

s1 = df.groupby('Year').sum()
s2 = df.query("Ind == 'A'").groupby('Year').sum()

s2.div(s1).round(2).add_suffix('Rate')

      XRate  YRate  ZRate
Year                     
2011   0.20   0.29   0.33
2012   0.47   0.62   0.25
user_128331660
Related