9.1 Accès aux bases de données

Puisque nous traitons de jeux de données multivariées potentiellement très gros, il devient important de pouvoir accéder aux données stockées de manière plus structurée que dans un fichier au format CSV ou Excel. Les bases de données, et notamment les bases de données relationnelles, sont prévues pour stocker de grandes quantités de données de manière structurée et pour pouvoir en extraire la partie qui nous intéresse à l’aide de requêtes. Nous allons ici étudier les rudiments indispensables pour nous permettre de gérer l’accès à de telles bases de données depuis R.

SQL est un langage dédié aux requêtes et à la manipulation de bases de données relationnelles, constituées de tables (équivalent à des tableaux cas par variables en statistiques) reliées entre elles par une ou plusieurs clés. Par exemple, le champ auteur d’une liste de livres dans la table Livres renvoie (est lié à) vers le champs nom d’une autre table Ecrivains qui fournit plus de détails sur chaque auteur.

Il existe différents moteurs de bases de données relationnelles. Les plus courants sont : SQLite, MySQL/MariaDB, PosgreSQL, SQL Server, Oracle … Mais il y a aussi des nouveaux systèmes qui offrent des fonctions intéressantes et/ou des performances inégalées dans certaines applications, notamment DuckDB que nous utiliserons ici. La plupart de ces solutions nécessitent d’installer un serveur de base de données centralisé. Cependant, SQLite et DuckDB proposent une approche plus légère permettant d’exploiter le langage SQL (prononcez “S.Q.L.” ou “Sequel”) sans devoir installer un serveur externe à R, y compris avec des petites bases de données tests en mémoire ou contenues dans un fichier.

9.1.1 Drivers de bases de données dans RStudio

S’il existe divers packages dans R permettant d’accéder à ces bases de données, RStudio offre aussi une interface plus intuitive via l’onglet Connexions que nous n’avons pas encore utilisé jusqu’ici.

Dans la SciViews Box, une série de drivers sont préinstallés, notamment pour accéder facilement à DuckDB, SQLite version 2 et SQLite version 3 ainsi qu’à PostgreSQL (plus quelques autres dans SaturnCloud). Avec ces trois bases de données différentes, il est possible d’aller très loin déjà. Pour créer une base de données DuckDB et s’y connecter à partir de RStudio (situation sans projet actif et répertoire courant ~/workspace) :

  • Activer l’onglet Connexions
  • Cliquer sur le bouton Nouvelle connexion
  • Choisir DuckDB dans la liste
  • Indiquer le nom du fichier qui va contenir une nouvelle base de données, par exemple, duckdb_test.db
  • Indiquer Nouveau script R dans le champ Connexion de:
  • Cliquer sur OK

Un nouveau script s’ouvre avec le code suivant :

library(DBI)
library(duckdb)

# create / connect to database file
drv <- duckdb(dbdir = "duckdb_test.db")
con <- dbConnect(drv)

## write a table to it
# dbWriteTable(con, "iris", iris)

## and disconnect
# dbDisconnect(con, shutdown=TRUE)

Ce code vous permet de vous connecter, interagir et vous déconnecter de la base DuckDB créée dans ~/workspace/duckdb_test.db. Moyennant l’adaptation du chemin d’accès, il est utilisable dans un projet (un script R, ou un document Quarto ou R Markdown). Exécutez ce code et votre processus R sera connectée à la base de données.

À vous de jouer !
h5p
Avec certains drivers (SQLite2, PostgreSQL …) RStudio affiche la liste des tables et des variables disponibles dans la base de données dans l’onglet Connexions de manière assez similaire à l’affichage des objets R en mémoire dans l’onglet Environnement. Malheureusement, cette fonctionnalité n’est pas (encore) disponible pour DuckDB. Par contre, vous pouvez utiliser dbListTables(con) pour lister les tables disponibles et dbListFields(con, "table_name") pour lister les champs dans une table nommée table_name.
À vous de jouer !
h5p

9.1.2 Base de données dans un fichier

Nous allons utiliser la connexion créée ci-avant pour explorer quelques notions relatives aux bases de données relationnelles. Nous profiterons de la simplicité organisationnelle de DuckDB qui inclut le serveur de base de données directement dans le processus R et qui permet de stocker nos données dans un simple fichier (tout comme SQLite permet également de le faire).

Nous nous intéressons à des données relatives à la tuberculose, issues de l’Organisation mondiale de la santé (World Health Organization en anglais, d’où le nom du jeu de données who). Comme les cas de tuberculose par pays sont à mettre en relation avec la taille de la population de chaque pays, nous récupérons ces informations complémentaires dans un autre data frame nommé pop. Ces deux jeux de données sont disponibles depuis le package {tidyr}.

who <- read("who", package = "tidyr")
str(who) # Structure du jeu de données who
# Classes 'data.table' and 'data.frame':    7240 obs. of  60 variables:
#  $ country     : chr  "Afghanistan" "Afghanistan" "Afghanistan" "Afghanistan" ...
#  $ iso2        : chr  "AF" "AF" "AF" "AF" ...
#  $ iso3        : chr  "AFG" "AFG" "AFG" "AFG" ...
#  $ year        : num  1980 1981 1982 1983 1984 ...
#  $ new_sp_m014 : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_m1524: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_m2534: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_m3544: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_m4554: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_m5564: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_m65  : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_f014 : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_f1524: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_f2534: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_f3544: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_f4554: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_f5564: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sp_f65  : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_m014 : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_m1524: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_m2534: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_m3544: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_m4554: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_m5564: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_m65  : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_f014 : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_f1524: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_f2534: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_f3544: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_f4554: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_f5564: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_sn_f65  : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_m014 : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_m1524: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_m2534: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_m3544: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_m4554: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_m5564: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_m65  : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_f014 : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_f1524: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_f2534: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_f3544: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_f4554: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_f5564: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ new_ep_f65  : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_m014 : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_m1524: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_m2534: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_m3544: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_m4554: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_m5564: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_m65  : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_f014 : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_f1524: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_f2534: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_f3544: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_f4554: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_f5564: num  NA NA NA NA NA NA NA NA NA NA ...
#  $ newrel_f65  : num  NA NA NA NA NA NA NA NA NA NA ...
#  - attr(*, "comment")= chr ""
#   ..- attr(*, "lang")= chr "en"
#   ..- attr(*, "lang_encoding")= chr "UTF-8"
#   ..- attr(*, "src")= chr "tidyr::who"
#  - attr(*, ".internal.selfref")=<externalptr>
pop <- read("population", package = "tidyr")
str(pop) # Structure du jeu de données pop
# Classes 'data.table' and 'data.frame':    4060 obs. of  3 variables:
#  $ country   : chr  "Afghanistan" "Afghanistan" "Afghanistan" "Afghanistan" ...
#  $ year      : num  1995 1996 1997 1998 1999 ...
#  $ population: num  17586073 18415307 19021226 19496836 19987071 ...
#  - attr(*, "comment")= chr ""
#   ..- attr(*, "lang")= chr "en"
#   ..- attr(*, "lang_encoding")= chr "UTF-8"
#   ..- attr(*, "src")= chr "tidyr::population"
#  - attr(*, ".internal.selfref")=<externalptr>

Allez voir l’aide relative à ces données en entrant ?tidyr::who à la console R. La table pop est assez claire. Elle contient trois variables :

  • country : le nom du pays (chaîne de caractères)
  • year : l’année (entier)
  • population : le nombre d’habitants dans ce pays cette année-là (entier).

Par contre, la table who est un peu plus “nébuleuse”, car elle contient 60 variables. Nous y retrouvons des variables identiques à la table pop : country et year. Deux colonnes précisent le pays avec des codes abrégés (colonnes nommées iso2 et iso3). Par exemple pour l’Afghanistan (premier pays par ordre alphabétique), le code ISO2 est “AF” et le code ISO3 est “AFG”. Ces quatre colonnes ne posent pas de problèmes de compréhension particuliers.

Ce sont les 56 autres colonnes qui devraient vous intriguer. La page d’aide indique que le nom de chaque colonne est formé de plusieurs items : “new” (pour nouveaux cas de tuberculose), “rel”, “sn”, “sp”, “ep”, un code indiquant la méthode de diagnostic, et enfin, une dernière composante commençant par “m” ou “f” (le genre) et suivi de deux à quatre chiffres indiquant la classe d’âge, par exemple, “1524” pour les 15 à 24 ans, avec “014”, la classe de 0 à 14 ans et “65”, la classe de 65 ans et plus. Vous vous doutez bien qu’un tel encodage des données ne va pas être facile à utiliser ! Cela nous permet d’aborder des règles de remaniement des tableaux de données qui simplifient leur usage dans une base de données relationnelle. Cela s’appelle la normalisation des données. Mais avant d’aborder cela, voyons comment travailler dans R directement avec ces données (à titre de comparaison).

La question à laquelle nous souhaitons répondre est la suivante : Quels sont les dix pays où la prévalence de la tuberculose est la plus forte en moyenne pendant les années 2000 (2000 à 2009 inclus), chez les femmes âgées de 25 à 54 ans ?

9.1.2.1 Traitement en R

Nous effectuons d’abord le traitement à partir du tableau wol non retravaillé (section facultative) afin de démontrer les difficultés qui apparaissent lorsque les données sont mal présentées. Ensuite, nous remanierons le tableau wol et ferons le traitement à nouveau. En R, nous pourrions utiliser les fonctions “tidy” ou “speedy” (et d’autres encore) pour décomposer le problème en plusieurs étapes plus simples. Nous utiliserons ici autant que possible les fonctions “tidy”. Réfléchissez un peu par vous-mêmes comment vous feriez pour répondre à cette question dans R avant de continuer votre lecture…

Traitement à partir du tableau wol non retravaillé

Nous pouvons décomposer le problème comme suit :

  1. fusion des tableaux who et pop,
  2. filtrage des données pour ne conserver que les années égales ou supérieures à 2000 et inférieures à 2010,
  3. filtrage des données pour ne conserver que les femmes entre 25 et 54 ans,
  4. somme de nouveaux cas de tuberculose, quelles que soit la méthode de détection et la classe d’âge,
  5. calcul des proportions, soit la somme de cas divisés par la population totale, variable nommée prevalence,
  6. regroupement par pays,
  7. résumé des prévalences moyennes par pays, toutes années confondues,
  8. tri de ces prévalences moyennes par ordre décroissant,
  9. enfin, ne conserver que les dix pays ayant les plus fortes prévalences moyennes.

Les étapes 3 et 4 sont mises en évidence, car elles seront difficiles à réaliser à cause du mauvais encodage. C’est parce que l’information requise (genre et classe d’âge) n’est pas reprise sous forme de données dans le tableau, mais cachée dans le nom des colonnes. Les deux premières étapes sont faciles pour quelqu’un qui maîtrise un peu les fonctions “tidy”.

dim(who)
# [1] 7240   60
dim(pop)
# [1] 4060    3
who %>.%
  left_join(., pop) %>.% # étape 1 fusion
  filter(., year >= 2000 & year < 2010) -> # étape 2 filtrage années 2000-2010
  who2
# Joining with `by = join_by(country, year)`
dim(who2)
# [1] 2133   61

Nous marquons un point d’arrêt après l’étape 2 avec who2 afin de constater que le premier filtrage sur les années se fait facilement. Notre fusion et notre filtrage ont fonctionné et nous avons maintenant un tableau à 2133 lignes et 61 colonnes. Attaquons-nous maintenant au second filtrage pour ne conserver que les femmes entre 25 et 54 ans. Vous noterez qu’il est impossible de le réaliser avec filter() (essayez, vous comprendrez vite). La seule solution à votre portée sans remanier le tableau consiste à liste manuellement le nom de toutes les colonnes qui répondent aux critères et à effectuer un select() un peu “bricolé” pour y arriver. Si vous listez exactement toutes les colonnes correspondantes, vous obtiendrez une liste de douze items, essentiellement parce que vous devez lister toutes les méthodes de détection. Une petite astuce consiste à utiliser ends_with() qui ne regarde que la dernière partie du nom. À ce moment, vous pouvez vous concentrer sur toutes les colonnes qui terminent par f2534, f3544 et f4554. Cependant, vous ne devez pas oublier de conserver au moins les colonnes country, year et population qui seront aussi nécessaires au calcul. Voici ce que cela donne :

who2 %>.% # étape 3 filtrage des femmes entre 25 et 54 ans uniquement
  select(., country, year, population, ends_with(c("f2534", "f3544", "f4554"))) ->
  who3
dim(who3)
# [1] 2133   15
names(who3)
#  [1] "country"      "year"         "population"   "new_sp_f2534" "new_sn_f2534"
#  [6] "new_ep_f2534" "newrel_f2534" "new_sp_f3544" "new_sn_f3544" "new_ep_f3544"
# [11] "newrel_f3544" "new_sp_f4554" "new_sn_f4554" "new_ep_f4554" "newrel_f4554"

L’étape 4 pose aussi quelques soucis, car à nouveau, l’information nécessaire utile est encodée dans le nom des variables. Ici, nous pourrions être tentés d’utiliser un mutate() avec quelque chose comme all_new = new_sp_f2534 + new_sn_f2534 + <...longue liste à compléter ici !>. Non seulement c’est pénible à encoder, mais en plus, cela ne fonctionnera pas à cause des valeurs manquantes. Vous vous retrouverez avec des NA à peu près partout ! Si vous êtes tenté d’utiliser all_new = sum(new_sp_f2534, new_sn_f2534, <...longue liste à compléter ici !>, na.rm = TRUE)… et bien cela ne fonctionnera pas non plus (essayez si vous ne me croyez pas) ! Il existe plusieurs façons de faire cela, mais aucune n’est très élégante. Nous vous épargnons les détails. Ce qui est important de noter, c’est la difficulté à réaliser cette étape 4 à cause du mauvais encodage des données dans who au départ. Allez, c’est parti en utilisant rowSums() qui, comme son nom l’indique, calcule des sommes par lignes (les plus téméraires peuvent rechercher une autre façon de faire comme exercice) !

who3 %>.%
  select(., -country, -year, -population) %>.%
  transmute(., all_new = rowSums(., na.rm = TRUE)) %>.%
  dtx(country = who3$country, year = who3$year, population = who3$population, all_new = .$all_new) ->
  who4
dim(who4)
# [1] 2133    4
names(who4)
# [1] "country"    "year"       "population" "all_new"

Les étapes suivantes ne posent pas de problèmes particuliers, car nous venons d’évacuer la difficulté du mauvais encodage des données avec le calcul de notre colonne all_new.

who4 %>.%
  mutate(., prevalence = all_new / population) %>.% # étape 5, calcul de la prévalence
  group_by(., country) %>.% # étape 6 regroupement par pays
  summarise(., mean_prev = mean(prevalence, na.rm = TRUE)) %>.% # étape 7 prévalence moyenne
  arrange(., desc(mean_prev)) %>.% # étape 8 tri décroissant
  head(., 10) # étape 9 seulement les 10 premiers pays
# # A tibble: 10 × 2
#    country      mean_prev
#    <chr>            <dbl>
#  1 South Africa  0.000971
#  2 Swaziland     0.000807
#  3 Namibia       0.000726
#  4 Botswana      0.000617
#  5 Lesotho       0.000606
#  6 Zimbabwe      0.000502
#  7 Cambodia      0.000382
#  8 Kiribati      0.000353
#  9 Djibouti      0.000344
# 10 Zambia        0.000338

Ouf ! Cela n’a pas été simple du tout. Faites une pause bien méritée, mais ne vous découragez pas si vous avez perdu le fil. L’important est de tirer la conclusion que, lorsque les données sont mal encodées, le traitement risque de s’avérer très pénible même dans R.

Nous avons quand même un petit problème ici que nous détaillerons un peu plus moin. Si vous examinez bien la page d’aide de ?rowSums, vous constaterez que, lorsque tous les éléments de la ligne contiennent NA, rowSums(..., na.rm = TRUE) renvoie 0 au lieu de NA. Dans ce cas, est-ce qu’il n’y a pas de cas répertoriés (et le calcul est le bon), ou est-ce qu’il n’y a pas eu de mesures durant cette année-là dans ce pays (auquel cas, il faut utiliser NA). Ici, le résultat considère 0 dans ce cas.

Nous allons remanier le tableau who pour nous économiser les difficultés dans nos traitements ultérieurs et dans la base de données. Le tableau who est en fait une forme large et nous devons le transformer en un tableau long en tout premier lieu avec pivot_longer(). Nous avons vu ces techniques au module 6 du cours de SDD I. Outre le tableau à traiter, nous indiquons successivement les colonnes à remanier en long (toutes celles qui commencent par "new"), le nom de la colonne qui reprend les anciens noms (type) et le nom de la colonne qui reprend les valeurs (new_cases).

whol <- pivot_longer(who, starts_with("new"), names_to = "type", values_to = "new_cases")
dim(whol)
# [1] 405440      6
head(whol)
# # A tibble: 6 × 6
#   country     iso2  iso3   year type         new_cases
#   <chr>       <chr> <chr> <dbl> <chr>            <dbl>
# 1 Afghanistan AF    AFG    1980 new_sp_m014         NA
# 2 Afghanistan AF    AFG    1980 new_sp_m1524        NA
# 3 Afghanistan AF    AFG    1980 new_sp_m2534        NA
# 4 Afghanistan AF    AFG    1980 new_sp_m3544        NA
# 5 Afghanistan AF    AFG    1980 new_sp_m4554        NA
# 6 Afghanistan AF    AFG    1980 new_sp_m5564        NA

Notez que notre tableau long possède plus de 400.000 lignes. Nous devons encore modifier le tableau pour avoir trois colonnes à la place de type. Ces colonnes vont reprendre les informations suivantes : (1) la méthode de détection (method), (2) le genre (sex) et (3) la classe d’âge (age). Nous pourrions être tentés d’utiliser separate() ici. Malheureusement, nous n’avons pas un caractère qui sépare de manière parfaite nos informations. Le trait souligné sépare la méthode du reste, mais il est repris dans la méthode également comme avec new_sp, mais pas toujours comme avec newrel. De plus, aucun caractère ne sépare le genre de la classe d’âge. Décidément, ce jeu de données est particulièrement pénible à traiter. Pas de découragement. Cela nous donne l’occasion de découvrir d’autres fonctions R utiles ! Nous allons extraire les infos avec substring(), fonction que vous n’avez probablement pas encore utilisée. Cette fonction extrait des parties d’une chaîne de caractères, en indiquant le caractère de début et de fin pour le découpage. Par exemple :

substring("new_ep_f2534", 1, 6) # method
# [1] "new_ep"
substring("new_ep_f2534", 8, 8) # sex
# [1] "f"
substring("new_ep_f2534", 9, 12) # age
# [1] "2534"

Nous pouvons maintenant créer nos trois nouvelles colonnes et nous débarrasser de type qui n’est plus utile :

whol %>.%
  mutate(.,
    method = substring(type, 1, 6),
    sex    = substring(type, 8, 8),
    age    = substring(type, 9, 12)) %>.%
  select(., -type) ->
  whol
head(whol)
# # A tibble: 6 × 8
#   country     iso2  iso3   year new_cases method sex   age  
#   <chr>       <chr> <chr> <dbl>     <dbl> <chr>  <chr> <chr>
# 1 Afghanistan AF    AFG    1980        NA new_sp m     014  
# 2 Afghanistan AF    AFG    1980        NA new_sp m     1524 
# 3 Afghanistan AF    AFG    1980        NA new_sp m     2534 
# 4 Afghanistan AF    AFG    1980        NA new_sp m     3544 
# 5 Afghanistan AF    AFG    1980        NA new_sp m     4554 
# 6 Afghanistan AF    AFG    1980        NA new_sp m     5564

Notre tableau whol est enfin correctement encodé. Toute l’information est maintenant dans les cellules du tableau et chaque cellule contient une et une seule information ou donnée. C’est parti pour le traitement avec fonctions “tidy” qui va répondre à notre question. Nous décomposons ce traitement comme suit :

  1. fusion des tableaux whol et pop,
  2. filtrage des données pour ne conserver que les années >= 2000 et < 2010, en ne conservant que les femmes entre 25 et 54 ans,
  3. regroupement par pays et par année,
  4. somme des nouveaux cas de tuberculose par pays et par année,
  5. calcul des proportions, soit la somme de cas divisés par la population totale, variable nommée prevalence,
  6. regroupement par pays,
  7. résumé des prévalences moyennes par pays, toutes années confondues,
  8. ne conserver que les dix pays ayant les plus fortes prévalences moyennes.
whol %>.%
  left_join(., pop) %>.% # étape 1 fusion
  filter(., year >= 2000 & year < 2010 & sex == "f" & age %in% c("2534", "3544", "4554")) %>.% # étape 2 filtrage
  group_by(., country, year) %>.% # étape 3 regroupement par pays et année
  summarise(., total = first(population), all_new = sum(new_cases, na.rm = TRUE)) %>.% # étape 4 somme des cas
  mutate(., prevalence = all_new / total) %>.% # étape 5, calcul de la prévalence
  group_by(., country) %>.% # étape 6 regroupement par pays
  summarise(., mean_prev = mean(prevalence, na.rm = TRUE)) %>.% # étape 7 prévalence moyenne
  slice_max(., mean_prev, n = 10) # étape 8 ne garder que les 10 plus élevés
# Joining with `by = join_by(country, year)`
# `summarise()` has grouped output by 'country'. You can override using the
# `.groups` argument.
# # A tibble: 10 × 2
#    country      mean_prev
#    <chr>            <dbl>
#  1 South Africa  0.000971
#  2 Swaziland     0.000807
#  3 Namibia       0.000726
#  4 Botswana      0.000617
#  5 Lesotho       0.000606
#  6 Zimbabwe      0.000502
#  7 Cambodia      0.000382
#  8 Kiribati      0.000353
#  9 Djibouti      0.000344
# 10 Zambia        0.000338

Ici, lorsqu’il y a des NA partout pour un pays et une année, le calcul avec sum() considère que le total vaut 0. L’autre fonction que nous utilisions parfois, fsum() renvoie NA dans ce cas.

sum(NA, na.rm = TRUE)
# [1] 0
fsum(NA, na.rm = TRUE)
# [1] NA

Voici le résultat, légèrement différent (comparez le Lesotho, le Botswana, et la Zambie), du calcul avec fsum() :

whol %>.%
  left_join(., pop) %>.% # étape 1 fusion
  filter(., year >= 2000 & year < 2010 & sex == "f" & age %in% c("2534", "3544", "4554")) %>.% # étape 2 filtrage
  group_by(., country, year) %>.% # étape 3 regroupement par pays et année
  summarise(., total = first(population), all_new = fsum(new_cases, na.rm = TRUE)) %>.% # étape 4 somme des cas AVEC fsum()
  mutate(., prevalence = all_new / total) %>.% # étape 5, calcul de la prévalence
  group_by(., country) %>.% # étape 6 regroupement par pays
  summarise(., mean_prev = mean(prevalence, na.rm = TRUE)) %>.% # étape 7 prévalence moyenne
  slice_max(., mean_prev, n = 10) # étape 8 ne garder que les 10 plus élevés
# Joining with `by = join_by(country, year)`
# `summarise()` has grouped output by 'country'. You can override using the
# `.groups` argument.
# # A tibble: 10 × 2
#    country      mean_prev
#    <chr>            <dbl>
#  1 South Africa  0.000971
#  2 Swaziland     0.000807
#  3 Namibia       0.000726
#  4 Lesotho       0.000674
#  5 Zimbabwe      0.000627
#  6 Botswana      0.000617
#  7 Cambodia      0.000382
#  8 Zambia        0.000375
#  9 Kiribati      0.000353
# 10 Djibouti      0.000344

9.1.3 Normalisation des données

La normalisation permet de présenter les données de manière correcte et optimale pour son utilisation dans une base de données relationnelle. Elle fait appel à des règles strictes. Il existe plusieurs niveaux de normalisation de 1 à 6. Nous n’étudierons ensemble que les trois premiers niveaux qui permettent déjà d’atteindre un schéma de base de données très utilisable et qui est suffisant pour comprendre la logique d’une base de données relationnelle. Ces notions sont également utilisables avec le package {dm} pour une série de data frames reliés entre eux, donc directement dans R, hors base de données externe ou complémentaire.

9.1.3.1 Normalisation niveau 1

La normalisation niveau 1 (on peut écrire 1NF) s’intéresse à chaque table séparément (en bases de données on parle de table au lieu de jeu de données ou data frame, et d’enregistrement au lieu des cas ou individus = lignes de la table). Il s’agit ici de reformater (éventuellement) une table afin que :

  • il n’y ait aucune ligne ou colonne dupliquée
  • chaque cellule de la table ne contienne qu’une seule valeur
  • la table possède une clé primaire pour identifier de manière non ambiguë chaque observation

Explicitons ces trois conditions. La non duplication parait assez évidente. En pratique, si nos avons plusieurs fois le même enregistrement, nous créerons une colonne qui dénombre cet enregistrement. C’est le cas dans notre table pop. Nous n’avons qu’une seule ligne pour un pays et une année, et la colonne population compte le nombre d’habitants. nous n’avons pas encodé 10 millions de fois "Belgium", 1995 par exemple, pour chaque belge. La seconde règle veut que les données soient atomiques, c’est-à-dire, que chaque cellule ne contienne qu’une seule valeur. Nous ne pouvons pas indiquer dans population un vecteur de plusieurs nombres qui reprennent la population de plusieurs années successives. Nous nous arrangeons pour avoir une ligne par année dans le tableau et une valeur relative à cette année pour population. La dernière règle indique qu’il faut une clé primaire qui identifie de manière univoque chaque enregistrement (il ne peut y avoir de doublons). En pratique, cette clé primaire peut être composite, donc, composée de plusieurs colonnes du tableau. Pour pop, la clé primaire serait ainsi constitué des colonnes country et year, sachant que chaque enregistrement est relatif à un pays et une année. Donc, à conditions de définir cette clé primaire (nous verrons plus loin comment faire), notre tableau pop peut être considéré 1NF.

Par contre pour who, nous avons plusieurs variables rassemblées au sein des colonnes 5 à 60 de manière pas très pratique (en réalité, des données sont “cachées” dans le nom des colonnes : la méthode de détection, le genre et la classe d’âge). Nous avons vu qu’un remaniement du tableau de large en long et la séparation de la colonne type ainsi obtenue en trois colonnes method, sex et age nous donne une table whol plus exploitable… et en même temps, cette table répond également aux contraintes pour être 1NF. La clé primaire est ici constituée de l’ensemble des cinq colonnes country, year, method, sex et age parce qu’elles sont toutes les cinq nécessaire simultanément pour caractériser un enregistrement de manière non équivoque.

Une clé primaire a les propriétés suivante :

  • Sa valeur est unique dans la table
  • Il n’y a pas de valeurs manquantes
  • La clé primaire ne doit jamais, voire rarement être modifiée
  • Une clé primaire est attribuable dès l’introduction de tout nouvel enregistrement dans la table

C’est bien le cas de nos deux clés primaires composites. Nous pouvons maintenant injecter nos données dans la base de données DuckDB et créer le schéma de notre base avec {dm}.

library(DBI)
library(duckdb)

# création et connexion à la base de données
drv <- duckdb(dbdir = "duckdb_test.db")
con <- dbConnect(drv)

# injection de deux data frames comme tables
# notez bien que nous utilisons le data frame long et correctement nettoyé
# `whol` pour notre table "who",... pas l'horrible data frame `who` initial !
dbWriteTable(con, "who", whol)
dbWriteTable(con, "pop", pop)

Pour manipuler et visualiser le schéma de la base de données il existe différents outils. Nous vous proposons d’utiliser le package R {dm} qui facilite grandement ce travail.

library(dm)
# 
# Attaching package: 'dm'
# The following object is masked from 'package:MASS':
# 
#     select
# The following object is masked from 'package:stats':
# 
#     filter
# création d'un objet dm qui reprend le schéma de la base
# nous indiquons learn_keys = FALSE car nous n'avons pas encore de clés
# primaires et de toutes façons, {dm} ne les détecte pas depuis DuckDB
dm_from_con(con, learn_keys = FALSE) %>.%
  dm_set_colors(., red = who, darkgreen = pop) -> # Couleurs pour les tables (pour le schéma)
  who_dm
# graphique du schéma de la base
dm_draw(who_dm, view_type = "all")

Si les clés primaires sont définies à partir d’une seule colonne, la fonction dm_enum_pk_candidates() est utile pour découvrir les colonnes qui pourraient être utilisées (voyez pour who à titre d’exercice) :

dm_enum_pk_candidates(who_dm, pop)
# # A tibble: 3 × 3
#   columns    candidate why                                                      
#   <keys>     <lgl>     <chr>                                                    
# 1 country    FALSE     has duplicate values: Afghanistan (19), Albania (19), Al…
# 2 year       FALSE     has duplicate values: 2011 (217), 2012 (217), 2013 (217)…
# 3 population FALSE     has duplicate values: 20186 (2), 52161 (2), 59117 (2), 1…

La colonne candidate indique si la variable peut être utilisée et la colonne why explique pourquoi dans le cas contraire. Ici, nous n’avons aucune variables qui ne reprend pas des valeurs non dupliquées. Ce n’est pas bien grave car nous allons utiliser une clé primaire composite qui utilise simultanément country et year pour la table “pop” et cinq variables pour la table “who”.

# création des clés primaires
who_dm %>.%
  dm_add_pk(., pop, c(country, year), force = TRUE) %>.%
  dm_add_pk(., who, c(country, year, method, sex, age), force = TRUE) ->
  who_dm
# graphique du schéma de la base
dm_draw(who_dm, view_type = "all")

Nous voyons un nouvel item souligné au bas de chaque table : nos clés primaires composites. À ce stade, la normalisation au niveau 1 est terminée.

9.1.3.2 Relations entre tables

Jusqu’ici nous avons traité chaque table “pop” et “who” indépendamment. Mais une relation existe entre les deux qui permet de rassembler leurs données si nécessaire. C’est le côté relationnel de la base de données. C’est aussi ce que nous avons fait dans R avec le left_join() en utilisant les colonnes communes country et year comme colonnes servant à la jointure. Contrairement à R, avec les bases de données relationnelles, nous n’allons pas fusionner des tableaux en un seul gros data frame, mais plutôt les diviser en sous-tables plus compactes liées entre elles.

Comment spécifier ces relations ? Avec des clés étrangères. Ces clés vont lier une table à la clé primaire d’une autre table avec un lien un à un, ou un à plusieurs (un enregistrement de la table avec clé primaire pointe vers un seul ou plusieurs enregistrements de la table qui contient la clé étrangère). Par contre, il n’est pas possible d’avoir une relation plusieurs à un ou plusieurs à plusieurs puisque les clés primaires doivent être uniques par définition. Il est cependant possible que certaines valeurs de la clé primaire ne pointent vers rien (c’est optionnel), ou pas. Les quatre situations possibles sont donc :

  • un à un
  • zéro ou un à un
  • un à plusieurs
  • zéro ou un à plusieurs

Ici, nous pouvons donc utiliser la clé primaire de la table “pop” et établir une relation zéro ou un à plusieurs vers une clé étrangère dans la table “who” composée de country et year. Il existe aussi une fonction dm_enum_fk_candidates() qui permet de repérer les candidats pour des clés étrangères dans une table par rapport à une autre.

# argument #2: table où l'on recherche une clé étrangère
# argument #3: table ayant la clé primaire pour la relation
dm_enum_fk_candidates(who_dm, who, pop)

Malheureusement, cette fonction ne semble pas travailler correctement avec des clés primaires composites. Peu importe car nous avons déjà une idée de la clé à utiliser ici. La fonction dm_add_fk() ajoute une clé étrangère.

who_dm <- dm_add_fk(who_dm, who, c(country, year), pop)
# graphique du schéma de la base avec relation entre les tables
dm_draw(who_dm, view_type = "all")

La définition de ce lien via une clé étrangère ne garanti pas l’intégrité des données à ce stade. En pratique, il faudrait que chaque clé étrangère composée de country + year soit également présente dans la table “pop”. Mais nous n’avons jamais vérifié cela. Il est facile de le vérifier avec les fonctions check_cardinality_x_y() où x est “0” ou “1” et y est “1” ou “n”. Cela défini les relations un à un, un à plusieurs, ainsi que zéro à un et zéro à plusieurs qui permettent aussi d’avoir des valeurs pour la clé primaire qui ne se retrouvent pas dans la clé étrangère. Vérifions donc si nous avons une cardinalité zéro ou un à plusieurs ici.

check_cardinality_0_n(pop, c(country, year), whol, c(country, year))
# # A tibble: 10 × 2
#    country      year
#    <chr>       <dbl>
#  1 Afghanistan  1980
#  2 Afghanistan  1980
#  3 Afghanistan  1980
#  4 Afghanistan  1980
#  5 Afghanistan  1980
#  6 Afghanistan  1980
#  7 Afghanistan  1980
#  8 Afghanistan  1980
#  9 Afghanistan  1980
# 10 Afghanistan  1980
# Error in `abort_not_subset_of()`:
# ! Columns (`country`, `year`) of table `whol` contain values (see examples above) that are not present in columns (`country`, `year`) of table `pop`.

Une erreur est générée et un tableau présente un échantillon d’enregistrements de whol qui ne se retrouvent pas dans pop. si nous investiguons plus loin, nous nous rendons compte que les années reprises dans pop sont plus restreintes que dans whol :

unique(pop$year)
#  [1] 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
# [16] 2010 2011 2012 2013
unique(whol$year)
#  [1] 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994
# [16] 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
# [31] 2010 2011 2012 2013

Les données de whol commencent en 1980 alors que les données de pop ne commencent qu’en 1995. Nous devrions donc compléter pop en remontant jusqu’en 1980… ou alors, tronquer whol pour éliminer les données avant 1995. Réalisons la seconde option…

whol1995 <- filter(whol, year >= 1995)

Nous pouvons aussi vérifier si tous les pays repris dans whol1995 le sont aussi dans pop :

whol_countries <- unique(whol1995$country)
pop_countries <- unique(pop$country)
all(whol_countries %in% pop_countries)
# [1] FALSE

Aïe, ce n’est pas le cas. Quels sont les pays non repris ?

whol_countries[!whol_countries %in% pop_countries]
# [1] "Cote d'Ivoire" "Curacao"

Bizarre que ces deux pays ne soient pas repris. Qu’avons-nous comme pays commençant pas “C” dans pop_countries ?

pop_countries[substring(pop_countries, 1, 1) == "C"]
#  [1] "Cabo Verde"               "Cambodia"                
#  [3] "Cameroon"                 "Canada"                  
#  [5] "Cayman Islands"           "Central African Republic"
#  [7] "Chad"                     "Chile"                   
#  [9] "China"                    "China, Hong Kong SAR"    
# [11] "China, Macao SAR"         "Colombia"                
# [13] "Comoros"                  "Congo"                   
# [15] "Cook Islands"             "Costa Rica"              
# [17] "Côte d'Ivoire"            "Croatia"                 
# [19] "Cuba"                     "Curaçao"                 
# [21] "Cyprus"                   "Czech Republic"

Ah voilà l’erreur : le nom n’est pas orthographié de la même façon. “Cote d’Ivoire” versus “Côte d’Ivoire” (avec un accent circonflexe sur le o) et “Curacao” versus “Curaçao” (avec une cédille au second c). Nous pouvons corriger l’orthographe d’un côté :

whol1995$country[whol1995$country == "Cote d'Ivoire"] <- "Côte d'Ivoire"
whol1995$country[whol1995$country == "Curacao"] <- "Curaçao"
# Vérification
all(unique(whol1995$country) %in% pop_countries)
# [1] TRUE

Cette fois-ci c’est bon. Cela ne garantit pas que toutes les combinaisons pays-année se retrouvent dans pop, mais nous pouvons le retester à présent avec check_cardinality_0_n().

check_cardinality_0_n(pop, c(country, year), whol1995, c(country, year))

Si la fonction ne génère plus d’erreur, comme ici, c’est bon. On a donc bien une relation zéro ou un à plusieurs entre la clé primaire de pop et la clé étrangère de whol1995. Nous allons mettre à jour notre base de données et recréer les relations pour qu’elles soient correctes. Nous pouvons effacer des clés avec dm_rm_fk() et dm_rm_pk(), mais ici, cela sera plus facile de recommencer notre objet who_dm après avoir remplacé la table “who”.

# effacement de l'ancienne table "whol" et remplacement par la version corrigée
dbRemoveTable(con, "who")
dbWriteTable(con, "who", whol1995)
# nouvel objet data model
dm_from_con(con, learn_keys = FALSE) %>.%
  dm_set_colors(., red = who, darkgreen = pop) %>.% # Couleurs (optionel)
  dm_add_pk(., pop, c(country, year)) %>.% # Clé primaire pop
  dm_add_pk(., who, c(country, year, method, sex, age)) %>.% # Clé primaire who
  dm_add_fk(., who, c(country, year), pop) -> # Clé étrangère who -> pop
  who_dm
# graphique du schéma de la base
dm_draw(who_dm, view_type = "all")

Et voilà le travail. Tout cela vous semble sans doute bien compliqué… et ça l’est si vous devez gérer manuellement toutes ces contraintes d’intégrité dans la base de données. Mais la bonne nouvelle, c’est qu’elles peuvent être spécifiées dès le départ dans son schéma. Dans ce cas, le serveur de base de données vérifiera l’intégrité à chaque modification et ne permettra pas les opérations qui la mettent à mal. Cela s’appelle l’intégrité référentielle. La définition d’un tel schéma ne peut être réalisée dans le cadre de ce cours (il faudrait pratiquement un cours entièrement consacré aux bases de données pour ce faire). L’important ici, c’est de comprendre la logique. Ainsi, lorsque vous utiliserez une base de données relationnelle existante, vous pourrez comprendre son modèle de données.

Nous pouvons examiner le type de cardinalité rencontrée avec examine_cardinality()  :

examine_cardinality(pop, c(country, year), whol1995, c(country, year))
# [1] "surjective mapping (child: 1 to n -> parent: 1)"

Ceci nous suggère une relation un à plusieurs, même si nous acceptons aussi le zéro ou un à plusieurs.

9.1.3.3 Normalisation niveau 2

La normalisation au niveau 2 impose déjà comme première règle que toutes les tables soient 1NF. Une règle supplémentaire vient cependant s’ajouter : il ne peut y avoir aucune donnée qui ne dépende pas de la clé primaire. Si c’est le cas, c’est le signe qu’il faut diviser la table en deux ou plusieurs sous-tables ayant chacune sa propre clé primaire. Un exemple serait si deux organisations différentes comptabilisaient indépendamment les cas de tuberculose. Pour chaque clé primaire de la table “who”, nous aurions donc deux enregistrement possibles, un par organisation. Dans ce cas, nous serions obligé de diviser les données en deux tables et créer une nouvelle table “org” qui définirait une clé primaire pour chaque organisation. Comme ce n’est pas le cas ici, nous pouvons considérer que notre base de données satisfait aussi au niveau 2, autrement dit, elle est 2NF.

9.1.3.4 Normalisation niveau 3

Le niveau 3 de normalisation vise à réduire la duplication de données dans les tables. Techniquement, on parle de dépendance fonctionnelle transitive qui définit que, si l’on change une entrée dans un enregistrement, une ou plusieurs autres doivent changer également. Pour qu’une base de données soit 3NF, il faut qu’elle soit 2NF et qu’en plus, elle n’ait pas de dépendance fonctionnelle transitive.

Un exemple concret sera plus parlant certainement. Dans la table “who”, nous avons les champs “country”, “iso2” et “iso3”. Or iso2 est le code en deux lettres du pays (par exemple “Belgium” et “BE”). Si le pays change, le code doit changer aussi. Il y a donc dépendance fonctionnelle transitive entre les champs “country” et “iso2”. Idem avec “iso3”.

Une autre façon, peut-être plus facile, de détecter le problème consiste à voir si des champs ont des entrées répétées et inutiles dans la table. C’est le cas ici. Si on connait le pays, on pourra en déduite les codes iso2 et iso3. Les champs iso2 et iso3 ne sont donc pas utiles dans la table “who”. Ils peuvent être déportés dans une autre table, par exemple nommée “countries” qui reprend le nom du pays comme clé primaire et “iso2” + “iso3”. Si nous effectuons cette modification, notre base de données atteindra le niveau 3 de normalisation ou 3NF.

# Création du data frame coutries
whol1995 %>.%
  select(., country, iso2, iso3) %>.%
  distinct(.) ->
  countries
head(countries)
# # A tibble: 6 × 3
#   country        iso2  iso3 
#   <chr>          <chr> <chr>
# 1 Afghanistan    AF    AFG  
# 2 Albania        AL    ALB  
# 3 Algeria        DZ    DZA  
# 4 American Samoa AS    ASM  
# 5 Andorra        AD    AND  
# 6 Angola         AO    AGO

Nous allons éliminer les colonnes “iso2” et “iso3” dans la table “who” et ajouter la table “countries” ainsi que la clé primaire et les clés étrangères utiles. Au lieu de passer son temps à effacer toute la table “who” et la recréer, nous allons maintenant utiliser une commande SQL pour effacer les deux colonnes “iso2” et “iso3” de “who”.

dbExecute(con, 'ALTER TABLE "who" DROP COLUMN "iso2";')
# [1] 0
dbExecute(con, 'ALTER TABLE "who" DROP COLUMN "iso3";')
# [1] 0
# Vérification
dbListFields(con, "who")
# [1] "country"   "year"      "new_cases" "method"    "sex"       "age"

Ensuite, nous ajoutons la table “countries” :

dbWriteTable(con, "countries", countries)
# Vérification
dbListTables(con)
# [1] "countries" "pop"       "who"

Enfin, nous ajustons le schéma en ajoutant les clés nécessaires (il faut recommencer le dm, mais cela se fait rapidement) :

dm_from_con(con, learn_keys = FALSE) %>.%
  dm_set_colors(., red = who, darkgreen = pop, blue = countries) %>.% # Couleurs (optionel)
  dm_add_pk(., pop, c(country, year)) %>.% # Clé primaire pop
  dm_add_pk(., who, c(country, year, method, sex, age)) %>.% # Clé primaire who
  dm_add_pk(., countries, country) %>.% # Clé primaire countries
  dm_add_fk(., who, c(country, year), pop) %>.% # Clé étrangère who -> pop
  dm_add_fk(., pop, country, countries) %>.% # Clé étrangère pop -> countries
  dm_add_fk(., who, country, countries) -> # Clé étrangère who -> countries
  who_dm
# Graphique du schéma de la base
dm_draw(who_dm, view_type = "all")

La définition de deux clés étrangères depuis “who” et “pop” vers la clé primaire “country” dans la table “countries” crée ce que l’on appelle une relation cyclique entre les trois tables. Avec les versions antérieures de {dm} cela n’était pas possible. Vérifiez donc bien la version de votre package {dm}. Dans la SciViews Box 2024, il s’agit de la version 1.0.5. Cependant, ces relations cycliques ne sont pas encore bien gérées par d’autres fonctions de {dm}, notamment dm_filter() que nous utiliserons ensuite. Nous allons donc créer une version plus simple de notre modèle de données sans la clé étrangère entre “pop” et “countries”.

dm_from_con(con, learn_keys = FALSE) %>.%
  dm_set_colors(., red = who, darkgreen = pop, blue = countries) %>.% # Couleurs (optionel)
  dm_add_pk(., pop, c(country, year)) %>.% # Clé primaire pop
  dm_add_pk(., who, c(country, year, method, sex, age)) %>.% # Clé primaire who
  dm_add_pk(., countries, country) %>.% # Clé primaire countries
  dm_add_fk(., who, c(country, year), pop) %>.% # Clé étrangère who -> pop
  # Nous n'utilisons pas cette clé étrangère pour éviter une relation cyclique
  #dm_add_fk(., pop, country, countries) %>.% # Clé étrangère pop -> countries
  dm_add_fk(., who, country, countries) -> # Clé étrangère who -> countries
  who_dm
# Graphique du schéma de la base
dm_draw(who_dm, view_type = "all")

Voilà notre modèle de données répond maintenant aux exigences 3NF. Enfin, nous pouvons valider le modèle de données comme suit (encore une fois, si aucune erreur n’est générée, le modèle est bon) :

dm_validate(who_dm)

9.1.4 Requête dans une base de données relationnelle avec {dm}

Revenons sur notre question de départ, nous souhaitons déterminer quels sont les dix pays où la prévalence annuelle moyenne entre les années 2000 et 2010 est la plus forte pour les femmes âgées de 25 à 54 ans. Nous pouvons interroger notre base de données au travers de notre objet dm en utilisant les fonctions équivalentes à celles que vous connaissez déjà comme dm_filter(), dm_select(). Comme notre objet dm connait les liens entre les tables, il va les combiner automatiquement de la meilleure façon. Pas besoin d’utiliser left_join() ici pour rassembler les données issues de la table “who” et de la table “pop”. Cela est fait automatiquement ! Il suffit, à un moment donné, de demander de regrouper le résultat calculé jusqu’ici avec dm_flatten_to_tbl() pour obtenir un data frame avec toutes les colonnes jointes (nous ne l’avons pas fait ici, mais dm_select() permet naturellement de restreindre les colonnes de chaque table liée que nous récupérons).

who_dm %>.%
  dm_filter(., who, year >= 2000 & year < 2010 & sex == "f" & age %in% c("2534", "3544", "4554")) %>.% # étape 2 filtrage
  dm_flatten_to_tbl(., who) ->
  who_flat
# Warning: The `table` argument of `dm_filter()` is deprecated as of dm 1.0.0.
# ℹ `dm_filter()` now takes named filter expressions, the names correspond to the
#   tables to be filtered. You no longer need to call `dm_apply_filters()` to
#   materialize the filters.
# This warning is displayed once every 8 hours.
# Call `lifecycle::last_lifecycle_warnings()` to see where this warning was
# generated.
head(who_flat)
# # Source:   SQL [6 x 9]
# # Database: DuckDB v0.10.1 [root@Darwin 23.6.0:R 4.3.3//Users/phgrosjean/Documents/GitHub/BioDataScience-Course/sdd-umons2-2024/duckdb_test.db]
#   country  year new_cases method sex   age   iso2  iso3  population
#   <chr>   <dbl>     <dbl> <chr>  <chr> <chr> <chr> <chr>      <dbl>
# 1 Mali     2000       232 new_sp f     2534  ML    MLI     10260577
# 2 Mali     2000        NA new_sn f     2534  ML    MLI     10260577
# 3 Mali     2000        NA new_ep f     2534  ML    MLI     10260577
# 4 Mali     2000        NA newrel f     2534  ML    MLI     10260577
# 5 Mali     2001        NA new_sp f     2534  ML    MLI     10562768
# 6 Mali     2001        NA new_sn f     2534  ML    MLI     10562768

À partir de ce point, nous pouvons effectuer le calcul comme si de rien était avec les fonctions “tidy”. Pour l’ensemble du traitement, cela donne :

who_dm %>.% # étape 1 de jointure inutile avec un objet dm
  dm_filter(., who, year >= 2000 & year < 2010 & sex == "f" & age %in% c("2534", "3544", "4554")) %>.% # étape 2 filtrage
  dm_flatten_to_tbl(., who) %>.% # Réduction en une seule table
  # Le traitement ci-dessous est identique à celui dans R,
  # sauf pour la gestion des pays ne rencontrant aucun cas !
  group_by(., country, year) %>.% # étape 3 regroupement par pays et année
  summarise(., total = first(population), all_new = sum(new_cases, na.rm = TRUE)) %>.% # étape 4 somme des cas
  mutate(., prevalence = all_new / total) %>.% # étape 5, calcul de la prévalence
  group_by(., country) %>.% # étape 6 regroupement par pays
  summarise(., mean_prev = mean(prevalence, na.rm = TRUE)) %>.% # étape 7 prévalence moyenne
  # étape supplémentaire nécessaire ici : éliminer les pays où mean_prev est NA
  filter(., !is.na(mean_prev)) %>.% # drop_na() ne fonctionne pas ici
  slice_max(., mean_prev, n = 10) # étape 8 garder les 10 plus élevés
# `summarise()` has grouped output by "country". You can override using the
# `.groups` argument.
# # Source:   SQL [10 x 2]
# # Database: DuckDB v0.10.1 [root@Darwin 23.6.0:R 4.3.3//Users/phgrosjean/Documents/GitHub/BioDataScience-Course/sdd-umons2-2024/duckdb_test.db]
#    country      mean_prev
#    <chr>            <dbl>
#  1 South Africa  0.000971
#  2 Swaziland     0.000807
#  3 Namibia       0.000726
#  4 Lesotho       0.000674
#  5 Zimbabwe      0.000627
#  6 Botswana      0.000617
#  7 Cambodia      0.000382
#  8 Zambia        0.000375
#  9 Kiribati      0.000353
# 10 Djibouti      0.000344

Si nous comparons ce résultat avec ce que nous avions obtenu plus haut en utilisant sum() ou fsum(), nous voyons que le résultat est identique à la version fsum() qui considère qu’il y a bel et bien un manque de données s’il y a des NA partout pour un pays et une année. C’est typiquement le traitement réalisé par une base de données qui peut différer, on le voit, de celui de R de base (version sum()). Faites bien attention aux cas particuliers et à la façon dont les valeurs manquantes sont traitées lorsque vous passez d’un code R à un code base de données et vice versa !

Une fois que nous avons terminé avec notre base de données, n’oublions pas de nous déconnecter.

# Déconnexion et nettoyage de la base de données
dbDisconnect(con, shutdown = TRUE)
# Seulement pour effacer définitivement la base de données !
# NE PAS utiliser avec une "vraie" base de données !!!
unlink("duckdb_test.db")
À vous de jouer !

Effectuez maintenant les exercices du tutoriel B09La_db (Bases de données et data model).

BioDataScience2::run("B09La_db")
Pour en savoir plus