Cumulative conditional product with reset

Question

I have a large xts object. However the example is in a data.frame two column subset of the data. I would like to calculate (in a new column) the cumulative product of the first column df$rt whenever the second column df$dd is less than 0. Whenever df$dd is 0 I want to reset the cumulating to 0 again. So for the next instance that df$dd is less than 0 the cumulative product starts again for df$rt.

The following example dataframe adds the desired outcome as column three df$crt, for reference. Note that some rounding has been applied.

    df <- data.frame(
  rt = c(0, 0.0171, 0.0796, 0.003, 0.0754, -0.0314, 0.0275, -0.0323, 0.0364, 0.0473, -0.0021),
  dd = c(0, -0.0657, -0.0013, 0, -0.018, -0.0012, 0, 0, 0, -0.0016, -0.0856),
  crt = c(0, 0.171, 0.0981, 0, 0.0754, 0.0415, 0, 0, 0, 0.473, 0.045)
)

I have tried various combinations of with, ifelse and cumprod like:

df$crt <- with(df, ifelse(df$dd<0, cumprod(1+df$rt)-1, 0))

However this does not reset the cumulative product after a 0 in df$dd, it only writes a 0 and continues the previous cumulation of df$rt when df$dd is below zero again.

I think I am missing a counter of some sort to initiate the reset. Note that the dataframe I'm working with to implement this is large.

Please show expected ouptut. Do you need df %>% group_by(grp = cumsum(dd < 0)) %>% mutate(crt = cumprod(1 + rt)-1) — akrun
The expected outcome is df$crt. That dplyr solution does not give the desired outcome. — RSX
Yes it works in dplyr. I did not mention that I have an xts object. Is there a way to do it in base r? — RSX

akrun akrun · Accepted Answer · 2021-06-30T18:12:49

Create a grouping column by taking the cumulative sum of logical vector (dd == 0) so that it increments by 1 at positions where dd is 0, then use replace with the condition to do the cumulative product in 'rt' only in places where 'dd' is not equal to 0

library(dplyr)
df %>% 
    group_by(grp = cumsum(dd ==  0)) %>% 
    mutate(crt1 = replace(dd, dd != 0, (cumprod(1 + rt[dd!=0]) - 1))) %>%
    ungroup %>%
    select(-grp)

-output

# A tibble: 11 x 4
        rt      dd    crt   crt1
     <dbl>   <dbl>  <dbl>  <dbl>
 1  0       0      0      0     
 2  0.0171 -0.0657 0.171  0.0171
 3  0.0796 -0.0013 0.0981 0.0981
 4  0.003   0      0      0     
 5  0.0754 -0.018  0.0754 0.0754
 6 -0.0314 -0.0012 0.0415 0.0416
 7  0.0275  0      0      0     
 8 -0.0323  0      0      0     
 9  0.0364  0      0      0     
10  0.0473 -0.0016 0.473  0.0473
11 -0.0021 -0.0856 0.045  0.0451

Or using base R

with(df, ave(rt * (dd != 0), cumsum(dd == 0), FUN = function(x)
        replace(x, x != 0, (cumprod(1 + x[x != 0]) - 1))))

-ouptut

 [1] 0.00000000 0.01710000 0.09806116 0.00000000 0.07540000 0.04163244 0.00000000 0.00000000 0.00000000 0.04730000 0.04510067

Cumulative conditional product with reset

1 Answers