CODEWITHSUNDEEP
python-3.xpandas
Krishnang K Dalal
Krishnang K Dalal

Reputation: 2566

Pandas Group By and Conditional Ratio

I want to take the ratio of counts based on a condition and I'm struggling to get it right using pandas data frame.

The data is as below:

                   JOB_ROLE           COMMENTS ACTIVITY_TYPE  COUNTS  
             Director-Level  Meeting Requested     EmailSend     490    
              Manager-Level  Meeting Requested     EmailSend     305  
             Non-Managerial  Meeting Requested     EmailSend     272  
     Top Executive; C-Level  Meeting Requested     EmailSend     226  
                   VP-Level  Meeting Requested     EmailSend     185
             Director-Level  Meeting Requested    FormSubmit     131
              Manager-Level  Meeting Requested    FormSubmit      74
     Top Executive; C-Level  Meeting Requested    FormSubmit      61
                   VP-Level  Meeting Requested    FormSubmit      53
             Non-Managerial  Meeting Requested    FormSubmit      52
                      Other  Meeting Requested     EmailSend      20
                      Other  Meeting Requested    FormSubmit       2

My attempt is as below:

ratios =  mr_jr.groupby('JOB_ROLE').apply(lambda x: x[x['ACTIVITY_TYPE']=='FormSubmit'].COUNTS / x[x['ACTIVITY_TYPE']=='EmailSend'].COUNTS)

What is the right way to apply the condition to each group and perform arithmetic?

Thanks a lot in advance.

EDITED

Desired Output:

print(list(ratios)) # prints: [0.26, 0.24, 0.19, 0.27, 0.28, 0.1]

Upvotes: 2

Views: 72

Answers (1)

hilberts_drinking_problem
hilberts_drinking_problem

Reputation: 11602

Looks like a job for a pivot table.

piv = df.pivot('JOB_ROLE', 'ACTIVITY_TYPE').COUNTS

Output:

In [119]: piv.FormSubmit / piv.EmailSend
Out[119]: 
JOB_ROLE
Director-Level            0.267347
Manager-Level             0.242623
Non-Managerial            0.191176
Other                     0.100000
Top Executive; C-Level    0.269912
VP-Level                  0.286486
dtype: float64

Without pivot:

df.set_index('JOB_ROLE', drop=True, inplace=True)
emails = df[df.ACTIVITY_TYPE == 'EmailSend']
forms  = df[df.ACTIVITY_TYPE == 'FormSubmit']
print(forms.COUNTS / emails.COUNTS)

Upvotes: 2

Related Questions