One way to show the relationships between the different tables is with a drawing:
Here the relations between tables are represented by key matching, which we will discuss in the next.
To help us learn how joins work, let’s look at the following visual representation:
x <- tribble(
~key, ~val_x,
1, "x1",
2, "x2",
3, "x3"
)
y <- tribble(
~key, ~val_y,
1, "y1",
2, "y2",
4, "y3"
)The coloured column represents the “key” variable: these are used to match the rows between the tables. The grey column represents the “value” column that is carried along for the ride. In these examples we use a single key variable, but the idea generalises in a straightforward way to multiple keys and multiple values.
The simplest type of join is the inner join. An inner join matches pairs of observations whenever their keys are equal:
x %>%
inner_join(y, by = "key")## # A tibble: 2 × 3
## key val_x val_y
## <dbl> <chr> <chr>
## 1 1 x1 y1
## 2 2 x2 y2For inner join, unmatched rows are not included in the result. This means that generally inner joins are usually not appropriate for use in analysis because it’s too easy to lose observations.
An inner join keeps observations that appear in both tables. An outer join keeps observations that appear in at least one of the tables. There are three types of outer joins:
x.y.x and
y.
In outer joins, the values of new columns are filled with
NA if there is no key matching. Another way to depict the
different types of joins is with a Venn diagram:
The most commonly used join is the left join: you use this whenever you look up additional data from another table, because it preserves the original observations even when there isn’t a match. The left join should be your default join: use it unless you have a strong reason to prefer one of the others.
The Venn diagram cannot well illustrate what will happen when there are duplicated keys. There are two possibilities:
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")## # A tibble: 4 × 3
## key val_x val_y
## <dbl> <chr> <chr>
## 1 1 x1 y1
## 2 2 x2 y2
## 3 2 x3 y2
## 4 1 x4 y1
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")## # A tibble: 6 × 3
## key val_x val_y
## <dbl> <chr> <chr>
## 1 1 x1 y1
## 2 2 x2 y2
## 3 2 x2 y3
## 4 2 x3 y2
## 5 2 x3 y3
## 6 3 x4 y4semi_join()Semi-joins are useful for matching filtered summary tables back to the original rows. For example, imagine you’ve found the top ten most popular destinations:
top_dest <- flights %>%
count(dest, sort = TRUE) %>%
head(10)
top_dest## # A tibble: 10 × 2
## dest n
## <chr> <int>
## 1 ORD 17283
## 2 ATL 17215
## 3 LAX 16174
## 4 BOS 15508
## 5 MCO 14082
## 6 CLT 14064
## 7 SFO 13331
## 8 FLL 12055
## 9 MIA 11728
## 10 DCA 9705Now you want to find each flight that went to one of those destinations. You could construct a filter yourself:
flights %>%
filter(dest %in% top_dest$dest)## # A tibble: 141,145 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 542 540 2 923 850
## 2 2013 1 1 554 600 -6 812 837
## 3 2013 1 1 554 558 -4 740 728
## 4 2013 1 1 555 600 -5 913 854
## 5 2013 1 1 557 600 -3 838 846
## 6 2013 1 1 558 600 -2 753 745
## 7 2013 1 1 558 600 -2 924 917
## 8 2013 1 1 558 600 -2 923 937
## 9 2013 1 1 559 559 0 702 706
## 10 2013 1 1 600 600 0 851 858
## # ℹ 141,135 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>But it’s difficult to extend that approach to multiple variables. For
example, imagine that you’d found the 10 days with highest average
delays. How would you construct the filter statement that used
year, month, and day to match it
back to flights?
Instead you can use a semi-join, which connects the two tables like a
mutating join, but instead of adding new columns, only keeps the
rows in x that have a match in y:
flights %>%
semi_join(top_dest)## # A tibble: 141,145 × 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 542 540 2 923 850
## 2 2013 1 1 554 600 -6 812 837
## 3 2013 1 1 554 558 -4 740 728
## 4 2013 1 1 555 600 -5 913 854
## 5 2013 1 1 557 600 -3 838 846
## 6 2013 1 1 558 600 -2 753 745
## 7 2013 1 1 558 600 -2 924 917
## 8 2013 1 1 558 600 -2 923 937
## 9 2013 1 1 559 559 0 702 706
## 10 2013 1 1 600 600 0 851 858
## # ℹ 141,135 more rows
## # ℹ 11 more variables: arr_delay <dbl>, carrier <chr>, flight <int>,
## # tailnum <chr>, origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>,
## # hour <dbl>, minute <dbl>, time_hour <dttm>Graphically, a semi-join looks like this:
anti_join()The inverse of a semi-join is an anti-join. An anti-join keeps the rows that don’t have a match:
Anti-joins are useful for diagnosing join mismatches. For example,
when connecting flights and planes, you might
be interested to know that there are many flights that
don’t have a match in planes:
flights %>%
anti_join(planes, by = "tailnum") %>%
count(tailnum, sort = TRUE)## # A tibble: 722 × 2
## tailnum n
## <chr> <int>
## 1 <NA> 2512
## 2 N725MQ 575
## 3 N722MQ 513
## 4 N723MQ 507
## 5 N713MQ 483
## 6 N735MQ 396
## 7 N0EGMQ 371
## 8 N534MQ 364
## 9 N542MQ 363
## 10 N531MQ 349
## # ℹ 712 more rowsThe following code creates a graph of all destination airports in the US map:
airports %>%
semi_join(flights, c("faa" = "dest")) %>%
ggplot(aes(lon, lat)) +
borders("state") +
geom_point() +
coord_quickmap()
Here the x- and y- axis are longitude and latitude respectively. The two points outside the continental map of US refer to the airport in Alaska and Hawaii.
Now let’s compute the average arrival delay of each destination airport and mark it in the map.
flights %>%
filter(!is.na(arr_delay)) %>%
group_by(dest) %>%
summarise(avg_delay = mean(arr_delay)) -> delay_data
airports %>%
semi_join(delay_data, by = c("faa" = "dest")) %>%
left_join(delay_data, by = c("faa" = "dest")) %>%
ggplot(aes(lon, lat)) +
borders("state") +
geom_point(aes(color = avg_delay)) +
scale_color_gradient( low = "green", high = "red") +
coord_quickmap() 
As another example, let’s study whether there is a relationship between the age of a plane and its delays?
To perform this analysis, we need to
flights data to remove all NA
values in dep_delay and arr_delay.flights data with planes data by
tailnum.mutate()tail_num and compute the average
delay for each plane.flights3 <- flights %>%
filter(!is.na(dep_delay) & !is.na(arr_delay)) %>%
left_join(planes, by = 'tailnum') %>%
rename("flights_year" = 'year.x', "plane_made_year" = 'year.y') %>%
mutate(plane_age = 2013 - plane_made_year) %>%
filter(!is.na(plane_age)) %>%
group_by(tailnum) %>%
summarise(avg_dep_delay = mean(dep_delay), avg_arr_delay = mean(arr_delay), plane_age = mean(plane_age))
cor(flights3$avg_arr_delay, flights3$plane_age)## [1] -0.01639459ggplot(flights3, aes(x = plane_age, y = avg_arr_delay)) +
geom_point() +
geom_smooth() +
ylim(0, 50) +
labs(title = "plane age vs departure delay time", x = "plane age (yrs)", y = "average arrival delay (minutes)") +
theme(plot.title = element_text(hjust = 0.5, size = rel(1.2), margin = margin(0,0,15,0)), axis.title.x = element_text(size = rel(1.0), margin = margin(10,0,0,0)), axis.title.y = element_text(size = rel(1.0), margin = margin(0,10,0,0)), axis.text = element_text(size = rel(1.0)), plot.margin = margin(1,1,1,1,"cm"))
As a conclusion, we don’t see significant correlation between the two variables.