How to Take a Rolling Product Using Data.Table

How do I take a rolling product using data.table

you can try

library(zoo)
rollapply(dt, 3, FUN = prod)
          x
[1,] 0.7200
[2,] 0.5400
[3,] 0.3000
[4,] 0.0375

To match the expected output

dt[, Prod.3 :=rollapply(x, 3, FUN=prod, fill=NA, align='left')]

Reducing rolling product data.table

You could use Reduce, with accumulate=TRUE option:

tst[order(grp,-orderVal),prod:=Reduce(`*`,val,accumulate=T),by=grp][]

   grp orderVal val   prod
1:   A        1 1.4 3.8416
2:   A        2 1.4 2.7440
3:   A        3 1.4 1.9600
4:   A        4 1.4 1.4000
5:   B        1 1.5 5.0625
6:   B        2 1.5 3.3750
7:   B        3 1.5 2.2500
8:   B        4 1.5 1.5000

Apply a rolling function to a data.table in R with a custom window size

dt[, z := shift(frollapply(x, n = 5, FUN = min, fill = 0), n = 5, fill = 0)]
dt[, all.equal(y, z)] # TRUE

Rolling a function on two columns in data.table

For such recursive calculation you may use Reduce here :

library(data.table)

dt[, v2 := Reduce(qma, v1[-1], init = first(v2), accumulate = TRUE)]
dt

#          date v1       v2
# 1: 2015-12-01  1  5.00000
# 2: 2015-12-02  2  7.00000
# 3: 2015-12-03  3  8.50000
# 4: 2015-12-04  4  9.75000
# 5: 2015-12-05  5 10.87500
# 6: 2015-12-06  6 11.93750
# 7: 2015-12-07  7 12.96875
# 8: 2015-12-08  8 13.98438
# 9: 2015-12-09  9 14.99219
#10: 2015-12-10 10 15.99609

Reduce when used with accumulate = TRUE performs recursive calculation output of which is dependent on previous output.

Take a simple example of calculating cumulative sum.

x <- 1:10
res <- Reduce(`+`, x, accumulate = TRUE)
res
#[1]  1  3  6 10 15 21 28 36 45 55

res[1] is x[1], res[2] is res[1] + x[2], res[3] is res[2] + x[3] and so on.

Rolling by group in data.table R

When you use dt$return the whole data.table is picked internally within the groups. Just use the column you need in the function definition and it will work fine:

#use the column instead of the data.table
zoo_fun <- function(column, N) {
  (rollapply(column + 1, N, FUN=prod, fill=NA, align='right') - 1)
}

#now it works fine
test[, momentum := zoo_fun(return, 3), by = sec]

As a separate note, you should probably not use return as a column or variable name.

Out:

> test
    return sec momentum
 1:    0.1   A       NA
 2:    0.1   A       NA
 3:    0.1   A    0.331
 4:    0.1   A    0.331
 5:    0.1   A    0.331
 6:    0.2   B       NA
 7:    0.2   B       NA
 8:    0.2   B    0.728
 9:    0.2   B    0.728
10:    0.2   B    0.728

Utilizing roll functions with data.table

As @Frank describes, the problem is that the result of roll_scale (and thus each element of lapply output) is a matrix. You can either use sapply instead of lapply, or put as.vector in your function definition.

DT[, RollingZScore := sapply(.SD, 
                             function(x) roll::roll_scale(as.matrix(x), width = 10, min_obs = 5)), 
   by = "group", .SDcols = "obs"]

or

DT[, RollingZScore := lapply(.SD, 
                              function(x) as.vector(roll::roll_scale(as.matrix(x), width = 10, min_obs = 5))), 
    by = "group", .SDcols = "obs"]

rolling weighted mean with data.table

"frollapply with a two column function": instead of rolling on the values, roll on the indices, and the internal function can use as many columns as desired.

 DT[, weighted_mean := frollapply(seq_len(.N),
                                  FUN = function(ind) weighted.mean(value[ind], weight[ind]),
                                  n = 3),
    by = .(group)]
#     group value weight weighted_mean
#     <num> <int>  <int>         <num>
#  1:     1     1     11            NA
#  2:     1     2     12            NA
#  3:     1     3     13      2.055556
#  4:     1     4     14      3.051282
#  5:     1     5     15      4.047619
#  6:     2     6     16            NA
#  7:     2     7     17            NA
#  8:     2     8     18      7.039216
#  9:     2     9     19      8.037037
# 10:     2    10     20      9.035088

---

Related Topics