bit001_dplyr-cheatsheet.rmd

---
title: "Cheatsheet for dplyr join functions"
author: "Jenny Bryan"
output:
  html_document:
    toc: true
    toc_depth: 4
---

```{r setup, include = FALSE, cache = FALSE}
knitr::opts_chunk$set(error = TRUE, collapse = TRUE, comment = "#>")
```

#### Why the cheatsheet

Examples for those of us who don't speak SQL so good. There are lots of [Venn diagrams re: SQL joins on the internet](//encrypted.google.com/search?q=sql+join&tbm=isch), but I wanted R examples. Those diagrams also utterly fail to show what's really going on vis-a-vis rows AND columns.

Other great places to read about joins:

  * The dplyr vignette on [Two-table verbs](http://dplyr.tidyverse.org/articles/two-table.html)
  * The [Relational data chapter](http://r4ds.had.co.nz/relational-data.html) in [R for Data Science](http://r4ds.had.co.nz). Excellent diagrams.
  
#### The data

Working with two small data frames, `superheroes` and `publishers`.

```{r, message = FALSE, warning = FALSE}
library(tidyverse) ## dplyr provides the join functions

superheroes <- "
    name, alignment, gender,         publisher
 Magneto,       bad,   male,            Marvel
   Storm,      good, female,            Marvel
Mystique,       bad, female,            Marvel
  Batman,      good,   male,                DC
   Joker,       bad,   male,                DC
Catwoman,       bad, female,                DC
 Hellboy,      good,   male, Dark Horse Comics
"
superheroes <- read_csv(superheroes, skip = 1)

publishers <- "
  publisher, yr_founded
         DC,       1934
     Marvel,       1939
      Image,       1992
"
publishers <- read_csv(publishers, skip = 1)
```

Sorry, cheat sheet does not illustrate "multiple match" situations terribly well.

Sub-plot: watch the row and variable order of the join results for a healthy reminder of why it's dangerous to rely on any of that in an analysis.

#### inner_join(superheroes, publishers)

> inner_join(x, y): Return all rows from x where there are matching values in y, and all columns from x and y. If there are multiple matches between x and y, all combination of the matches are returned. This is a mutating join.

```{r}
(ijsp <- inner_join(superheroes, publishers))
```

We lose Hellboy in the join because, although he appears in `x = superheroes`, his publisher Dark Horse Comics does not appear in `y = publishers`. The join result has all variables from `x = superheroes` plus `yr_founded`, from `y`.

```{r include = FALSE}
superheroes_kable <- knitr::kable(superheroes)
publishers_kable <- knitr::kable(publishers)
ijsp_kable <- knitr::kable(ijsp)
```


<table style="font-size:14px" class="table table-bordered">
<tr>
<td valign="top">
  superheroes
  `r superheroes_kable`
</td>
<td valign="top">
  publishers
  `r publishers_kable`
</td>
<td valign="top">
  inner_join(x = superheroes, y = publishers)
  `r ijsp_kable`
</td>
</tr>
</table>
  
#### semi_join(superheroes, publishers)

> semi_join(x, y): Return all rows from x where there are matching values in y, keeping just columns from x. A semi join differs from an inner join because an inner join will return one row of x for each matching row of y, where a semi join will never duplicate rows of x. This is a filtering join.

```{r}
(sjsp <- semi_join(superheroes, publishers))
```

We get a similar result as with `inner_join()` but the join result contains only the variables originally found in `x = superheroes`. But note the row order has changed.

```{r include = FALSE}
sjsp_kable <- knitr::kable(sjsp)
```

<table style="font-size:14px" class="table table-bordered">
  <tr>
  <td valign="top">
  superheroes
  `r superheroes_kable`
</td>
  <td valign="top">
  publishers
  `r publishers_kable`
</td>
  <td valign="top">
  semi-join(x = superheroes, y = publishers)
  `r sjsp_kable`
</td>
</tr>
</table>

#### left_join(superheroes, publishers)

> left_join(x, y): Return all rows from x, and all columns from x and y. If there are multiple matches between x and y, all combination of the matches are returned. This is a mutating join.

```{r}
(ljsp <- left_join(superheroes, publishers))
```

We basically get `x = superheroes` back, but with the addition of variable `yr_founded`, which is unique to `y = publishers`. Hellboy, whose publisher does not appear in `y = publishers`, has an `NA` for `yr_founded`.

```{r include = FALSE}
ljsp_kable <- knitr::kable(ljsp)
```

<table style="font-size:14px" class="table table-bordered">
  <tr>
  <td valign="top">
  superheroes
  `r superheroes_kable`
</td>
  <td valign="top">
  publishers
  `r publishers_kable`
</td>
  <td valign="top">
  left_join(x = superheroes, y = publishers)
  `r ljsp_kable`
</td>
</tr>
</table>

#### anti_join(superheroes, publishers)

> anti_join(x, y): Return all rows from x where there are not matching values in y, keeping just columns from x. This is a filtering join.

```{r}
(ajsp <- anti_join(superheroes, publishers))
```

We keep __only__ Hellboy now (and do not get `yr_founded`).

```{r include = FALSE}
ajsp_kable <- knitr::kable(ajsp)
```

<table style="font-size:14px" class="table table-bordered">
  <tr>
  <td valign="top">
  superheroes
  `r superheroes_kable`
</td>
  <td valign="top">
  publishers
  `r publishers_kable`
</td>
  <td valign="top">
  anti_join(x = superheroes, y = publishers)
  `r ajsp_kable`
</td>
</tr>
</table>

#### inner_join(publishers, superheroes)

> inner_join(x, y): Return all rows from x where there are matching values in y, and all columns from x and y. If there are multiple matches between x and y, all combination of the matches are returned. This is a mutating join.

```{r}
(ijps <- inner_join(publishers, superheroes))
```

In a way, this does illustrate multiple matches, if you think about it from the `x = publishers` direction. Every publisher that has a match in `y = superheroes` appears multiple times in the result, once for each match. In fact, we're getting the same result as with `inner_join(superheroes, publishers)`, up to variable order (which you should also never rely on in an analysis).

```{r include = FALSE}
ijps_kable <- knitr::kable(ijps)
```

<table style="font-size:14px" class="table table-bordered">
<tr>
<td valign="top">
  publishers
  `r publishers_kable`
</td>
<td valign="top">
  superheroes
  `r superheroes_kable`
</td>
<td valign="top">
  inner_join(x = publishers, y = superheroes)
  `r ijps_kable`
</td>
</tr>
</table>
  
#### semi_join(publishers, superheroes)

> semi_join(x, y): Return all rows from x where there are matching values in y, keeping just columns from x. A semi join differs from an inner join because an inner join will return one row of x for each matching row of y, where a semi join will never duplicate rows of x. This is a filtering join.

```{r}
(sjps <- semi_join(x = publishers, y = superheroes))
```

Now the effects of switching the `x` and `y` roles is more clear. The result resembles `x = publishers`, but the publisher Image is lost, because there are no observations where `publisher == "Image"` in `y = superheroes`.

```{r include = FALSE}
sjps_kable <- knitr::kable(sjps)
```


<table style="font-size:14px" class="table table-bordered">
<tr>
<td valign="top">
publishers
`r publishers_kable`
</td>
<td valign="top">
superheroes
`r superheroes_kable`
</td>
<td valign="top">
semi-join(x = publishers, y = superheroes)
`r sjps_kable`
</td>
</tr>
</table>

#### left_join(publishers, superheroes)

> left_join(x, y): Return all rows from x, and all columns from x and y. If there are multiple matches between x and y, all combination of the matches are returned. This is a mutating join.

```{r}
(ljps <- left_join(publishers, superheroes))
```

We get a similar result as with `inner_join()` but the publisher Image survives in the join, even though no superheroes from Image appear in `y = superheroes`. As a result, Image has `NA`s for `name`, `alignment`, and `gender`.

```{r include = FALSE}
ljps_kable <- knitr::kable(ljps)
```

<table style="font-size:14px" class="table table-bordered">
<tr>
<td valign="top">
publishers
`r publishers_kable`
</td>
<td valign="top">
superheroes
`r superheroes_kable`
</td>
<td valign="top">
left_join(x = publishers, y = superheroes)
`r ljps_kable`
</td>
</tr>
</table>

#### anti_join(publishers, superheroes)

> anti_join(x, y): Return all rows from x where there are not matching values in y, keeping just columns from x. This is a filtering join.

```{r}
(ajps <- anti_join(publishers, superheroes))
```

We keep __only__ publisher Image now (and the variables found in `x = publishers`).

```{r include = FALSE}
ajps_kable <- knitr::kable(ajps)
```

<table style="font-size:14px" class="table table-bordered">
<tr>
<td valign="top">
publishers
`r publishers_kable`
</td>
<td valign="top">
superheroes
`r superheroes_kable`
</td>
<td valign="top">
anti_join(x = publishers, y = superheroes)
`r ajps_kable`
</td>
</tr>
</table>

#### full_join(superheroes, publishers)

> full_join(x, y): Return all rows and all columns from both x and y. Where there are not matching values, returns NA for the one missing. This is a mutating join.

```{r}
(fjsp <- full_join(superheroes, publishers))
```

We get all rows of `x = superheroes` plus a new row from `y = publishers`, containing the publisher Image. We get all variables from `x = superheroes` AND all variables from `y = publishers`. Any row that derives solely from one table or the other carries `NA`s in the variables found only in the other table.

```{r include = FALSE}
fjsp_kable <- knitr::kable(fjsp)
```

<table style="font-size:14px" class="table table-bordered">
<tr>
<td valign="top">
  superheroes
  `r superheroes_kable`
</td>
<td valign="top">
  publishers
  `r publishers_kable`
</td>
<td valign="top">
  full_join(x = superheroes, y = publishers)
  `r fjsp_kable`
</td>
</tr>
</table>