Fusion de tables
Ce chapitre est évoqué dans le webin-R #04 (manipuler les données avec dplyr) sur YouTube.
Lorsqu’on traite de grosses enquêtes, notamment les enquêtes de l’INSEE, on a souvent à gérer des données réparties dans plusieurs tables, soit du fait de la construction du questionnaire, soit du fait de contraintes techniques (fichiers dbf ou Excel limités à 256 colonnes, par exemple).
Cela arrive également lorsque l’on traitre de données d’une enquêtes réalisée à différents niveaux (par exemple, un questionnaire ménage et un questionnaire individu).
On peut distinguer deux situations :
- l’ajout de variables (jointure entre tables)
- l’ajout d’observations (concaténation de tables)
La fonction merge et les jointures
Une opération relativement courante consiste à fusionner plusieurs tables pour regrouper tout ou partie des données dans un unique tableau.
Nous allons simuler artificiellement une telle situation en créant deux tables à partir de l’extrait de l’enquête Histoire de vie :
library(questionr)
data(hdv2003)
d <- hdv2003
dim(d)[1] 2000 20d1 <- subset(d, select = c("id", "age", "sexe"))
dim(d1)[1] 2000 3d2 <- subset(d, select = c("id", "clso"))
dim(d2)[1] 2000 2On a donc deux tableaux de données, d1 et d2, comportant chacun 2000 lignes et respectivement 3 et 2 colonnes. Comment les rassembler pour n’en former qu’un ?
Intuitivement, cela paraît simple. Il suffit de « coller » d2 à la droite de d1, comme dans l’exemple suivant.
|
|
|
|
|
Cela semble fonctionner. La fonction qui permet d’effectuer cette opération sous R s’appelle cbind, elle « colle » des tableaux côte à côte en regroupant leurs colonnes.
head(cbind(d1, d2))À part le fait qu’on a une colonne id en double, le résultat semble satisfaisant. À première vue seulement. Imaginons maintenant que nous avons travaillé sur d1 et d2, et que nous avons ordonné les lignes de d1 selon l’âge des enquêtés :
d1 <- d1[order(d1$age), ]Répétons l’opération de collage :
head(cbind(d1, d2))Que constate-t-on ? La présence de la variable id en double nous permet de voir que les identifiants ne coïncident plus ! En regroupant nos colonnes nous avons donc attribué à des individus les réponses d’autres individus.
La commande cbind ne peut en effet fonctionner que si les deux tableaux ont exactement le même nombre de lignes, et dans le même ordre, ce qui n’est pas le cas ici.
Pour éviter toute erreur, il est préférable de ne jamais utiliser cbind ou son équivalent bind_cols fournis par dplyr.
On aura recours à la jointure entre tables présentée ci-dessous.
On va donc être obligé de procéder à une fusion des deux tableaux, qui va permettre de rendre à chaque ligne ce qui lui appartient. Pour cela nous avons besoin d’un identifiant qui permet d’identifier chaque ligne de manière unique et qui doit être présent dans tous les tableaux. Dans notre cas, c’est plutôt rapide, il s’agit de la variable id.
Une fois l’identifiant identifié1, on peut utiliser la commande merge. Celle-ci va fusionner les deux tableaux en supprimant les colonnes en double et en regroupant les lignes selon leurs identifiants :
d.complet <- merge(d1, d2, by = "id")
head(d.complet)Ici l’utilisation de la fonction merge est plutôt simple car nous sommes dans le cas de figure idéal : les lignes correspondent parfaitement et l’identifiant est clairement identifié. Parfois les choses peuvent être un peu plus compliquées :
- parfois les identifiants n’ont pas le même nom dans les deux tableaux. On peut alors les spécifier par les options
by.xetby.y; - parfois les deux tableaux comportent des colonnes (hors identifiants) ayant le même nom.
mergeconserve dans ce cas ces deux colonnes mais les renomme en les suffixant par .x pour celles provenant du premier tableau et .y pour celles du second ; - parfois on n’a pas d’identifiant unique préétabli, mais on en construit un à partir de plusieurs variables. On peut alors donner un vecteur en paramètres de l’option
by, par exempleby=c("nom","prenom","date.naissance").
Une subtilité supplémentaire intervient lorsque les deux tableaux fusionnés n’ont pas exactement les mêmes lignes. Par défaut, merge ne conserve que les lignes présentes dans les deux tableaux :
|
|
|
|
|
On peut cependant modifier ce comportement avec les options all.x et all.y.
Ainsi, all.x = TRUE indique de conserver toutes les lignes du premier tableau. Dans ce cas merge donne une valeur NA pour ces lignes aux colonnes provenant du second tableau. Ce qui donnerait :
|
|
|
|
|
L’option all.y = TRUE fait la même chose en conservant toutes les lignes du second tableau.
|
|
|
|
|
Enfin, on peut décider de conserver toutes les lignes des deux tableaux en utilisant à la fois all.x = TRUE et all.y = TRUE, ce qui donne :
|
|
|
|
|
Parfois, l’un des identifiants est présent à plusieurs reprises dans l’un des tableaux (par exemple lorsque l’une des tables est un ensemble de ménages et que l’autre décrit l’ensemble des individus de ces ménages). Dans ce cas les lignes de l’autre table sont dupliquées autant de fois que nécessaires :
|
|
|
|
|
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.
library(tidyverse)
library(nycflights13)
data(flights)
data(airports)
data(airlines)Clés implicites
Très souvent, les données relatives à une analyse sont réparties dans plusieurs tables différentes. Dans notre exemple, on peut voir que la table flights contient seulement le code de la compagnie aérienne du vol dans la variable carrier :
flights %>% select(carrier)Et que par ailleurs la table airlines contient une information supplémentaire relative à ces compagnies, à savoir le nom complet.
airlinesIl 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 left_join :
left_join(flights, airlines)Pour faciliter la lecture, on va afficher seulement certaines colonnes du résultat :
left_join(flights, airlines) %>%
select(month, day, carrier, name)Joining, by = "carrier"On voit que la table résultat 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.
À noter qu’on peut tout à fait utiliser le pipe avec les fonctions de jointure :
flights %>% left_join(airlines).
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.
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 :
flights_ex <- flights %>% select(month, day, origin, dest)
airports_ex <- airports %>% select(faa, alt, name)Si on se contente d’un 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 :
left_join(flights_ex, airports_ex)Error in `standardise_join_by()`:
! `by` must be supplied when `x` and `y` have no common variables.
i use by = character()` to perform a cross-join.On doit donc spécifier explicitement les clés avec l’argument by de 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 :
left_join(flights_ex, airports_ex, by = c("origin" = "faa"))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 :
flights_ex <- flights_ex %>%
left_join(airports_ex, by = c("origin" = "faa"))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 :
left_join(flights_ex, airports_ex, by = c("dest" = "faa"))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 rename, mais on peut aussi utiliser l’argument suffix de left_join, qui permet d’indiquer les suffixes à ajouter aux colonnes. Ainsi, on peut faire :
left_join(flights_ex, airports_ex,
by = c("dest" = "faa"),
suffix = c("_depart", "_arrivee")
)On obtient ainsi directement des noms de colonnes nettement plus clairs.
Types de jointures
Jusqu’à présent nous avons utilisé la fonction 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")
)| nom | voiture |
|---|---|
| Sylvie | Twingo |
| Sylvie | Ferrari |
| Monique | Scenic |
| Gunter | Lada |
| Rayan | Twingo |
| Rayan | Clio |
voitures <- tibble(
voiture = c("Twingo", "Ferrari", "Clio", "Lada", "208"),
vitesse = c("140", "280", "160", "85", "160")
)| voiture | vitesse |
|---|---|
| Twingo | 140 |
| Ferrari | 280 |
| Clio | 160 |
| Lada | 85 |
| 208 | 160 |
left_join
Si on fait un left_join de voitures sur personnes :
left_join(personnes, voitures)Joining, by = "voiture"| nom | voiture | vitesse |
|---|---|---|
| Sylvie | Twingo | 140 |
| Sylvie | Ferrari | 280 |
| Monique | Scenic | NA |
| Gunter | Lada | 85 |
| Rayan | Twingo | 140 |
| 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é mise à NA. Dans le cas de 208, présente dans voitures mais pas dans personnes, la ligne n’apparaît pas.
Si on fait un left_join cette fois de personnes sur voitures, c’est l’inverse :
left_join(voitures, personnes)Joining, by = "voiture"| voiture | vitesse | nom |
|---|---|---|
| Twingo | 140 | Sylvie |
| Twingo | 140 | Rayan |
| Ferrari | 280 | Sylvie |
| Clio | 160 | Rayan |
| Lada | 85 | Gunter |
| 208 | 160 | NA |
La ligne 208 est là, mais nom est à NA. Par contre Monique est absente. Et 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.
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 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, left_join sera le type de jointures le plus fréquemment utilisé.
right_join
La jointure right_join est l’exacte symétrique de left_join, c’est-à dire que right_join(x, y) est équivalent à left_join(x,y) :
right_join(personnes, voitures)Joining, by = "voiture"| nom | voiture | vitesse |
|---|---|---|
| Sylvie | Twingo | 140 |
| Sylvie | Ferrari | 280 |
| Gunter | Lada | 85 |
| Rayan | Twingo | 140 |
| Rayan | Clio | 160 |
| NA | 208 | 160 |
inner_join
Dans le cas de 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 :
inner_join(personnes, voitures)Joining, by = "voiture"| nom | voiture | vitesse |
|---|---|---|
| Sylvie | Twingo | 140 |
| Sylvie | Ferrari | 280 |
| Gunter | Lada | 85 |
| Rayan | Twingo | 140 |
| Rayan | Clio | 160 |
Ici la ligne 208 est absente, ainsi que la ligne Monique, qui dans le cas d’un left_join avait été conservée et s’était vue attribuer une vitesse à NA.
full_join
Dans le cas de 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 :
full_join(personnes, voitures)Joining, by = "voiture"| nom | voiture | vitesse |
|---|---|---|
| Sylvie | Twingo | 140 |
| Sylvie | Ferrari | 280 |
| Monique | Scenic | NA |
| Gunter | Lada | 85 |
| Rayan | Twingo | 140 |
| Rayan | Clio | 160 |
| NA | 208 | 160 |
semi_join et anti_join
semi_join et anti_join sont des jointures filtrantes, c’est-à-dire qu’elles sélectionnent les lignes de x sans ajouter les colonnes de y.
Ainsi, 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 :
semi_join(personnes, voitures)Joining, by = "voiture"| nom | voiture |
|---|---|
| Sylvie | Twingo |
| Sylvie | Ferrari |
| Gunter | Lada |
| Rayan | Twingo |
| Rayan | Clio |
Un 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 :
anti_join(personnes, voitures)Joining, by = "voiture"| nom | voiture |
|---|---|
| Monique | Scenic |
Jointures avec data.table
data.table fournit une fonction merge beaucoup plus rapide que celle standard de R mais fonctionnant de manière identique.
Ajouter des observations
La fonction 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 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 bind_rows. On commence par créer trois tableaux t1, t2 et t3 :
t1 <- airports %>%
select(faa, name, lat, lon) %>%
slice(1:2)
t1t2 <- airports %>%
select(name, faa, lon, lat) %>%
slice(5:6)
t2t3 <- airports %>%
select(faa, name) %>%
slice(100:101)
t3On concaténe ensuite les trois tables avec bind_rows :
bind_rows(t1, t2, t3)On remarquera que si des colonnes sont manquantes pour certaines tables, comme les colonnes lat et lon de t3, des NA sont automatiquement insérées.
De plus, peu importe l’ordre des variables entre les différentes tables, 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 bind_rows. On passe à cet argument le nom d’une colonne qui contiendra l’indicateur d’origine des lignes :
bind_rows(t1, t2, t3, .id = "source")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 bind_rows de la manière suivante :
bind_rows(table1 = t1, table2 = t2, table3 = t3, .id = "source")Une alternative à bind_rows est la fonction rbind.fill de l’extension plyr qui fonctionne de manière similaire.
Si vous me passez l’expression…↩︎