Vectorizing lagged operations

user_10105402 0
Viewed 49

How can I vectorize the following operation in R that involves modifying column Z recursively using lagged values of Z?

library(dplyr)
set.seed(5)

initial_Z=1000

df <- data.frame(X=round(100*runif(10),0), Y=round(100*runif(10),0))
df
    X  Y
1  20 27
2  69 49
3  92 32
4  28 56
5  10 26
6  70 20
7  53 39
8  81 89
9  96 55
10 11 84

df <- df %>%  mutate(Z=if_else(row_number()==1, initial_Z-Y, NA_real_)) 
df
    X  Y   Z
1  20 27 973
2  69 49  NA
3  92 32  NA
4  28 56  NA
5  10 26  NA
6  70 20  NA
7  53 39  NA
8  81 89  NA
9  96 55  NA
10 11 84  NA

for (i in 2:nrow(df)) {
  
  df$Z[i] <- (df$Z[i-1]*df$X[i-1]/df$X[i])-df$Y[i]
  
}
df
    X  Y           Z
1  20 27  973.000000
2  69 49  233.028986
3  92 32  142.771739
4  28 56  413.107143
5  10 26 1130.700000
6  70 20  141.528571
7  53 39  147.924528
8  81 89    7.790123
9  96 55  -48.427083
10 11 84 -506.636364

So the first value of Z is set first, based on initial_Z and first value of Y. Rest of the values of Z are calculated by using lagged values of X and Z, and current value of Y.

My actual df is large, and I need to repeat this operation thousands of times in a simulation. Using a for loop takes too much time. I prefer implementing this using dplyr, but other approaches are also welcome.

Many thanks in advance for any help.

dplyr vectorization dataframe r for-loop
2 Answers

I don't know that you can avoid the effect of for loops, but in general R should be pretty good at them. Given that, here is a Reduce variant that might suffice for you:

set.seed(5)
initial_Z=1000
df <- data.frame(X=round(100*runif(10),0), Y=round(100*runif(10),0))

df$Z <- with(df, Reduce(function(prevZ, i) {
  if (i == 1) return(prevZ - Y[i])
  prevZ*X[i-1]/X[i] - Y[i]
}, seq_len(nrow(df)), init = initial_Z, accumulate = TRUE))[-1]
df
#     X  Y           Z
# 1  20 27  973.000000
# 2  69 49  233.028986
# 3  92 32  142.771739
# 4  28 56  413.107143
# 5  10 26 1130.700000
# 6  70 20  141.528571
# 7  53 39  147.924528
# 8  81 89    7.790123
# 9  96 55  -48.427083
# 10 11 84 -506.636364

To be clear, Reduce uses for loops internally to get through the data. I generally don't like using indices as the values for Reduce's x, but since Reduce only iterates over one value, and we need both X and Y, the indices (rows) are a required step.

user_33582720

The same can be accomplished using accumulate2. Note that these are just for-loops. You should consider writing the for loop in Rcpp if at all its causing a problem in R

df %>%
  mutate(Z = accumulate2(Y, c(1, head(X, -1)/X[-1]), ~ ..1 * ..3 -..2, .init = 1000)[-1])

    X  Y         Z
1  20 27       973
2  69 49   233.029
3  92 32  142.7717
4  28 56  413.1071
5  10 26    1130.7
6  70 20  141.5286
7  53 39  147.9245
8  81 89  7.790123
9  96 55 -48.42708
10 11 84 -506.6364

You could unlist(Z):

df %>%
   mutate(Z = unlist(accumulate2(Y, c(1, head(X, -1)/X[-1]), ~ ..1 * ..3 -..2, .init = 1000))[-1])
user_83802720
Related