Data Cleaning Checklist: 7 Steps for Clean Datasets

The 7-Step Data Cleaning Workflow

   [1. Remove Duplicates]  ──►  [2. Handle Missing Data]  ──►  [3. Standardize Formatting]
                                                                        │
   [6. Address Skewness]   ◄──  [5. Detect Outliers]     ◄──  [4. Fix Structural Errors]
             │
             ▼
   [7. Validate Final Data] ──► Ready for Analysis & Modeling

1. Step 1: Remove Duplicate Observations

• The Problem: Duplicates artificially inflate sample sizes, bias statistical averages, and cause data leakage if the same instance ends up in both your training and testing sets.
• The Action:
  • Identify exact row duplicates across all columns.
  • Identify partial duplicates (e.g., matching user_id or transaction_id with contradictory timestamps).
  • Retain the most recent or complete record and drop the rest.
• Python Snippet:
  Python

  # Remove exact duplicate rows
  df_clean = df.drop_duplicates()
  

2. Step 2: Handle Missing Values Appropriately

• The Action:
  • Quantify: Measure the percentage of missingness per column.
  • Drop: Remove columns with extreme missingness ($>50\%$) if they are non-critical.
  • Impute Numerics: Use the Median for skewed data or the Mean for normally distributed features.
  • Impute Categoricals: Fill missing entries with the Mode or create a distinct "Unknown" label.
  • Advanced Imputation: Apply algorithms like K-Nearest Neighbors (KNN) or MICE for correlated numeric features.

3. Step 3: Fix Structural Errors & Inconsistent Formatting

• The Action:
  • Standardize Text Case: Convert categorical strings to lowercase or title case (e.g., merge "NY", "ny", and "New York" into a single consistent label).
  • Trim Whitespace: Strip leading and trailing spaces from text entries (" customer_name " $\rightarrow$ "customer_name").
  • Standardize Date & Time: Enforce ISO 8601 format (YYYY-MM-DD) across all timestamp columns.
  • Fix Data Types: Ensure integer IDs are not cast as floats and continuous numbers are stored correctly as float types.

4. Step 4: Correct Invalid Values & Out-of-Range Anomalies

• The Action:
  • Check numeric features for impossible values (e.g., age = -5, height = 999, or conversion_rate = 150%).
  • Verify cross-field logic (e.g., a ship_date occurring before an order_date).
  • Enforce schema bounds and replace or flag illogical entries.

5. Step 5: Detect and Manage Statistical Outliers

                  IQR Method for Outlier Detection
     Low Outliers                                      High Outliers
     ◄───●───┤─────────────────[ Q1 │ Q3 ]─────────────────┤───●───►
          Q1 - 1.5*(IQR)           Median            Q3 + 1.5*(IQR)

• The Action:
  • Visualize: Use Box Plots and Scatter Plots to spot extreme deviations.
  • Calculate Thresholds: Apply the $1.5 \times \text{IQR}$ rule or calculate $Z$-scores ($\vert{}Z\vert{} > 3$).
  • Treat Appropriately:
    • Cap / Winsorize: Set extreme values to the 1st or 99th percentile boundary.
    • Transform: Apply logarithmic transformations ($\log(x)$) to compress long-tailed numeric features.
    • Remove: Drop outliers only if they represent verified recording errors.

6. Step 6: Address Class Imbalance and Data Skewness

• The Action:
  • Target Variable Imbalance: For classification problems (e.g., fraud detection), check target distributions. Apply techniques like SMOTE (oversampling) or random undersampling if class ratios exceed $90:10$.
  • Feature Skewness: Check feature distributions using histograms. Apply power transforms (Box-Cox or Yeo-Johnson) to normalize heavily skewed numeric inputs.

7. Step 7: Validate the Cleaned Dataset

• Validation Checklist:
  • [ ] Are there zero unhandled NaN / null values in required columns?
  • [ ] Do all features match their designated target data types (int, float, datetime, category)?
  • [ ] Are key structural invariants holding (e.g., total row count matches expectations after cleaning)?
  • [ ] Is summary statistics (df.describe()) showing plausible minimums, maximums, and standard deviations?

Checklist Quick Reference Table

Key Takeaway