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

La fonction merge

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   20
d1 <- subset(d, select = c("id", "age", "sexe"))
dim(d1)
[1] 2000    3
d2 <- subset(d, select = c("id", "clso"))
dim(d2)
[1] 2000    2

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

id v1 v2
1 H 12
2 H 17
3 F 41
4 F 9
+
id v3
1 rouge
2 bleu
3 bleu
4 rouge
=
id v1 v2 v3
1 H 12 rouge
2 H 17 bleu
3 F 41 bleu
4 F 9 rouge

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

head(cbind(d1, d2))
  id age  sexe id clso
1  1  28 Femme  1  Oui
2  2  23 Femme  2  Oui
3  3  59 Homme  3  Non
4  4  34 Homme  4  Non
5  5  71 Femme  5  Oui
6  6  35 Femme  6  Non

À 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))
     id age  sexe id clso
162 162  18 Homme  1  Oui
215 215  18 Homme  2  Oui
346 346  18 Femme  3  Non
377 377  18 Homme  4  Non
511 511  18 Homme  5  Oui
646 646  18 Homme  6  Non

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.

On va donc être obligé de pocé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é2, 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)
  id age  sexe clso
1  1  28 Femme  Oui
2  2  23 Femme  Oui
3  3  59 Homme  Non
4  4  34 Homme  Non
5  5  71 Femme  Oui
6  6  35 Femme  Non

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.x et by.y ;
  • parfois les deux tableaux comportent des colonnes (hors identifiants) ayant le même nom. merge conserve 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 exemple by=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 :

id v1
1 H
2 H
3 F
+
id v2
1 10
2 15
5 31
=
id v1 v2
1 H 10
2 H 15

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 :

id v1
1 H
2 H
3 F
+
id v2
1 10
2 15
5 31
=
id v1 v2
1 H 10
2 H 15
3 F NA

L’option all.y=TRUE fait la même chose en conservant toutes les lignes du second tableau.

id v1
1 H
2 H
3 F
+
id v2
1 10
2 15
5 31
=
id v1 v2
1 H 10
2 H 15
5 NA 31

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 :

id v1
1 H
2 H
3 F
+
id v2
1 10
2 15
5 31
=
id v1 v2
1 H 10
2 H 15
3 F NA
5 NA 31

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 :

id v1
1 H
2 H
3 F
+
id v2
1 10
1 18
1 21
2 15
3 42
=
id v1 v2
1 H 10
1 H 18
1 H 21
2 H 15
3 F 42

Extension dplyr

library(dplyr)
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)
# A tibble: 336,776 x 1
   carrier
   <chr>  
 1 UA     
 2 UA     
 3 AA     
 4 B6     
 5 DL     
 6 UA     
 7 B6     
 8 EV     
 9 B6     
10 AA     
# ... with 336,766 more rows

Et que par ailleurs la table airlines contient une information supplémentaire relative à ces compagnies, à savoir le nom complet.

airlines
# A tibble: 16 x 2
   carrier name                       
   <chr>   <chr>                      
 1 9E      Endeavor Air Inc.          
 2 AA      American Airlines Inc.     
 3 AS      Alaska Airlines Inc.       
 4 B6      JetBlue Airways            
 5 DL      Delta Air Lines Inc.       
 6 EV      ExpressJet Airlines Inc.   
 7 F9      Frontier Airlines Inc.     
 8 FL      AirTran Airways Corporation
 9 HA      Hawaiian Airlines Inc.     
10 MQ      Envoy Air                  
11 OO      SkyWest Airlines Inc.      
12 UA      United Air Lines Inc.      
13 US      US Airways Inc.            
14 VX      Virgin America             
15 WN      Southwest Airlines Co.     
16 YV      Mesa Airlines Inc.         

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 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"
# A tibble: 336,776 x 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.  
# ... with 336,766 more rows

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: `by` required, because the data sources have no common variables

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"))
# A tibble: 336,776 x 6
   month   day origin dest    alt name               
   <int> <int> <chr>  <chr> <int> <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         
# ... with 336,766 more rows

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"))
# A tibble: 336,776 x 8
   month   day origin dest  alt.x name.x              alt.y name.y             
   <int> <int> <chr>  <chr> <int> <chr>               <int> <chr>              
 1     1     1 EWR    IAH      18 Newark Liberty Intl    97 George Bush Interc~
 2     1     1 LGA    IAH      22 La Guardia             97 George Bush Interc~
 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 Ho~
 8     1     1 LGA    IAD      22 La Guardia            313 Washington Dulles ~
 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 
# ... with 336,766 more rows

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"))
# A tibble: 336,776 x 8
   month   day origin dest  alt_depart name_depart   alt_arrivee name_arrivee  
   <int> <int> <chr>  <chr>      <int> <chr>               <int> <chr>         
 1     1     1 EWR    IAH           18 Newark Liber~          97 George Bush I~
 2     1     1 LGA    IAH           22 La Guardia             97 George Bush I~
 3     1     1 JFK    MIA           13 John F Kenne~           8 Miami Intl    
 4     1     1 JFK    BQN           13 John F Kenne~          NA <NA>          
 5     1     1 LGA    ATL           22 La Guardia           1026 Hartsfield Ja~
 6     1     1 EWR    ORD           18 Newark Liber~         668 Chicago Ohare~
 7     1     1 EWR    FLL           18 Newark Liber~           9 Fort Lauderda~
 8     1     1 LGA    IAD           22 La Guardia            313 Washington Du~
 9     1     1 JFK    MCO           13 John F Kenne~          96 Orlando Intl  
10     1     1 LGA    ORD           22 La Guardia            668 Chicago Ohare~
# ... with 336,766 more rows

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 <- data_frame(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 <- data_frame(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
Rayan Twingo 140
Sylvie Ferrari 280
Rayan Clio 160
Gunter Lada 85
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

Extension data.table

data.table fourni une fonction merge beaucoup plus rapide que celle standard de R mais fonctionnant de manière identique.


  1. L’équivalent de cbind pour les lignes s’appelle rbind.

  2. Si vous me passez l’expression…