Calculate a case when count in a group by using Pandas

user_10061482 0
Viewed 602

i'm preatty beginner using python, i'm trying to calculate the open rate ratio (a ration between two distinct count) in just one code line. My dataframe is like this:

   df = pd.DataFrame([
    (142, 1, 'open' , 'Mobile'),
    (144, 2, 'open' , 'Mobile'),
    (144, 1, 'delivered', 'Web'),
    (142, 1, 'delivered', 'Mobile'),
    (142, 2, 'delivered', 'Web'),
    (144, 1, 'open', 'Web'),
    (142, 2, 'open', 'Mobile')
], columns=['sent_mail_id', 'customer_id', 'event' , 'Tool_used'])

I would like to calculate the open rate while grouping by the Column Tool_used Using Pandas. In SQL Language would be this:

  select 
  Tool_used ,  
  count(distinct case when event='open' then sent_mail_id end)/count(distinct case when 
  event='delivered' then sent_mail_id end)
  from df
  group by 1

Note that i would need to count distinctly the sent_mail_id since unique count is needed. Thank you

pandas case python-3.x group-by python
2 Answers

See if this is what you need, with a column of open rate ratio within each group:

df1 = ((df.loc[df['event'] == 'open'].groupby('Tool_used')['event'].count() 
       / 
       df.loc[df['event'] == 'delivered'].groupby('Tool_used')['event'].count())
       .to_frame(name='open rate ratio')
      ).reset_index()

Result:

print(df1)



  Tool_used  open rate ratio
0    Mobile              3.0
1       Web              0.5
user_150706970

Use crosstab, so then is necessary only divide columns open with delivered with Series.reset_index:

df1 = pd.crosstab(df['Tool_used'], df['event'])
print (df1)
event      delivered  open
Tool_used                 
Mobile             1     3
Web                2     1

df2 = df1['open'].div(df1['delivered']).reset_index(name='open rate ratio')
print (df2)
  Tool_used  open rate ratio
0    Mobile              3.0
1       Web              0.5

If need groupby compare and aggregate sum, but this is more complicated in my opinion:

a = (df['event'] == 'open').groupby(df['Tool_used']).sum()
b = (df['event'] == 'delivered').groupby(df['Tool_used']).sum()

df2 = a.div(b).reset_index(name='open rate ratio')
print (df2)
  Tool_used  open rate ratio
0    Mobile              3.0
1       Web              0.5

Solution with custom function (worse performance if big data):

def f(x):
    return (x == 'open').sum() / (x == 'delivered').sum()

df2 = df.groupby('Tool_used')['event'].agg(f).reset_index(name='open rate ratio')
print (df2)
  Tool_used  open rate ratio
0    Mobile              3.0
1       Web              0.5
user_29010020
Related