I have been stacked by an easy question, and my question title might be inappropriate.
JavaScript
x
6
1
df = pd.DataFrame(list(zip(['a', 'a', 'b', 'b', 'c', 'c', 'c'],
2
['a1', 'a2', 'b1', 'b2', 'c1', 'c2', 'c3'],
3
[110, 80, 100, 180, 12],
4
[5, 7, 2, 6, 10])),
5
columns=['name', 'ingredient', 'amount', 'con'])
6
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:
JavaScript
1
6
1
df['cal'] =df.amount * df.con
2
df = df.merge(df.groupby('name').agg({'amount':'sum'}).reset_index(),
3
on = ['name'], how = 'left', suffixes = (None, '_y'))
4
df['what_i_want'] = df['cal']/df['amount_y']
5
df.groupby('name').what_i_want.sum()
6
output:
JavaScript
1
6
1
name
2
a 5.842105
3
b 4.571429
4
c 10.000000
5
Name: what_i_want, dtype: float64
6
Any short-cut for this calculation?
Thanks ahead.
Advertisement
Answer
IIUC, you can use:
JavaScript
1
5
1
out = (df
2
.groupby('name')
3
.apply(lambda g: g['amount'].mul(g['con']).sum()/g['amount'].sum())
4
)
5
output:
JavaScript
1
6
1
name
2
a 5.842105
3
b 4.571429
4
c 10.000000
5
dtype: float64
6