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

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

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

在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方法。请参见以下每种连接类型的示例。有两种方法，一种是在将第二个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

你也可以使用哈德利·威克姆（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)

使用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。