Below is a reproducible example with a test dataframe:
dat<- structure(list(A = c(1.3, 1.5, 1.6, 1.2, 1.1, 1.2),
B = c(0.25, 0.21, 0.21, 0.15, 0.26, 0.17),
sig = c(1, 0, 1, 1, 1, 1 ),
coef = c(1.25, 2.5, 3.3, 1.8, 2.25, 4.5)),
class = c("tbl_df", "tbl", "data.frame"),
row.names = c(NA, -6L))
I want to create a new column mv with mutate where the formula applied depends on condition in other column. Besides, this has to be recursive, so I need the previous as input. Also I need to set an initial starting value.
For example, if value in column A is superior or equal to 1.2 and sig equals to 1; mv = ((1 - Column B) * 1000)) +(Column B * 1000 * coef). But this is for the first row only. For the rest, instead of 1000 (initial value), it should be previous value. If value in column A is superior or equal to 1.2 and sig equals to 0 then mv = previous value - (previous value * Column B. If the value of column A is inferior to 1.2 then take the previous value unchanged.
The desired output is:
| A | B | sig | coef | mv |
|---|---|---|---|---|
| 1.3 | 0.25 | 1 | 1.25 | 1062.5 |
| 1.5 | 0.21 | 0 | 2.5 | 839.4 |
| 1.6 | 0.21 | 1 | 3.3 | 1244.79 |
| 1.2 | 0.15 | 1 | 1.8 | 1394.17 |
| 1.1 | 0.26 | 1 | 2.25 | 1394.17 |
| 1.2 | 0.17 | 1 | 4.5 | 2223.70 |
I have tried using case_when but the results are off and I'm stuck.
dat<-dat %>%
mutate(mv = case_when(
sig==1 ~ accumulate(
B *(A>=1.2) * coef, .f = ~ .x * (1 + .y), .init = 1000)[-1],
sig== 0 ~ accumulate(
B *(A>=1.2), .f = ~ .x - (1 * .y), .init = 1000)[-1]))
As per your update, it seems the rules are complicated but they indeed can be simplified. You can try the code below
dat %>%
mutate(mv = cumprod((1 + B * (coef * sig - 1))**(A >= 1.2)) * 1000)
or
dat %>%
mutate(mv = cumprod(ifelse(A >= 1.2, 1 + B * (coef * sig - 1), 1)) * 1000)
# A tibble: 6 × 5
A B sig coef mv
<dbl> <dbl> <dbl> <dbl> <dbl>
1 1.3 0.25 1 1.25 1062.
2 1.5 0.21 0 2.5 839.
3 1.6 0.21 1 3.3 1245.
4 1.2 0.15 1 1.8 1394.
5 1.1 0.26 1 2.25 1394.
6 1.2 0.17 1 4.5 2224.