Joins

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.

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:

tm |> activate(rows) |> pull(country) |> table()
#> 
#>  DE  EE  FI  LT  LV  SE 
#>  11 159  71  46  50  51

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

big5_countries
#>   country country_name region   language population_m
#> 1      EE      Estonia Baltic   Estonian         1.37
#> 2      FI      Finland Nordic    Finnish         5.60
#> 3      LV       Latvia Baltic    Latvian         1.87
#> 4      LT    Lithuania Baltic Lithuanian         2.89
#> 5      SE       Sweden Nordic    Swedish        10.55
#> 6      NO       Norway Nordic  Norwegian         5.55

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.

tm_left <- tm |>
  activate(rows) |>
  left_join(big5_countries, by = "country")

tm_left
#> # A tidymatrix: 388 x 30 matrix
#> # Active: rows
#> #
#> # Row data: 388 rows x 12 columns
#> # Column data: 30 rows x 5 columns
#> #
#> # Active data (rows):
#>   respondent_id age gender education     occupation country life_satisfaction
#> 1          R001  52 Female Secondary Service/Manual      EE                 8
#> 2          R002  32 Female    Master         Office      FI                 4
#> 3          R003  66   Male  Bachelor         Office      EE                 6
#> 4          R004  42   Male  Bachelor   Professional      EE                 9
#> 5          R005  48 Female Secondary         Office      FI                 8
#> 6          R006  62 Female     Basic Service/Manual      EE                 4
#>   completion_min country_name region language population_m
#> 1           19.8      Estonia Baltic Estonian         1.37
#> 2            6.2      Finland Nordic  Finnish         5.60
#> 3            6.3      Estonia Baltic Estonian         1.37
#> 4            9.7      Estonia Baltic Estonian         1.37
#> 5           14.2      Finland Nordic  Finnish         5.60
#> 6           11.6      Estonia Baltic Estonian         1.37

tm_left |>
  activate(rows) |>
  filter(is.na(country_name)) |>
  select(respondent_id, country, country_name)
#> # A tidymatrix: 11 x 30 matrix
#> # Active: rows
#> #
#> # Row data: 11 rows x 3 columns
#> # Column data: 30 rows x 5 columns
#> #
#> # Active data (rows):
#>   respondent_id country country_name
#> 1          R043      DE         <NA>
#> 2          R051      DE         <NA>
#> 3          R066      DE         <NA>
#> 4          R116      DE         <NA>
#> 5          R140      DE         <NA>
#> 6          R187      DE         <NA>

identical(dim(tm_left$matrix), dim(tm$matrix))
#> [1] TRUE

Inner join: keep only respondents with a match

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

tm_inner <- tm |>
  activate(rows) |>
  inner_join(big5_countries, by = "country")

nrow(tm$matrix)
#> [1] 388
nrow(tm_inner$matrix)
#> [1] 377

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

tm_inner |>
  activate(rows) |>
  count(region)
#> # A tidymatrix: 2 x 30 matrix
#> # Active: rows
#> #
#> # Row data: 2 rows x 2 columns
#> # Column data: 30 rows x 5 columns
#> #
#> # Active data (rows):
#>   region   n
#> 1 Baltic 255
#> 2 Nordic 122

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.

tm_full <- tm |>
  activate(rows) |>
  full_join(big5_countries, by = "country")

nrow(tm_full$matrix)
#> [1] 389

tm_full |>
  activate(rows) |>
  filter(is.na(respondent_id)) |>
  select(respondent_id, country, country_name)
#> # A tidymatrix: 1 x 30 matrix
#> # Active: rows
#> #
#> # Row data: 1 rows x 3 columns
#> # Column data: 30 rows x 5 columns
#> #
#> # Active data (rows):
#>   respondent_id country country_name
#> 1          <NA>      NO       Norway

tail(tm_full$matrix[, 1:6], 3)
#>      E1 E2 E3 E4 E5 E6
#> R399  1  2  2  1  4  2
#> R400  2  1  3  4  4  5
#> <NA> NA NA NA NA NA NA

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.

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

nrow(tm_semi$matrix)
#> [1] 377
ncol(tm_semi$row_data)  # no new columns
#> [1] 8

# respondents from countries missing from the table
tm |>
  activate(rows) |>
  anti_join(big5_countries, by = "country") |>
  select(respondent_id, country, age, gender)
#> # A tidymatrix: 11 x 30 matrix
#> # Active: rows
#> #
#> # Row data: 11 rows x 4 columns
#> # Column data: 30 rows x 5 columns
#> #
#> # Active data (rows):
#>   respondent_id country age gender
#> 1          R043      DE  31 Female
#> 2          R051      DE  27   Male
#> 3          R066      DE  33 Female
#> 4          R116      DE  41 Female
#> 5          R140      DE  59 Female
#> 6          R187      DE  44   Male

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:

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 tidymatrix: 388 x 30 matrix
#> # Active: columns
#> #
#> # Row data: 388 rows x 8 columns
#> # Column data: 30 rows x 4 columns
#> #
#> # Active data (columns):
#>   item_id        trait abbreviation           high_pole
#> 1      E1 Extraversion            E outgoing, energetic
#> 2      E2 Extraversion            E outgoing, energetic
#> 3      E3 Extraversion            E outgoing, energetic
#> 4      E4 Extraversion            E outgoing, energetic
#> 5      E5 Extraversion            E outgoing, energetic
#> 6      E6 Extraversion            E outgoing, energetic

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:

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)
#>  [1] "E1" "E5" "A1" "A5" "C1" "C5" "N1" "N5" "O1" "O5"

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:

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)
#> # A tidymatrix: 388 x 30 matrix
#> # Active: rows
#> #
#> # Row data: 388 rows x 3 columns
#> # Column data: 30 rows x 5 columns
#> #
#> # Active data (rows):
#>   respondent_id country eu_member_since
#> 1          R001      EE            2004
#> 2          R002      FI            1995
#> 3          R003      EE            2004
#> 4          R004      EE            2004
#> 5          R005      FI            1995
#> 6          R006      EE            2004

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:

anyDuplicated(big5_countries$country) == 0
#> [1] TRUE

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:

tm_analysed <- tm |>
  activate(rows) |>
  left_join(big5_countries, by = "country") |>
  activate(columns) |>
  compute_hclust(k = 5, method = "ward.D2")

list_analyses(tm_analysed)
#> [1] "column_hclust"