Merge two large data.tables based on column name of one table and column value of the other without melting

Question

I've got two large data.tables DT1 (2M rows x 300 cols) and DT2 (50M rows x 2 cols) and i would like to merge the values of DT1 columns to a new column in DT2 based on the name of the column specified in a DT2 column. I'd like to achieve this without having to melt DT1, and by using data.table operations only, if possible. Hora, a sample dataset.

> require(data.table)
> DT1 <- data.table(ID = c('A', 'B', 'C', 'D'), col1 = (1:4), col2 = (5:8), col3 = (9:12), col4 = (13:16))
> DT1
   ID col1 col2 col3 col4
1:  A    1    5    9   13
2:  B    2    6   10   14
3:  C    3    7   11   15
4:  D    4    8   12   16

> DT2
   ID  col
1:  A col1
2:  B col2
3:  B col3
4:  C col1
5:  A col4

#desired output
> DT2_merge
   ID  col col_value
1:  A col1         1
2:  B col2         6
3:  B col3        10
4:  C col1         3
5:  A col4        13

Since dealing with two large data.tables, hoping to find the most efficient way of doing this.

Answer 1

Using set():

setkey(DT1, "ID")
setkey(DT2, "ID")
for (k in names(DT1)[-1]) {
  rows <- which(DT2[["col"]] == k)
  set(DT2, i = rows, j = "col_value", DT1[DT2[rows], ..k])
}

   ID  col col_value
1:  A col1         1
2:  A col4        13
3:  B col2         6
4:  B col3        10
5:  C col1         3

Note: Setting the key up front speeds up the process but reorders the rows.

Answer 2

Maybe there is a pure data.table version to do this but one way is to use matrix subsetting

library(data.table)

setDF(DT1)
DT2[, col_value := DT1[cbind(match(ID, DT1$ID), match(col, names(DT1)))]]

DT2
#   ID  col col_value
#1:  A col1         1
#2:  B col2         6
#3:  B col3        10
#4:  C col1         3
#5:  A col4        13

Answer 3

You can use lookup tables to find the indices for subsetting like:

setDF(DT1)
DT2[, col_value := DT1[matrix(c(setNames(seq_len(nrow(DT1)), DT1$ID)[DT2$ID], 
                    setNames(2:NCOL(DT1), colnames(DT1)[-1])[DT2$col]), ncol=2)]]
DT2
#   ID  col col_value
#1:  A col1         1
#2:  B col2         6
#3:  B col3        10
#4:  C col1         3
#5:  A col4        13

Using a matrix for subsetting is currently not sported in DT so if you have data.frame instead of data.table you can do it in base with:

DT2$col_value <- DT1[matrix(c(setNames(seq_len(nrow(DT1)), DT1$ID)[DT2$ID], 
                     setNames(2:NCOL(DT1), colnames(DT1)[-1])[DT2$col]), ncol=2)]

You can also change your data structure before and change from matrix- to vector-subsetting:

DT1ID  <- setNames(seq_len(nrow(DT1)), DT1$ID)
DT1  <- as.matrix(DT1[,-1])
DT2$col  <- as.integer(substring(DT2$col, 4))

DT2$col_value <- DT1[c(DT1ID[DT2$ID] + (DT2$col-1)*nrow(DT1))]

Maybe also try fastmatch:

library(fastmatch)
DT1 <- as.matrix(DT1[,-1], rownames=DT1$ID)
DT2$col  <- as.integer(substring(DT2$col, 4))
DT2$col_value <- DT1[c(fmatch(DT2$ID, rownames(DT1)) + (DT2$col-1)*nrow(DT1))]

Or you avoid lookup during subsetting und use levels when creating factor:

DT1 <- as.matrix(DT1[,-1], rownames=DT1$ID, colnames=colnames(DT1)[-1])
DT2$ID <- factor(DT2$ID, levels=rownames(DT1))
DT2$col <- factor(DT2$col, levels=colnames(DT1))

DT2$col_value <- DT1[c(unclass(DT2$ID) + (unclass(DT2$col)-1)*nrow(DT1))]

Answer 4

Here are two solutions also applicable to data.frame():

• Solution 1

DT2$col_value <- apply(DT2, 1, function(v) DT1[which(DT1$ID==v[1]),which(colnames(DT1)==v[2])])

• Solution 2 (same with solution by @Ronak Shah) maybe much faster than Solution 1 when with large dataset

DT2$col_value <- DT1[cbind(match(DT2$ID,DT1$ID),match(DT2$col,colnames(DT1)))]

• Solution 3 (maybe the fastest)

m <- as.matrix(DT1[-1])
rownames(m) <- DT1$ID
DT2$col_value <- m[as.matrix(DT2)]

Answer 5

Testing some of the methods on a larger data-set and show their performance:

#sindri_baldur
library(data.table)
DT1 <- data.table(ID = rownames(x1), x1)
DT2 <- as.data.table(x2)
setkey(DT1, "ID")
setkey(DT2, "ID")
system.time(for (k in names(DT1)[-1]) {
  rows <- which(DT2[["col"]] == k)
  set(DT2, i = rows, j = "col_value", DT1[DT2[rows], ..k])
})
#User: 6.696

#Ronak Shah
library(data.table)
DT1 <- data.table(ID = rownames(x1), x1)
DT2 <- as.data.table(x2)
setDF(DT1)
system.time(DT2[, col_value := DT1[cbind(match(ID, DT1$ID), match(col, names(DT1)))]])
#User: 5.210

#Using fastmatch
library(fastmatch)
DT1  <- x1
DT2  <- x2
system.time(DT2$col_value <- DT1[c(fmatch(DT2$ID, rownames(DT1))
   + (fmatch(DT2$col, colnames(DT1))-1)*nrow(DT1))])
#User: 0.061

#Using factors
DT1  <- x1
DT2  <- x2
system.time(DT2$col_value <- DT1[c(unclass(DT2$ID) + (unclass(DT2$col)-1)*nrow(DT1))])
#User: 0.024

Data:

set.seed(7)
nrows <- 1e5
ncols <- 300
x1 <- matrix(sample(0:20, nrows*ncols, replace=TRUE), ncol=ncols
  , dimnames=list(sample(do.call("paste0", expand.grid(rep(list(letters)
    , ceiling(log(nrows, length(letters)))))), nrows), seq_len(ncols)))
x2  <- data.frame(ID=factor(sample(rownames(x1), nrows*10, replace=TRUE)
  , levels=rownames(x1))
  , col=factor(sample(colnames(x1), nrows*10, replace=TRUE), levels=colnames(x1)))