CODEWITHSUNDEEP
rdata.table
Vijay
Vijay

Reputation: 151

Joining tables based on different column names

I was watching a video[1] by Greg Reda about Pandas to see what Pandas can do how it compares with data.table. I was surprised to learn how difficult it was to join tables in data.table. If you watch the video, specifically @49:00 to @52:00 minutes you see that Pandas allows you to join tables based on different column names and you can choose different suffixes for left and right tables. I understand that setkey is used for optimizaion purposes[2] and understand how to join tables using same column names[3]. I tried data.table's merge but had much difficulty setting the by= keyword parameter using different column names. So here are my questions.

Is it possible, in data.table, to join tables based on different column names? If so, how? If not, why not? Also, more usefully, wouldn't this feature be useful? I find it surprising that this issue hasn't come up earlier. Pardon me (and please point me to them) if this has been discussed earlier.

BTW, the data that Greg is talking about is found on his github[4].

  1. https://www.youtube.com/watch?v=1uVWjdAbgBg
  2. https://stackoverflow.com/a/13686768/3892933
  3. Joining tables with identical (non-keyed) column names in R data.table
  4. https://github.com/gjreda/pydata2014nyc

Upvotes: 3

Views: 4315

Answers (3)

victor_v
victor_v

Reputation: 95

With reference to the Rdatatable github page, if you want to perform functions on your join rather than just merge tables, you can also do d1[d2, somefunc, on = "A==W"], where A is your column in d1 and W is your column in d2.

Upvotes: 1

Arun
Arun

Reputation: 118879

Update: All the features listed below are implemented and is available in the current stable version of data.table v1.9.6 on CRAN.

There are at least these improvements possible for joins in data.tables.

  • merge.data.table gaining by.x and by.y arguments

  • Using secondary keys to join using both forms discussed above without need to set keys, but rather by specifying columns on x and i.

The simplest reason is that we've not managed to get to it yet.

Upvotes: 6

MrFlick
MrFlick

Reputation: 206546

Normally, when joining data.tables the column names don't actually matter. You just need to make sure both tables have a compatible key.

library(data.table)
dt1<-data.table(a=letters[1:10], b=1:10)
setkey(dt1,a)
dt2<-data.table(x=letters[1:10], y=10:1)
setkey(dt2,x)

dt1[dt2]

Basically it will join on all the key columns. Normally you are joining on a key. If you really need to specify non-key columns, you can always cast the data.table to a data.frame and use the standard merge() function

merge(as.data.frame(dt1),dt2, by.x="a", by.y="x")
merge(as.data.frame(dt1),dt2, by.x="b", by.y="y")

Upvotes: 2

Related Questions