如何在R中按ID汇总多行状态？（对标SAS BY语句实现）

需求：按每个ID判断colour列的状态，分为"red only"、"blue only"或"both"，已知SAS可通过BY语句结合first.和last.实现，以下是R中的实现方案。

示例数据 #

生成数据框的代码：

example <- data.frame(id = c("A1", "A1", "A1", "A2", "A3", "A3", "A4", "A4", "A4", "A5", "A5", "A6"), 
                      colour = c("red", "red", "blue", "red", "blue", "blue", "red", "red", "red", "red", "blue", "red"))

原始数据输出：

id colour
1  A1    red
2  A1    red
3  A1   blue
4  A2    red
5  A3   blue
6  A3   blue
7  A4    red
8  A4    red
9  A4    red
10 A5    red
11 A5   blue
12 A6    red

期望结果 #

id    status
1 A1      both
2 A2  red only
3 A3 blue only
4 A4  red only
5 A5      both
6 A6  red only

SAS实现参考代码 #

data table1 (keep=id status);
set example;
by id;
retain red_count blue_count;

if first.id then do;
  red_count = 0;
  blue_count = 0;
end;

if colour = "red" then red_count+1;
if colour = "blue" then blue_count+1;

if last.id then do;
  if red_count > 0 and blue_count > 0 then status = "both";
  else if red_count > 0  then status = "red only";
  else if blue_count > 0 then status = "blue only";
  output;
end;

run;

R实现方案 #

方法一：使用dplyr包（tidyverse生态） #

这是最常用的 tidy 风格实现，逻辑清晰直观：

library(dplyr)

# 处理数据
result <- example %>%
  # 按ID分组
  group_by(id) %>%
  # 计算组内是否存在red和blue
  summarise(
    has_red = any(colour == "red"),
    has_blue = any(colour == "blue"),
    .groups = "drop" # 取消分组状态
  ) %>%
  # 根据存在情况生成状态
  mutate(
    status = case_when(
      has_red & has_blue ~ "both",
      has_red ~ "red only",
      has_blue ~ "blue only",
      TRUE ~ "none" # 处理无颜色的边缘情况
    )
  ) %>%
  # 保留需要的列
  select(id, status)

print(result)

方法二：Base R实现 #

无需加载额外包，用原生函数完成：

# 按ID分组，提取每个ID的唯一colour值
colour_groups <- tapply(example$colour, example$id, unique)

# 生成结果数据框
result_base <- data.frame(
  id = names(colour_groups),
  status = sapply(colour_groups, function(cols) {
    has_red <- "red" %in% cols
    has_blue <- "blue" %in% cols
    if (has_red & has_blue) "both"
    else if (has_red) "red only"
    else if (has_blue) "blue only"
    else "none"
  })
)

# 按ID排序，和示例结果顺序一致
result_base <- result_base[order(result_base$id), ]
# 重置行名
rownames(result_base) <- NULL

print(result_base)

逻辑对比 #

SAS中需要手动用retain维护计数变量，通过first.id初始化、last.id触发输出；而R的分组聚合操作直接封装了组内逻辑，无需手动处理组的起始/结束标识，只需关注组内需要判断的条件即可。

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