Create count of sequential years by groups in R

user_13818948 0
Viewed 79

R newbie here. I am looking for a dplyr solution (preferably) for creating a vector that shows the number of sequential years within a group. If the sequence is interrupted by any gaps, the counter should start again even if it is the same group.

My data looks similar to this:

library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union
library(magrittr)
library(tidyverse)

df <- tribble(
    ~id, ~ref, ~branch, ~year, ~unit, ~client, ~group,
    1, 561, "LA", 2000, "x", "y", "z",  
    2, 561, "LA", 2001, "x", "y", "z",
    3, 561, "LA", 2002, "x", "y", "z",
    4, 561, "LA", 2003, "x", "y", "z",
    5, 561, "LA", 2004, "x", "y", "z",
    6, 561, "LA", 2005, "x", "y", "z",
    7, 561, "LA", 2007, "x", "y", "z",
    8, 561, "LA", 2008, "x", "y", "z",
    9, 561, "LA", 2009, "x", "y", "z",
    )

My expected output would be something like this, where "seq_count" is added:

df_exp <- tribble(
    ~id, ~ref, ~branch, ~year, ~unit, ~client, ~group, ~seq_count,
    1, 561, "LA", 2000, "x", "y", "z", 6,
    2, 561, "LA", 2001, "x", "y", "z", 6,
    3, 561, "LA", 2002, "x", "y", "z", 6,
    4, 561, "LA", 2003, "x", "y", "z", 6,
    5, 561, "LA", 2004, "x", "y", "z", 6,
    6, 561, "LA", 2005, "x", "y", "z", 6,
    7, 561, "LA", 2007, "x", "y", "z", 3,
    8, 561, "LA", 2008, "x", "y", "z", 3,
    9, 561, "LA", 2009, "x", "y", "z", 3,
    )

I have tried with dplyr::add_count as per below:

df1 <- df %>% 
    group_by(ref, branch, unit, client, group) %>% 
    add_count()

However, this only adds the count as specified by the group_by command and does not considers the gap between 2005 and 2007. Is there a a way to do this in a succinct way in R?

tidyr dplyr lubridate r
2 Answers

n() will give you the number of observations in the group.

df1 <- df %>% 
    group_by(ref, branch, unit, client, group) %>% 
    mutate(seq_count = n())

You can use summarise instead of mutate if you only want the summary.

user_42626240

You can create another group which will change when there is gap between years.

library(dplyr)
df %>% 
    add_count(group, grp = cumsum(year - lag(year, default = first(year)) > 1), 
               name = 'seq_count')

# A tibble: 9 x 9
#     id   ref branch  year unit  client group   grp seq_count
#  <dbl> <dbl> <chr>  <dbl> <chr> <chr>  <chr> <int>     <int>
#1     1   561 LA      2000 x     y      z         0         6
#2     2   561 LA      2001 x     y      z         0         6
#3     3   561 LA      2002 x     y      z         0         6
#4     4   561 LA      2003 x     y      z         0         6
#5     5   561 LA      2004 x     y      z         0         6
#6     6   561 LA      2005 x     y      z         0         6
#7     7   561 LA      2007 x     y      z         1         3
#8     8   561 LA      2008 x     y      z         1         3
#9     9   561 LA      2009 x     y      z         1         3

Or with n()

df %>%
  group_by(group, grp = cumsum(year - lag(year, default = first(year)) > 1)) %>%
  mutate(seq_count = n())
user_39629140
Related