1

I'm about to merge large data sets. That's why I try out data.table and am thrilled by its speed.

# base R
system.time(
  M1 <- Reduce(function(...) merge(..., all=TRUE), L)
  )
# user  system elapsed 
# 5.05    0.00    5.20 

# data.table    
library(data.table)
L.dt <- lapply(L, function(x) setkeyv(data.table(x), c("sid", "id")))
system.time(
  M2 <- Reduce(function(...) merge(..., all=TRUE), L.dt)
  )
# user  system elapsed 
# 0.12    0.00    0.12

Both approaches yield the same values, however there are some columns that are split with data.table.

base R:

set.seed(1)
car::some(M1, 5)
#        sid    id         V3        V4          a         b
# 60504    1 60504 -0.6964804 -1.210195         NA        NA
# 79653    1 79653 -2.5287163 -1.087546         NA        NA
# 111637   2 11637  0.7104236        NA -1.7377657        NA
# 171855   2 71855  0.2023342        NA -0.6334279        NA
# 272460   3 72460 -0.5098994        NA         NA 0.2738896

data.table:

set.seed(1)
car::some(M2, 5)
#    sid    id       V3.x        V4      V3.y          a         V3         b
# 1:   1 60504 -0.6964804 -1.210195        NA         NA         NA        NA
# 2:   1 79653 -2.5287163 -1.087546        NA         NA         NA        NA
# 3:   2 11637         NA        NA 0.7104236 -1.7377657         NA        NA
# 4:   2 71855         NA        NA 0.2023342 -0.6334279         NA        NA
# 5:   3 72460         NA        NA        NA         NA -0.5098994 0.2738896

Did I miss something? Or is there an easy way to solve this, i.e. get the split columns combined? (I don't want to use any other packages.)

Data

fun <- function(x){
  set.seed(x)
  data.frame(cbind(sid=x, id=1:1e5, matrix(rnorm(1e5*2), 1e5)))
}
tmp <- lapply(1:3, fun)
df1 <- tmp[[1]]
df2 <- tmp[[2]]
df3 <- tmp[[3]]
rm(tmp)
names(df2)[4] <- c("a")
names(df3)[4] <- c("b")
L <- list(df1, df2, df3)

Related: 1, 2

jay.sf
  • 60,139
  • 8
  • 53
  • 110

1 Answers1

6

The by argument in base::merge defaults to intersect(names(x), names(y)) where x and y are the 2 tables to be merged. Hence, base::merge also uses V3 as the merging key.

The by argument in data.table::merge defaults to the shared key columns between the two tables (i.e. sid and id in this case). And since the tables have columns named V3, suffixes are appended to the new columns.

So if your intention is to merge by all common columns, you can identify the common columns, set keys then merge:

commcols <- Reduce(intersect, lapply(L, names))
L.dt <- lapply(L, function(x) setkeyv(data.table(x), commcols))
M2 <- Reduce(function(...) merge(..., all=TRUE), L.dt)
chinsoon12
  • 25,005
  • 4
  • 25
  • 35
  • Ok, in my real data, as you can imagine, there are hundreds of `V3`s, which is actually the point. – jay.sf Nov 23 '18 at 00:22
  • you can specify your `by` columns if you really want to merge by V3 as well. or `L.dt <- lapply(L, function(x) setkeyv(data.table(x), c("sid", "id", "V3")))` if this is really what you intended and what you did with `base::merge` – chinsoon12 Nov 23 '18 at 00:25
  • And this then continues with the `a`s and `b`s and `c`s and ... from the `dt`s to merge. – jay.sf Nov 23 '18 at 00:26
  • just identify the common columns using `by=Reduce(intersect, lapply(L.dt, names))` – chinsoon12 Nov 23 '18 at 00:27
  • I have a follow-up issue up with the real data, though. Error message `x has some duplicated column name(s)` citing 118 names, data has 431 names. Thought it was related to `intersect` and tried `union` but didn't help. Could you clarify that here, or shall I rather ask another question? – jay.sf Nov 23 '18 at 00:56
  • which line is causing the error? `commcols <- Reduce(intersect, lapply(L, names))`? – chinsoon12 Nov 23 '18 at 00:58
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184118/discussion-between-jay-sf-and-chinsoon12). – jay.sf Nov 23 '18 at 01:01