Quatre jointures, et la question à laquelle chacune répond

Choisissez la jointure d’après la phrase que vous allez écrire

La plupart des gens choisissent une jointure par habitude — la jointure à gauche, parce que c’est celle qui marche d’ordinaire. C’est l’inverse qu’il faut faire. La jointure est une affirmation sur les lignes qui ont leur place dans le résultat, et cette affirmation vient de la phrase que vous comptez publier.

La phrase que vous écrirez	La jointure
« La présence de chaque élève inscrit »	à gauche, registre à gauche
« La présence des élèves ayant à la fois une trace et une ligne de liste »	interne
« Tout des deux côtés, pour ne rien perdre »	complète
« Les élèves marqués présents qui ne sont pas sur la liste »	anti

Lisez-les dans l’autre sens et chaque jointure appelle une défaillance. Une jointure interne écarte en silence les lignes non appariées, et ces lignes sont fréquemment le constat. Une jointure à gauche multiplie en silence lorsque le côté droit n’est pas unique. Une jointure complète produit une table où une valeur manquante peut vouloir dire deux choses différentes. Et une anti-jointure ne renvoie rien lorsque tout s’est apparié, ce que l’on lit comme « le contrôle est passé » sans vérifier qu’il a bien tourné.

L’arithmétique, une fois, à la main

Toutes les surprises de ce cours découlent d’une seule règle. Une jointure émet une ligne pour chaque couple de lignes partageant la clé.

Si une valeur de clé apparaît m fois à gauche et n fois à droite, elle contribue m × n lignes.

Occurrences à gauche	Occurrences à droite	Lignes émises
1	1	1
60	1	60
60	2	120
50	3000	150 000

Trois conséquences découlent directement de ce tableau.

• Nombre de lignes préservé seulement si le côté droit est unique sur la clé. C’est la phrase sur laquelle repose toute la leçon.
• Nombre de lignes multiplié dans le cas contraire — en silence, sans erreur, par un facteur que personne n’a choisi.
• Nombre de lignes réduit uniquement par une jointure interne, ou par une clé qui ne s’apparie pas. Une jointure à gauche ne retire jamais de ligne, donc une jointure à gauche qui rétrécit signale une clé qui a changé sous vos pieds.

La troisième ligne du tableau est un défaut réel du cours précédent — deux élèves présents deux fois sur la liste, soixante jours de classe chacun, et une jointure à gauche qui ajoute 120 lignes à une table de 70 245. La quatrième est ce qui se produit quand on joint sur la mauvaise colonne, ce que nous ferons délibérément dans un instant.

Le montage

Deux fichiers. Une liste de 1 202 lignes couvrant 1 200 élèves de 24 écoles, et 70 245 marques de présence journalière.

import pandas as pd

roster = pd.read_csv("school-roster-2024.v1.csv")
attendance = pd.read_csv("school-attendance-2024.v1.csv", parse_dates=["attendance_date"])

print(len(roster), roster["student_id"].nunique())
print(len(attendance))

library(dplyr)
library(readr)

roster     <- read_csv("school-roster-2024.v1.csv")
attendance <- read_csv("school-attendance-2024.v1.csv")

c(rows = nrow(roster), students = n_distinct(roster$student_id))
nrow(attendance)

1 202 lignes et 1 200 élèves. Réglez cela avant de joindre — le cours précédent a montré pourquoi, et la suite de cette leçon suppose que c’est fait.

roster = roster.drop_duplicates(subset=["student_id"], keep="first")

roster <- roster |> distinct(student_id, .keep_all = TRUE)

Garder la première ligne est une décision, pas un réglage par défaut. Pour ces deux élèves, c’est la mauvaise — l’une des lignes enregistre l’école quittée et l’autre l’école rejointe, et « la première » retient celle que l’export a triée en tête. Dans la vraie vie, cela remonte à la personne qui tient le registre. Ici c’est un provisoire pour que les jointures ci-dessous aient de quoi travailler, et cela a sa place dans le journal de nettoyage.

Jointure à gauche — garder la table de gauche, y accrocher des colonnes

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

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

cat(nrow(attendance), "->", nrow(joined), "\n")

70 245 vers 70 245. C’est ce qu’une jointure à gauche est censée faire, et l’argument validate / relationship est ce qui en fait une garantie plutôt qu’un espoir. Sans lui, le même appel sur la liste non dédoublonnée renvoie 70 365 et ne vous dit rien.

Jointure interne — et les lignes qu’elle emporte

inner = attendance.merge(roster, on="student_id", how="inner")
print(len(inner), "rows;", len(attendance) - len(inner), "attendance rows dropped")

inner <- attendance |> inner_join(roster, by = "student_id")
cat(nrow(inner), "rows;", nrow(attendance) - nrow(inner), "dropped\n")

Ici rien n’est écarté, car chaque ligne de présence a une ligne de liste. C’est inhabituel et cela mérite d’être dit à voix haute quand cela arrive.

Quand ce n’est pas zéro, le nombre importe plus que la jointure. Une jointure interne qui écarte 4 % d’une liste de distribution écarte quatre pour cent des bénéficiaires de quelqu’un, et le rapport dira « 12 000 personnes atteintes » sans la moindre note de bas de page, parce que les lignes qui auraient soulevé la question sont justement celles qui sont parties.

Utilisez une jointure interne quand vous avez déjà regardé ce qu’elle retire. L’utiliser pour les retirer est la manière dont les lignes gênantes sont évacuées sans décision.

Jointure complète — rien n’est perdu, et vous avez désormais deux sortes de vide

full = attendance.merge(roster, on="student_id", how="outer", indicator=True)
print(full["_merge"].value_counts())

full <- attendance |> full_join(roster, by = "student_id")

Le indicator=True de pandas ajoute une colonne _merge valant both, left_only ou right_only, et c’est l’argument le plus utile de cette leçon. dplyr n’a pas d’équivalent, alors ajoutez-en un.

full <- attendance |>
  mutate(in_attendance = TRUE) |>
  full_join(mutate(roster, in_roster = TRUE), by = "student_id") |>
  mutate(
    side = case_when(
      in_attendance & in_roster ~ "both",
      in_attendance             ~ "attendance only",
      TRUE                      ~ "roster only"
    )
  )

La raison de s’en donner la peine — après une jointure complète, un grade vide signifie soit « cet élève n’a pas de ligne de liste », soit « cet élève a une ligne de liste sans niveau renseigné ». Ce sont deux constats totalement différents et la jointure les a rendus identiques. La colonne de provenance les maintient séparés.

Anti-jointure — le contrôle, pas la jointure

Une anti-jointure renvoie les lignes d’un côté sans partenaire de l’autre. Elle est rarement l’analyse et presque toujours le contrôle.

in_roster = set(roster["student_id"])
orphans = attendance[~attendance["student_id"].isin(in_roster)]
never_seen = roster[~roster["student_id"].isin(set(attendance["student_id"]))]

print(len(orphans), "attendance rows with no roster entry")
print(len(never_seen), "enrolled students with no attendance row at all")

orphans     <- attendance |> anti_join(roster, by = "student_id")
never_seen  <- roster |> anti_join(attendance, by = "student_id")

c(orphans = nrow(orphans), never_seen = nrow(never_seen))

Les deux sens, à chaque fois, et ils veulent dire des choses opposées.

• De la présence sans ligne de liste — on enregistre quelqu’un qui n’est pas inscrit. Un problème de données, ou une liste d’inscription périmée.
• Des lignes de liste sans aucune présence — des enfants inscrits jamais marqués, ni dans un sens ni dans l’autre. Dans un programme d’éducation ce n’est pas un défaut, c’est le constat : ce sont les enfants qui ne sont jamais venus.

Les deux valent zéro ici. Écrivez le contrôle quand même, car au trimestre suivant ce ne sera plus le cas.

Joindre sur la mauvaise colonne

Faites-le une fois, délibérément, pour en reconnaître la silhouette le jour où cela arrivera par accident. Les deux fichiers portent school_id après la première jointure, et joindre sur lui plutôt que sur student_id est une erreur d’un seul caractère.

wrong = attendance.merge(roster, on="school_id", how="left")
print(f"{len(attendance):,} -> {len(wrong):,}")

wrong <- attendance |> left_join(roster, by = "school_id")
format(nrow(wrong), big.mark = ",")

70 245 devient 3 567 105. Chaque ligne de présence s’est appariée à tous les élèves de la même école — une cinquantaine — et le taux de présence calculé sur cette table tourne toujours autour de 88 %, parce que multiplier chaque ligne par cinquante laisse la proportion intacte.

C’est là tout le propos. Le taux survit, les effectifs non. Tout ce qui est rapporté en nombre d’enfants est multiplié par cinquante, et un taux qui a l’air juste est exactement la raison pour laquelle personne ne vérifie l’effectif.

La suite

Vous disposez de quatre jointures et de l’arithmétique qui les gouverne. La leçon suivante en fait une habitude qui tourne sans y penser — un tableau de rapprochement produit par chaque jointure, avec les lignes appariées, les deux comptes d’anti-jointure et une assertion qui échoue quand le nombre de lignes bouge.