在R中合并宽长数据集为单参与者行的宽格式数据集

现有两个数据集：

• 参与者表现数据集：每行对应一位参与者
• 参与者评分数据集：一位参与者对应多行（每行对应一位评分者）

需求：将两个数据集合并为每行对应一位参与者的宽格式数据集（保留NA值），并将评分者名称与评分项列名组合为新列（如Fran_Rating1）。

可复现示例数据 #

# 参与者表现数据集
pPerformance <- data.frame(
  Participant = c(1, 2, 3, 4, 5),
  Session = c("A", "B", "B", "A", "C"),
  Group = c(1, 1, 2, 1, 1),
  Answer1 = c("incorrect", "correct", "correct", "incorrect", "correct"),
  Condition = c("chat", "essay", "essay", "chat", "essay"),
  Answer2 = c("correct", "correct", "correct", "incorrect", "incorrect"),
  Duration = c(96, 43, 56, 75, 23)
)

# 参与者评分数据集
pRatings <- data.frame(
  Participant = c(1, 2, 3, 4, 5, 1, 2, 3, 4, 5),
  Session = c("A", "B", "B", "A", "C", "A", "B", "B", "A", "C"),
  Group = c(1, 1, 2, 1, 1, 1, 1, 2, 1, 1),
  Rater = c("Fran", "Fran", "Fran", "Fran", "Fran",
            "Fred", "Fred", "Fred", "Fred", "Fred"),
  Rating1 = c("Yes", "No", "Yes", "Yes", "No", "No", "Yes", "Yes", "Yes", NA),
  Rating2 = c(3, 0, 1, 2, 0, 2, 1, 1, 2, 1)
)

# 期望的合并后数据集
pMerged <- data.frame(
  Participant = c(1, 2, 3, 4, 5),
  Session = c("A", "B", "B", "A", "C"),
  Group = c(1, 1, 2, 1, 1),
  Answer1 = c("incorrect", "correct", "correct", "incorrect", "correct"),
  Condition = c("chat", "essay", "essay", "chat", "essay"),
  Answer2 = c("correct", "correct", "correct", "incorrect", "incorrect"),
  Duration = c(96, 43, 56, 75, 23),
  Fran_Rating1 = c("Yes", "No", "Yes", "Yes", "No"),
  Fred_Rating1 = c("No", "Yes", "Yes", "Yes", NA),
  Fran_Rating2 = c(3, 0, 1, 2, 0),
  Fred_Rating2 = c(2, 1, 1, 2, 1)
)

解决方案 #

直接按Participant合并会生成重复行，需先将评分数据集转换为宽格式，再与表现数据集合并：

步骤1：将评分数据集转为宽格式 #

使用tidyr包的pivot_wider()函数，将Rater的值作为评分列的前缀，生成目标格式的列名：

library(tidyr)
library(dplyr)

# 转换评分数据集为宽格式
pRatings_wide <- pRatings %>%
  pivot_wider(
    id_cols = c(Participant, Session, Group), # 确保每个参与者的唯一标识
    names_from = Rater,
    values_from = c(Rating1, Rating2),
    names_glue = "{Rater}_{.value}" # 拼接评分者与评分项为新列名
  )

步骤2：合并两个数据集 #

使用dplyr包的left_join()，基于共同的唯一键合并，确保每个参与者仅保留一行：

# 合并数据集
pMerged_result <- pPerformance %>%
  left_join(pRatings_wide, by = c("Participant", "Session", "Group"))

验证结果 #

运行上述代码后，pMerged_result与期望的pMerged完全一致，包含所有目标列，无重复行，NA值保留正确。

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