Activity 16 — 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. 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. Students hand in the finished script.

Author

Bill Perry

Published

November 10, 2026

In-class Activity 16: Two Fisher 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 such as the Washington Cascades translocations, but the numbers are invented. Use them to learn joins.

Today’s Objectives

Find the key that links two tables, and clean it before joining
Join with left_join() when the key columns are named differently
Compare inner_join(), left_join(), and full_join()
Use semi_join() and anti_join() to filter instead of add columns
Build one analysis-ready table
Join in a table of your own, for homework

📂 Files for today

Skeleton script: 16_joins_skeleton.R → save it as scripts/16_joins.R.
Two data files, both 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 capture event: capture_date, site (capture site), mass_kg, collar_id. Keyed by animal_id.

What you hand in

All of today’s code is right here in this activity. Type it into your script as we go and run it chunk by chunk. The skeleton has everything through Part 10 already written — Part 11 is the one blank you fill in yourself.
The Q boxes are things to think about, not inline homework. Read them, talk them over, answer them in your head. They are there for your benefit — you do not have to type answers into the script, and they are not graded.
The homework at the bottom brings in a small table of your own — that part you write, and that part is graded.
Hand in scripts/16_joins.R. Run it top to bottom (Ctrl/Cmd + Shift + Enter) first — it must run with no errors.

🔮 Predict before you run

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.

The invisible(NULL) lines in the skeleton

Every chunk ends with invisible(NULL). It is a bumper. Press Ctrl/Cmd + Enter on a chunk’s last real line and your cursor jumps to the next line that has code on it — without the bumper, that is the next chunk, way down past the next heading. With it, the cursor stops right there and you keep your place. It does nothing and prints nothing.

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

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

Part 1 · Load both tables

▶ Run this:

# Load libraries ---------------------------------------
library(tidyverse)   # dplyr + readr + stringr

▶ Run this:

# Read BOTH fisher tables ------------------------------
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

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

✏️ Q1: Which table has more rows, and why would that be?

Part 2 · Find the key

▶ Run this:

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

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

✏️ Q2: What column links the two tables, and what is it called in each? Are the names the same?

✏️ Q3: Both tables also have a site column. Do the two mean the same thing? Look again at what each table is a row of.

Part 3 · Clean the key

▶ Run this:

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

▶ Run this:

# Fix the case, then count distinct keys ---------------
captures_df <- captures_df %>%
  mutate(animal_id = str_to_upper(animal_id))   # "f07" -> "F07"

n_distinct(individuals_df$fisher_id)

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

⚠️ Watch out! A join silently fails to match when the keys differ in type (text vs. number), case, or format. It does not error — it just hands you a column full of NA. Clean the key first, every time.

✏️ Q4: animal_id is stored as f07 but fisher_id is F07. Why must you fix the case before joining? What would a join have produced if you had not?

✏️ Q5: One value of animal_id is not a fisher ID at all. What is it, and what does it mean about those capture events?

💡 Key idea: a key has to be unique in the lookup table and the same type and format in both. Almost every failed join is one of those two things.

🧩 Chunk 2 — Your first join (after lecture Chunk 2)

Parts 4–5: the left join.

Part 4 · The left join

🔮 Predict first: you want to 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?

▶ Run this:

# 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

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

✏️ Q6: How many capture events could not be tied to a known animal? Was your prediction close?

Note that the join hands you two site columns — site.x (capture site) and site.y (release site). Real fishers move; those are genuinely different things, and dplyr is right to keep both.

Part 5 · Name the key every time

▶ Run this:

# Same join, cut down to something readable ------------
captures_df %>%
  left_join(individuals_df, by = c("animal_id" = "fisher_id")) %>%
  select(capture_event, animal_id, capture_date, sex, age_class) %>%
  head()

🧩 Chunk 3 — The join family (after lecture Chunk 3)

Parts 6–8: inner vs. left vs. full.

Part 6 · Run all three joins

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

▶ Run this:

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

Storing the key in a variable means you type it once and all three joins are guaranteed to use the same rule.

✏️ Q7: Why does full have more rows than left — which fishers does it add back?

Part 7 · What each join dropped or kept

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

✏️ Q8: In your own words, what does each of the three joins keep — and what does inner_join drop that left_join keeps?

Part 8 · Pick the right join

✏️ Q9: You want every capture event for a survival analysis, even of unmarked animals. Which join, and why?

💡 Key idea: decide which rows you cannot afford to lose first, then pick the join. An unmarked-animal capture is still a capture, so an event-level analysis calls for left_join with captures on the left — not inner_join.

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

Parts 9–11: anti/semi joins and the analysis-ready table.

Part 9 · `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 ---------------
captures_df %>%
  anti_join(individuals_df, by = c("animal_id" = "fisher_id")) %>%
  nrow()

▶ Run this:

# (b) released fishers never recaptured ----------------
# Note: this runs the OTHER direction, so the by = flips too
never_seen <- individuals_df %>%
  anti_join(captures_df, by = c("fisher_id" = "animal_id"))

never_seen %>%
  select(fisher_id, sex, site)

✏️ Q10: Were your two predictions close?

✏️ Q11: Did anti_join() add any columns, or only filter rows?

Part 10 · `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)

# These two should add back up to nrow(individuals_df)
nrow(recaptured) + nrow(never_seen)

nrow(individuals_df)

✏️ Q12: How is semi_join() different from inner_join()? Look at the columns, not the rows.

✏️ Q13: Those two numbers should add up to nrow(individuals_df). What would it mean if they did not?

Part 11 · Build one analysis-ready table (you write this one)

This is the one chunk the skeleton leaves blank — this is where we left off in class. You have the pattern from Part 6; what changes is what you do after the join.

🔮 Predict first: this one uses inner_join. Will it have more or fewer rows than captures_joined from Part 4, and why?

▶ Type this yourself:

# 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)
  ) %>%
  select(
    capture_event, animal_id, sex, age_class, source,
    release_site = site.y,
    capture_site = site.x,
    release_date, capture_date, days_since_release,
    mass_kg, collar_id
  )

dim(fisher_analysis)

head(fisher_analysis)

The two renames inside select() are the whole reason to bother: site.x and site.y are meaningless three months from now, and capture_site / release_site are not.

▶ Type this yourself:

# Sanity check, then save it ---------------------------
range(fisher_analysis$days_since_release)

write_csv(fisher_analysis, "data/fisher_joined.csv")

✏️ Q14: Why is this table smaller than captures_df?

✏️ Q15: Why inner_join() here, when Part 8 argued for left_join()? What is different about this table’s job?

✏️ Q16: What would a negative days_since_release have told you about the data?

Homework — summarize it, then join your own table

Write it at the bottom of the same script. Hand it in with your answers as # comments.

H1 · Summarize the joined table

Using fisher_analysis, write two summaries of your own:

Mean mass_kg by sex, with the sample size for each.
Capture-event count by release_site and sex.

✏️ H-Q1: Mean mass of females and of males, with both sample sizes. Fishers are strongly sexually dimorphic — does your answer match that?

✏️ H-Q2: Which release-site-and-sex combination has the most capture events? Does that tell you about the animals, or about where the crews spent their time? Say which, and how you would tell the two apart.

H2 · Recaptures per fisher

Count how many times each identified fisher was caught. Use a filtering join, not a mutating one — you do not need any new columns to do this.

✏️ H-Q3: The most-recaptured fisher and its count, and how many fishers were caught exactly once.

H3 · Join in a table of your own

Build a small table with tibble() — four or five rows is plenty — keyed by fisher_id, holding something you invent: a home-range size in km², a collar battery status, a field crew name. Then join it onto the fisher data on the key.

✏️ H-Q4: Paste your tibble() and say which join you chose and why.

✏️ H-Q5: Your table covers only some of the fishers. How many rows came back with NA from your new column, and is that a problem or exactly what you asked for?

H4 · The thing that surprised you

✏️ H-Q6: Across everything in this activity, what surprised you most about joining these two tables? Be specific — name the numbers or the rows that surprised you, and say what you expected instead. “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.

Review and checkpoint

At this point you should be able to:

Explain what a key is and why it must be unique and the same type and case
Join two tables whose key columns are named differently with by = c("a" = "b")
Use left_join, inner_join, and full_join, and predict their row counts
Use anti_join to find unmatched rows and semi_join to keep matched rows
Say why a filtering join adds no columns and a mutating join does

Run your whole script top to bottom with Ctrl/Cmd + Shift + Enter. It must run clean.

💡 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. Your own project data will almost certainly arrive the same way.

End of Activity 16. Next: mapping — the same join thinking, on a map.

Other Formats

Activity 16 — Joins: Combining Two Fisher Monitoring Tables

In-class Activity 16: Two Fisher Tables into One

Today’s Objectives

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

Part 1 · Load both tables

Part 2 · Find the key

Part 3 · Clean the key

🧩 Chunk 2 — Your first join (after lecture Chunk 2)

Part 4 · The left join

Part 5 · Name the key every time

🧩 Chunk 3 — The join family (after lecture Chunk 3)

Part 6 · Run all three joins

Part 7 · What each join dropped or kept

Part 8 · Pick the right join

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

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

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

Part 11 · Build one analysis-ready table (you write this one)

Homework — summarize it, then join your own table

H1 · Summarize the joined table

H2 · Recaptures per fisher

H3 · Join in a table of your own

H4 · The thing that surprised you

Review and checkpoint

Other Formats

In-class Activity 16: Two Fisher Tables into One

Today’s Objectives

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

Part 1 · Load both tables

Part 2 · Find the key

Part 3 · Clean the key

🧩 Chunk 2 — Your first join (after lecture Chunk 2)

Part 4 · The left join

Part 5 · Name the key every time

🧩 Chunk 3 — The join family (after lecture Chunk 3)

Part 6 · Run all three joins

Part 7 · What each join dropped or kept

Part 8 · Pick the right join

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

Part 9 · anti_join() — what’s missing

Part 10 · semi_join() — keep only matches

Part 11 · Build one analysis-ready table (you write this one)

Homework — summarize it, then join your own table

H1 · Summarize the joined table

H2 · Recaptures per fisher

H3 · Join in a table of your own

H4 · The thing that surprised you

Review and checkpoint

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

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