Nettoyage de grands jeux de données avec SQL
Le guide théorique complémentaire à ce cas pratique.
Dataset brut de 63 000 ventes → doublons, valeurs manquantes, dates incohérentes, paiements non standardisés. Découvrez étape par étape comment SQL permet de nettoyer et préparer vos données pour l'analyse.
Les données brutes sont rarement exploitables directement. Avant toute analyse ou modélisation, une étape de nettoyage est indispensable : suppression des doublons, traitement des valeurs manquantes, standardisation des formats, correction des incohérences.
Dans ce cas pratique, nous allons nettoyer un dataset de ventes de 63 000 lignes généré aléatoirement avec Faker. Les problèmes sont variés et réalistes : doublons, valeurs manquantes, dates mal formatées, paiements non standardisés, quantités aberrantes, prix à zéro, et incohérences de calcul.
À l'issue de ce cas pratique, vous saurez :
- Importer un CSV dans SQLite
- Inspecter la qualité des données avec SQL
- Nettoyer les problèmes courants (doublons, NULL, formats)
- Exporter un dataset propre
Figure 1 : Le dataset brut contient 63 000 lignes.
Le dataset raw_sales_data_56k.csv contient des ventes simulées avec des erreurs volontaires pour illustrer les problèmes récurrents de qualité des données.
Colonnes du dataset :
Transaction_ID : identifiant unique de la transactionSale_Date : date de la vente (formats variés)Customer_ID : identifiant clientCustomer_Email : email client (avec valeurs manquantes)Product_Category : catégorie produit (casse variable, espaces)Product_Name : nom du produitQuantity : quantité achetée (TEXT, valeurs aberrantes)Unit_Price : prix unitaire (NULL, zéro)Total_Amount : montant total (incohérent avec Quantity × Unit_Price)Store_Location : localisation du magasin (casse variable)Payment_Method : moyen de paiement (non standardisé)
Figure 2 : Aperçu du dataset brut – on observe déjà des NULL et des formats incohérents.
Figure 3 : Structure des colonnes – on note que Quantity est en TEXT au lieu de INTEGER.
Pour ce cas pratique, nous utilisons :
SQLite est parfait pour ce type d'exercice : zéro configuration, un seul fichier, et le langage SQL standard (sauf quelques fonctions spécifiques). Les requêtes présentées sont adaptables à PostgreSQL, MySQL ou SQL Server.
Figure 4 : Import du fichier CSV dans DB Browser.
Lors de l'import, la ligne d'en-tête a été importée comme une ligne de données. Nous avons dû la supprimer manuellement. Voici la requête utilisée :
DELETE FROM raw_sales WHERE Transaction_ID = 'Transaction_ID';
SELECT COUNT(*) FROM raw_sales;Résultat : 63 000 lignes
PRAGMA table_info(raw_sales);Observation : La colonne `Quantity` est au format TEXT au lieu de INTEGER – à corriger.
Valeurs manquantes :
Dates incohérentes : mélange de YYYY-MM-DD, YYYY/MM/DD, MM-DD-YYYY, et même du texte ("Jan 2024 (approx)").
Payment_Method : 9 valeurs distinctes ("Credit Card", "cc", "PayPal", "ppal", etc.) – à standardiser.
Casse et espaces : "HOME GOODS", "Home Goods", "home goods" – à uniformiser.
Quantités aberrantes : valeurs négatives (-1) et absurdes (9999).
Prix à zéro : 20 lignes avec Unit_Price = 0 (à corriger).
Incohérence Total_Amount : 11 059 lignes où Total_Amount ≠ Quantity × Unit_Price.
CREATE TABLE clean_sales AS SELECT * FROM raw_sales;CREATE TABLE clean_sales_fixed AS
SELECT
Transaction_ID,
Sale_Date,
Customer_ID,
Customer_Email,
Product_Category,
Product_Name,
CAST(Quantity AS INTEGER) AS Quantity,
Unit_Price,
Total_Amount,
Store_Location,
Payment_Method
FROM clean_sales;
DROP TABLE clean_sales;
ALTER TABLE clean_sales_fixed RENAME TO clean_sales;DELETE FROM clean_sales
WHERE rowid NOT IN (
SELECT MIN(rowid)
FROM clean_sales
GROUP BY Transaction_ID, Sale_Date, Customer_ID, Customer_Email,
Product_Category, Product_Name, Quantity, Unit_Price,
Total_Amount, Store_Location, Payment_Method
);Résultat : 167 doublons supprimés.
ALTER TABLE clean_sales ADD COLUMN Sale_Date_Clean TEXT;
UPDATE clean_sales
SET Sale_Date_Clean = SUBSTR(Sale_Date, 1, 10)
WHERE Sale_Date GLOB '*-*-*' AND LENGTH(Sale_Date) = 10;
UPDATE clean_sales
SET Sale_Date_Clean = Sale_Date
WHERE Sale_Date_Clean IS NULL;
UPDATE clean_sales SET Product_Category = UPPER(TRIM(Product_Category));
UPDATE clean_sales SET Store_Location = UPPER(TRIM(Store_Location));
UPDATE clean_sales
SET Payment_Method = CASE
WHEN Payment_Method IN ('Credit Card', 'cc') THEN 'Credit Card'
WHEN Payment_Method IN ('PayPal', 'ppal') THEN 'PayPal'
WHEN Payment_Method IN ('Debit Card', 'debit') THEN 'Debit Card'
WHEN Payment_Method IS NULL THEN 'Unknown'
ELSE Payment_Method
END;
UPDATE clean_sales SET Quantity = NULL WHERE Quantity < 0 OR Quantity > 1000;
UPDATE clean_sales SET Unit_Price = NULL WHERE Unit_Price = 0;Résultat : 20 lignes corrigées.
UPDATE clean_sales
SET Quantity = (
SELECT AVG(Quantity)
FROM (
SELECT Quantity
FROM clean_sales
WHERE Quantity IS NOT NULL
ORDER BY Quantity
LIMIT 2 - (SELECT COUNT(*) FROM clean_sales WHERE Quantity IS NOT NULL) % 2
OFFSET (SELECT (COUNT(*) - 1) / 2 FROM clean_sales WHERE Quantity IS NOT NULL)
)
)
WHERE Quantity IS NULL;
UPDATE clean_sales
SET Unit_Price = (SELECT AVG(Unit_Price) FROM clean_sales WHERE Unit_Price IS NOT NULL)
WHERE Unit_Price IS NULL;Résultat : 2 935 lignes imputées.
UPDATE clean_sales
SET Total_Amount = Quantity * Unit_Price
WHERE Total_Amount != Quantity * Unit_Price;Résultat : 15 445 lignes recalculées.
SELECT
COUNT(CASE WHEN Customer_Email IS NULL THEN 1 END) AS missing_email,
COUNT(CASE WHEN Quantity IS NULL THEN 1 END) AS missing_quantity,
COUNT(CASE WHEN Unit_Price IS NULL THEN 1 END) AS missing_price
FROM clean_sales;-- Dans DB Browser : Fichier → Exporter → Table(s) CSV
-- Ou via SQLite CLI :
.headers on
.mode csv
.output clean_sales_data.csv
SELECT * FROM clean_sales;Créez une table de travail avant toute modification destructive.
Assurez-vous que les colonnes numériques sont bien importées en INTEGER/REAL.
Gardez une trace des requêtes exécutées pour reproduire le nettoyage.
BEGIN; ... COMMIT; permet d'annuler en cas d'erreur.
SQL est plus rapide sur les gros volumes (63 000 lignes, Excel commence à ramer), plus reproductible (les requêtes peuvent être réexécutées), et plus puissant pour les transformations complexes (jointures, sous-requêtes, window functions).
Globalement oui, avec quelques adaptations : la fonction GLOB est spécifique à SQLite (remplacer par LIKE ou REGEXP). L'imputation par médiane sera plus simple avec PERCENTILE_CONT (PostgreSQL) ou NTILE.
SQLite n'a pas de type DATE natif. Pour un usage professionnel, préférez PostgreSQL ou MySQL, et utilisez DATE() pour normaliser. Dans notre cas, nous avons simplifié en extrayant les 10 premiers caractères des formats standard.
SQLite peut gérer des millions de lignes, mais les performances peuvent diminuer. Passez alors à PostgreSQL ou utilisez des index sur les colonnes de filtrage. Évitez les SELECT DISTINCT * et préférez les GROUP BY ciblés.
La quantité a une distribution asymétrique (petites valeurs fréquentes, quelques outliers). La médiane est plus robuste. Le prix unitaire a une distribution plus symétrique, la moyenne est acceptable.
Ce cas pratique montre comment SQL permet de nettoyer efficacement un dataset de taille réelle (63 000 lignes) avec des problèmes variés. En une vingtaine de requêtes, nous avons :
