CODEWITHSUNDEEP
rdplyrtidyverse
M.O
M.O

Reputation: 471

Conditional recursive formula in dplyr

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]))

Upvotes: 2

Views: 122

Answers (3)

ThomasIsCoding
ThomasIsCoding

Reputation: 102529

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.

Upvotes: 5

lroha
lroha

Reputation: 34586

Adapting ThomasIsCoding's answer from your previous question, you can just split it into two steps - first calculate what the conditional value should be and then feed the values to cumprod multiplied by the sig condition:

library(dplyr)

dat %>%
  mutate(coef_x = case_match(sig,
                             1 ~ B *(A >= 1.2) * coef,
                             0 ~ B *(A >= 1.2)),
         mv = cumprod(1 + coef_x * (sig == 1) - coef_x * (sig == 0))  * 1000) 

# A tibble: 6 × 6
      A     B   sig  coef coef_x    mv
  <dbl> <dbl> <dbl> <dbl>  <dbl> <dbl>
1   1.3  0.25     1  1.25  0.312 1312.
2   1.5  0.21     0  2.5   0.21  1037.
3   1.6  0.21     1  3.3   0.693 1755.
4   1.2  0.15     1  1.8   0.27  2229.
5   1.1  0.26     1  2.25  0     2229.
6   1.2  0.17     1  4.5   0.765 3935.

Upvotes: 3

Friede
Friede

Reputation: 7979

If nrow(dat) is rather small, we might use a simple for-loop logic (which works but lacks efficiency).

# dat$mv = 
local({  
  # indexing 
  u = dat$A >= 1.2
  v = dat$sig == 1
  
  # initialise 
  n = nrow(dat)
  mv = vector(mode = 'numeric', length = n)
  mv[1] = 1e3 + (dat$B[1] * 1e3 * dat$coef[1])
  # do we need to check for u & v for i = 1 (first row)? 
  
  for(i in seq(n)[-1]) {
    l = mv[i-1]
    mv[i] = if(u[i] & v[i]) {
      l + (dat$B[i] * l * dat$coef[i])
    } else if(u[i] & !v[i]) {
      l - (dat$B[i] * l)
    } else l
  }
  mv 
})

[1] 1312.500 1036.875 1755.429 2229.395 2229.395 3934.883

Upvotes: 0

Related Questions