Data Cleaning

Data cleaning (data cleansing / scrubbing) is the process of detecting and correcting corrupt, inaccurate, incomplete, duplicate or irrelevant records in a dataset. It is the single most time-consuming activity in analytics — commonly 60–80% of a project's effort.

Garbage In, Garbage Out (GIGO) — no algorithm can rescue conclusions built on dirty data.

Dimensions of Data Quality

1. Handling Missing Values

Why data goes missing: non-response, sensor failure, data-entry omission, merge mismatches, or a value genuinely not applicable.

Types of missingness:

Treatment strategies:

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "name":   ["Riya", "Zoya", "Kabir", "Aarav", "Meera"],
    "age":    [19, np.nan, 21, 20, np.nan],
    "marks":  [88, 76, np.nan, 54, 91],
    "city":   ["Delhi", "Noida", np.nan, "Delhi", "Delhi"],
})

# --- Detect ---
print(df.isnull().sum())
# age 2, marks 1, city 1
print(round(df.isnull().mean() * 100, 1))   # % missing per column

# --- Treat ---
df["age"]   = df["age"].fillna(df["age"].median())      # skew-safe
df["marks"] = df["marks"].fillna(df["marks"].mean())    # numeric, symmetric
df["city"]  = df["city"].fillna(df["city"].mode()[0])   # categorical -> mode
print(df.isnull().sum().sum())   # 0

# KNN imputation — uses similar rows instead of a single global statistic
from sklearn.impute import KNNImputer

num = pd.DataFrame({"age": [19, np.nan, 21, 20], "marks": [88, 76, np.nan, 54]})
imputer = KNNImputer(n_neighbors=2)
filled = pd.DataFrame(imputer.fit_transform(num), columns=num.columns)
print(filled.round(2))

2. Removing Duplicates

Duplicates arise from repeated form submissions, merging datasets, or ETL reruns. They inflate counts and bias every statistic.

df2 = pd.DataFrame({
    "roll": [101, 102, 101, 103, 102],
    "name": ["Riya", "Zoya", "Riya", "Kabir", "Zoya"],
    "marks": [88, 76, 88, 91, 79],
})

print(df2.duplicated().sum())                    # 1  (exact duplicate row)
df2_clean = df2.drop_duplicates()                # drops the exact duplicate

# Business rule: one row per roll number — keep the latest record
df2_latest = df2.drop_duplicates(subset=["roll"], keep="last")
print(df2_latest)

Watch for near-duplicates: "Riya Sharma" vs "riya sharma" vs "Riya Sharma". Normalise case and whitespace before de-duplicating (fuzzy matching handles harder cases).

3. Detecting and Treating Outliers

An outlier is an observation that lies abnormally far from the rest of the data.

Detection methods:

IQR method (most common, distribution-free)
    Q1 = 25th percentile,  Q3 = 75th percentile
    IQR = Q3 - Q1
    Lower fence = Q1 - 1.5 × IQR
    Upper fence = Q3 + 1.5 × IQR
    Anything outside the fences is an outlier

Z-score method (assumes roughly normal data)
    z = (x - mean) / standard deviation
    |z| > 3  =>  outlier

marks = pd.Series([55, 62, 58, 61, 60, 59, 63, 57, 5, 98])

Q1, Q3 = marks.quantile(0.25), marks.quantile(0.75)
IQR = Q3 - Q1
low, high = Q1 - 1.5 * IQR, Q3 + 1.5 * IQR
print(f"Fences: {low:.2f} to {high:.2f}")
print("Outliers:", marks[(marks < low) | (marks > high)].tolist())
# Fences: 47.62 to 72.38
# Outliers: [5, 98]

# Z-score alternative
z = (marks - marks.mean()) / marks.std()
print("Z-score outliers:", marks[z.abs() > 2].tolist())

Treatment options:

In fraud detection and fault detection, the outlier IS the signal — never blindly delete outliers.

4. Fixing Structural Errors

messy = pd.DataFrame({
    "city": [" delhi", "Delhi ", "DELHI", "noida", "Noida"],
    "gender": ["M", "male", "Male", "F", "female"],
})

messy["city"] = messy["city"].str.strip().str.title()
messy["gender"] = (messy["gender"].str.strip().str.lower()
                   .replace({"m": "Male", "male": "Male",
                             "f": "Female", "female": "Female"}))
print(messy["city"].unique())    # ['Delhi' 'Noida']
print(messy["gender"].unique())  # ['Male' 'Female']

Typical structural problems: inconsistent casing, stray whitespace, mixed date formats (14/01/2026 vs 2026-01-14), mixed units (kg vs g), spelling variants, and stray currency/percent symbols inside numeric columns.

5. Correcting Data Types

raw = pd.DataFrame({
    "roll": ["101", "102", "103"],
    "joined": ["2026-01-14", "2026-02-01", "2026-03-09"],
    "fee": ["12,500", "13,000", "11,750"],
})

raw["roll"] = raw["roll"].astype(int)
raw["joined"] = pd.to_datetime(raw["joined"])
raw["fee"] = raw["fee"].str.replace(",", "").astype(float)
print(raw.dtypes)
# roll               int64
# joined    datetime64[ns]
# fee              float64

6. Validation Rules

Define explicit rules and check every record against them:

def validate(df):
    problems = []
    if (df["age"] < 0).any() or (df["age"] > 120).any():
        problems.append("age out of valid range 0-120")
    if (df["marks"] < 0).any() or (df["marks"] > 100).any():
        problems.append("marks out of valid range 0-100")
    if df["roll"].duplicated().any():
        problems.append("duplicate roll numbers")
    return problems or ["All validation checks passed"]

check = pd.DataFrame({"roll": [1, 2, 3], "age": [19, 21, 20], "marks": [88, 76, 91]})
print(validate(check))    # ['All validation checks passed']

A Reusable Cleaning Checklist

1. Profile the data — df.info(), df.describe(), df.isnull().sum(), df.nunique()
2. Fix data types and parse dates
3. Standardise text — case, whitespace, spelling, units
4. Remove exact and near-duplicates
5. Handle missing values with a documented, justified strategy
6. Detect outliers; investigate before treating
7. Apply domain validation rules
8. Document every transformation — cleaning must be reproducible, never a one-off manual edit

Cleaning produces correct data. The next lesson, preprocessing, makes that correct data analysis-ready.