'Trying to get a fixed value in Group by pandas

I am trying to calculate a weighted score for each line here.

import pandas as pd
df = pd.read_excel('data.xlsx')
index   firm    sales  burgers

0   McDonalds   100 2

1   McDonalds   100 1

2   McDonalds   100 3

3   McDonalds   100 2

4   McDonalds   100 4

5   Burger King 105 1

6   Burger King 105 1

7   Burger King 105 1

8   Burger King 105 1

9   Burger King 105 3

10  Burger King 105 4

11  Burger King 105 4

Output from df

df[weighted score] = df[Total Sales] * df[total sales / sum(firm name total sales)] # I know this second part is wrong but trying to illustrate what I am going for

What I want is to have a fixed denominator for each Firm (mcdonalds, Burger King). So for Mcdonalds, it would be the sum of all the sales (500). this would then be divided by total sales for that line (so 100/500 for each, which would be a score of .2 for each line in the new column.

I will then aggregate by firm using groupby.

I want a fixed value denominator i can use in each line item to calculate a weighted average



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source