data.table join then add columns to existing data.frame without re-copy

Question

I have two data.tables, X (3m rows by ~500 columns), and Y (100 rows by two columns).

set.seed(1)
X <- data.table( a=letters, b=letters, c=letters, g=sample(c(1:5,7),length(letters),replace=TRUE), key="g" )
Y <- data.table( z=runif(6), g=1:6, key="g" )

I want to do a left outer join on X, which I can do by Y[X] thanks to:

Why does X[Y] join of data.tables not allow a full outer join, or a left join?

But I want to add the new column to X without copying X (since it's huge).

Obviously, something like X <- Y[X] works, but unless data.table is far cleverer than I give it credit for (and I give it credit for quite a lot of deviousness!), I believe this copies the whole of X.

X[ , z:= Y[X,z]$z ] works, but is kludgy and doesn't scale well to more than one column.

How do I store the results of a merge back into the retained data.table in an efficient (both in terms of copies and in terms of programmer time) way?

you don't need to do Y[X,z] (and will possibly run into problems doing that if you forget about by-without-by), just X[, z := Y[X]$z] works and seems to be faster for this example; although ultimately X = Y[X] is by far the fastest of the different expressions I've tried so far — eddi
– eddi, Commented Oct 23, 2013 at 22:19
Interesting. I had the ,z in there because I thought that would give DT info about what variables it needed to retain since it optimizes on that. But your (deleted) point is worth copying here: "watch out for hidden by-without-by when doing smth like Y[X,z]." Even if it's fast, if X = Y[X] creates a copy I'm potentially in trouble.... — Ari B. Friedman
– Ari B. Friedman, Commented Oct 23, 2013 at 22:29
I see, so your concern is more about memory usage and less speed; in that case I think what you suggested, in the form that you suggested, by-without-by's notwithstanding (ok here I think - I always get confused about it), is probably the way to go — eddi
– eddi, Commented Oct 23, 2013 at 22:34
Related and also answered by eddi albeit without anyone finding the documentation: stackoverflow.com/questions/16843728/… — Frank
– Frank, Commented Oct 26, 2013 at 9:01

Arun · Accepted Answer · 2013-10-24 08:13:09Z

33

+50

This is easy to do:

X[Y, z := i.z]

It works because the only difference between Y[X] and X[Y] here, is when some elements are not in Y, in which case presumably you'd want z to be NA, which the above assignment will exactly do.

It would also work just as well for many variables:

X[Y, `:=`(z1 = i.z1, z2 = i.z2, ...)]

Since you require the operation Y[X], you can add the argument nomatch=0 (as @mnel points out) so as to not get NAs for those where X doesn't contain the key values from Y. That is:

X[Y, z := i.z, nomatch=0]

From the NEWS for data.table

    **********************************************
    **                                          **
    **   CHANGES IN DATA.TABLE VERSION 1.7.10   **
    **                                          **
    **********************************************

NEW FEATURES

o   The prefix i. can now be used in j to refer to join inherited
    columns of i that are otherwise masked by columns in x with
    the same name.

edited Oct 24, 2013 at 8:13

Arun

119k28 gold badges290 silver badges396 bronze badges

answered Oct 23, 2013 at 22:12

eddi

49.5k6 gold badges109 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ari B. Friedman Over a year ago

Many thanks. That works (even with my edited example data), but I don't understand why. Is there a name for the i. notation? I don't see it anywhere in ?[.data.table....

eddi Over a year ago

I think the i. usage is poorly documented and I forget where it is right now - but it's simply used to refer to columns of the i-expression data.table

Ari B. Friedman Over a year ago

I get it. Still a little confused as to why X[Y] (right outer join) produces such different results from X[Y, z:= i.z] (left outer join with assignment).

eddi Over a year ago

they both produce the same result as far as the join goes, and in the second case, the resulting z from Y is assigned to a new column in X (which automatically produces NA's for cases where there was no match)

mnel Over a year ago

@AriB.Friedman -- it is in the NEWS for version 1.7.10 (I've added a link). For the differences, it comes down to the fact you can't add rows by reference (yet), so X[Y, z:=i.z] will be the equivalent of setting z=i.z X[Y, z:=i.z, nomatch=0]`

|

Community · Accepted Answer · 2017-05-23 12:17:20Z

10

As an addition to the answer above, you can also do (v1.9.6+):

require(data.table) # v1.9.6+
X[Y, (colNames) := mget(paste0("i.", colNames))]

where colNames is a character vector listing the columns you want from Y. This lets you efficiently select columns to add (define colNames from a subset of names(Y)) in the case you are adding many columns.

Also, you can combine it with the new on= argument (from v1.9.6+) as:

# ad-hoc joins using 'on=' instead of setting keys
require(data.table) # v1.9.6+
X[Y, (colNames) := mget(paste0("i.", colNames)), on = "g"]

Credit to akrun for the (colNames) := mget(colNames) strategy here: Update rows of data frame in R.

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Nov 17, 2015 at 21:01

Alexander Li

3171 gold badge3 silver badges10 bronze badges

1 Comment

David Arenburg Over a year ago

Actually not akrun, rather the guy above you introduced this here: stackoverflow.com/a/30469832/3001626 . So basically both of the answers on this thread are eddi's

Collectives™ on Stack Overflow

data.table join then add columns to existing data.frame without re-copy

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related