Pandas: Find max in rolling window and return sum of another column for the row of the max and proceeding four rows

user_7472084 0
Viewed 590

I have a dataframe with two columns. I want to find the rolling 5 period max of the first column and calculate the sum of values and a second column for the row of the rolling max and the the preceding 4 rows.

Below is an example of the desired output with Col1 and Col2 as mentioned above, a third column of the rolling max and a final column of the desired result of the calculation

Here is code to set the first three columns:

data ={'Col1': [4,2,3,4,5,6,5,4,3,2,1,4,3,2,1],
  'Col2' :[10,20,10,15,10,20,10,15,10,20,10,15,10,20,10]}
df = pd.DataFrame(data) 
df['Col1_Rolling5_Max'] = df['Col1'].rolling(5).max()
df

Here is an example of the desired output:

enter image description here

rolling-computation pandas dataframe python
1 Answers

You can only calculate the rolling sum on rows where the next Rolling5_Max is different, then forward fill to populate the rest.

import pandas as pd
import numpy as np

data ={'Col1': [4,2,3,4,5,6,5,4,3,2,1,4,3,2,1],
  'Col2' :[10,20,10,15,10,20,10,15,10,20,10,15,10,20,10]}
df = pd.DataFrame(data) 
df['Col1_Rolling5_Max'] = df['Col1'].rolling(5).max()


df['flag'] = df['Col1_Rolling5_Max'].ne(df['Col1_Rolling5_Max'].shift())
df['Sum_5_Col1_Before_Max']= np.where(df['flag']==True,df['Col2'].rolling(5).sum(),np.nan)
df.ffill(inplace=True)
df[['Col1','Col2','Col1_Rolling5_Max','Sum_5_Col1_Before_Max']]

Output

    Col1  Col2  Col1_Rolling5_Max  Sum_5_Col1_Before_Max
0      4    10                NaN                    NaN
1      2    20                NaN                    NaN
2      3    10                NaN                    NaN
3      4    15                NaN                    NaN
4      5    10                5.0                   65.0
5      6    20                6.0                   75.0
6      5    10                6.0                   75.0
7      4    15                6.0                   75.0
8      3    10                6.0                   75.0
9      2    20                6.0                   75.0
10     1    10                5.0                   65.0
11     4    15                4.0                   70.0
12     3    10                4.0                   70.0
13     2    20                4.0                   70.0
14     1    10                4.0                   70.0
user_47183500
Related