R - Join on data.table, selecting a different column based on value of another column in row

1
votes

I have two data.tables:

DT1 <- data.table(A=c('A', 'B', 'C'), idx=c(1,2,3))
DT2 <- data.table(idx=c(1,2,3), A=rep('foo', 3), B=rep('bar', 3), C=rep('baz', 3))

> DT1
   A idx
1: A   1
2: B   2
3: C   3

> DT2
   idx   A   B   C
1:   1 foo bar baz
2:   2 foo bar baz
3:   3 foo bar baz

And I want to get to this:

> DT3
   idx value
1:   1   foo
2:   2   bar
3:   3   baz

Basically I want to merge DT1 and DT2 on idx, but I only want the column from DT2 that corresponds to the value of A in the row of DT1. both DT1 and DT2 have the same number of rows and are in the rows are in same order

Is there a data.table native way to do this?

1
rdata.table

1 Answers

6
votes

Updated answer following the old (implicit) by-without-by feature being replaced with by=.EACHI (and also using on= argument instead of setting key:

require(data.table) # v1.9.6+
DT2[DT1, .(value=get(i.A)), on="idx", by=.EACHI]
#    idx value
# 1:   1   foo
# 2:   2   bar
# 3:   3   baz