Update: How to compare values in 3 consecutive rows in pandas dataframe?

Question

I am looking for a solution which would compare values in 3 consecutive rows of data and update column if condition is true.

import pandas as pd aapl = pd.read_csv(....)
aapl['3lows'] = False
aapl.head(10)

and the output is the table where for each row there are columns with

Row number/ Date / Open / High / Low / Close / Adj Close / Volume / 3lows
0 / 2006-01-03 / 10.340000 / 10.678572 / 10.321428 / 10.678572 / 9.572629 / 201808600 / False

Now I want to run some "script" to update column 3lows to true if value in column Low from row that is being updated e.g. 100 is lower than from row 99, and from 99 lower than from 98 and from 98 lower than 97.

Answer 1

IIUC:

Let use something like this:

#where is is the s = appl['Low']; let's make up some data
s = pd.Series([100,99,98,97,99,100,99,95,94,93,92,100,95])

s.diff().rolling(3).max().lt(0)

Returns:

0     False
1     False
2     False
3      True
4     False
5     False
6     False
7     False
8      True
9      True
10     True
11    False
12    False
dtype: bool

Details:

s

Output:

0     100
1      99
2      98
3      97
4      99
5     100
6      99
7      95
8      94
9      93
10     92
11    100
12     95
dtype: int64

Compare each value to previous using diff:

s.diff()

Output:

0     NaN
1    -1.0
2    -1.0
3    -1.0
4     2.0
5     1.0
6    -1.0
7    -4.0
8    -1.0
9    -1.0
10   -1.0
11    8.0
12   -5.0
dtype: float64

Now, let's look at a rolling windows of 3 values if the max is less than zero then you have three declines in a value:

s.diff().rolling(3).max().lt(0)

Output:

0     False
1     False
2     False
3      True
4     False
5     False
6     False
7     False
8      True
9      True
10     True
11    False
12    False
dtype: bool

Now, let's compare our result to the original data:

print(pd.concat([s,s.diff().rolling(3).max().lt(0)], axis=1))

      0      1
0   100  False
1    99  False
2    98  False
3    97   True
4    99  False
5   100  False
6    99  False
7    95  False
8    94   True
9    93   True
10   92   True
11  100  False
12   95  False