CODEWITHSUNDEEP
pandaspandas-groupby
Danish
Danish

Reputation: 2871

create new column by dividing groupby sum in pandas

I have data frame as shown below

df:

product_x  year     total_price      total_sale
A          2016     50               200           
B          2016     200              100           
A          2017     250              250           
B          2017     1000             300           
A          2018     100              50           
B          2018     900              600

from the above I would like calculate the product_x wise sales percentage as shown below.

Expected output:

product_x  year     total_price      total_sale     product_sale_perc
A          2016     50               200            40
B          2016     200              100            10
A          2017     250              250            50 
B          2017     1000             300            30    
A          2018     100              50             10     
B          2018     900              600            60

I tried below

df['product_sale_perc'] = df['total_sale']/df.groupby('product_x')['total_sale'].sum()

Upvotes: 0

Views: 57

Answers (2)

Mark Wang
Mark Wang

Reputation: 2757

Use groupby apply

df.groupby('product_x')['total_sale'].apply(lambda x:x/x.sum()*100)

Upvotes: 1

Andy L.
Andy L.

Reputation: 25239

You need transform

df['total_sale']/df.groupby('product_x')['total_sale'].transform('sum')*100

Out[541]:
0    40.0
1    10.0
2    50.0
3    30.0
4    10.0
5    60.0
Name: total_sale, dtype: float64

Upvotes: 1

Related Questions