python - 如何使用 pandas 计算加权数据？

Question

这里我有一个大数据集

为简单起见，我写了一个简化的例子

import pandas as pd
import numpy as np

indices = (1,2,3,4,5,6)
cols = ["id", "region", "weight", "score1", "score2"]
data = (["xxx1", 1, 2, 10, 20], ["xxx2", 2, 5, 6, 8], ["xxx3", 1, 3, 9, 12], ["xxx4", 1, 3, 12, 20], ["xxx5", 1, 5, 5, 30], ["xxx6", 2, 10, 12, 20])
df = df = pd.DataFrame(data, index = indices, columns = cols)
df

看起来像这样

我只想使用 pandas 和 numpy 包计算加权平均分数，我的理想结果应该是一个 2x3 表，其行是它们所属的区域，列是加权平均 score1 和 score2。下面是我的尝试

def cal(x, w):
    assert len(x) == len(w)
    nlist = len(x)
    sum_weight = np.sum(w)
    weight_total = 0
    for i in range(nlist):
        weight_total = weight_total + x[i] * w[i]
    
    wavg = weight_total / sum_weight
    return wavg

但是，我认为这种方法太尴尬了。我想知道是否有更方便的方法来实现相同的目标。欢迎任何提示或帮助

理想的结果

标签： pythonpandasdataframe

Answer 1

尝试分别计算加权总和和重量总和，然后除以：

weighted_total = df.filter(like='score').mul(df.weight, axis=0).groupby(df.region).sum()
sum_weight = df.weight.groupby(df.region).sum()
weighted_total.div(sum_weight, axis=0)

           score1  score2
region                   
1        8.307692    22.0
2       10.000000    16.0

或者在一个单一的groupby：

df.groupby('region').apply(
  lambda g: g.filter(like='score').mul(g.weight, axis=0).sum().div(g.weight.sum(), axis=0)
)
           score1  score2
region                   
1        8.307692    22.0
2       10.000000    16.0