R语言ETL工程：连接（join）

作者： 黄天元 ，复旦大学博士在读，目前研究涉及文本挖掘、社交网络分析和机器学习等。希望与大家分享学习经验，推广并加深R语言在业界的应用。

邮箱：huang.tian-yuan@qq.com

本章将会介绍如何在R中完成数据表的连接操作 。首先要明确一个问题：为什么要进行连接？本质上来说，连接就是按照一定的对应规则，把两个表格合并为一个表格的操作。举个例子：比如我们一张表格中有乐队的歌手名字和他们所属的乐队，另一张表格有歌手的名字和他们擅长的乐器类型。因为两张表格都含有歌手的名字，而歌手的名字也是唯一的（在数据库理论框架中，这个属性被称为主键），即不存在一张表格会重复出现同一个歌手的名字。这个时候，我们就希望把两张表格合并起来，做一张包含歌手名字、所属乐队、擅长乐器的大表格。通过连接，我们能够把众多表格的数据合并起来，从而让孤立的数据能够联系在一起。

本章会采用较为简单的数据集进行实现，下面我们会参考dplyr的帮助文档举例，前期准备如下：

 1library(tidyverse)  2band_members  3  4## # A tibble: 3 x 2  5## name band   6## <chr> <chr>   7## 1 Mick Stones   8## 2 John Beatles  9## 3 Paul Beatles 10 11band_instruments 12 13## # A tibble: 3 x 2 14## name plays  15## <chr> <chr>  16## 1 John guitar 17## 2 Paul bass  18## 3 Keith guitar 19 20band_instruments2 21 22## # A tibble: 3 x 2 23## artist plays  24## <chr> <chr>  25## 1 John guitar 26## 2 Paul bass  27## 3 Keith guitar 

观察表格，我们可以知道，band_members包含了歌手名称和乐队信息，band_instruments包含歌手名称和乐器信息，band_instruments2与band_instrument包含的信息一样，但是歌手名称的列名称由name变化为artist。

基本概念

连接分为很多种，包括 内连接、全链接、左连接、右连接 等。我们先介绍一些基础的逻辑，先看下面这张图：

下面我们逐个讲解这些连接的概念。 首先我们来讲内连接，又叫做自然连接。还是歌手、乐队、乐器的例子，比如我们的A表格中有歌手名称和乐队的信息，B表格中有歌手名称和擅长乐器的信息。另外，我们发现两张表格中，A表格包含的歌手信息和B表格不同，有的歌手只有A表格有，B表格就没有；有的歌手只有B表格有，但是A表格没有。但是我们还是希望把A表格和B表格连接起来，形成一个大表格C。采用内连接的话，就会把A表格和B表格都有的歌手提取出来（也就是取了一个交集），然后对两个表格的列进行连接。

下面介绍左连接和右连接。如果是A表格左连接B表格的话，那么就是A表格的歌手肯定全部保留，如果在A里面有的歌手，在B中找不到，那么就需要填充缺失值，一切以A表格为主。理解了左连接，右连接就非常简单了，它其实就是左连接的逆运算，也就是说A右连接B，实际就是B左连接A。就是这么简单。 最后我们讲一下全连接。全连接就是A表格B表格的歌手我们统统都保留，但是如果A表格有的歌手B表格没有，那么在B表格的列中就需要填充缺失值；同理，如果B表格的歌手A没有，那么A表格带来的列也需要填充缺失值。

如果还是不理解，可以看一下下面这一张图片。

基本连接实操

内

连接

 1band_members %>%  2inner_join(band_instruments)  3  4## Joining, by = "name"  5  6## # A tibble: 2 x 3  7## name band plays   8## <chr> <chr> <chr>   9## 1 John Beatles guitar 10## 2 Paul Beatles bass 

SQL代码如下：

1<SQL> SELECT `TBL_LEFT`.`name` AS `name`, `TBL_LEFT`.`band` AS `band`, `TBL_RIGHT`.`plays` AS `plays` 2FROM `band_members` AS `TBL_LEFT` 3INNER JOIN `band_instruments` AS `TBL_RIGHT` 4ON (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`) 

需要注意的是，如果没有指定根据哪个列（主键）进行合并，那么在连接的时候，函数会自动默认用两个表格都包含的列进行连接。在我们的例子中，因为两个表格都包含名为name的列，因此会根据name来进行连接。如果需要指定用哪些列进行连接，可以更改by参数，例子如下：

1band_members %>%  2inner_join(band_instruments2, by = c("name" = "artist")) 3 4## # A tibble: 2 x 3 5## name band plays  6## <chr> <chr> <chr>  7## 1 John Beatles guitar 8## 2 Paul Beatles bass 

这样一来，我们就用band_members的name列与band_instrument2的artist进行了连接。

另外一点需要明确的是，合并之后，两个表合并的键只会保留其中一个，也就是第一个表。如果两者的键的名称不一样，也只会保留第一个，比如上面的例子中，我们合并的表格已经没有出现artist这一列，而是统一合并到name这一列中。

左

连接

 1band_members %>%   2left_join(band_instruments)  3  4## Joining, by = "name"  5  6## # A tibble: 3 x 3  7## name band plays   8## <chr> <chr> <chr>   9## 1 Mick Stones <NA>  10## 2 John Beatles guitar 11## 3 Paul Beatles bass 

SQL代码如下：

1<SQL> SELECT `TBL_LEFT`.`name` AS `name`, `TBL_LEFT`.`band` AS `band`, `TBL_RIGHT`.`plays` AS `plays` 2FROM `band_members` AS `TBL_LEFT` 3LEFT JOIN `band_instruments` AS `TBL_RIGHT` 4ON (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`) 

右

连接

 1band_members %>%   2right_join(band_instruments)  3  4## Joining, by = "name"  5  6## # A tibble: 3 x 3  7## name band plays   8## <chr> <chr> <chr>   9## 1 John Beatles guitar 10## 2 Paul Beatles bass  11## 3 Keith <NA> guitar 

SQL代码如下：

1<SQL> SELECT `TBL_RIGHT`.`name` AS `name`, `TBL_LEFT`.`band` AS `band`, `TBL_RIGHT`.`plays` AS `plays` 2FROM `band_members` AS `TBL_LEFT` 3RIGHT JOIN `band_instruments` AS `TBL_RIGHT` 4ON (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`) 

全

连接

 1band_members %>%   2full_join(band_instruments)  3  4## Joining, by = "name"  5  6## # A tibble: 4 x 3  7## name band plays   8## <chr> <chr> <chr>   9## 1 Mick Stones <NA>  10## 2 John Beatles guitar 11## 3 Paul Beatles bass  12## 4 Keith <NA> guitar 

SQL代码如下：

1<SQL> SELECT coalesce(`TBL_LEFT`.`name`, `TBL_RIGHT`.`name`) AS `name`, `TBL_LEFT`.`band` AS `band`, `TBL_RIGHT`.`plays` AS `plays` 2FROM `band_members` AS `TBL_LEFT` 3FULL JOIN `band_instruments` AS `TBL_RIGHT` 4ON (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`) 

大家可以通过观察结果来体会不同连接的效果。

如果两个表格中用相同名字的列怎么办？连接函数会自动给同名列加入后缀，比如A表格和B表格都有一列名为same，那么合并之后，会出现两列，名字分别为same.x和same.y。后缀名是可以变更的，你可以通过suffix参数对后缀名进行设置。

基

于多个列进行连接

有时候我们不仅仅需要基于单列连接，而且要用到基于多个列的连接。比如我们遇到这么一个问题：我们两个表格都有年、月、日的数据，我们需要把两个表格按照日期对应起来，那么就需要年月日都对的上号才能够进行连接。下面我们来举个例子，我们先构造两个表：

 1d1 <- tibble(  2x = letters[4:6],  3y = LETTERS[4:6],  4a = rnorm(3)  5)  6  7d2 <- tibble(  8x2 = letters[6:4],  9y2 = LETTERS[6:4], 10b = rnorm(3) 11) 12 13d1 14 15## # A tibble: 3 x 3 16## x y a 17## <chr> <chr> <dbl> 18## 1 d D 0.140  19## 2 e E 0.0746 20## 3 f F -0.549 21 22d2 23 24## # A tibble: 3 x 3 25## x2 y2 b 26## <chr> <chr> <dbl> 27## 1 f F -0.355 28## 2 e E -1.47  29## 3 d D -0.490 

下面我们要把d1和d2连接起来，根据它们的前两列：

1left_join(d1, d2, by = c("x" = "x2", "y" = "y2")) 2 3## # A tibble: 3 x 4 4## x y a b 5## <chr> <chr> <dbl> <dbl> 6## 1 d D 0.140 -0.490 7## 2 e E 0.0746 -1.47  8## 3 f F -0.549 -0.355 

如果不能够确定两个表格中是否有以及有多少相同的列名称，在连接的时候应该总是指定by参数，来决定两个表格究竟基于什么键进行连接。

事实上，两个表格的连接可以基于无穷多的列，比如年月日就涉及了3列，如果再加上地理位置等信息，就更多了。这也充分告诉我们，连接能够给我们连接数据带来巨大的便利。

半

连接（semi_join）与反连接（anti_join）

半连接与反连接在实际工作中相对比较少用，但是这也取决于你的工作内容是什么。首先需要声明的是，半连接和反连接都只会返回“左表”的内容，也就是A表格与B表格进行半连接或反连接的时候，只会返回A表格的信息，不会返回B表格的信息。其中，半连接其实相当于进行了一次内连接，不过，它不会保留B表格的任何内容（也就是没有保留B表格的列）。反连接则与半连接相反，它会匹配在A表格出现而B表格没有出现的键值，比如A表格x列出现了a,b,c，B表格x列出现了b,c,d，那么半连接会保留A表格x列出现b,c的记录，反连接则会保留A表格x列出现a的记录。

纸上得来终觉浅，我们来进行实际操作：

 1band_members %>%   2semi_join(band_instruments)  3  4## Joining, by = "name"  5  6## # A tibble: 2 x 2  7## name band   8## <chr> <chr>   9## 1 John Beatles 10## 2 Paul Beatles 11 12band_members %>%  13anti_join(band_instruments) 14 15## Joining, by = "name" 16 17## # A tibble: 1 x 2 18## name band  19## <chr> <chr>  20## 1 Mick Stones 

SQL代码如下：

 1<SQL> SELECT * FROM `band_members` AS `TBL_LEFT`  2  3WHERE EXISTS (  4SELECT 1 FROM `band_instruments` AS `TBL_RIGHT`  5WHERE (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`)  6)  7  8<SQL> SELECT * FROM `band_members` AS `TBL_LEFT`  9 10WHERE NOT EXISTS ( 11SELECT 1 FROM `band_instruments` AS `TBL_RIGHT` 12WHERE (`TBL_LEFT`.`name` = `TBL_RIGHT`.`name`) 13)

小

结

本章介绍了强大的数据表连接操作，在实际工作中，连接操作的速度是非常快的，效率极高。对于内连接、全连接、左连接、右连接是我们工作中最常用的连接方法，需要牢牢把握。我们还介绍了如何根据多个键值进行连接，并简要介绍了半连接和反连接的概念和操作方法。需要注意的是，当进行复杂连接时，一定要用by参数指定不同表格中用于连接的键，否则容易发生错误。

——————————————

往期精彩：

• IT男，程序猿在婚恋市场有多受欢迎？

• 都挺好，怎么好？

• 史上最污技术解读，让你秒懂IT术语

• R语言中文社区2018年终文章整理（作者篇）

• R语言中文社区2018年终文章整理（类型篇）

天善智能 每日一道算法题，打卡学习 小程序

本文由R语言中文社区 创作，采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。

转载、引用前需联系作者，并署名作者且注明文章出处。

本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。