TP Nettoyage de données

Ce TP a pour but de se confronter à des problèmes que l’on peut recontrer lors de l’intégration d’un jeu de données au sein d’une base: compréhension des données et correction.

Remarque: ce TP peut s’effectuer sur la base de donnée PostgreSQL du département doc.

Rendu

Ce TP est à rendre pour le 18 octobre 2020. Le rendu consistera en un rapport contenant le code SQL commenté. Le fichier SQL est à déposer sur tomuss dans la case rendu_tp2_nd de l’UE UE-INF2338M Interopérabilité. Bien penser à mettre au début du fichier vos noms, prénoms et numéros d’étudiants. Ce TP peut-être fait en binôme, trinômes ou plus interdits.

Il est demandé d’essayer de produire un code SQL le plus général possible. En particulier, on évitera au maximum d’utiliser des valeurs écrites à la main dans le code.

Données

On considère les données concernant les subventions de l’état aux associations, disponibles sur www.data.gouv.fr. Dans le cadre de ce TP, on se concentrera sur les données des subventions 2016 rendues disponibles dans le cas projet de loi de finances (PLF) 2018.

Fichier csv à télécharger: copie locale ou www.data.gouv.fr.

Le code suivant permet de créer une table postgresql afin d’y importer les données csv brutes:

CREATE TABLE s2016 (
	"Programme 2016" DECIMAL NOT NULL,
	"SIREN" VARCHAR NOT NULL,
	"NIC" VARCHAR,
	"Dénomination" VARCHAR NOT NULL,
	"Montant" DECIMAL NOT NULL,
	"Objet" VARCHAR NOT NULL,
	"Parlementaire" VARCHAR,
	"Réserve 2016" VARCHAR,
	"Convention 2016" VARCHAR,
	"COG : code département" VARCHAR,
	"COG : code commune" DECIMAL,
	"COG : ville ou pays" VARCHAR NOT NULL,
	"Nomenclature juridique" DECIMAL NOT NULL,
	"Code NAF" VARCHAR NOT NULL,
	"Situation SIRENE" VARCHAR,
	"RNA" VARCHAR
);

Le code suivant permet d’insérer les données du csv via le shell psql:

\copy s2016 from projet-de-loi-de-finances-pour-2018-plf-2018-donnees-de-lannexe-jaune-effort-fin.csv csv delimiter ';' encoding 'utf-8' header;

On pourra renommer les attributs de la table afin de simplifier l’écriture des requêtes dans la suite du TP.

Nettoyage des données numériques

Certaines données devraient être des données numériques mais les attributs correspondant ont une valeur textuelle.

Comprendre pourquoi c’est le cas et corriger les données afin de pouvoir changer le type dans un type numérique

Aide: documentation sur les expressions rationnelles

Regarder les différentes valeurs de l’attribut “Réserve 2016” et faire les modifications nécessaires pour en faire un attribut booléen.

Schéma des données

Essayer de comprendre un peu les données, on pourra s’aider de la version excel disponible ici comprenant des onglet sur la signification de différents codes utilisés.

Vérifier qu’il existe des lignes qui ne diffèrent que par le montant de la subvention.

Expliquer pourquoi, du point de vue métier, le montant de la subvention n’est cependant pas un attribut qui devrait faire partie de la clé.

Essayer de deviner quelles dépendances fonctionnelles devraient être satisfaites dans la table.

Vérifier la satisfaction de ces dépendances.

Pour les dépendances non satisfaites, déterminer si les cas de violation sont dus à des erreurs. Corriger ces erreurs au besoin.

Sans prendre en compte l’attribut “Parlementaire”, proposer un schéma relationnel pour représenter les données, créer les tables et y insérer les données.

Il sera peut-être nécessaire d’ajouter des identifiants artificiels, en particulier pour distinguer des subventions faites à une même association pour un même objet. Il est possible d’utiliser des séquences pour générer ces identifiants.

Traitement des parlementaires

L’attribut parlementaire n’est pas en première forme normale (plusieurs valeurs sont dans un même attribut).

Comprendre en regardant les données le type d’information contenu dans cette colonne, modifier votre schéma relationnel ainsi que vos scripts d’insertion pour y remédier.

Aide: la fonction regexp_split_to_table peut être utile ici.

Bonus: on peut constater des incohérences sur la manière dont est saisi le nom des parlementaires. En utilisant la source du Fichier historique des Députés et de leurs mandats, corriger les données concernées. Il sera probablement nécessaire d’utiliser les fonctions de manipulation JSON de PostgreSQL.

Pour aller plus loin

Cette partie constitue une ouverture et n’est pas à rendre.

Les données de subventions aux associations sont disponibles à partir de l’année 2010 (PLF 2012), les CSV sont les suivants: 2010, 2011, 2012, 2013, 2014, 2015, 2016.

En regardant ces fichiers, on peut constater que leur schéma évolue d’année en année. Si l’on souhaite analyser ces subventions sur plusieurs années, il faut intégrer ces informations dans le schéma précédent.