2.4 表关联

在我们做数据分析项目时,大多数时候数据源不止一个,我们需要通过表间共有信息字段将表关联,让表信息更加全面,从而方便做各种信息分类汇总。

dplyr 中表关联是通过一组函数实现,像sql中的left join,inner join等表格之间的操作,或者是Excel中Power Piovt建模的建立关系,从而实现不同表格间的关联。

2.4.1 两表关联

dplyr 中left_join(),full_join,inner_join()等动词关联两个表,详情请查看:vignette("two-table")

left_join(),right_join(),full_join(),inner_join(),第一个以左表为主,第二个右表为主,第三个全连接,第四个内连接(只返回两表中都有的记录),和数据库中用法一致。

left_join()实现类似 Excel中VLOOKUP函数功能,将匹配到的“右表”字段依据对应关系关联到“左表”上。

2.4.1.1 基础用法

dplyr中的关联函数的关联条件可以分为:

  • 同名列关联

  • 不同名称列关联

2.4.1.1.1 同名列关联

默认同名列关联,也可指定关联条件列,类似数据库中on a.column = b.column,但 R 代码看起来更加“优雅”。

  1. 单个关联条件
library("nycflights13")
flights2 <- flights %>% select(year:day, hour, origin, dest, tailnum, carrier)

# 默认同名
flights2 %>% left_join(airlines)
#> Joining, by = "carrier"
#> # A tibble: 336,776 x 9
#>    year month   day  hour origin dest  tailnum carrier name                  
#>   <int> <int> <int> <dbl> <chr>  <chr> <chr>   <chr>   <chr>                 
#> 1  2013     1     1     5 EWR    IAH   N14228  UA      United Air Lines Inc. 
#> 2  2013     1     1     5 LGA    IAH   N24211  UA      United Air Lines Inc. 
#> 3  2013     1     1     5 JFK    MIA   N619AA  AA      American Airlines Inc.
#> 4  2013     1     1     5 JFK    BQN   N804JB  B6      JetBlue Airways       
#> 5  2013     1     1     6 LGA    ATL   N668DN  DL      Delta Air Lines Inc.  
#> 6  2013     1     1     5 EWR    ORD   N39463  UA      United Air Lines Inc. 
#> # ... with 336,770 more rows

在某些时候,不同的表间可能出现同名的字段,但是并不能作为关联字段,这是就需要人为指定关联字段,并且建议在写代码的时候明确指定关联字段,能避免一些可能不必要的错误。

# 或指定列名
flights2 %>% left_join(planes, by = "tailnum")
#> # A tibble: 336,776 x 16
#>   year.x month   day  hour origin dest  tailnum carrier year.y type             
#>    <int> <int> <int> <dbl> <chr>  <chr> <chr>   <chr>    <int> <chr>            
#> 1   2013     1     1     5 EWR    IAH   N14228  UA        1999 Fixed wing multi~
#> 2   2013     1     1     5 LGA    IAH   N24211  UA        1998 Fixed wing multi~
#> 3   2013     1     1     5 JFK    MIA   N619AA  AA        1990 Fixed wing multi~
#> 4   2013     1     1     5 JFK    BQN   N804JB  B6        2012 Fixed wing multi~
#> 5   2013     1     1     6 LGA    ATL   N668DN  DL        1991 Fixed wing multi~
#> 6   2013     1     1     5 EWR    ORD   N39463  UA        2012 Fixed wing multi~
#> # ... with 336,770 more rows, and 6 more variables: manufacturer <chr>,
#> #   model <chr>, engines <int>, seats <int>, speed <int>, engine <chr>

像 Sql 中指定关联字段,以下 Sql 代码和上面 R 代码 等效。

select * from flights2 a left join planes b on a.tailnum = b.tailnum 
  1. 多个关联条件
dta <- data.frame(name = 'vega',grade = 1)
dtb <- data.frame(name = c('vega','yufei'),grade = 1:2,性别 = c('男','男'))
left_join(dta,dtb,by=c('name','grade'))
#>   name grade 性别
#> 1 vega     1   男

必须指定关联字段的情况,如下所示:

# id 是无效列,默认同名关联的话导致无法正常返回结果

dta <- data.frame(id = 1:2, name = c('vega','yufei'),grade = 1:2)
dtb <- data.frame(id = 2:1,name = c('vega','yufei'),grade = 1:2,性别 = c('男','男'))
left_join(dta,dtb)
#> Joining, by = c("id", "name", "grade")
#>   id  name grade 性别
#> 1  1  vega     1 <NA>
#> 2  2 yufei     2 <NA>
left_join(dta,dtb,by=c('name','grade'))
#>   id.x  name grade id.y 性别
#> 1    1  vega     1    2   男
#> 2    2 yufei     2    1   男
2.4.1.1.2 不同名称列关联

left_join(x,y,by = c("a" = "b", "c" = "d")) 将会匹配 x$a to y$bx$c to y$d 作为关联条件。

#出发机场和目的机场信息

flights2 %>% left_join(airports, by = c("dest" = "faa"))
#> # A tibble: 336,776 x 15
#>    year month   day  hour origin dest  tailnum carrier name      lat   lon   alt
#>   <int> <int> <int> <dbl> <chr>  <chr> <chr>   <chr>   <chr>   <dbl> <dbl> <dbl>
#> 1  2013     1     1     5 EWR    IAH   N14228  UA      George~  30.0 -95.3    97
#> 2  2013     1     1     5 LGA    IAH   N24211  UA      George~  30.0 -95.3    97
#> 3  2013     1     1     5 JFK    MIA   N619AA  AA      Miami ~  25.8 -80.3     8
#> 4  2013     1     1     5 JFK    BQN   N804JB  B6      <NA>     NA    NA      NA
#> 5  2013     1     1     6 LGA    ATL   N668DN  DL      Hartsf~  33.6 -84.4  1026
#> 6  2013     1     1     5 EWR    ORD   N39463  UA      Chicag~  42.0 -87.9   668
#> # ... with 336,770 more rows, and 3 more variables: tz <dbl>, dst <chr>,
#> #   tzone <chr>
# flights2 %>% left_join(airports, c("origin" = "faa"))

# 多条件关联时用逗号隔开
dta <- data.frame(id = 1:2, Name = c('vega','yufei'),Grade = 1:2)
dtb <- data.frame(id = 2:1,name = c('vega','yufei'),grade = 1:2,性别 = c('男','男'))
left_join(dta,dtb,by = c("Name" = "name","Grade" = "grade")) %>% 
  select(-id.x,-id.y)
#>    Name Grade 性别
#> 1  vega     1   男
#> 2 yufei     2   男
2.4.1.1.3 不唯一关联

如果关联表中值不是唯一的,连接将添加匹配表的所有可能组合(笛卡尔积):

右表重复

df1 <- tibble(x = c(1,  2), y = 1:2)
df2 <- tibble(x = c(1, 1, 2,2), z = c("a", "a", "b","b"))
df1 %>% left_join(df2)
#> Joining, by = "x"
#> # A tibble: 4 x 3
#>       x     y z    
#>   <dbl> <int> <chr>
#> 1     1     1 a    
#> 2     1     1 a    
#> 3     2     2 b    
#> 4     2     2 b
df1 %>% right_join(df2)
#> Joining, by = "x"
#> # A tibble: 4 x 3
#>       x     y z    
#>   <dbl> <int> <chr>
#> 1     1     1 a    
#> 2     1     1 a    
#> 3     2     2 b    
#> 4     2     2 b

两表重复

df1 <- tibble(x = c(1, 1, 2), y = 1:3)
df2 <- tibble(x = c(1, 1, 2), z = c("a", "b", "a"))

df1 %>% left_join(df2)
#> Joining, by = "x"
#> # A tibble: 5 x 3
#>       x     y z    
#>   <dbl> <int> <chr>
#> 1     1     1 a    
#> 2     1     1 b    
#> 3     1     2 a    
#> 4     1     2 b    
#> 5     2     3 a

在我们工作中可能需要求两个表之间的笛卡尔乘积5,并且没有可关联字段,该如何求?

dta <- data.frame(a=letters)
dtb <- data.frame(b=LETTERS)

full_join(dta,dtb,by = character())%>% 
  glimpse()
#> Rows: 676
#> Columns: 2
#> $ a <chr> "a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a"~
#> $ b <chr> "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N"~

以上是简单的构造笛卡尔积表的办法,我们还可以通过构建相同列达到效果,如下所示:

dta %>%
  mutate(col = 1) %>% 
  left_join(dtb %>%  mutate(col=1)) %>% 
  mutate(col = NULL) %>% 
  glimpse()
#> Joining, by = "col"
#> Rows: 676
#> Columns: 2
#> $ a <chr> "a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a", "a"~
#> $ b <chr> "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N"~

2.4.1.2 筛选关联

在某些时候筛选关联会比较方便,但是我个人比较少用。

anti_join():删除所有左表中在右表中匹配到的行

semi_join():保留所有左表在右表中匹配到的行

df1 <- tibble(a=letters[1:20],b=1:20)
df2 <- tibble(a=letters,b=1:26)

df2 %>% anti_join(df1)
#> Joining, by = c("a", "b")
#> # A tibble: 6 x 2
#>   a         b
#>   <chr> <int>
#> 1 u        21
#> 2 v        22
#> 3 w        23
#> 4 x        24
#> 5 y        25
#> 6 z        26
df1 %>% semi_join(df2)
#> Joining, by = c("a", "b")
#> # A tibble: 20 x 2
#>   a         b
#>   <chr> <int>
#> 1 a         1
#> 2 b         2
#> 3 c         3
#> 4 d         4
#> 5 e         5
#> 6 f         6
#> # ... with 14 more rows

2.4.1.3 集合操作

以下动词要求x,y具有相同的变量,也就是x,y的格式一样。

  1. intersect(x,y)返回x,y交集

  2. union(x,y)返回x,y中唯一的值

  3. setdiff(x,y)返回存在x中但是不存在y中的记录

  4. union_all(x,y)返回全部值,不删除重复值

(df1 <- tibble(x = 1:2, y = c(1L, 1L)))
#> # A tibble: 2 x 2
#>       x     y
#>   <int> <int>
#> 1     1     1
#> 2     2     1
(df2 <- tibble(x = 1:2, y = 1:2))
#> # A tibble: 2 x 2
#>       x     y
#>   <int> <int>
#> 1     1     1
#> 2     2     2
intersect(df1, df2)
#> # A tibble: 1 x 2
#>       x     y
#>   <int> <int>
#> 1     1     1
union(df1, df2)
#> # A tibble: 3 x 2
#>       x     y
#>   <int> <int>
#> 1     1     1
#> 2     2     1
#> 3     2     2
union_all(df1, df2)
#> # A tibble: 4 x 2
#>       x     y
#>   <int> <int>
#> 1     1     1
#> 2     2     1
#> 3     1     1
#> 4     2     2
setdiff(df1, df2)
#> # A tibble: 1 x 2
#>       x     y
#>   <int> <int>
#> 1     2     1
setdiff(df2, df1)
#> # A tibble: 1 x 2
#>       x     y
#>   <int> <int>
#> 1     2     2

2.4.2 多表操作

当我们需要将多个表连续关联时:

  • 常规写法
dt1 <- data.frame(x = letters)
dt2 <- data.frame(x = letters,cola = 1:26)
dt3 <- data.frame(x = letters,colb = 1:26)
dt4 <- data.frame(x = letters,cold = 1:26)
dt5 <- data.frame(x = letters,cole = 1:26)
dt1 %>% 
  left_join(dt2) %>% 
  left_join(dt3) %>% 
  left_join(dt4) %>% 
  left_join(dt5)
#> Joining, by = "x"
#> Joining, by = "x"
#> Joining, by = "x"
#> Joining, by = "x"
#>    x cola colb cold cole
#> 1  a    1    1    1    1
#> 2  b    2    2    2    2
#> 3  c    3    3    3    3
#> 4  d    4    4    4    4
#> 5  e    5    5    5    5
#> 6  f    6    6    6    6
#> 7  g    7    7    7    7
#> 8  h    8    8    8    8
#> 9  i    9    9    9    9
#> 10 j   10   10   10   10
#> 11 k   11   11   11   11
#> 12 l   12   12   12   12
#> 13 m   13   13   13   13
#> 14 n   14   14   14   14
#> 15 o   15   15   15   15
#> 16 p   16   16   16   16
#> 17 q   17   17   17   17
#> 18 r   18   18   18   18
#> 19 s   19   19   19   19
#> 20 t   20   20   20   20
#> 21 u   21   21   21   21
#> 22 v   22   22   22   22
#> 23 w   23   23   23   23
#> 24 x   24   24   24   24
#> 25 y   25   25   25   25
#> 26 z   26   26   26   26
  • 改进写法

当需要合并多个表时,可用purrr::reduce()实现

dtlist <- list(dt1,dt2,dt3,dt4,dt5)
purrr::reduce(dtlist,left_join,by='x')
#>    x cola colb cold cole
#> 1  a    1    1    1    1
#> 2  b    2    2    2    2
#> 3  c    3    3    3    3
#> 4  d    4    4    4    4
#> 5  e    5    5    5    5
#> 6  f    6    6    6    6
#> 7  g    7    7    7    7
#> 8  h    8    8    8    8
#> 9  i    9    9    9    9
#> 10 j   10   10   10   10
#> 11 k   11   11   11   11
#> 12 l   12   12   12   12
#> 13 m   13   13   13   13
#> 14 n   14   14   14   14
#> 15 o   15   15   15   15
#> 16 p   16   16   16   16
#> 17 q   17   17   17   17
#> 18 r   18   18   18   18
#> 19 s   19   19   19   19
#> 20 t   20   20   20   20
#> 21 u   21   21   21   21
#> 22 v   22   22   22   22
#> 23 w   23   23   23   23
#> 24 x   24   24   24   24
#> 25 y   25   25   25   25
#> 26 z   26   26   26   26

  1. 笛卡尔乘积是指在数学中,两个集合X和Y的笛卡尔积(Cartesian product),又称直积,表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员↩︎