Calculate linear regression slope with moving window

Question

I have a dataframe and i need to automate the slope calculation by lm each 5 rows and jump 1 point and calculate slope with next 5 points and again jump 1 point and calculate slope with 5 points. And if the end of datafra when you not have 5 points put NA or no calculate slope

mtcars[c(1:5),c(1,3)] %>% mutate(slope = lm(c(mpg) ~ c(disp))$coefficients[[2]])

I need to calculate slope to next 5 points with gap = 1

mtcars[c(2:6),c(1,3)] %>% mutate(slope = lm(c(mpg) ~ c(disp))$coefficients[[2]])

`rollapply` of package `zoo` might do the trick: https://stackoverflow.com/questions/41061140/how-to-calculate-the-average-slope-within-a-moving-window-in-r — I_O, Jul 03 '23 at 09:22
Also a rolling average makes no sense in the context of mtcars, each row is a different variable, the cars don't have an order — Mark, Jul 03 '23 at 09:30
Hi Mark, After having calculated the slope on the points from 1 to 5 I recalculate another slope on the points 2 to 6 then from 3 to 7... — user21482806, Jul 03 '23 at 09:46
okay, cool! thanks for letting me know. But like I said, the cars are nominal data types, doing a regression on them is a bit like doing a regression on the numbers in the phone book — Mark, Jul 03 '23 at 11:00

Mark · Accepted Answer · 2023-07-03T11:35:05.433

Using map to iterate over each row number:

# toy dataset
data <- tibble(
        x = 1:50,
        y = rnorm(50))

data %>% 
        # for each row, get the coefficient of the independent variable (i.e the coefficient of x)
        mutate(model = c(map(1:(nrow(data) - 4), ~lm(y ~ x, data = data[.x:(.x + 4), ])$coefficients[2]), NA, NA, NA, NA) %>% unlist())

# A tibble: 50 × 3
       x       y   model
   <int>   <dbl>   <dbl>
 1     1 -2.52    0.775 
 2     2 -0.455   0.370 
 3     3 -0.914   0.333 
 4     4 -1.14    0.221 
 5     5  1.70   -0.214 
 6     6  0.0893  0.0863
 7     7  0.138  -0.305 
 8     8  0.748  -0.329 
 9     9  0.298  -0.239 
10    10  0.441   0.274 
# ℹ 40 more rows

score 0 · Answer 2 · answered Jul 03 '23 at 12:57

rollapply will compute an arbitrary function on a moving window of indicated szie (5). We define slope as shown or optionally use the commented out definition which is equivalent but slower.

The code below gives a center aligned window so there are 2 NA rows on either end but rollapplyr with an r on the end can be used to specify a right aligned window in which case they will all be at the beginning. The align="left" argument of rollapply can be used to specify left alignment if that is wanted.

library(dplyr)
library(zoo)

# slope <- function(x) coef(lm(x[, 2] ~ x[, 1]))[[2]]
slope <- function(x) cov(x[, 1], x[, 2]) / var(x[, 1])

mtcars %>%
  mutate(slope = rollapply(cbind(disp, mpg), 5, slope, by.column=FALSE, fill=NA))

Calculate linear regression slope with moving window

2 Answers2