dplyr中如何实现Excel引用同列上一行单元格的计算逻辑？

问题描述 #

在可复现R代码示例中，需要使用dplyr包创建名为LowClassSplit的列，该列取值依赖同列紧邻上一行的自身计算结果，逻辑等价于对应Excel示例中黄色高亮列的计算规则：F列每个单元格计算时均引用同列正上方的自身单元格值，蓝色单元格为Excel中通过=FORMULATEXT(...)公式展示的F列计算规则。
待解答问题：

• dplyr中是否存在实现该自引用列计算的等价方案？
• 如果dplyr无对应支持，base R中如何实现该需求？

期望输出（与Excel计算结果完全一致） #

# A tibble: 10 x 4
   Element SplitCode PreSplitClass ClassAtSplit
   <chr>       <dbl>         <int>        <dbl>
 1 C               0             1            0
 2 B               0             1            0
 3 D               0             1            0
 4 A               0             1            0
 5 A               1             2            2
 6 A               1             3            2
 7 C               0             2            0
 8 B               0             2            0
 9 B               2             3            0
10 B               2             4            0

初始尝试代码 #

library(dplyr)

data <- 
  data.frame(
    Element = c("C","B","D","A","A","A","C","B","B","B"),
    SplitCode = c(0,0,0,0,1,1,0,0,2,2)  
  )

data <- data %>% group_by(Element) %>% mutate(PreSplitClass=row_number()) %>% ungroup()

data %>%
  mutate(ClassAtSplit =
           case_when(
             SplitCode == 0 ~ as.integer(0), # 无需额外判断值大于0的场景
             SplitCode > lag(SplitCode) ~ PreSplitClass, # 当前值大于上一行值时取当前PreSplitClass
             SplitCode == SplitCode ~ lag(PreSplitClass) # 值相等时取上一行PreSplitClass，已排除0值场景
           )
  )

# 编写LowClassSplit列时遇到自引用问题，以下为未完成的尝试代码
# data %>%
#   mutate(LowClassSplit =
#            case_when(
#              SplitCode == 0 ~ as.integer(0),
#              (SplitCode == lag(SplitCode) && lagitself == 1)|(SplitCode > lag(SplitCode) & PreSplitClass == 1) ~ 1
#            )
#   )

最终实现代码 #

无需编写复杂递归逻辑，纯dplyr流程即可复现Excel的计算结果，完整代码如下：

library(dplyr)

data <- 
  data.frame(
    Element = c("C","B","D","A","A","A","C","B","B","B"),
    SplitCode = c(0,0,0,0,1,1,0,0,2,2)  
  )

excelCopy <- data %>% 
  group_by(Element) %>% 
  mutate(PreSplitClass = row_number()) %>% 
  ungroup() %>%
  mutate(ClassAtSplit =
           case_when(
             SplitCode == 0 ~ as.integer(0),
             SplitCode > lag(SplitCode) ~ PreSplitClass,
             SplitCode == lag(SplitCode) ~ lag(PreSplitClass)
           )
  ) %>%
  mutate(
    LowClassSplit =
      case_when(
        SplitCode > lag(SplitCode) & SplitCode == 1 ~ PreSplitClass,
        SplitCode == lag(SplitCode) & SplitCode == 1 ~ lag(PreSplitClass),
        TRUE ~ 0L
      )
  )

上述解决方案由Rui Barrada提供。

内容的提问来源于stack exchange，提问作者Curious Jorge - user9788072