熊猫:连续零值

Question

我有一个像这样的数据框架

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

我希望按连续值(在本例中为0)分组，并具有如下所示的输出

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

Answer 1

您可以使用.groupby()检查相邻值是否发生变化(即df["Speed"] != df["Speed"].shift())，然后检查每个块中的速度是否为0。也许有一种更好的方法来重新组装最终的DataFrame，但是我只是把结果放到一个列表中，并在最后重新组装它。

您的表在pd.read_clipboard()中读得不太好，所以我只知道时间，但是它应该与实际数据一样工作。

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

Answer 2

这里有一个非循环实现

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