mercredi 23 mai 2018

Cumsum with criteria in R

I want to perform this excel maneuver in R on a list of values:

=IF($H2+$G3>0;$H2+$G3;0)

The data:

d <- c(0.007084, 0.000037, -0.400000, -0.400000, 0.000033, 0.000467, 0.001167, 0.000363)

The result from excel, which I want to create in R is:

result <- c(0.007084, 0.007121, 0.000000, 0.000000, 0.000033, 0.000499, 0.001666, 0.002030)

d <- data.frame(values, result)

The cumulative sum should be performed, but if the sum is negative the result should be zero. So I need a cumsum function which resets to zero if the sum is negative. However in the following row the sum should be based on fx 0.000000 and 0.000033, which makes the sum grow again in the following row with the sum of 0.000033 and 0.000467 (=0.000499).

Did I explain sufficiently?

Tried different approaches without success based on these:

fx lapply, dplyr, lookup....

library(dplyr)

d1 <- d %>%
  group_by(grp = cumsum(lag(cumsum(replace(values, is.na(values), 0.000000)) < 0.000000, default = TRUE))) %>%
  mutate(calc = cumsum(replace(values, is.na(values), 0.000000)), calc =     replace(calc, calc < 0.000000, 0.000000)) %>%
  ungroup() %>%
  select(-grp)

Should be so simple, but for some reason I cannot make it work...

resetting cumsum if value goes to negative in r

https://www.dataquest.io/blog/control-structures-in-r-using-loops-and-if-else-statements/

How to get cumulative sum based on a condition

Conditional cumsum with reset

Aucun commentaire:

Enregistrer un commentaire