给出两个数据框:
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 style, joins?也就是说,我该怎么做:
-
df1
ofdf1
和df2
:
仅返回左表在右表中具有匹配键的行 . -
outer join of
df1
和df2
:
返回两个表中的所有行,从左侧连接具有右表中匹配键的记录 . -
left outer join (or simply left join) of
df1
和df2
返回左表中的所有行,以及右表中具有匹配键的所有行 . -
right outer join of
df1
和df2
返回右表中的所有行,以及左表中具有匹配键的所有行 .
额外信用:
如何进行SQL样式选择语句?
13 回答
使用
merge
函数我们可以选择左表或右表的变量,就像我们熟悉SQL中的select语句一样(EX:选择a . * ...或从b中选择b . *)我们必须添加额外的代码,这些代码将从新连接的表中进行子集化 .
SQL: -
select a.* from df1 a inner join df2 b on a.CustomerId=b.CustomerId
R: -
merge(df1, df2, by.x = "CustomerId", by.y = "CustomerId")[,names(df1)]
同样的方式
SQL: -
select b.* from df1 a inner join df2 b on a.CustomerId=b.CustomerId
R: -
merge(df1, df2, by.x = "CustomerId", by.y = "CustomerId")[,names(df2)]
通过使用
merge
函数及其可选参数:Inner join:
merge(df1, df2)
将适用于这些示例,因为R会自动通过公共变量名称连接帧,但您很可能希望指定merge(df1, df2, by = "CustomerId")
以确保仅匹配所需的字段 . 如果匹配变量在不同数据框中具有不同的名称,也可以使用by.x
和by.y
参数 .Outer join:
merge(x = df1, y = df2, by = "CustomerId", all = TRUE)
Left outer:
merge(x = df1, y = df2, by = "CustomerId", all.x = TRUE)
Right outer:
merge(x = df1, y = df2, by = "CustomerId", all.y = TRUE)
Cross join:
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"
,其中CustomerId_in_df1
是第一个数据框中列的名称,CustomerId_in_df2
是第二个数据框中列的名称 . (如果需要在多个列上合并,这些也可以是向量 . )我建议检查Gabor Grothendieck's sqldf package,它允许您在SQL中表达这些操作 .
我发现SQL语法比它的R等价物更简单,更自然(但这可能只反映了我的RDBMS偏见) .
有关联接的更多信息,请参见Gabor's sqldf GitHub .
内连接有 data.table 方法,这非常节省时间和内存(对于一些较大的data.frames是必需的):
merge
也适用于data.tables(因为它是通用的并且调用merge.data.table
)stackoverflow上记录的data.table:
How to do a data.table merge operation
Translating SQL joins on foreign keys to R data.table syntax
Efficient alternatives to merge for larger data.frames R
How to do a basic left outer join with data.table in R?
另一个选项是plyr包中的
join
函数选项
type
:inner
,left
,right
,full
.从
?join
:与merge
不同,[join
]保留x的顺序,无论使用何种连接类型 .您也可以使用Hadley Wickham的精彩dplyr包进行连接 .
Mutating join:使用df2中的匹配将列添加到df1
过滤联接:过滤掉df1中的行,不要修改列
在R Wiki有一些很好的例子 . 我会偷一对夫妇:
Merge Method
由于您的密钥命名相同,因此进行内部联接的简短方法是merge():
可以使用“all”关键字创建完整的内部联接(来自两个表的所有记录):
df1和df2的左外连接:
df1和df2的右外连接:
你可以翻转它们,拍打它们并按下它们以获得你询问的另外两个外部连接:)
Subscript Method
使用下标方法在左侧使用df1的左外连接将是:
可以通过对左外连接下标示例进行mungling来创建外连接的其他组合 . (是的,我知道这相当于说“我会留下它作为读者的练习......”)
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中的列这都是here非常详细 .
选择列可以通过
select(df,"column")
完成 . 如果's not SQL-ish enough for you, then there'是sql()
函数,您可以按原样输入SQL代码,它将执行您指定的操作,就像您一直在R中编写一样(有关更多信息,请参阅dplyr/databases vignette) . 例如,如果正确应用,sql("SELECT * FROM hflights")
将选择"hflights" dplyr表中的所有列("tbl") .更新data.table方法以加入数据集 . 请参阅以下每种联接类型的示例 . 有两种方法,一种是从
[.data.table
传递第二个data.table作为子集的第一个参数,另一种方法是使用merge
函数调度到快速data.table方法 .基准测试基础R,sqldf,dplyr和data.table .
基准测试未加密/未加索引的数据集 . 基准测试是在50M-1行数据集上执行的,在连接列上有50M-2个常用值,因此可以测试每个场景(内部,左侧,右侧,完整),并且连接仍然不容易执行 . 它是很好地强调连接算法的连接类型 . 时间是
sqldf:0.4.11
,dplyr:0.7.8
,data.table:1.12.0
.请注意,您可以使用
data.table
执行其他类型的连接:代码重现:
dplyr自0.4实现了所有这些连接,包括outer_join,但值得注意的是 for the first few releases it used not to offer outer_join, and as a result there was a lot of really bad hacky workaround user code floating around for quite a while (you can still find this in SO and Kaggle answers from that period).
加入相关release highlights:
v0.5 (6/2016)
处理POSIXct类型,时区,重复项,不同因子级别 . 更好的错误和警告 .
用于控制后缀重复变量名称接收的新后缀参数(#1296)
v0.4.0 (1/2015)
Implement right join and outer join (#96)
变异连接,它将新变量从另一个表中的匹配行添加到一个表中 . 过滤连接,根据是否与另一个表中的观察匹配来过滤来自一个表的观察 .
v0.3 (10/2014)
v0.2 (5/2014)
v0.1.3 (4/2014)
有 inner_join, left_join, semi_join, anti_join
outer_join 尚未实现,回退是使用base :: merge()(或plyr :: join())
尚未implement right_join and outer_join
Hadley mentioning other advantages here
目前有一个次要特征合并,dplyr不是the ability to have separate by.x,by.y columns,例如Python熊猫的确如此 .
每个hadley在该问题上的评论的解决方法:
right_join (x,y)就行而言与left_join(y,x)相同,只是列将是不同的顺序 . 使用select(new_column_order)轻松解决问题
outer_join 基本上是union(left_join(x,y),right_join(x,y)) - 即保留两个数据帧中的所有行 .
在连接两个数据帧时,每个行有大约100万行,一个有2列,另一个有〜20,我惊奇地发现
merge(..., all.x = TRUE, all.y = TRUE)
比dplyr::full_join()
快 . 这与dplyr v0.4有关合并需要大约17秒,full_join大约需要65秒 .
虽然有些食物,因为我通常默认使用dplyr进行操作任务 .
用于左连接的情况下与
0..*:0..1
基数或右连接用0..1:0..*
基数也可以从木匠(所述0..1
表),可在位分配单方面列直接到joinee(所述0..*
表),并由此避免创建一个全新的数据表 . 这需要将来自参赛者的关键列与加入者进行匹配,并对索引进行索引,以便相应地对加入者的行进行分配 .如果键是单列,那么我们可以使用match()的单个调用来进行匹配 . 我将在这个答案中介绍这种情况 .
这里's an example based on the OP, except I' ve添加了一个额外的行到
df2
,id为7,以测试joiner中不匹配键的情况 . 这实际上是df1
左加入df2
:在上面我硬编码了一个假设,即键列是两个输入表的第一列 . 我认为,一般来说,这不是一个不合理的假设,因为,如果你有一个带有键列的data.frame,如果它还没有被设置为data.frame的第一列,那就太奇怪了 . 一开始 . 并且您可以随时重新排序列以实现它 . 这种假设的一个有利结果是,关键列的名称不必是硬编码的,尽管我认为它只是将一个假设替换为另一个假设 . Concision是整数索引以及速度的另一个优点 . 在下面的基准测试中,我将更改实现以使用字符串名称索引来匹配竞争实现 .
我认为这是一个特别合适的解决方案,如果你有几个表要保持连接对一个大表 . 为每次合并重复重建整个表将是不必要且低效的 .
另一方面,如果您因为任何原因需要通过此操作保持不变,则不能使用此解决方案,因为它直接修改了joinee . 虽然在这种情况下您可以简单地复制并在副本上执行就地分配 .
作为旁注,我简要介绍了多列密钥的可能匹配解决方案 . 不幸的是,我找到的唯一匹配解决方案是:
低效的连接 . 例如
match(interaction(df1$a,df1$b),interaction(df2$a,df2$b))
,或与paste()
相同的想法 .低效的笛卡尔连词,例如
outer(df1$a,df2$a,
==) & outer(df1$b,df2$b,
==)
.base R
merge()
和等效的基于包的合并函数,它总是分配一个新表来返回合并结果,因此不适合基于内部赋值的解决方案 .例如,请参阅Matching multiple columns on different data frames and getting other column as result,match two columns with two other columns,Matching on multiple columns,以及我最初提出的就地解决方案Combine two data frames with different number of rows in R的问题 .
基准测试
我决定进行自己的基准测试,以了解就地分配方法与此问题中提供的其他解决方案的对比情况 .
测试代码:
这是我之前演示的基于OP的示例的基准:
在这里,我对随机输入数据进行基准测试,尝试不同的比例和两个输入表之间的键重叠的不同模式 . 此基准仍限于单列整数键的情况 . 同样,为确保就地解决方案适用于同一表的左右连接,所有随机测试数据都使用
0..1:0..1
基数 . 这是通过在生成第二个data.frame的键列时不替换第一个data.frame的键列而进行采样来实现的 .我写了一些代码来创建上述结果的日志 - 日志图 . 我为每个重叠百分比生成了一个单独的图 . 它有点杂乱,但我喜欢在同一个图中表示所有解决方案类型和连接类型 .
我使用样条插值来显示每个解决方案/连接类型组合的平滑曲线,使用单独的pch符号绘制 . 连接类型由pch符号捕获,左侧和右侧的内侧,左侧和右侧尖括号使用点,使用完整的菱形 . 解决方案类型由颜色捕获,如图例中所示 .
这里's a second large-scale benchmark that'在关键列的数量和类型以及基数方面更加重要 . 对于此基准测试,我使用三个关键列:一个字符,一个整数和一个逻辑,对基数没有限制(即
0..*:0..*
) . (一般情况下,'s not advisable to define key columns with double or complex values due to floating-point comparison complications, and basically no one ever uses the raw type, much less for key columns, so I haven' t包含了关键列中的那些类型 . 另外,由于某些原因,可能由于浮点比较异常而导致sqldf.indexed
解决方案与sqldf.indexed
解决方案配合良好,因此我删除了它 . )生成的图,使用上面给出的相同绘图代码:
对于所有列的内部联接,您还可以使用data.table-package中的
fintersect
或dplyr-package中的intersect
作为merge
的替代,而不指定by
-columns . 这将给出两个数据帧之间相等的行:例数据:
Update join. 另一个重要的SQL样式连接是“update join”,其中一个表中的列使用另一个表更新(或创建) .
修改OP的示例表...
假设我们要将客户的状态从
cust
添加到购买表sales
,忽略年份列 . 使用基数R,我们可以识别匹配的行,然后复制值:从这里可以看出,
match
从customer表中选择第一个匹配的行 .Update join with multiple columns. 当我们只加入一个列并且对第一个匹配感到满意时,上述方法很有效 . 假设我们希望客户表中的计量年份与销售年份相匹配 .
正如@ bgoldst的答案提到的那样,
match
与interaction
可能是这种情况的选择 . 更直接的是,可以使用data.table:Rolling update join. 或者,我们可能想要找到客户所在的最后一个州:
上面的三个示例都专注于创建/添加新列 . 有关更新/修改现有列的示例,请参见the related R FAQ .