The Python Oracle

Pandas groupby get filtered sum over total sum

--------------------------------------------------
Hire the world's top talent on demand or became one of them at Toptal: https://topt.al/25cXVn
and get $2,000 discount on your first invoice
--------------------------------------------------

Music by Eric Matyas
https://www.soundimage.org
Track title: Darkness Approaches Looping

--

Chapters
00:00 Pandas Groupby Get Filtered Sum Over Total Sum
00:50 Answer 1 Score 2
01:18 Accepted Answer Score 2
02:06 Answer 3 Score 1
02:17 Thank you

--

Full question
https://stackoverflow.com/questions/5862...

--

Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...

--

Tags
#python #pandas #pandasgroupby

#avk47



ANSWER 1

Score 2


I believe you can use a division on groupby.transform() with sum and assign using .assign() after filtering so as to align on ythe index:

df[df['C']].assign(Ratio=df['Value']/df.groupby('Group')['Value'].transform('sum'))

If more than 1 True per group, use:

m=(df.groupby(['Group','C'],as_index=False,sort=False)['Value'].sum()
  .query('C==True').assign(Sum=df.groupby(['Group'])['Value'].transform('sum')))
m[['Group']].assign(Ratio=m['Value']/m['Sum'])

   Group     Ratio
0      1  0.333333
3      2  0.571429



ACCEPTED ANSWER

Score 2


You can divide by aggregate filtered rows with all rows and then convert Series to one column DataFrame:

filt = df.loc[df['C']].groupby('Group')['Value'].sum()
tot = df.groupby('Group')['Value'].sum()
df1 = filt.div(tot, fill_value=0).to_frame('ratio')
print (df1)
          ratio
Group          
1      0.333333
2      0.571429

Your solution is possible with change .agg working with all columns to GroupBy.apply for return Series, but if large data/ many unique groups it should be slow:

df = (df.groupby('Group')
        .apply(lambda x: x.loc[x.C, 'Value'].sum() / x.Value.sum())
        .to_frame('ratio'))
print (df)
          ratio
Group          
1      0.333333
2      0.571429

Solutions working nice also with only False group:

df = pd.DataFrame([[0, 2, False], [1, 2, True], [1, 4, False], 
                   [2, 6, False], [2, 8, True]], columns=["Group", "Value", "C"])


df1 = (df.groupby('Group')
        .apply(lambda x: x.loc[x.C, 'Value'].sum() / x.Value.sum())
        .to_frame('ratio'))
print (df1)
          ratio
Group          
0      0.000000
1      0.333333
2      0.571429

filt = df.loc[df['C']].groupby('Group')['Value'].sum()
tot = df.groupby('Group')['Value'].sum()

print (df1)
          ratio
Group          
0      0.000000
1      0.333333
2      0.571429



ANSWER 3

Score 1


You could use apply:

result = df.groupby('Group').apply(lambda x: pd.Series({'ratio' : (x.Value * x.C).sum() / x.Value.sum()}))
print(result)

Output

          ratio
Group          
1      0.333333
2      0.571429