Time series - aggregate by a custom period of time-CodePudding

New to zoo/xts. I'm trying to go from monthly values to quarterly aggregates, defined as the sum of monthly values over a 3-month period ending on the last available month.

Reproducible example:

library(xts)
months <- as.yearmon(c(paste0("2021-", 11:12),
                       paste0("2022-", 1:10)))
values <- c(23, 21, 45, 63, 12, 45,
            23, 91, 28, 17, 27, 28)

ts <- as.xts(values,
       order.by = months,
       dateFormat="yearmon")
ts
#>          [,1]
#> Nov 2021   23
#> Dec 2021   21
#> Jan 2022   45
#> Feb 2022   63
#> Mar 2022   12
#> Apr 2022   45
#> May 2022   23
#> Jun 2022   91
#> Jul 2022   28
#> Aug 2022   17
#> Sep 2022   27
#> Oct 2022   28

Intended output:

|          | [,1] |
| -------- | ---- |
| Jan 2022 | 89   |
| Apr 2022 | 120  |
| Jul 2022 | 142  |
| Oct 2022 | 72   |

I can't get this to work because the apply.quarterly function uses calendar quarters, rather than generic 3-month periods:


apply.quarterly(ts, sum)
#>          [,1]
#> Dec 2021   44
#> Mar 2022  120
#> Jun 2022  159
#> Sep 2022   72
#> Oct 2022   28

And period.apply doesn't accept custom periods:

period.apply(ts, endpoints(ts, on = "3 months"), sum)
#> Error in match.arg(on, c("years", "quarters", "months", "weeks", "days", : 'arg' should be one of "years", "quarters", "months", "weeks", "days", "hours", "minutes", "seconds", "milliseconds", "microseconds", "ms", "us"

CodePudding user response：

We can use rollaply from zoo package:

> na.omit(rollapply(ts_data, width=3, by=3, FUN = sum))
          [,1]
ene. 2022   89
abr. 2022  120
jul. 2022  142
oct. 2022   72

Note that in your expected output you showed the sum of values not the mean values, so if you want get mean instead, just set FUN=mean:

> na.omit(rollapply(ts_data, width=3, by=3, FUN = mean))
              [,1]
ene. 2022 29.66667
abr. 2022 40.00000
jul. 2022 47.33333
oct. 2022 24.00000

CodePudding user response：

Using your approach (i.e. period.apply and endpoints) with the k-argument:

library(zoo)
library(xts)

period.apply(ts, endpoints(ts, "months", k = 3), sum)

Output:

         [,1]
Jan 2022   89
Apr 2022  120
Jul 2022  142
Oct 2022   72