I have been stacked by an easy question, and my question title might be inappropriate.
df = pd.DataFrame(list(zip(['a', 'a', 'b', 'b', 'c', 'c', 'c'], ['a1', 'a2', 'b1', 'b2', 'c1', 'c2', 'c3'], [110, 80, 100, 180, 12], [5, 7, 2, 6, 10])), columns=['name', 'ingredient', 'amount', 'con'])
I want to calculate (df.amount * df.con)/df.groupby('name').agg({'amount':'sum'}).reset_index().loc(df.name==i).amount)
(Sorry, this line will return error, but what I want is to calculate total concentration (under each name) based on each ingredient amount and ingredient con.
Here is my code:
df['cal'] =df.amount * df.con df = df.merge(df.groupby('name').agg({'amount':'sum'}).reset_index(), on = ['name'], how = 'left', suffixes = (None, '_y')) df['what_i_want'] = df['cal']/df['amount_y'] df.groupby('name').what_i_want.sum()
output:
name a 5.842105 b 4.571429 c 10.000000 Name: what_i_want, dtype: float64
Any short-cut for this calculation?
Thanks ahead.
Advertisement
Answer
IIUC, you can use:
out = (df .groupby('name') .apply(lambda g: g['amount'].mul(g['con']).sum()/g['amount'].sum()) )
output:
name a 5.842105 b 4.571429 c 10.000000 dtype: float64