1

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?

hgcrpd
  • 1,820
  • 3
  • 19
  • 32

1 Answers1

6

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
Arun
  • 116,683
  • 26
  • 284
  • 387
  • Wonderful, that did it. I had just seen this other answer of yours mentioning that `i` was available in `j`. Is there a place where this is documented? I've seen the mention of `.SD` but didn't look at it closely enough. – hgcrpd Aug 11 '13 at 15:19
  • I see that there is some more detail under the `by` section of the `data.table` help, but it doesn't mention `i`, only `.I` – hgcrpd Aug 11 '13 at 16:05
  • That i's and x's columns can be referred to using `i.` and `x.` prefix is now documented in `?data.table` (of current devel, v1.9.7). And it'll be soon added to the vignettes as well. – Arun Apr 13 '16 at 15:46
  • @Arun not sure whether this is a new data.table glitch (v 1.10.4-3), but your answer gets me `Error in validate(cols, x) : cols value(s) [i.A] not present (or out of range) in x`. Wrapping in an extra get fixes it: `DT2[DT1, .(value=get(get('i.A'))), on="idx", by=.EACHI]` – ds440 Jun 08 '18 at 14:19