滚动组展望未来

Question

我想在接下来的10个交易日找到最高的股票价格。

    Date    Symbol  Average Expected Value
3505    03-01-2000  ASIANPAINT  38.054  42.24
3506    04-01-2000  ASIANPAINT  38.35   42.24
3507    05-01-2000  ASIANPAINT  37.981  42.24
3508    06-01-2000  ASIANPAINT  37.988  42.24
3509    07-01-2000  ASIANPAINT  38.338  42.98
3510    10-01-2000  ASIANPAINT  41.497  
3511    11-01-2000  ASIANPAINT  41.251  
3512    12-01-2000  ASIANPAINT  42.146  
3513    13-01-2000  ASIANPAINT  42.24   
3514    14-01-2000  ASIANPAINT  41.005  
3515    17-01-2000  ASIANPAINT  41.372  
3516    18-01-2000  ASIANPAINT  41.376  
3517    19-01-2000  ASIANPAINT  42.193  
3518    20-01-2000  ASIANPAINT  41.878  
3519    21-01-2000  ASIANPAINT  42.977  
8994    03-01-2000  AXISBANK    5.34    5.448
8995    04-01-2000  AXISBANK    5.448   5.254
8996    05-01-2000  AXISBANK    5.248   5.254
8997    06-01-2000  AXISBANK    5.254   5.112
8998    07-01-2000  AXISBANK    5.008   5.112
8999    10-01-2000  AXISBANK    5.058   
9000    11-01-2000  AXISBANK    4.78    
9001    12-01-2000  AXISBANK    4.794   
9002    13-01-2000  AXISBANK    4.754   
9003    14-01-2000  AXISBANK    4.634   
9004    17-01-2000  AXISBANK    5.018   
9005    18-01-2000  AXISBANK    4.988   
9006    19-01-2000  AXISBANK    5.112   
9007    20-01-2000  AXISBANK    5.01    
9008    21-01-2000  AXISBANK    4.958   

csv格式的数据

Date,Symbol,Average,Expected Value
03-01-2000,ASIANPAINT,38.054,42.24
04-01-2000,ASIANPAINT,38.35,42.24
05-01-2000,ASIANPAINT,37.981,42.24
06-01-2000,ASIANPAINT,37.988,42.24
07-01-2000,ASIANPAINT,38.338,42.98
10-01-2000,ASIANPAINT,41.497,
11-01-2000,ASIANPAINT,41.251,
12-01-2000,ASIANPAINT,42.146,
13-01-2000,ASIANPAINT,42.24,
14-01-2000,ASIANPAINT,41.005,
17-01-2000,ASIANPAINT,41.372,
18-01-2000,ASIANPAINT,41.376,
19-01-2000,ASIANPAINT,42.193,
20-01-2000,ASIANPAINT,41.878,
21-01-2000,ASIANPAINT,42.977,
03-01-2000,AXISBANK,5.34,5.448
04-01-2000,AXISBANK,5.448,5.254
05-01-2000,AXISBANK,5.248,5.254
06-01-2000,AXISBANK,5.254,5.112
07-01-2000,AXISBANK,5.008,5.112
10-01-2000,AXISBANK,5.058,
11-01-2000,AXISBANK,4.78,
12-01-2000,AXISBANK,4.794,
13-01-2000,AXISBANK,4.754,
14-01-2000,AXISBANK,4.634,
17-01-2000,AXISBANK,5.018,
18-01-2000,AXISBANK,4.988,
19-01-2000,AXISBANK,5.112,
20-01-2000,AXISBANK,5.01,
21-01-2000,AXISBANK,4.958,

我已经试过了

all_scripts_df.Average[::-1].rolling(window=10).max()[::-1][:15]

但这一方法也将考虑当前的交易时段。同时分组也会比较困难。

我尝试过的其他方法是

all_scripts_df.groupby('Symbol').rolling(10, min_periods=1)['Average'].max().reset_index().head(10)

但这种方法着眼于价值，而不是向前看。

Answer 1

使用双groupby

df['Value'] = df.groupby('Symbol').rolling(10, min_periods=1)['Average'].max() \
                .groupby(level=0).shift(-10).droplevel(0)

输出：

>>> df
           Date      Symbol  Average   Value
3505 2000-03-01  ASIANPAINT   38.054  42.240
3506 2000-04-01  ASIANPAINT   38.350  42.240
3507 2000-05-01  ASIANPAINT   37.981  42.240
3508 2000-06-01  ASIANPAINT   37.988  42.240
3509 2000-07-01  ASIANPAINT   38.338  42.977
3510 2000-10-01  ASIANPAINT   41.497     NaN
3511 2000-11-01  ASIANPAINT   41.251     NaN
3512 2000-12-01  ASIANPAINT   42.146     NaN
3513 2000-01-13  ASIANPAINT   42.240     NaN
3514 2000-01-14  ASIANPAINT   41.005     NaN
3515 2000-01-17  ASIANPAINT   41.372     NaN
3516 2000-01-18  ASIANPAINT   41.376     NaN
3517 2000-01-19  ASIANPAINT   42.193     NaN
3518 2000-01-20  ASIANPAINT   41.878     NaN
3519 2000-01-21  ASIANPAINT   42.977     NaN
8994 2000-03-01    AXISBANK    5.340   5.448
8995 2000-04-01    AXISBANK    5.448   5.254
8996 2000-05-01    AXISBANK    5.248   5.254
8997 2000-06-01    AXISBANK    5.254   5.112
8998 2000-07-01    AXISBANK    5.008   5.112
8999 2000-10-01    AXISBANK    5.058     NaN
9000 2000-11-01    AXISBANK    4.780     NaN
9001 2000-12-01    AXISBANK    4.794     NaN
9002 2000-01-13    AXISBANK    4.754     NaN
9003 2000-01-14    AXISBANK    4.634     NaN
9004 2000-01-17    AXISBANK    5.018     NaN
9005 2000-01-18    AXISBANK    4.988     NaN
9006 2000-01-19    AXISBANK    5.112     NaN
9007 2000-01-20    AXISBANK    5.010     NaN
9008 2000-01-21    AXISBANK    4.958     NaN

Answer 2

使用GroupBy.transform结合lambda函数避免双重groupby

df['Value'] = (df.groupby('Symbol')['Average']
                 .transform(lambda x: x.rolling(10, min_periods=1).max().shift(-10)))