Select rows where 3 consecutive values match condition - Python, Pandas

Question

I have a dataframe like:

   values
0   45
1   47
2   58
3   40
4   45
5   40
6   50
7   55
8   60
9   60
10  20
...

I would like to obtain a dataframe containing only rows where 3 consecutive values are greater than a specific number, let's say greater than 44. The resultin df would be:

  values
0   45
1   47
2   58
6   50
7   55
8   60
9   60
...

Please note that value=45 in index=3 has been excluded because there are no 3 consecutive values greater than 44. Thank you!

Answer 1

Use:

A = 44
B = 3

m = df['values'].gt(A)
s = (~m).cumsum()[m]
df1 = df[s.map(s.value_counts()).ge(B).reindex(df.index, fill_value=False)]
print (df1)
   values
0      45
1      47
2      58
6      50
7      55
8      60
9      60

Explanation/details:

First compare by Series.gt for greater:

print (df['values'].gt(A))
0      True
1      True
2      True
3     False
4      True
5     False
6      True
7      True
8      True
9      True
10    False
Name: values, dtype: bool

Then create groups by Series.cumsum with inverted mask by ~:

print ((~m).cumsum())
0     0
1     0
2     0
3     1
4     1
5     2
6     2
7     2
8     2
9     2
10    3
Name: values, dtype: int32

Filter mask only by greater values with m by boolean indexing:

print ((~m).cumsum()[m])
0    0
1    0
2    0
4    1
6    2
7    2
8    2
9    2
Name: values, dtype: int32

Compare by second value by Series.ge for greater od equal:

print (s.map(s.value_counts()).ge(B))
0     True
1     True
2     True
4    False
6     True
7     True
8     True
9     True
Name: values, dtype: bool

Last add filter out rows by Series.reindex, so possible filter by boolean indexing:

print (s.map(s.value_counts()).ge(B).reindex(df.index, fill_value=False))
0      True
1      True
2      True
3     False
4     False
5     False
6      True
7      True
8      True
9      True
10    False
Name: values, dtype: bool