Nearest "n" rolling join in R data table

Here is something very raw (we go row by row):

n <- 2L
sen <- 1L:n
for (i in 1:nrow(dt1)) {
  set(dt1, i, j = "nearest", list(which(frank(abs(dt1$x[i] - dt2$x)) %in% sen)))
}
dt1[, .(id1, nearest = unlist(nearest)), by = x
    ][, id2 := dt2$id2[nearest]
      ][, roll := paste0("nr", frank(abs(dt2$x[nearest] - x))), by = x][]

#      x id1 nearest id2 roll
# 1:  15   x       1   a  nr1
# 2:  15   x       2   b  nr2
# 3: 101   y       2   b  nr2
# 4: 101   y       3   c  nr1

Slightly cleaner:

dt1[, 
    {
      nrank <- frank(abs(x - dt2$x), ties.method="first")
      nearest <- which(nrank %in% sen)
      .(x = x, id2 = dt2$id2[nearest], roll = paste0("nr", nrank[nearest]))
    }, 
    by = id1] # assumes unique ids.

Data:

dt1 <- data.table(x = c(15, 101), id1 = c("x", "y"))
dt2 <- data.table(x = c(10, 50, 100, 200), id2 = c("a", "b", "c", "d"))

EDIT (as suggested/written by OP) Joining with multiple keys:

dt1[, 
    {
      g <- group
      dt_tmp <- dt2[dt2$group == g]
      nrank <- frank(abs(x - dt_tmp$x), ties.method="first")
      nearest <- which(nrank %in% sen)
      .(x = x, id2 = dt_tmp$id2[nearest], roll = paste0("nr", nrank[nearest]))
    }, 
    by = id1]

Edited for corrected ordering.

I don't know that roll= is going to allow nearest-n, but here's a possible workaround:

dt1[, id2 := lapply(x, function(z) { r <- head(order(abs(z - dt2$x)), n = 2); dt2[ r, .(id2, nr = order(r)) ]; }) ]
as.data.table(tidyr::unnest(dt1, id2))
#      x id1 id2 nr
# 1:  15   x   a  1
# 2:  15   x   b  2
# 3: 101   y   c  2
# 4: 101   y   b  1

(I'm using tidyr::unnest because I think it fits and works well here, and data.table/#3672 is still open.)


Second batch of data:

dt1 = data.table(x = c(1, 5, 7), id1 = c("x", "y", "z"))
dt2 = data.table(x = c(2, 5, 6, 10), id2 = c(2, 5, 6, 10))
dt1[, id2 := lapply(x, function(z) { r <- head(order(abs(z - dt2$x)), n = 2); dt2[ r, .(id2, nr = order(r)) ]; }) ]
as.data.table(tidyr::unnest(dt1, id2))
#    x id1 id2 nr
# 1: 1   x   2  1
# 2: 1   x   5  2
# 3: 5   y   5  1
# 4: 5   y   6  2
# 5: 7   z   6  2
# 6: 7   z   5  1

Tags:

Join

R

Data.Table