合并具有重复列名的多个数据表

Question

我正在尝试合并（联接）多个数据表格（使用fread从5个CSV文件中获取）以形成一个单一的数据表格。当我尝试合并5个数据表格时，会出现错误，但当我只合并4个时，则没有问题。以下是MWE：

# example data
DT1 <- data.table(x = letters[1:6], y = 10:15)
DT2 <- data.table(x = letters[1:6], y = 11:16)
DT3 <- data.table(x = letters[1:6], y = 12:17)
DT4 <- data.table(x = letters[1:6], y = 13:18)
DT5 <- data.table(x = letters[1:6], y = 14:19)

# this gives an error
Reduce(function(...) merge(..., all = TRUE, by = "x"), list(DT1, DT2, DT3, DT4, DT5))

Error in merge.data.table(..., all = TRUE, by = "x") : x存在一些重复的列名：y.x，y.y。请删除或重命名这些重复项，然后再试一次。

# whereas this works fine
Reduce(function(...) merge(..., all = TRUE, by = "x"), list(DT1, DT2, DT3, DT4))

    x y.x y.y y.x y.y 
 1: a  10  11  12  13 
 2: b  11  12  13  14 
 3: c  12  13  14  15 
 4: d  13  14  15  16 
 5: e  14  15  16  17 
 6: f  15  16  17  18

我有一个解决办法，如果我更改DT1的第二列列名：

setnames(DT1, "y", "new_y")

# this works now
Reduce(function(...) merge(..., all = TRUE, by = "x"), list(DT1, DT2, DT3, DT4, DT5))

为什么会发生这种情况，有没有办法合并任意数量具有相同列名的数据表而不更改任何列名？

- srao

---

"运行正常"? 祝你好运，有两个 y.y，等等。 - Frank

@srao - 那是个不好的主意 - 在连接之前/期间进行setnames，而不是之后。 - eddi

@Frank 是的，x 在所有 DT 中完全相同。没有重复的值。 - srao

@srao 如果 x 对于它们所有的值都完全相同，那么你不应该合并。 - eddi

好的，谢谢你澄清。虽然最好让你的问题代表你实际面临的问题，但我认为这一次你很幸运，所有答案都恰好适用于该情况，除了Jaap的第一部分可能不适用。 - Frank

Answer 1

如果只涉及这5个Datatables（其中x对于所有Datatables都相同），您也可以使用嵌套连接：

# set the key for each datatable to 'x'
setkey(DT1,x)
setkey(DT2,x)
setkey(DT3,x)
setkey(DT4,x)
setkey(DT5,x)

# the nested join
mergedDT1 <- DT1[DT2[DT3[DT4[DT5]]]]

就像@Frank在评论中所说：

DTlist <- list(DT1,DT2,DT3,DT4,DT5)
Reduce(function(X,Y) X[Y], DTlist)

这将会得到：

   x y1 y2 y3 y4 y5
1: a 10 11 12 13 14
2: b 11 12 13 14 15
3: c 12 13 14 15 16
4: d 13 14 15 16 17
5: e 14 15 16 17 18
6: f 15 16 17 18 19

这与以下代码产生相同的结果：

mergedDT2 <- Reduce(function(...) merge(..., all = TRUE, by = "x"), list(DT1, DT2, DT3, DT4, DT5))

> identical(mergedDT1,mergedDT2)
[1] TRUE

当您的X列具有不同的值时，嵌套连接将无法提供所需的解决方案：

DT1[DT2[DT3[DT4[DT5[DT6]]]]]

这将会给出：

   x y1 y2 y3 y4 y5 y6
1: b 11 12 13 14 15 15
2: c 12 13 14 15 16 16
3: d 13 14 15 16 17 17
4: e 14 15 16 17 18 18
5: f 15 16 17 18 19 19
6: g NA NA NA NA NA 20

当:

Reduce(function(...) merge(..., all = TRUE, by = "x"), list(DT1, DT2, DT3, DT4, DT5, DT6))

提供：

   x y1 y2 y3 y4 y5 y6
1: a 10 11 12 13 14 NA
2: b 11 12 13 14 15 15
3: c 12 13 14 15 16 16
4: d 13 14 15 16 17 17
5: e 14 15 16 17 18 18
6: f 15 16 17 18 19 19
7: g NA NA NA NA NA 20

使用的数据：

为了让使用 Reduce 的代码正常工作，我更改了 y 列的名称。

DT1 <- data.table(x = letters[1:6], y1 = 10:15)
DT2 <- data.table(x = letters[1:6], y2 = 11:16)
DT3 <- data.table(x = letters[1:6], y3 = 12:17)
DT4 <- data.table(x = letters[1:6], y4 = 13:18)
DT5 <- data.table(x = letters[1:6], y5 = 14:19)

DT6 <- data.table(x = letters[2:7], y6 = 15:20, key="x")

- Jaap

---

这与使用all=TRUE进行合并不同。 - eddi

对于这个玩具示例能够正常工作并不奇怪。但是，一旦您在5个样本中添加了不完全相同的“x”值，它们就会分歧。all = TRUE的merge执行外连接，而[执行单向连接。 - eddi

你的第一个是别名为 Reduce(function(X,Y) X[Y], DTlist)。 - Frank

您IP地址为143.198.54.68，由于运营成本限制，当前对于免费用户的使用频率限制为每个IP每72小时10次对话，如需解除限制，请点击左下角设置图标按钮（手机用户先点击左上角菜单按钮）。 - Jaap

好的，虽然如果 x 完全相同，进行合并是愚蠢的。 - eddi

@Frank 谢谢，我已经把它加到我的答案里了。 - Jaap

Answer 2

以下是一种在Reduce中保持计数器的方法，如果您想在合并过程中进行重命名：

Reduce((function() {counter = 0
                    function(x, y) {
                      counter <<- counter + 1
                      d = merge(x, y, all = T, by = 'x')
                      setnames(d, c(head(names(d), -1), paste0('y.', counter)))
                    }})(), list(DT1, DT2, DT3, DT4, DT5))
#   x y.x y.1 y.2 y.3 y.4
#1: a  10  11  12  13  14
#2: b  11  12  13  14  15
#3: c  12  13  14  15  16
#4: d  13  14  15  16  17
#5: e  14  15  16  17  18
#6: f  15  16  17  18  19

- eddi

---

函数定义周围和后面的括号是怎么回事，比如(function()...)()？ - Frank

@Frank 这是一个闭包。外部函数创建环境并返回内部函数，这就是这些括号提取的内容。 - eddi

Answer 3

堆叠和重塑我不认为这与merge函数完全对应，但是...

mycols <- "x"
DTlist <- list(DT1,DT2,DT3,DT4,DT5)

dcast(rbindlist(DTlist,idcol=TRUE), paste0(paste0(mycols,collapse="+"),"~.id"))

#    x  1  2  3  4  5
# 1: a 10 11 12 13 14
# 2: b 11 12 13 14 15
# 3: c 12 13 14 15 16
# 4: d 13 14 15 16 17
# 5: e 14 15 16 17 18
# 6: f 15 16 17 18 19

我不确定这是否适用于拥有比 y 更多的列。

合并分配。

DT <- Reduce(function(...) merge(..., all = TRUE, by = mycols), 
  lapply(DTlist,`[.noquote`,mycols))

for (k in seq_along(DTlist)){
  js = setdiff( names(DTlist[[k]]), mycols )
  DT[DTlist[[k]], paste0(js,".",k) := mget(paste0("i.",js)), on=mycols, by=.EACHI]
}

#    x y.1 y.2 y.3 y.4 y.5
# 1: a  10  11  12  13  14
# 2: b  11  12  13  14  15
# 3: c  12  13  14  15  16
# 4: d  13  14  15  16  17
# 5: e  14  15  16  17  18
# 6: f  15  16  17  18  19

我不确定这是否完全适用于其他情况。很难说，因为OP的示例并没有要求merge的全部功能。在OP的情况下，使用mycols="x"并且x在所有DT*中都相同，显然合并是不合适的，正如@eddi所提到的。然而，一般的问题很有趣，这就是我在这里尝试解决的问题。

- Frank

Answer 4

使用重塑可以让你在命名列时更加灵活。

library(dplyr)
library(tidyr)

list(DT1, DT2, DT3, DT4, DT5) %>%
  bind_rows(.id = "source") %>%
  mutate(source = paste("y", source, sep = ".")) %>%
  spread(source, y)

或者，这也可以起作用。

library(dplyr)
library(tidyr)

list(DT1 = DT1, DT2 = DT2, DT3 = DT3, DT4 = DT4, DT5 = DT5) %>%
  bind_rows(.id = "source") %>%
  mutate(source = paste(source, "y", sep = ".")) %>%
  spread(source, y)

- bramtayl

---

在 bind_rows 之后没有 source 列，因此我看到了 Error: cannot coerce type 'closure' to vector of type 'character'（因为 source 是一个函数）。不确定如何修复这个问题... 可能是你误用了 bind_rows ...? - Frank

".id" 特性是在 dplyr 0.4.3 中新增的。请问您正在使用这个版本吗？ - bramtayl

不对，是0.4.2版本的问题。谢谢。 - Frank

Answer 5

另一种方法：

dts <- list(DT1, DT2, DT3, DT4, DT5)

names(dts) <- paste("y", seq_along(dts), sep="")
data.table::dcast(rbindlist(dts, idcol="id"), x ~ id, value.var = "y")

#   x y1 y2 y3 y4 y5
#1: a 10 11 12 13 14
#2: b 11 12 13 14 15
#3: c 12 13 14 15 16
#4: d 13 14 15 16 17
#5: e 14 15 16 17 18
#6: f 15 16 17 18 19

在"data.table::dcast"中添加包名是为了确保调用返回的是数据表而不是数据框，即使已经加载了"reshape2"包。如果没有明确提及包名，可能会使用来自reshape2包的dcast函数，它适用于数据框并返回数据框，而不是数据表。

- Henrik Seidel

Answer 6

或者您可以在合并前为列设置名称，然后像这样执行merge

dts = list(DT1, DT2, DT3, DT4, DT5)
names(dts) = paste('DT', c(1:5), sep = '')    

dtlist = lapply(names(dts),function(i) 
         setNames(dts[[i]], c('x', paste('y',i,sep = '.'))))

Reduce(function(...) merge(..., all = T), dtlist)

#   x y.DT1 y.DT2 y.DT3 y.DT4 y.DT5
#1: a    10    11    12    13    14
#2: b    11    12    13    14    15
#3: c    12    13    14    15    16
#4: d    13    14    15    16    17
#5: e    14    15    16    17    18
#6: f    15    16    17    18    19

- Veerendra Gadekar

---

顺便提一下，给 dts 命名并不是必要的；你可以通过 1:5 来引用它们。此外，你可能想使用 setnames 的 for 循环，而不是 setNames（eddi 在评论中提到并被 OP 使用）。 - Frank

@Frank 是的，谢谢！我只是想到要包括这一步骤，考虑到如果 OP 想要放置 data.table 名称而不仅仅是数字，那么最终的 data.table 将更具信息性。 - Veerendra Gadekar

@Frank 我不明白为什么要用for循环？ - Veerendra Gadekar

data.table 函数 setnames 是通过引用操作的，它会修改对象本身，因此不需要分配函数的值/结果。 - Frank

@Frank 哦，那就是我在使用 data.table 的 setnames 时的情况.. 好的，太棒了！ - Veerendra Gadekar

Answer 7

这是一种替代方案 - 当您的x列没有相同的值时，您可以每次定义连接列。您需要定义具有列名称的向量。然后，您可以通过引用链接来链接：

cols_dt1 <- colnames(dt_dt1)[!colnames(dt_dt1) %in% 'join_column1']
cols_dt2 <- colnames(dt_dt2)[!colnames(dt_dt2) %in% ' join_column2']
cols_dt3 <- colnames(dt_dt3)[!colnames(dt_dt3) %in% ' join_column3']
cols_dt4 <- colnames(dt_dt4)[!colnames(dt_dt4) %in% ' join_column4']
cols_dt5 <- colnames(dt_dt5)[!colnames(dt_dt5) %in% ' join_column5']

data_dt[dt_dt1, on=.( join_column1), (cols_dt1) := mget(cols_dt1)][
  dt_dt2, on=.( join_column2), (cols_dt2) := mget(cols_dt2)][
    dt_dt3, on=.( join_column3), (cols_dt3) := mget(cols_dt3)][
      dt_dt4, on=.( join_column4), (cols_dt4) := mget(cols_dt4)][
        dt_dt5, on=.( join_column5), (cols_dt5) := mget(cols_dt5)]

- AdamKarol