Group data frame row by consecutive value in R

user_3778744 0
Viewed 150

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?

tidyverse r
3 Answers

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
user_133216470

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
user_143145200

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
user_37322710
Related