在滾動視窗上按一天中的小時對時間序列進行分組并將滾動方法分配給新列的矢量化方式

我們有一個時間序列，values除了列之外還有一列date，每天我們有 24 行（一天中的幾個小時）。目標是創建一個附加列，其中包含滾動視窗上一天中特定小時的值的平均值。例如，對于 365*24 的滾動視窗，新列將傳達以下資訊：“該values列在去年一天中的這個時間點的平均值為 X”。

對于 24 的滾動視窗，輸入和輸出應如下所示：

index    date(as datetime64)  values new_column reasoning

0   2021-02-21  00:00:00 00:00  100     -   
1   2021-02-21  01:00:00 00:00  200     -   
2   2021-02-21  02:00:00 00:00  300     -   
3   2021-02-21  03:00:00 00:00  400     -   
4   2021-02-21  04:00:00 00:00  500     -   
5   2021-02-21  05:00:00 00:00  600     -   
6   2021-02-21  06:00:00 00:00  700     -   
7   2021-02-21  07:00:00 00:00  800     -   
8   2021-02-21  08:00:00 00:00  900     -   
9   2021-02-21  09:00:00 00:00  1000    -   
10  2021-02-21  10:00:00 00:00  1100    -   
11  2021-02-21  11:00:00 00:00  1200    -   
12  2021-02-21  12:00:00 00:00  1300    -   
13  2021-02-21  13:00:00 00:00  1400    -   
14  2021-02-21  14:00:00 00:00  1500    -   
15  2021-02-21  15:00:00 00:00  1600    -   
16  2021-02-21  16:00:00 00:00  1700    -   
17  2021-02-21  17:00:00 00:00  1800    -   
18  2021-02-21  18:00:00 00:00  1900    -   
19  2021-02-21  19:00:00 00:00  2000    -   
20  2021-02-21  20:00:00 00:00  2100    -   
21  2021-02-21  21:00:00 00:00  2200    -   
22  2021-02-21  22:00:00 00:00  2300    -   
23  2021-02-21  23:00:00 00:00  2400    -   
24  2021-02-22  00:00:00 00:00  200     150   (i.e the mean of 100 and 200, the values at 00:00 for the 2 days)
25  2021-02-22  01:00:00 00:00  400     300   (i.e. mean of 200 and 400)
26  2021-02-22  02:00:00 00:00  600     450   (i.e. mean of 300 and 600)
27  2021-02-22  03:00:00 00:00  800     etc.    etc.
28  2021-02-22  04:00:00 00:00  1000        
29  2021-02-22  05:00:00 00:00  1200        
30  2021-02-22  06:00:00 00:00  1400        
31  2021-02-22  07:00:00 00:00  1600        
32  2021-02-22  08:00:00 00:00  1800        
33  2021-02-22  09:00:00 00:00  2000        
34  2021-02-22  10:00:00 00:00  2200        
35  2021-02-22  11:00:00 00:00  2400        
36  2021-02-22  12:00:00 00:00  2600        
37  2021-02-22  13:00:00 00:00  2800        
38  2021-02-22  14:00:00 00:00  3000        
39  2021-02-22  15:00:00 00:00  3200        
40  2021-02-22  16:00:00 00:00  3400        
41  2021-02-22  17:00:00 00:00  3600        
42  2021-02-22  18:00:00 00:00  3800        
43  2021-02-22  19:00:00 00:00  4000        
44  2021-02-22  20:00:00 00:00  4200        
45  2021-02-22  21:00:00 00:00  4400        
46  2021-02-22  22:00:00 00:00  4600        
47  2021-02-22  23:00:00 00:00  4800        

在撰寫新專欄之前，我曾想過嘗試groupby：

means = df.groupby(df.date.dt.hour).values.mean()

但是，這存在回傳整個資料幀的平均每小時而不是它的滾動視窗的問題，而 agroupby(df.date.dt.hour).values.rolling(X).mean()只考慮最終分組結果的視窗而不是分組前要考慮的初始資料視窗，而且我不知道如何將值分配給初始資料幀的新列，因為 groupby() 結果沒有相同的索引。

uj5u.com熱心網友回復：

droplevel在groupby/之后使用rolling以洗掉由創建的級別groupby：

# Or .droplevel('date')
df['mean'] = df.groupby(df['date'].dt.hour).rolling(2)['values'].mean().droplevel(0)
print(df)

# Output
                        date  values    mean
0  2021-02-21 00:00:00 00:00     100     NaN
1  2021-02-21 01:00:00 00:00     200     NaN
2  2021-02-21 02:00:00 00:00     300     NaN
3  2021-02-21 03:00:00 00:00     400     NaN
4  2021-02-21 04:00:00 00:00     500     NaN
5  2021-02-21 05:00:00 00:00     600     NaN
6  2021-02-21 06:00:00 00:00     700     NaN
7  2021-02-21 07:00:00 00:00     800     NaN
8  2021-02-21 08:00:00 00:00     900     NaN
9  2021-02-21 09:00:00 00:00    1000     NaN
10 2021-02-21 10:00:00 00:00    1100     NaN
11 2021-02-21 11:00:00 00:00    1200     NaN
12 2021-02-21 12:00:00 00:00    1300     NaN
13 2021-02-21 13:00:00 00:00    1400     NaN
14 2021-02-21 14:00:00 00:00    1500     NaN
15 2021-02-21 15:00:00 00:00    1600     NaN
16 2021-02-21 16:00:00 00:00    1700     NaN
17 2021-02-21 17:00:00 00:00    1800     NaN
18 2021-02-21 18:00:00 00:00    1900     NaN
19 2021-02-21 19:00:00 00:00    2000     NaN
20 2021-02-21 20:00:00 00:00    2100     NaN
21 2021-02-21 21:00:00 00:00    2200     NaN
22 2021-02-21 22:00:00 00:00    2300     NaN
23 2021-02-21 23:00:00 00:00    2400     NaN
24 2021-02-22 00:00:00 00:00     200   150.0
25 2021-02-22 01:00:00 00:00     400   300.0
26 2021-02-22 02:00:00 00:00     600   450.0
27 2021-02-22 03:00:00 00:00     800   600.0
28 2021-02-22 04:00:00 00:00    1000   750.0
29 2021-02-22 05:00:00 00:00    1200   900.0
30 2021-02-22 06:00:00 00:00    1400  1050.0
31 2021-02-22 07:00:00 00:00    1600  1200.0
32 2021-02-22 08:00:00 00:00    1800  1350.0
33 2021-02-22 09:00:00 00:00    2000  1500.0
34 2021-02-22 10:00:00 00:00    2200  1650.0
35 2021-02-22 11:00:00 00:00    2400  1800.0
36 2021-02-22 12:00:00 00:00    2600  1950.0
37 2021-02-22 13:00:00 00:00    2800  2100.0
38 2021-02-22 14:00:00 00:00    3000  2250.0
39 2021-02-22 15:00:00 00:00    3200  2400.0
40 2021-02-22 16:00:00 00:00    3400  2550.0
41 2021-02-22 17:00:00 00:00    3600  2700.0
42 2021-02-22 18:00:00 00:00    3800  2850.0
43 2021-02-22 19:00:00 00:00    4000  3000.0
44 2021-02-22 20:00:00 00:00    4200  3150.0
45 2021-02-22 21:00:00 00:00    4400  3300.0
46 2021-02-22 22:00:00 00:00    4600  3450.0
47 2021-02-22 23:00:00 00:00    4800  3600.0

標籤：熊猫 数据框 麻木的

上一篇：在Python中比較相似的資料框和相似的列

下一篇：從文本檔案制作numpy陣列