How to join data to only the first matching row with {data.table} in R

user_8675075 0
Viewed 216

I have a look-up table of "firsts" in column d. For example, the first time the patient was admitted because of a specific disease. I would like to join this back into the main data frame via data.table on multiple other conditions.

My problem is that, unfortunately, the main data.table could have multiple records with identical joining criteria that results in multiple "firsts" per patient after the join. Real world data is messy, people!

Is it possible to do a {data.table} join on only the first matching record?

This is similar to this question, but the multiple-matches are on the main data table. I think that mult only works on when there are several entries on the table being joined in.

repex:

library(data.table)

set.seed(1724)

d1 <- data.table(a = c(1, 1, 1),
                 b = c(1, 1, 2),
                 c = sample(1:10, 3))

d2 <- data.table(a = 1, b = 1, d = TRUE)

d2[d1, on = c("a", "b")]

   a b    d c
1: 1 1 TRUE 4
2: 1 1 TRUE 8
3: 1 2   NA 2

desired output

   a b    d c
1: 1 1 TRUE 4
2: 1 1   NA 8
3: 1 2   NA 2
data.table r join
3 Answers

One way would be to turn the values to NA after join.

library(data.table)

d3 <- d2[d1, on = c("a", "b")]
d3[, d:= replace(d, seq_len(.N) != 1, NA), .(a, b)]
d3

#   a b    d c
#1: 1 1 TRUE 4
#2: 1 1   NA 8
#3: 1 2   NA 2
user_39629140 
library(data.table)
set.seed(1724)
d1 = data.table(a = c(1, 1, 1), b = c(1, 1, 2), c = sample(1:10, 3))
d2 = data.table(a = 1, b = 1, d = TRUE)

d1[, i1:=seq_len(.N), by=c("a","b")]
d2[, i2:=seq_len(.N), by=c("a","b")]

d2[d1, on = c("a","b","i2==i1")][, "i2":=NULL][]
#       a     b      d     c
#   <num> <num> <lgcl> <int>
#1:     1     1   TRUE     4
#2:     1     1     NA     8
#3:     1     2     NA     2
user_24904970

The easy solution would be to index every row and join on this also (the d2 is a filtered version of d1):

library(data.table)

set.seed(1724)

d1 <- data.table(a = c(1, 1, 1),
                 b = c(1, 1, 2),
                 c = sample(1:10, 3))

d1[, rid := seq(to = .N)]
d2 <- d1[, .SD[1], by = c("a"), .SDcols = c("b", "rid")][, d := TRUE] # UPDATE

d2[d1, on = c("a", "b", "rid")]
user_86750750
Related