按列分组后的移动平均数

Question

   CargoTons   DateOrigin  DateDestination Origin  Destination
0   72875.0 2020-01-01  2020-01-08  Snohvit Dragon
1   77126.0 2020-01-01  2020-01-16  Cameron (Liqu.) Grain
2   0       2020-01-02          
3   67500.0 2020-01-03  2020-01-18  Sabine Pass South Hook
4   93843.0 2020-01-04  2020-01-23  Ras Laffan  South Hook
5   76239.0 2020-01-05  2020-01-14  Yamal       Grain
6   71749.0 2020-01-05  2020-01-23  Sabine Pass Dragon
7   75353.0 2020-01-06  2020-01-22  Sabine Pass South Hook
8   71749.0 2020-01-07  2020-01-21  Sabine Pass South Hook
9   0       2020-01-08          
10  96925.0 2020-01-09  2020-01-25  Ras Laffan  South Hook
11  65013.0 2020-01-10  2020-01-22  Snohvit     Grain
12  76505.0 2020-01-10  2020-01-19  Yamal       Dragon
13  0       2020-01-11          
14  0       2020-01-12          
15  0       2020-01-13          
16  0       2020-01-14          
17  0       2020-01-15  

上面是可用数据的快照

我希望有一个移动平均值列，该列根据日期而不是行给出MA，即我在同一日期有多个条目的天数应该只有一个值作为MA

我希望MA在第一组中按原点计算，然后按日期计算

以下是每个来源组中缺失日期的脚本-

start = pd.datetime(2020,1,1)
end = pd.datetime(2020,1,31)  
index = pd.date_range(start, end)
mockdata = pd.DataFrame()
mockdata['DateOrigin'] = index

Answer 1

模块：

import io
import pandas as pd

数据示例：

df = pd.read_csv(io.StringIO("""
   CargoTons DateOrigin Origin
 0 72875.0 2020-01-01 Snohvit
 1 77126.0 2020-01-01 Cameron
 2 0 2020-01-02          
 3 67500.0 2020-01-03 SabinePass
 4 93843.0 2020-01-04 RasLaffan
 5 76239.0 2020-01-05 Yamal
 6 71749.0 2020-01-05 SabinePass
 7 75353.0 2020-01-06 SabinePass
 8 71749.0 2020-01-07 SabinePass
 9 0 2020-01-08          
 10 96925.0 2020-01-09 RasLaffan
 11 65013.0 2020-01-10 Snohvit
 12 76505.0 2020-01-10 Yamal
 13 0 2020-01-11          
 14 0 2020-01-12          
 15 0 2020-01-13          
 16 0 2020-01-14          
 17 0 2020-01-15
 """), sep="\s", engine="python")

您可以在Origin和DateOrigin上使用groupby，首先计算相同日期不同值的平均值

ndf = pd.DataFrame(df.groupby(['Origin', 'DateOrigin']).aggregate('CargoTons').mean()).reset_index()

然后使用rolling，例如在2的窗口上使用平均值。将Origin作为groupby

ndf.groupby(['Origin']).rolling(2).mean()

其中：

               CargoTons
Origin                  
Cameron    0         NaN
RasLaffan  1         NaN
           2     95384.0
SabinePass 3         NaN
           4     69624.5
           5     73551.0
           6     73551.0
Snohvit    7         NaN
           8     68944.0
Yamal      9         NaN
           10    76372.0

验证

如您所见，在Origin的每个类别中，它都是从NAN开始的，这是由于滚动窗口的缘故。例如，Snohvit 68944.0的输出是其输入（72875+65013）/2的平均值