CODEWITHSUNDEEP
pythonpandas
Bhavesh Ghodasara
Bhavesh Ghodasara

Reputation: 2071

Group by Rolling max look forward

I want to find highest price of stock in next 10 trading sessions.

    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

Data in csv format

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,

I have tried so far

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

But this approach will consider current trading session as well. also group by will be little difficult.

Other approach I tried is

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

But this approach looks back value and not look forward.

Upvotes: 1

Views: 53

Answers (2)

jezrael
jezrael

Reputation: 863246

Use GroupBy.transform with lambda function for avoid double groupby:

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

Upvotes: 1

Corralien
Corralien

Reputation: 120479

Use a double groupby:

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

Output:

>>> 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

Upvotes: 1

Related Questions