Joins
Real analysis almost always spans more than one table: measurements in one, sample or gene
metadata in another. A join combines them on a shared key. Here the key is gene: on the
left, the mean counts per measured gene; on the right, a gene annotation carrying pathway
and chromosome. The annotation includes KRAS, which was never measured, so it is the
unmatched key that reveals what each join type does.
Every code block runs in a container in the companion repository. dplyr names each join
inner_join, left_join, anti_join, semi_join; Polars uses one join method with a
how argument.
The two tables
Section titled “The two tables”gene_means <- expr |> group_by(gene) |> summarise(mean_counts = round(mean(counts), 1), .groups = "drop")
annotation <- read.csv("../fixtures/gene_annotation.csv")# gene_means (left) # annotation (right) gene mean_counts gene pathway chromosome1 ACTB 3881. TP53 Apoptosis chr172 BRCA1 182. EGFR Growth signaling chr73 EGFR 513. MYC Growth signaling chr84 GAPDH 4028. BRCA1 DNA repair chr175 MYC 502. GAPDH Housekeeping chr126 TP53 504. ACTB Housekeeping chr7 KRAS Growth signaling chr12gene_means = expr.group_by("gene", maintain_order=True).agg( pl.col("counts").mean().round(1).alias("mean_counts"))
annotation = pl.read_csv("../fixtures/gene_annotation.csv")gene_means (6, 2) annotation (7, 3)gene mean_counts gene pathway chromosomeTP53 504.1 TP53 Apoptosis chr17EGFR 512.8 EGFR Growth signaling chr7MYC 501.6 MYC Growth signaling chr8BRCA1 181.6 BRCA1 DNA repair chr17GAPDH 4028.2 GAPDH Housekeeping chr12ACTB 3880.6 ACTB Housekeeping chr7 KRAS Growth signaling chr12Inner join: rows in both
Section titled “Inner join: rows in both”An inner join keeps only genes present in both tables. All six measured genes are
annotated, so all six survive. KRAS drops out: it was never measured.
inner_join(gene_means, annotation, by = "gene")# A tibble: 6 × 4 gene mean_counts pathway chromosome <chr> <dbl> <chr> <chr>1 ACTB 3881. Housekeeping chr72 BRCA1 182. DNA repair chr173 EGFR 513. Growth signaling chr74 GAPDH 4028. Housekeeping chr125 MYC 502. Growth signaling chr86 TP53 504. Apoptosis chr17gene_means.join(annotation, on="gene", how="inner")shape: (6, 4)┌───────┬─────────────┬──────────────────┬────────────┐│ gene ┆ mean_counts ┆ pathway ┆ chromosome ││ --- ┆ --- ┆ --- ┆ --- ││ str ┆ f64 ┆ str ┆ str │╞═══════╪═════════════╪══════════════════╪════════════╡│ TP53 ┆ 504.1 ┆ Apoptosis ┆ chr17 ││ EGFR ┆ 512.8 ┆ Growth signaling ┆ chr7 ││ MYC ┆ 501.6 ┆ Growth signaling ┆ chr8 ││ BRCA1 ┆ 181.6 ┆ DNA repair ┆ chr17 ││ GAPDH ┆ 4028.2 ┆ Housekeeping ┆ chr12 ││ ACTB ┆ 3880.6 ┆ Housekeeping ┆ chr7 │└───────┴─────────────┴──────────────────┴────────────┘Left join: keep every row on the left
Section titled “Left join: keep every row on the left”A left join keeps every row of the left table and fills the missing side with NA (dplyr)
or null (Polars). Joining the annotation on the left keeps KRAS, whose mean_counts is
empty because it was never measured. This is how you spot annotated features with no data.
left_join(annotation, gene_means, by = "gene") gene pathway chromosome mean_counts1 TP53 Apoptosis chr17 504.12 EGFR Growth signaling chr7 512.83 MYC Growth signaling chr8 501.64 BRCA1 DNA repair chr17 181.65 GAPDH Housekeeping chr12 4028.26 ACTB Housekeeping chr7 3880.67 KRAS Growth signaling chr12 NAannotation.join(gene_means, on="gene", how="left")shape: (7, 4)┌───────┬──────────────────┬────────────┬─────────────┐│ gene ┆ pathway ┆ chromosome ┆ mean_counts ││ --- ┆ --- ┆ --- ┆ --- ││ str ┆ str ┆ str ┆ f64 │╞═══════╪══════════════════╪════════════╪═════════════╡│ TP53 ┆ Apoptosis ┆ chr17 ┆ 504.1 ││ EGFR ┆ Growth signaling ┆ chr7 ┆ 512.8 ││ MYC ┆ Growth signaling ┆ chr8 ┆ 501.6 ││ BRCA1 ┆ DNA repair ┆ chr17 ┆ 181.6 ││ GAPDH ┆ Housekeeping ┆ chr12 ┆ 4028.2 ││ ACTB ┆ Housekeeping ┆ chr7 ┆ 3880.6 ││ KRAS ┆ Growth signaling ┆ chr12 ┆ null │└───────┴──────────────────┴────────────┴─────────────┘Filtering joins: keep rows, add no columns
Section titled “Filtering joins: keep rows, add no columns”A filtering join uses the second table only to decide which rows of the first to keep; it
adds no columns. An anti join keeps the rows with no match, here the annotated but
unmeasured KRAS. A semi join keeps the rows that do match. These are how you ask “which
annotated genes am I missing data for?” and “which do I have?” without widening the table.
anti_join(annotation, gene_means, by = "gene") # annotated, never measuredsemi_join(annotation, gene_means, by = "gene") # annotated and measured# anti_join gene pathway chromosome1 KRAS Growth signaling chr12
# semi_join -> the 6 measured genes (TP53, EGFR, MYC, BRCA1, GAPDH, ACTB)annotation.join(gene_means, on="gene", how="anti") # annotated, never measuredannotation.join(gene_means, on="gene", how="semi") # annotated and measured# how="anti"shape: (1, 3)┌──────┬──────────────────┬────────────┐│ gene ┆ pathway ┆ chromosome ││ str ┆ str ┆ str │╞══════╪══════════════════╪════════════╡│ KRAS ┆ Growth signaling ┆ chr12 │└──────┴──────────────────┴────────────┘
# how="semi" -> the 6 measured genesSame four results in both: inner 6 rows, left 7, anti 1 (KRAS), semi 6. Next, Reshaping turns the long counts into a gene-by-sample matrix and back.