---
title: "Wrangling 2<br>Multiple-Table Data Wrangling"
author: "Keith VanderLinden<br>Calvin University"
output:
  xaringan::moon_reader:
    nature:
      titleSlideClass: ["left", "top"]
      highlightLines: true
      ratio: "16:9"     
---

```{r child = "../../../scripts/slide-setup.Rmd"}
```

```{r echo=FALSE, warning=FALSE, message=FALSE}
options(dplyr.print_max=12)

library(tidyverse)
```

# Multiple-Table Data Wrangling with dplyr

.pull-left[
`dplyr`, Tidyverse&rsquo;s data manipulation package, provides a *grammar* of *multiple-table* data manipulation &ldquo;verbs&rdquo; implemented as functions:

- `inner_join()`
- `left_join()`
- `right_join()`
- `full_join()`

These functions integrate with the tidyverse as the single-table functions did.
]
.pull-right[
![dplyr logo](images/tidyverse-dplyr.png)
]

???
- This unit focuses on multiple-table wrangling. 

---
# Dataset: Women in Science

We&rsquo;d like to work with this dataset of female scientists (taken from [Meet 10 Women in Science Who Changed the World](https://www.discovermagazine.com/the-sciences/meet-10-women-in-science-who-changed-the-world)) with the goal of merging related information from multiple tables.

.pull-left[
```{r women, results="hide", message=FALSE, warning=FALSE}
notability <- 
  read_csv("data/notability.csv")
glimpse(notability)


professions <- 
  read_csv("data/professions.csv")
glimpse(professions)


dates <- read_csv("data/dates.csv", 
    col_types = cols(
      birth_year = col_integer(), 
      death_year = col_integer())
    )
glimpse(dates)
```
]
.pull-right[
```{r ref.label="women", echo=FALSE, message=FALSE, warning=FALSE, output.lines=18}
```
]

???
- This dataset is a toy database with multiple tables.
- It's not uncommon to have different data about single entities distributed over multiple datasets.

---
# Dataset: Multiple Tables

.panelset[

.panel[.panel-name[professions]
```{r echo=FALSE}
professions
```
]

.panel[.panel-name[dates]
```{r echo=FALSE}
dates
```
]

.panel[.panel-name[notability]
```{r echo=FALSE}
notability
```
]

.panel[.panel-name[Desired Output]
```{r echo=FALSE}
professions %>%
  left_join(dates) %>%
  left_join(notability)
```
]

]

.footnote[The `name` column is the *primary key* for all of these tables.]

???
- To achieve the desired output, we can't just paste the tables together, either vertically or horizontally. The lines have to be *joined* with respect to the name field value.
- The `name` field is the *primary key* value, which indicates that it uniquely identifies a row in the table. Thus, the desired output only includes the primary key value once, because it's the same in all the rows that are combined.

---
# The Join Operation

```{r echo=FALSE}
x <- tibble(key = c(1, 2, 3), xdata = paste0('x', key))
y <- tibble(key = c(1, 2, 4), ydata = paste0('y', key))
y_extra <- tibble(key = c(1, 2, 4, 2), ydata = c("y1", "y2", "y4", "y5"))
```

.pull-left[
*Joining* two tables &ldquo;matches up&rdquo; records from the two tables (e.g., `X` &amp; `Y`) based on matching *key* values.

```{r echo=FALSE}
x
y
```
]
.pull-right[
![join tables](images/original-dfs.png)
]

.footnote[Images from: TidyExplain, https://github.com/gadenbuie/tidyexplain]

???
Briefly describe the desired output of each with respect to the sample tables.

- *Inner Join* --- Drops rows that don't have matching *key* values.
- *Left Join* --- Fills in `NA` values where the left row has no match.
- *Right Join* --- This is the reverse of the left join.
- *Full (aka. Outer) Join* --- Fills in `NA` values for all unmatched rows.

References

- Relational databases & SQL
- https://r4ds.had.co.nz/relational-data.html#understanding-joins

---
# Inner Join

.pull-left[
```{r}
inner_join(x, y, by = "key")
```
]
.pull-right[
![inner join](images/inner-join.gif)
]

???

---
# Left Join

.pull-left[
```{r}
left_join(x, y, by = "key")
```
]
.pull-right[
![left join](images/left-join.gif)
]

???
The left join is an inner join that adds `NA` for fields of unmatched records from the left (i.e., the first) table.

---
# Right Join

.pull-left[
```{r}
right_join(x, y, by = "key")
```
]
.pull-right[
![right join](images/right-join.gif)
]

???

---
# Full (aka. Outer) Join

.pull-left[
```{r}
full_join(x, y, by = "key")
```
]
.pull-right[
![full join](images/full-join.gif)
]

???

---
# Example: Women in Science

```{r}
professions %>%
  left_join(dates, by = "name") %>%
  left_join(notability, by = "name")
```

.footnote[The `name` field is used to join records. In cases where the field names don&rsquo;t match, we use `key = c("key_name_table1", "key_name_table2")` to specify the key names in the first and second tables respectively.]

???
- Joins are based on *key values*. Because the key column names are the same (i.e., `name`), we don't need to specify them (i.e., we can use the *natural* join). We'll address more general foreign keys in the demo.
- But it's important to remind ourselves how the *relationships* between table rows are specified, so I generally include the key match specification anyway (i.e., `key = c("name", "name")`).
- *Key* values are generally unique, in which case they are called *primary keys*, but they don't have to be. See the next example.

---
# Left Join - Multiple Matches

.pull-left[
```{r}
left_join(x, y_extra, by = "key")
```
]
.pull-right[
![left join](images/left-join-extra.gif)
]

???
Here, the Y table has an extra matching record for the key value 2.

---
# Example: Women in Science with Multiple Matches

.pull-left[
```{r women-multi, results="hide", message=FALSE, warning=FALSE}
notability_multi <- 
  read_csv("data/notability-multi.csv")
notability_multi








professions_multi <- 
  read_csv("data/professions-multi.csv")
professions_multi








# The dates dataset doesn't change.
```
]
.pull-right[
```{r ref.label="women-multi", echo=FALSE, message=FALSE, warning=FALSE, output.lines=8}
```
]

???
- This is the same basic structure, but there are more than one matching foreign key values.
- In this case, `name` is not a primary key. Tables do not have to have primary keys.

---
# Example: Women in Science with Multiple Matches

```{r}
professions_multi %>%                       #<<
  left_join(dates, by = "name") %>%
  left_join(notability_multi, by = "name")  #<<
```

???
- Ada Lovelace still only has one row; she didn't have multiple, matching records.
- Marie Curie now has multiple rows, one for each combination of the multiple, matching records (i.e., it's the cross product of the sets of matching records).
