350_relational_data.Rmd

<!-- 
This file by Martin Monkman 
is licensed under a Creative Commons Attribution 4.0 International License
https://creativecommons.org/licenses/by/4.0/  

-->


# Relational data {#relational-data}


## Objectives

* understand the function of keys in relational databases

* understand how to join tables

* understand the primary types of mutating and filtering joins


## Setup

This chunk of R code loads the packages that we will be using.

```{r}
library(tidyverse)
#
library(nycflights13)
```


## Reading


This hands-on exercise draws heavily on the following sources:

* ["Relational data" at _R for Data Science_, 1st ed.](https://r4ds.had.co.nz/relational-data.html)

* ["Two-table verbs", article at the {dplyr} website](https://dplyr.tidyverse.org/articles/two-table.html)

* ["Join two tbls together", part of the {dplyr} reference pages](https://dplyr.tidyverse.org/reference/join.html)

* ["Join two tables" at the STAT545 site](https://stat545.com/join-cheatsheet.html)


Another resource with a good explanation of the types of joins can be found at [Tidy Animated Verbs}(https://www.garrickadenbuie.com/project/tidyexplain/)[@AdenBuie_tidyanimated]


For some additional examples of table joins, see William Surles, [Joining Data in R with dplyr](https://rpubs.com/williamsurles/293454)




## Relational data


Often, the data you are working with are spread across multiple tables. This allows for efficient database storage (there's an entire discipline dedicated to database theory and practical implementations of those theories.)

This requires you, the data analyst, to join tables, so that the information held in multiple tables can be used to answer the research question at hand.

Earlier you worked with the {nycflights13} package; for this hands-on exercise we will return to it.

In addition to `flights`, there are four other tables in the package:

* `airlines`

* `airports`

* `planes`

* `weather`

The tables are _related_ to `flights` by the fact that they have variables in common. These are known as the "key" variables.

This diagram shows the relationships:

![nycflights13](static/img/relational-nycflights.png){width=100%}


## Keys

1. Primary: identifies a unique observation in the table.

2. Foreign: a unique observation in another table, but not this one.

An example: `tailnum`

* _primary_ in `planes` -- there is only one observation for each aircraft

* _foreign_ in `flights` -- a plane could have multiple flights in and out of NYC airports


Having the same key in two tables forms the "relation" -- hence "relational database".



::: {.rmdtip}
#### Your turn


Use `count` to check that `planes$tailnum` is a primary key


<details>

<summary>
Solution
</summary>


```{r}
# solution (in _R4DS_)
planes |> 
  count(tailnum) |> 
  filter(n > 1)

# alternate solution
planes |> 
  count(tailnum) |> 
  summarise(max(n))

flights |>
  group_by(tailnum) |> 
  tally()

```


</details>
:::



Sometimes tables don't have a primary key! When that happens, it can be useful to create one: `mutate()` and `row_number()` is one approach. This is a _surrogate key_.


## Mutating joins

The first kind of joins are "mutating joins"—new variables are added to one data frame from matching observations in another.

### Understanding keys 

* adapted from from [_R for Data Science, "Understanding joins"]("https://r4ds.had.co.nz/relational-data.html#mutating-joins")

  - In particular, you may wish to review the visual representations at ["Understanding joins"](https://r4ds.had.co.nz/relational-data.html#understanding-joins)


First, we will make two small tables, `table_one` and `table_two`

```{r}
table_one <- tribble(
  ~key_var, ~colour_t1,
     "key1", "red",
     "key2", "blue",
     "key3", "yellow"    # note that this has key_var == "key3"
)

table_two <- tribble(
  ~key_var, ~fruit_t2,
     "key1", "apple",
     "key2", "blueberry",
     "key4", "banana"     # note that this has key_var == "key4"
)


```


### Inner joins

An inner join keeps only the observations where there is a **match on both sides**. The key variables is identified using the `by =` argument:



```{r 350_inner_join_1}

inner_join(x = table_one, y = table_two, by = "key_var")

```


Note that in the circumstances where we are confident that the key variables are named the same in both tables, we can use a bit of coding short-hand and omit the `by =` argument. You will see a message in the console (and your R markdown thumbnail) letting you know what variables are used for the join.

```{r}

inner_join(table_one, table_two)

```


### Left join

Left, right, and full joins are varieties of "outer joins".

Join the two tables using a left join: ... all of the observations from the first table (on the left-hand side of the function), and variables from the matching records from the secon (the right-hand side of the function).

The {dplyr} function for this is `left_join()`

```{r}
left_join(table_one, table_two, by = "key_var")
```

Note that all 3 of the `table_one` values are there; because there is no `table_two` observation with the key value of "key3", the `fruit_t2` value is `NA`.

**right join**

Is the same as a left join, but keeps all of the observations in the right-hand table `y`

**full join**

Keeps all of the observations in both `x` and `y`


See what happens when you join the tables with a `right_join()`, followed by a `full_join()`


```{r}
# solution
right_join(table_one, table_two, by = "key_var")

```

```{r}

full_join(table_one, table_two, by = "key_var")

```


::: {.rmdtip}
#### Your turn


In this example, we will add the name of the airline to the `flights` table. First, we will make a smaller version of the `flights` table by selecting a few of the variables, and taking the first 100 rows with the `slice()` function (see https://dplyr.tidyverse.org/reference/slice.html).

```{r}
flights2 <- flights |> 
  select(year:day, hour, origin, dest, tailnum, carrier) |> 
  slice(1:100)
flights2
```

To this table we will add the name of the airline, which we can find in the table `airlines`.

* both tables have the variable "carrier"

* we want a `left_join`: all the `flight` observations, and adding the "name" variable from `airlines`


<details>

<summary>
Solution
</summary>


```{r}
# solution
left_join(flights2, airlines, by = "carrier")

```

Using a pipe, the same result can be achieved with the following:

```{r}
# alternate version, using a pipe
flights2 |>
  left_join(airlines, by = "carrier")

```




Here's an example of where the utility of the pipe operator can be seen. If we want to count the number of flights be each airline, we could use the "carrier" variable, which is the short code. But a more useful table would have the airline name. In this code, the join is followed by a `group_by` and then `tally()`, which produces a table with the airline name.

```{r}

flights2 |>
  left_join(airlines, by = "carrier") |> 
  group_by(name) |> 
  tally()
```



</details>
:::




### Duplicate Keys

In real life, tables start to get more complex. It's often the case that you will have tables that have duplicate keys in one or both of the tables.

The chunk below creates new tables `table_a` and `table_b`, where there are duplicate keys in one.

```{r}


table_a <- tribble(~ key_var, ~ day_a,
             "key1", "Mon",
             "key1", "Tue",
             "key2", "Wed",
             "key2", "Thu")

table_b <- tribble(~ key_var, ~ veg_b,
             "key1", "carrot",
             "key2", "tomato")
```


::: {.rmdtip}
#### Your turn


Join the tables with `left_join()`, with `a` as the table on the left.


<details>

<summary>
Solution
</summary>


```{r}
# solution

left_join(table_a, table_b, by = "key_var")

```

In this example, where the duplicates are on the left, the same value "carrot" gets joined to both "key1" cases.

</details>
:::


A situation where there are duplicate keys in both tables is usually an error—there is no unique identifier of a single observation. (A question to ask yourself is "Is there are third table?")


::: {.rmdtip}
#### Your turn



Here's different tables, where the key "key2" is duplicated in both.

```{r}
table_m <- tribble(~ key_var, ~ month_m,
             "key1", "Jan",
             "key2", "Feb",
             "key2", "Mar")

table_n <- tribble(~ key_var, ~ ball_n,
             "key1", "foot",
             "key2", "basket",
             "key2", "base")

```



What does a left join do? How many rows does the resulting table have?


<details>

<summary>
Solution
</summary>

```{r}
# solution

left_join(table_m, table_n, by = "key_var")

```


The `left_join()` function adds both "basket" and "base" to each of the "Feb" and "Mar" records on the left. This leads to a duplication of the rows with "key2"—so the whole table jumps from 3 rows (what we would expect with a `left_join()`) to 5 rows. 


</details>
:::





***

## Filtering joins

The other sort of joins filter observations from one data frame based on whether or not they match an observation in the other table.

There are two sorts: 

* `semi_join(x, y)` keeps all observations in `x` that have a match in `y`.

* `anti_join(x, y)` drops all observations in `x` that have a match in `y`.


Let's go back to our original test tables again:


```{r}

table_one <- tribble(
  ~key_var, ~colour_t1,
     "key1", "red",
     "key2", "blue",
     "key3", "yellow"
)

table_two <- tribble(
  ~key_var, ~fruit_t2,
     "key1", "apple",
     "key2", "blueberry",
     "key4", "banana"
)


```

**Semi-join**: only the observations in `x` that have a match in `y`.

Note that no variables from `y` appear in the result.

```{r}
semi_join(table_one, table_two, by = "key_var")
```


**Anti-join**: returns the observations in `x` that _don't_ have a key match in `y`. Again, no values from `y` appear in the result.

```{r}
anti_join(table_one, table_two, by = "key_var")
```



## More complex scenarios


### Keys with different names

In some circumstances, you will encounter a situation where your key variables are named one thing in one table, and something quite different in another. 

Here is a solution. In this example, the key variable in one table is named `key_x` and in the other it is `key_y`.


```{r}
left_tbl <- tribble(
  ~key_x, ~val_x,
     1, "x1",
     2, "x2",
     3, "x3"
)

right_tbl <- tribble(
  ~key_y, ~val_y, ~val_y2,
     1, "y1", "Monday",
     2, "y2", "Tuesday",
     4, "y3", "Wednesday"
)

```


Here are two different ways of writing the same code:

```{r}
# to specify key variables with different names:
left_join(left_tbl, right_tbl, by = c("key_x" = "key_y"))

left_tbl |> 
  left_join(right_tbl, by = c("key_x" = "key_y"))

```

### Join on multiple keys 

In the {nycflights13} database (that is, the multiple related tables), we see that the "flights" table is linked to the "weather" table on five different variables. The "weather" table holds the primary key, where there is a unique row by the five variables "year", "month", "day", "hour", and "origin" (with the airport code).

```{r}
head(weather)
```


If we were analyzing the relationship between flight departure delays (in the "flights" table) and weather conditions, we would need to link these tables.

The first solution is to name all of the variables inside a `by = c()` argument.

```{r}
# solution

left_join(flights, weather, by = c("year", "month", "day", "hour", "origin"))

```

In this second approach, the range of variables is specified with the `:` operator. Note that this is now inside a pipe, and the left table ("flights") is named at the top of the pipe sequence. 


```{r}

left_join(flights, weather)

```


```{r}
flights |> 
  left_join(select(weather, origin:temp))
```

### Select specific columns

Using our `left_tbl` and `right_tbl` from above, here is a solution that embeds a pipe and select function inside the `right_tbl` call. Note that the comma follows the `select()`, not the name of the table.

```{r}
# to specify variables to add to new table
# one solution: put select in the table naming 
full_join(left_tbl, 
          right_tbl |> select(key_y, val_y2), 
          by = c("key_x" = "key_y"))

```

In the {nycflights13} case, where we are joining on multiple keys _and_ then wanting just the temperature variable from the right ("weather") table, here are three different  `left_join()` solutions:

```{r}
# solution

flights |> 
  left_join(weather |> select("year", "month", "day", "hour", "origin", "temp"),
            by = c("year", "month", "day", "hour", "origin"))

# slightly different syntax:
flights |> 
  left_join(select(weather, "year", "month", "day", "hour", "origin", "temp"),
            by = c("year", "month", "day", "hour", "origin"))



# let {dplyr} decide the variables on which to join
flights |> 
  left_join(select(weather, origin:temp))

```




### Non-key variables with the same names

Here's another case—let's imagine we have two tables, called "orders" and "shipments". They have two variables with the same names, "order_number" and "dollar_value". The "order_number" is a unique ID (the key variable), but the dollars associated with the order might be different than the shipment—sometimes items are out of stock, so they can't be sent, so the value of the shipment is less than what was ordered.


```{r}

orders <- tribble(
  ~order_number, ~dollar_value,
  "x1", 11,
  "x2", 12,
  "x3", 13,
  "x4", 14
)

shipments <- tribble(
  ~order_number, ~dollar_value,
  "x1", 11,
  "x2", 11,
  "x3", 13,
  "x4", 4
)


```



When we join the tables, we will use "order_number" to join them. There will be only one column in the resulting dataframe with this name. 

A variable called "dollar_value" exists in both tables, but means different things. You and I know that one could be called "dollar_value_orders" and the other "dollar_value_shipments"—but R doesn't know that, so it renames them "dollar_value.x" and "dollar_value.y". The one with the ".x" at the end will be the left table, and ".y" will be the right table.

```{r}

orders_shipped <- orders |> 
  full_join(shipments, by = "order_number")

orders_shipped

```


You could then rename "dollar_value.x" and "dollar_value.y" to make it clear which is which. In this example, we also then add a column showing the percentage of the original order that was shipped.

```{r}

orders_shipped |> 
  rename("dollar_value_order" = dollar_value.x,
         "dollar_value_shipment" = dollar_value.y) |> 
  mutate(filled_pct = round((dollar_value_shipment / dollar_value_order) *100, 1))

```


### Joining three or more tables

To join three or more tables, we join them sequentially—we can't join them in a single step.

Let's revisit our original example tables, but where there's a consistent and add a third:


```{r}
table_one <- tribble(
  ~key_var, ~colour_t1,
     "key1", "red",
     "key2", "blue",
     "key3", "yellow"  # note that this has key_var == "key3"
)

table_two <- tribble(
  ~key_var, ~fruit_t2,
     "key1", "apple",
     "key2", "blueberry",
     "key4", "banana"  # note that this has key_var == "key4" 
)

table_three <- tribble(
  ~key_var, ~food_t3,
     "key1", "pie",
     "key2", "muffin",
     "key4", "bread"   # note that this has key_var == "key4"
)

```

The first way we will join these tables is by joining `table_one` and `table_two`, and assigning the output to an intermediate table, `table_a`. 

```{r}
table_a <- full_join(table_one, table_two, by = "key_var")

table_a
```


In the second step, `table_a` becomes the left table, and `table_three` is joined to it.

```{r}
table_b <- full_join(table_a, table_three, by = "key_var")

table_b
```





::: {.rmdtip}
#### Your turn


How could you write this two-step join process using a pipe?


<details>

<summary>
Solution
</summary>


```{r}

table_c <- table_one |> 
  full_join(table_two, by = "key_var") |> 
  full_join(table_three, by = "key_var")

table_c

```


</details>
:::



-30-