Pandas groupby get filtered sum over total sum
Become part of the top 3% of the developers by applying to Toptal https://topt.al/25cXVn
--
Music by Eric Matyas
https://www.soundimage.org
Track title: Fantascape Looping
--
Chapters
00:00 Question
01:09 Accepted answer (Score 2)
02:18 Answer 2 (Score 2)
02:53 Answer 3 (Score 1)
03:11 Thank you
--
Full question
https://stackoverflow.com/questions/5862...
Accepted answer links:
[GroupBy.apply]: http://pandas.pydata.org/pandas-docs/sta...
Answer 3 links:
[apply]: https://pandas.pydata.org/pandas-docs/st...
--
Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...
--
Tags
#python #pandas #pandasgroupby
#avk47
--
Music by Eric Matyas
https://www.soundimage.org
Track title: Fantascape Looping
--
Chapters
00:00 Question
01:09 Accepted answer (Score 2)
02:18 Answer 2 (Score 2)
02:53 Answer 3 (Score 1)
03:11 Thank you
--
Full question
https://stackoverflow.com/questions/5862...
Accepted answer links:
[GroupBy.apply]: http://pandas.pydata.org/pandas-docs/sta...
Answer 3 links:
[apply]: https://pandas.pydata.org/pandas-docs/st...
--
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