Count consecutive rows above limit-CodePudding

I have a dataframe consisting of a name and speed measurements for each 0.1 seconds. I want to get a count of how many times the speed is above the speed limit for 0.3 seconds (i.e. 3 consecutive rows).

limit = 25.3

Name   speed   time
Mary   25.6    0.1
Mary   25.8    0.2
Mary   25.1    0.3
Mary   24.4    0.4
Mary   24.2    0.5
Mary   25.8    0.6
Mary   25.9    0.7
Mary   25.8    0.8
Mary   25.4    0.9
Mary   24.9    1.0
Mary   25.6    1.1
Mary   25.8    1.2
Mary   26.2    1.3

Result

{Mary: 2}

CodePudding user response：

First idea is create groups by consecutive values and aggregate counts per grsoups and Name, filter if greater or equal like limit and again counts by Names:

limit = 25.3

m = df['speed'].gt(limit)
s = df.groupby([(~m).shift().bfill()[m].cumsum(),'Name']).size()
s = s[s.ge(3)].groupby(level=1).size()
print (s)
Mary    2
dtype: int64

Or create counter of consecutive values, filter for equal by 3 and count by Series.value_counts:

limit = 25.3

m = df['speed'].gt(limit)

#https://stackoverflow.com/a/52718619/2901002
b = m.cumsum()
c = b.sub(b.mask(m).ffill().fillna(0)).astype(int)

df['count'] = c
print (df)

s = df.loc[df['count'].eq(3), 'Name'].value_counts()
print (s)
Mary    2
Name: Name, dtype: int64