Data Cleaning Checklist — Step-by-Step Data Quality Guide

import pandas as pd
import numpy as np

# Load data
df = pd.read_csv('raw_data.csv')
print(f"Original shape: {df.shape}")

# 1. Initial inspection
print(df.head())
print(df.info())
print(df.describe())

# 2. Missing values
print("\nMissing values:")
print(df.isnull().sum())
print("\n% Missing:")
print((df.isnull().sum() / len(df)) * 100)

# Drop columns with >50% missing
threshold = 0.5
df = df.loc[:, df.isnull().mean() < threshold]

# Impute numeric with median
numeric_cols = df.select_dtypes(include=[np.number]).columns
df[numeric_cols] = df[numeric_cols].fillna(df[numeric_cols].median())

# Impute categorical with mode
cat_cols = df.select_dtypes(include=['object']).columns
df[cat_cols] = df[cat_cols].fillna(df[cat_cols].mode().iloc[0])

# 3. Duplicates
print(f"\nDuplicates: {df.duplicated().sum()}")
df = df.drop_duplicates()

# 4. Data types
# Convert date columns
df['date'] = pd.to_datetime(df['date'], errors='coerce')

# Extract date components
df['year'] = df['date'].dt.year
df['month'] = df['date'].dt.month

# 5. Outliers (cap at 99th percentile)
def cap_outliers(df, column, percentile=0.99):
    upper_limit = df[column].quantile(percentile)
    df[column] = df[column].clip(upper=upper_limit)
    return df

df = cap_outliers(df, 'price', 0.99)

# 6. Validation
# Remove invalid ages
df = df[(df['age'] >= 0) & (df['age'] <= 120)]

# Remove negative prices
df = df[df['price'] > 0]

# 7. Standardization
# Lowercase column names
df.columns = df.columns.str.lower().str.replace(' ', '_')

# Standardize categorical values
df['city'] = df['city'].str.title()  # Mumbai not MUMBAI
df['city'] = df['city'].replace({'Bombay': 'Mumbai', 'Bengaluru': 'Bangalore'})

print(f"\nFinal shape: {df.shape}")
print(f"Rows removed: {len(pd.read_csv('raw_data.csv')) - len(df)}")

# Save cleaned data
df.to_csv('cleaned_data.csv', index=False)

-- 1. Find duplicates
SELECT customer_id, email, COUNT(*)
FROM customers
GROUP BY customer_id, email
HAVING COUNT(*) > 1;

-- Remove duplicates (keep first occurrence)
DELETE FROM customers
WHERE id NOT IN (
    SELECT MIN(id)
    FROM customers
    GROUP BY customer_id, email
);

-- 2. Handle missing values
-- Find rows with null in critical columns
SELECT *
FROM orders
WHERE customer_id IS NULL OR order_date IS NULL;

-- Replace nulls with default
UPDATE products
SET category = 'Uncategorized'
WHERE category IS NULL;

-- 3. Fix data types
-- Convert string to date
ALTER TABLE orders
ALTER COLUMN order_date TYPE DATE
USING order_date::DATE;

-- 4. Outlier detection (IQR method)
WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) AS q1,
        PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) AS q3
    FROM products
)
SELECT *
FROM products, stats
WHERE price < (q1 - 1.5 * (q3 - q1))  -- Lower outliers
   OR price > (q3 + 1.5 * (q3 - q1)); -- Upper outliers

-- 5. Standardize text
UPDATE customers
SET city = INITCAP(city),  -- Title case
    email = LOWER(email);  -- Lowercase emails

-- Replace variations
UPDATE customers
SET city = REPLACE(city, 'Bengaluru', 'Bangalore');

# Standardize
df['city'] = df['city'].str.title()  # Mumbai
df['city'] = df['city'].replace({'Bombay': 'Mumbai', 'Bengaluru': 'Bangalore'})

# Verify
print(df['city'].value_counts())

# pandas auto-detects most formats
df['date'] = pd.to_datetime(df['date'], errors='coerce')

# Custom format
df['date'] = pd.to_datetime(df['date'], format='%d-%b-%Y')

# IQR method
Q1 = df['salary'].quantile(0.25)
Q3 = df['salary'].quantile(0.75)
IQR = Q3 - Q1

# Define outlier bounds
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Remove outliers
df = df[(df['salary'] >= lower_bound) & (df['salary'] <= upper_bound)]

# OR cap outliers (keep row, adjust value)
df['salary'] = df['salary'].clip(lower=lower_bound, upper=upper_bound)

# Convert to numeric, coerce errors to NaN
df['age'] = pd.to_numeric(df['age'], errors='coerce')

# Then handle nulls
df['age'].fillna(df['age'].median(), inplace=True)