给定两个数据帧:

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


当前回答

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

其他回答

更新用于连接数据集的data.table方法。请参见以下每种连接类型的示例。有两种方法,一种是在将第二个data.table作为第一个参数传递给子集时使用[.data.table,另一种方法是使用merge函数,将其分派给快速data.table方法。

df1 = data.frame(CustomerId = c(1:6), Product = c(rep("Toaster", 3), rep("Radio", 3)))
df2 = data.frame(CustomerId = c(2L, 4L, 7L), State = c(rep("Alabama", 2), rep("Ohio", 1))) # one value changed to show full outer join

library(data.table)

dt1 = as.data.table(df1)
dt2 = as.data.table(df2)
setkey(dt1, CustomerId)
setkey(dt2, CustomerId)
# right outer join keyed data.tables
dt1[dt2]

setkey(dt1, NULL)
setkey(dt2, NULL)
# right outer join unkeyed data.tables - use `on` argument
dt1[dt2, on = "CustomerId"]

# left outer join - swap dt1 with dt2
dt2[dt1, on = "CustomerId"]

# inner join - use `nomatch` argument
dt1[dt2, nomatch=NULL, on = "CustomerId"]

# anti join - use `!` operator
dt1[!dt2, on = "CustomerId"]

# inner join - using merge method
merge(dt1, dt2, by = "CustomerId")

# full outer join
merge(dt1, dt2, by = "CustomerId", all = TRUE)

# see ?merge.data.table arguments for other cases

下面是基准测试baseR、sqldf、dplyr和data.table。基准测试未索引/未索引的数据集。基准测试是在50M-1行数据集上执行的,联接列上有50M-2个公共值,因此可以测试每个场景(内部、左侧、右侧、完整),并且联接仍然不容易执行。这是一种很好地强调连接算法的连接类型。时间截至sqldf:0.4.11,dplyr:0.7.8,data.table:1.12.0。

# inner
Unit: seconds
   expr       min        lq      mean    median        uq       max neval
   base 111.66266 111.66266 111.66266 111.66266 111.66266 111.66266     1
  sqldf 624.88388 624.88388 624.88388 624.88388 624.88388 624.88388     1
  dplyr  51.91233  51.91233  51.91233  51.91233  51.91233  51.91233     1
     DT  10.40552  10.40552  10.40552  10.40552  10.40552  10.40552     1
# left
Unit: seconds
   expr        min         lq       mean     median         uq        max 
   base 142.782030 142.782030 142.782030 142.782030 142.782030 142.782030     
  sqldf 613.917109 613.917109 613.917109 613.917109 613.917109 613.917109     
  dplyr  49.711912  49.711912  49.711912  49.711912  49.711912  49.711912     
     DT   9.674348   9.674348   9.674348   9.674348   9.674348   9.674348       
# right
Unit: seconds
   expr        min         lq       mean     median         uq        max
   base 122.366301 122.366301 122.366301 122.366301 122.366301 122.366301     
  sqldf 611.119157 611.119157 611.119157 611.119157 611.119157 611.119157     
  dplyr  50.384841  50.384841  50.384841  50.384841  50.384841  50.384841     
     DT   9.899145   9.899145   9.899145   9.899145   9.899145   9.899145     
# full
Unit: seconds
  expr       min        lq      mean    median        uq       max neval
  base 141.79464 141.79464 141.79464 141.79464 141.79464 141.79464     1
 dplyr  94.66436  94.66436  94.66436  94.66436  94.66436  94.66436     1
    DT  21.62573  21.62573  21.62573  21.62573  21.62573  21.62573     1

请注意,您可以使用data.table执行其他类型的联接:-连接时更新-如果要将值从另一个表查找到主表-联接时聚合-如果要在联接的键上聚合,则不必实现所有联接结果-重叠连接-如果要按范围合并-滚动联接-如果您希望合并能够通过向前或向后滚动来匹配前一行/后一行的值-非相等联接-如果联接条件不相等

要复制的代码:

library(microbenchmark)
library(sqldf)
library(dplyr)
library(data.table)
sapply(c("sqldf","dplyr","data.table"), packageVersion, simplify=FALSE)

n = 5e7
set.seed(108)
df1 = data.frame(x=sample(n,n-1L), y1=rnorm(n-1L))
df2 = data.frame(x=sample(n,n-1L), y2=rnorm(n-1L))
dt1 = as.data.table(df1)
dt2 = as.data.table(df2)

mb = list()
# inner join
microbenchmark(times = 1L,
               base = merge(df1, df2, by = "x"),
               sqldf = sqldf("SELECT * FROM df1 INNER JOIN df2 ON df1.x = df2.x"),
               dplyr = inner_join(df1, df2, by = "x"),
               DT = dt1[dt2, nomatch=NULL, on = "x"]) -> mb$inner

# left outer join
microbenchmark(times = 1L,
               base = merge(df1, df2, by = "x", all.x = TRUE),
               sqldf = sqldf("SELECT * FROM df1 LEFT OUTER JOIN df2 ON df1.x = df2.x"),
               dplyr = left_join(df1, df2, by = c("x"="x")),
               DT = dt2[dt1, on = "x"]) -> mb$left

# right outer join
microbenchmark(times = 1L,
               base = merge(df1, df2, by = "x", all.y = TRUE),
               sqldf = sqldf("SELECT * FROM df2 LEFT OUTER JOIN df1 ON df2.x = df1.x"),
               dplyr = right_join(df1, df2, by = "x"),
               DT = dt1[dt2, on = "x"]) -> mb$right

# full outer join
microbenchmark(times = 1L,
               base = merge(df1, df2, by = "x", all = TRUE),
               dplyr = full_join(df1, df2, by = "x"),
               DT = merge(dt1, dt2, by = "x", all = TRUE)) -> mb$full

lapply(mb, print) -> nul

在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"]

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

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

你也可以使用哈德利·威克姆(Hadley Wickham)很棒的dplyr包来完成连接。

library(dplyr)

#make sure that CustomerId cols are both the same type
#they aren’t in the provided data (one is integer and one is double)
df1$CustomerId <- as.double(df1$CustomerId)

可变联接:使用df2中的匹配项将列添加到df1

#inner
inner_join(df1, df2)

#left outer
left_join(df1, df2)

#right outer
right_join(df1, df2)

#alternate right outer
left_join(df2, df1)

#full join
full_join(df1, df2)

过滤联接:过滤掉df1中的行,不修改列

#keep only observations in df1 that match in df2.
semi_join(df1, df2)

#drop all observations in df1 that match in df2.
anti_join(df1, df2)

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