What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
A repeatable pandas pipeline can normalize a tabular file, coerce messy types, enforce required fields and numeric ranges, remove exact duplicates, and return rejected rows for review. The compact example below is a teaching implementation—not a production data-quality platform—but it gives you an auditable starting point for CSV workflows.
What this pipeline does
Unlike ad hoc notebook cleanup, a pipeline applies the same deterministic steps whenever a new file arrives. It has an input contract, explicit rules, measurable accepted and rejected records, and an output that can be regenerated. The approach follows the cleaning stages described in Analytics Vidhya’s July 29, 2025 example, while preserving rejected data instead of silently discarding it.
As an Amazon Associate I earn from qualifying purchases.
| Problem | Policy in this example |
|---|---|
| Missing required value | Reject the row |
| Unparseable number or date | Coerce to missing, then reject when required or invalid |
| Out-of-range number | Reject the row |
| Optional missing value | Leave it missing until an explicit imputation policy is chosen |
| Exact duplicate | Remove from the accepted set |
| Malformed email | Reject using a basic syntax check |
Install pandas and define the input contract
Create an isolated environment, activate it, and install pandas:
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchespython -m venv .venv
source .venv/bin/activate # macOS/Linux
# .venvScriptsActivate.ps1 # Windows PowerShell
python -m pip install pandasThe input is expected to contain these columns:
customer_id: required positive integeremail: required text with a basic email-shaped formatage: optional integer from 0 through 120signup_date: required datescore: optional number from 0 through 100
For example, a CSV might contain a missing ID, "thirty" in the age column, an impossible age, an invalid date, a score of 105, a malformed email, an exact duplicate, and a blank optional score. Those cases exercise different rules rather than pretending every bad value has the same remedy.
#1 Best Overall
The compact cleaning function
This function is deliberately conservative: it never invents a replacement value. It returns accepted and rejected tables, and each rejected row carries a reason.
import re
import pandas as pd
SCHEMA = {
"customer_id": {"type": "int", "required": True, "min": 1},
"email": {"type": "string", "required": True},
"age": {"type": "int", "min": 0, "max": 120},
"signup_date": {"type": "date", "required": True},
"score": {"type": "float", "min": 0, "max": 100},
}
def clean(df, schema=SCHEMA):
df = df.copy()
missing = set(schema) - set(df.columns)
if missing:
raise ValueError(f"Missing columns: {sorted(missing)}")
df.columns = df.columns.str.strip().str.lower()
rejected = []
def reject(mask, reason):
nonlocal df
if mask.any():
bad = df.loc[mask].copy(); bad["reason"] = reason
rejected.append(bad); df = df.loc[~mask]
df = df.drop_duplicates()
for col, rule in schema.items():
if rule["type"] == "int":
df[col] = pd.to_numeric(df[col], errors="coerce").astype("Int64")
elif rule["type"] == "float":
df[col] = pd.to_numeric(df[col], errors="coerce")
elif rule["type"] == "date":
df[col] = pd.to_datetime(df[col], errors="coerce")
else:
df[col] = df[col].astype("string").str.strip()
if rule.get("required"):
reject(df[col].isna(), f"{col} is required or unparseable")
if "min" in rule:
reject(df[col] < rule["min"], f"{col} below minimum")
if "max" in rule:
reject(df[col] > rule["max"], f"{col} above maximum")
email_bad = ~df["email"].str.lower().str.match(r"^[^@\s]+@[^@\s]+\.[^@\s]+$", na=False)
reject(email_bad, "email has invalid basic syntax")
return df, (pd.concat(rejected) if rejected else pd.DataFrame(columns=[*df.columns, "reason"]))The complete function is roughly 50 executable lines, depending on how blank lines and comments are counted. It is a compact demonstration, not evidence that all data is correct.
Run the pipeline and save both outputs
raw = pd.read_csv("customers.csv")
cleaned, rejected = clean(raw)
print(f"Accepted: {len(cleaned)}")
print(f"Rejected: {len(rejected)}")
cleaned.to_csv("customers_clean.csv", index=False)
rejected.to_csv("customers_rejected.csv", index=False)df.copy() prevents unexpected mutation of the caller’s DataFrame. A missing expected column raises an error before partial processing. Exact duplicates are removed with pandas’ drop_duplicates; this does not identify near-duplicates or repeated business events.
Recommended Free Tools
Why the order of operations matters
- Copy and normalize: column names are stripped and lowercased so a file with accidental capitalization does not create a second field.
- Deduplicate: identical rows are removed before reporting accepted data.
- Coerce types:
to_numericandto_datetimeconvert parseable text;errors="coerce"turns unparseable values into missing values. - Check required fields: conversion failures in required columns therefore become visible rejections rather than hidden strings.
- Apply bounds: age and score comparisons happen only after numeric conversion.
- Validate semantics: string dtype alone does not prove that an email, postal code, or identifier is meaningful.
The rejected table keeps original values where possible and adds a reason. For operational use, add a run timestamp, source filename, batch ID, and failing column so an analyst can trace each decision.
Missing values: reject, repair, or leave them?
There is no universal imputation rule. A required identifier or signup date should normally be rejected. An optional numeric feature might use a median, while a categorical field might use a mode or "Unknown". Descriptive text can remain missing when absence itself is meaningful. pandas provides dropna for removal and fillna for explicit replacement; choose and document the policy per field rather than applying one blanket fill.
Email checks are deliberately limited
The regular expression catches obvious malformed input after trimming and lowercasing. It does not prove that a domain exists, accepts mail, or is controlled by the person represented by the row. Internationalized addresses and organization-specific rules may require a specialized validator or a confirmation workflow.
Add cross-field business rules
Field-level checks cannot detect contradictions between columns. Parse both dates, then quarantine rows where an end precedes a start:
bad = df["end_date"] < df["start_date"]
# preserve bad rows with reason="end_date before start_date"
df = df.loc[~bad]Other examples include a signup date later than the comparison timestamp, a positive quantity when an order exists, a discount greater than subtotal, or a country whose postal-code format does not agree. Define the comparison timezone and timestamp; naive and timezone-aware datetimes should not be mixed, and a small clock-skew tolerance may be appropriate.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Do not make outlier deletion the default
The interquartile-range rule uses Q1 - 1.5 × IQR and Q3 + 1.5 × IQR as bounds. It can be useful for flagging unusual observations, but an unusually large purchase may be a legitimate customer, quartiles are unstable in small samples, and multiple populations can make one global threshold misleading. Prefer an outlier_flag, domain-specific limits, or a review queue. If you remove values outside an IQR rule, state that you removed statistical extremes—not that you found errors.
Quick Recap
What this script does not solve
- Business-key duplicates: two customer records with different formatting may still represent one person.
- Lineage: a count of rejected rows is not a regulatory-grade audit trail.
- Monitoring: recurring jobs need alerts for schema changes, rejection-rate spikes, and distribution drift.
- Scale: pandas is convenient for small and medium tables, not automatically suitable for distributed or streaming workloads.
- Machine-learning leakage: learned imputers, encoders, or scalers must be fitted on training data only.
Production checklist
- Version the schema and write unit tests for every rejection rule.
- Record input and output row counts, run time, source file, and batch ID.
- Store rejected rows securely and review personal-data retention requirements.
- Make the job idempotent so rerunning a batch does not duplicate outputs.
- Separate normalization, repair, validation, quarantine, and permanent deletion in logs and documentation.
- Define whether extra columns are preserved and whether row order matters.
When to graduate from a custom pandas function
| Option | Useful when | Less suitable when |
|---|---|---|
| Pandera | You need declarative DataFrame schemas and reusable checks. | A tiny one-off script is clearer with plain pandas. |
| Great Expectations | You need formal expectations, validation results, and documentation. | You specifically need the smallest possible local script. |
| Soda | You need recurring quality monitoring across production sources. | You are cleaning one CSV during exploration. |
| Polars | The workload is performance-sensitive or expression-oriented. | Your codebase and team rely on pandas APIs. |
| scikit-learn pipelines | Cleaning is part of model training and parameters must be learned without leakage. | The task is raw-record validation independent of a model. |
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
