How can I find 5 consecutive rows in pandas Dataframe where a value of a certain column is at least 0.5

user_13439436 0
Viewed 1126

I have a pandas DataFrame with 3 columns; Time (datetime object), real_generation (float) and predicted_generation (float). I've created a 4th column 'residual' (also float), the difference between real_generation and predicted_generation. I now want to detect when 5 consecutive rows have a residual that is at least 0.5. The Dataframe looks like this:

Index         Time               real_generation    predicted_generation    residual  
0     2019-01-01 10:00:00+00:00     0.0                  0.239                 0.239
1     2019-01-01 11:00:00+00:00     0.126                0.627                 0.501
2     2019-01-01 12:00:00+00:00     0.227                0.833                 0.606
3     2019-01-01 13:00:00+00:00     0.230                0.833                 0.603
4     2019-01-01 14:00:00+00:00     0.245                0.827                 0.582
5     2019-01-01 15:00:00+00:00     0.255                0.756                 0.501
6     2019-01-01 16:00:00+00:00     0.260                0.627                 0.367
7     2019-01-01 17:00:00+00:00     0.255                0.533                 0.278
8     2019-01-01 18:00:00+00:00     0.248                0.427                 0.179
9     2019-01-01 19:00:00+00:00     0.124                0.233                 0.109

I would like to create a function that finds these rows and prints the first index of each set. That would mean printing index '1', because rows 1,2,3,4 and 5 have a residual > 0.5. I have tried writing a function that iterates over all rows in my dataframe but it is very slow so I want to know if there are faster ways to do this. I thought maybe creating an extra boolean column 'residual>0.5' that is True when a residual is at least 0.5 and False when it is smaller than 0.5, but I don't really know how to work that out in Python. Does anyone have any ideas how to implement this or maybe know a function that might help? Thanks in advance!

pandas dataframe python-3.x python
2 Answers

Here is a pandas, non-iterative approach, and therefore quite efficient.

Steps:

  • Create a rolling window of 5 points and determine the minimum value.
  • If the minimum value is >= 0.5, store True, else store False.
  • All booleans are stored in a numpy.array, called idx.
  • The idx array is used as a filter on the main dataset with a value of 4 subtracted to determine the first index of the run of 5.
  • The filtered DataFrame is presented.

Sample code:

idx = (df['residual'].rolling(window=5).min() >= 0.5).to_numpy()
df.iloc[df.index[idx]-4]

Output:

Index                       Time  real_generation  predicted_generation  residual
    1  2019-01-01 11:00:00+00:00            0.126                 0.627     0.501
user_63404960

One brute-force way of doing this would be first extracting the rows that have a value >= 0.5:

df_extr = df[df['residual'] >= 0.5]

and then check whether the index of this extraction contains a consequent sequence, e.g.

def find_n_seq(ll, n):
    row_ids = []
    for i, r in enumerate(ll):
        window = list(ll[i:i+n])
        rg = list(range(r, r+n))
        if len(first) < n:
            break
        if window == rg:
            row_ids.append(r)
    return row_ids


find_n_seq(list(df_extr.index), 5)
user_119890800
Related