如何将多个data.frame的指定列合并为新表并自定义列名？

我有448个列名完全一致（共9列）的data.frame，示例结构如下：

V1              V2      V4    ... V9
ENSG00000000003.15   TSPAN6   7095
ENSG00000000005.6     TNMD    4355
       .                .       .
       .                .       .

我想要生成一个新的data.frame，要求：

• 保留所有data.frame中完全相同的前两列（V1和V2）
• 合并所有data.frame中的V4列（各表V4数据不同）
• 排除其余列
• 将合并后的V4列重命名为sample1、sample2……直到sample448

最终目标结构如下：

V1              V2     sample1    sample2 ... sample448
ENSG00000000003.15   TSPAN6   7095    3856       .
ENSG00000000005.6     TNMD    4355    2976       .
       .                .       .      .         . 
       .                .       .      .         .

我已经完成了以下代码：

reader <- function(f){
  read.table(f, sep='\t', skip=6, header=FALSE)
}

files <- list.files(path, 
                    recursive=TRUE, full.names=TRUE)

myfilelist <- lapply(files, reader)

但不知道如何合并指定列。以下是dput(lapply(myfilelist[1:2], head))的输出结果：

myfilelist <- list(structure(list(V1 = c("ENSG00000000003.15", "ENSG00000000005.6", 
"ENSG00000000419.13", "ENSG00000000457.14", "ENSG00000000460.17", 
"ENSG00000000938.13"), V2 = c("TSPAN6", "TNMD", "DPM1", "SCYL3", 
"C1orf112", "FGR"), V3 = c("protein_coding", "protein_coding", 
"protein_coding", "protein_coding", "protein_coding", "protein_coding"
), V4 = c(7094L, 2L, 4355L, 1149L, 372L, 585L), V5 = c(3573L, 
1L, 2201L, 953L, 553L, 281L), V6 = c(3521L, 1L, 2154L, 883L, 
579L, 308L), V7 = c(59.9764, 0.052, 138.3704, 6.4018, 2.3896, 
6.6335), V8 = c(20.5827, 0.0178, 47.4859, 2.197, 0.8201, 2.2765
), V9 = c(22.2037, 0.0192, 51.2256, 2.37, 0.8847, 2.4558)), row.names = c(NA, 
6L), class = "data.frame"), structure(list(V1 = c("ENSG00000000003.15", 
"ENSG00000000005.6", "ENSG00000000419.13", "ENSG00000000457.14", 
"ENSG00000000460.17", "ENSG00000000938.13"), V2 = c("TSPAN6", 
"TNMD", "DPM1", "SCYL3", "C1orf112", "FGR"), V3 = c("protein_coding", 
"protein_coding", "protein_coding", "protein_coding", "protein_coding", 
"protein_coding"), V4 = c(2616L, 23L, 3746L, 1288L, 510L, 1578L
), V5 = c(1369L, 9L, 1876L, 1015L, 681L, 797L), V6 = c(1250L, 
14L, 1871L, 984L, 693L, 782L), V7 = c(16.8063, 0.4541, 90.4417, 
5.4531, 2.4895, 13.5969), V8 = c(4.8615, 0.1314, 26.1617, 1.5774, 
0.7201, 3.9331), V9 = c(6.0158, 0.1625, 32.3733, 1.9519, 0.8911, 
4.867)), row.names = c(NA, 6L), class = "data.frame"))

方法1：基础R实现 #

因为所有data.frame的V1和V2列完全一致，直接以第一个表的这两列为基础，循环提取每个表的V4列并合并：

# 初始化结果：取第一个数据框的V1和V2列
result <- myfilelist[[1]][, c("V1", "V2")]

# 遍历所有数据框，提取V4列并添加到结果中，列名设为sample1~sample448
for (i in seq_along(myfilelist)) {
  result[[paste0("sample", i)]] <- myfilelist[[i]]$V4
}

方法2：tidyverse工具包实现 #

用purrr和dplyr更简洁地完成合并：

library(tidyverse)

result <- myfilelist %>%
  # 遍历每个数据框，提取V4列并设置对应sample名称，按列合并
  map_dfc(~select(., V4) %>% set_names(paste0("sample", which(myfilelist == .)))) %>%
  # 绑定第一个数据框的V1、V2列到前面
  bind_cols(myfilelist[[1]][, c("V1", "V2")], .) %>%
  # 调整列顺序，确保V1、V2在最前
  select(V1, V2, everything())

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