Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

R: cumulative sum based on another column and max sum value

Tags:

r

This is similar to previous post for cumulative sum that resets based on value in another column except that I want to restrict the sum so that it also resets when it reaches a maximum value. For example, if the maximum value is 3:

> data.frame(x=rep(1,10), 
+ y=c(0,0,1,0,0,0,0,1,0,0), 
+ cum_sum_mod=c(1, 2, 1, 2, 3, 1, 2, 1, 2, 3))

   x y cum_sum_mod
1  1 0           1
2  1 0           2
3  1 1           1
4  1 0           2
5  1 0           3
6  1 0           1
7  1 0           2
8  1 1           1
9  1 0           2
10 1 0           3

The cum_sum_mod sums the x column until it reaches the max value (3) or the value in y column is 1. I want to avoid using a loop.

like image 716
Vlad Avatar asked Jan 30 '23 14:01

Vlad


1 Answers

By using dplyr

 library(dplyr)

 dat=data.frame(x=rep(1,10), 
             y=c(0,0,1,0,0,0,0,1,0,0))
 dat$B=cumsum(dat$y)
 dat%>%group_by(B)%>%mutate(cum_sum_mod=ifelse(cumsum(x)%%3==0,3,cumsum(x)%%3))

# A tibble: 10 x 4
# Groups:   B [3]
       x     y     B cum_sum_mod
   <dbl> <dbl> <dbl>       <dbl>
 1     1     0     0           1
 2     1     0     0           2
 3     1     1     1           1
 4     1     0     1           2
 5     1     0     1           3
 6     1     0     1           1
 7     1     0     1           2
 8     1     1     2           1
 9     1     0     2           2
10     1     0     2           3
like image 93
BENY Avatar answered Feb 01 '23 07:02

BENY