逐行合并 `data.table` 中的逗号分隔值

Question

我有一个 data.table DT 如下。

DT <- structure(list(ID = c("Bats", "HL", "JL", "Spidey", "Supes", 
"X"), List1 = c("Morrison, Brubaker, Daniel, Loeb", "David, Bryne, Lee", 
"", "Loeb, Lee", "Moore, Siegel, Millar", "Bendis, Whendon"), 
    List2 = c("Rucka, Kane, Morrison", "Lee, Mantlo, Bryne", 
    "Meltzer, Sekowsky, Morrison", "Waid, Yost, Kirby, Lee", 
    "", "Claremont, Whendon, Morrison")), .Names = c("ID", "List1", 
"List2"), row.names = c(NA, -6L), class = c("data.table", "data.frame"
), .internal.selfref = NULL, sorted = "ID")

数字

      ID                            List1                        List2
1:   Bats Morrison, Brubaker, Daniel, Loeb        Rucka, Kane, Morrison
2:     HL                David, Bryne, Lee           Lee, Mantlo, Bryne
3:     JL                                   Meltzer, Sekowsky, Morrison
4: Spidey                        Loeb, Lee       Waid, Yost, Kirby, Lee
5:  Supes            Moore, Siegel, Millar                             
6:      X                  Bendis, Whendon Claremont, Whendon, Morrison

我想将 DT$List1 和 DT$List2 列中的两个列表按行合并在一起，不要重复。

我可以使用 apply 来做到这一点，如下所示。

DT$merged <- apply(DT,1,function(vec){
  paste(unique(strsplit(paste(vec[2],vec[3],sep=", "),", ")[[1]]),collapse=", ")
})

     DT
       ID                            List1                        List2
1:   Bats Morrison, Brubaker, Daniel, Loeb        Rucka, Kane, Morrison
2:     HL                David, Bryne, Lee           Lee, Mantlo, Bryne
3:     JL                                   Meltzer, Sekowsky, Morrison
4: Spidey                        Loeb, Lee       Waid, Yost, Kirby, Lee
5:  Supes            Moore, Siegel, Millar                             
6:      X                  Bendis, Whendon Claremont, Whendon, Morrison
                                          merged
1: Morrison, Brubaker, Daniel, Loeb, Rucka, Kane
2:                     David, Bryne, Lee, Mantlo
3:                 , Meltzer, Sekowsky, Morrison
4:                  Loeb, Lee, Waid, Yost, Kirby
5:                         Moore, Siegel, Millar
6:          Bendis, Whendon, Claremont, Morrison

由于单元格为空，如何使用 data.table 在开头和结尾没有“,”的情况下有效地获得相同的结果？

Answer 1

DT[, merged := toString(unique(c(strsplit(List1, split = ", ")[[1]],
                                 strsplit(List2, split = ", ")[[1]]))), by = ID][]
#       ID                            List1                        List2
#1:   Bats Morrison, Brubaker, Daniel, Loeb        Rucka, Kane, Morrison
#2:     HL                David, Bryne, Lee           Lee, Mantlo, Bryne
#3:     JL                                   Meltzer, Sekowsky, Morrison
#4: Spidey                        Loeb, Lee       Waid, Yost, Kirby, Lee
#5:  Supes            Moore, Siegel, Millar                             
#6:      X                  Bendis, Whendon Claremont, Whendon, Morrison
#                                          merged
#1: Morrison, Brubaker, Daniel, Loeb, Rucka, Kane
#2:                     David, Bryne, Lee, Mantlo
#3:                   Meltzer, Sekowsky, Morrison
#4:                  Loeb, Lee, Waid, Yost, Kirby
#5:                         Moore, Siegel, Millar
#6:          Bendis, Whendon, Claremont, Morrison

如果您的 ID 列不唯一，则将“by”替换为 1:nrow(DT)。