题 如何加入(合并)数据框(内部,外部,左侧,右侧)?


给出两个数据框:

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


930
2017-08-19 13:18


起源


stat545-ubc.github.io/bit001_dplyr-cheatsheet.html ←我最喜欢这个问题的答案 - isomorphismes
由RStudio创建和维护的dplyr备忘单的数据转换也有关于联接如何在dplyr中工作的很好的信息图表 rstudio.com/resources/cheatsheets - Arthur Yip


答案:


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

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

外联合:  merge(x = df1, y = df2, by = "CustomerId", all = TRUE)

左外:  merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)

右外:  merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE)

交叉加入:  merge(x = df1, y = df2, by = NULL)

就像内连接一样,您可能希望将“CustomerId”显式传递给R作为匹配变量。  我认为最好明确说明要合并的标识符;如果输入data.frames意外更改并且稍后更容易阅读,则更安全。

您可以通过给出合并多个列 by 一个矢量,例如, by = c("CustomerId", "OrderId")

如果要合并的列名不相同,您可以指定,例如, by.x = "CustomerId_in_df1",by.y =“CustomerId_in_df2”whereCustomerId_in_df1is the name of the column in the first data frame andCustomerId_in_df2`是第二个数据框中列的名称。 (如果需要在多个列上合并,这些也可以是向量。)


1031
2017-08-19 15:15



@MattParker我一直在使用sqldf包进行一系列针对数据帧的复杂查询,真的需要它来进行自交联接(即data.frame交叉连接本身)我想知道它是如何从性能角度进行比较的... ??? - Nicholas Hamilton
@ADP我从来没有真正使用过sqldf,所以我不确定速度。如果性能对您来说是一个主要问题,那么您也应该考虑一下 data.table package - 这是一组全新的连接语法,但它比我们在这里讨论的任何内容都要快得多。 - Matt Parker
更清晰和解释..... mkmanu.wordpress.com/2016/04/08/... - Manoj Kumar
一个对我有帮助的次要添加 - 当您想要使用多个列进行合并时: merge(x=df1,y=df2, by.x=c("x_col1","x_col2"), by.y=c("y_col1","y_col2")) - Dileep Kumar Patchigolla
这适用于 data.table 现在,同样的功能只是更快。 - marbel


我建议退房 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的sqldf GitHub 有关联接的更多信息。


183
2017-08-20 17:54





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进行基本的左外连接?

另一种选择是 join 找到的功能 plyr 包

library(plyr)

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

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

选项 typeinnerleftrightfull

?join:不像 merge,[join无论使用何种连接类型,都保留x的顺序。


166
2018-03-11 06:24



+1提及 plyr::join。 Microbenchmarking表明,它的执行速度比快3倍 merge。 - Beasterfield
然而, data.table 比两者都要快得多。在SO中也有很大的支持,我没有看到很多包装作者在这里经常回答问题 data.table 作家或贡献者。 - marbel
是什么 data.table 合并的语法 数据框列表? - Aleksandr Blekh
请注意: dt1 [dt2]是右外连接(不是“纯”内连接) 这样即使dt1中没有匹配的行,来自dt2的所有行也将成为结果的一部分。影响: 您的结果可能有多余的行 如果你在dt2中的键值与dt1的键值不匹配。 - R Yoda
@RYoda你可以指定 nomatch = 0L 在这种情况下。 - David Arenburg


你也可以使用Hadley Wickham的精彩连接 dplyr 包。

library(dplyr)

#make sure that CustomerId cols are both type numeric
#they ARE not using the provided code in question and dplyr will complain
df1$CustomerId <- as.numeric(df1$CustomerId)
df2$CustomerId <- as.numeric(df2$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中的行,不要修改列

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

138
2018-02-06 21:35



你为什么需要转换 CustomerId 数字?我没有在文档中看到任何提及(两者都有 plyr 和 dplyr)关于这种类型的限制。如果合并列将是错误的,您的代码是否会正常工作 character 类型(特别感兴趣的 plyr)?我错过了什么吗? - Aleksandr Blekh


在这方面有一些很好的例子 R维基。我会偷一对夫妇:

合并方法

由于您的密钥命名相同,因此内部连接的简短方法是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"]

可以通过对左外连接下标示例进行mungling来创建外连接的其他组合。 (是的,我知道这相当于说“我会把它作为读者的练习......”)


71
2017-08-19 15:15





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-ish足够你,那就有了 sql() 函数,您可以按原样输入SQL代码,它将执行您指定的操作,就像您一直在R中编写一样(有关详细信息,请参阅 dplyr / databases vignette)。例如,如果应用正确, sql("SELECT * FROM hflights") 将从“hflights”dplyr表中选择所有列(“tbl”)。


62
2018-01-29 17:43



鉴于dplyr软件包在过去两年中的重要性,这绝对是最佳解决方案。 - Marco Fumagalli


更新data.table方法以加入数据集。请参阅以下每种联接类型的示例。有两种方法,一种来自 [.data.table 当传递第二个data.table作为子集的第一个参数时,另一种方法是使用 merge 调度到快速data.table方法的函数。

2016-04-01更新 - 这不是愚人节的笑话!
在1.9.7版本的data.table连接现在能够使用现有索引,这极大地减少了连接的时间。 下面的代码和基准测试不会在连接时使用data.table索引。如果您正在寻找近实时连接,则应使用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=0L, on = "CustomerId"]

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

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

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

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

基准测试基础R,sqldf,dplyr和data.table。
基准测试未加密/未加索引的数据集。如果在data.tables或使用sqldf的索引上使用键,则可以获得更好的性能。 Base R和dplyr没有索引或键,因此我没有在基准测试中包含该场景。
基准测试是在5M-1行数据集上执行的,在连接列上有5M-2个常用值,因此可以测试每个场景(左,右,全,内),并且连接仍然不容易执行。

library(microbenchmark)
library(sqldf)
library(dplyr)
library(data.table)

n = 5e6
set.seed(123)
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)

# inner join
microbenchmark(times = 10L,
               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"),
               data.table = dt1[dt2, nomatch = 0L, on = "x"])
#Unit: milliseconds
#       expr        min         lq      mean     median        uq       max neval
#       base 15546.0097 16083.4915 16687.117 16539.0148 17388.290 18513.216    10
#      sqldf 44392.6685 44709.7128 45096.401 45067.7461 45504.376 45563.472    10
#      dplyr  4124.0068  4248.7758  4281.122  4272.3619  4342.829  4411.388    10
# data.table   937.2461   946.0227  1053.411   973.0805  1214.300  1281.958    10

# left outer join
microbenchmark(times = 10L,
               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")),
               data.table = dt2[dt1, on = "x"])
#Unit: milliseconds
#       expr       min         lq       mean     median         uq       max neval
#       base 16140.791 17107.7366 17441.9538 17414.6263 17821.9035 19453.034    10
#      sqldf 43656.633 44141.9186 44777.1872 44498.7191 45288.7406 47108.900    10
#      dplyr  4062.153  4352.8021  4780.3221  4409.1186  4450.9301  8385.050    10
# data.table   823.218   823.5557   901.0383   837.9206   883.3292  1277.239    10

# right outer join
microbenchmark(times = 10L,
               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"),
               data.table = dt1[dt2, on = "x"])
#Unit: milliseconds
#       expr        min         lq       mean     median        uq       max neval
#       base 15821.3351 15954.9927 16347.3093 16044.3500 16621.887 17604.794    10
#      sqldf 43635.5308 43761.3532 43984.3682 43969.0081 44044.461 44499.891    10
#      dplyr  3936.0329  4028.1239  4102.4167  4045.0854  4219.958  4307.350    10
# data.table   820.8535   835.9101   918.5243   887.0207  1005.721  1068.919    10

# full outer join
microbenchmark(times = 10L,
               base = merge(df1, df2, by = "x", all = TRUE),
               #sqldf = sqldf("SELECT * FROM df1 FULL OUTER JOIN df2 ON df1.x = df2.x"), # not supported
               dplyr = full_join(df1, df2, by = "x"),
               data.table = merge(dt1, dt2, by = "x", all = TRUE))
#Unit: seconds
#       expr       min        lq      mean    median        uq       max neval
#       base 16.176423 16.908908 17.485457 17.364857 18.271790 18.626762    10
#      dplyr  7.610498  7.666426  7.745850  7.710638  7.832125  7.951426    10
# data.table  2.052590  2.130317  2.352626  2.208913  2.470721  2.951948    10

55
2017-12-11 09:23



是否值得添加一个示例,说明如何在中使用不同的列名称 on =  太? - SymbolixAU
@Symbolix我们可能会等待1.9.8版本,因为它会添加非equi连接运算符 on ARG - jangorecki
另一个想法;是否值得添加一个注释 merge.data.table 有默认值 sort = TRUE 参数,在合并期间添加一个键并将其保留在结果中。这是值得注意的,特别是如果你试图避免设置键。 - SymbolixAU
我很惊讶没有人提到,如果有重复,大多数人都没有工作...... - statquant
@statquant你可以用笛卡尔连接 data.table, 你什么意思?请你更具体一点。 - David Arenburg