I have a dataframe with sales quantity for a list of products. Each product is assigned a design/range name. Within each design, there may be multiple products. How can I perform calculations within only a certain design to find the sales split? I want to find out what percentage of a given range come from a certain product. I have so far only been able to take the entire list of products and calculate the percentage each contributes to the overall sales quantity.
Original datafram:
id Product Range Quantity
1 Prod1 A 6
2 Prod2 A 4
3 Prod3 B 2
4 Prod4 B 8
Dataframe after calculation:
id Product Range Quantity % of range
1 Prod1 A 6 60%
2 Prod2 A 4 40%
3 Prod3 B 2 20%
4 Prod4 B 8 80%
>Solution :
You need a simple groupby.transform('sum') to get the total per group, then perform classical vector arithmetic.
I provided an example as float and one as string:
total = df.groupby('Range')['Quantity'].transform('sum')
# as float
df['% of range'] = df['Quantity'].div(total)
# as string
df['% of range (str)'] = df['Quantity'].div(total).mul(100).astype(int).astype(str) + ' %'
output:
id Product Range Quantity % of range % of range (str)
0 1 Prod1 A 6 0.6 60 %
1 2 Prod2 A 4 0.4 40 %
2 3 Prod3 B 2 0.2 20 %
3 4 Prod4 B 8 0.8 80 %