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

user_8597839 0
Viewed 53

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
dplyr r group-by
2 Answers

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
user_121587570

Based on the updated post, a recursive addition can be done with an if/else condition

grp <- 1
for(i in 2:nrow(data)) {
   grp <- if((data$data[i-1] + data$data[i]) > 200) {
         c(grp, grp[i-1] + 1)
        } else c(grp, grp[i-1])
 }
data$group <- grp

-output

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

data

data <- structure(list(data = c(0L, 110L, 1011L, 1014L, 622L, 1L, 3L, 
1173L, 3L, 6L, 199L, 1L, 200L)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13"
))
user_37322710
Related