Home Create groups in data frame based upon whether next value reaches a threshold

Questions

Create groups in data frame based upon whether next value reaches a threshold

March 30, 2022

I have df with a column diff_index.

I’d like to create a grouping column based on whether the next value is greater than a threshold x – if the next value is greater than x, then I want a new group.

So in this case, if the threshold is 100, the first 12 entries will be group 1, then since the 13th value is 3877, group 2 begins here, until we reach 1979, in which group 3 starts, etc.

A data.table solution would be ideal.

df=structure(list(diff_index = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 3877, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1979, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 136, 1, 1, 1, 1, 97, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 2, 11905, 1, 1, 1, 2764, 1, 1, 1, 676, 1, 1, 1, 2, 
1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 469, 1, 1, 
2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 
1, 1, 1, 1, 19, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
8121, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1737, 1, 1, 1, 1, 1, 1, 1, 
1, 681, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, NA)), row.names = c(NA, 
-393L), class = "data.frame")

>Solution :

library(tidyverse)
df %>% 
  mutate(group = cumsum(diff_index > 100) + 1)

Or with data.table:

dt[,group:=cumsum(diff_index > 100) + 1]

     diff_index group
  1:          1     1
  2:          1     1
  3:          1     1
  4:          1     1
  5:          1     1
 ---                 
389:          1    11
390:          1    11
391:          1    11
392:          1    11
393:         NA    NA