Prove the key before you join — Data Cleaning and Validation — Data Analysis

The assumption nothing checks

You have a student roster and a daily attendance file. You join them on student_id, and in doing so you have asserted something: that student_id identifies 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

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.

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"]))

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

On this roster: 1,202 rows, 1,200 distinct student_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:

repeated = roster[roster["student_id"].duplicated(keep=False)]
print(repeated.sort_values("student_id"))

roster |>
  group_by(student_id) |>
  filter(n() > 1) |>
  arrange(student_id)

student_id	school_id	grade	feeding_programme
STU0150	SCH09	3	false
STU0150	SCH08	3	true
STU0896	SCH01	2	true
STU0896	SCH06	2	false

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

student_id is not a key of the roster. student_id plus school_id is, 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:

print(is_key(attendance, ["student_id", "attendance_date"]))

is_key(attendance, c("student_id", "attendance_date"))

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:

ROSTER_KEY = ["student_id"]          # intended; violated by 2 rows, see cleaning log
ATTENDANCE_KEY = ["student_id", "attendance_date"]

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

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.

joined = attendance.merge(roster, on="student_id", how="left")
print(len(attendance), "->", len(joined))

joined <- attendance |> left_join(roster, by = "student_id")
cat(nrow(attendance), "->", nrow(joined), "\n")

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

Both languages will check the relationship for you. Use it.

joined = attendance.merge(
    roster,
    on="student_id",
    how="left",
    validate="many_to_one",   # many attendance rows, one roster row
)

joined <- attendance |>
  left_join(roster, by = "student_id", relationship = "many-to-one")

Both fail loudly on this data:

MergeError: Merge keys are not unique in right dataset; not a many-to-one merge

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`.

An error you have to deal with today beats a silently inflated attendance denominator you deal with in a review meeting in November. Put validate= or relationship= 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

Where the relationship argument does not fit — a many-to-many that genuinely is one — assert the count directly:

before = len(attendance)
joined = attendance.merge(roster, on="student_id", how="left")
assert len(joined) == before, f"join changed row count: {before} -> {len(joined)}"

before <- nrow(attendance)
joined <- attendance |> left_join(roster, by = "student_id")
stopifnot(nrow(joined) == before)

The assertion is three words longer than the join. Write it every time.

The rows on one side and not the other

A key can be unique and still not match. Check both directions before you accept the result of a join:

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

setdiff(attendance$student_id, roster$student_id) |> length()
setdiff(roster$student_id, attendance$student_id) |> length()

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:

Attendance without a roster row — a student marked present who is not enrolled. Either the roster is incomplete or someone is being recorded who should not be.
Roster rows with no attendance — enrolled students never marked either way. In an education programme that is not a data defect, it is the finding: those are the children who never came.

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

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.

Duplicates create false merges. Two households whose head is called Jean Baptiste become one household.
Variants create false splits. Jean Baptiste, JEAN BAPTISTE and Jean Baptiste become three.

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 is merge(on="name") and move on.

Assert, do not inspect

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.

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)}"
        )

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

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.

Unit 4 turns this pattern into a validation suite. For now, put assert_key at the top of every script that joins anything.

What comes next

assert_key finds 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.