使用pandas或my d中的last 12记录获取过去12个月的产品

Question

我有月末的数据集。我必须将最后12个值相乘，并在“val”列中进行sve。我该怎么做

我尝试使用shift和grouper循环，但没有成功

我的代码：

filtered_df=df.copy()
filtered_df = filtered_df[filtered_df['monthly'].notnull()]

for index, row in filtered_df.iterrows():
     if index > 12:
         pre_1 = row.shift(1)
         pre_2 = row.shift(2)
         pre_3 = row.shift(3)
         pre_4 = row.shift(4)
         pre_5 = row.shift(5)
         pre_6 = row.shift(-6)
         pre_7 = row.shift(-7)
         pre_8 = row.shift(-8)
         pre_9 = row.shift(-9)
         pre_10 = row.shift(-10)
         pre_11 = row.shift(-11)
         pre_12 = row.shift(-12)

         all_vals = (pre_1['monthly'] * pre_2['monthly'] * pre_3['monthly'] * pre_4[
            'monthly'] * pre_5['monthly'] * pre_6['monthly'] * pre_7['monthly'] * pre_8[
                        'monthly'] * pre_9['monthly'] * pre_10['monthly'] * pre_11[
                        'monthly'] * pre_12['monthly'])

         row['generic_momentum'] = all_vals

但是我得到了nan值，而且它没有选择正确的列

我也尝试过这个，但不起作用：

df.tail(12).prod()

数据集

Date        monthly      val
31/01/11    0.959630357 
28/02/11    0.939530957 
31/03/11    1.024870166 
31/05/11    0.956831905 
30/06/11    1.06549785  
30/09/11    0.903054795 
31/10/11    1.027355404 
30/11/11    0.893328025 
31/01/12    1.015152156 
29/02/12    1.05621569  
30/04/12    1.116884715 
31/05/12    0.878896927 
31/07/12    0.950743984 
31/08/12    1.094999121 
31/10/12    0.94769417  
30/11/12    1.073116682 
31/12/12    0.986747164 
31/01/13    0.975354237 
28/02/13    0.888879072 
30/04/13    0.940063889 
31/05/13    1.017259688 
31/07/13    0.990201439 
30/09/13    1.018815133 
31/10/13    1.088671085 
31/12/13    1.104019842 
31/01/14    0.989041096 
28/02/14    1.017825485 
31/03/14    0.960047355 
30/04/14    1.064095477 
30/06/14    1.023850957 
31/07/14    1.08941545  
30/09/14    1.065516629 
31/10/14    0.984540626 
31/12/14    1.023386988 
28/02/15    1.150857956 
31/03/15    1.01209752  
30/04/15    1.00295515  
30/06/15    1.043231635 
31/07/15    1.042820448 
31/08/15    1.241814907 
30/09/15    1.014741935 
30/11/15    0.980878108 
31/12/15    0.995258408 
29/02/16    1.0507026   
31/03/16    1.033018209 
31/05/16    0.931798992 
30/06/16    1.032879184 
31/08/16    0.881060764 
30/09/16    1.000240668 
30/11/16    0.849364675 
31/01/17    1.075015059 
28/02/17    0.933706879 
31/03/17    1.036073194 
31/05/17    1.203092255 
30/06/17    0.956726321 
31/07/17    1.010709024 
31/08/17    1.102072394 
31/10/17    0.99223153  
30/11/17    1.088148242 
31/01/18    0.982730721 
28/02/18    1.102215081 

Answer 1

IIUC：使用pd.Series.rolling和np.prod的组合

df['monthly val'].rolling(12).apply(np.prod)

0          NaN
1          NaN
2          NaN
3          NaN
4          NaN
5          NaN
6          NaN
7          NaN
8          NaN
9          NaN
10         NaN
11    0.821766
12    0.814156
13    0.948878
14    0.877424
15    0.984058
16    0.911327
17    0.984289
....

另一种方法是使用cumprod和shift

df['monthly val'].cumprod().pipe(lambda s: s / s.shift(12))