Data Cleaning Pipeline

A skill for building systematic, reproducible data cleaning pipelines for research datasets. Covers common data quality issues, step-by-step cleaning workflows, handling missing values, detecting and treating outliers, validating data integrity, and documenting cleaning decisions for reproducibility.

The Data Cleaning Workflow

Pipeline Overview

Data cleaning should follow a consistent, documented order. Each step builds on the previous one, and the entire pipeline should be scripted for reproducibility.

Data Cleaning Pipeline (recommended order):

1. Initial Assessment
   - Load data, check dimensions, inspect dtypes
   - Generate summary statistics and missing value report
   - Identify structural issues (merged cells, inconsistent delimiters)

2. Structural Fixes
   - Standardize column names (snake_case, no spaces)
   - Fix data types (strings to numbers, dates, categories)
   - Split or merge columns as needed
   - Remove completely empty rows/columns

3. Deduplication
   - Identify exact duplicates
   - Identify near-duplicates (fuzzy matching)
   - Decide keep-first, keep-last, or merge strategy

4. Missing Value Treatment
   - Classify missingness mechanism (MCAR, MAR, MNAR)
   - Apply appropriate imputation or exclusion strategy
   - Document and justify missing data decisions

5. Outlier Detection and Treatment
   - Statistical methods (IQR, z-score, Mahalanobis)
   - Domain-based validation (impossible values)
   - Decide: correct, cap, remove, or keep with flag

6. Consistency Checks
   - Cross-field validation (age vs birth date)
   - Range validation (0-100 for percentages)
   - Referential integrity (foreign keys exist)

7. Documentation and Export
   - Log all changes with before/after counts
   - Export cleaned dataset with version number
   - Save cleaning script for reproducibility

Initial Data Assessment

Automated Quality Report

import pandas as pd
import numpy as np

def generate_quality_report(df):
    """
    Generate a comprehensive data quality report.
    Run this BEFORE any cleaning to establish a baseline.
    """
    report = {
        "dimensions": f"{df.shape[0]} rows x {df.shape[1]} columns",
        "memory_usage": f"{df.memory_usage(deep=True).sum() / 1e6:.1f} MB",
        "duplicate_rows": df.duplicated().sum(),
    }

    col_report = []
    for col in df.columns:
        info = {
            "column": col,
            "dtype": str(df[col].dtype),
            "missing_count": df[col].isna().sum(),
            "missing_pct": f"{df[col].isna().mean() * 100:.1f}%",
            "unique_values": df[col].nunique(),
            "sample_values": str(df[col].dropna().head(3).tolist()),
        }

        if pd.api.types.is_numeric_dtype(df[col]):
            info["min"] = df[col].min()
            info["max"] = df[col].max()
            info["mean"] = df[col].mean()
            info["std"] = df[col].std()

        col_report.append(info)

    report["columns"] = col_report
    return report