3

I'm looking to construct a moving average while aggregating a timeseries dataset over two categorical variables. While I've seen a few other tutorials, none of them seem to capture the specific task I'd like to achieve.

My original dataset (df) has rows for each individual (id) for a series of dates ranging from 0-180 (Days). Individuals can be members of one of two subsets of data (Group).

I then aggregate this data frame to get a daily mean for the two groups.

library(plyr)
summary <- ddply(df, .(Group,Days), summarise,
                      DV = mean(variable), resp=length(unique(Id)))

The next step, however, is to construct a moving average within the two groups. In the sample dataframe below, I've just constructed a 5-day mean using the previous 5 days.

Group       Days  DV    5DayMA
exceeded    0   2859    
exceeded    1   2948    
exceeded    2   4412    
exceeded    3   5074    
exceeded    4   5098    4078
exceeded    5   5147    4536
exceeded    6   4459    4838
exceeded    7   4730    4902
exceeded    8   4643    4815
exceeded    9   4698    4735
exceeded    10  4818    4670
exceeded    11  4521    4682
othergroup  0   2859    
othergroup  1   2948    
othergroup  2   4412    
othergroup  3   5074    
othergroup  4   5098    4078
othergroup  5   5147    4536
othergroup  6   4459    4838
othergroup  7   4730    4902
othergroup  8   4643    4815
othergroup  9   4698    4735
othergroup  10  4818    4670
othergroup  11  4521    4682

Any thoughts on how to do this?

roody
  • 2,633
  • 5
  • 38
  • 50

2 Answers2

9

You could try zoo::rollmean

df <- structure(list(Group = c("exceeded", "exceeded", "exceeded", 
"exceeded", "exceeded", "exceeded", "exceeded", "exceeded", "exceeded", 
"exceeded", "exceeded", "exceeded", "othergroup", "othergroup", 
"othergroup", "othergroup", "othergroup", "othergroup", "othergroup", 
"othergroup", "othergroup", "othergroup", "othergroup", "othergroup"
), Days = c(0L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L, 
0L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 11L), DV = c(2859L, 
2948L, 4412L, 5074L, 5098L, 5147L, 4459L, 4730L, 4643L, 4698L, 
4818L, 4521L, 2859L, 2948L, 4412L, 5074L, 5098L, 5147L, 4459L, 
4730L, 4643L, 4698L, 4818L, 4521L), X5DayMA = c(NA, NA, NA, NA, 
4078L, 4536L, 4838L, 4902L, 4815L, 4735L, 4670L, 4682L, NA, NA, 
NA, NA, 4078L, 4536L, 4838L, 4902L, 4815L, 4735L, 4670L, 4682L
)), .Names = c("Group", "Days", "DV", "X5DayMA"), class = "data.frame", row.names = c(NA, 
-24L))

head(df)
     Group Days   DV X5DayMA
1 exceeded    0 2859      NA
2 exceeded    1 2948      NA
3 exceeded    2 4412      NA
4 exceeded    3 5074      NA
5 exceeded    4 5098    4078
6 exceeded    5 5147    4536

library(plyr)
library(zoo)
ddply(
  df, "Group",
  transform,
  5daymean = rollmean(DV, 5, align="right", na.pad=TRUE ))

        Group Days   DV X5DayMA 5daymean
1    exceeded    0 2859      NA       NA
2    exceeded    1 2948      NA       NA
3    exceeded    2 4412      NA       NA
4    exceeded    3 5074      NA       NA
5    exceeded    4 5098    4078   4078.2
6    exceeded    5 5147    4536   4535.8
7    exceeded    6 4459    4838   4838.0
8    exceeded    7 4730    4902   4901.6
9    exceeded    8 4643    4815   4815.4
10   exceeded    9 4698    4735   4735.4
11   exceeded   10 4818    4670   4669.6
12   exceeded   11 4521    4682   4682.0
13 othergroup    0 2859      NA       NA
14 othergroup    1 2948      NA       NA
15 othergroup    2 4412      NA       NA
16 othergroup    3 5074      NA       NA
17 othergroup    4 5098    4078   4078.2
18 othergroup    5 5147    4536   4535.8
19 othergroup    6 4459    4838   4838.0
20 othergroup    7 4730    4902   4901.6
21 othergroup    8 4643    4815   4815.4
22 othergroup    9 4698    4735   4735.4
23 othergroup   10 4818    4670   4669.6
24 othergroup   11 4521    4682   4682.0

or even faster with dplyr

library(dplyr)
df %.%
  dplyr:::group_by(Group) %.%
  dplyr:::mutate('5daymean' = rollmean(DV, 5, align="right", na.pad=TRUE ))

OR the super fast data.table

library(data.table)
dft <- data.table(df)
dft[ , `:=` ('5daymean' = rollmean(DV, 5, align="right", na.pad=TRUE )) , by=Group ]
Paulo E. Cardoso
  • 5,778
  • 32
  • 42
  • 1
    Or this variation of the above which uses `ave` and zoo's `rollmeanr`: `transform(DF, MA = ave(DV, Group, FUN = function(x) rollmeanr(x, 5, na.pad = TRUE)))` – G. Grothendieck May 12 '14 at 23:42
  • @G.Grothendieck +1 for this comment, which adds something new for me. Do you think it will be faster? – Paulo E. Cardoso May 12 '14 at 23:46
  • 1
    In the discussion of speed, it is worth noting that even an unoptimised `ave` solution on `1e7` rows and with `1000` groups only takes about `1.4` seconds on a modest machine. The relative difference may be large using faster packages, but the absolute difference is tiny if it is a one-off calculation. – thelatemail May 13 '14 at 00:18
  • Not necessarily. You will have to try it. `roll_mean` in RcppRoll package used in place of `rolmeanr` is written in C++ and may have some speed advantage. – G. Grothendieck May 13 '14 at 00:21
  • 1
    `roll_mean()` in RcppRoll is most definitely faster than zoo's `rollmean()` (see [here](http://stackoverflow.com/questions/21368245/performance-of-rolling-window-functions-in-r/21371399#21371399)), but it seems to lack the ability to handle missing values (there's no `na.pad` option), so I don't know how you'd use it with `dplyr` or the other methods in this answer… – Andrew May 13 '14 at 00:31
  • Thank you all. This is interesting. The differences in speed may arise when including more factors into grouping, beside nrows. I think. But this may be relevant for very large data sets. – Paulo E. Cardoso May 13 '14 at 00:34
3

ave and filter:

with(df, ave(DV, Group, FUN=function(x) filter(x,rep(1/5,5),sides=1)))
# [1]     NA     NA     NA     NA 4078.2 4535.8 4838.0 4901.6 4815.4 4735.4
#[11] 4669.6 4682.0     NA     NA     NA     NA 4078.2 4535.8 4838.0 4901.6
#[21] 4815.4 4735.4 4669.6 4682.0
thelatemail
  • 91,185
  • 12
  • 128
  • 188