7 Day Moving Average per group - R

Question

I have daily data with multiple categorical values, stored as a data frame:

YYYYMM    Date         ID    Count
201401    01/01/2014   A     151
201401    01/01/2014   B     68
201401    01/01/2014   C     487
201401    02/01/2014   A     198
201401    02/01/2014   B     97
201401    02/01/2014   C     403

I am trying to plot a moving average against the actual values, using ggplot.

What I would like to do is create a 5th column in my data frame which has the mean in it.

I have tried this solution (found here: Constructing moving average over a categorical variable in R)

df$Mean<-0
library(plyr)
library(zoo)
ddply(
      df, "ID",
       transform,
        df$Mean<-rollmean(Count, 7, na.pad=TRUE)
     )

and it works, but it calculates the mean for every column in my data frame, and makes another data frame within the existing one, so I end up with something like this:

YYYYMM  Date        ID  Count  Mean.YYYYMM  Mean.Date   Mean.ID  Mean.Count
201401  01/01/2014  A   151    201401       01/01/2014  B        58.90
201401  01/01/2014  B   68     201401       01/01/2014  B        62.05
201401  01/01/2014  C   487    201401       01/01/2014  B        61.84
201401  02/01/2014  A   198    201401       01/01/2014  B        58.02
201401  02/01/2014  B   97     201401       01/01/2014  B        57.65
201401  02/01/2014  C   403    201401       01/01/2014  B        59.65

When I try and plot this

for (var in unique(df$ID))
{
ggplot(df[df$ID==var,], aes(x=Date)) +
        geom_line(aes(y=Count),color="blue") +
        geom_line(aes(y=Mean$Count),color="grey",linetype="dashed") +
        facet_wrap(~ID) +
        theme_bw()
}

I get an error message. I'm wondering what I'm missing here, or if there is another way to go about this?

7 Day Moving Average per group - R

1 Answers