Нужна помощь в вычислении скользящих сумм в R для групп
У меня есть таблица данных с 4 столбцами: ID, Cat, Date и Val. Я хотел бы создать 2 новых столбца в этой таблице, в которых будут скользящие суммы Val за 3 месяца и 12 месяцев, сгруппированные по ID и Cat. В идеале мне нужно решение data.table, так как у меня очень большой набор данных. Кроме того, у меня могут отсутствовать данные за определенные месяцы для определенной группы ID/Cat, поэтому этот месяц следует пропустить при расчете.
Это создаст образец таблицы данных для работы.
sample_data <- as.data.frame(matrix(nrow =34, ncol = 4))
colnames(sample_data) <- c("ID", "Cat", "Date", "Val")
sample_data$ID <- c("PMM", "PMM", "PMM", "PMM", "PMM",
"PMM", "PMM", "PMM", "PMM", "PMM",
"PMM", "PMM", "PMM", "LCG", "LCG",
"LCG", "LCG", "LCG", "LCG", "LCG",
"LCG", "LCG", "LCG", "LCG", "LCG",
"LCG", "LCG", "LCG", "LCG", "LCG",
"LCG", "LCG", "LCG", "LCG")
sample_data$Cat <- c("MM", "MM", "MM", "MM", "MM",
"MM", "MM", "MM", "MM", "MM",
"MM", "MM", "MM", "OE", "OE",
"OE", "OE", "OE", "OE", "OE",
"OE", "OE", "OE", "OE", "OE",
"OE", "OE", "OE", "OE", "OE",
"OE", "OE", "OE", "OE")
sample_data$Date <- c(
"2018-12-31", "2018-11-30", "2018-10-31", "2018-09-30", "2018-08-31",
"2018-07-31", "2018-06-30", "2018-05-31", "2018-04-30", "2018-03-31",
"2018-02-28", "2018-01-31", "2017-12-31", "2018-12-31", "2018-11-30",
"2018-10-31", "2018-09-30", "2018-08-31", "2018-07-31", "2018-06-30",
"2018-05-31", "2018-04-30", "2018-03-31", "2018-02-28", "2018-01-31",
"2017-12-31", "2017-11-30", "2017-10-31", "2017-09-30", "2017-08-31",
"2017-07-31", "2017-06-30", "2017-05-31", "2017-04-30")
sample_data$Val <- c(-11, 84, 74, 80, -9,
-40, -76, -47, -50, -50,
97, 42, 44, 53, 1,
13, 65, 52, -5, 75,
-41, -6, 8, -79, 53,
22, -100, -57, -89, 28,
37, -24, 17, -53)
Я искал различные решения stackoverflow, но не могу заставить что-либо работать. Большинство решений имеют дело с группой только в одном столбце, а не в нескольких, и выполняют только одну скользящую сумму, а не несколько.
Вот то, что я начал, но не смог правильно реализовать.
sample_data <- sample_data[, cumsum3mo := sample_data[.(ID, Cat, Date, (Date - day(Date)+1) %m-% months(2) - 1),
on = .(ID = V1, Cat = V2, Date <= V3, Date > V4),
sum(Val), by = .EACHI]][]
Это ожидаемый результат:
sample_data <- as.data.frame(matrix(nrow =34, ncol = 6))
colnames(sample_data) <- c("ID", "Cat", "Date", "Val", "cumsum3mo", "cumsum12mo")
sample_data$ID <- c("PMM", "PMM", "PMM", "PMM", "PMM",
"PMM", "PMM", "PMM", "PMM", "PMM",
"PMM", "PMM", "PMM", "LCG", "LCG",
"LCG", "LCG", "LCG", "LCG", "LCG",
"LCG", "LCG", "LCG", "LCG", "LCG",
"LCG", "LCG", "LCG", "LCG", "LCG",
"LCG", "LCG", "LCG", "LCG")
sample_data$Cat <- c("MM", "MM", "MM", "MM", "MM",
"MM", "MM", "MM", "MM", "MM",
"MM", "MM", "MM", "OE", "OE",
"OE", "OE", "OE", "OE", "OE",
"OE", "OE", "OE", "OE", "OE",
"OE", "OE", "OE", "OE", "OE",
"OE", "OE", "OE", "OE")
sample_data$Date <- c("2018-12-31", "2018-11-30", "2018-10-31", "2018-09-30", "2018-08-31",
"2018-07-31", "2018-06-30", "2018-05-31", "2018-04-30", "2018-03-31",
"2018-02-28", "2018-01-31", "2017-12-31", "2018-12-31", "2018-11-30",
"2018-10-31", "2018-09-30", "2018-08-31", "2018-07-31", "2018-06-30",
"2018-05-31", "2018-04-30", "2018-03-31", "2018-02-28", "2018-01-31",
"2017-12-31", "2017-11-30", "2017-10-31", "2017-09-30", "2017-08-31",
"2017-07-31", "2017-06-30", "2017-05-31", "2017-04-30")
sample_data$Val <- c(-11, 84, 74, 80, -9,
-40, -76, -47, -50, -50,
97, 42, 44, 53, 1,
13, 65, 52, -5, 75,
-41, -6, 8, -79, 53,
22, -100, -57, -89, 28,
37, -24, 17, -53)
sample_data$cumsum3mo <- c(147, 238, 145, 31, -125,
-163, -173, -147, -3, 89,
183, 86, 44, 67, 79,
130, 112, 122, 29, 28,
-39, -77, -18, -4, -25,
-135, -246, -118, -24, 41,
30, -60, -36, -53)
sample_data$cumsum12mo <- c(94, 149, 65, -9, -89,
-80, -40, 36, 83, 133,
183, 86, 44, 189, 158,
57, -13, -167, -191, -149,
-248, -190, -237, -245, -166,
-219, -241, -141, -84, 5,
-23, -60, -36, -53)
Ответы 1
Предполагая, что ваши данные на конец месяца, это должно помочь вам начать:
library(data.table)
setDT(sample_data)
sample_data[, Date := as.Date(Date, format = "%Y-%m-%d")]
sample_data[, c("cumsum3mo", "cumsum12mo") := .(
sapply(Date, function(d) sum(Val[between(Date, seq(d+1L, by = "-3 month", len=2L)[2L], d)])),
sapply(Date, function(d) sum(Val[between(Date, seq(d+1L, by = "-12 months", len=2L)[2L], d)]))
),
by=.(ID, Cat)]
Спасибо. Кажется, это работает (позже я проверю свой большой набор данных). Не могли бы вы объяснить часть [между (дата, последовательность (d + 1L, by = "-3 месяца", len = 2L) [2L], d)]. Я понимаю, что Val суммируется между двумя датами (дата и дата 3 месяца назад). Однако я не понимаю, как в инструкции seq получается дата 3 месяца назад.
3 месяца назад с даты окончания месяца не всегда дают вам дату окончания месяца. Использование 1-го числа месяца более надежно. Следовательно, например, при использовании 31-го декабря код последовательности дает 1-й окт.
Понял, спасибо!. Был перепутан с функцией «seq», но похоже, что она просто строит поток дат на основе поля «Дата» с интервалом -3 месяца или -12 месяцев и продолжает это всего за 2 интервала (len = 2L), и возвращает только второй ([2L]). Думаю, я мог бы построить этот диапазон дат и другими способами, но это работает.
Я получил приемлемые результаты, используя
dplyrдля данных в миллионах строк, используя что-то вродеmutate(cuml = cumsum(Val), cumsum3mo = Val - lag(Val, 3), cumsum12mo = Val - lag(Val, 12)).