CODEWITHSUNDEEP
pythonpandasnumpydataframe
gibbz00
gibbz00

Reputation: 1987

Apply function on a rolling basis within groupby in pandas

I have a dataframe that looks like the following. 

           symbol    Range             
Date                                      
2018-08-16     spy    1.5
2018-08-17     spy    1.2
2018-08-16     spy    1.3
2018-08-17     spy    1.6
2017-07-17     spy    1.1
2017-07-18     spy    1.9
2018-08-16     nflx   4.5
2018-08-17     nflx   5.2

I have added a column that finds the 15th percentile of Range by doing the following:

df['Range_quantile'] = df.groupby(['symbol'])['Range'].transform(lambda x: np.percentile(x.unique(), 15))

As of a given row, how do I apply the same function only to the last 20 rows(within the group) on a rolling basis grouped by symbol and then add back the output as a column(Range_quantile_rolling) to the dataframe? My example applies the lambda x: np.percentile(x.unique(), 15) function to the whole Range column.

For example, if I am adding the function in the last 3 rows within groupby, it might look like this:

         symbol    Range     Range_Quantile_Rolling_3        
Date                                      
2018-08-16     spy    1.5      NA
2018-08-17     spy    1.2      NA
2018-08-16     spy    1.3      1.21
2018-08-17     spy    1.6      1.25
2017-07-17     spy    1.1      1.15
2017-07-18     spy    1.9      1.3
2018-08-16     nflx   4.5      NA
2018-08-17     nflx   5.2      NA

Upvotes: 1

Views: 408

Answers (1)

piRSquared
piRSquared

Reputation: 294218

groupby and transform with a lambda

df.assign(Range=df.groupby('symbol').Range.transform(
    lambda x: x.rolling(3).apply(lambda y: np.percentile(np.unique(y), 15))
))

           symbol  Range
Date                    
2018-08-16    spy    NaN
2018-08-17    spy    NaN
2018-08-16    spy   1.23
2018-08-17    spy   1.23
2017-07-17    spy   1.16
2017-07-18    spy   1.25
2018-08-16   nflx    NaN
2018-08-17   nflx    NaN

Upvotes: 2

Related Questions