给定两个数据帧:

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样式的选择语句?


当前回答

使用merge函数,我们可以选择左表或右表的变量,就像我们熟悉的SQL中的select语句一样(例如:从…中选择a.*…或选择b.*)我们必须添加额外的代码,这些代码将从新连接的表中子集。SQL:-从df1中选择a.*a内部联接df2 b,位于a.CustomerId=b.CustomerIdR:-mmerge(df1,df2,按.x=“CustomerId”,按.y=“CustomerId)[,名称(df1)]

同样的方式

SQL:-从df1中选择b.*a内部联接df2 b,位于a.CustomerId=b.CustomerIdR:-mmerge(df1,df2,by.x=“CustomerId”,by.y=“客户ID”)[,名称(df2)]

其他回答

我建议您查看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函数,我们可以选择左表或右表的变量,就像我们熟悉的SQL中的select语句一样(例如:从…中选择a.*…或选择b.*)我们必须添加额外的代码,这些代码将从新连接的表中子集。SQL:-从df1中选择a.*a内部联接df2 b,位于a.CustomerId=b.CustomerIdR:-mmerge(df1,df2,按.x=“CustomerId”,按.y=“CustomerId)[,名称(df1)]

同样的方式

SQL:-从df1中选择b.*a内部联接df2 b,位于a.CustomerId=b.CustomerIdR:-mmerge(df1,df2,by.x=“CustomerId”,by.y=“客户ID”)[,名称(df2)]

内部连接有data.table方法,它非常节省时间和内存(对于一些更大的data.frames也是必要的):

library(data.table)
  
dt1 <- data.table(df1, key = "CustomerId") 
dt2 <- data.table(df2, key = "CustomerId")

joined.dt1.dt.2 <- dt1[dt2]

merge也适用于data.tables(因为它是通用的并调用merge.data.table)

merge(dt1, dt2)

stackoverflow上记录的data.table:如何执行data.table合并操作将外键上的SQL联接转换为R data.table语法合并更大数据的有效替代方案。帧R如何在R中与data.table进行基本的左外连接?

另一个选项是plyr包中的join函数。【2022年注意:plyr现已退役,并已被dplyr取代。dplyr中的连接操作在本答案中描述。】

library(plyr)

join(df1, df2,
     type = "inner")

#   CustomerId Product   State
# 1          2 Toaster Alabama
# 2          4   Radio Alabama
# 3          6   Radio    Ohio

类型选项:内部、左侧、右侧、完整。

从…起join:与merge不同,[join]保留x的顺序,无论使用何种连接类型。

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”)中选择所有列。

在连接两个数据帧时,每个数据帧约有100万行,一个数据帧有2列,另一个数据框约有20行,我惊讶地发现merge(…,all.x=TRUE,all.y=TRUE)比dplyr::full_join()更快。这是dplyr v0.4

合并需要约17秒,完全加入需要约65秒。

尽管如此,我还是需要一些食物,因为我通常默认使用dplyr来执行操作任务。