Proving the join did what you said — Joining and Reshaping Programme Data — Data Analysis

The check has to be cheaper than the bug

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.

Declare the relationship, always

The first line of defence is an argument you were already able to pass.

joined = attendance.merge(
    roster, on="student_id", how="left", validate="many_to_one",
)

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

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

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`

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.

The reconciliation table

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.

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


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

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

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

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.

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

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.

Guard the row count when you meant to preserve it

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

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
}

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.

The unmatched rate is an indicator, not an error

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

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

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

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.

Normalise the key before you blame the join

A large share of “the join did not work” is a key that does not compare equal:

Type. A facility code read as an integer on one side and a string on the other. 1042 never equals "1042", and "01042" read as a number loses the leading zero permanently.
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 period column is safer as a string.

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

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

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.

The suffix collision

Both tables have a column called updated_at, or district, or notes. The join does not fail; it renames.

merged = registers.merge(aggregates, on="facility_id", suffixes=("_register", "_dhis2"))

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

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:

overlap = (set(registers.columns) & set(aggregates.columns)) - {"facility_id"}
print("columns in both:", sorted(overlap))

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

Keys with different names

Say so explicitly rather than renaming a column to make the join work — the rename outlives the join and confuses the next reader.

merged = cases.merge(
    facilities, left_on="site_code", right_on="facility_id", how="left",
    validate="many_to_one",
)

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

What comes next

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.