Group by cumulative sums with conditions

user_8039978 0
Viewed 132

In this dataframe:

df <- data.frame(
  ID = c("C", "B", "B", "B", NA, "C", "A", NA, "B", "B", "B")
)

I'd like to group the rows using cumsum with two conditions: (i) cumsum should not continue if is.na(ID) and (ii) it should not continue if the next ID value is the same as the prior. I do meet condition (i) with this:

df %>%
  group_by(grp = cumsum(!is.na(ID)))
# A tibble: 11 x 2
# Groups:   grp [9]
   ID      grp
   <chr> <int>
 1 C         1
 2 B         2
 3 B         3
 4 B         4
 5 NA        4
 6 C         5
 7 A         6
 8 NA        6
 9 B         7
10 B         8
11 B         9

but I don't know how to implement condition (ii) too, to obtain the desired result:

 1 C         1
 2 B         2
 3 B         2
 4 B         2
 5 NA        2
 6 C         3
 7 A         4
 8 NA        4
 9 B         5
10 B         5
11 B         5

I tried it with this but I doesn't work:

df %>%
  group_by(grp = cumsum(!is.na(ID) |!lag(ID,1) == ID))
cumsum dplyr r
3 Answers

Use na.locf0 from zoo to fill in the NAs and then apply rleid from data.table:

library(data.table)
library(zoo)

rleid(na.locf0(df$ID))
##  [1] 1 2 2 2 2 3 4 4 5 5 5
user_5165480

Using tidyr and dplyr, you could do:

df %>%
 mutate(grp = fill(., ID) %>% pull(),
        grp = cumsum(grp != lag(grp, default = first(grp))))

     ID grp
1     C   0
2     B   1
3     B   1
4     B   1
5  <NA>   1
6     C   2
7     A   3
8  <NA>   3
9     B   4
10    B   4
11    B   4
user_59645570

Using rle

library(zoo)
with(rle(na.locf0(df$ID)), rep(seq_along(values), lengths))
#[1] 1 2 2 2 2 3 4 4 5 5 5
user_37322710
Related