9.1 Accès aux bases de données
Puisque nous traitons de jeux de données multivariés 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, par exemple. 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.
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 qui offrent des fonctions intéressantes et/ou des performances inégalées dans certaines applications, notamment DuckDB que nous avons déjà utilisé au module précédent et que nous continuerons à utiliser 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 différentes bases de données, RStudio offre aussi une interface plus intuitive via l’onglet Connections 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. 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 Connections
- Cliquer sur le bouton New Connection
- 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 New R Script dans la section Connect from:
- 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 R Markdown). Exécutez ce code et votre session R est connectée à la base de données.
À vous de jouer !
dbListTables(con) pour lister les tables disponibles et dbListFields(con, "table_name") pour lister les champs dans une table nommée table_name.
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é de DuckDB qui inclut le serveur de base de données 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}.
# 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 : int 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 ...
# $ new_sp_m014 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_m1524: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_m2534: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_m3544: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_m4554: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_m5564: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_m65 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_f014 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_f1524: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_f2534: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_f3544: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_f4554: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_f5564: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sp_f65 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_m014 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_m1524: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_m2534: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_m3544: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_m4554: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_m5564: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_m65 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_f014 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_f1524: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_f2534: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_f3544: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_f4554: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_f5564: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_sn_f65 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_m014 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_m1524: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_m2534: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_m3544: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_m4554: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_m5564: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_m65 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_f014 : int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_f1524: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_f2534: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_f3544: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_f4554: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_f5564: int NA NA NA NA NA NA NA NA NA NA ...
# $ new_ep_f65 : int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_m014 : int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_m1524: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_m2534: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_m3544: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_m4554: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_m5564: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_m65 : int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_f014 : int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_f1524: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_f2534: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_f3544: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_f4554: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_f5564: int NA NA NA NA NA NA NA NA NA NA ...
# $ newrel_f65 : int 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># Classes 'data.table' and 'data.frame': 4060 obs. of 3 variables:
# $ country : chr "Afghanistan" "Afghanistan" "Afghanistan" "Afghanistan" ...
# $ year : int 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 ...
# $ population: int 17586073 18415307 19021226 19496836 19987071 20595360 21347782 22202806 23116142 24018682 ...
# - 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. De plus, 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 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).
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 :
- fusion des tableaux
whoetpop, - filtrage des données pour ne conserver que les années égales ou supérieures à 2000 et inférieures à 2010,
- filtrage des données pour ne conserver que les femmes entre 25 et 54 ans,
- somme de nouveaux cas de tuberculose, quelles que soit la méthode de détection et la classe d’âge,
- calcul des proportions, soit la somme de cas divisés par la population totale, variable nommée
prevalence, - regroupement par pays,
- résumé des prévalences moyennes par pays, toutes années confondues,
- tri de ces prévalences moyennes par ordre décroissant,
- 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”.
# [1] 7240 60# [1] 4060 3who %>.%
left_join(., pop) %>.% # étape 1 fusion
filter(., year >= 2000 & year < 2010) %->% # étape 2 filtrage années 2000-2010
who2# Joining, by = c("country", "year")# [1] 2133 61Nous 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# [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)) %>.%
collect_dtx(.) %>.%
dtx(country = who3$country, year = who3$year, population = who3$population, all_new = .$all_new) %->%
who4
dim(who4)# [1] 2133 4# [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
collect_dtx(.) %>.%
head(., 10) # étape 9 seulement les 10 premiers pays# # A data.table: 10 x 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.000338Ouf ! 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 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).
who %>.%
pivot_longer(., starts_with("new"), names_to = "type", values_to = "new_cases") %->%
whol
dim(whol)# [1] 405440 6# # A data.table: 6 x 6
# country iso2 iso3 year type new_cases
# <chr> <chr> <chr> <int> <chr> <int>
# 1 Afghanistan AF AFG 1980 new_sp_m014 NA
# 2 Afghanistan AF AFG 1981 new_sp_m014 NA
# 3 Afghanistan AF AFG 1982 new_sp_m014 NA
# 4 Afghanistan AF AFG 1983 new_sp_m014 NA
# 5 Afghanistan AF AFG 1984 new_sp_m014 NA
# 6 Afghanistan AF AFG 1985 new_sp_m014 NANotez que notre tableau long possède plus de 400.000 lignes. Nous devons encore modifier le tableau afin d’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() que vous n’avez probablement pas encore utilisé. 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 :
# [1] "new_ep"# [1] "f"# [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 data.table: 6 x 8
# country iso2 iso3 year new_cases method sex age
# <chr> <chr> <chr> <int> <int> <chr> <chr> <chr>
# 1 Afghanistan AF AFG 1980 NA new_sp m 014
# 2 Afghanistan AF AFG 1981 NA new_sp m 014
# 3 Afghanistan AF AFG 1982 NA new_sp m 014
# 4 Afghanistan AF AFG 1983 NA new_sp m 014
# 5 Afghanistan AF AFG 1984 NA new_sp m 014
# 6 Afghanistan AF AFG 1985 NA new_sp m 014Notre 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 :
- fusion des tableaux
wholetpop, - filtrage des données pour ne conserver que les années >= 2000 e < 2010, et que les femmes entre 25 et 54 ans,
- regroupement par pays et par année,
- somme des nouveaux cas de tuberculose par pays et par année,
- calcul des proportions, soit la somme de cas divisés par la population totale, variable nommée
prevalence, - regroupement par pays,
- résumé des prévalences moyennes par pays, toutes années confondues,
- 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
collect_dtx(.)# Joining, by = c("country", "year")
# `summarise()` has grouped output by 'country'. You can override using the
# `.groups` argument.# # A data.table: 10 x 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.0003389.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.
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 du tableau). Il s’agit ici de reformatter (é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
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.
#
# Attachement du package : 'dm'# L'objet suivant est masqué depuis 'package:MASS':
#
# select# L'objet suivant est masqué depuis 'package:stats':
#
# filter# création d'un objet dm qui reprend le schéma de la base
who_dm <- dm_from_src(con) %>.%
dm_set_colors(., red = who, darkgreen = pop) # Couleurs pour les tables (pour le schéma)# Keys could not be queried, use `learn_keys = FALSE` to mute this message.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) :
# # 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é dans chaque table : nos clés primaires composites.
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 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 enregistrement 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 quatres 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.
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, et 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, ou zéro à un et zéro à plusieurs qui permet aussi d’avoir des vlaeurs pour la clé primaire qui ne se retrouvent pas dans la clé étrangère. Sinon, toutes doivent être représentées. Vérifions donc si nous avons une cardinalité zéro ou un à plusieurs ici (remarque : les tableaux doivent être transformés en tibble sinon un bug empêche le calcul de se faire correctement).
# # A tibble: 10 × 8
# country iso2 iso3 year new_cases method sex age
# <chr> <chr> <chr> <int> <int> <chr> <chr> <chr>
# 1 Afghanistan AF AFG 1980 NA new_sp m 014
# 2 Afghanistan AF AFG 1981 NA new_sp m 014
# 3 Afghanistan AF AFG 1982 NA new_sp m 014
# 4 Afghanistan AF AFG 1983 NA new_sp m 014
# 5 Afghanistan AF AFG 1984 NA new_sp m 014
# 6 Afghanistan AF AFG 1985 NA new_sp m 014
# 7 Afghanistan AF AFG 1986 NA new_sp m 014
# 8 Afghanistan AF AFG 1987 NA new_sp m 014
# 9 Afghanistan AF AFG 1988 NA new_sp m 014
# 10 Afghanistan AF AFG 1989 NA new_sp m 014# Error in `abort_not_subset_of()`:
# ! Columns (`country`, `year`) of table `as_dtbl(whol)` contain values (see examples above) that are not present in columns (`country`, `year`) of table `as_dtbl(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 :
# [1] 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009
# [16] 2010 2011 2012 2013# [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 2013Les 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…
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] FALSEAïe, quels sont les pays non repris ?
# [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 ?
# [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] TRUECette 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().
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 whol_dm après avoir remplacé la table “who”.
# Effacement de la table "whol"
dbRemoveTable(con, "who")
dbWriteTable(con, "who", whol1995)
# Data model
who_dm <- dm_from_src(con) %>.%
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# Keys could not be queried, use `learn_keys = FALSE` to mute this message.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é 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() :
# [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 “whol”, 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 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étyecter 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 data.table: 6 x 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 AGONous allons éliminer les colonnes “iso2” et “iso3” dans la table “who” et ajouter la talbe “countries” ainsi que la clé primaire et les clés étrangères utiles. Au lieu de passer son temps à effecter toute la table “who” et la recréer, nous allons ici utiliser une commande SQL pour effacer les deux colonnes.
# [1] 0# [1] 0# [1] "country" "year" "new_cases" "method" "sex" "age"Ensuite, nous ajoutons la table “countries” :
# [1] "countries" "pop" "who"Enfin, nous ajustions le schéma en ajoutant les clés nécessaires (il faut recommencer le dm) :
dm_from_src(con) %>.%
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# Keys could not be queried, use `learn_keys = FALSE` to mute this message.Nous pourrions être tenté de rajouter une clé étrangère sur “country” entre les tables “pop” et “countries” également. Cependant, cela définirait une relation cyclique ente les trois tables que l’objet dm ne peut pas encore gérer. Nous l’avons donc mise en commentaire dans le code. Voilà notre modèle de données qui répond 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) :
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 via 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”. c’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
head(who_flat)# # A data.table: 6 x 9
# country year new_cases method sex age iso2 iso3 population
# <chr> <int> <int> <chr> <chr> <chr> <chr> <chr> <int>
# 1 Afghanistan 2000 565 new_sp f 2534 AF AFG 20595360
# 2 Afghanistan 2001 888 new_sp f 2534 AF AFG 21347782
# 3 Afghanistan 2002 1251 new_sp f 2534 AF AFG 22202806
# 4 Afghanistan 2003 1287 new_sp f 2534 AF AFG 23116142
# 5 Afghanistan 2004 1561 new_sp f 2534 AF AFG 24018682
# 6 Afghanistan 2005 1959 new_sp f 2534 AF AFG 24860855à 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 !
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 garder les 10 plus élevés# `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.000338Une 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)
unlink("duckdb_test.db") # Seulement pour effacer définitivement la base de données!À 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
RStudio et bases de données : tout un site web dédié à l’accès aux bases de données depuis RStudio (en anglais).
Explication en anglais sur la normalisation des données avec un exemple simple.
Un autre tutoriel sur la normalisation des données en anglais.
Site web du package {dm} en anglais. Il y a queqlques documents techniques qui explisuent les différents aspects de ce package riche en fonctions.
Une introduction aux requêtes SQL dans R (en anglais).
Un tutoriel SQL avec des exercices (en anglais).
Un cours en ligne sur SQL par vidéos par la Kahn Academy (en anglais).
Un autre tutoriel complet sur SQL, en français cette fois-ci. Remarquez qu’il en existe beaucoup. Faites une recherche via Google et choisissez le tutoriel qui vous plaît le plus.
Les dix commandements d’une base de données réussie. Il s’agit ici plutôt de la création d’une base de données que de la requête sur une base de données existante… mais tôt ou tard, vous créerez vos propres bases de données et ces conseils vous seront alors utiles.