Calculate rolling average in matrix

Question

I want to calculate a rolling average. Specifically, I want to fill each row of columns 5 and 6 of Mat1, with a rolling average of the prior 3 columns. For column 5 this implies an average over 2,3,4 and for column 6, the average over columns 3,4,5. I only want to calculate the average when there are no NAs in the columns over which the average is calculated.

mat1 <- data.frame(matrix(nrow =6, ncol =6))
mat1[1:4,1:4] = rnorm(16,0,1)
mat1[5:6,1:3] = rnorm(6,0,1)
mat1 

        X1          X2          X3         X4 X5 X6
1  0.40023542  2.05111693 0.695422777  0.9938004 NA NA
2  0.22673283 -0.86433614 0.002620227  0.8464388 NA NA
3  0.88522293 -0.72385091 0.751663489  1.3240476 NA NA
4  0.65373734  1.68385938 0.759718967 -0.4577604 NA NA
5 -0.09442161  0.72186678 0.180312264         NA NA NA
6  0.39930843  0.04311092 2.141065229         NA NA NA

for entry 1,5 = mean(2.051,0.69,0.99) and for entry 1,6 = mean(0.69, 0.99, mean(2.051,0.69,0.99)).

Do you want to return NA for NA values? So row 5 and 6 would be NA for both columns ? — Ronak Shah

Ronak Shah Ronak Shah · Accepted Answer · 2019-09-15T09:50:49

We can use for loop to calculate rolling mean of last three columns

cols <- 5:6
for(i in cols) {
   mat1[i] <- rowMeans(mat1[(i-3):(i-1)])
}

mat1
#           X1          X2          X3         X4           X5        X6
#1  0.40023542  2.05111693 0.695422777  0.9938004  1.246780036 0.9786677
#2  0.22673283 -0.86433614 0.002620227  0.8464388 -0.005092371 0.2813222
#3  0.88522293 -0.72385091 0.751663489  1.3240476  0.450620060 0.8421104
#4  0.65373734  1.68385938 0.759718967 -0.4577604  0.661939316 0.3212993
#5 -0.09442161  0.72186678 0.180312264         NA           NA        NA
#6  0.39930843  0.04311092 2.141065229         NA           NA        NA

This returns NA if any NA value is present in the calculation as mentioned in the comments. If we need to ignore NA values, we can set na.rm = TRUE in rowMeans.

data

mat1 <- structure(list(X1 = c(0.40023542, 0.22673283, 0.88522293, 0.65373734, 
-0.09442161, 0.39930843), X2 = c(2.05111693, -0.86433614, -0.72385091, 
 1.68385938, 0.72186678, 0.04311092), X3 = c(0.695422777, 0.002620227, 
0.751663489, 0.759718967, 0.180312264, 2.141065229), X4 = c(0.9938004, 
0.8464388, 1.3240476, -0.4577604, NA, NA), X5 = c(NA, NA, NA, 
NA, NA, NA), X6 = c(NA, NA, NA, NA, NA, NA)), class = "data.frame", 
row.names = c("1", "2", "3", "4", "5", "6"))

Calculate rolling average in matrix

1 Answers