Excel Sumproduct in Pandas-CodePudding

I have a df:

Type   price   stock
a      2       2
b      4       1
b      3       3
a      1       2
a      3       1

The result I would like to get is:

Type   price*stock
a      2*2 1*2 3*1 = 9
b      4*1 3*3 = 13

I can easily do it in Excel, but how about in Pandas? I have tried groupby function but still fails:(

CodePudding user response：

First multiple columns and then aggregate sum for improve performance:

df1 = df.price.mul(df.stock).groupby(df.Type).sum().reset_index(name='price*stock')
print (df1)
  Type  price*stock
0    a            9
1    b           13

Another idea is first crete column with multiple values and then aggregate it:

df1 = (df.assign(**{'price*stock': df.price.mul(df.stock)})
         .groupby('Type', as_index=False)['price*stock']
         .sum())
print (df1)
  Type  price*stock
0    a            9
1    b           13

CodePudding user response：

groupby with respect to Type and apply equation to each group.

out = df.groupby("Type").apply(lambda x: sum(x["price"]*x["stock"])).reset_index(name="price*stock")

print(out)
>>  Type  price*stock
0   a         9
1   b         13

CodePudding user response：

Also:

df.groupby('Type').apply(lambda x:[email protected]).reset_index(name='price_stock')
 
  Type  price_stock
0    a            9
1    b           13

CodePudding user response：

df.groupby('Type').apply(lambda x: x['price'].dot(x['stock'])).to_frame('sumproduct')



       sumproduct
Type            
a              9
b             13