Time series cumulative sum with two conditional resets

Question

I'm looking for a (preferably tidyverse) solution to create a running counter (cumulative sum) based on column direction, with counter resetting each time the direction changes or stops.

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union

df <- tibble::tribble(
    ~direction,             ~datetime, ~counter,
        "Left",  "2020-09-27 8:00:00",       1L,
        "Left",  "2020-09-27 9:00:00",       2L,
       "Right", "2020-09-27 10:00:00",       1L,
       "Right", "2020-09-27 11:00:00",       2L,
       "Right", "2020-09-27 12:00:00",       3L,
        "Stop", "2020-09-27 13:00:00",       1L,
        "Left", "2020-09-27 14:00:00",       1L,
       "Right", "2020-09-27 15:00:00",       1L,
        "Left", "2020-09-27 16:00:00",       1L,
        "Left", "2020-09-27 17:00:00",       2L
    ) %>% 
  mutate(datetime = as_datetime(datetime))
df
#> # A tibble: 10 x 3
#>    direction datetime            counter
#>    <chr>     <dttm>                <int>
#>  1 Left      2020-09-27 08:00:00       1
#>  2 Left      2020-09-27 09:00:00       2
#>  3 Right     2020-09-27 10:00:00       1
#>  4 Right     2020-09-27 11:00:00       2
#>  5 Right     2020-09-27 12:00:00       3
#>  6 Stop      2020-09-27 13:00:00       1
#>  7 Left      2020-09-27 14:00:00       1
#>  8 Right     2020-09-27 15:00:00       1
#>  9 Left      2020-09-27 16:00:00       1
#> 10 Left      2020-09-27 17:00:00       2

^{Created on 2021-07-27 by the reprex package (v2.0.0)}

I tried adapting the code from this solution but couldn't get it to work for two conditional resets.

score 1 · Accepted Answer · answered Jul 27 '21 at 02:53

We may use rowid with rleid

library(dplyr)
library(data.table)
df %>% 
    mutate(counter2 = rowid(rleid(direction)))

-output

# A tibble: 10 x 4
   direction datetime            counter counter2
   <chr>     <dttm>                <int>    <int>
 1 Left      2020-09-27 08:00:00       1        1
 2 Left      2020-09-27 09:00:00       2        2
 3 Right     2020-09-27 10:00:00       1        1
 4 Right     2020-09-27 11:00:00       2        2
 5 Right     2020-09-27 12:00:00       3        3
 6 Stop      2020-09-27 13:00:00       1        1
 7 Left      2020-09-27 14:00:00       1        1
 8 Right     2020-09-27 15:00:00       1        1
 9 Left      2020-09-27 16:00:00       1        1
10 Left      2020-09-27 17:00:00       2        2

Or using only dplyr

df %>% 
    group_by(grp = cumsum(direction != 
        lag(direction, default = first(direction)))) %>% 
    mutate(counter2 = row_number()) %>%
    ungroup %>% 
    select(-grp)
# A tibble: 10 x 4
   direction datetime            counter counter2
   <chr>     <dttm>                <int>    <int>
 1 Left      2020-09-27 08:00:00       1        1
 2 Left      2020-09-27 09:00:00       2        2
 3 Right     2020-09-27 10:00:00       1        1
 4 Right     2020-09-27 11:00:00       2        2
 5 Right     2020-09-27 12:00:00       3        3
 6 Stop      2020-09-27 13:00:00       1        1
 7 Left      2020-09-27 14:00:00       1        1
 8 Right     2020-09-27 15:00:00       1        1
 9 Left      2020-09-27 16:00:00       1        1
10 Left      2020-09-27 17:00:00       2        2

score 1 · Answer 2 · answered Jul 27 '21 at 05:40

In base R, you can use rle with sequence -

df$counter <- sequence(rle(df$direction)$lengths)

If you want to use this in a pipe you can do -

library(dplyr)
df %>% mutate(counter = sequence(rle(direction)$lengths))

#  direction datetime            counter
#   <chr>     <dttm>                <int>
# 1 Left      2020-09-27 08:00:00       1
# 2 Left      2020-09-27 09:00:00       2
# 3 Right     2020-09-27 10:00:00       1
# 4 Right     2020-09-27 11:00:00       2
# 5 Right     2020-09-27 12:00:00       3
# 6 Stop      2020-09-27 13:00:00       1
# 7 Left      2020-09-27 14:00:00       1
# 8 Right     2020-09-27 15:00:00       1
# 9 Left      2020-09-27 16:00:00       1
#10 Left      2020-09-27 17:00:00       2

Time series cumulative sum with two conditional resets

2 Answers2