Is there a way to group values in a column between data gaps in R?

Question

I want to group my data in different chunks when the data is continuous. Trying to get the group column from dummy data like this:

       a     b group
   <dbl> <dbl> <dbl>
 1     1     1     1
 2     2     2     1
 3     3     3     1
 4     4    NA    NA
 5     5    NA    NA
 6     6    NA    NA
 7     7    12     2
 8     8    15     2
 9     9    NA    NA
10    10    25     3

I tried using

test %>% mutate(test = complete.cases(.)) %>%
  group_by(group = cumsum(test == TRUE)) %>%
  select(group, everything())

But it doesn't work as expected:

   group     a     b test 
   <int> <dbl> <dbl> <lgl>
 1     1     1     1 TRUE 
 2     2     2     2 TRUE 
 3     3     3     3 TRUE 
 4     3     4    NA FALSE
 5     3     5    NA FALSE
 6     3     6    NA FALSE
 7     4     7    12 TRUE 
 8     5     8    15 TRUE 
 9     5     9    NA FALSE
10     6    10    25 TRUE

Any advice?

Related post but without NAs - https://stackoverflow.com/q/37809094/680068 — zx8754, Oct 26 '21 at 12:00

score 1 · Answer 1 · answered Oct 26 '21 at 07:02

Using rle in base R -

transform(df, group1 = with(rle(!is.na(b)), rep(cumsum(values), lengths))) |>
  transform(group1 = replace(group1, is.na(b), NA))

#    a  b group group1
#1   1  1     1      1
#2   2  2     1      1
#3   3  3     1      1
#4   4 NA    NA     NA
#5   5 NA    NA     NA
#6   6 NA    NA     NA
#7   7 12     2      2
#8   8 15     2      2
#9   9 NA    NA     NA
#10 10 25     3      3

score 0 · Accepted Answer · answered Oct 26 '21 at 11:21

A couple of approaches to consider if you wish to use dplyr for this.

First, you could look at transition from non-complete cases (using lag) to complete cases.

library(dplyr)

test %>% 
  mutate(test = complete.cases(.)) %>%
  group_by(group = cumsum(test & !lag(test, default = F))) %>%
  mutate(group = replace(group, !test, NA))

Alternatively, you could add row numbers to your data.frame. Then, you could filter to include only complete cases, and group_by enumerating with cumsum based on gaps in row numbers. Then, join back to original data.

test$rn <- seq.int(nrow(test))

test %>% 
  filter(complete.cases(.)) %>%
  group_by(group = c(0, cumsum(diff(rn) > 1)) + 1) %>%
  right_join(test) %>%
  arrange(rn) %>%
  dplyr::select(-rn)

Output

       a     b group
   <int> <int> <dbl>
 1     1     1     1
 2     2     2     1
 3     3     3     1
 4     4    NA    NA
 5     5    NA    NA
 6     6    NA    NA
 7     7    12     2
 8     8    15     2
 9     9    NA    NA
10    10    25     3

score 0 · Answer 3 · answered Oct 26 '21 at 11:59

Using data.table, get rleid then remove group IDs for NAs, then fix the sequence with factor to integer conversion:

library(data.table)

setDT(test)[, group1 := {
  x <- complete.cases(test)
  grp <- rleid(x)
  grp[ !x ] <- NA
  as.integer(factor(grp))
  }]
#      a  b group group1
#  1:  1  1     1      1
#  2:  2  2     1      1
#  3:  3  3     1      1
#  4:  4 NA    NA     NA
#  5:  5 NA    NA     NA
#  6:  6 NA    NA     NA
#  7:  7 12     2      2
#  8:  8 15     2      2
#  9:  9 NA    NA     NA
# 10: 10 25     3      3

Is there a way to group values in a column between data gaps in R?

3 Answers3