---
title: "Joins"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Joins}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

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

Metadata often lives in more than one table. tidymatrix supports all six dplyr
joins on the active metadata. Joins that only *add columns* leave the matrix
alone; joins that *add or remove rows* of the metadata add or remove the
corresponding rows (or columns) of the matrix.

```{r load-packages}
library(tidymatrix)
library(dplyr, warn.conflicts = FALSE)

tm <- tidymatrix(big5_responses, big5_respondents, big5_items) |>
  activate(rows) |>
  filter(completion_min > 3.5)
```

We use the `big5` personality survey (see `?big5`), without the careless
respondents. Respondents come from six countries:

```{r countries-in-data}
tm |> activate(rows) |> pull(country) |> table()
```

The `big5_countries` table has extra information about the countries. It is
deliberately imperfect: Germany (`DE`) is missing and Norway (`NO`) has no
respondents.

```{r countries}
big5_countries
```

## Mutating joins on rows

### Left join: add columns, keep every respondent

`left_join()` is the usual way to add annotations. Every respondent is kept;
the ones without a match (Germany) get `NA`.

```{r left-join}
tm_left <- tm |>
  activate(rows) |>
  left_join(big5_countries, by = "country")

tm_left

tm_left |>
  activate(rows) |>
  filter(is.na(country_name)) |>
  select(respondent_id, country, country_name)

identical(dim(tm_left$matrix), dim(tm$matrix))
```

### Inner join: keep only respondents with a match

`inner_join()` drops the German respondents from the metadata *and* from the
matrix.

```{r inner-join}
tm_inner <- tm |>
  activate(rows) |>
  inner_join(big5_countries, by = "country")

nrow(tm$matrix)
nrow(tm_inner$matrix)
```

Now we can, for example, compare Baltic and Nordic respondents:

```{r inner-join-use}
tm_inner |>
  activate(rows) |>
  count(region)
```

### Right and full joins: new rows get `NA` in the matrix

`right_join()` keeps every row of the *external* table, and `full_join()` keeps
every row of both. A row that exists only in the external table — Norway
here — has no answers, so its row of the matrix is filled with `NA`.

```{r full-join}
tm_full <- tm |>
  activate(rows) |>
  full_join(big5_countries, by = "country")

nrow(tm_full$matrix)

tm_full |>
  activate(rows) |>
  filter(is.na(respondent_id)) |>
  select(respondent_id, country, country_name)

tail(tm_full$matrix[, 1:6], 3)
```

These joins are rarely what you want for annotations, but they can be useful
to line up a matrix against a fixed list of expected rows or columns.

## Filtering joins

`semi_join()` and `anti_join()` filter the metadata by whether a match exists,
without adding any columns.

```{r semi-anti}
# respondents from countries in the table
tm_semi <- tm |>
  activate(rows) |>
  semi_join(big5_countries, by = "country")

nrow(tm_semi$matrix)
ncol(tm_semi$row_data)  # no new columns

# respondents from countries missing from the table
tm |>
  activate(rows) |>
  anti_join(big5_countries, by = "country") |>
  select(respondent_id, country, age, gender)
```

`anti_join()` is a handy check before a `left_join()`: it shows exactly which
rows will end up with missing annotations.

## Joins on columns

Everything above works for the column metadata too. Here is a table describing
the five traits:

```{r trait-table}
trait_info <- data.frame(
  trait = c("Extraversion", "Agreeableness", "Conscientiousness",
            "Neuroticism", "Openness"),
  abbreviation = c("E", "A", "C", "N", "O"),
  high_pole = c("outgoing, energetic", "friendly, compassionate",
                "organised, dependable", "anxious, moody",
                "curious, imaginative"),
  low_pole = c("reserved, quiet", "critical, detached",
               "careless, spontaneous", "calm, stable",
               "conventional, practical")
)

tm_items <- tm |>
  activate(columns) |>
  left_join(trait_info, by = "trait")

tm_items |>
  activate(columns) |>
  select(item_id, trait, abbreviation, high_pole)
```

A filtering join is a convenient way to select a predefined subset of columns.
Suppose a short form of the questionnaire uses only two items per trait:

```{r short-form}
short_form <- data.frame(
  item_id = c("E1", "E5", "A1", "A5", "C1", "C5", "N1", "N5", "O1", "O5")
)

tm_short <- tm |>
  activate(columns) |>
  semi_join(short_form, by = "item_id")

colnames(tm_short$matrix)
```

Note that the matrix columns keep their original order, not the order of
`short_form`. Use `arrange()` afterwards if the order matters.

## Joining on differently named keys

`by` accepts everything `dplyr` does, including `join_by()` and named vectors
for keys with different names:

```{r different-keys}
country_codes <- data.frame(
  iso2 = c("EE", "FI", "LV", "LT", "SE", "DE"),
  eu_member_since = c(2004, 1995, 2004, 2004, 1995, 1958)
)

tm |>
  activate(rows) |>
  left_join(country_codes, by = join_by(country == iso2)) |>
  select(respondent_id, country, eu_member_since)
```

## Duplicate keys

If the external table has several rows for the same key, a mutating join
duplicates the matching metadata rows — and the matrix rows with them. This is
the same behaviour as in dplyr, and it is almost always a mistake when
annotating a matrix. Check that the key is unique first:

```{r unique-keys}
anyDuplicated(big5_countries$country) == 0
```

## Joins and stored analyses

Stored analysis objects (PCA, clustering, ...) are removed after a join,
because a join may change which rows or columns are present. The metadata
columns an analysis created are kept. If you need both, join first and analyse
afterwards:

```{r join-then-analyse}
tm_analysed <- tm |>
  activate(rows) |>
  left_join(big5_countries, by = "country") |>
  activate(columns) |>
  compute_hclust(k = 5, method = "ward.D2")

list_analyses(tm_analysed)
```

## See also

* [Working with rows and columns](dplyr-verbs.html) for the other dplyr verbs.
* [Getting started](basic-usage.html) for an overview of the package.