Introduction to Data Cleaning

DATA CLEANING DEFINITION:
─────────────────────────────────────────────────────────────────
Data cleaning (also called data cleansing or data scrubbing) is
the process of detecting, diagnosing, and correcting or removing
corrupt, inaccurate, incomplete, or irrelevant records from a
dataset to improve data quality before analysis.

REAL-WORLD ANALOGY:
Think of data as raw vegetables from a farm:
  • Some vegetables are bruised       → Outliers
  • Some are missing                  → Missing values
  • Some are duplicates (two carrots) → Duplicate records
  • Some are labeled wrong            → Incorrect data types
  • Some have dirt on them            → Extra whitespace/formatting

Data cleaning = washing and preparing vegetables before cooking.
─────────────────────────────────────────────────────────────────

"Garbage in, garbage out" — the most fundamental principle of data science.

REAL CONSEQUENCES OF DIRTY DATA:
┌─────────────────────────────────────────────────────────────────┐
│ Industry    │ Problem              │ Consequence                 │
├─────────────┼──────────────────────┼─────────────────────────────┤
│ Healthcare  │ Wrong drug dosage    │ Patient harm                │
│ Finance     │ Duplicate transaction│ Customer overcharged        │
│ E-commerce  │ Wrong address format │ Failed delivery             │
│ ML Model    │ Biased training data │ Discriminatory predictions  │
│ Analytics   │ Missing sales data   │ Wrong business decisions    │
│ Government  │ Census errors        │ Misallocated resources      │
└─────────────┴──────────────────────┴─────────────────────────────┘

COST: Gartner estimates poor data quality costs organizations
      an average of $12.9 million per year.

6 DIMENSIONS OF DATA QUALITY:

1. COMPLETENESS — Are all required values present?
   Bad:  name="Alice", email=NULL, phone=NULL
   Good: name="Alice", email="alice@co.com", phone="+1-555-0100"

2. ACCURACY — Do values reflect reality?
   Bad:  age = 250  (impossible)
   Good: age = 25

3. CONSISTENCY — Is data uniform across systems?
   Bad:  USA / United States / U.S.A (3 formats for same thing)
   Good: United States (one format everywhere)

4. VALIDITY — Do values conform to defined rules?
   Bad:  email = "not-an-email"
   Good: email = "user@domain.com"

5. UNIQUENESS — Are there duplicate records?
   Bad:  Row 1 and Row 47 are identical customer records
   Good: Each customer appears exactly once

6. TIMELINESS — Is data current/relevant?
   Bad:  Using 2019 product prices in 2024 analysis
   Good: Data reflects the current state of reality

DATA CLEANING PIPELINE:

Raw Data (CSV, Excel, Database, API)
        │
        ▼
┌─────────────────────────────────────────────────┐
│  STEP 1: DATA PROFILING                          │
│  • Shape, types, missing values, distributions  │
│  • Tools: df.info(), df.describe(), df.head()   │
└─────────────────────────────────────────────────┘
        │
        ▼
┌─────────────────────────────────────────────────┐
│  STEP 2: ISSUE DETECTION                         │
│  • Find NaN, outliers, duplicates, bad formats  │
│  • Tools: isnull(), duplicated(), value_counts()│
└─────────────────────────────────────────────────┘
        │
        ▼
┌─────────────────────────────────────────────────┐
│  STEP 3: CLEANING & TRANSFORMATION               │
│  • Fix each issue type systematically           │
│  • fillna(), drop_duplicates(), str.strip()     │
└─────────────────────────────────────────────────┘
        │
        ▼
┌─────────────────────────────────────────────────┐
│  STEP 4: VALIDATION                              │
│  • Verify fixes didn't introduce new problems   │
│  • Re-run profiling, check constraints          │
└─────────────────────────────────────────────────┘
        │
        ▼
┌─────────────────────────────────────────────────┐
│  STEP 5: EXPORT & DOCUMENT                       │
│  • Save cleaned dataset                         │
│  • Document all changes for reproducibility     │
└─────────────────────────────────────────────────┘
        │
        ▼
Analytics-Ready Dataset ✅

import pandas as pd
import numpy as np

# ─── DIRTY STUDENT DATASET ────────────────────────────
raw_data = {
    'student_id': [1, 2, 3, 4, 5, 5, 6, 7, 8, 9],
    'name':       ['Alice', 'BOB', '  Carol  ', 'David', 'Eve',
                   'Eve', 'Frank', None, 'Heidi', 'Ivan'],
    'age':        [20, 21, 19, 250, 22, 22, 20, 21, -5, 23],
    'email':      ['alice@edu.com', 'bob@edu.com', 'carol@edu.com',
                   'david@edu.com', 'eve@edu.com', 'eve@edu.com',
                   'not-an-email', None, 'heidi@edu.com', 'ivan@edu.com'],
    'score':      [85, 92, 78, 88, 95, 95, 72, 80, None, 88],
    'grade':      ['B', 'A', 'C', 'B', 'A', 'A', 'C', 'B', None, 'B']
}
df = pd.DataFrame(raw_data)

print("=== RAW DATA ===")
print(df.to_string())
print(f"\nShape: {df.shape}")
print(f"\nData Types:\n{df.dtypes}")
print(f"\nMissing Values:\n{df.isnull().sum()}")

# ─── STEP 1: PROFILE ──────────────────────────────────
print("\n=== DATA QUALITY ISSUES FOUND ===")
print(f"  Duplicates: {df.duplicated().sum()}")
print(f"  Missing values: {df.isnull().sum().sum()}")
print(f"  Invalid ages (< 0 or > 120): {((df[&#039;age'] < 0) | (df['age'] > 120)).sum()}")
print(f"  Invalid emails: {(~df[&#039;email'].str.contains('@', na=False)).sum()}")

# ─── STEP 2: CLEAN ────────────────────────────────────
# Fix names: strip whitespace + title case
df[&#039;name'] = df['name'].str.strip().str.title()

# Fix invalid ages
df[&#039;age'] = df['age'].where((df['age'] >= 5) & (df['age'] <= 100), np.nan)
df[&#039;age'] = df['age'].fillna(df['age'].median())

# Handle missing names
df[&#039;name'] = df['name'].fillna('Unknown')

# Mark invalid emails
df[&#039;email_valid'] = df['email'].str.contains(r'^[\w.]+@[\w.]+\.[a-z]{2,}$', na=False)

# Impute missing scores
df[&#039;score'] = df['score'].fillna(df['score'].median())

# Remove duplicates
df = df.drop_duplicates(subset=[&#039;student_id']).reset_index(drop=True)

# ─── STEP 3: VALIDATE ─────────────────────────────────
print("\n=== CLEANED DATA ===")
print(df.to_string())
print(f"\nShape after cleaning: {df.shape}")
print(f"Remaining missing: {df.isnull().sum().sum()}")

# ─── STEP 4: EXPORT ───────────────────────────────────
df.to_csv(&#039;cleaned_students.csv', index=False)
print("\n✅ Cleaned data saved to cleaned_students.csv")

=== DATA QUALITY ISSUES FOUND ===
  Duplicates: 1
  Missing values: 3
  Invalid ages (< 0 or > 120): 2
  Invalid emails: 1

=== CLEANED DATA ===
   student_id   name   age           email  score grade  email_valid
0           1  Alice  20.0   alice@edu.com   85.0     B         True
1           2    Bob  21.0     bob@edu.com   92.0     A         True
...
Shape after cleaning: (9, 7)
Remaining missing: 0

✅ Always keep a copy of raw data — never overwrite the original
✅ Profile before cleaning (understand the problem first)
✅ Log every transformation step
✅ Validate after each major cleaning operation
✅ Involve domain experts for business rule validation
✅ Use version control for cleaning scripts