I am looking to do a "rolling" .max() .min() of B column "groupedby" date(column A values). However, trick is it should start on every row again so i can not use for example anything like df['MAX'] = df['B'].rolling(10).max().shift(-9) (couse i need to end it where group ends - every group can have different number of rows) or simply groupby A column (becouse i need that rolling max min with start on each row and end where each group ends - which means for row 1 column C is max of rows 1-4 in column B, for row 2 column C is max of rows 2-4 from column B, for row 3 column C is max of rows 3-4 from column B, for row 4 column C is max of row 4 from column B etc etc..). Hope it make sence - columns C and D are desired results. Thank you all in advance.
A B C(max) D(min)
1 2016-01-01 0 7 0
2 2016-01-01 7 7 3
3 2016-01-01 3 4 3
4 2016-01-01 4 4 4
5 2016-01-02 2 5 1
6 2016-01-02 5 5 1
7 2016-01-02 1 1 1
8 2016-01-03 1 4 1
9 2016-01-03 3 4 2
10 2016-01-03 4 4 2
11 2016-01-03 2 2 2
CodePudding user response:
df['C_max'] = df.groupby('A')['B'].transform(lambda x: x[::-1].cummax()[::-1])
df['D_min'] = df.groupby('A')['B'].transform(lambda x: x[::-1].cummin()[::-1])
A B C(max) D(min) C_max D_min
1 2016-01-01 0 7 0 7 0
2 2016-01-01 7 7 3 7 3
3 2016-01-01 3 4 3 4 3
4 2016-01-01 4 4 4 4 4
5 2016-01-02 2 5 1 5 1
6 2016-01-02 5 5 1 5 1
7 2016-01-02 1 1 1 1 1
8 2016-01-03 1 4 1 4 1
9 2016-01-03 3 4 2 4 2
10 2016-01-03 4 4 2 4 2
11 2016-01-03 2 2 2 2 2