Lecture — Joins: Combining Two Tables

Keys, mutating joins, and filtering joins on a fisher reintroduction dataset

joins

tidyverse

wrangling

mustelids

Real projects spread data across several tables. This 2-day lecture teaches joins: what a key is, how to join two tables when the key columns are named differently, the mutating joins (left/inner/full), and the filtering joins (semi/anti) — by combining a table of released fishers with a table of field capture events into one analysis-ready dataset.

Author

Bill Perry

Published

September 10, 2026

Where we left off

Pivoting — reshaped wide data to long; ANOVA — compared group means; Factors — controlled category order
Your final project is under way — data found, question pitched
So far every analysis used one table

Note

✅ Transition

Real data almost never lives in a single table. Fishers (Pekania pennanti, a large forest weasel) are reintroduced and then monitored: one table lists the animals released, another logs every field capture / collar event. Neither alone tells you a captured animal’s sex or where it came from. Joins stitch them on a shared key. (The data are simulated for teaching, patterned on real reintroduction programs.)

Goals for today (a 2-day lecture)

Understand a key — the column that links two tables
Join when the key columns have different names
The mutating joins — left_join(), inner_join(), full_join()
The filtering joins — semi_join(), anti_join()
Build one combined capture + individual table

Tools today:

tidyverse (dplyr)

Data (two tables, simulated):

fisher_captures.csv — one row per capture event
fisher_individuals.csv — one row per released fisher

Reference:

📖 R4DS Ch. 19 — Joins
📖 common_code/13_joins

Tip

Day 1: keys + left_join. Day 2: the join family + filtering joins.

How to Use These Slides — Predict · Type · Run

This lecture runs in four chunks over two days. After each chunk you switch to the activity and type the code yourself.

For every join, do three things:

Predict — before it runs, say how many rows you’ll get
Type the code by hand — do not copy-paste
Run it and compare to your prediction

Note

✅ Why bother?

A join can run without error and still hand you the wrong table — the only way to catch that is to know the row count you expected before you see the actual one.
Every join in this lecture takes by = c("a" = "b") as an argument — typing that syntax a dozen times is what makes it automatic under deadline pressure.
Four short chunks instead of one long one means left_join, inner/full, and semi/anti each get their own uncluttered mental slot.

🧩 Chunk 1 of 4 · Two Tables & a Key (Day 1)

We will cover: why data comes in multiple tables, and what a key is.

Tip

🖐 After this chunk: Activity Parts 1–3 (load both tables, find the key).

The Problem — Data in Two Tables

# Load BOTH fisher tables ----------------------------
library(tidyverse)

captures_df    <- read_csv("data/fisher_captures.csv")
individuals_df <- read_csv("data/fisher_individuals.csv")

dim(captures_df)       # field events: when/where/mass/collar

[1] 45  6

dim(individuals_df)    # released animals: sex/age/source/site

[1] 18  6

captures_df — one row per capture event: date, site, mass, collar id
individuals_df — one row per released fisher: sex, age class, source, release site
To use a capture you often need both — the event and who the animal is

What Is a Key?

# The columns that identify an animal ---------------
captures_df    %>% select(animal_id, capture_date, site, mass_kg) %>% head(3)

# A tibble: 3 × 4
  animal_id capture_date site       mass_kg
  <chr>     <date>       <chr>        <dbl>
1 f07       2018-03-25   Snoqualmie    1.4 
2 f16       2018-04-11   Mt Rainier    5.52
3 f06       2018-04-29   Snoqualmie    2.06

individuals_df %>% select(fisher_id, sex, site, release_date) %>% head(3)

# A tibble: 3 × 4
  fisher_id sex   site       release_date
  <chr>     <chr> <chr>      <date>      
1 F01       F     Mt Rainier 2019-05-10  
2 F02       F     Mt Rainier 2018-11-08  
3 F03       M     Snoqualmie 2018-02-07

A key is the column that identifies an animal and links the tables.

In captures_df it’s animal_id
In individuals_df it’s fisher_id
Same idea, different name — we’ll tell the join that

Note

📖 Vocabulary

The key must be unique in the table you look things up in (individuals_df — one row per fisher). It is not unique in captures_df — a fisher can be caught many times.

Clean the Key Before Joining

# The capture key is lowercase; the individual key is not
captures_df %>% count(animal_id) %>% head()

# A tibble: 6 × 2
  animal_id     n
  <chr>     <int>
1 f01           3
2 f02           4
3 f03           2
4 f05           3
5 f06           5
6 f07           5

captures_df <- captures_df %>%
  mutate(animal_id = str_to_upper(animal_id))   # "f07" -> "F07"

n_distinct(individuals_df$fisher_id)

[1] 18

n_distinct(captures_df$animal_id)   # includes "UNK" — unmarked animals

[1] 15

str_to_upper() — animal_id was "f07", fisher_id is "F07". Case must match or the join finds nothing
"UNK" is not a real fisher — those events have no individual to attach

Warning

⚠️ Watch out! A join silently fails to match if the keys differ in type (text vs number), case, or format — clean them first.

🛑 Pause — Do Activity Parts 1–3 Now

Load both tables, find the key in each, and fix its case. Predict how many capture events can be tied to a known fisher.

🧩 Chunk 2 of 4 · Your First Join — `left_join()` (Day 1)

We will cover: joining the two tables when the key columns are named differently.

Tip

🖐 After this chunk: Activity Parts 4–6 (run the left join, read the result).

`left_join()` — Keep Every Capture Event

Note

🔮 Predict first: captures_df has ~45 events; a few are unmarked animals. After a left_join (captures on the left), how many rows will we have, and how many will have a missing sex?

# Match on differently-named keys with by = c() ------
captures_joined <- captures_df %>%
  left_join(individuals_df, by = c("animal_id" = "fisher_id"))

nrow(captures_joined)                  # every capture event is kept

[1] 45

sum(is.na(captures_joined$sex))        # events we can't attribute

[1] 3

by = c("animal_id" = "fisher_id") — “match my animal_id to their fisher_id”
left_join keeps every row on the left (all capture events)
Unmarked-animal events get NA for sex, age, source

📖 R4DS §19.3 — mutating joins

Live Demo — Watch It Break (a silent wrong join)

If I forget by = ..., dplyr looks for a shared column name — and both tables have a site column:

captures_df %>% left_join(individuals_df)
#> Joining with `by = join_by(site)`

But site means different things: the capture site vs the release site. Joining on it matches a capture to every fisher released at that site — the row count explodes and the animal ids are nonsense. No error.

The fix — always name the real key:

captures_df %>% left_join(individuals_df, by = c("animal_id" = "fisher_id"))

Important

✅ Why show a silent wrong join?

Nothing throws an error, so nothing forces you to notice. captures_df and individuals_df both have a site column; dplyr matches on it happily and hands you hundreds of rows that pair a capture with the wrong animal. If the message “Joining with by = ...” names a column you didn’t pick, treat it as a bug report, not a status update.

🛑 End of Day 1 · Start Day 2 Here

Day 1 recap — you can now:

Explain a key and why it must be unique (in the lookup table) and same case/type
Join two tables whose key columns are named differently
Use left_join() to keep every row on the left

Day 2 — the rest of the family:

inner_join vs left_join vs full_join
semi_join and anti_join to filter instead of add columns

🧩 Chunk 3 of 4 · The Join Family (Day 2)

We will cover: inner_join, left_join, and full_join, and how to choose.

Tip

🖐 After this chunk: Activity Parts 7–9 (compare the three joins).

Inner vs. Left vs. Full

Note

🔮 Predict first: Rank by row count: inner_join (only matches), left_join (all captures), full_join (all captures and every released fisher, even the never-recaptured ones).

# Same key, three different rules --------------------
key <- c("animal_id" = "fisher_id")

inner <- inner_join(captures_df, individuals_df, by = key)
left  <- left_join (captures_df, individuals_df, by = key)
full  <- full_join (captures_df, individuals_df, by = key)

c(inner = nrow(inner), left = nrow(left), full = nrow(full))

inner  left  full 
   42    45    49

Join	Keeps
`inner_join`	only events with a known animal
`left_join`	all capture events
`full_join`	all events and every released fisher

inner drops the unmarked-animal events; full adds back the fishers that were released but never recaptured.

Which Join Should You Use?

left_join — the default. Keep everything on your main table, add what you can. (Event-level analysis: keep all captures.)
inner_join — only rows with a match. (When an unattributed event would ruin the analysis.)
full_join — keep everything from both. (To audit what’s missing on each side — which fishers vanished.)

Note

✅ Choosing among the three

Ask which rows you’d be upset to lose. Drop an unmarked capture and you undercount effort; drop a never-recaptured fisher from a survival analysis and you bias it badly. Match the join to the question.

📖 R4DS §19.3–19.4

🛑 Pause — Do Activity Parts 7–9 Now

Run all three joins, compare the row counts, and decide which one you’d use to build a map. Predict each count first.

🧩 Chunk 4 of 4 · Filtering Joins & the Payoff (Day 2)

We will cover: semi_join and anti_join — joins that filter rather than add columns.

Tip

🖐 After this chunk: Activity Parts 10–12 (find the missing reports, build the map table).

`anti_join()` — What Didn’t Match?

Note

🔮 Predict first (two ways): how many capture events have no known animal? How many released fishers were never recaptured?

# (a) capture events of unmarked animals
captures_df %>%
  anti_join(individuals_df, by = c("animal_id" = "fisher_id")) %>%
  nrow()

[1] 3

# (b) released fishers with no capture — anti_join the OTHER way
individuals_df %>%
  anti_join(captures_df, by = c("fisher_id" = "animal_id")) %>%
  select(fisher_id, sex, site)

# A tibble: 4 × 3
  fisher_id sex   site           
  <chr>     <chr> <chr>          
1 F04       F     Snoqualmie     
2 F11       F     Gifford Pinchot
3 F17       M     Snoqualmie     
4 F18       F     Mt Rainier

anti_join keeps the left rows with no match — “what’s missing?”
It adds no columns — it filters
Direction matters: run it both ways to see each side’s gaps

📖 R4DS §19.5 — filtering joins

`semi_join()` — Keep Only What Matched

# Released fishers that WERE recaptured at least once
individuals_df %>%
  semi_join(captures_df, by = c("fisher_id" = "animal_id")) %>%
  nrow()

[1] 14

semi_join keeps left rows that have a match — again, no new columns
Think of semi/anti as a filter driven by another table
semi + anti on the same table split it in two: recaptured vs never seen

The Payoff — Build One Analysis-Ready Table

# Every KNOWN-individual capture, with the animal's attributes
fisher_analysis <- captures_df %>%
  inner_join(individuals_df, by = c("animal_id" = "fisher_id")) %>%
  mutate(days_since_release = as.numeric(capture_date - release_date))

fisher_analysis %>%
  select(animal_id, sex, age_class, capture_date, days_since_release, mass_kg) %>%
  head()

# A tibble: 6 × 6
  animal_id sex   age_class capture_date days_since_release mass_kg
  <chr>     <chr> <chr>     <date>                    <dbl>   <dbl>
1 F07       F     juvenile  2018-03-25                 -244    1.4 
2 F16       F     juvenile  2018-04-11                 -139    5.52
3 F06       F     adult     2018-04-29                 -301    2.06
4 F09       F     juvenile  2018-05-21                 -165    2.49
5 F06       F     adult     2018-05-30                 -270    1.4 
6 F02       F     juvenile  2018-06-08                 -153    4.21

You just built a real dataset from two raw ones. fisher_analysis didn’t exist in either source file — every row is a capture event plus the animal’s sex, age, and days-since-release, ready for a model.

Here inner_join is right: an event with no known animal can’t go into an individual-level analysis.

🛑 Pause — Do Activity Parts 10–12 Now

Use anti_join both directions to find the gaps, semi_join to see who was recaptured, and build the combined table. Predict the counts first.

What We Learned Today

Concepts:

A key links two tables; it must be unique and the same type
Join differently-named keys with by = c("a" = "b")
Mutating joins add columns: left (all left), inner (matches), full (all)
Filtering joins keep rows: semi (has a match), anti (no match)
Always name your key — a printed by = ... you didn’t choose is a bug

R skills:

left_join(), inner_join(), full_join()
semi_join(), anti_join()
str_to_upper() (and as.integer(), str_trim()) for key hygiene

References:

📖 R4DS Ch. 19 — Joins
📖 common_code/13_joins

Up next — Mapping:

Turn latitude/longitude into a map with sf and geom_sf()
A different dataset (Bigfoot sightings) — same join thinking

Other Formats

Where we left off

Goals for today (a 2-day lecture)

How to Use These Slides — Predict · Type · Run

🧩 Chunk 1 of 4 · Two Tables & a Key (Day 1)

The Problem — Data in Two Tables

What Is a Key?

Clean the Key Before Joining

🛑 Pause — Do Activity Parts 1–3 Now

🧩 Chunk 2 of 4 · Your First Join — left_join() (Day 1)

left_join() — Keep Every Capture Event

Live Demo — Watch It Break (a silent wrong join)

🛑 End of Day 1 · Start Day 2 Here

🧩 Chunk 3 of 4 · The Join Family (Day 2)

Inner vs. Left vs. Full

Which Join Should You Use?

🛑 Pause — Do Activity Parts 7–9 Now

🧩 Chunk 4 of 4 · Filtering Joins & the Payoff (Day 2)

anti_join() — What Didn’t Match?

semi_join() — Keep Only What Matched

The Payoff — Build One Analysis-Ready Table

🛑 Pause — Do Activity Parts 10–12 Now

What We Learned Today

🧩 Chunk 2 of 4 · Your First Join — `left_join()` (Day 1)

`left_join()` — Keep Every Capture Event

`anti_join()` — What Didn’t Match?

`semi_join()` — Keep Only What Matched