CODEWITHSUNDEEP
rmatrixaveragemoving-averagerolling-computation
JC3019
JC3019

Reputation: 383

Calculate rolling average in matrix

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)).

Upvotes: 0

Views: 531

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 389355

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"))

Upvotes: 2

Related Questions