cumsum with a condition to restart in R

user_18503039 0
Viewed 115

I have this dataset containing multiple columns. I want to use cumsum() on a column conditioning the sum on another column. That is when X happens I want the sum to restart from zero but, I want to sum also the number of the "x" event row. I'll be more precise here in an example.

inv     ass    port   G    cumsum(G)
i        x       2    1       1
i        x       2    0       1
i        x       0    1       2
i        x       3    0       0
i        x       3    1       1

So in the 3rd row the condition port == 0 happens. I want to cumsum(G), but on the 3rd row i want to still sum the value of G and to restart the count from the following row.

I'm using dplyr to group_by(investor, asset) but I'm stuck here since I'm doing:

res1 <- res %>% 
  group_by(investor, asset) %>% 
  mutate(posdays = ifelse(operation < 0 & portfolio == 0, 0, cumsum(G))) %>% 
            ungroup()

Since this restart the cumsum() but excludes the sum of the 3rd row. I think something saying "cumsum(G) but when condition "x" in the previous row, restart the sum in the following row".

Can you help me?

cumsum conditional-statements r
1 Answers

You may use cumsum to create groups as well.

library(dplyr)

df <- df %>%
  group_by(group = cumsum(dplyr::lag(port == 0, default = 0))) %>%
  mutate(cumsum_G = cumsum(G)) %>%
  ungroup

df

#  inv   ass    port     G group cumsum_G
#  <chr> <chr> <int> <int> <dbl>    <int>
#1 i     x         2     1     0        1
#2 i     x         2     0     0        1
#3 i     x         0     1     0        2
#4 i     x         3     0     1        0
#5 i     x         3     1     1        1

You may remove the group column from output using %>% select(-group).

data

df <- structure(list(inv = c("i", "i", "i", "i", "i"), ass = c("x", 
"x", "x", "x", "x"), port = c(2L, 2L, 0L, 3L, 3L), G = c(1L, 
0L, 1L, 0L, 1L)), class = "data.frame", row.names = c(NA, -5L))
user_39629140
Related