dplyr reset counter when the threshold is reached

user_4436572 0
Viewed 99

I have following tibble called test:

  datetime                volume
  <dttm>                   <dbl>
  2020-08-25 09:30:00.000      0
  2020-08-25 09:30:12.000    107
  2020-08-25 09:30:50.000    221
  2020-08-25 09:30:50.000    132
  2020-08-25 09:30:50.000    148
  2020-08-25 09:30:50.000    100
  2020-08-25 09:30:50.000    100
  2020-08-25 09:30:58.000    100
  2020-08-25 09:31:56.000    157
  2020-08-25 09:32:36.000    288
  2020-08-25 09:32:36.000    100
  2020-08-25 09:33:10.000    235
  2020-08-25 09:33:23.000    182
  2020-08-25 09:33:44.000    218
  2020-08-25 09:33:44.000    179
  2020-08-25 09:34:18.000    318
  2020-08-25 09:34:27.000    101
  2020-08-25 09:34:27.000    157
  2020-08-25 09:34:27.000    200
  2020-08-25 09:34:27.000    114

I wanted to calculate the cumulative time difference (or even the just the number of rows where the timestamps are the same) when a threshold for the volume is reached. Once the threshold is reached/surpassed, I reset the counter to 0, and accumulate from that point onward again.

For example, if my threshold is 300, I accumulate from row 1 to row 3, I'd get 0+107+221=328, I would now choose to:

  • retain the timestamp of this row,
  • calculate the time difference from row 1 to row 3,
  • or count the number of rows till I reached the threshold

any of above would serve the purpose, best option would be retaining the timestamp.

Next step is to reset the counter (which at the moment stays at 328) and start counting again from row 4; from row 4 to row 7 I accumulate 148+100+100=348, I'd retain the timestamp again (for example). I'd then again, reset the counter and move on again.

I was trying to do this with dplyr or generally in tidyverse however I wasn't able to come up with reasonable solution. I don't think there's a way to do this just solely piping-along-with-dplyr.

I think I can get by with a for-loop but that'd my last option. The difficult part for me is to reset the counter and start counting again.

purrr tidyr dplyr r
2 Answers

One dplyr and purrr possibility could be:

df %>%
 group_by(group_id = cumsum(c(0, diff(accumulate(volume, ~ if_else(.x >= 300, .y, .x + .y))) < 0))) %>%
 summarise(timestamp_first = first(datetime),
           timestamp_last = last(datetime),
           time_diff = last(datetime) - first(datetime),
           n_rows = n(),
           volume_sum = sum(volume))

  group_id timestamp_first     timestamp_last      time_diff n_rows volume_sum
     <dbl> <dttm>              <dttm>              <drtn>     <int>      <int>
1        0 2020-08-25 09:30:00 2020-08-25 09:30:50 50 secs        3        328
2        1 2020-08-25 09:30:50 2020-08-25 09:30:50  0 secs        3        380
3        2 2020-08-25 09:30:50 2020-08-25 09:31:56 66 secs        3        357
4        3 2020-08-25 09:32:36 2020-08-25 09:32:36  0 secs        2        388
5        4 2020-08-25 09:33:10 2020-08-25 09:33:23 13 secs        2        417
6        5 2020-08-25 09:33:44 2020-08-25 09:33:44  0 secs        2        397
7        6 2020-08-25 09:34:18 2020-08-25 09:34:18  0 secs        1        318
8        7 2020-08-25 09:34:27 2020-08-25 09:34:27  0 secs        3        458
9        8 2020-08-25 09:34:27 2020-08-25 09:34:27  0 secs        1        114
user_59645570

This will probably get you going. It makes use of the MESS-package, especially the genius cumsumbinning-function.

sample data

library( data.table )
library( MESS )
test <- data.table::fread( "datetime                volume
2020-08-25T09:30:00.000      0
2020-08-25T09:30:12.000    107
2020-08-25T09:30:50.000    221
2020-08-25T09:30:50.000    132
2020-08-25T09:30:50.000    148
2020-08-25T09:30:50.000    100
2020-08-25T09:30:50.000    100
2020-08-25T09:30:58.000    100
2020-08-25T09:31:56.000    157
2020-08-25T09:32:36.000    288
2020-08-25T09:32:36.000    100
2020-08-25T09:33:10.000    235
2020-08-25T09:33:23.000    182
2020-08-25T09:33:44.000    218
2020-08-25T09:33:44.000    179
2020-08-25T09:34:18.000    318
2020-08-25T09:34:27.000    101
2020-08-25T09:34:27.000    157
2020-08-25T09:34:27.000    200
2020-08-25T09:34:27.000    114")

test[, datetime := as.POSIXct( datetime, format = "%Y-%m-%dT%H:%M:%OS") ]

code

is in data.table syntax, but can easily be integreted in any tidyverse or baseR solution

#create groups based on cumsum with threshold of 300
test[, group_id := MESS::cumsumbinning( volume, threshold = 300, cutwhenpassed = TRUE )]

#                datetime volume group_id
#  1: 2020-08-25 09:30:00      0        1
#  2: 2020-08-25 09:30:12    107        1
#  3: 2020-08-25 09:30:50    221        1
#  4: 2020-08-25 09:30:50    132        2
#  5: 2020-08-25 09:30:50    148        2
#  6: 2020-08-25 09:30:50    100        2
#  7: 2020-08-25 09:30:50    100        3
#  8: 2020-08-25 09:30:58    100        3
#  9: 2020-08-25 09:31:56    157        3
# 10: 2020-08-25 09:32:36    288        4
# 11: 2020-08-25 09:32:36    100        4
# 12: 2020-08-25 09:33:10    235        5
# 13: 2020-08-25 09:33:23    182        5
# 14: 2020-08-25 09:33:44    218        6
# 15: 2020-08-25 09:33:44    179        6
# 16: 2020-08-25 09:34:18    318        7
# 17: 2020-08-25 09:34:27    101        8
# 18: 2020-08-25 09:34:27    157        8
# 19: 2020-08-25 09:34:27    200        8
# 20: 2020-08-25 09:34:27    114        9

Now the grouping is done, and summarising should be easy peasy..

user_63562780
Related