在R中基于两列任意一列合并数据框并保持顺序

需求说明：

• 有两个数据框：df1仅包含一列，df2包含两列
• 需要将df1的列与df2的任意一列进行匹配，返回所有匹配结果
• 合并后结果的first_column顺序必须与df1的输入顺序保持一致

示例输入 #

# 定义单列数据框df1
first_column_df1 <- c("ENSG00000165588","ENSG00000213551")
df1 <- data.frame(first_column = first_column_df1)

# 定义双列数据框df2
first_column_df2 <- c("ENSG00000142192", "ENSG00000140575", "ENSG00000165588", "ENSG00000165588", "ENSG00000213551", "ENSG00000213551","ENSG00000197153")
second_column_df2 <- c("ENSG00000165588", "ENSG00000165588", "ENSG00000186908", "ENSG00000135446", "ENSG00000273983", "ENSG00000274267","ENSG00000213551")
df2 <- data.frame(first_column = first_column_df2, second_column = second_column_df2)

示例输出 #

first_column <- c("ENSG00000165588","ENSG00000165588","ENSG00000165588","ENSG00000165588","ENSG00000213551","ENSG00000213551","ENSG00000213551")
second_column <- c("ENSG00000142192","ENSG00000140575","ENSG00000186908","ENSG00000135446","ENSG00000273983","ENSG00000274267","ENSG00000197153")
output <- data.frame(first_column, second_column)

实现代码 #

# 初始化结果存储列表
result_list <- list()

# 遍历df1的每个元素，按顺序处理匹配
for (val in df1$first_column) {
  # 筛选df2中任意一列等于当前val的行
  matched_rows <- df2[df2$first_column == val | df2$second_column == val, ]
  
  # 确定匹配行对应的结果列：当前val作为first_column，另一列作为second_column
  matched_rows$result_first <- val
  matched_rows$result_second <- ifelse(matched_rows$first_column == val, matched_rows$second_column, matched_rows$first_column)
  
  # 将当前批次的匹配结果加入列表
  result_list[[length(result_list)+1]] <- matched_rows[, c("result_first", "result_second")]
}

# 合并所有批次结果，并重命名列名
output <- do.call(rbind, result_list)
colnames(output) <- c("first_column", "second_column")

# 输出结果
output

运行上述代码后，即可得到与示例一致的输出，且first_column的顺序严格遵循df1的输入顺序。

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