I have some data in a Pandas DataFrame:
JavaScript
x
9
1
Price
2
1 $10
3
2 $11
4
3 $15
5
4 $18
6
5 $13
7
6 $4
8
7 $25
9
and I am trying to get the offset for the maximum of the next N
rows. For example, when ****, the output would look like
JavaScript
1
9
1
Price offset
2
1 $10 2 <- offset is defined as the row offset of the maximum for the next two (N) values ($11 and $15)
3
2 $11 2 <- similarly, the offset is here is the row offset of the maximum for the next two (N) values ($15 and $18)
4
3 $15 1
5
4 $18 1
6
5 $13 2
7
6 $4 1
8
7 $25 0
9
I can get the value of the maximum over the next N
rows using:
JavaScript
1
3
1
# Here, N=12
2
df['max_price'] = df['Price'].rolling(12).max().shift(-11)
3
However, is it possible to get the row offset position for the maximum value of the next N rows using similar logic? Any pointers would be very helpful.
Advertisement
Answer
You can use rolling
, but you need to cheat a bit by rolling on the reversed series.
JavaScript
1
11
11
1
s = df['Price'].str.strip('$').astype(int)
2
3
N = 2
4
df['offset'] = (s.iloc[::-1]
5
.rolling(N, min_periods=1)
6
.apply(lambda s: s.idxmax())
7
.shift()
8
.iloc[::-1]
9
-df.index
10
).fillna(0, downcast='infer')
11
Output:
JavaScript
1
9
1
Price offset
2
1 $10 2
3
2 $11 2
4
3 $15 1
5
4 $18 1
6
5 $13 2
7
6 $4 1
8
7 $25 0
9