如何在R中按Group级别创建Col1/Col2的派生指示变量Col_I？

原始数据集 #

data <- structure(list(ID = c(1L, 2L, 2L, 3L, 4L, 4L, 5L, 5L, 6L, 7L, 7L, 7L, 7L), 
                       Group = c("A", "A", "A", "B", "B", "B", "C", "C", "C", "D", "D", "D", "D"), 
                       Col1 = c(1.49, 0.12, NA, NA, NA, NA, 1.53, 0.38, 71.92, 0.25, 0.92, NA, NA), 
                       Col2 = c(NA, NA, 0.35, 0.87, 0.64, 0.43, NA, NA, NA, 0.88, 0.1, 0.53, 0.6)), 
                  class = "data.frame", row.names = c(NA, -13L))

需求说明 #

按Group分组，为每组生成Col_I指示列，标识组内Col1与Col2的取值关系，具体分为四类：

• Only Col1：组内仅Col1存在非NA值，Col2全为NA
• Only Col2：组内仅Col2存在非NA值，Col1全为NA
• Col1, Col2 mutually exclusive：组内Col1和Col2不同时出现非NA值（每行仅一列有值），且两组均存在非NA值
• Col1, Col2 overlap, Col2 majority：组内存在Col1和Col2同时非NA的行，且Col2非NA的行数多于Col1

高效解决方案（dplyr实现） #

利用dplyr的分组计算能力，先统计每组关键特征，再通过条件判断生成Col_I：

library(dplyr)

result <- data %>%
  group_by(Group) %>%
  mutate(
    has_col1 = any(!is.na(Col1)),
    has_col2 = any(!is.na(Col2)),
    has_overlap = any(!is.na(Col1) & !is.na(Col2)),
    count_col1 = sum(!is.na(Col1)),
    count_col2 = sum(!is.na(Col2))
  ) %>%
  mutate(
    Col_I = case_when(
      has_col1 & !has_col2 ~ "Only Col1",
      !has_col1 & has_col2 ~ "Only Col2",
      has_col1 & has_col2 & !has_overlap ~ "Col1, Col2 mutually exclusive",
      has_col1 & has_col2 & has_overlap & count_col2 > count_col1 ~ "Col1, Col2 overlap, Col2 majority"
    )
  ) %>%
  select(-has_col1, -has_col2, -has_overlap, -count_col1, -count_col2) %>%
  ungroup()

print(result)

代码说明 #

1. 按Group分组后，计算每组核心统计量：
   • has_col1/has_col2：组内是否存在对应列的非NA值
   • has_overlap：组内是否存在两列同时非NA的行
   • count_col1/count_col2：组内对应列非NA的行数
2. 通过case_when按优先级判断每组类别，生成Col_I
3. 移除临时统计列，取消分组得到最终结果

高效解决方案（data.table实现） #

针对超大规模数据集，data.table性能更优：

library(data.table)

setDT(data)

result <- data[, 
               c(
                 list(has_col1 = any(!is.na(Col1))),
                 list(has_col2 = any(!is.na(Col2))),
                 list(has_overlap = any(!is.na(Col1) & !is.na(Col2))),
                 list(count_col1 = sum(!is.na(Col1))),
                 list(count_col2 = sum(!is.na(Col2)))
               ), 
               by = Group][
                 data, on = "Group"
               ][, 
                 Col_I := case_when(
                   has_col1 & !has_col2 ~ "Only Col1",
                   !has_col1 & has_col2 ~ "Only Col2",
                   has_col1 & has_col2 & !has_overlap ~ "Col1, Col2 mutually exclusive",
                   has_col1 & has_col2 & has_overlap & count_col2 > count_col1 ~ "Col1, Col2 overlap, Col2 majority"
                 )
               ][, 
                 c("has_col1", "has_col2", "has_overlap", "count_col1", "count_col2") := NULL
               ]

print(result)

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