I have this matrix:
matrix =
value1 | value2 | value3 | value4 | value5 | value6 | value7 | value8 | value9 | value10 | |
---|---|---|---|---|---|---|---|---|---|---|
1 | 0 | 0 | 45 | 6 | 6 | 8 | 3 | 0 | 4 | 5 |
2 | 0 | 0 | 0 | 2 | 9 | 1 | 8 | 3 | 0 | 0 |
n | n | n | n | n | n | n | n | n | n | n |
what i want:
roll_aver =
value1 | value2 | value3 | value4 | value5 | value6 | value7 | value8 | value9 | value10 | |
---|---|---|---|---|---|---|---|---|---|---|
1 | 0 | 0 | 45 | 25.5 | 19 | 16.25 | 5.75 | 5.67 | 5 | 4 |
2 | 0 | 0 | 0 | 2 | 5.5 | 4 | 5 | 3.5 | 4 | 5.5 |
n | n | n | n | n | n | n | n | n | n | n |
I know that I can do a Roll Average by having this:
roll_aver <- t(apply(matrix, 1, function(x) rollmean(x, k=4, fill = 0, align ="right")))
But in this way it makes an roll average including zeroes.
Imagine its a excel equivalent of doing an excel: averageif(range of last 4 in the row,>0)
We can apply the rollapply
from the first non-zero element on the left by looping over the rows. Then, with the FUN
for rollapply
, exclude the zero elements, when getting the mean
library(zoo)
t(apply(matrix, 1, FUN = function(x) {
i1 <- cumsum(x != 0) > 0
x[i1] <- rollapply(x[i1], width = 4, fill = 0, align = "right",
FUN = function(u) mean(u[u!=0]), partial = TRUE)
x}))
-output
value1 value2 value3 value4 value5 value6 value7 value8 value9 value10
1 0 0 45 25.5 19.0 16.25 5.75 5.666667 5 4.0
2 0 0 0 2.0 5.5 4.00 5.00 5.250000 4 5.5
matrix <- structure(c(0L, 0L, 0L, 0L, 45L, 0L, 6L, 2L, 6L, 9L, 8L, 1L,
3L, 8L, 0L, 3L, 4L, 0L, 5L, 0L), .Dim = c(2L, 10L), .Dimnames = list(
c("1", "2"), c("value1", "value2", "value3", "value4", "value5",
"value6", "value7", "value8", "value9", "value10")))