r - Aggregating monthly column values into quarterly values

Question

Hello Everybody I am pretty much completely new to R and any help is greatly appreciated. I have the following data (called "depressionaggregate") from 2004 until 2013 for each month:

    Month   Year    DepressionCount
1   01      2004    285
2   02      2004    323
3   03      2004    267
4   04      2004    276
5   05      2004    312
6   06      2004    232
7   07      2004    228
8   08      2004    280
9   09      2004    277
10  10      2004    335
11  11      2004    273

I am trying to create a new column with the aggregated values for each year for each quarter (i.e. 2004 Q1, 2004 Q2 etc.). I have tried using the function aggregate but have not been successful. Hope you can help me! Regards

Answer 1

1) If DF is the input data.frame convert it to a zoo object z with a "yearmon" index and then aggregate that to "yearqtr":

library(zoo)

toYearmon <- function(y, m) as.yearmon(paste(y, m, sep = "-"))
z <- read.zoo(DF, index = 2:1, FUN = toYearmon)
ag <- aggregate(z, as.yearqtr, sum)

giving:

> ag
2004 Q1 2004 Q2 2004 Q3 2004 Q4 
    875     820     785     608 

2) This would also work:

library(zoo)

yq <- as.yearqtr(as.yearmon(paste(DF$Year, DF$Month), "%Y %m"))
ta <- tapply(DF$DepressionCount, yq, sum)