15_join-tibbles.Rmd

# Join two tables {#join-cheatsheet}

```{r include = FALSE}
source("common.R")
```

<!--Original content: https://stat545.com/bit001_dplyr-cheatsheet.html--> 

Join (a.k.a. merge) two tables: dplyr join cheatsheet with comic characters and publishers.

```{r gt-table-making-functions, include = FALSE}
library(gt)

# function to style the tables for display
# used alongside CSS
style_table <- function(data, table_title) {
  data %>% 
  gt() %>% 
  tab_header(
    title = table_title
  ) %>% 
  tab_options(
    table.align = "left",
    table.font.size = pct(80),
    heading.title.font.size = pct(90),
    column_labels.font.size = pct(90),
    table.width = "100%",
    row.striping.include_table_body = TRUE
  )
}

get_col_widths <- function(super_first = TRUE) {
  if (super_first == TRUE) {
    col_left <- 38; col_mid <- 18; col_right <- (100 - col_left - col_mid)
  } else {
    col_left <- 18; col_mid <- 38; col_right <- (100 - col_left - col_mid)
  }
  gt_col_widths <- list(col_left_width = col_left, 
                        col_mid_width = col_mid, 
                        col_right_width = col_right)
  return(gt_col_widths)
}
  
make_three_gt <- function(gt_left, gt_mid, gt_right, ...) {
  gt_col_widths <- get_col_widths(...)
  htmltools::withTags(
  table(style = "width: 100%; border: 0px;",
    tr(
      td(style = glue::glue("width: {gt_col_widths[[1]]}%; vertical-align: top;"),
        gt:::as.tags.gt_tbl(gt_left)
      ),
      td(style = glue::glue("width: {gt_col_widths[[2]]}%; vertical-align: top;"),
        gt:::as.tags.gt_tbl(gt_mid)
      ),
      td(style = glue::glue("width: {gt_col_widths[[3]]}%; vertical-align: top;"),
        gt:::as.tags.gt_tbl(gt_right)
      )
    )
  )
)
}
```


## 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][google-sql-join], 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][dplyr-vignette-two-table].
* The [Relational data chapter][r4ds-relational-data] in [R for Data Science][r4ds] [@wickham2016]. Excellent diagrams.
  
## The data

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

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

superheroes <- tibble::tribble(
       ~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"
  )

publishers <- tibble::tribble(
  ~publisher, ~yr_founded,
        "DC",       1934L,
    "Marvel",       1939L,
     "Image",       1992L
  )
```

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.

```{r style-gt-tables, include = FALSE}
# superheroes will always be lilac
super_gt <- style_table(superheroes, "superheroes") %>% 
  tab_options(
    table.background.color = "#edc7fc" # lilac
  )

# publishers will always be light blue
pub_gt <- style_table(publishers, "publishers") %>% 
  tab_options(
    table.background.color = "#cce6f6" # light blue
  )
```

## `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 echo = FALSE}
ijsp_gt <- style_table(ijsp, "inner_join(x = superheroes, y = publishers)") 
```

```{r echo = FALSE}
make_three_gt(super_gt, pub_gt, ijsp_gt)
```



## `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`.

```{r echo = FALSE}
sjsp_gt <- style_table(sjsp, "semi_join(x = superheroes, y = publishers)") 
```

```{r echo = FALSE}
make_three_gt(super_gt, pub_gt, sjsp_gt)
```


## `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 echo = FALSE}
ljsp_gt <- style_table(ljsp, "left_join(x = superheroes, y = publishers)") 
```

```{r echo = FALSE}
make_three_gt(super_gt, pub_gt, ljsp_gt)
```


## `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 echo = FALSE}
ajsp_gt <- style_table(ajsp, "anti_join(x = superheroes, y = publishers)") 
```

```{r echo = FALSE}
make_three_gt(super_gt, pub_gt, ajsp_gt)
```


## `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 echo = FALSE}
ijps_gt <- style_table(ijps, "inner_join(x = publishers, y = superheroes)") 
```

```{r echo = FALSE}
make_three_gt(pub_gt, super_gt, ijps_gt, super_first = FALSE)
```


## `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 echo = FALSE}
sjps_gt <- style_table(sjps, "semi_join(x = publishers, y = superheroes)") 
```

```{r echo = FALSE}
make_three_gt(pub_gt, super_gt, sjps_gt, super_first = FALSE)
```


## `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 echo = FALSE}
ljps_gt <- style_table(ljps, "left_join(x = publishers, y = superheroes)") 
```

```{r echo = FALSE}
make_three_gt(pub_gt, super_gt, ljps_gt, super_first = FALSE)
```

## `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 echo = FALSE}
ajps_gt <- style_table(ajps, "anti_join(x = publishers, y = superheroes)") 
```

```{r echo = FALSE}
make_three_gt(pub_gt, super_gt, ajps_gt, super_first = FALSE)
```

## `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 echo = FALSE}
fjsp_gt <- style_table(fjsp, "full_join(x = superheroes, y = publishers)") 
```

```{r echo = FALSE}
make_three_gt(super_gt, pub_gt, fjsp_gt)
```

```{r links, child="links.md"}
```