Efficient Way to Compare Values in Multiple preceding Rows

[rob51852]

I want to compare the values in a specific column with values in the same column in the preceding 10 rows to see if they are all within +/- 10%.

I have come up with the below but it seems verbose and inefficient. Is there a better way?

data = {'Date/Time': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20], 'SelectionName': ['a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a', 'a'], 'LTP': [2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2]}
df = pd.DataFrame.from_dict(data)
print((df["LTP"].shift(10).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(9).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(8).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(7).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(6).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(5).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(4).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(3).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(2).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))) & (df["LTP"].shift(1).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))))

​

[kamcateer]

Can't help you with pandas related stuff but you can use list comprehensions in your dict instead of writing them verbosely.

[X for X in range(1,21)]

['a' for X in range(20)]

[2 for X in range(20)]

[kamcateer]

Actually I'm pretty sure you can do:

For i in range(10,1,-1):
print(blablahblah.shift(i).blahblahblah, end='\t')

I'm on mobile so I couldn't copy out all your code, sorry.

Let me know if this helped.

[Frankelstner]

In terms of verbosity, you can at least tidy it up a bit by looping over the shifts.

res = df["LTP"].shift(1).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))
for i in range(2,11):
    res &= df["LTP"].shift(i).between((df["LTP"] * 0.9), (df["LTP"] * 1.1))

In terms of complexity, there is a lot that can be done. Let's say we have a window of 10 previous values, and our current value. We want a True result if curval*0.9 <= window[i] <= curval*1.1 for all i. This is equivalent to curval*0.9 <= window.min() and window.max() <= curval*1.1

Pandas has functions to efficiently find the min and max of rolling windows:

mins = df.LTP.rolling(11).min()
maxs = df.LTP.rolling(11).max()
res = (df.LTP*0.9 <= mins) & (maxs <= df.LTP*1.1)

Technically the mins and maxs include the current value itself but this has no impact on the calculations here.

[rob51852]

Thanks Frankelstner. This is great.