Prouver que la jointure a fait ce que vous avez annoncé — Jointures et restructuration des données de programme — Analyse de données

Le contrôle doit coûter moins cher que le bug

Personne ne saute les contrôles de jointure en croyant les jointures sûres. On les saute parce que vérifier prend quatre commandes, qu’il faut lire la sortie, et que la jointure a « manifestement » fonctionné.

Le contrôle doit donc devenir un seul appel produisant un seul petit tableau. Écrivez-le une fois, placez-le dans le fichier que tous vos scripts importent, et le coût de la vérification passe sous celui du doute.

Déclarez la relation, toujours

La première ligne de défense est un argument que vous pouviez déjà passer.

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

Quatre valeurs, et choisir entre elles vous oblige à dire ce que vous croyez.

Ce que vous attendez	pandas `validate=`	dplyr `relationship=`
Une ligne de chaque côté	`one_to_one`	`one-to-one`
Plusieurs à gauche, une à droite	`many_to_one`	`many-to-one`
Une à gauche, plusieurs à droite	`one_to_many`	`one-to-many`
Réellement plusieurs à plusieurs	omettre	`many-to-many`

dplyr avertit d’un plusieurs-à-plusieurs inattendu même sans l’argument ; pandas non. Cette différence a décidé du sort d’un certain nombre de chiffres discrètement faux, et c’est la raison pour laquelle les exemples Python de ce cours passent toujours validate=.

Notez la dernière ligne. Passer many-to-many explicitement n’est pas une façon de faire taire le contrôle — c’est l’affirmation que le gonflement est voulu, et cela va de pair avec un commentaire disant quelle est la granularité obtenue.

Le tableau de rapprochement

L’argument de relation vous dit que la forme était bonne. Il ne vous dit pas ce qui n’a pas trouvé de partenaire, et c’est en général la question la plus intéressante.

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

Remarquez qu’il rapproche les clés distinctes, pas les lignes. Comparer des nombres de lignes de part et d’autre d’une jointure un-à-plusieurs n’apprend rien ; comparer les ensembles de clés dit exactement qui est d’un côté et pas de l’autre.

mesure	valeur
lignes de présence	70 245
lignes de liste	1 200
clés appariées	1 200
présence seulement	0
liste seulement	0

Voilà le tableau à coller dans une cellule au-dessus de chaque jointure. Quatre secondes de lecture, et les deux modes de défaillance silencieux deviennent impossibles à manquer.

Protégez le nombre de lignes quand vous vouliez le préserver

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
}

C’est strictement plus fort que validate=, car cela attrape aussi le cas où la clé est unique des deux côtés mais où la table de gauche a perdu des lignes — ce qui arrive dès que quelqu’un remplace how="left" par how="inner" en déboguant autre chose et oublie de revenir en arrière.

Le taux de non-appariement est un indicateur, pas une erreur

Une jointure qui n’apparie pas 6 % d’une liste de bénéficiaires vous dit quelque chose sur l’enregistrement, pas sur votre code. Rapportez-le.

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

Suivez ce nombre d’un tour à l’autre et il devient une tendance de qualité des données qui a sa place dans un rapport mensuel — le même geste que la série de constats de validation du cours de nettoyage. Un taux d’appariement qui tombe de 98 % à 91 % entre deux tours signale un processus d’enregistrement qui a changé, et il est bien plus utile de le savoir en mars que de le découvrir lors d’un audit.

Normalisez la clé avant d’accuser la jointure

Une large part des « la jointure n’a pas marché » vient d’une clé qui ne se compare pas égale.

Le type. Un code de formation sanitaire lu comme entier d’un côté et comme chaîne de l’autre. 1042 n’égale jamais "1042", et "01042" lu comme un nombre perd définitivement son zéro initial.
Espaces et casse. "FAC001 " et "fac001" font trois clés différentes.
Dates contre horodatages. 2024-03-01 et 2024-03-01 00:00:00 se comparent égaux dans certaines bibliothèques et pas dans d’autres ; une colonne de période est plus sûre en chaîne de caractères.

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

Faites-le sur les deux côtés dans la même fonction, pour qu’ils ne puissent pas diverger. Deux expressions de nettoyage séparées, c’est le même défaut que deux calculs d’indicateur séparés.

La collision de suffixes

Les deux tables ont une colonne nommée updated_at, ou district, ou notes. La jointure n’échoue pas, elle renomme.

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

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

Les deux bibliothèques ont un défaut peu utile — _x et _y en pandas, .x et .y en dplyr — et trois jointures plus loin, plus personne ne peut dire si district_x vient du registre ou de l’agrégat. Nommez les suffixes d’après la source, à chaque fois. Cela coûte un argument et fait la différence entre une table traçable et une supposition.

Pire encore, une colonne partagée dont vous ignoriez l’existence, transportée en silence par la jointure puis utilisée. Vérifiez avant de joindre.

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

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

Des clés aux noms différents

Dites-le explicitement plutôt que de renommer une colonne pour faire marcher la jointure — le renommage survit à la jointure et égare le lecteur suivant.

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

La suite

Tous les contrôles vus jusqu’ici supposent que vous savez ce qu’est une ligne de chaque table. C’est dans cette supposition que logent les défaillances restantes — une table de ménages jointe à une table de personnes, ou un agrégat mensuel joint à un registre journalier. La leçon suivante porte sur la désignation de la granularité, et sur l’agrégation à cette granularité avant la jointure plutôt que sur la découverte après coup d’une multiplication.