1

I have a dataset that looks something like this:

data.table(x=c(11:30),y=rnorm(20))

I would like to calculate the rolling regression coefficient and rsquared over the last 10 items:

dtset[,coefficient:=rollapply(1:20,width=10,FUN=function(a) {
  subdtset <- dtset[a]
  reg <- lm.fit(matrix(data=c(subdtset$x, rep(1,nrow(subdtset))), nrow=nrow(subdtset), ncol=2), subdtset$y)
  return(coef(reg)[1])
},align="right",fill=NA)]
dtset[,rsquare:=rollapply(1:20,width=10,FUN=function(a) {
  subdtset <- dtset[a]
  reg <- lm.fit(matrix(data=c(subdtset$x, rep(1,nrow(subdtset))), nrow=nrow(subdtset), ncol=2), subdtset$y)
  return(1 - sum((subdtset$y - reg$fitted.values)^2) / sum((subdtset$y - mean(subdtset$y, na.rm=TRUE))^2))
},align="right",fill=NA)]

The code above accomplishes this, but my dataset has millions of rows and I have multiple columns where I want to make these calculations so it is taking a very long time. I am hoping there is a way to speed things up:

  1. Is there a better way to capture the last 10 items in rollapply rather than passing the row numbers as the variable a and then doing subdtset <- dtset[a]? I tried using .SD and .SDcols but was unable to get that to work. I can only figure out how to get rollapply to accept one column or vector as the input, not two columns/vectors.
  2. Is there a way to return 2 values from one rollapply statement? I think I could get significant time savings if I only had to do the regression once, and then from that take the coefficient and calculate RSquare. It's pretty inefficient to do the same calculations twice.

Thanks for the help!

Kevin
  • 107
  • 5
  • (2) Return a `list` (and wrap with `asplit(.,2)`) and assign to two values, such as `dtset[, c("a","b") := asplit(rollapply(..., FUN=function(a) { ...; list(1, 2);}),2)]`. – r2evans Sep 12 '21 at 18:48
  • Thank you for this! I did not know about the asplit function. – Kevin Sep 13 '21 at 02:16

1 Answers1

1

Use by.column = FALSE to pass both columns to the function. In the function calculate the slope and r squared directly to avoid the overhead of lm.fit. Note that rollapply can return a vector and that rollapplyr with an r on the end is right aligned. This also works if dtset consists of a single x column followed by multiple y columns as in the example below with the builtin anscombe data frame.

library(data.table)
library(zoo)

stats <- function(X, x = X[, 1], y = X[, -1]) {
  c(slope = cov(x, y) / var(x), rsq = cor(x, y)^2)
}
rollapplyr(dtset, 10, stats, by.column = FALSE, fill = NA)

a <- anscombe[c("x3", "y1", "y2", "y3")]
rollapplyr(a, 3, stats, by.column = FALSE, fill = NA)

Check

We check the formulas using the built-in BOD data frame.

fm <- lm(demand ~ Time, BOD)
c(coef(fm)[[2]], summary(fm)$r.squared)
## [1] 1.7214286 0.6449202

stats(BOD)
##     slope       rsq 
## 1.7214286 0.6449202
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • I just learned `by.column=` and *love it*. – r2evans Sep 12 '21 at 18:49
  • Thank you for this. I had seen the by.column input before but I didn't know to input the whole datatable. Your example was very helpful in getting that working for me. – Kevin Sep 13 '21 at 02:20
  • (On playing with it, though, I'm mildly annoyed that `X` is a `matrix`, not a `data.frame` or `data.table`. :-( ) – r2evans Sep 14 '21 at 15:40
  • 1
    @r2evans, The idea of zoo is to provide an irregularly spaced version of ts and ts is based on vectors and matrices. Read the [design vignette](https://cran.r-project.org/web/packages/zoo/vignettes/zoo-design.pdf) to understand the goals of zoo. – G. Grothendieck Sep 14 '21 at 17:26