CODEWITHSUNDEEP
rdplyr
GrBa
GrBa

Reputation: 649

cumulative sum with two conditions

The given data are fictitious, and in reality they are more complicated

t <- data.frame(v1=c(265, -268, 123, 58, 560, 56, -260, 40, 530, -895, 20))

I want to count a cumulative sum with two limiting values: 0 and 500. If the cumulative total exceeds 500 then you must keep 500. If the cumulative total becomes negative then you must store 0 . The results obtained are as follows:

     v1 sum.c
1   265   265
2  -268     0
3   123   123
4    58   181
5   560   500
6    56   500
7  -260   240
8    40   280
9   530   500
10 -895     0
11   20    20

The formula in excel would be like this: =if(B1+A2<0; 0; if(B1+A2>500; 500; B1+A2))

Any ideas?

Upvotes: 3

Views: 74

Answers (3)

one
one

Reputation: 3902

Using Rcpp (Base code from here):

library(Rcpp)
cppFunction('NumericVector cumsumCPP(NumericVector x){

    // Need to do this in order to avoid modifying the original x
    int n = x.size();
    NumericVector res(n);
    res[0] = x[0];

    for (int i = 1 ; i < n ; i++) {
      res[i] = res[i - 1] + x[i];
      if (res[i] > 500) { 
        res[i] = 500;
      }
      if (res[i] < 0) {
        res[i] = 0;
      }
    }

    return res;
}')

cumsumCPP(t$v1)
[1] 265   0 123 181 500 500 240 280 500   0  20

library(dplyr)
t%>%mutate(cum_s=cumsumCPP(v1))

     v1 cum_s
1   265   265
2  -268     0
3   123   123
4    58   181
5   560   500
6    56   500
7  -260   240
8    40   280
9   530   500
10 -895     0
11   20    20

You can also define your own customize cumulative sum in R.

Upvotes: 3

TarJae
TarJae

Reputation: 78937

Taking the idea from @ThomasIsCoding, here is tidyverse approach:

library(dplyr)
library(purrr)

t %>%
  mutate(sum.c = accumulate(v1, ~ min(max(.x + .y, 0), 500)))

     v1 sum.c
1   265   265
2  -268     0
3   123   123
4    58   181
5   560   500
6    56   500
7  -260   240
8    40   280
9   530   500
10 -895     0
11   20    20

Upvotes: 3

ThomasIsCoding
ThomasIsCoding

Reputation: 101663

We can use min and max to set the boundaries and Reduce to iterate the vector

> v1 <- c(265, -268, 123, 58, 560, 56, -260, 40, 530, -895, 20)

> Reduce(function(x, y) min(max(x + y, 0), 500),v1, accumulate = TRUE)
 [1] 265   0 123 181 500 500 240 280 500   0  20

Upvotes: 5

Related Questions