如何在R语言中将含多时段字段的data.frame转换为指定合并格式？

原始数据 #

DATA = data.frame(      
  STUDENT = c(1, 2),
  CLASSROOM = c('A', 'B'),
  TIME1 = c(5, 7),
  TIME2 = c(9, 8),
  TIME3 = c(11, 10),
  ENG1 = c(4, 6),
  ENG2 = c(0, NA),
  ENG3 = c(3, 1),
  MATH1 = c(7, 9),
  MATH2 = c(6, 1),
  MATH3 = c(6, 8)
)

目标数据 #

WANT = data.frame(
  STUDENT = c(1, 2),
  ALL = c("A, 5, 4, 7; 9, 0, 6; 11, 3, 6", "B, 7, 6, 9; 8, NA, 1; 10, 1, 8")
)

解决方案 #

方法一：Base R 逐行处理

通过自定义函数对每一行数据按规则分组拼接字符串：

# 定义分组拼接函数
concat_groups <- function(row) {
  # 第一组：CLASSROOM + TIME1 + ENG1 + MATH1
  group1 <- paste(row[c("CLASSROOM", "TIME1", "ENG1", "MATH1")], collapse = ", ")
  # 第二组：TIME2 + ENG2 + MATH2
  group2 <- paste(row[c("TIME2", "ENG2", "MATH2")], collapse = ", ")
  # 第三组：TIME3 + ENG3 + MATH3
  group3 <- paste(row[c("TIME3", "ENG3", "MATH3")], collapse = ", ")
  # 合并三组，用分号+空格分隔
  paste(group1, group2, group3, collapse = "; ")
}

# 应用函数生成结果
WANT_base <- do.call(rbind, lapply(1:nrow(DATA), function(i) {
  row <- DATA[i, ]
  data.frame(
    STUDENT = row$STUDENT,
    ALL = concat_groups(row),
    stringsAsFactors = FALSE
  )
}))

方法二：Tidyverse 数据重塑后拼接

利用pivot_longer重塑数据结构，再分组拼接，更适配大规模数据场景：

library(tidyverse)

WANT_tidy <- DATA %>%
  # 提取列名中的数字后缀，将TIME/ENG/MATH转为独立列，group标记分组
  pivot_longer(
    cols = -c(STUDENT, CLASSROOM),
    names_to = c(".value", "group"),
    names_pattern = "(TIME|ENG|MATH)(\\d)"
  ) %>%
  # 按学生和分组生成单组字符串
  group_by(STUDENT, group) %>%
  mutate(group_str = if_else(group == "1",
                            str_c(CLASSROOM, TIME, ENG, MATH, sep = ", "),
                            str_c(TIME, ENG, MATH, sep = ", "))) %>%
  # 按学生合并所有分组字符串
  group_by(STUDENT) %>%
  summarise(ALL = str_c(group_str, collapse = "; "), .groups = "drop")

验证结果 #

将目标数据的因子列转为字符列后，可验证结果一致性：

WANT$ALL <- as.character(WANT$ALL)
all.equal(WANT_base, WANT) # 返回TRUE
all.equal(WANT_tidy, WANT) # 返回TRUE

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