给定两个数据帧:

df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2, 4, 6), State = c(rep("Alabama", 2), rep("Ohio", 1)))

df1
#  CustomerId Product
#           1 Toaster
#           2 Toaster
#           3 Toaster
#           4   Radio
#           5   Radio
#           6   Radio

df2
#  CustomerId   State
#           2 Alabama
#           4 Alabama
#           6    Ohio

如何进行数据库样式(即sql样式)连接?也就是说,我如何获得:

df1和df2的内部连接:仅返回左表在右表中具有匹配键的行。df1和df2的外部连接:返回两个表中的所有行,连接左侧表中具有匹配键的记录。df1和df2的左外联接(或简单的左联接)返回左表中的所有行,以及右表中具有匹配键的所有行。df1和df2的右外部连接返回右表中的所有行,以及左表中具有匹配键的所有行。


额外学分:

如何执行SQL样式的选择语句?


当前回答

2014年新增:

特别是如果您还对数据操作感兴趣(包括排序、过滤、子设置、汇总等),那么您应该看看dplyr,它提供了各种功能,所有这些功能都旨在帮助您处理数据帧和某些其他数据库类型。它甚至提供了相当复杂的SQL接口,甚至还提供了一个将(大多数)SQL代码直接转换为R的函数。

dplyr包中的四个连接相关功能是(引用):

inner_join(x,y,by=NULL,copy=FALSE,…):返回x,其中y中有匹配的值,以及x和y中的所有列left_join(x,y,by=NULL,copy=FALSE,…):返回x中的所有行,以及x和y中的所有列semi_join(x,y,by=NULL,copy=FALSE,…):返回x中存在匹配值的所有行y、 只保留x中的列。anti_join(x,y,by=NULL,copy=FALSE,…):返回x中的所有行其中y中没有匹配的值,只保留x中的列

这一切都很详细。

可以通过select(df,“column”)来选择列。如果这对您来说还不够SQL,那么还有SQL()函数,您可以在其中原样输入SQL代码,它将执行您指定的操作,就像您一直在用R编写一样(有关更多信息,请参阅dplyr/databases vignette)。例如,如果应用正确,sql(“SELECT*FROM hflights”)将从“hflights“dplyr表(一个“tbl”)中选择所有列。

其他回答

dplyr从0.4开始实现了包括outer_join在内的所有连接,但值得注意的是,在0.4之前的前几个版本中,因此,在之后的相当长一段时间里,有很多非常糟糕的黑客解决方法用户代码(你仍然可以在那个时期的SO、Kaggle answers和github中找到这样的代码。因此,这个答案仍然有用。)

加入相关发布亮点:

版本0.5(2016年6月)

POSIXct类型、时区、重复项、不同因素级别的处理。更好的错误和警告。新后缀参数,用于控制重复变量名称接收的后缀(#1296)

版本0.4.0(2015年1月)

实施右联接和外联接(#96)可变联接,它从另一个表中的匹配行向一个表添加新变量。过滤联接,根据观察值是否与另一个表中的观察值匹配来过滤一个表的观察值。

版本0.3(2014年10月)

现在可以通过每个表中的不同变量进行left_join:df1%>%left_join(df2,c(“var1”=“var2”))

0.2版(2014年5月)

*_join()不再重新排序列名(#324)

版本0.1.3(2014年4月)

具有inner_join、left_join、semi_join、anti_joinouter_join尚未实现,回退是使用base::merge()(或plyr::join())尚未实现right_join和outer_join哈德利在这里提到了其他优势目前,dplyr所没有的一个小特性是,可以像Python panda那样,通过.x和.y列进行分隔。

根据哈德利在该问题中的评论采取的解决方法:

就行而言,rightjoin(x,y)与leftjoin(y,x)相同,只是列的顺序不同。轻松使用select(new_column_order)outer_join基本上是并集(leftjoin(x,y),rightjoin(y,x)),即保留两个数据帧中的所有行。

对于所有列上的内部联接,还可以使用data.table-package中的finteract或dplyr包中的intersect作为合并的替代方法,而不指定by列。这将给出两个数据帧之间相等的行:

merge(df1, df2)
#   V1 V2
# 1  B  2
# 2  C  3

dplyr::intersect(df1, df2)
#   V1 V2
# 1  B  2
# 2  C  3

data.table::fintersect(setDT(df1), setDT(df2))
#    V1 V2
# 1:  B  2
# 2:  C  3

示例数据:

df1 <- data.frame(V1 = LETTERS[1:4], V2 = 1:4)
df2 <- data.frame(V1 = LETTERS[2:3], V2 = 2:3)

我建议您查看Gabor Grothendieck的sqldf包,它允许您用SQL表示这些操作。

library(sqldf)

## inner join
df3 <- sqldf("SELECT CustomerId, Product, State 
              FROM df1
              JOIN df2 USING(CustomerID)")

## left join (substitute 'right' for right join)
df4 <- sqldf("SELECT CustomerId, Product, State 
              FROM df1
              LEFT JOIN df2 USING(CustomerID)")

我发现SQL语法比它的R等效语法更简单和自然(但这可能只是反映了我的RDBMS偏见)。

有关连接的更多信息,请参阅Gabor的sqldfGitHub。

通过使用merge函数及其可选参数:

内部连接:merge(df1,df2)将适用于这些示例,因为R会通过公共变量名自动连接帧,但您很可能希望指定merge(df1,df1,by=“CustomerId”),以确保仅在所需字段上匹配。如果匹配变量在不同的数据帧中具有不同的名称,也可以使用by.x和by.y参数。

外部联接:合并(x=df1,y=df2,by=“CustomerId”,all=TRUE)

左外部:合并(x=df1,y=df2,by=“CustomerId”,all.x=TRUE)

右外部:合并(x=df1,y=df2,by=“CustomerId”,all.y=TRUE)

交叉联接:合并(x=df1,y=df2,by=NULL)

与内部联接一样,您可能希望将“CustomerId”显式传递给R作为匹配变量。我认为几乎总是最好明确说明要合并的标识符;如果输入data.frames发生意外变化,则会更安全,并且以后更容易阅读。

您可以通过给定向量(例如,by=c(“CustomerId”,“OrderId”))合并多个列。

如果要合并的列名不相同,则可以指定,例如,by.x=“CustomerId_in_df1”,by.y=“CustomerId.in_df2”,其中CustomerId_in_df1是第一个数据帧中的列名,CustomerId_in-df2是第二个数据帧的列名。(如果需要合并多个列,这些也可以是向量。)

在R Wiki上有一些很好的例子。我会在这里偷一对:

合并方法

由于键的名称相同,所以进行内部连接的简单方法是merge():

merge(df1, df2)

可以使用“all”关键字创建完整的内部联接(两个表中的所有记录):

merge(df1, df2, all=TRUE)

df1和df2的左外连接:

merge(df1, df2, all.x=TRUE)

df1和df2的右外连接:

merge(df1, df2, all.y=TRUE)

你可以翻转它们,拍打它们,然后摩擦它们,以获得你询问的其他两个外部连接:)

下标方法

使用下标方法与左边的df1进行左外部连接将是:

df1[,"State"]<-df2[df1[ ,"Product"], "State"]

另一种外部联接的组合可以通过对左外部联接下标示例进行排序来创建。(是的,我知道这相当于说“我会把它作为练习留给读者……”)