R dplyr中lag函数使用错误排查：reSeq列结果不符预期

问题说明 #

从Excel迁移到R，尝试用dplyr复刻Excel中Target列的逻辑（分为Step1和Step2），但运行代码后，reSeq列最后一个单元格生成错误值8，按照逻辑应该输出7。

错误输出 #

Element Group eleCnt reSeq
   <chr>   <dbl>  <int> <int>
 1 R           0      1     1
 2 R           0      2     2
 3 X           0      1     1
 4 X           1      2     2
 5 X           1      3     2
 6 X           0      4     4
 7 X           0      5     5
 8 X           0      6     6
 9 B           0      1     1
10 R           0      3     3
11 R           2      4     4
12 R           2      5     4
13 X           3      7     7
14 X           3      8     7
15 X           3      9     8

运行代码 #

library(dplyr)

myDF <- data.frame(
  Element = c("R","R","X","X","X","X","X","X","B","R","R","R","X","X","X"),
  Group = c(0,0,0,1,1,0,0,0,0,0,2,2,3,3,3)
)

myDF %>% 
  group_by(Element) %>%
    mutate(eleCnt = row_number()) %>%
  ungroup()%>%
  mutate(reSeq = eleCnt) %>%
  mutate(reSeq = ifelse(
    Element == lag(Element)& Group == lag(Group) & Group > 0, 
    lag(reSeq),
    eleCnt)
  )

问题原因 #

dplyr的mutate函数在同一批操作中，所有对列的引用都是基于数据框的初始状态，而非逐行更新后的结果。也就是说，第二个mutate里的lag(reSeq)，取的是第一个mutate生成的原始reSeq值（即eleCnt），不是上一行已经修改后的reSeq值。这导致最后一行判断时，lag(reSeq)取的是第14行的原始eleCnt（8），而非更新后的7。

解决方案 #

用purrr::accumulate实现逐行递推逻辑，它会基于前一行的结果计算当前行的值，完全复刻Excel的逐行计算逻辑：

修正后代码 #

library(dplyr)
library(purrr)

myDF <- data.frame(
  Element = c("R","R","X","X","X","X","X","X","B","R","R","R","X","X","X"),
  Group = c(0,0,0,1,1,0,0,0,0,0,2,2,3,3,3)
)

myDF %>% 
  group_by(Element) %>%
  mutate(eleCnt = row_number()) %>%
  ungroup() %>%
  mutate(
    reSeq = accumulate(
      .x = 1:n(),
      .f = function(prev, i) {
        if (i == 1) {
          eleCnt[i]
        } else if (Element[i] == Element[i-1] && Group[i] == Group[i-1] && Group[i] > 0) {
          prev
        } else {
          eleCnt[i]
        }
      }
    )
  )

正确输出 #

Element Group eleCnt reSeq
   <chr>   <dbl>  <int> <int>
 1 R           0      1     1
 2 R           0      2     2
 3 X           0      1     1
 4 X           1      2     2
 5 X           1      3     2
 6 X           0      4     4
 7 X           0      5     5
 8 X           0      6     6
 9 B           0      1     1
10 R           0      3     3
11 R           2      4     4
12 R           2      5     4
13 X           3      7     7
14 X           3      8     7
15 X           3      9     7

如果不想引入purrr包，也可以用rowwise()结合临时变量，但accumulate的写法更简洁高效。

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