#使用dplyr处理关系数据
#要想处理关系数据，你需要能够在两张表之间进行的操作。我们设计了三类操作来处理关系数据。
#• 合并连接:向数据框中加入新变量，新变量的值是另一个数据框中的匹配观测。
#• 筛选连接:根据是否匹配另一个数据框中的观测，筛选数据框中的观测。
#• 集合操作:将观测作为集合元素来处理。
#关系数据最常见于关系数据库管理系统(relational database management system，RDBMS)， 该系统几乎囊括了所有的现代数据库。
library(tidyverse)
library(nycflights13)
#nycflights13 中包含了与 flights 相关的 4 个tibble
#• airlines:可以根据航空公司的缩写码查到公司全名。
airlines
#• airports:给出了每个机场的信息，通过 faa 机场编码进行标识。
airports
#• planes:给出了每架飞机的信息，通过 tailnum 进行标识。
planes
#• weather:给出了纽约机场每小时的天气状况。
weather
#展示不同数据表之间关系的一种方法是绘制图形。
#记住每种关系只与两张表有关。
#对于 nycflights13 包中的表来说:
#• flights 与 planes 通过单变量 tailnum 相连;
#• flights 与 airlines 通过变量 carrier 相连;
#• flights 与 airports 通过两种方式相连(变量 origin 和 dest);
#• flights 与 weather 通过变量 origin(位置)以及 year、month、day 和 hour(时间)相连。
#键
#用于连接每对数据表的变量称为键。
#键是能唯一标识观测的变量(或变量集合)。
#简单情况下，单个变量就足以标识一个观测。
#例如，每架飞机都可以由 tailnum 唯一标识。
#其他情况可能需要多个变量。
#例如，要想标识 weather 中的观测，你需要 5 个变量:year、 month、day、hour 和 origin。
#键的类型有两种。
#• 主键:唯一标识其所在数据表中的观测。
#例如，planes$tailnum 是一个主键，因为其可以唯一标识 planes 表中的每架飞机。
#• 外键:唯一标识另一个数据表中的观测。
#例如，flights$tailnum 是一个外键，因为其出现在 flights 表中，并可以将每次航班与唯一一架飞机匹配。
#一个变量既可以是主键，也可以是外键。
#例如，origin 是 weather 表主键的一部分，同时也是 airports 表的外键。
#一旦识别出表的主键，最好验证一下，看看它们能否真正唯一标识每个观测。
#一种验证方法是对主键进行 count() 操作，然后查看是否有 n 大于 1 的记录
planes %>%
count(tailnum) %>%
filter(n > 1)
weather %>%
count(year, month, day, hour, origin) %>%
filter(n > 1)
#有时数据表没有明确的主键:每行都是一个观测，但没有一个变量组合能够明确地标识它。
#例如，flights 表中的主键是什么?你可能认为是日期加航班号或者是日期加机尾编号，但这两种组合都不是唯一标识:
flights %>%
count(year, month, day, flight) %>%
filter(n > 1)
flights %>%
count(year, month, day, tailnum) %>%
filter(n > 1)
#如果一张表没有 主键，有时就需要使用 mutate() 函数和 row_number() 函数为表加上一个主键。
#如果你完成了一些筛选工作，并想要使用原始数据检查的话，就可以更容易地匹配观测。 这种主键称为代理键。
#主键与另一张表中与之对应的外键可以构成关系。
#关系通常是一对多的。例如，每个航班只有一架飞机，但每架飞机可以飞多个航班。
#在另一些数据中，你有时还会遇到一对一的关系。
#你可以将这种关系看作一对多关系的特殊情况。
#你可以使用多对一关系加上一对多关系来构造多对多关系。
#例如，在这份数据中，航空公司与机场之间存在着多对多关系:
#每个航空公司可以使用多个机场，每个机场可以服务多个航空公司。
#合并连接
#本节将介绍用于组合两个表格的第一种工具，即合并连接。
#合并连接可以将两个表格中的变量组合起来，它先通过两个表格的键匹配观测，然后将一个表格中的变量复制到另一个表格中。
#连接函数也会将变量添加在表格的右侧
flights2 <- flights %>%
select(year:day, hour, origin, dest, tailnum, carrier)
flights2
#假设想要将航空公司的全名加入 flights2 数据集，
#你可以通过 left_join() 函数组合airlines 和 flights2 数据框:
flights2 %>%
select(-origin, -dest) %>%
left_join(airlines, by = "carrier")
#将航空公司数据连接到 flights2 的结果产生了一个新变量:name。
#对于这个示例，我们可以通过 mutate() 函数和 R 的取子集操作达到同样的效果:
flights2 %>%
select(-origin, -dest) %>%
mutate(name = airlines$name[match(carrier, airlines$carrier)])
#但这种方式很难推广到需要匹配多个变量的情况
#理解连接
x <- tribble(
~key, ~val_x,
1, "x1",
2, "x2",
3, "x3"
)
y <- tribble(
~key, ~val_y,
1, "y1",
2, "y2",
4, "y3" )
#连接是将 x 中每行连接到 y 中 0 行、一行或多行的一种方法。
#匹配就是两行之间的交集。
#内连接
#内连接是最简单的一种连接。
#只要两个观测的键是相等的，内连接就可以匹配它们。
#内连接的结果是一个新数据框，其中包含键、x 值和 y 值。
#我们使用 by 参数告诉 dplyr 哪 个变量是键:
x %>%
inner_join(y, by = "key")
#内连接最重要的性质是，没有匹配的行不会包含在结果中。
#这意味着内连接一般不适合在分析中使用，因为太容易丢失观测了。
#外连接
#内连接保留同时存在于两个表中的观测，
#外连接则保留至少存在于一个表中的观测。
#外连接有 3 种类型。
#• 左连接:保留 x 中的所有观测。left_join(x,y)
#• 右连接:保留 y 中的所有观测.right_join(x,y)
#• 全连接:保留 x 和 y 中的所有观测。full_join(x,y)
#这些连接会向每个表中添加额外的“虚拟”观测，
#这个观测拥有总是匹配的键(如果没有其他键可匹配的话)，
#其值则用 NA 来填充。
#最常用的连接是左连接:只要想从另一张表中添加数据，就可以使用左连接，
#因为它会保留原表中的所有观测，即使它没有匹配。
#左连接应该是你的默认选择，除非有足够充分的 理由选择其他的连接方式。
#重复键
#当键不唯一时将会发生的两种情况。
#• 一张表中具有重复键。
#通常来说，当存在一对多关系时，如果你想要向表中添加额外信息，就会出现这种情况。
x <- tribble(
~key, ~val_x,
1, "x1",
2, "x2",
2, "x3",
1, "x4"
)
y <- tribble(
~key, ~val_y,
1, "y1",
2, "y2" )
left_join(x, y, by = "key")
#• 两张表中都有重复键。
#这通常意味着出现了错误，因为键在任意一张表中都不能唯一标识观测。
#当连接这样的重复键时，你会得到所有可能的组合，即笛卡儿积
x <- tribble(
~key, ~val_x,
1, "x1",
2, "x2",
2, "x3",
3, "x4"
)
y <- tribble(
~key, ~val_y,
1, "y1",
2, "y2",
2, "y3",
3, "y4" )
left_join(x, y, by = "key")
#定义键列
#迄今为止，两张表都是通过一个单变量来连接的，
#而且这个变量在两张表中具有同样的名称。
#这种限制条件是通过by = "key"来实现的。
#• 默认值by = NULL。这会使用存在于两个表中的所有变量，这种方式称为自然连接。
#匹配航班表和天气表时使用的就是其公共变量:year、month、day、hour 和 origin。
flights2 %>%
left_join(weather)
#• 字符向量by = "x"。这种方式与自然连接很相似，但只使用某些公共变量。
#例如， flights 和 planes 表中都有 year 变量，但是它们的意义不同，因此我们只通过 tailnum 进行连接:
flights2 %>%
left_join(planes, by = "tailnum")
#注意，结果中的 year 变量(同时存在于两个输入数据框中，但并不要求相等)添加了一个后缀，以消除歧义。
#• 命名字符向量by = c("a" = "b")。
#这种方式会匹配x表中的a变量和y表中的b变量。
#输出结果中使用的是 x 表中的变量。
#因为每次航班都有起点机场和终点机场，所以需要指定使用哪个机场进行连接:
flights2 %>%
left_join(airports, c("dest" = "faa"))
flights2 %>%
left_join(airports, c("origin" = "faa"))
#筛选连接
#筛选连接匹配观测的方式与合并连接相同，但前者影响的是观测，而不是变量。
#筛选连接有两种类型。
#• semi_join(x, y):保留 x 表中与 y 表中的观测相匹配的所有观测。
#• anti_join(x, y):丢弃 x 表中与 y 表中的观测相匹配的所有观测。
#对数据表进行筛选或摘要统计后，
#如果想要使用表中原来的行来匹配筛选或摘要结果，
#那么半连接是非常有用的。
#例如，假设你已经找出了最受欢迎的前 10 个目的地:
top_dest <- flights %>% count(dest, sort = TRUE) %>% head(10)
top_dest
#现在想要找出飞往这些目的地的所有航班，你可以自己构造一个筛选器:
flights %>%
filter(dest %in% top_dest$dest)
#但这种方法很难扩展到多个变量。例如，假设已经找出了平均延误时间最长的 10 天，那么你应该如何使用 year、month 和 day 来构造筛选语句，才能在 flights 中找出这 10 天的观测?
#此时你应该使用半连接，它可以像合并连接一样连接两个表，但不添加新列，而是保留 x 表中那些可以匹配 y 表的行:
flights %>%
semi_join(top_dest)
#重要的是存在匹配，匹配了哪条观测则无关紧要。
#这说明筛选连接不会像合并连接那样造成重复的行。
#半连接的逆操作是反连接。反连接保留 x 表中那些没有匹配 y 表的行。
#反连接可以用于诊断连接中的不匹配。例如，在连接 flights 和 planes 时，你可能想知道 flights 中是否有很多行在 planes 中没有匹配记录:
flights %>%
anti_join(planes, by = "tailnum") %>% count(tailnum, sort = TRUE)
#连接中的问题
#(1) 首先，需要找出每个表中可以作为主键的变量。
#(2) 确保主键中的每个变量都没有缺失值。如果有缺失值，那么这个变量就不能标识观测!
#(3) 检查外键是否与另一张表的主键相匹配。
#最好的方法是使用 anti_join()，
#由于数据录入错误，外键和主键不匹配的情况很常见。
#解决这种问题通常需要大量工作。
#如果键中确实有缺失值，那么你就要深思熟虑一下，是应该使用内连接还是外连接，
#此外，是否应该丢弃那些没有匹配记录的行。
#注意，仅凭检查连接前后的行数是不足以确保连接能够顺畅运行的。
#集合操作
#两表之间的最后一种操作就是集合操作。
#但如果你想要将一个复杂的筛选操作分解为多个简单部分时，它们还是有些用处的。
#所有集合操作都是作用于整行的，比较的是每个变量的值。
#集合操作需要 x 和 y 具有相同的变量，并将观测按照集合来处理。
#intersect(x, y)
#返回既在 x 表，又在 y 表中的观测。
#union(x, y)
#返回 x 表或 y 表中的唯一观测。
#setdiff(x, y)
#返回在 x 表，但不在 y 表中的观测。
#给定以下简单数据:
df1 <- tribble(
~x, ~y,
1, 1,
2, 1 )
df2 <- tribble(
~x, ~y,
1, 1,
1, 2 )
#4 种可能的集合操作为:
intersect(df1, df2)
#> # A tibble: 1 × 2
#> x y
#> <dbl> <dbl>
#> 1 1 1
union(df1, df2)
#> # A tibble: 3 × 2
#> x y
#> <dbl> <dbl>
#> 1 1 2
#> 2 2 1
#> 3 1 1
# 注意，我们得到了3行，而不是4行
setdiff(df1, df2)
#> # A tibble: 1 × 2 #> x y
#> <dbl> <dbl>
#> 1 2 1
setdiff(df2, df1)
#> # A tibble: 1 × 2 #> x y
#> <dbl> <dbl>
#> 1 1 2
