Group data frame row by consecutive value in R

Question

I need to group a data frame by consecutive value in a row. So for example, given this data frame:

tibble( time = c(1,2,3,4,5,10,11,20,30,31,32,40) )

I want to have a grouping column like:

tibble( time = c(1,2,3,4,5,10,11,20,30,31,32,40), group=c(1,1,1,1,1,2,2,3,4,4,4,5) )

What's a tidyverse (or base R) way to get the column group as explained?

Just skip the `split` part in the linked post. Or use the convenience function `collapse::seqid`. Cheers — Henrik, Sep 03 '21 at 17:21

score 2 · Answer 1 · answered Sep 03 '21 at 17:04

2

We could it this way:

df %>%
    arrange(time) %>%
    group_by(grp = (time  %/% 10)+1)

 time group
   <dbl> <dbl>
 1     1     1
 2     2     1
 3     3     1
 4     4     1
 5     5     1
 6    10     2
 7    11     2
 8    20     3
 9    30     4
10    31     4
11    32     4
12    40     5

answered Sep 03 '21 at 17:04

TarJae

72,363
6
19
66

1

My bad, I put consecutive in the same about ten, but this is not a generic case. So I've to reject your answer. – Ilproff_77 Sep 03 '21 at 19:36

score 1 · Answer 2 · answered Sep 03 '21 at 16:52

You can use the following solution:

library(dplyr)
library(purrr)

df %>%
  mutate(grp = accumulate(2:nrow(df), .init = 1, 
                          ~ if(time[.y] - time[.y - 1] == 1) {
                            .x 
                          } else {
                            .x + 1
                          }))

# A tibble: 12 x 2
    time   grp
   <dbl> <dbl>
 1     1     1
 2     2     1
 3     3     1
 4     4     1
 5     5     1
 6    10     2
 7    11     2
 8    20     3
 9    30     4
10    31     4
11    32     4
12    40     5

score 1 · Accepted Answer · answered Sep 03 '21 at 17:12

We could use diff on the adjacent values of 'time', check if the difference is not equal to 1, then change the logical vector to numeric index by taking the cumulative sum (cumsum) so that there is an increment of 1 at each TRUE value

library(dplyr)
df1 %>% 
   mutate(grp = cumsum(c(TRUE, diff(time) != 1)))

-output

# A tibble: 12 x 2
    time   grp
   <dbl> <int>
 1     1     1
 2     2     1
 3     3     1
 4     4     1
 5     5     1
 6    10     2
 7    11     2
 8    20     3
 9    30     4
10    31     4
11    32     4
12    40     5

Group data frame row by consecutive value in R

3 Answers3