Prouvez la clé avant de joindre — Nettoyage et validation des données — Analyse de données

L’hypothèse que rien ne vérifie

Vous avez une liste d’élèves et un fichier de présence journalière. Vous les joignez sur student_id, et ce faisant vous avez affirmé quelque chose — que student_id identifie exactement une ligne de la liste.

Rien dans aucun des deux fichiers ne le dit. Aucune erreur n’est levée si c’est faux. Ce qui se produit à la place, c’est que la jointure produit discrètement plus de lignes qu’elle n’en avait, que tous les décomptes en aval sont gonflés d’une quantité invisible, et que le taux de présence obtenu est faux d’un écart trop petit pour avoir l’air faux.

Cette leçon consiste à tester cette hypothèse avant de la formuler.

Une clé est une affirmation, et elle est testable

L’affirmation a deux volets — unique et complète. Une colonne comportant des doublons n’est pas une clé. Une colonne comportant des valeurs manquantes n’en est pas une non plus, car une valeur manquante n’identifie rien.

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

Sur cette liste — 1 202 lignes, 1 200 student_id distincts. Ce n’est pas une clé, à deux lignes près.

Deux lignes sur 1 202 font 0,17 % et cela n’a l’air de rien. Regardez ce qu’elles sont.

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

Deux élèves ont changé d’école sans jamais être radiés de la première. Chacun figure désormais une fois dans une école avec cantine et une fois dans une école sans — et l’analyse phare de ce jeu de données porte précisément sur l’effet de l’alimentation scolaire sur la présence. Ces deux élèves apporteront tout leur historique de présence aux deux bras de la comparaison.

Telle est la forme générale de ce défaut. Le doublon est rarement fortuit ; il est en général la trace de quelque chose qui a eu lieu — un transfert, une réinscription, un formulaire envoyé deux fois — et il atterrit en général exactement dans la variable sur laquelle porte l’analyse.

Clés composites, et celle dont vous disposez vraiment

student_id n’est pas une clé de la liste. student_id plus school_id en est une, puisque les deux lignes diffèrent par l’école. Savoir si c’est la clé que vous voulez est une autre question — si l’analyse suppose « une ligne par élève », une clé composite qui admet deux lignes par élève a documenté le problème, pas résolu.

Le fichier de présence relève du cas plus courant, où la clé naturelle est composite dès le départ.

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

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

70 245 lignes, 70 245 couples distincts. Voilà une clé. Une ligne par élève et par jour de classe, exactement ce que le fichier prétend être.

Prenez l’habitude d’écrire la clé dans le code, à côté de la lecture.

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

Ce qu’une clé cassée fait à une jointure

L’arithmétique mérite d’être faite une fois à la main, car elle explique tous les gonflements que vous rencontrerez.

Une jointure apparie chaque ligne de gauche à toutes les lignes de droite partageant la clé. Si un élève a 60 lignes de présence et 1 ligne de liste, la jointure produit 60 lignes. Si cet élève a 2 lignes de liste, elle en produit 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 devient 70 365. Cent vingt lignes de plus — deux élèves, soixante jours de classe chacun, comptés deux fois.

Une jointure à gauche qui ajoute des lignes est une contradiction dans les termes : « jointure à gauche » se lit d’ordinaire « garder la table de gauche et y accrocher des colonnes », et cette lecture n’est vraie que si le côté droit est unique sur la clé. Ce n’est pas un défaut de la jointure. C’est la jointure qui vous dit la vérité sur vos données, sous une forme que personne ne regarde.

Dites ce que vous attendez, et laissez la jointure échouer

Les deux langages vérifient la relation pour vous. Servez-vous-en.

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

Les deux échouent bruyamment sur ces données.

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

Une erreur qu’il faut traiter aujourd’hui vaut mieux qu’un dénominateur de présence discrètement gonflé qu’on traitera en réunion de revue au mois de novembre. Mettez validate= ou relationship= sur chaque jointure que vous écrivez. Cela coûte un argument et transforme la classe de bugs silencieux la plus coûteuse en trace d’erreur.

Protégez le nombre de lignes quand la jointure est l’objectif

Là où l’argument de relation ne convient pas — un plusieurs-à-plusieurs qui en est réellement un — affirmez directement le décompte.

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)

L’affirmation fait trois mots de plus que la jointure. Écrivez-la à chaque fois.

Les lignes d’un côté et pas de l’autre

Une clé peut être unique et malgré tout ne pas s’apparier. Vérifiez les deux sens avant d’accepter le résultat d’une jointure.

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

Ici les deux valent zéro, ce qui est la réponse souhaitée et qu’on n’obtient presque jamais. Quand ce n’est pas zéro, les deux sens veulent dire des choses totalement différentes.

De la présence sans ligne de liste — un élève marqué présent sans être inscrit. Soit la liste est incomplète, soit on enregistre quelqu’un qui ne devrait pas l’être.
Des lignes de liste sans aucune présence — des élèves inscrits jamais marqués, dans un sens ni dans l’autre. Dans un programme d’éducation, ce n’est pas un défaut de données, c’est le constat : ce sont les enfants qui ne sont jamais venus.

Une jointure interne fait disparaître les deux groupes et rapporte un chiffre propre. C’est pourquoi le contrôle précède la jointure et ne la suit pas.

Des colonnes qui n’étaient pas faites pour être des clés

Noms, numéros de téléphone et noms de chefs de ménage servent constamment de clés, parce qu’ils sont la seule chose que deux fichiers ont en commun. Ce ne sont pas des clés, et le mode de défaillance est asymétrique.

Les doublons créent de fausses fusions. Deux ménages dont le chef s’appelle Jean Baptiste deviennent un seul ménage.
Les variantes créent de fausses scissions. Jean Baptiste, JEAN BAPTISTE et Jean Baptiste en deviennent trois.

Si un nom est réellement le seul lien dont vous disposez, il s’agit d’un problème d’appariement d’enregistrements, pas d’une jointure, et la leçon suivante lui est entièrement consacrée. Ce qu’il ne faut surtout pas faire est un merge(on="name") suivi de rien.

Affirmez, ne regardez pas

Le motif que cette leçon enseigne réellement est plus petit que chacun de ses contrôles. Chacun d’eux peut s’écrire comme un affichage qu’on lit une fois, ou comme une assertion qui s’exécute sur chaque export à venir. Seule la seconde survit.

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

Un contrôle exécuté une fois vous renseigne sur le fichier que vous aviez. Un contrôle exécuté à la lecture vous renseigne sur le fichier que vous avez.

L’unité 4 transforme ce motif en une suite de validation. Pour l’instant, placez assert_key en tête de tout script qui joint quoi que ce soit.

La suite

assert_key trouve les élèves enregistrés deux fois sous le même identifiant. Il ne peut pas trouver l’enfant réinscrit sous un nouvel identifiant, le ménage interrogé deux fois par deux enquêteurs, ni le bénéficiaire figurant sur trois listes de distribution avec trois graphies de son nom. Ceux-là ne partagent aucune clé, et la leçon suivante montre comment les retrouver sans fusionner deux personnes qui se ressemblent simplement.