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 :

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.

library(tidyverse)
library(nycflights13)
data(flights)
data(airports)
data(airlines)

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 :

flights |> labelled::look_for()
 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.

airlines |> labelled::look_for()
 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() :

fusion <- flights |> left_join(airlines)
Joining with `by = join_by(carrier)`

Pour faciliter la lecture, on va afficher seulement certaines colonnes du résultat et les premières lignes de la table :

fusion |> 
  select(month, day, carrier, name) |> 
  head(10)
# 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.

Note

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 :

flights_ex <- flights |> select(month, day, origin, dest)
airports_ex <- airports |> select(faa, alt, name)

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 :

flights_ex |> left_join(airports_ex)
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 :

flights_ex |> 
  left_join(airports_ex, by = c("origin" = "faa")) |> 
  head(10)
# 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 :

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 :

flights_ex |> 
  left_join(airports_ex, by=c("dest" = "faa")) |> 
  head(10)
# 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 :

personnes |> left_join(voitures, 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    

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.

personnes |> nrow()
[1] 6
personnes |> left_join(voitures, by = "voiture") |> nrow()
[1] 6

Si on fait un dplyr::left_join() cette fois de personnes sur voitures, c’est l’inverse :

voitures |> left_join(personnes, by = "voiture")
# 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.

Important

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.

personnes |> right_join(voitures, by = "voiture")
# 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    
voitures |> left_join(personnes, by = "voiture")
# 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 :

personnes |> inner_join(voitures, by = "voiture")
# 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 :

personnes |> full_join(voitures, by = "voiture")
# A tibble: 7 × 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    
7 <NA>    208     160    

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 :

personnes |> semi_join(voitures, by = "voiture")
# 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 :

personnes |> anti_join(voitures, by = "voiture")
# A tibble: 1 × 2
  nom     voiture
  <chr>   <chr>  
1 Monique Scenic 

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

merge(personnes, voitures, by = "voiture")
  voiture    nom vitesse
1    Clio  Rayan     160
2 Ferrari Sylvie     280
3    Lada Gunter      85
4  Twingo Sylvie     140
5  Twingo  Rayan     140

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

merge(personnes, voitures, by = "voiture", all.x = 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     140
personnes |> left_join(voitures)
Joining 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 :

t1 <- airports |> 
  select(faa, name, lat, lon) |> 
  slice(1:2)
t1
# 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
t2 <- airports |> 
  select(name, faa, lon, lat) |> 
  slice(5:6)

t2
# 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
t3 <- airports |> 
  select(faa, name) |> 
  slice(100:101)
t3
# A tibble: 2 × 2
  faa   name             
  <chr> <chr>            
1 ADW   Andrews Afb      
2 AET   Allakaket Airport

On concatène ensuite les trois tables avec dplyr::bind_rows() :

bind_rows(t1, t2, t3)
# 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 :

bind_rows(t1, t2, t3, .id = "source")
# 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 :

bind_rows(table1 = t1, table2 = t2, table3 = t3, .id = "source")
# 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.