Reputation: 383
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
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