CODEWITHSUNDEEP
rcumsum
user3356873
user3356873

Reputation: 63

Conditional cumsum with reset when accumulating and substracting at once

I have a data frame with three variables i.e. V1, V2 and V3. 

ts<- c(-2, 4, 3,-5,-5,-7, -8, -2, -3, -5,-7, -8, -9, -2, 1, 2,4)

x<- c(6, 0, 0 ,1, 0, 2, 3,5,7,7,8,2,0, 0, 0 , 0, 0)

y<- c(0, 5, 8, 0, 0 , 0 , 0 , 0 , 0, 0, 0, 0, 0, 7, 9, 12, 0)

ve <- data.frame(V1 = ts, V2 = x, V3 =y)

I applied conditional cumsum with the code given below:

ve$yt<- cumsum(ifelse(ve$V1>0, ve$V2-(ve$V3), ve$V2))

I have to admit, this code did its job partially for me until I encountered negative value. As such, I have different desired output (DO). I want to restart cumulating the value as shown in table below, once I have the negative value as encountered in column yt. 

View(Ve)

      V1      V2     V3     yt      DO
      -2      6      0      6       6
       4      0      5      1       1 
       3      0      8     -7       0          
      -5      1      0     -6       1
      -5      0      0     -6       1
      -7      2      0     -4       3
      -8      3      0     -1       6
      -2      5      0      4       11
      -3      7      0      11      18
      -5      7      0      18      25
      -7      8      0      26      33
      -8      2      0      28      35 
      -9      0      0      28      35
      -2      0      7      28      35
       1      0      9      19      26 
       2      0      12     7       14  
       4      0      0      7       14

I searched for similar problem but I was unable to get any answer to solve my problem. These are some of the links I tried to solve my problem:

Conditional cumsum with reset

resetting cumsum if value goes to negative in r

I sincerely request to help me solve my problem.

Upvotes: 2

Views: 139

Answers (1)

lroha
lroha

Reputation: 34291

Here is one way you might do this:

ve$DO <- Reduce(function(x,y) pmax(x + y, 0), with(ve, V2-V3*(V1 >  0)), accumulate = TRUE)

ve
   V1 V2 V3 DO
1  -2  6  0  6
2   4  0  5  1
3   3  0  8  0
4  -5  1  0  1
5  -5  0  0  1
6  -7  2  0  3
7  -8  3  0  6
8  -2  5  0 11
9  -3  7  0 18
10 -5  7  0 25
11 -7  8  0 33
12 -8  2  0 35
13 -9  0  0 35
14 -2  0  7 35
15  1  0  9 26
16  2  0 12 14
17  4  0  0 14

Equivalent using purrr/dplyr:

library(purrr)
library(dplyr)

ve %>%
  mutate(DO = accumulate(V2-V3*(V1 >  0), .f = ~pmax(.x + .y, 0)))

Upvotes: 2

Related Questions