Lecture 12 — Joins: Combining Two Tables

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

joins
tidyverse
wrangling

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 the Bigfoot report text with its coordinates to build exactly the dataset we map next week.

Author

Bill Perry

Published

July 5, 2026

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:

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:

  1. Predict — before it runs, say how many rows you’ll get
  2. Type the code by hand — do not copy-paste
  3. 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:

Up next — Lecture 13:

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