Rolling a function on two columns in data.table

Question

I have a data.table as follows -

library(data.table)
dt = data.table(
  date = seq(as.Date("2015-12-01"), as.Date("2015-12-10"), by="days"),
  v1 = seq(1, 10),
  v2 = c(5, rep(NA, 9))
)
dt
          date v1 v2
 1: 2015-12-01  1  5
 2: 2015-12-02  2 NA
 3: 2015-12-03  3 NA
 4: 2015-12-04  4 NA
 5: 2015-12-05  5 NA
 6: 2015-12-06  6 NA
 7: 2015-12-07  7 NA
 8: 2015-12-08  8 NA
 9: 2015-12-09  9 NA
10: 2015-12-10 10 NA

I want to roll apply the function qma to the current row value of v1 and the previous row value of v2 qma <- function(x, y){(x+y+7)/2}

I am sure there must be a simple way to do this in one line using zoo::rollapplyr or data.table.

This is a follow-up question of the original one here R - Rolling sum of two columns in data.table

This is now possible inside data.table, use `adapt` branch till it is merged to master. Enable with `by.column=FALSE`. — jangorecki, Jun 22 '23 at 11:06

Ronak Shah · Accepted Answer · 2021-01-16T04:28:19.650

3

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.

edited Jan 16 '21 at 04:28

answered Jan 16 '21 at 04:13

Ronak Shah

377,200
20
156
213

Can you confirm if this is what you were looking for? – Ronak Shah Jan 16 '21 at 04:22
I added some explanation to the answer. – Ronak Shah Jan 16 '21 at 04:29
Thanks! I never knew about Reduce function. It's a must-have in R skills. – Saurabh Jan 16 '21 at 04:30

score 0 · Answer 2 · answered Jan 16 '21 at 19:36

We can use accumulate from purrr

library(dplyr)
library(purrr)
dt %>%
    mutate(v2 = accumulate(v1[-1], qma, .init = first(v2)))
#          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

Rolling a function on two columns in data.table

2 Answers2

Linked