Retour à la leçonLeçon 1 sur 8Des jointures démontrables

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

Le même diaporama que les téléchargements, rendu sous forme de page. Lancez le diaporama pour le présenter en plein écran — les flèches ou un clic avancent d'une diapositive, Échap quitte.

Diapositives · PDF Diapositives · PowerPoint

Diapositive 1 / 24
Ce que couvre cette leçon
- Choisissez la jointure d'après la phrase que vous allez écrire
- L'arithmétique, une fois, à la main
- Le montage
- Jointure à gauche — garder la table de gauche, y accrocher des colonnes
- Jointure interne — et les lignes qu'elle emporte
- Jointure complète — rien n'est perdu, et vous avez désormais deux sortes de vide
- Anti-jointure — le contrôle, pas la jointure
- Joindre sur la mauvaise colonne
- La suite
Notes du présentateur
Interne, à gauche, complète et anti — choisies d'après ce qui doit être vrai du résultat plutôt que par habitude. Plus l'arithmétique des lignes qui explique tous les gonflements que vous rencontrerez.

Diapositive 2 / 24

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

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

Notes du présentateur

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

Diapositive 3 / 24
L'arithmétique, une fois, à la main
Occurrences à gauche Occurrences à droite Lignes émises
1 1 1
60 1 60
60 2 120
50 3000 150 000
Notes du présentateur
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.
Diapositive 4 / 24
L'arithmétique, une fois, à la main
- Nombre de lignes préservé seulement si le côté droit est unique sur la clé. C'est la phrase sur laquelle repose…
- 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 à…
Notes du présentateur
Trois conséquences découlent directement de ce tableau. 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.

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

Diapositive 5 / 24

Le montage — En Python

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

Notes du présentateur

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.

Diapositive 6 / 24

Le montage — En R

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)

Diapositive 7 / 24
Le montage — En Python
```
roster = roster.drop_duplicates(subset=["student_id"], keep="first")
```
Notes du présentateur
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.

Diapositive 8 / 24

Le montage — En R

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

Diapositive 9 / 24
Le montage
- 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…
Notes du présentateur
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.

Diapositive 10 / 24

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

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

Diapositive 11 / 24
Jointure à gauche — garder la table de gauche, y accrocher des colonnes — En R
```
joined <- attendance |>
  left_join(roster, by = "student_id", relationship = "many-to-one")

cat(nrow(attendance), "->", nrow(joined), "\n")
```
Notes du présentateur
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.

Diapositive 12 / 24

Jointure interne — et les lignes qu'elle emporte — En Python

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

Diapositive 13 / 24

Jointure interne — et les lignes qu'elle emporte — En R

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

Diapositive 14 / 24
Jointure interne — et les lignes qu'elle emporte
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.
Notes du présentateur
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.
Diapositive 15 / 24
Jointure complète — rien n'est perdu, et vous avez désormais deux sortes de vide — En Python
```
full = attendance.merge(roster, on="student_id", how="outer", indicator=True)
print(full["_merge"].value_counts())
```
Diapositive 16 / 24
Jointure complète — rien n'est perdu, et vous avez désormais deux sortes de vide — En R
```
full <- attendance |> full_join(roster, by = "student_id")
```
Diapositive 17 / 24
Jointure complète — rien n'est perdu, et vous avez désormais deux sortes de vide — En R
```
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"
    )
  )
```
Notes du présentateur
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. 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.

Diapositive 18 / 24

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

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

Notes du présentateur

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.

Diapositive 19 / 24

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

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

Diapositive 20 / 24
Anti-jointure — le contrôle, pas la jointure
- 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…
- Des lignes de liste sans aucune présence — des enfants inscrits jamais marqués, ni dans un sens ni dans l'autre.…
Notes du présentateur
Les deux sens, à chaque fois, et ils veulent dire des choses opposées. Les deux valent zéro ici. Écrivez le contrôle quand même, car au trimestre suivant ce ne sera plus le cas.
Diapositive 21 / 24
Joindre sur la mauvaise colonne — En Python
```
wrong = attendance.merge(roster, on="school_id", how="left")
print(f"{len(attendance):,} -> {len(wrong):,}")
```
Notes du présentateur
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.
Diapositive 22 / 24
Joindre sur la mauvaise colonne — En R
```
wrong <- attendance |> left_join(roster, by = "school_id")
format(nrow(wrong), big.mark = ",")
```
Notes du présentateur
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.
Diapositive 23 / 24
La suite
- Vous disposez de quatre jointures et de l'arithmétique qui les gouverne.
Notes du présentateur
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.
Diapositive 24 / 24
La suite
Lire la leçon complète, avec le code exécutable Retour à la leçon

Ce que couvre cette leçon

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

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

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

Le montage — En Python

Le montage — En R

Le montage — En Python

Le montage — En R

Le montage

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

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

Jointure interne — et les lignes qu'elle emporte — En Python

Jointure interne — et les lignes qu'elle emporte — En R

Jointure interne — et les lignes qu'elle emporte

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

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

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

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

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

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

Joindre sur la mauvaise colonne — En Python

Joindre sur la mauvaise colonne — En R

La suite

La suite