Search code examples
rsplitlapplyxtsdo.call

Average of month's data (jan-dec) in xts objects

I have this large xts, aggregated monthly with apply.monthly function.

2011-07-31 269.8
2011-08-31 251.0
2011-09-30 201.8
2011-10-31 95.8
2011-11-30 NA
2011-12-31 49.3
2012-01-31 77.1
...

What I want is to calculate the average of Jan-Dec months for all the period. Something like this, but in xts form:

01 541.8
02 23.0
03 34.8
04 12.8
05 21.8
06 44.8
07 22.8
08 55.0
09 287.8
10 15.8
11 113
12 419.3

I want to avoid using dplyr functions like group_by. I think there must be a solution using split and lapply / do.call

I tried spliting the xts in years

xtsobject <- split(xtsobject, f = "years")

and then I dont know how to use properly the lapply function in order to calculate the 12 averages (Jan-Dec) of all the period. This question Group by period.apply() in xts is similar, but in my xts I dont have/want a new column, I think it can be done using the xts index.

Solution

  • Assuming the input data x, shown reproducibly in the Note at the end, useaggregate.zoo like this:

    ag <- aggregate(x, cycle(as.yearmon(time(x))), mean, na.rm = TRUE)
ag

    giving the following zoo series:

    1   77.1
7  269.8
8  251.0
9  201.8
10  95.8
11   NaN
12  49.3

    We could plot it like this:

    plot(ag, type = "h")

    screenshot

    Note

    Lines <- "2011-07-31 269.8
2011-08-31 251.0
2011-09-30 201.8
2011-10-31 95.8
2011-11-30 NA
2011-12-31 49.3
2012-01-31 77.1"

library(xts)
z <- read.zoo(text = Lines)
x <- as.xts(z)