You can use the following function to calculate a weighted average in Pandas:
def w_avg(df, values, weights): d = df[values] w = df[weights] return (d * w).sum() / w.sum()
The following examples show how to use this syntax in practice.
Example 1: Weighted Average in Pandas
The following code shows how to use the weighted average function to calculate a weighted average for a given dataset, using “price” as the values and “amount” as the weights:
import pandas as pd
#create DataFrame
df = pd.DataFrame({'sales_rep': ['A', 'A', 'A', 'B', 'B', 'B'],
'price': [8, 5, 6, 7, 12, 14],
'amount': [1, 3, 2, 2, 5, 4]})
#view DataFrame
df
sales_rep price amount
0 A 8 1
1 A 5 3
2 A 6 2
3 B 7 2
4 B 12 5
5 B 14 4
#find weighted average of price
w_avg(df, 'price', 'amount')
9.705882352941176
The weighted average of “price” turns out to be 9.706.
Example 2: Groupby and Weighted Average in Pandas
The following code shows how to use the weighted average function to calculate the weighted average of price, grouped by sales rep:
import pandas as pd
#create DataFrame
df = pd.DataFrame({'sales_rep': ['A', 'A', 'A', 'B', 'B', 'B'],
'price': [8, 5, 6, 7, 12, 14],
'amount': [1, 3, 2, 2, 5, 4]})
#find weighted average of price, grouped by sales rep
df.groupby('sales_rep').apply(w_avg, 'price', 'amount')
sales_rep
A 5.833333
B 11.818182
dtype: float64
We can see the following:
- The weighted average of “price” for sales rep A is 5.833.
- The weighted average of “price for sales rep B is 11.818.
Additional Resources
How to Compare Two Columns in Pandas
How to Calculate the Sum of Columns in Pandas
How to Calculate the Mean of Columns in Pandas