按STARlevel分组跨月份计算newfacil累计值的R语言技术问题

先看你提供的原始数据：

temp<-structure(list(App_Month = structure(c(1L, 2L, 2L, 2L, 2L, 3L, 
            3L, 3L, 3L, 4L, 4L, 4L, 4L, 5L), levels = c("Feb 2020", "Mar 2020", "Apr 2020", "May 2020", "Jun 2020", "Jul 2020", "Aug 2020", "Sep 2020", "Oct 2020", "Nov 2020", "Dec 2020", "Jan 2021", "Feb 2021", "Mar 2021", "Apr 2021", "May 2021", "Jun 2021", "Jul 2021", "Aug 2021", "Sep 2021", "Oct 2021", "Nov 2021", "Dec 2021", "Jan 2022", "Feb 2022", "Mar 2022", "Apr 2022", "May 2022", "Jun 2022", "Jul 2022", "Aug 2022", "Sep 2022", "Oct 2022", "Nov 2022", "Dec 2022", "Jan 2023", "Feb 2023"), class = c("ordered", "factor")), STARlevel = c("STAR 2", "STAR 1", "STAR 2", "STAR 3", "STAR 4", "STAR 1", "STAR 2", "STAR 3", "STAR 4", "STAR 1", "STAR 2", 
  "STAR 3", "STAR 4", "STAR 1"),  newfacil = c(2L, 1L, 3L, 3L, 3L, 25L, 34L, 51L, 36L, 11L, 16L, 19L, 41L, 13L)), row.names = c(NA, -14L), class = c("tbl_df", "tbl", "data.frame"))

temp$App_Month<-factor(temp$App_Month,levels=c("Feb 2020", "Mar 2020", "Apr 2020", "May 2020", "Jun 2020", "Jul 2020", "Aug 2020", "Sep 2020","Oct 2020", "Nov 2020", "Dec 2020", "Jan 2021", "Feb 2021", "Mar 2021", "Apr 2021", "May 2021","Jun 2021", "Jul 2021", "Aug 2021", "Sep 2021", "Oct 2021", "Nov 2021", "Dec 2021", "Jan 2022","Feb 2022", "Mar 2022", "Apr 2022", "May 2022", "Jun 2022", "Jul 2022", "Aug 2022", "Sep 2022","Oct 2022", "Nov 2022", "Dec 2022", "Jan 2023", "Feb 2023"),ordered = T)


temp$STARlevel<-factor(temp$STARlevel, levels=c("STAR 1","STAR 2", "STAR 3","STAR 4"),ordered=T )

correct<-c(2,1,6,3,3,26,42,54,39,37,45,73,80,50)
temp<-cbind(temp,correct)

为什么之前的写法不对？ #

你之前的data.table代码里，by=list(STARlevel,App_Month)是同时按星级+月份分组，这会把每个月每个星级的记录单独分成一个小组。比如Mar 2020的STAR 1是一个组，Apr 2020的STAR 1又是另一个组，cumsum只会在每个小组内计算总和，自然得不到跨月份的累计值。

要实现跨月份的滚动累计，核心是：

• 只按STARlevel分组
• 保证同组内的记录是按App_Month从早到晚排序的（因为cumsum是按行顺序计算的）

正确的实现方法 #

1. data.table方案 #

先按星级、月份排序，再按星级分组计算累计：

library(data.table)
tempDT <- data.table(temp)
# 先按星级、月份升序排序
tempDT <- tempDT[order(STARlevel, App_Month)]
# 按星级分组计算滚动累计
tempDT[, CumulativeSums := cumsum(newfacil), by = STARlevel]
# 还原原始顺序（如果需要）
tempDT <- tempDT[order(App_Month, STARlevel)]

2. dplyr方案 #

同样先排序，再分组累计：

library(dplyr)
temp <- temp %>%
  arrange(STARlevel, App_Month) %>%
  group_by(STARlevel) %>%
  mutate(CumulativeSums = cumsum(newfacil)) %>%
  ungroup() %>%
  arrange(App_Month, STARlevel) # 还原原始顺序

运行后你会发现CumulativeSums列和你给出的correct列完全一致。

内容的提问来源于stack exchange，提问作者Ben