Assuming I have a dataframe like this:
# importing pandas and numpy
import pandas as pd
import numpy as np
# create a sample dataframe
data = pd.DataFrame({
'A' : [ 1, 2, 3, -1, -2, 3, 5, 15, -1, -12, 11, -2, -3, 12, 22],
'B' : [ -1, -2, -3, 12, -12, -3, 5, 23, -1, 11, -3, -1, 4, 12, 23]
})
Such that the data looks like:
A B
0 1 -1
1 2 -2
2 3 -3
3 -1 12
4 -2 -12
5 3 -3
6 5 5
7 15 23
8 -1 -1
9 -12 11
10 11 -3
11 -2 -1
12 -3 4
13 12 12
14 22 23
What if I want to create a column that find the Min values of the next 7 rows, such that it looks like this:
A B Min A
0 1 -1 -2
1 2 -2 -2
2 3 -3 -2
3 -1 12 -12
4 -2 -12 -12
5 3 -3 -12
6 5 5 -12
7 15 23 -12
8 -1 -1 -12
9 -12 11 N/A
10 11 -3 N/A
11 -2 -1 N/A
12 -3 4 N/A
13 12 12 N/A
14 22 23 N/A
In this example we find the min value in column A, row 0 to 6, row 1 to 7, and so on, and populate it in the 'Min A' column and so on.
Is there a way to do this for Min and Max values for both A and B such that there are now 4 additional columns i.e. Min A, Max A, Min B, Max B?
Appreciate all of you for the help.
Thank you!
CodePudding user response：
You can do rolling on reversed data:
for col in ['A','B']:
data[f'Min {col}'] = data[col].iloc[::-1].rolling(7).min()
data[f'Max {col}'] = data[col].iloc[::-1].rolling(7).max()
Outpu:
A B Min A Max A Min B Max B
0 1 -1 -2.0 5.0 -12.0 12.0
1 2 -2 -2.0 15.0 -12.0 23.0
2 3 -3 -2.0 15.0 -12.0 23.0
3 -1 12 -12.0 15.0 -12.0 23.0
4 -2 -12 -12.0 15.0 -12.0 23.0
5 3 -3 -12.0 15.0 -3.0 23.0
6 5 5 -12.0 15.0 -3.0 23.0
7 15 23 -12.0 15.0 -3.0 23.0
8 -1 -1 -12.0 22.0 -3.0 23.0
9 -12 11 NaN NaN NaN NaN
10 11 -3 NaN NaN NaN NaN
11 -2 -1 NaN NaN NaN NaN
12 -3 4 NaN NaN NaN NaN
13 12 12 NaN NaN NaN NaN
14 22 23 NaN NaN NaN NaN
You can also roll on the dataframe like:
data[['A','B']].iloc[::-1].rolling(7).agg(['min','max'])
which gives a double-leveled-column dataframe (notice the reversed index):
A B
min max min max
14 NaN NaN NaN NaN
13 NaN NaN NaN NaN
12 NaN NaN NaN NaN
11 NaN NaN NaN NaN
10 NaN NaN NaN NaN
9 NaN NaN NaN NaN
8 -12.0 22.0 -3.0 23.0
7 -12.0 15.0 -3.0 23.0
6 -12.0 15.0 -3.0 23.0
5 -12.0 15.0 -3.0 23.0
4 -12.0 15.0 -12.0 23.0
3 -12.0 15.0 -12.0 23.0
2 -2.0 15.0 -12.0 23.0
1 -2.0 15.0 -12.0 23.0
0 -2.0 5.0 -12.0 12.0
CodePudding user response：
You could shift it by n-1
n = 7
data.rolling(n, closed="right").min().shift(-(n-1))
# OR
pd.concat([data, data.rolling(n, closed="right").agg(["min", "max"]).shift(-(n-1))], axis=1)