Lecture 12 — Joins: Combining Two Tables

Keys, mutating joins, and filtering joins with real Bigfoot report data

Bill Perry

2026-07-05

Where we left off — ANOVA & Factors

One-way ANOVA — compared a numeric response across several groups
Factors — reordered and relabeled those groups for clean plots
So far every analysis used one table

Note

✅ Transition

Real data almost never lives in a single table. Today’s dataset comes in two: one file has the Bigfoot report text (where, when, what), a second has the coordinates. Neither alone lets you map a sighting. Joins stitch them together on a shared key — and the result is exactly the file we’ll map next week. 🦶

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 the combined report + coordinates table

Tools today:

tidyverse (dplyr)

Data (two tables):

bfro_reports.csv — the report text + class
bfro_locations.csv — the coordinates

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?

Predicting the row count is the whole skill — a join that returns the wrong number of rows is the #1 data bug.
Typing builds the *_join() muscle memory.
Chunk → practice keeps each join type distinct.

🧩 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 Bigfoot tables ---------------------------
library(tidyverse)

reports_df   <- read_csv("data/bfro_reports.csv")
locations_df <- read_csv("data/bfro_locations.csv")

dim(reports_df)      # the text: where/when/what

[1] 5485   19

dim(locations_df)    # the coordinates

[1] 4250    7

reports_df — one row per sighting report: year, season, state, county, class, and the written account
locations_df — one row per report that got geocoded: a latitude and longitude
To map a sighting you need both — its class and its coordinates

What Is a Key?

# The columns that identify a report -----------------
reports_df %>% select(REPORT_NUMBER, YEAR, STATE, REPORT_CLASS) %>% head(3)

# A tibble: 3 × 4
  REPORT_NUMBER YEAR         STATE        REPORT_CLASS
          <dbl> <chr>        <chr>        <chr>       
1         30680 2010         Alabama      Class B     
2          1261 Early 1990's Alaska       Class A     
3          6496 1974         Rhode Island Class A

locations_df %>% select(number, latitude, longitude) %>% head(3)

# A tibble: 3 × 3
  number latitude longitude
   <dbl>    <dbl>     <dbl>
1    637     61.5     -143.
2   2917     55.2     -133.
3   7963     55.2     -133.

A key is the column that uniquely identifies a row and links the tables.

In reports_df it’s REPORT_NUMBER
In locations_df it’s number
Same idea, different name — we’ll tell the join that

Note

📖 Vocabulary

The key must be unique in the table you’re looking things up in (locations_df).

Clean the Key Before Joining

# Two fixes: drop duplicate reports, match key type --
reports_df <- reports_df %>%
  distinct(REPORT_NUMBER, .keep_all = TRUE) %>%   # one row per report
  mutate(REPORT_NUMBER = as.integer(REPORT_NUMBER))

n_distinct(reports_df$REPORT_NUMBER)

[1] 5022

n_distinct(locations_df$number)

[1] 4250

distinct() — keep one row per report number
as.integer() — REPORT_NUMBER was stored like 30680.0; number is 30680. Keys must be the same type to match

Warning

⚠️ Watch out! A join silently fails to match if the keys are different types (text vs number) or formats — clean them first.

🛑 Pause — Do Activity Parts 1–3 Now

Load both tables, find the key in each, and clean it. Predict how many reports have coordinates before we join.

🧩 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 Report

Note

🔮 Predict first: reports_df has ~5,000 reports; only ~4,000 were geocoded. After a left_join, how many rows will we have, and how many will have a latitude?

# Match on differently-named keys with by = c() ------
bigfoot_df <- reports_df %>%
  left_join(locations_df, by = c("REPORT_NUMBER" = "number"))

nrow(bigfoot_df)                    # every report is kept

[1] 5022

sum(!is.na(bigfoot_df$latitude))    # how many got coordinates

[1] 4056

by = c("REPORT_NUMBER" = "number") — “match my REPORT_NUMBER to their number”
left_join keeps every row on the left (all reports)
Reports with no location get NA coordinates — they simply weren’t geocoded

📖 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 happen to have an index column:

reports_df %>% left_join(locations_df)
#> Joining with `by = join_by(index)`

That matches row 1 to row 1, row 2 to row 2 — nonsense! index is just a row number, not a report ID. You get coordinates glued to the wrong reports, with no error.

The fix — always name the real key:

reports_df %>% left_join(locations_df, by = c("REPORT_NUMBER" = "number"))

Important

✅ Why show a silent wrong join?

When dplyr prints “Joining with by = ...” and you didn’t choose that column — stop. Auto-joining on an accidental shared name (index, id, x) is a classic silent bug. Name your key every time.

🛑 End of Day 1 · Start Day 2 Here

Day 1 recap — you can now:

Explain a key and why it must be unique and same-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: Which will have more rows — an inner_join (only matches) or a left_join (all reports)? By roughly how many?

# Same key, three different rules --------------------
inner <- inner_join(reports_df, locations_df, by = c("REPORT_NUMBER" = "number"))
left  <- left_join (reports_df, locations_df, by = c("REPORT_NUMBER" = "number"))
full  <- full_join (reports_df, locations_df, by = c("REPORT_NUMBER" = "number"))

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

inner  left  full 
 4056  5022  5216

Join	Keeps
`inner_join`	only rows that match in both
`left_join`	all rows from the left (reports)
`full_join`	all rows from both tables

inner drops the reports with no coordinates; full even keeps locations with no report.

Which Join Should You Use?

left_join — the default. Keep everything on your main table, add what you can. (Use this for the map: keep all reports.)
inner_join — only rows with a match. (Use when missing data would ruin the analysis.)
full_join — keep everything from both. (Use to audit what’s missing on each side.)

Note

✅ Key idea

Start from the question: “Which rows do I refuse to lose?” That answer picks your join. For mapping we refuse to lose reports, so left_join.

📖 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: How many reports have no coordinates? (Hint: total reports minus the number that got a latitude in Chunk 2.)

# Reports that have NO location ----------------------
missing_coords <- reports_df %>%
  anti_join(locations_df, by = c("REPORT_NUMBER" = "number"))

nrow(missing_coords)

[1] 966

missing_coords %>% count(STATE, sort = TRUE) %>% head(4)

# A tibble: 4 × 2
  STATE            n
  <chr>        <int>
1 California     140
2 Pennsylvania    67
3 Oregon          63
4 Washington      61

anti_join keeps the left rows with no match — perfect for “what’s missing?”
It adds no columns — it filters
Here: the reports we could never map, and where they cluster

📖 R4DS §19.5 — filtering joins

`semi_join()` — Keep Only What Matched

# Keep only reports that DO have a location ----------
mappable_reports <- reports_df %>%
  semi_join(locations_df, by = c("REPORT_NUMBER" = "number"))

nrow(mappable_reports)

[1] 4056

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_join result = the rows an inner_join would keep, but without the extra columns

The Payoff — Build the Map-Ready Table

# Reports + coordinates, ready to map next week ------
bigfoot_mappable <- reports_df %>%
  left_join(locations_df, by = c("REPORT_NUMBER" = "number")) %>%
  filter(!is.na(latitude), !is.na(longitude))

bigfoot_mappable %>%
  select(REPORT_NUMBER, STATE, SEASON, REPORT_CLASS, latitude, longitude) %>%
  head()

# A tibble: 6 × 6
  REPORT_NUMBER STATE         SEASON REPORT_CLASS latitude longitude
          <dbl> <chr>         <chr>  <chr>           <dbl>     <dbl>
1          6496 Rhode Island  Fall   Class A          41.4     -71.5
2          9765 Oklahoma      Fall   Class A          35.3     -99.2
3          4983 Ohio          Summer Class A          39.4     -81.7
4         31940 New York      Fall   Class A          41.3     -73.7
5          5692 Nevada        Fall   Class B          39.6    -120. 
6         55269 New Hampshire Summer Class A          43.4     -72.3

This joined table — report text plus coordinates — is essentially bfro_reports_geocoded.csv, the file you’ll turn into a map in Lecture 13.

You just built a real dataset from two raw ones. That’s the everyday work of data science.

🛑 Pause — Do Activity Parts 10–12 Now

Use anti_join to find the un-geocoded reports, semi_join to keep the mappable ones, 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()
distinct() + as.integer() for key hygiene

References:

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

Up next — Lecture 13:

Mapping — turn this joined table into a map with sf and geom_sf()
Where is Bigfoot, anyway?

Lecture 12 — Joins: Combining Two Tables

Where we left off — ANOVA & Factors

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 Report

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 the Map-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 Report

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

`semi_join()` — Keep Only What Matched