join based on two columns in R

Question

suppose that we have 2 tables.

First table A:

name    x     y
  a     1     2
  b     3     4
  c     5     7
  d     8     7
  c     5     3
  b     5     4

Second table B:

name    z     w
  a     1     9
  b     3     5
  c     5     2
  d     8     1
  b     5     9

I wish to do left join on these 2 tables on name and x and z, i.e., x and z are basically the same but with different names. Therefore, the final table should look like:

name    x     y    w
  a     1     2    9
  b     3     4    5
  c     5     7    2
  d     8     7    1
  c     5     3    2
  b     5     4    9

Any idea on how to do that in R or SQL?

If `x` and `w` are the same, why are they different in every row of your final table? Seems like you want to just join on `name`. — Gregor Thomas, Jun 04 '14 at 21:13

score 0 · Answer 1 · answered Jun 04 '14 at 21:17

0

   SELECT A.NAME,A.X,B.Y.B.W FROM A INNER JOIN B 
   ON 
   A.X=B.Z

answered Jun 04 '14 at 21:17

VJ Hil

904
6
15

do you know how to do this in R? – A.M. Jun 04 '14 at 21:18
The Below link will help you http://stackoverflow.com/questions/1299871/how-to-join-data-frames-in-r-inner-outer-left-right – VJ Hil Jun 04 '14 at 21:20
Didn't downvote, but you should probably add an equality condition for `NAME`s. Good link though, probably best to close as duplicate. – Gregor Thomas Jun 04 '14 at 21:23

Gregor Thomas · Answer 2 · 2014-06-04T21:28:29.573

0

In SQL

select A.name, x, y, w
from A
join B
on A.name = B.name and x = z

In dplyr you'll need to rename B's z to x. dplyr will make sure any columns named the same in each are equal.

library(dplyr)
names(B)[which(names(B) == "z")] <- "x"
left_join(A, B)

You can do similar with base functions, see ?merge.

edited Jun 04 '14 at 21:28

answered Jun 04 '14 at 21:18

Gregor Thomas

136,190
20
167
294

score 0 · Answer 3 · answered Jun 04 '14 at 21:25

In PostgreSQL:

SELECT * 
FROM "A"
LEFT OUTER JOIN "B"
ON "A".name = "B".name
AND x = w

In r:

Please enter

?merge

on the r command line to see the documentation for the merge function.

This should be something like what you're looking for:

merge(x=A, y=B, by.x = c("name","x"), by.y=c("name","w"), all.x=TRUE)

join based on two columns in R

3 Answers3