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 代码看起来更加“优雅”。
- 单个关联条件
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 - 多个关联条件
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$b 和 x$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 rows2.4.1.3 集合操作
以下动词要求x,y具有相同的变量，也就是x,y的格式一样。
intersect(x,y)返回x,y交集union(x,y)返回x,y中唯一的值setdiff(x,y)返回存在x中但是不存在y中的记录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 22.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笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积(Cartesian product)，又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员↩︎