Identify 10 minute windows that have more than N rows

user_9530105 0
Viewed 54

I have a pandas dataframe with one column. I would like to run through the column and identify where there are 6 consecutive rows in a 10 minute window. Or just a count of how many times there were 6 consecutive rows in a 10 minute window.

I converted from string to time:

df.iloc[0]

Time   07:19:14
Name: 0, dtype: timedelta64[ns]

The dataframe:

df
    Time
0   07:19:14
1   07:27:39
2   07:29:14
3   07:29:59
4   09:23:49
5   09:26:44
6   09:26:49
7   09:29:04
8   09:30:14
9   09:35:09
10  09:35:29
11  09:38:09
12  09:40:49
13  09:46:19
14  09:56:04
15  10:08:24
16  10:09:54
17  10:24:14
18  10:45:29
19  10:55:54
20  10:58:34
21  11:00:35
22  11:02:29
23  11:04:04
24  11:04:04
25  11:26:34
26  11:34:00
27  11:52:04
28  11:55:15
29  11:56:19
... ...
62  15:00:54
63  15:01:24
64  15:03:09
65  15:05:04
66  15:05:29
67  15:07:09
68  15:11:24

So it would identify the following from the above:

62  15:00:54
63  15:01:24
64  15:03:09
65  15:05:04
66  15:05:29
67  15:07:09
pandas-groupby pandas dataframe group-by python
1 Answers

Start by converting the "Time" column to a Timedelta column, then use groupby + ngroup to label groups, find the size of each group, and filter.

df.Time = pd.TimedeltaIndex(df.Time.astype(str))

v = df.groupby(pd.Grouper(key='Time', freq='10min')).ngroup()
df[v.groupby(v).transform('size').ge(6)]

       Time
62 15:00:54
63 15:01:24
64 15:03:09
65 15:05:04
66 15:05:29
67 15:07:09
user_49090870
Related