Pandas apply function to each row by calculating multiple columns-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

To shortcut the operations (esp. remove the merge), you can use groupy.transform, which will retain the original index:

df["what_i_want_2"] = (df["amount"] * df["con"]) / (
    df.groupby("name")["amount"].transform("sum")
)