Back to the lessonLesson 2 of 8Joins you can prove

Proving the join did what you said

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.

Slides · PDF Slides · PowerPoint

Slide 1 / 27
What this lesson covers
- The check has to be cheaper than the bug
- Declare the relationship, always
- The reconciliation table
- Guard the row count when you meant to preserve it
- The unmatched rate is an indicator, not an error
- Normalise the key before you blame the join
- The suffix collision
- Keys with different names
- What comes next
Speaker notes
A reconciliation table from every join — matched rows, both anti-join counts, the row count before and after — plus the suffix collision that overwrites a column without telling you.
Slide 2 / 27
The check has to be cheaper than the bug
- Nobody skips join checks because they think joins are safe.
Speaker notes
Nobody skips join checks because they think joins are safe. They skip them because checking takes four commands, the output has to be read, and the join "obviously" worked. So the check has to become one call that produces one small table. Write it once, put it in the file every script imports, and the cost of checking drops below the cost of wondering.
Slide 3 / 27
Declare the relationship, always — In Python
```
joined = attendance.merge(
    roster, on="student_id", how="left", validate="many_to_one",
)
```
Speaker notes
The first line of defence is an argument you were already able to pass.

Slide 4 / 27

Declare the relationship, always — In R

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

Slide 5 / 27

Declare the relationship, always

You expect	pandas `validate=`	dplyr `relationship=`
One row each side	`one_to_one`	`one-to-one`
Many left, one right	`many_to_one`	`many-to-one`
One left, many right	`one_to_many`	`one-to-many`
Genuinely many-to-many	omit	`many-to-many`

Speaker notes

Four values, and choosing between them forces you to say what you believe:

Slide 6 / 27
Declare the relationship, always
- dplyr warns on an unexpected many-to-many even without the argument — pandas does not
Speaker notes
dplyr warns on an unexpected many-to-many even without the argument; pandas does not. That difference has decided a number of quietly wrong figures, and it is the reason the Python examples in this course always pass validate=. Note the last row. Passing many-to-many explicitly is not a way of silencing the check — it is a claim that the fan-out is intended, and it belongs beside a comment saying what the resulting grain is.

Slide 7 / 27

The reconciliation table — In Python (cont.)

def reconcile(left, right, on, left_name="left", right_name="right"):
    left_keys = left[on].drop_duplicates()
    right_keys = right[on].drop_duplicates()
    merged = left_keys.merge(right_keys, on=on, how="outer", indicator=True)
    counts = merged["_merge"].value_counts()

    return pd.Series({
        f"{left_name} rows": len(left),
        f"{right_name} rows": len(right),
        "keys matched": int(counts.get("both", 0)),
        f"{left_name} only": int(counts.get("left_only", 0)),
        f"{right_name} only": int(counts.get("right_only", 0)),
        f"{left_name} unmatched %": round(
            100 * counts.get("left_only", 0) / max(len(left_keys), 1), 2
        ),
    })

Speaker notes

The relationship argument tells you the shape was right. It does not tell you what failed to match, and that is usually the more interesting question.

Slide 8 / 27

The reconciliation table — In Python (cont.)



print(reconcile(attendance, roster, ["student_id"], "attendance", "roster"))

Slide 9 / 27

The reconciliation table — In R (cont.)

reconcile <- function(left, right, on, left_name = "left", right_name = "right") {
  left_keys  <- dplyr::distinct(dplyr::select(left, dplyr::all_of(on)))
  right_keys <- dplyr::distinct(dplyr::select(right, dplyr::all_of(on)))

  tibble::tibble(
    measure = c(paste(left_name, "rows"), paste(right_name, "rows"),
                "keys matched", paste(left_name, "only"), paste(right_name, "only")),
    value = c(
      nrow(left), nrow(right),
      nrow(dplyr::inner_join(left_keys, right_keys, by = on)),
      nrow(dplyr::anti_join(left_keys, right_keys, by = on)),
      nrow(dplyr::anti_join(right_keys, left_keys, by = on))
    )
  )
}

Slide 10 / 27

The reconciliation table — In R (cont.)

reconcile(attendance, roster, "student_id", "attendance", "roster")

Slide 11 / 27
The reconciliation table
measure value
attendance rows 70,245
roster rows 1,200
keys matched 1,200
attendance only 0
roster only 0
Speaker notes
Note it reconciles the distinct keys, not the rows. Comparing row counts across a one-to-many join tells you nothing; comparing the key sets tells you exactly who is on one side and not the other. That is the table to paste into a notebook cell above every join. Four seconds to read, and it makes the two silent failure modes impossible to miss.

measure	value
attendance rows	70,245
roster rows	1,200
keys matched	1,200
attendance only	0
roster only	0

Slide 12 / 27

Guard the row count when you meant to preserve it — In Python

def join_preserving(left, right, on, how="left", **kwargs):
    before = len(left)
    out = left.merge(right, on=on, how=how, **kwargs)
    if len(out) != before:
        raise ValueError(f"join changed row count: {before} -> {len(out)}")
    return out

Slide 13 / 27
Guard the row count when you meant to preserve it — In R
```
join_preserving <- function(left, right, by, ...) {
  before <- nrow(left)
  out <- dplyr::left_join(left, right, by = by, ...)
  if (nrow(out) != before) {
    stop(sprintf("join changed row count: %d -> %d", before, nrow(out)))
  }
  out
}
```
Speaker notes
This is strictly stronger than validate=, because it also catches the case where the key is unique on both sides but the left table lost rows — which happens the moment someone changes how="left" to how="inner" while debugging something else and does not change it back.

Slide 14 / 27

The unmatched rate is an indicator, not an error — In Python

summary = reconcile(distributions, registration, ["beneficiary_id"])
if summary["left unmatched %"] > 5:
    print(f"WARNING: {summary['left unmatched %']}% of distribution rows "
          "have no registration record")

Speaker notes

A join that fails to match 6% of a beneficiary list is telling you something about registration, not about your code. Report it.

Slide 15 / 27
The unmatched rate is an indicator, not an error — In R
```
unmatched <- nrow(anti_join(distributions, registration, by = "beneficiary_id"))
share <- unmatched / nrow(distributions)
if (share > 0.05) warning(sprintf("%.1f%% of distributions have no registration", 100 * share))
```
Speaker notes
Track the number across rounds and it becomes a data quality trend that belongs in a monthly report — the same move as the validation-finding series in the cleaning course. A match rate that falls from 98% to 91% between two rounds is a registration process that changed, and it is far more useful to know that in March than to discover it in an audit.
Slide 16 / 27
Normalise the key before you blame the join
- Type. A facility code read as an integer on one side and a string on the other. 1042 never equals "1042", and…
- Whitespace and case. "FAC001 " and "fac001" are three different keys.
- Dates against date-times. 2024-03-01 and 2024-03-01 00:00:00 compare equal in some libraries and not others; a…
Speaker notes
A large share of "the join did not work" is a key that does not compare equal:

Slide 17 / 27

Normalise the key before you blame the join — In Python

def key_clean(series):
    return series.astype("string").str.strip().str.upper()


for frame in (registers, aggregates):
    frame["facility_id"] = key_clean(frame["facility_id"])

Slide 18 / 27

Normalise the key before you blame the join — In R

key_clean <- function(x) toupper(stringr::str_squish(as.character(x)))

registers  <- registers  |> mutate(facility_id = key_clean(facility_id))
aggregates <- aggregates |> mutate(facility_id = key_clean(facility_id))

Slide 19 / 27
Normalise the key before you blame the join
- Do this to both sides in the same function — so they cannot drift
Speaker notes
Do this to both sides in the same function, so they cannot drift. Two separate cleaning expressions is the same defect as two separate indicator calculations.
Slide 20 / 27
The suffix collision — In Python
```
merged = registers.merge(aggregates, on="facility_id", suffixes=("_register", "_dhis2"))
```
Speaker notes
Both tables have a column called updated_at, or district, or notes. The join does not fail; it renames.

Slide 21 / 27

The suffix collision — In R

merged <- registers |>
  left_join(aggregates, by = "facility_id", suffix = c("_register", "_dhis2"))

Slide 22 / 27
The suffix collision — In Python
```
overlap = (set(registers.columns) & set(aggregates.columns)) - {"facility_id"}
print("columns in both:", sorted(overlap))
```
Speaker notes
Both libraries default to something unhelpful — _x and _y in pandas, .x and .y in dplyr — and three joins later nobody can say whether district_x came from the register or the aggregate. Name the suffixes after the source, every time. It costs one argument and it is the difference between a traceable table and a guess. The worse version is a shared column you did not know about, silently carried through the join and then used. Check before joining:

Slide 23 / 27

The suffix collision — In R

setdiff(intersect(names(registers), names(aggregates)), "facility_id")

Slide 24 / 27
Keys with different names — In Python
```
merged = cases.merge(
    facilities, left_on="site_code", right_on="facility_id", how="left",
    validate="many_to_one",
)
```
Speaker notes
Say so explicitly rather than renaming a column to make the join work — the rename outlives the join and confuses the next reader.

Slide 25 / 27

Keys with different names — In R

merged <- cases |>
  left_join(facilities, by = c("site_code" = "facility_id"),
            relationship = "many-to-one")

Slide 26 / 27
What comes next
- Every check so far has assumed you know what one row of each table is.
Speaker notes
Every check so far has assumed you know what one row of each table is. That assumption is where the remaining join failures live: a table of households joined to a table of people, or a monthly aggregate joined to a daily register. The next lesson is about naming the grain — and aggregating to it before the join, rather than discovering afterwards that you multiplied.
Slide 27 / 27
Where this goes next
Read the full lesson, with runnable code Back to the lesson

What this lesson covers

The check has to be cheaper than the bug

Declare the relationship, always — In Python

Declare the relationship, always — In R

Declare the relationship, always

Declare the relationship, always

The reconciliation table — In Python (cont.)

The reconciliation table — In Python (cont.)

The reconciliation table — In R (cont.)

The reconciliation table — In R (cont.)

The reconciliation table

Guard the row count when you meant to preserve it — In Python

Guard the row count when you meant to preserve it — In R

The unmatched rate is an indicator, not an error — In Python

The unmatched rate is an indicator, not an error — In R

Normalise the key before you blame the join

Normalise the key before you blame the join — In Python

Normalise the key before you blame the join — In R

Normalise the key before you blame the join

The suffix collision — In Python

The suffix collision — In R

The suffix collision — In Python

The suffix collision — In R

Keys with different names — In Python

Keys with different names — In R

What comes next

Where this goes next