CODEWITHSUNDEEP
pandas
Amit
Amit

Reputation: 55

Pandas: Consecutive values of zero

I have a data frame which looks like this

Timestamp                     Speed
2014-10-10 00:10:10            112
2014-10-10 00:10:13            34
2014-10-10 00:10:17            0
2014-10-10 00:10:20            0
2014-10-10 00:10:45            0
2014-10-10 00:10:56            3
2014-10-10 00:11:06            0
2014-10-10 00:11:09            0
2014-10-10 00:11:14            11

I want to group by consecutive values (0 in this case) and have output like

start_time              end_time               number
2014-10-10 00:10:17    2014-10-10 00:10:45     3
2014-10-10 00:11:06    2014-10-10 00:11:09     2

Upvotes: 1

Views: 761

Answers (2)

Ted Petrou
Ted Petrou

Reputation: 61947

Here's a non-loop implementation

s = (((df['speed'] == 0) & (df['speed'].shift(1) == 0)) | ((df['speed'] == 0) & (df['speed'].shift(-1) == 0)) ) * 1
s1 = s.diff()
group_labels = s1[s1 == 1].cumsum()
s_nan = s.replace(1, np.nan)
df_copy = df.copy()
df_copy['label'] = s_nan.combine_first(group_labels).fillna(method='ffill').replace(0, np.nan)
df_copy = df_copy.groupby('label')['timestamp'].agg({'start_time':'first', 'end_time':'last', 'number':'size'})
df_copy = df_copy[['start_time', 'end_time', 'number']].reset_index(drop=True)

df_copy

            start_time             end_time  number
0  2014-10-10 00:10:17  2014-10-10 00:10:45       3
1  2014-10-10 00:11:06  2014-10-10 00:11:09       2

Upvotes: 0

Randy
Randy

Reputation: 14849

You can use a .groupby() to check whether adjacent values change (i.e., df["Speed"] != df["Speed"].shift()) and then check whether the speed in each of those blocks is 0 or not. There might be a better way to reassemble the final DataFrame, but I just threw the results into a list and reassembled it at the end.

Your table didn't read nicely with pd.read_clipboard() and so I've only got the times, but it should work the same with your real data. 

In [113]: df
Out[113]:
           Speed
Timestamp
00:10:10     112
00:10:13      34
00:10:17       0
00:10:20       0
00:10:45       0
00:10:56       3
00:11:06       0
00:11:09       0
00:11:14      11

In [114]: l = []

In [115]: for k, v in df.groupby((df["Speed"] != df["Speed"].shift()).cumsum()):
     ...:     if v["Speed"].iloc[0] == 0:
     ...:         l.append({'start_time': v.index.min(), 'end_time': v.index.max(), 'number': len(v)})
     ...: pd.DataFrame(l, columns=['start_time', 'end_time', 'number'])
     ...:
Out[115]:
  start_time  end_time  number
0   00:10:17  00:10:45       3
1   00:11:06  00:11:09       2

Upvotes: 1

Related Questions