7 · Join (exercises) — source
A. Stamm 
This page shows the source file of the exercise below. Use the button to download it as a plain file and run it in your own workspace.
---
title: "Join Data with dplyr"
---
```afu
#| label: setup
#| include: false
library(tidyverse)
library(DT)
library(nycflights13)
band <- tribble(
~name, ~band,
"Mick", "Stones",
"John", "Beatles",
"Paul", "Beatles"
)
instrument <- tribble(
~name, ~plays,
"John", "guitar",
"Paul", "bass",
"Keith", "guitar"
)
instrument2 <- tribble(
~artist, ~plays,
"John", "guitar",
"Paul", "bass",
"Keith", "guitar"
)
```
# nycflights13
```afu
flights
```
Visualizing a raw tibble is not that pretty. Let's wrap the next output in a `DT::datatable()` function to make it more readable:
```afu
DT::datatable(airlines)
```
# mutating joins
```afu
band |> left_join(instrument, by = "name")
band |> right_join(instrument, by = "name")
band |> full_join(instrument, by = "name")
band |> inner_join(instrument, by = "name")
```
## Your Turn 1
Which airlines had the largest arrival delays? Complete the code below.
1. Join `airlines` to `flights`
2. Compute and order the average arrival delays by airline. Display full names, no codes.
*(Hint: Be sure to remove each `_` before turning eval to true)*
```afu
#| eval: false
flights |>
filter(!is.na(arr_delay)) |>
__________________ |>
group_by(_________) |>
__________________ |>
arrange(__________)
```
## Your Turn 2
Join `flights` and `airports` by `dest` and `faa`.
Then for each `name`, compute the `distance` from NYC and the average `arr_delay`. *Hint: use `first()` to get the first value of distance.*
Order by average delay, worst to best.
*(Hint: Be sure to remove each `_` before turning eval to true)*
```afu
#| eval: false
flights |>
filter(!is.na(arr_delay)) |>
______(airports, __________________) |>
group_by(name) |>
_________(distance = _____________,
delay = _____________) |>
______(_____(delay))
```
# filtering joins
```afu
band |> semi_join(instrument, by = "name")
band |> anti_join(instrument, by = "name")
```
## Your Turn 3
How many airports in `airports` are serviced by flights in `flights`? (i.e. how many places can you fly to direct from New York?)
Notice that the column to join on is named `faa` in the **airports** data set and `dest` in the **flights** data set.
*(Hint: Be sure to remove each `_` before turning eval to true)*
```afu
#| eval: false
__________ |>
_____________________________ |>
select(faa)
```
# Take aways
* `left_join()` retains all cases in _left_ data set
* `right_join()` retains all cases in _right_ data set
* `full_join()` retains all cases in _either_ data set
* `inner_join()` retains _only_ cases in _both_ data sets
* `semi_join()` extracts cases that _have_ a match
* `anti_join()` extracts cases that _do not have_ a match