A light cleaning pipeline

A notebook with forty cells, half of them run out of order, three of them commented “DO NOT RERUN,” and a final number nobody can reproduce on a clean kernel is not analysis. It is performance art. The audience clapped once. The encore fails every time.

This is Part 8 of Python for analytics. You can filter, group, merge, and clean missing values. Now you string those moves into a light cleaning pipeline: a short chain of small steps that runs top to bottom and leaves a trail a colleague can follow without a séance.

What you’ll learn

Why small functions beat mystery cells
A repeatable shape: load, clean, validate, summarize
How to make a notebook (or script) rerunnable from a fresh start
A pipeline checklist for Monday morning work
A full mini pipeline you can adapt to your own CSV

Reproducibility is a kindness, not a personality type

You do not need a ten-service platform to be reproducible. You need a file that, given the same inputs, produces the same outputs when run from the top. That standard is how you sleep before a board meeting. It is also how you hand work to a teammate without narrating every click.

If you lived through fragile spreadsheet chains in From spreadsheets to real data, this part is the Python version of the same lesson. One path. One order. One definition of clean. Foundations thinking from Analytics foundations still applies: know the question before you polish columns for sport.

The diagram is intentionally boring: arrows in one direction. Boring is the point. Clever branching notebooks are where metrics go to mutate.

Design rules for a light pipeline

Example:

Pipeline steps status

One direction. Raw in at the top. Clean frames and summaries out at the bottom. No editing earlier cells after you have a final number.
Small steps. Each function does one job: rename, coerce types, filter test rows, aggregate.
Visible checks. Print row counts, null rates, and a few assertions after risky steps.
No silent globals. Pass DataFrames into functions and return new ones. Avoid depending on a cell you ran yesterday.
Inputs are explicit. Path, sheet name, filter date, and business rules live in a small config block at the top.

These rules scale from a 40-line notebook to a modest script. You can grow into packaging later. First, earn the right to be fancy by being clear.

The four stages

1. Load

Read the raw file once. Do not clean inside the read call beyond basic options (encoding, separator). Keep a raw frame you never overwrite if you can afford the memory. That gives you a before/after story when someone asks why a row disappeared.

2. Clean

Rename columns, normalize blanks, cast dtypes, map sentinels, strip keys, drop pure junk rows (for example, total rows accidentally exported from a sheet). This is Parts 6 and 7 applied as functions, not as improvisation.

3. Validate

Assert the things that must be true for the analysis to mean anything: primary key uniqueness (if claimed), no negative quantities when impossible, date range inside the expected window, join fanout under a threshold. Fail loud.

4. Summarize

Only after validation do you groupby, pivot, or chart-ready aggregates. Summaries are outputs, not places to hide cleaning.

Worked example: a mini pipeline

Imagine a weekly orders export with the usual drama: mixed types, a sentinel, a blank region, and a header row that includes a stray total line from a spreadsheet. We will keep the example small enough to run mentally.

import pandas as pd
from pathlib import Path

# --- config (edit only this block for a new week) ---
INPUT_PATH = Path("data/orders_raw.csv")
AS_OF = "2024-06-30"
MIN_AMOUNT = 0

def load_orders(path: Path) -> pd.DataFrame:
    df = pd.read_csv(path)
    print(f"loaded rows={len(df)} cols={list(df.columns)}")
    return df

def clean_orders(df: pd.DataFrame) -> pd.DataFrame:
    out = df.copy()

    # Standard names
    out = out.rename(
        columns={
            "Order ID": "order_id",
            "Customer ID": "customer_id",
            "Order Date": "order_date",
            "Amount": "amount",
            "Region": "region",
        }
    )

    # Drop spreadsheet total rows if present
    out = out[out["order_id"].astype(str).str.lower() != "total"]

    # Text nulls
    out["region"] = out["region"].replace(r"^\s*$", pd.NA, regex=True)
    out["region"] = out["region"].replace({"Unknown": pd.NA, "N/A": pd.NA})

    # Types
    out["amount"] = pd.to_numeric(out["amount"], errors="coerce")
    out.loc[out["amount"] == -999, "amount"] = pd.NA
    out["order_date"] = pd.to_datetime(out["order_date"], errors="coerce")
    out["customer_id"] = out["customer_id"].astype("string").str.strip()

    print(
        "after clean",
        f"rows={len(out)}",
        f"amount_nulls={out['amount'].isna().sum()}",
        f"date_nulls={out['order_date'].isna().sum()}",
    )
    return out

def validate_orders(df: pd.DataFrame, as_of: str, min_amount: float) -> pd.DataFrame:
    out = df.copy()
    as_of_ts = pd.Timestamp(as_of)

    # Required fields for this analysis
    before = len(out)
    out = out.dropna(subset=["order_id", "customer_id", "order_date", "amount"])
    dropped = before - len(out)
    print(f"dropped incomplete rows={dropped}")

    if out["order_id"].duplicated().any():
        raise ValueError("order_id is not unique after clean")

    if (out["amount"] < min_amount).any():
        bad = (out["amount"] < min_amount).sum()
        raise ValueError(f"found {bad} rows below min_amount={min_amount}")

    if out["order_date"].max() > as_of_ts:
        raise ValueError("order_date contains values after AS_OF")

    print(f"validated rows={len(out)}")
    return out

def summarize_orders(df: pd.DataFrame) -> pd.DataFrame:
    summary = (
        df.groupby("region", dropna=False, as_index=False)
        .agg(
            orders=("order_id", "count"),
            revenue=("amount", "sum"),
            customers=("customer_id", "nunique"),
        )
        .sort_values("revenue", ascending=False)
    )
    print(summary)
    return summary

def run_pipeline(path: Path = INPUT_PATH) -> tuple[pd.DataFrame, pd.DataFrame]:
    raw = load_orders(path)
    clean = clean_orders(raw)
    good = validate_orders(clean, AS_OF, MIN_AMOUNT)
    summary = summarize_orders(good)
    return good, summary

# On a fresh kernel, one call:
# clean_df, region_summary = run_pipeline()

Example:

Pipeline validation summary

Example output:

Example output: pipeline run log

Even if you paste this into a notebook, treat run_pipeline() as the only cell that must succeed from cold start (plus imports and config). Everything else is definition. That structure is how you avoid the “works on my kernel” trap.

Inline demo without a file

If you want to practice without CSV I/O, build a raw frame and pass it through the same clean and validate functions (adjust load). Here is a tiny raw sample that mimics export mess:

raw = pd.DataFrame(
    {
        "Order ID": [101, 102, 103, "Total"],
        "Customer ID": [" 1 ", "2", "2", ""],
        "Order Date": ["2024-06-01", "2024-06-15", "not a date", ""],
        "Amount": ["40", "12.5", "-999", "52.5"],
        "Region": ["East", "", "West", ""],
    }
)

clean = clean_orders(raw)
# Expect: total row gone, amounts numeric, -999 becomes NA, blank region NA
print(clean)

When you later enable validate_orders, the bad date row should drop via dropna(subset=...), and you should see the drop count printed. That print is not noise. It is the audit trail.

Pipeline checklist

Stage	Must have	Done?
Config	Paths, as-of date, business thresholds in one place
Load	Row/column print; raw preserved if possible
Clean	Renames, nulls, dtypes, sentinels; no aggregates yet
Validate	Assertions on keys, ranges, fanout; fail loud
Summarize	Metrics only after validation
Rerun test	Restart kernel / new process; run top to bottom once
Handoff note	What one row means; known drops; as-of timestamp

Print this checklist in your team wiki if you must. Sticky note works. The ritual matters more than the tool.

Functions over mystery cells

Notebooks are excellent for exploration. They are dangerous as unreviewed production. A few patterns reduce drama:

Put imports and config first.
Put pure functions next.
Put a single orchestration call near the end.
Keep charts after the numbers exist, not interleaved with mutating clean steps.
If you experiment, copy to a scratch section clearly marked, or use a separate exploratory notebook.

When the same pipeline must run on a schedule, move the functions to a .py file and call them from the notebook or a job runner. That migration is easier if you never relied on cell order magic.

What “done” looks like for a weekly pipeline

A pipeline is done for the week when a cold run produces the same summary you would defend in a meeting, and when a teammate can find: the input path, the as-of date, the drop counts, and the output files. That bar is lower than “fully automated on Kubernetes” and higher than “I clicked Run All once on Friday and it looked fine.”

If your team is small, store the notebook or script next to a one-page runbook: where the raw file lands, who drops it, what time you run, where outputs go, and who gets pinged when validation fails. The runbook is part of the pipeline. Code without operational notes becomes folklore.

When validation fails, resist the urge to comment out the assert so you can ship. Fix the data, widen the rule with an explicit documented exception, or ship a partial result with a loud caveat. Silent assert removal is how wrong numbers become tradition.

Common mistakes

Cleaning in place across ten cells so a second run double-filters or double-fills.
Validating only the happy path with rows you hand-picked, never the ugly export.
Hiding filters inside groupby without printing how many rows remain.
Changing business rules in the middle of the notebook after stakeholders already saw a number.
No as-of date so “weekly” means whatever happened to be on disk.
Copy-pasting the pipeline into five notebooks that drift apart. Prefer one shared module when the team is ready.

Practice and next step

Take last week’s real export. Write four functions with the names above (even if bodies are short). Restart your environment and run only the orchestration call. If it fails, good: you found the hidden dependency. Fix until cold start works.

Part 9 is about exporting results and handing them to humans and BI tools without losing dtypes or context. For SQL-shaped validation checks you already know, revisit the SQL series. More learning paths live on the Learn hub.

Quick recap

A light pipeline is load, clean, validate, summarize in one direction.
Functions and a top config beat mystery cells and tribal kernel state.
Print row losses and assert invariants; fail loud when grain breaks.
Cold-start reruns are the real test of reproducibility.
Summaries come last so cleaning never hides inside a chart.

Sources

pandas I/O tools (CSV): https://pandas.pydata.org/docs/user_guide/io.html
Python tutorial, defining functions: https://docs.python.org/3/tutorial/controlflow.html#defining-functions
pandas groupby user guide: https://pandas.pydata.org/docs/user_guide/groupby.html
Analytics Made Simple, spreadsheets series: https://analyticsmadesimple.com/series/spreadsheets-to-data/
Analytics Made Simple, Learn hub: https://analyticsmadesimple.com/learn/