R эквивалент SQL SUM OVER PARTITION BY ROWS PRECEDING
У меня возникли проблемы с попыткой воспроизвести оконные функции SQL в R, в частности, в отношении создания итоговых сумм, которые указывают количество предыдущих месяцев, которые я хочу суммировать.
Хотя пакет sqldf в R позволяет манипулировать данными, он, похоже, не поддерживает оконные функции.
У меня есть фиктивные данные в R
set.seed(10)
data_1 <- data.table(Cust_ID = c(1,1,1,1,2,2,2,2,3,3,3,3),Month=c(4,3,2,1,4,3,2,1,4,3,2,1),
StatusCode=LETTERS[4:6],SalesValue=round(runif (12,50,1500)))
Cust_ID Month StatusCode SalesValue
1 4 D 786
1 3 E 495
1 2 F 669
1 1 D 1055
2 4 E 173
2 3 F 377
2 2 D 448
2 1 E 445
3 4 F 943
3 3 D 673
3 2 E 995
3 1 F 873
Для каждой строки я хотел бы создать совокупную сумму значений, относящихся к клиенту (Cust_ID), за предыдущие 2 месяца (не включая текущий месяц).
Это будет означать, что для каждого покупателя строки с месяцами 1 и 2 должны быть нулевыми (учитывая, что предшествующих месяцев не было 2), в 3-м месяце должна содержаться сумма продаж за 1 и 2 месяцев для этого клиента, а в 4-м месяце должна содержаться сумма продаж. Стоимость для 2 и 3 месяцев.
В SQL я бы использовал синтаксис, подобный следующему: SUM (SalesValue) OVER (РАЗДЕЛЕНИЕ ПО Cust_ID ORDER BY MONTH DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) as PAST_3Y_SALES
Можно ли добиться этого в R - в идеале, используя data.table (для эффективности)? Любое руководство будет очень признательно.
PS Примечание: это фиктивные данные, в моих `` реальных '' данных у клиентов есть разные объемы данных - то есть у некоторых клиентов есть данные за 5 месяцев, у других - данные за> 36 месяцев и т. д.
Ответы 5
Вот решение с использованием dplyr
library(dplyr)
library(zoo)
as.data.frame(data_1) %>% group_by(Cust_ID) %>% arrange(Cust_ID, Month) %>%
mutate(Sum_prev =rollapplyr(SalesValue, list(-(1:2)), sum, fill = NA))
# A tibble: 12 x 5
# Groups: Cust_ID [3]
Cust_ID Month StatusCode SalesValue Sum_prev
<dbl> <dbl> <chr> <dbl> <dbl>
1 1 1 D 1055 NA
2 1 2 F 669 NA
3 1 3 E 495 1724
4 1 4 D 786 1164
5 2 1 E 445 NA
6 2 2 D 448 NA
7 2 3 F 377 893
8 2 4 E 173 825
9 3 1 F 873 NA
10 3 2 E 995 NA
11 3 3 D 673 1868
12 3 4 F 943 1668
Используя data.table:
library(data.table)
library(zoo)
#dt <- data_1[order(Cust_ID,Month)]
#dt[, Sum_prev:= rollapplyr(SalesValue, list(-(1:2)), sum, fill = NA), by=Cust_ID][]
#OR Without chaining
data_1[, Sum_prev := rollapplyr(SalesValue, list((1:2)), sum, fill = NA), by = Cust_ID][order(Cust_ID,Month)]
Cust_ID Month StatusCode SalesValue Sum_prev
1: 1 1 D 1055 NA
2: 1 2 F 669 NA
3: 1 3 E 495 1724
4: 1 4 D 786 1164
5: 2 1 E 445 NA
6: 2 2 D 448 NA
7: 2 3 F 377 893
8: 2 4 E 173 825
9: 3 1 F 873 NA
10: 3 2 E 995 NA
11: 3 3 D 673 1868
12: 3 4 F 943 1668
@MKR для любого решения сначала потребуется отсортировать данные, поскольку OP предоставляет несортированный набор данных. Однако в моем решении [order(Cust_ID,Month)] будет сначала сортировать, а затем длинный data_1[ ] выполнит расчет, как и в выражениях цепочки data.table, то есть flights[carrier == "AA", .N, by = .(origin, dest)][order(origin, -dest)]. Я прав или я что-то здесь упустил ?. Спасибо
Обратите внимание, что если вы используете список смещений в качестве ширины, вы можете просто использовать rollapply, а не rollapplyr, поскольку выравнивание, указанное r, будет отменено шириной.
Решение data.table:
# sort the data first if the Month column is not ordered for any Cust_ID
data_1 <- data_1[order(Cust_ID, Month)]
# sum up the value of two previous Month for each Cust_ID
data_1[, rsum := shift(SalesValue, 1) + shift(SalesValue, 2), by = Cust_ID]
# Cust_ID Month StatusCode SalesValue rsum
# 1: 1 1 D 1055 NA
# 2: 1 2 F 669 NA
# 3: 1 3 E 495 1724
# 4: 1 4 D 786 1164
# 5: 2 1 E 445 NA
# 6: 2 2 D 448 NA
# 7: 2 3 F 377 893
# 8: 2 4 E 173 825
# 9: 3 1 F 873 NA
# 10: 3 2 E 995 NA
# 11: 3 3 D 673 1868
# 12: 3 4 F 943 1668
Вероятно, всего две операции shift и сумма сделают это решение быстрее.
@MKR. Полагаю, что так. Поэтому я заменил свое исходное решение rowSums на решение с двумя shift :). И в случае, если необходимо суммировать более двух месяцев, метод скользящей суммы (RcppRoll или zoo) будет более эффективным, чем подход rowSums. Думаю, надо удалить решение rowSums.
Поскольку OP использовал data.table, следовательно, решение, использующее RcppRoll::roll_sumr в рамках data.table, может быть таким:
library(data.table)
library(RcppRoll)
# Order on 'Cust_ID' and 'Month'
setkeyv(data_1,c("Cust_ID","Month"))
data_1[, Sum_prev:=shift(roll_sumr(SalesValue, n=2)), by=Cust_ID]
data_1
# Cust_ID Month StatusCode SalesValue Sum_prev
# 1: 1 1 D 1055 NA
# 2: 1 2 F 669 NA
# 3: 1 3 E 495 1724
# 4: 1 4 D 786 1164
# 5: 2 1 E 445 NA
# 6: 2 2 D 448 NA
# 7: 2 3 F 377 893
# 8: 2 4 E 173 825
# 9: 3 1 F 873 NA
# 10: 3 2 E 995 NA
# 11: 3 3 D 673 1868
# 12: 3 4 F 943 1668
Подход состоит в том, чтобы сначала вычислить сумму с шириной как 2, а затем взять предыдущее значение, используя data.table::shift с lag для текущей строки, имеющей сумму предыдущих 2 строк.
Работает очень хорошо, похоже, пакет RccpRoll - это именно то, что я искал! Спасибо :)
@ G.Grothendieck Я думаю, что OP только что сослался на SQL, чтобы сообщить о типе своих требований, в противном случае он очень четко упомянул Months 1 & 2 should be null . Означает сумму предыдущих 2-х строк.
быстрый последующий вопрос @MKR. Если бы я хотел изменить запрос, чтобы вычислить сумму всех результатов за предыдущий месяц (а не скользящее окно за 2 месяца), как этого можно было бы достичь?
1) sqldf / RpostgreSQL Вы можете использовать оконные функции с серверной частью PostgreSQL и своим кодом (слегка измененным для работы) в R, как это (где data_1 - это фрейм данных в вашем рабочем пространстве).
library(RPostgreSQL)
library(sqldf)
sql <- 'select *, SUM("SalesValue") OVER (PARTITION BY "Cust_ID"
ORDER BY "Month" DESC
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING ) as PAST_3Y_SALES
from "data_1"'
sqldf(sql)
давая:
Cust_ID Month StatusCode SalesValue past_3y_sales
1 1 4 D 786 NA
2 1 3 E 495 786
3 1 2 F 669 1281
4 1 1 D 1055 1164
5 2 4 E 173 NA
6 2 3 F 377 173
7 2 2 D 448 550
8 2 1 E 445 825
9 3 4 F 943 NA
10 3 3 D 673 943
11 3 2 E 995 1616
12 3 1 F 873 1668
2) data.table / rollapply
В качестве альтернативы используйте data.table с rollapply, указав ширину как смещения с помощью list(-2:-1).
Приведенный ниже код был написан так, чтобы соответствовать коду SQL в вопросе, но если вы хотите вместо этого иметь два NA для каждого Cust_ID, а не один, и суммировать предыдущие месяцы, где месяцы расположены в возрастающем порядке (не по убыванию, как указано в вопросе) SQL), затем измените -Month на Month в операторе setorder и удалите аргумент partial=TRUE в rollapply.
library(data.table)
library(zoo)
setorder(data_1, Cust_ID, -Month)
roll <- function(x) rollapply(x, list(-2:-1), sum, partial = TRUE, fill = NA)
data_1[, past_3y_sales := roll(SalesValue), by = Cust_ID]
давая:
> data_1
Cust_ID Month StatusCode SalesValue past_3y_sales
1: 1 4 D 786 NA
2: 1 3 E 495 786
3: 1 2 F 669 1281
4: 1 1 D 1055 1164
5: 2 4 E 173 NA
6: 2 3 F 377 173
7: 2 2 D 448 550
8: 2 1 E 445 825
9: 3 4 F 943 NA
10: 3 3 D 673 943
11: 3 2 E 995 1616
12: 3 1 F 873 1668
У меня была аналогичная проблема, но приведенные выше решения мне не помогли. Мои данные были data_1:
CIF_ID LEAD_RESULT
10000009 1
10000009 0
10000025 0
10000025 0
10000055 0
И мне нужно было просуммировать LEAD_RESULT по CIF_ID.
В library(data.table) я сделал следующее:
dt <- data.table::as.data.table(data_1)
dt<-dt[, group_sum := sum(LEAD_RESULT), by = "CIF_ID"][]
dt
Результат:
CIF_ID LEAD_RESULT group_sum
10000009 1 1
10000009 0 1
10000025 0 0
10000025 0 0
10000055 0 0
@ASuliman Решение на основе
data.tableможет не дать правильный ответ, пока данные не будут отсортированы перед применениемrollapplyr. Не могли бы вы еще раз проверить свой ответ?