python - GroupBy 多列并应用移动功能

Question

假设我有这个数据集:

Country_id  Company_id  Date    Company_value
1   1   01/01/2018  1
1   1   02/01/2018  0
1   1   03/01/2018  2
1   1   04/01/2018  NA
1   2   01/01/2018  1
1   2   02/01/2018  2
1   2   03/01/2018  NA
1   2   04/01/2018  NA
2   1   01/01/2018  3
2   1   02/01/2018  0
2   1   03/01/2018  2
2   1   04/01/2018  NA
2   2   01/01/2018  1
2   2   02/01/2018  2
2   2   03/01/2018  NA
2   2   04/01/2018  NA

我想应用移动函数(例如移动平均线)来检索每个日期和国家/地区的聚合值。

因此，例如在移动平均线的情况下(窗口 = 2 & min_periods = 1，不计入 NA)我想要以下内容:

Country_id  Date    Companies_value
1   01/01/2018  1
1   02/01/2018  1
1   03/01/2018  1.33
1   04/01/2018  2
2   01/01/2018  2
2   02/01/2018  1.5
2   03/01/2018  1.33
2   04/01/2018  2

为了方便您，这是按以下方式计算的:

Country_id  Date    Companies_value
1   01/01/2018  (1+1)/2
1   02/01/2018  (0+1+2+1)/4
1   03/01/2018  (2+0+2)/3
1   04/01/2018  (2)/1
2   01/01/2018  (3+1)/2
2   02/01/2018  (0+3+2+1)/4
2   03/01/2018  (2+0+2)/3
2   04/01/2018  (2)/1

我如何用 pandas 做到这一点？

用文字举个简单的例子，例如我想要国家 1 在 03/01/2018 的日期是取这个国家所有公司在 02/01/2018 和03/01/2018(在窗口大小为 2 的情况下)。

因此，这就是我想在 2018 年 3 月 1 日为国家 1 做的事情:

( Company_value(Company_1, 03/01/2018) + Company_value(Company_1, 02/01/2018) 
+ Company_value(Company_2, 03/01/2018) + Company_value(Company_2, 02/01/2018) ) / 4 =

= ( 2 + 0 + NA + 2) / 4 

= ( 2 + 0 + 2) / 3 # NAs not counted in

= 1.33

类似地，我想对每个国家/地区的所有日期执行相同的操作。

正如我所说，除了 pandas 的移动平均线之外，我还想对自己的移动函数做同样的事情，因此最好提供一个对任何自定义函数都有效的解决方案。

Answer 1

更新了更多信息

数据:

import pandas as pd
import numpy as np

df = pd.DataFrame({'date':['2018-01-01', '2018-02-01', '2018-03-01', '2018-04-01']*4,
              'country_id':[1]*8+[2]*8,
              'company_id':[1]*4+[2]*4+[1]*4+[2]*4,
              'value':[1, 0, 2, np.nan, 1, 2, np.nan, np.nan, 3, 0, 2, np.nan, 1, 2, np.nan, np.nan]})

在 country_id 内创建滚动总和

df['rolling_sum'] = df.groupby('country_id').apply(lambda x: x.value.rolling(window=2, min_periods=1).sum()).reset_index(drop=True)

在 country_id 内创建滚动计数

df['sum_records'] = df.groupby('country_id').apply(lambda x: x.value.rolling(window=2, min_periods=1).count()).reset_index(drop=True)

现在在 country_id 和 date 内分组，对总和进行求和，然后除以计数总和

summarized_df = df.groupby(['country_id', 'date']).apply(lambda x: x.rolling_sum.sum()/x.sum_records.sum()).reset_index()

country_id  date      
1           2018-01-01    1.000000
            2018-02-01    1.000000
            2018-03-01    1.333333
            2018-04-01    2.000000
2           2018-01-01    2.000000
            2018-02-01    1.500000
            2018-03-01    1.333333
            2018-04-01    2.000000

让我们更详细地看看这个。由于我们按 country_id 分组，我们将分出一个国家 id 以在以下方面实践此方法:

如果我们只取其中的一部分，比如 country_id == 1:

df2 = df[df['country_id'] == 1]

         date  country_id  company_id  value
0  2018-01-01           1           1    1.0
1  2018-02-01           1           1    0.0
2  2018-03-01           1           1    2.0
3  2018-04-01           1           1    NaN
4  2018-01-01           1           2    1.0
5  2018-02-01           1           2    2.0
6  2018-03-01           1           2    NaN
7  2018-04-01           1           2    NaN

如果我们想要这个的滚动平均值，我们可以这样做:

df2.value.rolling(window=2, min_periods=1).mean()
0    1.0
1    0.5
2    1.0
3    2.0
4    1.0
5    1.5
6    2.0
7    NaN

我们可以在这里看到子集 country_id == 1 数据帧中的值以及它们与滚动平均值的关系:

0    1.0  = (1)/1 = 1
1    0.0  = (0 + 1)/2 = 0.5
2    2.0  = (2 + 0)/2 = 1
3    NaN  = (Nan + 2)/1 = 2
4    1.0  = (1 + Nan)/1 = 1
5    2.0  = (2 + 1)/2 = 1.5
6    NaN  = (Nan + 2)/1 = 2
7    NaN  = (Nan + Nan)/0 = Nan

这就是我们如何获得单个 country_id 分组的滚动平均值

如果我们想要按日期分组，并且我们采用了先按 country_id 分组，然后按日期分组的路线，单个组将如下所示:

df3 = df[(df['country_id'] == 1) & (df['date'] == '2018-03-01')]

df3.value
2    2.0
6    NaN

df3.value.rolling(window=2, min_periods=1).mean()
2    2.0
6    2.0

df3.value
2    2.0 = (2)/1 = 2
6    NaN = (Nan + 2)/1 = 2

这里的问题是，您希望滚动平均值首先按country_id，而不是按date 分组。 然后在您按国家/地区找到滚动平均值后，您想要获取那些值并对它们进行平均。如果我们采用滚动平均值，然后对它们进行平均，结果会不正确。

那么让我们回到我们为 country_id == 1 创建的原始滚动平均值，并查看日期:

2018-01-01    1.0  = (1)/1 =         1
2018-02-01    0.0  = (0 + 1)/2 =     0.5
2018-03-01    2.0  = (2 + 0)/2 =     1
2018-04-01    NaN  = (Nan + 2)/1 =   2
2018-01-01    1.0  = (1 + Nan)/1 =   1
2018-02-01    2.0  = (2 + 1)/2 =     1.5
2018-03-01    NaN  = (Nan + 2)/1 =   2
2018-04-01    NaN  = (Nan + Nan)/0 = Nan

现在这里棘手的部分是，此时我们不能将它们平均在一起，因为例如，如果您查看 2018-03-01 滚动平均值，我们有 1 和 2，即 3。将其除以2 会给我们 1.5。

我们必须先对滚动值求和，然后除以记录数。