The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

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"

See also

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.