33 Fusion de tables
Il est fréquent d’avoir à gérer des données réparties dans plusieurs tables de données, notamment lorsque l’on a une enquêtes réalisée à différents niveaux (par exemple, un questionnaire ménage et un questionnaire individu) ou des données longitudinales.
On peut distinguer deux types d’actions :
- l’ajout de variables (jointure entre tables)
- l’ajout d’observations (concaténation de tables)
33.1 Jointures avec dplyr
Le jeu de données nycflights13 est un exemple de données réparties en plusieurs tables. Ici on en a trois : les informations sur les vols, celles sur les aéroports et celles sur les compagnies aériennes sont dans trois tables distinctes.
dplyr propose différentes fonctions permettant de travailler avec des données structurées de cette manière.
33.1.1 Clés implicites
Lorsque les données sont réparties dans plusieurs tables différentes, il est essentiel de repérer les identifiants permettant de naviguer d’une table à l’autre. Dans notre exemple, on peut voir que la table flights contient le code de la compagnie aérienne du vol dans la variable carrier :
pos variable label col_type missing values
1 year — int 0
2 month — int 0
3 day — int 0
4 dep_time — int 8255
5 sched_dep_time — int 0
6 dep_delay — dbl 8255
7 arr_time — int 8713
8 sched_arr_time — int 0
9 arr_delay — dbl 9430
10 carrier — chr 0
11 flight — int 0
12 tailnum — chr 2512
13 origin — chr 0
14 dest — chr 0
15 air_time — dbl 9430
16 distance — dbl 0
17 hour — dbl 0
18 minute — dbl 0
19 time_hour — dttm 0 Et que par ailleurs la table airlines contient une information supplémentaire relative à ces compagnies, à savoir le nom complet.
pos variable label col_type missing values
1 carrier — chr 0
2 name — chr 0 Il est donc naturel de vouloir associer les deux, en l’occurrence pour ajouter les noms complets des compagnies à la table flights. Dans ce cas on va faire une jointure : les lignes d’une table seront associées à une autre en se basant non pas sur leur position, mais sur les valeurs d’une ou plusieurs colonnes. Ces colonnes sont appelées des clés.
Pour faire une jointure de ce type, on va utiliser la fonction dplyr::left_join() :
Pour faciliter la lecture, on va afficher seulement certaines colonnes du résultat et les premières lignes de la table :
# A tibble: 10 × 4
month day carrier name
<int> <int> <chr> <chr>
1 1 1 UA United Air Lines Inc.
2 1 1 UA United Air Lines Inc.
3 1 1 AA American Airlines Inc.
4 1 1 B6 JetBlue Airways
5 1 1 DL Delta Air Lines Inc.
6 1 1 UA United Air Lines Inc.
7 1 1 B6 JetBlue Airways
8 1 1 EV ExpressJet Airlines Inc.
9 1 1 B6 JetBlue Airways
10 1 1 AA American Airlines Inc. On voit que la table obtenue est bien la fusion des deux tables d’origine selon les valeurs des deux colonnes clés carrier. On est parti de la table flights, et pour chaque ligne on a ajouté les colonnes de airlines pour lesquelles la valeur de carrier est la même. On a donc bien une nouvelle colonne name dans notre table résultat, avec le nom complet de la compagnie aérienne.
Nous sommes ici dans le cas le plus simple concernant les clés de jointure : les deux clés sont uniques et portent le même nom dans les deux tables. Par défaut, si on ne lui spécifie pas explicitement les clés, dplyr fusionne en utilisant l’ensemble des colonnes communes aux deux tables. On peut d’ailleurs voir dans cet exemple qu’un message a été affiché précisant que la jointure s’est faite sur la variable carrier.
33.1.2 Clés explicites
La table airports, elle, contient des informations supplémentaires sur les aéroports : nom complet, altitude, position géographique, etc. Chaque aéroport est identifié par un code contenu dans la colonne faa.
Si on regarde la table flights, on voit que le code d’identification des aéroports apparaît à deux endroits différents : pour l’aéroport de départ dans la colonne origin, et pour celui d’arrivée dans la colonne dest. On a donc deux clés de jointures possibles, et qui portent un nom différent de la clé de airports.
On va commencer par fusionner les données concernant l’aéroport de départ. Pour simplifier l’affichage des résultats, on va se contenter d’un sous-ensemble des deux tables :
Si on se contente d’un dplyr::left_join() comme à l’étape précédente, on obtient un message d’erreur car aucune colonne commune ne peut être identifiée comme clé de jointure :
Error in `left_join()`:
! `by` must be supplied when `x` and `y` have no common variables.
ℹ Use `cross_join()` to perform a cross-join.On doit donc spécifier explicitement les clés avec l’argument by de dplyr::left_join(). Ici la clé est nommée origin dans la première table, et faa dans la seconde. La syntaxe est donc la suivante :
# A tibble: 10 × 6
month day origin dest alt name
<int> <int> <chr> <chr> <dbl> <chr>
1 1 1 EWR IAH 18 Newark Liberty Intl
2 1 1 LGA IAH 22 La Guardia
3 1 1 JFK MIA 13 John F Kennedy Intl
4 1 1 JFK BQN 13 John F Kennedy Intl
5 1 1 LGA ATL 22 La Guardia
6 1 1 EWR ORD 18 Newark Liberty Intl
7 1 1 EWR FLL 18 Newark Liberty Intl
8 1 1 LGA IAD 22 La Guardia
9 1 1 JFK MCO 13 John F Kennedy Intl
10 1 1 LGA ORD 22 La Guardia On constate que les deux nouvelles colonnes name et alt contiennent bien les données correspondant à l’aéroport de départ.
On va stocker le résultat de cette jointure dans flights_ex :
Supposons qu’on souhaite maintenant fusionner à nouveau les informations de la table airports, mais cette fois pour les aéroports d’arrivée de notre nouvelle table flights_ex. Les deux clés sont donc désormais dest dans la première table, et faa dans la deuxième. La syntaxe est donc la suivante :
# A tibble: 10 × 8
month day origin dest alt.x name.x alt.y name.y
<int> <int> <chr> <chr> <dbl> <chr> <dbl> <chr>
1 1 1 EWR IAH 18 Newark Liberty Intl 97 George Bush Interco…
2 1 1 LGA IAH 22 La Guardia 97 George Bush Interco…
3 1 1 JFK MIA 13 John F Kennedy Intl 8 Miami Intl
4 1 1 JFK BQN 13 John F Kennedy Intl NA <NA>
5 1 1 LGA ATL 22 La Guardia 1026 Hartsfield Jackson …
6 1 1 EWR ORD 18 Newark Liberty Intl 668 Chicago Ohare Intl
7 1 1 EWR FLL 18 Newark Liberty Intl 9 Fort Lauderdale Hol…
8 1 1 LGA IAD 22 La Guardia 313 Washington Dulles I…
9 1 1 JFK MCO 13 John F Kennedy Intl 96 Orlando Intl
10 1 1 LGA ORD 22 La Guardia 668 Chicago Ohare Intl Cela fonctionne, les informations de l’aéroport d’arrivée ont bien été ajoutées, mais on constate que les colonnes ont été renommées. En effet, ici les deux tables fusionnées contenaient toutes les deux des colonnes name et alt. Comme on ne peut pas avoir deux colonnes avec le même nom dans un tableau, dplyr a renommé les colonnes de la première table en name.x et alt.x, et celles de la deuxième en name.y et alt.y.
C’est pratique, mais pas forcément très parlant. On pourrait renommer manuellement les colonnes pour avoir des intitulés plus explicites avec dplyr::rename(), mais on peut aussi utiliser l’argument suffix de dplyr::left_join(), qui permet d’indiquer les suffixes à ajouter aux colonnes. Ainsi, on peut faire :
flights_ex |>
left_join(
airports_ex,
by = c("dest" = "faa"),
suffix = c("_depart", "_arrivee")
) |>
head(10)# A tibble: 10 × 8
month day origin dest alt_depart name_depart alt_arrivee name_arrivee
<int> <int> <chr> <chr> <dbl> <chr> <dbl> <chr>
1 1 1 EWR IAH 18 Newark Liberty … 97 George Bush…
2 1 1 LGA IAH 22 La Guardia 97 George Bush…
3 1 1 JFK MIA 13 John F Kennedy … 8 Miami Intl
4 1 1 JFK BQN 13 John F Kennedy … NA <NA>
5 1 1 LGA ATL 22 La Guardia 1026 Hartsfield …
6 1 1 EWR ORD 18 Newark Liberty … 668 Chicago Oha…
7 1 1 EWR FLL 18 Newark Liberty … 9 Fort Lauder…
8 1 1 LGA IAD 22 La Guardia 313 Washington …
9 1 1 JFK MCO 13 John F Kennedy … 96 Orlando Intl
10 1 1 LGA ORD 22 La Guardia 668 Chicago Oha…On obtient ainsi directement des noms de colonnes nettement plus clairs.
33.1.3 Types de jointures
Jusqu’à présent nous avons utilisé la fonction dplyr::left_join(), mais il existe plusieurs types de jointures.
Partons de deux tables d’exemple, personnes et voitures :
personnes <- tibble(
nom = c("Sylvie", "Sylvie", "Monique", "Gunter", "Rayan", "Rayan"),
voiture = c("Twingo", "Ferrari", "Scenic", "Lada", "Twingo", "Clio")
)
personnes# A tibble: 6 × 2
nom voiture
<chr> <chr>
1 Sylvie Twingo
2 Sylvie Ferrari
3 Monique Scenic
4 Gunter Lada
5 Rayan Twingo
6 Rayan Clio voitures <- tibble(
voiture = c("Twingo", "Ferrari", "Clio", "Lada", "208"),
vitesse = c("140", "280", "160", "85", "160")
)
voitures# A tibble: 5 × 2
voiture vitesse
<chr> <chr>
1 Twingo 140
2 Ferrari 280
3 Clio 160
4 Lada 85
5 208 160
33.1.3.1 left_join()
Si on fait un dplyr::left_join() de voitures sur personnes :
# A tibble: 6 × 3
nom voiture vitesse
<chr> <chr> <chr>
1 Sylvie Twingo 140
2 Sylvie Ferrari 280
3 Monique Scenic <NA>
4 Gunter Lada 85
5 Rayan Twingo 140
6 Rayan Clio 160 On voit que chaque ligne de personnes est bien présente, et qu’on lui a ajouté une ligne de voitures correspondante si elle existe. Dans le cas du Scenic, il n’y a avait pas de ligne dans voitures, donc vitesse a été peuplée avec la valeur manquante NA. Dans le cas de la 208, présente dans voitures mais pas dans personnes, la ligne n’apparaît pas.
La clé de fusion étant unique dans la table de droite
, le nombre de lignes de la table de gauche
est donc bien préservée.
Si on fait un dplyr::left_join() cette fois de personnes sur voitures, c’est l’inverse :
# A tibble: 6 × 3
voiture vitesse nom
<chr> <chr> <chr>
1 Twingo 140 Sylvie
2 Twingo 140 Rayan
3 Ferrari 280 Sylvie
4 Clio 160 Rayan
5 Lada 85 Gunter
6 208 160 <NA> La ligne 208 est bien là avec la variable nom remplie avec une valeur manquante NA. Par contre Monique est absente.
On remarquera que la ligne Twingo, présente deux fois dans personnes, a été dupliquée pour être associée aux deux lignes de données de Sylvie et Rayan. Autrement dit, si la clé de fusion n’est pas unique dans la table de droite
, certaines de lignes de la table de gauche
seront dupliquées.
En résumé, quand on fait un left_join(x, y), toutes les lignes de x sont présentes, et dupliquées si nécessaire quand elles apparaissent plusieurs fois dans y. Les lignes de y non présentes dans x disparaissent. Les lignes de x non présentes dans y se voient attribuer des valeurs manquantes NA pour les nouvelles colonnes.
Intuitivement, on pourrait considérer que left_join(x, y) signifie ramener l’information de la table
.y sur la table x
En général, dplyr::left_join() sera le type de jointures le plus fréquemment utilisé.
33.1.3.2 right_join()
La jointure dplyr::right_join() est l’exacte symétrique de dplyr::left_join(), c’est-à dire que x |> right_join(y) est équivalent1 à y |> left_join(x) :
1 À l’exception de l’ordre des variables dans le tableau final.
# A tibble: 6 × 3
nom voiture vitesse
<chr> <chr> <chr>
1 Sylvie Twingo 140
2 Sylvie Ferrari 280
3 Gunter Lada 85
4 Rayan Twingo 140
5 Rayan Clio 160
6 <NA> 208 160 # A tibble: 6 × 3
voiture vitesse nom
<chr> <chr> <chr>
1 Twingo 140 Sylvie
2 Twingo 140 Rayan
3 Ferrari 280 Sylvie
4 Clio 160 Rayan
5 Lada 85 Gunter
6 208 160 <NA>
33.1.3.3 inner_join()
Dans le cas de dplyr::inner_join(), seules les lignes présentes à la fois dans x et y sont présentes (et si nécessaire dupliquées) dans la table résultat :
# A tibble: 5 × 3
nom voiture vitesse
<chr> <chr> <chr>
1 Sylvie Twingo 140
2 Sylvie Ferrari 280
3 Gunter Lada 85
4 Rayan Twingo 140
5 Rayan Clio 160 Ici la ligne 208 est absente, ainsi que la ligne Monique, qui dans le cas d’un dplyr::left_join() avait été conservée et s’était vue attribuer NA à vitesse.
33.1.3.4 full_join()
Dans le cas de dplyr::full_join(), toutes les lignes de x et toutes les lignes de y sont conservées (avec des NA ajoutés si nécessaire) même si elles sont absentes de l’autre table :
33.1.3.5 semi_join() et anti_join()
dplyr::semi_join() et dplyr::anti_join() sont des jointures filtrantes, c’est-à-dire qu’elles sélectionnent les lignes de x sans ajouter les colonnes de y.
Ainsi, dplyr::semi_join() ne conservera que les lignes de x pour lesquelles une ligne de y existe également, et supprimera les autres. Dans notre exemple, la ligne Monique est donc supprimée :
# A tibble: 5 × 2
nom voiture
<chr> <chr>
1 Sylvie Twingo
2 Sylvie Ferrari
3 Gunter Lada
4 Rayan Twingo
5 Rayan Clio Un dplyr::anti_join() fait l’inverse, il ne conserve que les lignes de x absentes de y. Dans notre exemple, on ne garde donc que la ligne Monique :
33.2 Jointures avec merge()
La fonction merge() est la fonction de R base pour fusionner des tables entre elles.
Par défaut, elle réalise un inner join, c’est-à-dire qu’elle ne garde que les observations dont la clé est retrouvée dans les deux tableaux fusionnés
voiture nom vitesse
1 Clio Rayan 160
2 Ferrari Sylvie 280
3 Lada Gunter 85
4 Twingo Sylvie 140
5 Twingo Rayan 140Les paramètres all.x et all.y permettent de réaliser fusions à gauche, à droite ou complète. L’équivalent de dplyr::left_join() sera obtenu avec all.x = TRUE, celui de dplyr::right_join() avec all.y = TRUE et celui de dplyr::full_join() avec all.x = TRUE, all.y = TRUE.
voiture nom vitesse
1 Clio Rayan 160
2 Ferrari Sylvie 280
3 Lada Gunter 85
4 Scenic Monique <NA>
5 Twingo Sylvie 140
6 Twingo Rayan 140Joining with `by = join_by(voiture)`# A tibble: 6 × 3
nom voiture vitesse
<chr> <chr> <chr>
1 Sylvie Twingo 140
2 Sylvie Ferrari 280
3 Monique Scenic <NA>
4 Gunter Lada 85
5 Rayan Twingo 140
6 Rayan Clio 160
33.3 Ajouter des observations avec bind_rows()
La fonction base::rbind(), fournie nativement avec R pour ajouter des observations à un tableau, doit être évitée car elle générera des résultats non pertinents si les tableaux que l’on concatènent n’ont pas exactement les mêmes colonnes dans le même ordre.
La fonction dplyr::bind_rows() de dplyr permet d’ajouter des lignes à une table à partir d’une ou plusieurs autres tables.
L’exemple suivant (certes très artificiel) montre l’utilisation de dplyr::bind_rows(). On commence par créer trois tableaux t1, t2 et t3 :
# A tibble: 2 × 4
faa name lat lon
<chr> <chr> <dbl> <dbl>
1 04G Lansdowne Airport 41.1 -80.6
2 06A Moton Field Municipal Airport 32.5 -85.7# A tibble: 2 × 4
name faa lon lat
<chr> <chr> <dbl> <dbl>
1 Jekyll Island Airport 09J -81.4 31.1
2 Elizabethton Municipal Airport 0A9 -82.2 36.4# A tibble: 2 × 2
faa name
<chr> <chr>
1 ADW Andrews Afb
2 AET Allakaket AirportOn concatène ensuite les trois tables avec dplyr::bind_rows() :
# A tibble: 6 × 4
faa name lat lon
<chr> <chr> <dbl> <dbl>
1 04G Lansdowne Airport 41.1 -80.6
2 06A Moton Field Municipal Airport 32.5 -85.7
3 09J Jekyll Island Airport 31.1 -81.4
4 0A9 Elizabethton Municipal Airport 36.4 -82.2
5 ADW Andrews Afb NA NA
6 AET Allakaket Airport NA NA On remarquera que si des colonnes sont manquantes pour certaines tables, comme les colonnes lat et lon de t3, des valeurs manquantes NA sont automatiquement insérées.
De plus, peu importe l’ordre des variables entre les différentes tables, dplyr::bind_rows() les ré-associera en considérant que deux colonnes ayant le même nom dans deux tableaux correspondent à la même variable.
Il peut être utile, quand on concatène des lignes, de garder une trace du tableau d’origine de chacune des lignes dans le tableau final. C’est possible grâce à l’argument .id de dplyr::bind_rows(). On passe à cet argument le nom d’une colonne qui contiendra l’indicateur d’origine des lignes :
# A tibble: 6 × 5
source faa name lat lon
<chr> <chr> <chr> <dbl> <dbl>
1 1 04G Lansdowne Airport 41.1 -80.6
2 1 06A Moton Field Municipal Airport 32.5 -85.7
3 2 09J Jekyll Island Airport 31.1 -81.4
4 2 0A9 Elizabethton Municipal Airport 36.4 -82.2
5 3 ADW Andrews Afb NA NA
6 3 AET Allakaket Airport NA NA Par défaut la colonne .id ne contient qu’un nombre, différent pour chaque tableau. On peut lui spécifier des valeurs plus explicites en “nommant” les tables dans dplyr::bind_rows() de la manière suivante :
# A tibble: 6 × 5
source faa name lat lon
<chr> <chr> <chr> <dbl> <dbl>
1 table1 04G Lansdowne Airport 41.1 -80.6
2 table1 06A Moton Field Municipal Airport 32.5 -85.7
3 table2 09J Jekyll Island Airport 31.1 -81.4
4 table2 0A9 Elizabethton Municipal Airport 36.4 -82.2
5 table3 ADW Andrews Afb NA NA
6 table3 AET Allakaket Airport NA NA Une alternative à dplyr::bind_rows() est la fonction plyr::rbind.fill() de l’extension plyr qui fonctionne de manière similaire.