基于R [duplicate]中的两列连接

This question already has an answer here:

这个问题在这里已有答案:

How to join (merge) data frames (inner, outer, left, right)? 13 answers

如何加入(合并)数据框(内部,外部,左侧,右侧)? 13个答案

suppose that we have 2 tables.

假设我们有2个表。

First table A:

第一表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:

第二表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:

我希望在名称和x和z上对这两个表进行左连接,即x和z基本相同但名称不同。因此,决赛桌应如下所示:

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?

如何在R或SQL中做到这一点?

3 个解决方案

#1

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

#2

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.

在r命令行上查看合并函数的文档。

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)

#3

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.

在dplyr中,您需要将B的z重命名为x。 dplyr将确保每个中名称相同的列相等。

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

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

您可以使用基本函数执行类似操作,请参阅?merge。

#1

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

#2