Data Cleaning Checklist: A Repeatable Process for Any Dataset

Dirty data breaks models silently. A single mis-typed column, a leaked duplicate, or an unflagged sentinel value like -999 can inflate your AUC-ROC by ten points and wreck production performance. This guide gives you a fixed, ordered checklist you can run on any tabular dataset, with copy-pasteable Pandas code for every step.

Quick Takeaways

• Order matters. Profile first, fix structure second, handle values third, validate last.
• Never impute before splitting. Fit imputers and scalers on training data only to prevent leakage.
• Log every change. A reproducible pipeline beats a clever one-off script.
• Automate validation. Assertions at the end catch regressions when new data arrives.

Why a Checklist Beats Ad Hoc Cleaning

Ad hoc cleaning depends on what you happen to notice. A checklist forces the same inspection every time. That consistency gives you three advantages:

1. Reproducibility. A teammate can rerun your steps on next month’s data.
2. Auditability. Each step documents a decision about the data.
3. Fewer silent failures. Problems like mixed types or hidden whitespace get caught by design, not by luck.

The checklist below has ten steps. Run them in order.

Step 0: Set Up the Workspace

Keep the raw file untouched. Work on a copy and make every transformation traceable.

import pandas as pd
import numpy as np

# Load raw data; never overwrite this file
raw = pd.read_csv("customers_raw.csv")

# Work on a copy so you can always compare against the original
df = raw.copy()

# Simple change log to record every cleaning decision
log = []
def record(step, detail):
    log.append({"step": step, "detail": detail, "rows": len(df)})

Step 1: Profile the Dataset

Profiling tells you what is wrong before you touch anything. Check shape, types, memory, and summary statistics.

print(df.shape)                      # rows x columns
df.info()                            # dtypes and non-null counts
print(df.describe(include="all").T)  # numeric and categorical summaries

# Percentage of missing values per column, sorted descending
missing_pct = df.isna().mean().mul(100).sort_values(ascending=False)
print(missing_pct[missing_pct > 0])

# Cardinality: columns with 1 unique value are useless; near-unique are likely IDs
print(df.nunique().sort_values())

Look for these red flags:

• Numeric columns stored as object (usually stray characters like $ or ,).
• Columns with a single unique value.
• Minimum or maximum values that are physically impossible (negative age, future dates).
• Sentinel values such as -1, 999, 9999, or "N/A" masquerading as real data.

Step 2: Standardize Column Names

Inconsistent names cause bugs in joins and feature pipelines. Convert everything to lowercase snake_case.

df.columns = (
    df.columns
      .str.strip()                          # remove leading/trailing spaces
      .str.lower()                          # consistent casing
      .str.replace(r"[^\w\s]", "", regex=True)  # drop special characters
      .str.replace(r"\s+", "_", regex=True)     # spaces -> underscores
)
record("rename_columns", "snake_case applied")

Step 3: Fix Data Types

Wrong dtypes waste memory and break calculations. Convert deliberately.

# Strip currency symbols and commas, then cast to float
df["revenue"] = (
    df["revenue"].astype(str)
      .str.replace(r"[$,]", "", regex=True)
      .pipe(pd.to_numeric, errors="coerce")  # invalid values become NaN
)

# Parse dates; invalid strings become NaT instead of raising
df["signup_date"] = pd.to_datetime(df["signup_date"], errors="coerce")

# Low-cardinality strings -> category dtype (saves memory, speeds groupby)
for col in ["country", "plan_type"]:
    df[col] = df[col].astype("category")

Using errors=”coerce” is deliberate. It converts failures to missing values so you can count and handle them in one place rather than crashing mid-pipeline.

Step 4: Remove Duplicates

Duplicates inflate counts and leak information between train and test sets. Separate exact duplicates from logical duplicates.

# Exact duplicates: every column identical
exact = df.duplicated().sum()
df = df.drop_duplicates()

# Logical duplicates: same entity, different timestamps or typos
# Keep the most recent record per customer
df = (
    df.sort_values("signup_date")
      .drop_duplicates(subset="customer_id", keep="last")
)
record("deduplicate", f"removed {exact} exact duplicates")

Step 5: Clean Text and Categorical Values

Categorical inconsistencies fragment your groups. "USA", "usa ", and "U.S.A." become three categories unless you normalize them.

# Normalize whitespace and case in object columns
text_cols = df.select_dtypes(include="object").columns
for col in text_cols:
    df[col] = df[col].str.strip().str.lower()

# Map known variants to a canonical value
country_map = {"usa": "us", "u.s.a.": "us", "united states": "us"}
df["country"] = df["country"].astype(str).replace(country_map)

# Replace sentinel strings with true missing values
df = df.replace({"n/a": np.nan, "none": np.nan, "": np.nan, "-999": np.nan})

Step 6: Handle Missing Values

Choose the strategy by missingness mechanism, not by convenience.

Compare common techniques:

Split first, then fit imputers on training data only:

from sklearn.model_selection import train_test_split
from sklearn.impute import SimpleImputer

train, test = train_test_split(df, test_size=0.2, random_state=42)

num_cols = train.select_dtypes(include="number").columns

# Flag missingness BEFORE imputing; missing-ness is often predictive
for col in num_cols:
    train[f"{col}_was_missing"] = train[col].isna().astype(int)
    test[f"{col}_was_missing"] = test[col].isna().astype(int)

imputer = SimpleImputer(strategy="median")
train[num_cols] = imputer.fit_transform(train[num_cols])  # fit on train only
test[num_cols] = imputer.transform(test[num_cols])        # reuse train medians

Drop columns with extreme missingness (commonly above 60%) unless the field is business-critical.

Step 7: Detect and Treat Outliers

Outliers are not always errors. Investigate before removing. Two standard detection methods:

# IQR method: robust to non-normal distributions
def iqr_bounds(s, k=1.5):
    q1, q3 = s.quantile([0.25, 0.75])
    iqr = q3 - q1
    return q1 - k * iqr, q3 + k * iqr

low, high = iqr_bounds(train["revenue"])
outlier_mask = ~train["revenue"].between(low, high)
print(f"Outliers flagged: {outlier_mask.sum()}")

# Z-score method: assumes roughly normal data
z = (train["revenue"] - train["revenue"].mean()) / train["revenue"].std()
z_outliers = z.abs() > 3

# Winsorize: cap extremes instead of deleting rows
train["revenue"] = train["revenue"].clip(lower=low, upper=high)
test["revenue"] = test["revenue"].clip(lower=low, upper=high)  # same train bounds

Use Isolation Forest for multivariate outliers where no single column looks abnormal but the combination does.

Step 8: Check Logical Consistency

Rules that span columns catch errors that univariate checks miss.

# Business-rule violations
bad_age = ~df["age"].between(0, 110)
bad_dates = df["signup_date"] > pd.Timestamp.today()
bad_order = df["end_date"] < df["start_date"]

issues = pd.DataFrame({
    "bad_age": bad_age,
    "future_signup": bad_dates,
    "end_before_start": bad_order,
})
print(issues.sum())

# Quarantine bad rows rather than silently dropping them
quarantine = df[issues.any(axis=1)]
df = df[~issues.any(axis=1)]
quarantine.to_csv("quarantine_rows.csv", index=False)

Quarantining preserves evidence. You can review the rejected rows with stakeholders and trace upstream data-entry problems.

Step 9: Encode and Scale (Only If Modeling)

Cleaning ends where feature engineering begins, but scaling choices affect cleaning decisions.

from sklearn.preprocessing import RobustScaler

scaler = RobustScaler()
train[num_cols] = scaler.fit_transform(train[num_cols])
test[num_cols] = scaler.transform(test[num_cols])

Step 10: Validate and Document

Finish with assertions. They turn your cleaning rules into executable tests.

# Fail loudly if the cleaned data violates expectations
assert train["customer_id"].is_unique, "Duplicate customer IDs remain"
assert train[num_cols].isna().sum().sum() == 0, "Missing values remain"
assert train["age"].between(0, 110).all(), "Age out of range"
assert train.shape[1] == test.shape[1], "Train/test column mismatch"

# Save cleaned output with a versioned name
train.to_parquet("customers_clean_train_v1.parquet")
test.to_parquet("customers_clean_test_v1.parquet")

pd.DataFrame(log).to_csv("cleaning_log.csv", index=False)

For production pipelines, replace manual assertions with a schema library such as pandera or Great Expectations.

Scaling the Process: Big Data with PySpark

Pandas loads everything into memory. For datasets beyond a few gigabytes, apply the same checklist in PySpark.

from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("cleaning").getOrCreate()
sdf = spark.read.csv("customers_raw.csv", header=True, inferSchema=True)

# Missing value counts per column
sdf.select([
    F.sum(F.col(c).isNull().cast("int")).alias(c) for c in sdf.columns
]).show()

# Deduplicate, trim strings, fill nulls
sdf = (
    sdf.dropDuplicates(["customer_id"])
       .withColumn("country", F.lower(F.trim(F.col("country"))))
       .fillna({"revenue": 0})
)

Prefer built-in pyspark.sql.functions over Python UDFs. UDFs serialize data between the JVM and Python and run far slower.

Real-World Use Cases

Predicting Customer Churn with Python

A telecom dataset stores total_charges as text because 11 new customers have blank strings. pd.to_numeric(errors=”coerce”) surfaces them as NaN. Since these customers have zero tenure, filling with 0 is the correct domain decision, not the column median. A checklist step (type fixing) exposed a business rule that blind imputation would have hidden.

Handling Missing Data in Real-Time Sensor Streams

An IoT pipeline drops readings when a device loses connectivity. Mean imputation would flatten the signal. Instead, use forward fill with a time limit, and flag longer gaps:

sensors = sensors.sort_values("timestamp")
sensors["temp"] = sensors["temp"].ffill(limit=3)  # fill gaps up to 3 readings
sensors["temp_gap_flag"] = sensors["temp"].isna().astype(int)

Cleaning E-Commerce Transaction Data

Order tables often contain test transactions, refunds recorded as negative quantities, and duplicate webhook events. Apply Step 4 (dedupe on order_id plus event_id) and Step 8 (rule: quantity > 0 for sales) before computing revenue metrics. Skipping either inflates reported revenue.

Common Mistakes to Avoid

Frequently Asked Questions

What is the first step in data cleaning?

Profile the data. Check shape, dtypes, missing-value percentages, and summary statistics with df.info() and df.describe() before changing anything. Profiling reveals which of the remaining steps your dataset actually needs.

How do I handle missing values in a dataset?

Identify the missingness mechanism (MCAR, MAR, or MNAR), then choose a method. Drop rows when under 5% are missing at random, use median or mode imputation for simple cases, and use KNNImputer or IterativeImputer for correlated features. Always fit imputers on training data only.

Should I remove outliers from my data?

Not automatically. Investigate whether the value is an error or a genuine rare event. Remove confirmed errors, cap extreme but valid values with winsorizing, and keep legitimate outliers when they carry signal, as in fraud detection.

How long should data cleaning take?

Data cleaning commonly consumes 50% to 80% of project time. A reusable checklist and a scripted pipeline cut that sharply on repeat datasets because you rerun the code instead of repeating manual inspection.