Unit 04.04: The only verb that can make a table bigger

A join is the only common verb that can make a table bigger. Count the rows before and after, every time.

Two questions: which rows survive, and does the key repeat

inner_join() keeps only rows with a match on both sides. left_join() keeps every row of the left table and fills the missing right-hand columns with NA. Choosing between them is a decision about your data, not a preference: an inner join silently deletes observations.

The subtler risk is a key that is not unique. If the right table has two rows for North, every North row on the left becomes two rows. The join succeeds, the row count grows, and any sum computed afterwards is inflated — usually by an amount too small to look obviously wrong.

anyDuplicated() on the key answers that in one line, and is worth running before every join.

This block joins two tables that only partly overlap, then joins one with a repeated key.

suppressPackageStartupMessages(library(dplyr))

visits <- data.frame(ward = c("North", "South", "East"),
                     visits = c(412, 388, 502))
population <- data.frame(ward = c("North", "South", "West"),
                         residents = c(12400, 9800, 7300))

# Always count rows before and after. A join that changes the count silently
# has either dropped observations or duplicated them.
inner  <- inner_join(visits, population, by = "ward")
left   <- left_join(visits, population, by = "ward")

cat("visits rows:", nrow(visits), "\n")
cat("inner_join :", nrow(inner), "rows -- East dropped, it has no population row\n")
cat("left_join  :", nrow(left), "rows -- East kept, residents is NA\n\n")
print(left)

# The dangerous case: a key that is not unique on the right multiplies rows.
duplicated_key <- data.frame(ward = c("North", "North"), site = c("A", "B"))
cat("\nJoining a table with 2 rows for North:",
    nrow(left_join(visits, duplicated_key, by = "ward")), "rows out of 3 in.\n")
cat("Check with: anyDuplicated(population$ward) ==",
    anyDuplicated(population$ward), "\n")

Three visit rows give 2 rows from the inner join — East is dropped because it has no population row — and 3 from the left join, where East survives with residents as NA. That NA is information: it says the population reference is incomplete. The inner join discarded that fact entirely. The last join then turns 3 rows into 4, because North appears twice on the right. anyDuplicated() returns 0 for the clean key, which is the check that would have caught it.

The mistake this prevents

The mistake is reaching for inner_join() because it avoids NAs. It avoids them by deleting the rows that would have carried them, so your analysis silently covers fewer observations than you report.

Takeaway

Choose the join type deliberately, check the key with anyDuplicated() first, and compare row counts before and after. A changed count is either intended or a bug — never a detail.

Mark complete