Back to the lesson·Lesson 3 of 8·Identity and duplication
Prove the key before you join
The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.
What this lesson covers
- The assumption nothing checks
- A key is a claim, and it is testable
- Composite keys, and the one you actually have
- What a broken key does to a join
- Say what you expect, and let it raise
- Guard the row count when the join is the point
- The rows on one side and not the other
- Columns that were never meant to be keys
- Assert, do not inspect
- What comes next
Speaker notes
Every join makes a claim about uniqueness that nothing checks. Test it, watch two duplicated roster rows fan a 70,245-row table out by 120, and learn to assert the row count instead of inspecting it.The assumption nothing checks
- You have a student roster and a daily attendance file.
Speaker notes
You have a student roster and a daily attendance file. You join them onstudent_id, and in doing so you have asserted something: thatstudent_ididentifies exactly one row of the roster. Nothing in either file says so. No error is raised if it is false. What happens instead is that the join quietly produces more rows than it started with, every downstream count is inflated by an amount nobody can see, and the resulting attendance rate is wrong by an amount too small to look wrong. This lesson is about testing that claim before making it.A key is a claim, and it is testable — In Python
def is_key(df, columns): subset = df[columns] return { "rows": len(df), "distinct": len(subset.drop_duplicates()), "missing_any": int(subset.isna().any(axis=1).sum()), "is_key": len(subset.drop_duplicates()) == len(df) and not subset.isna().any(axis=None), } print(is_key(roster, ["student_id"])) print(is_key(attendance, ["student_id", "attendance_date"]))Speaker notes
The claim has two parts: unique and complete. A column with duplicates is not a key. A column with missing values is not a key either, because a missing value cannot identify anything.A key is a claim, and it is testable — In R
is_key <- function(df, columns) { subset <- dplyr::select(df, dplyr::all_of(columns)) list( rows = nrow(df), distinct = nrow(dplyr::distinct(subset)), missing_any = sum(!stats::complete.cases(subset)), is_key = nrow(dplyr::distinct(subset)) == nrow(df) && !any(is.na(subset)) ) } is_key(roster, "student_id") is_key(attendance, c("student_id", "attendance_date"))A key is a claim, and it is testable — In Python
repeated = roster[roster["student_id"].duplicated(keep=False)] print(repeated.sort_values("student_id"))Speaker notes
On this roster: 1,202 rows, 1,200 distinctstudent_id. It is not a key, by two rows. Two rows out of 1,202 is 0.17% and sounds like it cannot matter. Look at what they are:A key is a claim, and it is testable — In R
roster |> group_by(student_id) |> filter(n() > 1) |> arrange(student_id)A key is a claim, and it is testable
student_id school_id grade feeding_programme STU0150 SCH09 3 false STU0150 SCH08 3 true STU0896 SCH01 2 true STU0896 SCH06 2 false Speaker notes
Two students transferred school and were never de-registered from the first one. Each now appears once in a school with a feeding programme and once in a school without — and the headline analysis on this dataset is whether school feeding improves attendance. Those two students will contribute their entire attendance record to both arms of the comparison. That is the shape of this defect in general. The duplicate is rarely random; it is usually the record of something that happened — a transfer, a re-registration, a form submitted twice — and it usually lands in exactly the variable the analysis is about.Composite keys, and the one you actually have — In Python
print(is_key(attendance, ["student_id", "attendance_date"]))Speaker notes
student_idis not a key of the roster.student_idplusschool_idis, because the two rows differ on school. Whether that is the key you want is a different question: if the analysis is "one row per student", a composite key that admits two rows per student has documented the problem rather than solved it. The attendance file is the more usual case, where the natural key is composite from the start:Composite keys, and the one you actually have — In R
is_key(attendance, c("student_id", "attendance_date"))Composite keys, and the one you actually have — In Python
ROSTER_KEY = ["student_id"] # intended; violated by 2 rows, see cleaning log ATTENDANCE_KEY = ["student_id", "attendance_date"]Speaker notes
70,245 rows, 70,245 distinct pairs. That is a key. One row per student per school day, exactly as the file claims to be. Get in the habit of writing the key down in the code, next to the read:Composite keys, and the one you actually have — In R
ROSTER_KEY <- "student_id" # intended; violated by 2 rows, see cleaning log ATTENDANCE_KEY <- c("student_id", "attendance_date")What a broken key does to a join — In Python
joined = attendance.merge(roster, on="student_id", how="left") print(len(attendance), "->", len(joined))Speaker notes
The arithmetic is worth doing once by hand, because it explains every fan-out you will ever see. A join matches every row on the left against every row on the right that shares the key. If a student has 60 attendance rows and 1 roster row, the join produces 60 rows. If that student has 2 roster rows, it produces 120.What a broken key does to a join — In R
joined <- attendance |> left_join(roster, by = "student_id") cat(nrow(attendance), "->", nrow(joined), "\n")Speaker notes
70,245 becomes 70,365. One hundred and twenty extra rows: two students, sixty school days each, counted twice. A left join that adds rows is a contradiction in terms — "left join" is usually read as "keep the left table and attach columns", and that reading is only true when the right side is unique on the key. It is not a bug in the join. It is the join telling you the truth about your data, in a form nobody looks at.Say what you expect, and let it raise — In Python
joined = attendance.merge( roster, on="student_id", how="left", validate="many_to_one", # many attendance rows, one roster row )Speaker notes
Both languages will check the relationship for you. Use it.Say what you expect, and let it raise — In R
joined <- attendance |> left_join(roster, by = "student_id", relationship = "many-to-one")Say what you expect, and let it raise — Example
MergeError: Merge keys are not unique in right dataset; not a many-to-one mergeSpeaker notes
Both fail loudly on this data:Say what you expect, and let it raise — Example
Error in `left_join()`: ! Each row in `x` must match at most 1 row in `y`. i Row 1 of `x` matches multiple rows in `y`.Speaker notes
An error you have to deal with today beats a silently inflated attendance denominator you deal with in a review meeting in November. Putvalidate=orrelationship=on every join you write. It costs one argument and it converts the most expensive class of silent bug into a stack trace.Guard the row count when the join is the point — In Python
before = len(attendance) joined = attendance.merge(roster, on="student_id", how="left") assert len(joined) == before, f"join changed row count: {before} -> {len(joined)}"Speaker notes
Where the relationship argument does not fit — a many-to-many that genuinely is one — assert the count directly:Guard the row count when the join is the point — In R
before <- nrow(attendance) joined <- attendance |> left_join(roster, by = "student_id") stopifnot(nrow(joined) == before)Speaker notes
The assertion is three words longer than the join. Write it every time.The rows on one side and not the other — In Python
left_only = set(attendance["student_id"]) - set(roster["student_id"]) right_only = set(roster["student_id"]) - set(attendance["student_id"]) print(f"{len(left_only)} students with attendance and no roster row") print(f"{len(right_only)} students on the roster with no attendance")Speaker notes
A key can be unique and still not match. Check both directions before you accept the result of a join:The rows on one side and not the other — In R
setdiff(attendance$student_id, roster$student_id) |> length() setdiff(roster$student_id, attendance$student_id) |> length()The rows on one side and not the other
- Attendance without a roster row — a student marked present who is not enrolled. Either the roster is incomplete or…
- Roster rows with no attendance — enrolled students never marked either way. In an education programme that is not a…
Speaker notes
Here both are zero, which is the answer you want and almost never get. When it is not zero, the two directions mean completely different things: An inner join makes both groups disappear and reports a clean number. That is why the check goes before the join and not after.Columns that were never meant to be keys
- Duplicates create false merges. Two households whose head is called Jean Baptiste become one household.
- Variants create false splits.
Jean Baptiste,JEAN BAPTISTEandJean Baptistebecome three.
Speaker notes
Names, phone numbers and household head names get used as keys constantly, because they are the only thing two files have in common. They are not keys, and the failure mode is asymmetric. If a name is genuinely the only link you have, that is a record linkage problem, not a join, and the next lesson is entirely about it. What you must not do ismerge(on="name")and move on.Assert, do not inspect — In Python
def assert_key(df, columns, name): duplicated = df.duplicated(subset=columns, keep=False) if duplicated.any(): offenders = df.loc[duplicated, columns].drop_duplicates() raise ValueError( f"{name}: {duplicated.sum()} rows violate the key {columns}\n" f"{offenders.head(10)}" )Speaker notes
The pattern this lesson is really teaching is smaller than any of the checks in it. Every one of them can be written as a printout you read once, or as an assertion that runs on every future export. Only the second one survives.Assert, do not inspect — In R
assert_key <- function(df, columns, name) { dup <- duplicated(df[columns]) | duplicated(df[columns], fromLast = TRUE) if (any(dup)) { stop(sprintf("%s: %d rows violate the key %s", name, sum(dup), paste(columns, collapse = " + "))) } invisible(df) }Assert, do not inspect
A check you ran once tells you about the file you had. A check that runs on read tells you about the file you have.
Speaker notes
Unit 4 turns this pattern into a validation suite. For now, putassert_keyat the top of every script that joins anything.What comes next
assert_keyfinds the students recorded twice under the same identifier.
Speaker notes
assert_keyfinds the students recorded twice under the same identifier. It cannot find the child re-registered under a new one, the household interviewed twice by two enumerators, or the beneficiary who appears on three distribution lists with three spellings of their name. Those share no key at all, and the next lesson is how to find them without merging two people who merely resemble each other.