给定两个数据帧:

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


当前回答

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)),即保留两个数据帧中的所有行。

其他回答

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)),即保留两个数据帧中的所有行。

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

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

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

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

对于所有列上的内部联接,还可以使用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)

更新联接。另一个重要的SQL样式连接是“更新连接”,其中一个表中的列使用另一个表更新(或创建)。

正在修改OP的示例表。。。

sales = data.frame(
  CustomerId = c(1, 1, 1, 3, 4, 6), 
  Year = 2000:2005,
  Product = c(rep("Toaster", 3), rep("Radio", 3))
)
cust = data.frame(
  CustomerId = c(1, 1, 4, 6), 
  Year = c(2001L, 2002L, 2002L, 2002L),
  State = state.name[1:4]
)

sales
# CustomerId Year Product
#          1 2000 Toaster
#          1 2001 Toaster
#          1 2002 Toaster
#          3 2003   Radio
#          4 2004   Radio
#          6 2005   Radio

cust
# CustomerId Year    State
#          1 2001  Alabama
#          1 2002   Alaska
#          4 2002  Arizona
#          6 2002 Arkansas

假设我们想将客户的状态从cust添加到purchases表sales,忽略年份列。使用基数R,我们可以识别匹配的行,然后复制值:

sales$State <- cust$State[ match(sales$CustomerId, cust$CustomerId) ]

# CustomerId Year Product    State
#          1 2000 Toaster  Alabama
#          1 2001 Toaster  Alabama
#          1 2002 Toaster  Alabama
#          3 2003   Radio     <NA>
#          4 2004   Radio  Arizona
#          6 2005   Radio Arkansas

# cleanup for the next example
sales$State <- NULL

从这里可以看到,match从customer表中选择第一个匹配行。


更新具有多个列的联接。当我们只加入一列并且对第一场比赛感到满意时,上面的方法效果很好。假设我们希望客户表中的测量年份与销售年份相匹配。

正如@bgoldst的回答所提到的,在这种情况下,匹配交互可能是一种选择。更直接地说,可以使用data.table:

library(data.table)
setDT(sales); setDT(cust)

sales[, State := cust[sales, on=.(CustomerId, Year), x.State]]

#    CustomerId Year Product   State
# 1:          1 2000 Toaster    <NA>
# 2:          1 2001 Toaster Alabama
# 3:          1 2002 Toaster  Alaska
# 4:          3 2003   Radio    <NA>
# 5:          4 2004   Radio    <NA>
# 6:          6 2005   Radio    <NA>

# cleanup for next example
sales[, State := NULL]

正在滚动更新加入。或者,我们可能希望获取找到客户的最后一个状态:

sales[, State := cust[sales, on=.(CustomerId, Year), roll=TRUE, x.State]]

#    CustomerId Year Product    State
# 1:          1 2000 Toaster     <NA>
# 2:          1 2001 Toaster  Alabama
# 3:          1 2002 Toaster   Alaska
# 4:          3 2003   Radio     <NA>
# 5:          4 2004   Radio  Arizona
# 6:          6 2005   Radio Arkansas

以上三个示例都侧重于创建/添加新列。有关更新/修改现有列的示例,请参阅相关的R常见问题解答。