Worksheet — Joins: Combining Two Fisher Monitoring Tables

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

joins

tidyverse

wrangling

mustelids

Hands-on companion to the joins lecture. Over two days students combine a table of released fishers (fisher_individuals.csv) with a table of field capture/collar events (fisher_captures.csv) using left/inner/full joins and semi/anti joins, and build one analysis-ready table.

Author

Bill Perry

Published

September 10, 2026

Joins — Two Fisher Monitoring Tables into One

About the data (constructed for teaching)

Fishers (Pekania pennanti) — a large forest weasel — have been reintroduced to several US states from Canadian source populations. Monitoring produces exactly the shape of data joins are for: one table of the animals released, and a separate table of every field capture / collar event. The two files here are simulated — patterned on real programs (e.g. the Washington Cascades translocations), but the numbers are invented. Use them to learn joins.

Before you start — the data

Two files live in your data/ folder:

fisher_individuals.csv — one row per released fisher: sex, age_class, source, site (release site), release_date. Keyed by fisher_id.
fisher_captures.csv — one row per field capture event: capture_date, site (capture site), mass_kg, collar_id. Keyed by animal_id.

Today’s Objectives (a 2-day worksheet)

Find the key that links the two tables
Join them with left_join() when the key columns are named differently
Compare inner, left, and full joins
Use semi_join() / anti_join() to filter, and build one analysis-ready table

How to use this worksheet

Work through the parts in order. Type the code into a new R script and run it line by line.

Blocks marked ▶ Run this should be executed as written.

Blocks marked ✏️ Your turn ask you to write, modify, or interpret.

Day 1 = Parts 1–6. Day 2 = Parts 7–12.

🔮 Predict before you run — and type, don’t paste

Before every join, write down the row count you expect. fisher_captures.csv has more rows than fisher_individuals.csv (a fisher can be recaptured many times), and a few capture events are of unmarked animals with no matching individual — a wrong guess almost always means you misjudged the matching, not that R did something strange.

🧩 Chunk 1 — Two tables & a key (Day 1, after lecture Chunk 1)

Parts 1–3: load both tables and clean the key.

Part 1 · Load both tables

▶ Run this:

library(tidyverse)

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

dim(individuals_df)
dim(captures_df)

✏️ Your turn:

individuals_df: rows =        cols =
captures_df:    rows =        cols =
Which table has more rows? Why might that be?

Part 2 · Find the key

▶ Run this:

individuals_df %>% select(fisher_id, sex, site, release_date) %>% head(3)
captures_df    %>% select(animal_id, capture_date, site, mass_kg) %>% head(3)

✏️ Your turn: What column links the two tables, and what is it called in each?

Key in individuals_df:
Key in captures_df:
Are the names the same?  Y / N
Both tables ALSO have a `site` column — do those mean the same thing? (look again)

Part 3 · Clean the key

▶ Run this:

# The capture key is lowercase ("f07"); the individual key is "F07" ----
captures_df %>% count(animal_id) %>% head()

captures_df <- captures_df %>%
  mutate(animal_id = str_to_upper(animal_id))

n_distinct(individuals_df$fisher_id)
n_distinct(captures_df$animal_id)

✏️ Your turn: animal_id was stored as f07 but fisher_id is F07. Why must we fix the case before joining? What is the UNK value, and why is it not a real fisher?

Your answer:

🧩 Chunk 2 — Your first join (Day 1, after lecture Chunk 2)

Parts 4–6: the left join and the accidental-key trap.

Part 4 · The left join

🔮 Predict first: You will keep every capture event and attach who the animal is. How many rows will the result have, and roughly how many will have a missing sex (because the animal was unmarked)?

▶ Run this:

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

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

✏️ Your turn:

Total rows after left_join:
Rows WITH an identified individual:
Rows with NA individual info:

Note the join gives you two site columns — site.x (capture site) and site.y (release site). Real fishers move; those are genuinely different.

Part 5 · The accidental-key trap

▶ Run this (on purpose — watch the message):

# Forget by = ... and see what dplyr does ----------------
captures_df %>% left_join(individuals_df)

✏️ Your turn: What column did dplyr announce it was joining on? Why is joining on site completely wrong here? (What does it match — a capture event to the right animal, or to every animal released at that site?)

dplyr joined on:
Why that's wrong:
Row count of this accidental join vs. Part 4's correct join:

⚠️ Watch out! When dplyr prints “Joining with by = join_by(site)” and you didn’t choose that column, stop and name your real key.

Part 6 · Name the key every time

▶ Run this (the correct join):

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

captures_joined %>%
  select(capture_event, animal_id, capture_date, sex, age_class) %>%
  head()

✏️ Your turn: Look at the first rows — do any already show NA for sex? What does that mean about that capture event?

Your answer:

🛑 End of Day 1. You can join two tables on a named key. Day 2: the rest of the join family.

🧩 Chunk 3 — The join family (Day 2, after lecture Chunk 3)

Parts 7–9: inner vs left vs full.

Part 7 · Run all three joins

🔮 Predict first: Rank these three by row count, most to fewest: inner_join, left_join, full_join.

▶ Run this:

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))

✏️ Your turn:

inner rows =        left rows =        full rows =
Was your ranking right?  Y / N
Why does full have MORE rows than left? (which fishers does it add back?)

Part 8 · What each join dropped or kept

✏️ Your turn: Fill in what each join does, in your own words.

inner_join keeps:
left_join keeps:
full_join keeps:
Which capture events does inner_join DROP that left_join keeps?
Which fishers does full_join ADD that neither inner nor left includes?

Part 9 · Pick the right join

✏️ Your turn: You want a table of every capture event for a survival analysis, keeping the event even if the animal was unmarked. Which join do you use, and why?

Join:
Why:

💡 Key idea: decide which rows you can’t afford to drop first — an unmarked-animal capture is still a capture, so an event-level analysis calls for left_join (captures on the left), not inner_join.

🧩 Chunk 4 — Filtering joins & the payoff (Day 2, after lecture Chunk 4)

Parts 10–12: anti/semi joins and the analysis-ready table.

Part 10 · `anti_join()` — what’s missing

🔮 Predict first (two ways): (a) how many capture events have no matching individual? (b) how many released fishers were never recaptured?

▶ Run this:

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

# (b) released fishers with no capture record — anti_join the OTHER direction
never_seen <- individuals_df %>%
  anti_join(captures_df, by = c("fisher_id" = "animal_id"))
never_seen %>% select(fisher_id, sex, site, release_date)

✏️ Your turn:

Capture events with no individual:
Released fishers never recaptured:
Did anti_join ADD any columns, or just filter rows?

Part 11 · `semi_join()` — keep only matches

▶ Run this:

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

nrow(recaptured)

✏️ Your turn: How is semi_join different from inner_join? (Hint: look at the columns, not just the rows.)

Your answer:
recaptured rows + never_seen rows should equal nrow(individuals_df) — does it?

Part 12 · Build one analysis-ready table

▶ Run this:

# Every KNOWN-individual capture, with the fisher's attributes attached ---
fisher_analysis <- captures_df %>%
  inner_join(individuals_df, by = c("animal_id" = "fisher_id")) %>%
  mutate(days_since_release = as.numeric(capture_date - release_date)) %>%
  select(animal_id, sex, age_class, capture_date, days_since_release,
         capture_site = site.x, release_site = site.y, mass_kg)

fisher_analysis %>% head()

# Save it for later ------------------------------------
write_csv(fisher_analysis, "data/fisher_joined.csv")

✏️ Your turn: How many rows are in your analysis table? Why is it smaller than captures_df, and why did we use inner_join here instead of left_join?

Analysis-table rows:
Difference from captures_df, and why:
Why inner_join and not left_join for THIS table:

Part 13 · Review and checkpoint

You should now be able to:

Explain what a key is and why it must be unique and same-type
Join two tables whose key columns are named differently (by = c("a" = "b"))
Use left_join, inner_join, full_join and predict their row counts
Use anti_join to find unmatched rows and semi_join to keep matched rows
Recognize the accidental-site-join bug

✏️ Your turn — before you move on: Run your whole script top to bottom. Does it run cleanly?

Ran cleanly?  Y / N
If not, what error appeared:

Part 14 · Going further

Optional — do this if you finish early.

Capture effort by site and sex

▶ Try this: join, then count capture events per release site per sex.

captures_df %>%
  inner_join(individuals_df, by = c("animal_id" = "fisher_id")) %>%
  count(site.y, sex) %>%        # site.y = release site
  arrange(desc(n))

✏️ Your turn: Which release site + sex combination has the most capture events? Does that tell you about the animals, or about where crews spent time?

Your answer:

Recapture count per fisher

▶ Try this: how many times was each identified fisher caught?

captures_df %>%
  semi_join(individuals_df, by = c("animal_id" = "fisher_id")) %>%
  count(animal_id, sort = TRUE)

Most-recaptured fisher and its count:
Number of fishers caught exactly once:

Extension — out of class (~30–40 min)

Turn this in with your worksheet. In class you joined two tables that were each incomplete on their own. Now you check how complete the join really is — and then tell me the one thing that surprised you most.

Important

Only E3 (the “most surprising thing”) is graded. E1 and E2 are the work that gets you there — do them, but the grade is on the write-up. E3 must be handwritten, photographed, and embedded (![caption](my_photo.jpg)).

E1 · How complete is the join? (not graded — do it anyway)

# Capture events with NO matching individual (unmarked animals)
no_id <- anti_join(captures_df, individuals_df,
                   by = c("animal_id" = "fisher_id"))
100 * (1 - nrow(no_id) / nrow(captures_df))   # % of events we can attribute

# Released fishers never recaptured
anti_join(individuals_df, captures_df, by = c("fisher_id" = "animal_id"))

# The wrong join: no `by =` at all
left_join(captures_df, individuals_df)        # what does dplyr say it joined on?

Note the attributable percentage, how many fishers were never seen again, and what column dplyr picks when you leave by = out (and why that answer is wrong).

E2 · One more join, your choice (not graded — do it anyway)

Either: summarize the joined table a new way (mean mass_kg by sex; capture count by month), or bring in a small table of your own (even 3–4 rows you type with tibble() — e.g. a home-range size per fisher_id) and join it on the key. Keep it small.

E3 · The most surprising thing — ✍️ by hand (graded)

In a short paragraph, hand-written: across everything you did in this worksheet and E1–E2, what surprised you most about joining these two tables? Be specific — name the numbers or the rows that surprised you, and say why you expected something different. “Nothing surprised me” is not an answer; if the joins went smoothly, the surprise might be how many capture events could not be tied to a known animal, or how many released fishers were never seen again.

Getting unstuck

by must be supplied / weird match → the key columns are named differently; use by = c("animal_id" = "fisher_id").
Zero matches → check the key case (str_to_upper() on animal_id) — "f07" won’t match "F07".
Way too many rows after a join → you joined on site by accident; a shared key that isn’t unique fans the rows out. Always name the real key.
dplyr announced by = join_by(site) → you forgot to name your key; both tables share a site column that means different things.
Cheat sheet — https://dplyr.tidyverse.org/reference/mutate-joins.html

💡 Key idea: fisher_individuals.csv and fisher_captures.csv were each incomplete on their own — one had no field history, the other had no animal attributes. fisher_joined.csv is what you get once a shared key ties them together.

End of the Joins worksheet.

Other Formats

Worksheet — Joins: Combining Two Fisher Monitoring Tables

Joins — Two Fisher Monitoring Tables into One

Before you start — the data

Today’s Objectives (a 2-day worksheet)

How to use this worksheet

🧩 Chunk 1 — Two tables & a key (Day 1, after lecture Chunk 1)

Part 1 · Load both tables

Part 2 · Find the key

Part 3 · Clean the key

🧩 Chunk 2 — Your first join (Day 1, after lecture Chunk 2)

Part 4 · The left join

Part 5 · The accidental-key trap

Part 6 · Name the key every time

🧩 Chunk 3 — The join family (Day 2, after lecture Chunk 3)

Part 7 · Run all three joins

Part 8 · What each join dropped or kept

Part 9 · Pick the right join

🧩 Chunk 4 — Filtering joins & the payoff (Day 2, after lecture Chunk 4)

Part 10 · `anti_join()` — what’s missing

Part 11 · `semi_join()` — keep only matches

Part 12 · Build one analysis-ready table

Part 13 · Review and checkpoint

Part 14 · Going further

Capture effort by site and sex

Recapture count per fisher

Extension — out of class (~30–40 min)

E1 · How complete is the join? (not graded — do it anyway)

E2 · One more join, your choice (not graded — do it anyway)

E3 · The most surprising thing — ✍️ by hand (graded)

Getting unstuck

Other Formats

Joins — Two Fisher Monitoring Tables into One

Before you start — the data

Today’s Objectives (a 2-day worksheet)

How to use this worksheet

🧩 Chunk 1 — Two tables & a key (Day 1, after lecture Chunk 1)

Part 1 · Load both tables

Part 2 · Find the key

Part 3 · Clean the key

🧩 Chunk 2 — Your first join (Day 1, after lecture Chunk 2)

Part 4 · The left join

Part 5 · The accidental-key trap

Part 6 · Name the key every time

🧩 Chunk 3 — The join family (Day 2, after lecture Chunk 3)

Part 7 · Run all three joins

Part 8 · What each join dropped or kept

Part 9 · Pick the right join

🧩 Chunk 4 — Filtering joins & the payoff (Day 2, after lecture Chunk 4)

Part 10 · anti_join() — what’s missing

Part 11 · semi_join() — keep only matches

Part 12 · Build one analysis-ready table

Part 13 · Review and checkpoint

Part 14 · Going further

Capture effort by site and sex

Recapture count per fisher

Extension — out of class (~30–40 min)

E1 · How complete is the join? (not graded — do it anyway)

E2 · One more join, your choice (not graded — do it anyway)

E3 · The most surprising thing — ✍️ by hand (graded)

Getting unstuck

Part 10 · `anti_join()` — what’s missing

Part 11 · `semi_join()` — keep only matches