Nettoyage des données en SQL

Le nettoyage des données est une partie importante de toute analyse de données. Nous discuterons ici des techniques que vous pouvez utiliser pour nettoyer les données en SQL.

Il m'est pratiquement impossible de me concentrer sur mon travail lorsque mon bureau est en désordre. S'il est encombré de papiers, de tasses à café ou de jouets que ma fille a glissés dans mon bureau, il n'y a aucune chance que je parvienne à faire quoi que ce soit tant que mon bureau ne sera pas remis en ordre. Pour une raison ou pour une autre, c'est comme si le désordre de mon bureau s'était frayé un chemin jusqu'à mon esprit.

Cette même idée s'applique au nettoyage des données. Il m'est souvent arrivé de passer des heures sur une analyse et de tirer mes conclusions pour finalement découvrir une incohérence dans les données qui réduit à néant l'ensemble de mon rapport. En tant qu'analystes, nous nous lançons généralement dans l'analyse des données sans prendre le temps de nous assurer que nos données sont propres. Cela peut entraîner de nombreuses heures de perte de temps ou, pire encore, des rapports inexacts.

Qu'est-ce que le nettoyage des données ?

Le processus de nettoyage des données (également appelé "data cleansing") consiste à identifier les inexactitudes dans un ensemble de données et à les corriger. C'est la première étape de toute analyse et elle comprend la suppression de données, la mise à jour de données et la recherche d'incohérences ou de choses qui n'ont tout simplement pas de sens.

Vous pouvez apprendre toutes les fonctionnalités SQL nécessaires pour nettoyer les données en SQL dans notre Le SQL de A à Z piste. La formation contient 7 cours SQL interactifs qui vous apprendront tout le langage SQL, depuis les bases jusqu'aux concepts SQL avancés tels que les fonctions de fenêtre et les requêtes récursives, en passant par les sujets intermédiaires. Il s'agit de l'ensemble de cours SQL le plus complet disponible sur Internet.

Techniques de nettoyage des données

Maintenant que vous avez saisi l'idée, examinons les techniques SQL que vous pouvez utiliser pour nettoyer les données. Pour chaque exemple, nous utiliserons le tableau des entreprises présenté ci-dessous. Elle contient des informations sur différentes entreprises :

Comment et quand supprimer des données

Vous rencontrerez parfois des scénarios dans lesquels vous devrez supprimer des données de votre ensemble de données. Cela peut être dû au fait que les données ne sont pas pertinentes pour ce que vous analysez ou qu'elles sont dupliquées ou inexactes. Dans les exemples suivants, nous allons explorer ces différents scénarios et la manière de les aborder.

Exemple 1 : Suppression des données en double

La première chose à faire est de rechercher les données qui doivent être supprimées. Il peut s'agir de doublons ou de données non pertinentes. Dans ce tableau, nous pouvons rapidement constater que la ligne correspondant à l'entreprise Toughtam est en double. Ce n'est pas si facile à identifier dans un grand ensemble de données. Avant de supprimer cette ligne, examinons comment nous pourrions la trouver.

Dans cet ensemble de données, chaque entreprise ne devrait avoir qu'une seule ligne, alors utilisons les clauses GROUP BY et HAVING pour identifier les noms en double. Cette requête va compter le nombre de fois que chaque nom existe dans la base de données en utilisant GROUP BY. Elle utilise ensuite la clause HAVING pour filtrer les résultats en ne retenant que les noms qui existent plus d'une fois.

SELECT name, 
	 COUNT(name) as count
FROM companies
GROUP BY name
HAVING(count > 1)

Cette requête renvoie le résultat suivant :

Génial ! Nous savons maintenant que l'entreprise Toughtam est dupliquée, mais comment supprimer l'une des lignes ? Nous allons utiliser une combinaison de ROW_NUMBER() et DELETE. Tout d'abord, ajoutons un numéro de ligne pour chaque ligne en fonction de la colonne nom :

SELECT name, 
	 ROW_NUMBER() OVER(PARTITION BY name) AS rn
FROM companies

Ce que nous avons fait, c'est ajouter une nouvelle colonne qui indique le numéro de ligne pour chaque nom. Comme vous pouvez le voir, il y a maintenant un 1 et un 2 pour les lignes de Toughtam. Nous allons maintenant exécuter une instruction DELETE pour supprimer toutes les lignes dont la colonne rn est supérieure à 1.

DELETE 
FROM (
SELECT name, 
	 	ROW_NUMBER() OVER(PARTITION BY name) AS rn
FROM companies
) 
WHERE rn > 1

Notre ensemble de données ressemble maintenant à ceci :

Exemple 2 : Ordonner les données avant de les supprimer

Dans cet exemple, les lignes pour Toughtam sont les mêmes (à l'exception de id), nous n'ordonnons donc pas les lignes autrement que par leur apparence dans la base de données. Il arrive souvent que des lignes soient dupliquées, mais que les champs ne soient pas identiques. Dans ce cas, vous pouvez ajouter une clause ORDER BY après la clause PARTITION BY.

Par exemple, supposons que les données ressemblent à ceci :

Il semble que cet enregistrement ait été mis à jour pour cette entreprise le 20/01/2023 et que le nombre d'employés ait augmenté. Si nous voulions conserver l'enregistrement le plus récent, nous exécuterions :

DELETE 
FROM (
SELECT name, 
	 	ROW_NUMBER() OVER(PARTITION BY name ORDER BY created DESC) AS rn
FROM companies
) 
WHERE rn > 1

Comme vous pouvez le voir, nous classons maintenant la déclaration ROW_NUMBER() par ordre décroissant de la rubrique created, en tirant d'abord l'enregistrement le plus récent. Nous supprimons ensuite les enregistrements qui suivent le premier.

Pour plus d'informations sur la recherche de valeurs dupliquées en SQL, consultez notre article Comment trouver des valeurs dupliquées en SQL.

Exemple 3 : Suppression des valeurs NULL

Examinons maintenant la valeur NULLs. NULL indique une valeur manquante ; vous pouvez en savoir plus à ce sujet ici. En fonction des implications des valeurs NULL dans vos données, vous pouvez soit supprimer ces lignes, soit les mettre à jour. Dans notre exemple, nous voyons deux valeurs NULL. Une ligne a une valeur NULL pour l'industrie et l'autre pour la valeur year_founded. Nous allons traiter chacune d'entre elles d'une manière différente.

Une entreprise doit avoir une année de création. Nous allons utiliser DELETE pour supprimer cette ligne lorsqu'elle est manquante, puisqu'il semble s'agir de mauvaises données.

SELECT
FROM companies 
WHERE year_founded IS NULL 

Dans la requête ci-dessus, nous utilisons la clause IS NULL. Cette clause examine la colonne year_founded et renvoie toutes les lignes où il y a IS NULL. Une fois que nous avons vérifié qu'il s'agit bien de la ligne que nous voulons supprimer, nous pouvons la supprimer en exécutant la commande :

DELETE
FROM companies 
WHERE year_founded IS NULL 

À ce stade, nous avons fini de supprimer les mauvaises données et nous sommes prêts à passer à l'instruction UPDATE. Utilisons-la pour corriger notre autre valeur NULL.

Comment mettre à jour des données

L'instruction UPDATE est utilisée pour modifier des données existantes. Vous utiliserez cette technique de nettoyage des données pour corriger des données inexactes ou pour formater vos données (les rendre plus lisibles). Dans les prochains exemples, nous verrons ces types de scénarios pour comprendre comment manipuler les données à l'aide de l'instruction UPDATE.

Exemple 1 : Mettre un libellé significatif pour les valeurs NULLES

Comme nous l'avons déjà vu, une entreprise a une valeur NULL pour l'industrie. Cela ne nous pose pas de problème car, dans notre situation hypothétique, nous savons que notre base de données ne contient pas toutes les branches d'activité. Ce qu'il faut faire dans ce cas, c'est mettre à jour la valeur NULL en la remplaçant par "Other" (Autre) ou en remplaçant NULLs par "NA" (Not Applicable).

Tout d'abord, utilisons SELECT pour extraire la ligne contenant l'industrie NULL:

SELECT *
FROM companies 
WHERE industry IS NULL 

Maintenant que nous savons que nous avons extrait la bonne ligne, nous pouvons UPDATE la colonne industrie. Nous le ferons en exécutant :

UPDATE companies 
SET industry = ‘Other’
WHERE industry IS NULL 

Lorsque nous utilisons UPDATE, la première chose à faire est d'identifier la table que nous voulons modifier. Dans notre exemple, cette table est companies. Ensuite, nous devons indiquer la colonne que nous mettons à jour et ce vers quoi nous la mettons à jour. Nous identifions la colonne en utilisant SET [column name]. Ensuite, nous définissons ce que nous voulons changer dans la colonne en utilisant = [ value ]. La clause WHERE est la même que si nous écrivions une déclaration SELECT. Nous ne voulons remplacer le secteur d'activité par "Autre" que si le secteur d'activité est IS NULL.

Exemple 2 : Correction de la mise en majuscule des valeurs

À ce stade, nos données se présentent mieux, mais la colonne state aurait besoin d'un peu de nettoyage. Certaines valeurs sont en majuscules et d'autres en minuscules. En règle générale, l'abréviation de l'État est en majuscule, alors mettons toutes les valeurs en minuscules en majuscules.

Puisque nous voulons nous assurer que toutes les valeurs d'état sont en majuscules, nous pouvons exécuter :

UPDATE companies 
SET state = UPPER(state)

Lorsque vous enroulez l'instruction UPPER() autour d'un nom de colonne, vous mettez toutes les lettres en majuscules. (Avec l'instruction LOWER(), vous faites l'inverse - vous changez toutes les lettres en minuscules). Notre tableau ressemble maintenant à ceci :

Exemple 3 : Corriger les erreurs logiques

Une dernière chose que vous voudrez rechercher dans votre ensemble de données, ce sont les erreurs logiques. Dans nos données, nous voyons qu'une entreprise a une valeur year_founded de 2032. Ce n'est tout simplement pas possible, car une entreprise ne peut pas être fondée dans le futur. Nous pouvons identifier les enregistrements datés du futur en exécutant :

SELECT *
FROM companies 
WHERE year_founded > CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP() renvoie la date et l'heure actuelles. Dans l'énoncé ci-dessus, nous extrayons tous les enregistrements pour lesquels year_founded est postérieur à la date d'exécution de la requête. Vous pouvez également coder en dur une date, par exemple la date d'aujourd'hui.

Après quelques recherches, nous constatons qu'il s'agit d'une faute de frappe et que l'entreprise a été créée en 2012 :

UPDATE companies
SET year_founded = 2012
FROM companies 
WHERE id = 3

Dans ce scénario, nous avons spécifié un identifiant spécifique dans la clause WHERE. En effet, il est très probable que vous ne souhaitiez pas mettre à jour toutes les valeurs de year_founded pour qu'elles correspondent à la même année pour toute entreprise dont l'année de création se situe dans le futur. Nous connaissons l'année de création de cette entreprise spécifique, nous ne mettrons donc à jour que cet enregistrement.

N'oubliez jamais de nettoyer vos données

Nos données sont désormais beaucoup plus claires que l'ensemble de données d'origine. Le nettoyage des données, bien que fastidieux, est un élément essentiel du processus d'analyse des données. Ne partez jamais du principe que les données avec lesquelles vous travaillez sont propres. Explorez les données en recherchant les doublons, NULLs, et toute erreur logique.

Vous connaissez maintenant plusieurs techniques SQL que vous pouvez utiliser pour modifier vos données, notamment DELETE et UPDATE. L'étape suivante consiste à prendre la Le SQL de A à Z de l'Université d'Ottawa. Elle contient 7 cours interactifs sur le langage SQL, y compris un cours complet sur les clauses DELETE, UPDATE et INSERT. Il s'agit de l'ensemble de cours SQL le plus complet disponible sur Internet. Cette piste vous aidera à renforcer tout ce que vous avez appris dans cet article et vous donnera l'occasion de vous exercer davantage. Bon nettoyage des données !