如何为test_frame2添加基于test_frame1转置重复值的ElementType列？

现有两个以ID关联的数据框：

test_frame1 <- data.frame(
  ID = c("A","B","C"),
  ElementType1 = c(1,6,1),
  ElementType2= c(4,4,5),
  ElementType3 = c('',6,1),
  Notes = c("Something random","","Something else random")
)

test_frame2 <- data.frame(
  ID = c("A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","C","C","C","C","C","C","C","C","C"),
  Syllable = c(1,1,2,2,3,3,1,1,1,2,2,2,3,3,3,1,1,1,2,2,2,3,3,3),
  ElementID = c(1,2,1,2,1,2,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3)
)

需要为test_frame2新增一列ElementType，取值规则为：

• 对应ID匹配test_frame1中的记录
• ElementType的值来自test_frame1的ElementType1、ElementType2、ElementType3列，且每个不同的Syllable下重复对应值，最终目标结果如下：

desired_frame <- data.frame(
  ID = c("A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","C","C","C","C","C","C","C","C","C"),
  Syllable = c(1,1,2,2,3,3,1,1,1,2,2,2,3,3,3,1,1,1,2,2,2,3,3,3),
  ElementID = c(1,2,1,2,1,2,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3),
  ElementType= c(1,4,1,4,1,4,6,4,6,6,4,6,6,4,6,1,5,1,1,5,1,1,5,1)
)

尝试过转置test_frame1、按ID和Syllable分组test_frame2，但无法完成合并，寻求简便实现方案。

方法一：使用tidyverse工具包（推荐） #

通过重塑test_frame1为长格式，再与test_frame2匹配，步骤简洁直观：

library(tidyverse)

# 处理test_frame1：转为长格式，提取ElementID序号
processed_frame1 <- test_frame1 %>%
  select(ID, starts_with("ElementType")) %>% # 仅保留关联所需列
  pivot_longer(
    cols = starts_with("ElementType"),
    names_to = "ElementID",
    values_to = "ElementType",
    names_prefix = "ElementType" # 去掉列名前缀，得到数字序号
  ) %>%
  mutate(ElementID = as.integer(ElementID)) # 转为整数类型，确保匹配一致

# 将ElementType值合并到test_frame2
result_frame <- test_frame2 %>%
  left_join(processed_frame1, by = c("ID", "ElementID"))

# 输出结果
print(result_frame)

逻辑说明：

• pivot_longer把test_frame1的宽格式转为长格式，每一行对应ID+ElementID的唯一组合，同时绑定对应的ElementType值
• left_join基于ID和ElementID两个关键字段，自动将ElementType填充到test_frame2的对应行，自然实现不同Syllable下的重复取值

方法二：基础R实现（无需额外包） #

如果不想加载tidyverse，可通过矩阵索引和匹配完成：

# 提取test_frame1中的ElementType列，转为矩阵
type_matrix <- as.matrix(test_frame1[, grep("ElementType", colnames(test_frame1))])
# 构建匹配用的唯一标识字符串
match_key <- paste(test_frame2$ID, test_frame2$ElementID)
# 生成目标矩阵的索引键
target_key <- paste(rep(test_frame1$ID, each = ncol(type_matrix)), col(type_matrix))
# 匹配并赋值
test_frame2$ElementType <- type_matrix[match(match_key, target_key)]

# 输出结果
print(test_frame2)

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