1

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)

willxwil
  • 13
  • 3

1 Answers1

1

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

data

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")))
akrun
  • 874,273
  • 37
  • 540
  • 662