I have a tsibble with monthly data which I need to aggregate by three months interval. These are not the "typical" quarters, but rather a custom three-months group made up of the following periods:
Dec-Jan-Feb
Mar-Apr-May
Jun-Jul-Aug
Sept-Oct-Nov
Of course, in the Dec-Jan-Feb case this will span two years (i.e. Dec2023-Jan2024-Feb2024). I am not sure if this is even possible for the index of a tsibble.
Working with tsibbles, I am used to group by time index via the index_by function. However, I am not sure how I should use it with a a custom function. There is an example at the end of the linked help page, but 1) it is different from what I need, and 2) it seems to give an error.
A sample dataset and the way I am used to group it by "normal" quarters.
data <- structure(list(date = structure(c(17897, 17928, 17956, 17987,
18017, 18048, 18078, 18109, 18140, 18170, 18201, 18231, 18262,
18293, 18322, 18353, 18383, 18414, 18444, 18475, 18506, 18536,
18567, 18597, 18628, 18659, 18687, 18718, 18748, 18779, 18809,
18840, 18871, 18901, 18932, 18962, 18993, 19024, 19052, 19083,
19113, 19144, 19174, 19205, 19236, 19266, 19297, 19327, 19358,
19389, 19417, 19448, 19478, 19509, 19539, 19570, 19601, 19631,
19662, 19692, 19723, 17897, 17928, 17956, 17987, 18017, 18048,
18078, 18109, 18140, 18170, 18201, 18231, 18262, 18293, 18322,
18353, 18383, 18414, 18444, 18475, 18506, 18536, 18567, 18597,
18628, 18659, 18687, 18718, 18748, 18779, 18809, 18840, 18871,
18901, 18932, 18962, 18993, 19024, 19052, 19083, 19113, 19144,
19174, 19205, 19236, 19266, 19297, 19327, 19358, 19389, 19417,
19448, 19478, 19509, 19539, 19570, 19601, 19631, 19662, 19692,
19723), class = c("yearmonth", "vctrs_vctr")), geo = c("AT",
"AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT",
"AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT",
"AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT",
"AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT",
"AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT", "AT",
"AT", "AT", "AT", "AT", "AT", "IT", "IT", "IT", "IT", "IT", "IT",
"IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT",
"IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT",
"IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT",
"IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT",
"IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT", "IT"
), value = c(870L, 770L, 870L, 820L, 880L, 850L, 1015L, 985L,
1000L, 1010L, 870L, 1050L, 1395L, 960L, 755L, 320L, 600L, 970L,
1045L, 1360L, 1375L, 1665L, 1395L, 1560L, 1455L, 1775L, 1505L,
1440L, 1810L, 2070L, 3165L, 4700L, 4660L, 5030L, 5725L, 4470L,
3275L, 3205L, 4380L, 4970L, 5855L, 9540L, 11300L, 14495L, 15930L,
18210L, 11745L, 6875L, 4095L, 2480L, 3045L, 3295L, 4140L, 4765L,
5280L, 6680L, 7995L, 9710L, 2310L, 2345L, 1980L, 3175L, 2680L,
2595L, 2565L, 3115L, 2820L, 3075L, 2265L, 3005L, 3890L, 3340L,
2485L, 3060L, 3060L, 850L, 120L, 890L, 1500L, 1810L, 930L, 1055L,
2915L, 2580L, 2560L, 2425L, 2310L, 2660L, 2390L, 2715L, 2960L,
3280L, 3890L, 6610L, 5875L, 5505L, 4575L, 4460L, 5230L, 6130L,
4700L, 6450L, 5690L, 5105L, 5985L, 7920L, 8385L, 9145L, 7995L,
8415L, 9840L, 11335L, 8175L, 11045L, 10730L, 9825L, 10005L, 11930L,
14950L, 14160L, 10160L, 12920L)), class = c("tbl_ts", "tbl_df",
"tbl", "data.frame"), row.names = c(NA, -122L), key = structure(list(
geo = c("AT", "IT"), .rows = structure(list(1:61, 62:122), ptype = integer(0), class = c("vctrs_list_of",
"vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -2L), .drop = TRUE), index = structure("date", ordered = TRUE), index2 = "date", interval = structure(list(
year = 0, quarter = 0, month = 1, week = 0, day = 0, hour = 0,
minute = 0, second = 0, millisecond = 0, microsecond = 0,
nanosecond = 0, unit = 0), .regular = TRUE, class = c("interval",
"vctrs_rcrd", "vctrs_vctr")))
data.q <- data |>
index_by(quarter = ~yearquarter(.)) |>
group_by_key() |>
summarise(geo = first(geo),
value = sum(value))
Add a quarter to your date before indexing. Then Q1 refers to Dec-Jan-Feb, Q2 is Mar-Apr-May etc. Insert a mutate(date = date + month(1)) |>:
data.q <- data |>
mutate(date = date + month(1)) |>
index_by(quarter = ~yearquarter(.)) |>
group_by_key() |>
summarise(geo = first(geo),
value = sum(value))