I've got a pandas dataframe with timeseries data describing the vehicle speed. I would like to sum the delta times during a vehicle has a speed of zero. But I am not able to sum these values.
This is the structure of the dataframe:
time value id delta
211 2022-11-18 04:09:30+01:00 0.000000 215071 NaN
212 2022-11-18 04:09:35+01:00 0.000000 215071 5.0
213 2022-11-18 04:09:40+01:00 0.000000 215071 5.0
214 2022-11-18 04:09:45+01:00 0.000000 215071 5.0
215 2022-11-18 04:09:50+01:00 0.000000 215071 5.0
216 2022-11-18 04:09:55+01:00 0.000000 215071 5.0
217 2022-11-18 04:10:00+01:00 0.000000 215071 5.0
218 2022-11-18 04:10:05+01:00 0.000000 215071 5.0
219 2022-11-18 04:10:10+01:00 0.000000 215071 5.0
220 2022-11-18 04:10:15+01:00 0.000000 215071 5.0
221 2022-11-18 04:10:20+01:00 0.000000 215071 5.0
222 2022-11-18 04:10:25+01:00 0.000000 215071 5.0
223 2022-11-18 04:10:30+01:00 0.000000 215071 5.0
224 2022-11-18 04:10:35+01:00 0.000000 215071 5.0
225 2022-11-18 04:10:40+01:00 6.652344 215071 5.0
226 2022-11-18 04:10:45+01:00 10.347656 215071 5.0
227 2022-11-18 04:10:50+01:00 19.585938 215071 5.0
228 2022-11-18 04:10:55+01:00 26.968750 215071 5.0
229 2022-11-18 04:11:00+01:00 44.253906 215071 5.0
230 2022-11-18 04:11:05+01:00 55.722656 215071 5.0
231 2022-11-18 04:11:10+01:00 50.984375 215071 5.0
232 2022-11-18 04:11:15+01:00 50.121094 215071 5.0
233 2022-11-18 04:11:20+01:00 61.324219 215071 5.0
234 2022-11-18 04:11:25+01:00 65.597656 215071 5.0
235 2022-11-18 04:11:30+01:00 66.691406 215071 5.0
236 2022-11-18 04:11:35+01:00 53.773438 215071 5.0
237 2022-11-18 04:11:40+01:00 55.796875 215071 5.0
238 2022-11-18 04:11:45+01:00 63.898438 215071 5.0
239 2022-11-18 04:11:50+01:00 64.484375 215071 5.0
240 2022-11-18 04:11:55+01:00 55.804688 215071 5.0
241 2022-11-18 04:12:00+01:00 37.117188 215071 5.0
242 2022-11-18 04:12:05+01:00 37.210938 215071 5.0
243 2022-11-18 04:12:10+01:00 25.179688 215071 5.0
244 2022-11-18 04:12:15+01:00 37.738281 215071 5.0
245 2022-11-18 04:12:20+01:00 54.781250 215071 5.0
246 2022-11-18 04:12:25+01:00 58.066406 215071 5.0
247 2022-11-18 04:12:30+01:00 59.097656 215071 5.0
248 2022-11-18 04:12:35+01:00 59.839844 215071 5.0
249 2022-11-18 04:12:41+01:00 52.769531 215071 6.0
250 2022-11-18 04:12:46+01:00 42.121094 215071 5.0
251 2022-11-18 04:12:51+01:00 48.792969 215071 5.0
252 2022-11-18 04:12:56+01:00 28.902344 215071 5.0
253 2022-11-18 04:13:01+01:00 38.085938 215071 5.0
254 2022-11-18 04:13:06+01:00 48.625000 215071 5.0
255 2022-11-18 04:13:11+01:00 33.242188 215071 5.0
256 2022-11-18 04:13:16+01:00 38.894531 215071 5.0
257 2022-11-18 04:13:21+01:00 41.777344 215071 5.0
258 2022-11-18 04:13:26+01:00 30.464844 215071 5.0
259 2022-11-18 04:13:31+01:00 10.472656 215071 5.0
260 2022-11-18 04:13:36+01:00 7.535156 215071 5.0
261 2022-11-18 04:13:41+01:00 0.000000 215071 5.0
262 2022-11-18 04:13:46+01:00 0.000000 215071 5.0
263 2022-11-18 04:13:51+01:00 0.000000 215071 5.0
264 2022-11-18 04:13:56+01:00 0.000000 215071 5.0
265 2022-11-18 04:14:01+01:00 0.000000 215071 5.0
266 2022-11-18 04:14:06+01:00 0.000000 215071 5.0
267 2022-11-18 04:14:11+01:00 0.000000 215071 5.0
268 2022-11-18 04:14:16+01:00 0.000000 215071 5.0
269 2022-11-18 04:14:21+01:00 0.000000 215071 5.0
270 2022-11-18 04:14:26+01:00 0.000000 215071 5.0
271 2022-11-18 04:14:31+01:00 1.812500 215071 5.0
272 2022-11-18 04:14:36+01:00 1.636719 215071 5.0
273 2022-11-18 04:14:41+01:00 0.000000 215071 5.0
274 2022-11-18 04:14:46+01:00 0.000000 215071 5.0
275 2022-11-18 04:14:51+01:00 0.000000 215071 5.0
276 2022-11-18 04:14:56+01:00 0.000000 215071 5.0
277 2022-11-18 04:15:01+01:00 0.000000 215071 5.0
278 2022-11-18 04:15:06+01:00 0.000000 215071 5.0
279 2022-11-18 04:15:11+01:00 3.125000 215071 5.0
280 2022-11-18 04:15:16+01:00 2.554688 215071 5.0
281 2022-11-18 04:15:21+01:00 3.750000 215071 5.0
282 2022-11-18 04:15:26+01:00 1.613281 215071 5.0
283 2022-11-18 04:15:31+01:00 2.699219 215071 5.0
284 2022-11-18 04:15:36+01:00 1.222656 215071 5.0
285 2022-11-18 04:15:41+01:00 1.312500 215071 5.0
286 2022-11-18 04:15:46+01:00 2.039062 215071 5.0
287 2022-11-18 04:15:51+01:00 0.000000 215071 5.0
288 2022-11-18 04:15:56+01:00 2.183594 215071 5.0
289 2022-11-18 04:16:01+01:00 4.058594 215071 5.0
290 2022-11-18 04:16:06+01:00 2.753906 215071 5.0
291 2022-11-18 04:16:11+01:00 3.820312 215071 5.0
292 2022-11-18 04:16:16+01:00 1.792969 215071 5.0
293 2022-11-18 04:16:21+01:00 1.218750 215071 5.0
294 2022-11-18 04:16:26+01:00 0.000000 215071 5.0
295 2022-11-18 04:16:31+01:00 0.000000 215071 5.0
296 2022-11-18 04:16:36+01:00 0.000000 215071 5.0
297 2022-11-18 04:16:41+01:00 0.000000 215071 5.0
298 2022-11-18 04:16:46+01:00 0.000000 215071 5.0
299 2022-11-18 04:16:51+01:00 0.000000 215071 5.0
300 2022-11-18 04:16:56+01:00 0.000000 215071 5.0
301 2022-11-18 04:17:01+01:00 0.000000 215071 5.0
302 2022-11-18 04:17:06+01:00 0.000000 215071 5.0
303 2022-11-18 04:17:11+01:00 0.000000 215071 5.0
304 2022-11-18 04:17:16+01:00 0.000000 215071 5.0
305 2022-11-18 04:17:21+01:00 0.000000 215071 5.0
306 2022-11-18 04:17:26+01:00 0.000000 215071 5.0
307 2022-11-18 04:17:31+01:00 0.000000 215071 5.0
308 2022-11-18 04:17:36+01:00 0.000000 215071 5.0
309 2022-11-18 04:17:41+01:00 0.000000 215071 5.0
310 2022-11-18 04:17:46+01:00 0.000000 215071 5.0
I would expect something like this:
time value id delta sum
211 2022-11-18 04:09:30+01:00 0.000000 215071 NaN 0
212 2022-11-18 04:09:35+01:00 0.000000 215071 5.0 5
213 2022-11-18 04:09:40+01:00 0.000000 215071 5.0 10
214 2022-11-18 04:09:45+01:00 0.000000 215071 5.0 15
215 2022-11-18 04:09:50+01:00 0.000000 215071 5.0 20
216 2022-11-18 04:09:55+01:00 0.000000 215071 5.0 25
217 2022-11-18 04:10:00+01:00 0.000000 215071 5.0 30
218 2022-11-18 04:10:05+01:00 0.000000 215071 5.0 35
219 2022-11-18 04:10:10+01:00 0.000000 215071 5.0 40
220 2022-11-18 04:10:15+01:00 0.000000 215071 5.0 45
221 2022-11-18 04:10:20+01:00 0.000000 215071 5.0 50
222 2022-11-18 04:10:25+01:00 0.000000 215071 5.0 55
223 2022-11-18 04:10:30+01:00 0.000000 215071 5.0 60
224 2022-11-18 04:10:35+01:00 0.000000 215071 5.0 65
225 2022-11-18 04:10:40+01:00 6.652344 215071 5.0 70
226 2022-11-18 04:10:45+01:00 10.347656 215071 5.0 -1
227 2022-11-18 04:10:50+01:00 19.585938 215071 5.0 -1
228 2022-11-18 04:10:55+01:00 26.968750 215071 5.0 -1
229 2022-11-18 04:11:00+01:00 44.253906 215071 5.0 -1
230 2022-11-18 04:11:05+01:00 55.722656 215071 5.0 -1
231 2022-11-18 04:11:10+01:00 50.984375 215071 5.0 -1
232 2022-11-18 04:11:15+01:00 50.121094 215071 5.0 -1
When the vehicles has some speed I am not interested in any delta's, so ideally it would be -1 or NaN. Has someone some suggestion how to achieve this result?
The logic is not fully clear, especially since you didn't provide the complete output.
If I guess correctly, you need a cumsum, that eventually restarts after each move. I provided both approaches:
m = df['value'].shift().eq(0)
# continue the cumulative sum after a move
df['sum'] = df['delta'].where(m).groupby(df['id']).cumsum()
# restart the cumulative sum after a move
df['sum2'] = df['delta'].where(m).groupby([df['id'], (~m).cumsum()]).cumsum()
Output:
time value id delta sum sum2
211 2022-11-18 04:09:30+01:00 0.000000 215071 NaN NaN NaN
212 2022-11-18 04:09:35+01:00 0.000000 215071 5.0 5.0 5.0
213 2022-11-18 04:09:40+01:00 0.000000 215071 5.0 10.0 10.0
214 2022-11-18 04:09:45+01:00 0.000000 215071 5.0 15.0 15.0
215 2022-11-18 04:09:50+01:00 0.000000 215071 5.0 20.0 20.0
216 2022-11-18 04:09:55+01:00 0.000000 215071 5.0 25.0 25.0
217 2022-11-18 04:10:00+01:00 0.000000 215071 5.0 30.0 30.0
218 2022-11-18 04:10:05+01:00 0.000000 215071 5.0 35.0 35.0
219 2022-11-18 04:10:10+01:00 0.000000 215071 5.0 40.0 40.0
220 2022-11-18 04:10:15+01:00 0.000000 215071 5.0 45.0 45.0
221 2022-11-18 04:10:20+01:00 0.000000 215071 5.0 50.0 50.0
222 2022-11-18 04:10:25+01:00 0.000000 215071 5.0 55.0 55.0
223 2022-11-18 04:10:30+01:00 0.000000 215071 5.0 60.0 60.0
224 2022-11-18 04:10:35+01:00 0.000000 215071 5.0 65.0 65.0
225 2022-11-18 04:10:40+01:00 6.652344 215071 5.0 70.0 70.0
226 2022-11-18 04:10:45+01:00 10.347656 215071 5.0 NaN NaN
227 2022-11-18 04:10:50+01:00 19.585938 215071 5.0 NaN NaN
...
260 2022-11-18 04:13:36+01:00 7.535156 215071 5.0 NaN NaN
261 2022-11-18 04:13:41+01:00 0.000000 215071 5.0 NaN NaN
262 2022-11-18 04:13:46+01:00 0.000000 215071 5.0 75.0 5.0
263 2022-11-18 04:13:51+01:00 0.000000 215071 5.0 80.0 10.0
264 2022-11-18 04:13:56+01:00 0.000000 215071 5.0 85.0 15.0
265 2022-11-18 04:14:01+01:00 0.000000 215071 5.0 90.0 20.0
266 2022-11-18 04:14:06+01:00 0.000000 215071 5.0 95.0 25.0
267 2022-11-18 04:14:11+01:00 0.000000 215071 5.0 100.0 30.0
268 2022-11-18 04:14:16+01:00 0.000000 215071 5.0 105.0 35.0
269 2022-11-18 04:14:21+01:00 0.000000 215071 5.0 110.0 40.0
270 2022-11-18 04:14:26+01:00 0.000000 215071 5.0 115.0 45.0
271 2022-11-18 04:14:31+01:00 1.812500 215071 5.0 120.0 50.0
272 2022-11-18 04:14:36+01:00 1.636719 215071 5.0 NaN NaN
273 2022-11-18 04:14:41+01:00 0.000000 215071 5.0 NaN NaN
274 2022-11-18 04:14:46+01:00 0.000000 215071 5.0 125.0 5.0
275 2022-11-18 04:14:51+01:00 0.000000 215071 5.0 130.0 10.0
276 2022-11-18 04:14:56+01:00 0.000000 215071 5.0 135.0 15.0
277 2022-11-18 04:15:01+01:00 0.000000 215071 5.0 140.0 20.0
278 2022-11-18 04:15:06+01:00 0.000000 215071 5.0 145.0 25.0
279 2022-11-18 04:15:11+01:00 3.125000 215071 5.0 150.0 30.0
280 2022-11-18 04:15:16+01:00 2.554688 215071 5.0 NaN NaN
281 2022-11-18 04:15:21+01:00 3.750000 215071 5.0 NaN NaN
282 2022-11-18 04:15:26+01:00 1.613281 215071 5.0 NaN NaN
283 2022-11-18 04:15:31+01:00 2.699219 215071 5.0 NaN NaN
284 2022-11-18 04:15:36+01:00 1.222656 215071 5.0 NaN NaN
285 2022-11-18 04:15:41+01:00 1.312500 215071 5.0 NaN NaN
286 2022-11-18 04:15:46+01:00 2.039062 215071 5.0 NaN NaN
287 2022-11-18 04:15:51+01:00 0.000000 215071 5.0 NaN NaN
288 2022-11-18 04:15:56+01:00 2.183594 215071 5.0 155.0 5.0
289 2022-11-18 04:16:01+01:00 4.058594 215071 5.0 NaN NaN
290 2022-11-18 04:16:06+01:00 2.753906 215071 5.0 NaN NaN
291 2022-11-18 04:16:11+01:00 3.820312 215071 5.0 NaN NaN
292 2022-11-18 04:16:16+01:00 1.792969 215071 5.0 NaN NaN
293 2022-11-18 04:16:21+01:00 1.218750 215071 5.0 NaN NaN
294 2022-11-18 04:16:26+01:00 0.000000 215071 5.0 NaN NaN
295 2022-11-18 04:16:31+01:00 0.000000 215071 5.0 160.0 5.0
296 2022-11-18 04:16:36+01:00 0.000000 215071 5.0 165.0 10.0
297 2022-11-18 04:16:41+01:00 0.000000 215071 5.0 170.0 15.0
298 2022-11-18 04:16:46+01:00 0.000000 215071 5.0 175.0 20.0
299 2022-11-18 04:16:51+01:00 0.000000 215071 5.0 180.0 25.0
300 2022-11-18 04:16:56+01:00 0.000000 215071 5.0 185.0 30.0
301 2022-11-18 04:17:01+01:00 0.000000 215071 5.0 190.0 35.0
302 2022-11-18 04:17:06+01:00 0.000000 215071 5.0 195.0 40.0
303 2022-11-18 04:17:11+01:00 0.000000 215071 5.0 200.0 45.0
304 2022-11-18 04:17:16+01:00 0.000000 215071 5.0 205.0 50.0
305 2022-11-18 04:17:21+01:00 0.000000 215071 5.0 210.0 55.0
306 2022-11-18 04:17:26+01:00 0.000000 215071 5.0 215.0 60.0
307 2022-11-18 04:17:31+01:00 0.000000 215071 5.0 220.0 65.0
308 2022-11-18 04:17:36+01:00 0.000000 215071 5.0 225.0 70.0
309 2022-11-18 04:17:41+01:00 0.000000 215071 5.0 230.0 75.0
310 2022-11-18 04:17:46+01:00 0.000000 215071 5.0 235.0 80.0