dplyr - How can I group data with a given condition (cumulative sum) in R?

Question

I want to group the rows until the cumulative total exceeds 200.

If the value exceeds 200, we want to create a new group.

Here is a data set:

data = data.frame(c(0,110,1011,1014,622,1,3,1173,3,6))
colnames(data)<-c("data")

   data
1    0
2    110
3    1011
4    1014
5    622
6    1
7    3
8    1173
9    3
10   6
11   199 
12   1    
13   200 

And what I want to is:

data$group<-c(1,1,2,3,4,5,5,6,7,7)

  data     group
1   0        1        #(cum:0, group:1)
2   110      1        #(cum:110,group:1)
3   1011     2        #(cum:1011, if the value exceeds the 200, assign new group, group: 2)
4   1014     3        #(cum:1011, if the value exceeds the 200, assign new group, group: 3)
5   622      4        #(cum:622, if the value exceeds the 200, assign new group, group: 4) 
6   1        5        #(cum:1, re-start, group: 5) 
7   3        5        #(cum:4, re-start, group: 5) 
8   1173     6        #(cum:6, if the value exceeds the 200, assign new group, group: 4) 
9   3        7        #(cum:3, re-start, group: 7) 
10  6        7        #(cum:9, re-start, group: 7) 
11  199      8
12  1        8
13  200      9

question from:https://stackoverflow.com/questions/65647860/how-can-i-group-data-with-a-given-condition-cumulative-sum-in-r

Answer 1

Here is a user function f that might help to group as you described

f <- function(v, threshold = 200) {
  grp <- c()
  cnt <- 1
  while (length(v)) {
    n <- sum(cumsum(v) <= threshold)
    grp <- c(grp, rep(cnt, max(n, 1)))
    v <- v[-(1:max(n, 1))]
    cnt <- cnt + 1
  }
  grp
}

which gives

> within(data, group <- f(data))
   data group
1     0     1
2   110     1
3  1011     2
4  1014     3
5   622     4
6     1     5
7     3     5
8  1173     6
9     3     7
10    6     7
11  199     8
12    1     8
13  200     9