'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
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 |
|---|
