Les données pour le journalisme quantitatif, qu'elles soient le produit de scraping ou issues d'institutions, sont souvent défectueuses. Des variations de format (point ou virgule pour la marque décimale), de type ('1' est un caractère, tandis que 1 est de type numérique), les espaces en fin de champ ou mélangés aux tabulations, les champs nuls et autres coquilles rendent leur exploitation statistique ou cartographique hasardeuse.
Le tableur est assez limité mais néanmoins utile et en tous cas nécessaire pour préparer les fichiers. Les outils en ligne gratuits, comme Table Fusion, impliquent un partage des données avec le fournisseur et une dépendance à son égard. On va voir ici comment les systèmes de gestion de bases de données relationnelles (SGBDR) peuvent aider.
L'idée est de créer des tables temporaires pour accueillir les données à valider, avant de les intégrer dans des tables définitives, qui auront toutes les contraintes référentielles voulues. Les exemples sont donnés ici avec PostgreSQL mais ils devraient fonctionner sur d'autres SGBDR.
Contraintes de type
On a travaillé sur le fichier des entités statistiques LAU2 de Eurostat. Le tableau, un fichier bureautique, a été enregistré sans les entêtes de colonnes et au format TSV (valeurs séparées par des tabulations), avec l'encodage de caractères de la base (UTF-8, ici). Avant d'importer ce fichier dans la base, on doit créer une table pour l'accueillir. Il faut réfléchir au type de données que contiendra chaque colonne (chaîne de caractères, de longueur fixe ou variable, numériques, entiers ou à virgule, avec quelle précision, date, booléen ... ). Le plus contraignant sera le mieux.
CREATE TABLE lau2_2010 (
idnuts3 char(5), /* les identifiants NUTS3 font exactement cinq caractères*/
lau1 varchar(15), /*les identifiants LAU1 font au maximum 15 caractères*/
lau2 varchar(15),
nom varchar, /* les noms sont de longueur variable et non spécifiée - ce dernier point est une particularité de PostgreSQL*/
area numeric
) ;
L'opération de copie du fichier TSV échoue si une seule ligne viole une contrainte. En général, l'erreur attire l'attention sur un problème commun à de nombreuses autres lignes que l'on doit résoudre avec le tableur. Une difficulté rencontrée tenait à la variété des indicateurs de données absentes, tantôt n.c.a., n.c.a, NA, etc. Sur un fichier de près de 120000 lignes, il est improbable de les déceler dès la première lecture. Les données non existantes doivent être traduites par \N pour être admises dans Postgres. Attention, le tableur peut ajouter des erreurs, au moment de l'ouverture du fichier source (s'il n'est pas dans son format naturel). On a vu 00AM et 00PM, qui sont des codes en vigueur au Royaume-Uni, interprétés en un format d'heures : minuit et midi. Même chose pour 95E15, vu comme la notation du nombre 95000000000000000.
Une fois les contraintes de type respectées, l'opération copie les 120000 lignes en deux secondes.
\copy into essai_temp from /chemin/vers/fichier.tsv
À la recherche du schéma logique
Une fois la table peuplée, on peut rechercher des incohérences plus subtiles et chercher à comprendre le schéma logique de la table. La recherche des champs vides et les clés candidates sont au menu.
SELECT idnuts3,lau1,nom FROM lau2_2010
WHERE lau2 IS NULL;
SELECT idnuts3,lau2,nom FROM lau2_2010
WHERE lau1 IS NULL
AND lau2 IS NOT NULL;
Ces requêtes renvoient les lignes dont LAU2 est vide et dont LAU1 est vide et pas LAU2, respectivement. On comprend ainsi que certains pays possèdent des entités LAU1 qui ne contiennent aucune LAU2 (la Grèce et le Danemark), tandis que d'autres ont rangé des entités LAU2 directement sous les NUTS3, sans LAU1. Cette table est sans doute une vue, une table virtuelle produite par une requête, facile à exploiter mais pas idéale pour organiser les données dans la base. Cette table pourrait être renormalisée, décomposée en tables dépourvues de redondances ou de champs nuls.
On cherche si un identifiant est unique au niveau européen, ce qui sera bien utile pour faire des jointures spatiales dans un logiciel de cartographie. L'identifiant LAU2 est-il unique au niveau européen ? Une requête à auto-jointure affiche les lau2 communs à des entités dont les noms diffèrent.
SELECT L1.nom,L1.lau2,L2.nom
FROM lau2_2010 AS L1
JOIN lau2_2010 AS L2
ON L1.lau2 = L2.lau2
WHERE L1.nom < L2.nom;
La liste renvoyée n'est pas nulle, elle est même longue (21178 entités). LAU2 ne peut être retenu comme identifiant unique, même en ignorant les lignes où il est vide.
Le couple idnuts3-lau2 est-il alors une clé candidate ? Cherchons s'il existe plusieurs noms pour ce couple d'attributs.
La directive GROUP BY regroupe les lignes avec des attributs communs. C'est très utile pour faire des statistiques mais aussi pour trouver d'éventuels doublons dans une table brute d'importation.
SELECT L1.idnuts3,L1.lau2
FROM (
SELECT * FROM lau2_2010
WHERE lau1 IS NOT NULL
AND idnuts3 IS NOT NULL
) AS L1
GROUP BY L1.idnuts3,L1.lau2
HAVING count (*) > 1
;
La requête montre les pays qui n'ont pas d'entités lau2 (Danemark et Grèce), ce qui est attendu, mais aussi une ligne, qui s'avère être un doublon. Une ligne fautive sur 120000, introduite lors des manipulations sur le tableur ou peut-être d'origine Eurostat.
Une autre requête confirme, par son résultat vide, que le couple idnuts3-lau2 est un identifiant possible, pour les lignes où lau2 n'est pas vide. Ici, on compare les noms.
SELECT R1.idnuts3,R1.lau2,R1.nom AS nom1,R2.nom AS nom2
FROM (
SELECT * FROM lau2_2010 WHERE lau2 IS NOT NULL
AND idnuts3 IS NOT NULL
) AS R1
JOIN (
SELECT * FROM lau2_2010 WHERE lau2 IS NOT NULL
AND idnuts3 IS NOT NULL
) AS R2
ON R1.idnuts3 = R2.idnuts3
WHERE R1.lau2 = R2.lau2
AND R1.nom < R2.nom
;
idnuts3 | lau2 | nom1 | nom2
---------+------+------+------
(0 ligne)
Au croisement des données
Une jointure sur une autre table, qui recense les entités NUTS niveau 1, 2 et 3, met en évidence trois identifiants NUTS3 non répertoriés.
SELECT DISTINCT idnuts3
FROM lau2_2010
WHERE idnuts3 NOT IN (
SELECT nuts_id FROM nuts2010
);
idnuts3
---------
N_A
DEZZZ
FR215
(3 lignes)
N_A est encore une variante pour un champ vide, qui a échappé au tableur. DEZZZ désigne une 'Extra-Regio NUTS 3', ligne absente de la table nuts2010 et que l'on rajoute. FR215 est sans doute une coquille. Les communes françaises sous cet identifiant sont situées en Haute-Marne, soit FR214, dans la version 2010 de NUTS3. On rétablit la situation avec :
UPDATE lau2_2010
SET idnuts3 = 'FR214'
WHERE idnuts3 = 'FR215' ;
Des fonctions statistiques simples du SGBD permettent des vérifications croisées entre plusieurs sources. Avec une requête, on a calculé la surface des entités NUTS3, à partir de celle des entités LAU2 qui les constituent, et la densité de peuplement de chaque NUTS3, à partir d'une jointure sur une table de population d'origine Eurostat. En fin de requête, on demande un classement pour montrer les entités les plus densément peuplées en haut de la liste.
SELECT surf.nuts_id,surf.nom,round(1000000*(population/surface)) AS densite
FROM (
SELECT n10.nom,n10.nuts_id,sum(l10.area) AS surface
FROM (
SELECT * FROM nuts2010 EXCEPT (
SELECT * FROM nuts2010
WHERE niveau =3 AND nuts_id IN (
SELECT idnuts3 FROM lau2_2010 WHERE area IS NULL
))) AS n10
JOIN lau2_2010 AS l10
ON l10.idnuts3=n10.nuts_id
GROUP BY n10.nuts_id,n10.nom) AS surf
JOIN pop ON pop.nuts_id = surf.nuts_id
WHERE pop.population IS NOT NULL
GROUP BY surf.nuts_id,surf.nom,surf.surface,pop.population
ORDER BY densite DESC
LIMIT 50;
Le problème est apparu immédiatement. Des régions néerlandaises étaient bien plus denses que Paris (qui a plus de 21000 hab/km2). L'anomalie touchait aussi la Suède. Pour ces deux pays, la surface était exprimée en ha et non en m2, comme pour le reste de la base. Un petit correctif a remis les bons ordres de grandeur.
UPDATE lau2_2010
SET area = area * 10000
WHERE idnuts3 LIKE 'SE%' OR idnuts3 LIKE 'NL%';
Enfin, la comparaison des surfaces et des densités obtenues avec d'autres sources, comme Wikipedia, a permis de s'assurer de la cohérence de la base.
Voilà, ces quelques exemples montrent comment une base SQL peut aider une rédaction, un journaliste, à valider un grand ensemble de données.
