使用 pandas /数据框计算加权平均值 [英] Calculate weighted average using a pandas/dataframe

问题描述

我有下表.我想根据以下公式计算按每个日期分组的加权平均值.我可以使用一些标准的常规代码来做到这一点，但是假设此数据在pandas数据框中，有没有比迭代更容易的方法了?

I have the following table. I want to calculate a weighted average grouped by each date based on the formula below. I can do this using some standard conventional code, but assuming that this data is in a pandas dataframe, is there any easier way to achieve this rather than through iteration?

Date        ID      wt      value   w_avg
01/01/2012  100     0.50    60      0.791666667
01/01/2012  101     0.75    80
01/01/2012  102     1.00    100
01/02/2012  201     0.50    100     0.722222222
01/02/2012  202     1.00    80

2012年1月1日w_avg = 0.5 *(60/sum(60,80,100))+ .75 *(80/ sum(60,80,100))+ 1.0 *(100/sum(60,80,100))

01/01/2012 w_avg = 0.5 * ( 60/ sum(60,80,100)) + .75 * (80/ sum(60,80,100)) + 1.0 * (100/sum(60,80,100))

2012年1月2日w_avg = 0.5 *(100/sum(100,80))+ 1.0 *(80/ sum(100,80))

01/02/2012 w_avg = 0.5 * ( 100/ sum(100,80)) + 1.0 * ( 80/ sum(100,80))

推荐答案

我想我可以通过两个groupbys来做到这一点.

I think I would do this with two groupbys.

首先计算加权平均值":

First to calculate the "weighted average":

In [11]: g = df.groupby('Date')

In [12]: df.value / g.value.transform("sum") * df.wt
Out[12]:
0    0.125000
1    0.250000
2    0.416667
3    0.277778
4    0.444444
dtype: float64

如果将其设置为列，则可以对其进行分组:

If you set this as a column, you can groupby over it:

In [13]: df['wa'] = df.value / g.value.transform("sum") * df.wt

现在此列的总和是所需的:

Now the sum of this column is the desired:

In [14]: g.wa.sum()
Out[14]:
Date
01/01/2012    0.791667
01/02/2012    0.722222
Name: wa, dtype: float64

或可能:

In [15]: g.wa.transform("sum")
Out[15]:
0    0.791667
1    0.791667
2    0.791667
3    0.722222
4    0.722222
Name: wa, dtype: float64

这篇关于使用 pandas /数据框计算加权平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！