Rolling / moving avg by group

Question

How to generate rolling mean with grouped data. Here's the data ``` set.seed(31) dd<-matrix(sample(seq(1:20),30,replace=TRUE),ncol=3) ``` Add a group identifier, and sort by group identifier ``` du<-sample(seq(1:4),10,replace=TRUE) d<-cbind(du,dd) d<-d[order(d[,1]),] ``` This gives the rolling mean but ignores group bounderis ``` d_roll_mean <- apply(d[,2:4], 2, function(x) { rollapply(zoo(x), 3, mean, partial=TRUE, align='right') } ) ``` This gives the results below ``` # cbind(d,d_roll_mean) # [1,] 1 3 3 12 3.000000 3.000000 12.000000 # [2,] 2 10 13 8 6.500000 8.000000 10.000000 # [3,] 2 17 2 17 10.000000 6.000000 12.333333 # [4,] 3 14 6 3 13.666667 7.000000 9.333333 # [5,] 3 6 20 1 12.333333 9.333333 7.000000 # [6,] 3 1 16 19 7.000000 14.000000 7.666667 # [7,] 3 19 2 11 8.666667 12.666667 10.333333 # [8,] 4 12 1 9 10.666667 6.333333 13.000000 # [9,] 4 10 13 12 13.666667 5.333333 10.666667 # [10,] 4 8 20 7 10.000000 11.333333 9.333333 ``` Here's the goal, rolling mean by group bound…

Accepted Answer

The only thing missing is a `do.call(rbind,d2_roll_mean)`. Add original data:

```
cbind(d,do.call(rbind,d2_roll_mean))
```

EDIT: I ran this through `system.time()` for a bigger example, and it does take its sweet time:

```
set.seed(31)
dd <- matrix(sample(seq(1:20),20000*500,replace=TRUE),ncol=500)
du <- sample(seq(1:350),20000,replace=TRUE)
d <- cbind(du,dd)
d <- d[order(d[,1]),]

system.time(d2_roll_mean <- by(d[,-1], d[,1], doApply))
       User      System      elapsed 
     399.60        0.57       409.91
```

`by()` and `apply()` are not the fastest functions. It may actually be faster to walk through the columns using a `for` loop and doing this by brute force, relying on the fact that `d` is sorted by ID.

Rolling / moving avg by group

Solution

Problem