R语言中按ID和PROC分组填充FINAL变量的实现求助

原始数据 #

DF_1 <- data.frame(ID = c("A","A","A","B","B","B","C","C","C ","D","D","D"),
                 PROC = c("11","22","33","44","55","66","77","88","99","10","20","30 "),
                 COLOR = c("OK","OK","OK","OK","PENDING","OK","PENDING","PENDING","PENDING","OK","OK","PENDING "))

需求 #

按ID和PROC分组，检查每组COLOR中是否存在"PENDING"值：

• 存在则新变量FINAL赋值为"PENDING"
• 不存在则赋值为"OK"

原代码问题分析 #

1. 管道符语法错误：%>应为%>%，这会直接导致代码无法运行
2. 字符串空格干扰：原始数据中部分字符串带首尾空格（如ID的"C "、COLOR的"PENDING "），直接用"PENDING" %in% COLOR会匹配不到带空格的目标值，导致判断逻辑失效

正确解决方案 #

步骤1：清理字符串首尾空格 #

先统一清理所有字符列的空格，避免匹配错误：

# 使用tidyverse的across批量处理
library(dplyr)
DF_1 <- DF_1 %>%
  mutate(across(c(ID, PROC, COLOR), trimws))

# 或用base R逐个处理
# DF_1$ID <- trimws(DF_1$ID)
# DF_1$PROC <- trimws(DF_1$PROC)
# DF_1$COLOR <- trimws(DF_1$COLOR)

步骤2：分组生成FINAL变量 #

修正管道符后，用any(COLOR == "PENDING")或"PENDING" %in% COLOR判断组内是否存在目标值：

DF_1 <- DF_1 %>%
  group_by(ID, PROC) %>%
  mutate(FINAL = ifelse(any(COLOR == "PENDING"), "PENDING", "OK")) %>%
  ungroup()

最终结果 #

DF_1
#>    ID PROC  COLOR   FINAL
#> 1   A   11     OK      OK
#> 2   A   22     OK      OK
#> 3   A   33     OK      OK
#> 4   B   44     OK  PENDING
#> 5   B   55 PENDING  PENDING
#> 6   B   66     OK  PENDING
#> 7   C   77 PENDING  PENDING
#> 8   C   88 PENDING  PENDING
#> 9   C   99 PENDING  PENDING
#> 10  D   10     OK  PENDING
#> 11  D   20     OK  PENDING
#> 12  D   30 PENDING  PENDING

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