在R中基于另一数据框将考勤列聚合为新分组列的实现方法

首先还原问题中的两个数据框，方便直接复现场景：

# 创建考勤数据框df1
df1 <- data.frame(
  No. = c("A001", "A002", "A003", "A004", "A005"),
  grade1 = c("V", "X", "V", "X", "V"),
  grade2 = c("X", "V", "X", "X", "X"),
  grade3 = c("V", "V", "X", "V", "V"),
  grade4 = c("V", "V", "V", "V", "X"),
  grade5 = c("X", "X", "V", "X", "X"),
  grade6 = c("X", "V", "V", "V", "X"),
  stringsAsFactors = FALSE
)

# 创建分组映射数据框df2
df2 <- data.frame(
  group = c("grade1", "grade2", "grade3", "grade4", "grade5", "grade6"),
  new_group = c("grade20", "grade20", "grade21", "grade21", "grade22", "grade22"),
  stringsAsFactors = FALSE
)

需求说明 #

根据df2的分组规则聚合df1的列：

• 分组内只要存在一个V，新分组列标记为V
• 仅当分组下**所有列均为X**时，新分组列才标记为X（若存在NA，视为考勤记录缺失，不满足"全X"条件，需保留NA或按需调整逻辑）

实现方案1：tidyverse 工具链（推荐） #

用dplyr+tidyr完成数据重塑与聚合，逻辑清晰易维护：

library(tidyverse)

# 1. 将宽格式转为长格式，便于按分组处理
df_long <- df1 %>%
  pivot_longer(cols = -No., names_to = "group", values_to = "status")

# 2. 关联分组映射规则
df_joined <- df_long %>%
  left_join(df2, by = "group")

# 3. 按学生和新分组聚合，判断最终状态
df_result <- df_joined %>%
  group_by(No., new_group) %>%
  summarise(
    status = case_when(
      # 只要有V就标记V
      any(status == "V", na.rm = TRUE) ~ "V",
      # 全为X才标记X，排除NA干扰
      all(status == "X", na.rm = TRUE) ~ "X",
      # 存在NA且无V的情况，返回NA（可按需修改）
      TRUE ~ NA_character_
    ),
    .groups = "drop"
  ) %>%
  # 转回宽格式，得到目标结果
  pivot_wider(names_from = new_group, values_from = status)

# 查看结果
print(df_result)

运行后输出与期望完全一致：

# A tibble: 5 × 4
  No.   grade20 grade21 grade22
  <chr> <chr>   <chr>   <chr>  
1 A001  V       V       X      
2 A002  V       V       V      
3 A003  V       V       X      
4 A004  X       V       V      
5 A005  V       V       X      

实现方案2：基础R（无需第三方包） #

如果不想依赖外部包，用基础R也能完成：

# 提取分组映射关系：新分组对应原列的列表
group_map <- split(df2$group, df2$new_group)

# 初始化结果数据框，保留学生编号
result_df <- df1[, "No.", drop = FALSE]

# 遍历每个新分组，逐行判断状态
for (new_grp in names(group_map)) {
  target_cols <- group_map[[new_grp]]
  # 判断每行是否有V
  has_v <- apply(df1[, target_cols], 1, function(x) any(x == "V", na.rm = TRUE))
  # 判断每行是否全为X
  all_x <- apply(df1[, target_cols], 1, function(x) all(x == "X", na.rm = TRUE))
  # 赋值新列
  result_df[[new_grp]] <- ifelse(has_v, "V", ifelse(all_x, "X", NA))
}

# 查看结果
print(result_df)

特殊场景处理：NA视为缺课 #

如果业务需要将NA等同于X（即未记录视为缺课），只需修改聚合逻辑中的判断条件：

# 以tidyverse方案为例，修改summarise部分
summarise(
  status = case_when(
    any(status == "V", na.rm = TRUE) ~ "V",
    # 将NA视为X，判断所有非空值都是X
    all(status %in% c("X", NA)) ~ "X",
    TRUE ~ NA_character_
  ),
  .groups = "drop"
)

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