如何在dplyr中实现Excel滚动sumifs等效计算？

需要在dplyr中新增SumIfs列，实现类似Excel中锚定范围顶部、逐行滚动计算的SUMIFS逻辑：

• 逐行从上到下遍历数据
• 对当前行及以上的Code2列数值求和
• 求和条件：对应行的Code1值小于当前行的Code1值

举个例子：第6行的计算结果为3，来自第3行（Code1=0 < 3，Code2=1）和第5行（Code1=1 < 3，Code2=2）的Code2数值之和。

可复现代码 #

library(dplyr)

myData <- 
  data.frame(
    Name = c("B","R","R","R","R","B","A","A","A"),
    Group = c(0,1,1,2,2,0,0,0,0),
    Code1 = c(0,1,1,3,3,4,-1,0,0),
    Code2 = c(1,0,2,0,1,2,1,0,0)
  )

# 示例CountIfs函数（供参考）
CountIfs <- function(x,y) {
  out <- integer(length(x))
  for(i in seq_along(x)) {
    cond1 <- y[1:i] > 0
    cond2 <- x[1:i] == x[i] 
    out[i] <- sum(cond1*cond2)
  }
  out
}

myDataRender <- 
  myData %>% 
  mutate(CountIfs = CountIfs(Code1, Code2))
print.data.frame(myDataRender)

解决方案 #

方法1：自定义滚动SumIfs函数 #

参考示例中CountIfs的写法，编写适配需求的SumIfs函数，通过循环实现逐行滚动计算：

SumIfs <- function(code1_col, code2_col) {
  out <- integer(length(code1_col))
  for(i in seq_along(code1_col)) {
    # 筛选当前行及以上、Code1小于当前行Code1的行，对Code2求和
    out[i] <- sum(code2_col[1:i][code1_col[1:i] < code1_col[i]])
  }
  out
}

# 在dplyr中调用函数生成列
myData %>%
  mutate(SumIfs = SumIfs(Code1, Code2)) %>%
  print.data.frame()

方法2：用sapply实现滚动计算 #

如果不想自定义函数，也可以直接用sapply在mutate中实现，注意把范围限制为1:x（当前行及以上）：

myData %>%
  mutate(SumIfs = sapply(1:n(), function(x) sum(Code2[1:x][Code1[1:x] < Code1[x]]))) %>%
  print.data.frame()

非滚动（全范围）SumIfs参考方案（Tsai方案适配版） #

以下是针对Excel中非滚动、锚定全范围的SUMIFS场景的适配代码，供Excel转R用户参考：

myData %>% mutate(SumIfs = sapply(1:n(), function(x) sum(Code2[1:n()][Code1[1:n()] < Code1[x]]))) 

内容的提问来源于stack exchange，提问作者Village.Idyot