Reproducible data cleaning in pandas: preserve the raw file, expose the rules, audit the exceptions

Reviewed October 1, 2026. The dataset and cleaning contract are synthetic. Examples were tested with pandas 2.2.3; API behavior was checked against the current official documentation. The rules are intentionally specific to this dataset, not universal cleaning defaults.

Data cleaning is not a contest to remove every empty cell. It is a process of making data suitable for a defined question while preserving enough evidence to explain each change. A blank amount, a duplicated identifier, and an impossible date require different decisions.

A reproducible workflow starts from an unchanged input, applies explicit rules, and produces consistent outputs. If another person cannot explain why a row disappeared, the workflow is not yet ready to support a reliable report.

Write a data contract before transforming values

For this tutorial, one row represents one order, and the source is a small CSV export. Define the following contract with the hypothetical data owner:

This contract is a business decision. A refund dataset may allow negative amounts. A source may use longer identifiers or legitimate city labels outside this list. Reusing the code without revisiting those assumptions would be a cleaning error.

Preserve the input and control parsing

pandas.read_csv can infer types and missing-value markers. For an audit-friendly first pass, load fields as strings and choose how missing markers are interpreted. Otherwise, an identifier such as 001 can become a number and lose its original representation.

from io import StringIO
import pandas as pd

csv_text = """order_id,order_date,amount,city
001,2026-09-28,125000, Jakarta
002,2026-09-29,90000,surabaya
003,2026-09-30,oops,Jakarta
004,2026-09-31,150000,Bandung
005,2026-09-30,-500,Surabaya
002,2026-09-29,90000,SURABAYA
006,2026-10-01,,
007,2026-10-01,0,Bandung
"""

raw = pd.read_csv(
    StringIO(csv_text), dtype="string", keep_default_na=False
)

Using keep_default_na=False here preserves the raw blank strings. The next stage treats blanks as missing only under our stated contract. In a real project, keep the original file outside the cleaned-output path and record the input version, source, extraction time, and library environment.

Make normalization narrow and visible

Removing surrounding whitespace and normalizing a controlled city label can be reasonable. Removing punctuation from every column is not. A hyphen may be essential in an identifier, and case can matter in codes or passwords.

Keep raw and normalized views separate. If the workflow turns “surabaya” into “SURABAYA,” that is a documented representation change. If it converts an invalid amount into a missing value, that is a diagnostic state—not permission to quietly replace it with a mean.

Detect exceptions rather than hiding them

to_numeric(errors="coerce") makes malformed numeric inputs detectable. to_datetime can parse an explicit date format and turn invalid dates into NaT. Preserve the original strings so these failures remain distinguishable from source blanks.

For missing values, use isna() and notna(). Equality comparisons with missing sentinels do not have ordinary-value semantics. Explicit tests make the rule easier to read and less sensitive to a column's dtype.

def clean_orders(raw_frame):
    columns = ["order_id", "order_date", "amount", "city"]
    if set(raw_frame.columns) != set(columns):
        raise ValueError("Unexpected input columns")

    work = raw_frame.loc[:, columns].copy()
    for column in columns:
        work[column] = work[column].astype("string").str.strip()
        work[column] = work[column].replace("", pd.NA)
    work["city"] = work["city"].str.upper()

    amounts = pd.to_numeric(work["amount"], errors="coerce")
    dates = pd.to_datetime(
        work["order_date"], format="%Y-%m-%d", errors="coerce"
    )
    allowed_cities = {"JAKARTA", "SURABAYA", "BANDUNG"}
    flags = pd.DataFrame({
        "invalid_id": ~work["order_id"].str.fullmatch(r"[0-9]{3}", na=False),
        "invalid_date": dates.isna() |
                        ~work["order_date"].str.fullmatch(r"[0-9]{4}-[0-9]{2}-[0-9]{2}", na=False),
        "missing_amount": work["amount"].isna(),
        "malformed_amount": work["amount"].notna() & amounts.isna(),
        "negative_amount": amounts.lt(0).fillna(False),
        "fractional_amount": amounts.mod(1).ne(0) & amounts.notna(),
        "missing_city": work["city"].isna(),
        "unknown_city": work["city"].notna() & ~work["city"].isin(allowed_cities),
        "duplicate_id": work["order_id"].notna() &
                        work.duplicated("order_id", keep=False),
    }, index=work.index).fillna(False)
    reject = flags.any(axis=1)

    rejected = work.loc[reject].copy()
    rejected["issues"] = flags.loc[reject].apply(
        lambda row: "; ".join(name for name, bad in row.items() if bad),
        axis=1,
    )
    clean = work.loc[~reject].copy()
    clean["amount"] = amounts.loc[~reject].astype("Int64")
    clean["order_date"] = dates.loc[~reject]
    clean = clean.sort_values("order_id").reset_index(drop=True)

    quality = {
        "input_rows": len(work),
        "accepted_rows": len(clean),
        "rejected_rows": int(reject.sum()),
        "issue_counts": {name: int(count) for name, count in flags.sum().items()},
    }
    return clean, rejected, quality

clean, rejected, quality = clean_orders(raw)
print(clean)
print(rejected[["order_id", "issues"]])
print(quality)

The code quarantines all rows with a duplicated order key. duplicated(keep=False) marks every member of a duplicate group, instead of arbitrarily preserving the first. That conservative policy is chosen for this example; a real source may supply a reliable version or update timestamp for resolving conflicts.

Check the output against an expected audit

There are 8 input rows, 2 accepted rows, and 6 rejected rows. Issue counts total 7 because order 006 has two issues. Counting flagged conditions is not the same as counting rejected records. The accepted amount total is 125000, but it is only a total for the accepted subset—not a complete total for all orders.

Handle missingness according to the analysis

A missing amount is not automatically zero. Zero may mean a free order, while missing may mean an incomplete export. Filling both with zero changes the measurement. Similarly, deleting every row with any missing optional field can discard perfectly usable observations.

For descriptive reporting, publish the relevant denominator and missing count. For modeling, decide whether imputation is justified and fit learned transformations only on training data. The data-leakage guide explains why preprocessing the full dataset before splitting can distort validation.

Validate joins before enrichment

If you attach a city lookup, establish its expected cardinality. merge(validate="many_to_one", indicator=True) can check that each incoming city maps to at most one lookup record and expose unmatched rows.

A duplicated lookup key can multiply orders without an exception unless you request validation. Also remember pandas' documented behavior: missing keys can match each other, unlike ordinary SQL equality joins. Reject or explicitly handle missing keys before enrichment when that behavior is inappropriate.

Test the rules and preserve the lineage

from pandas.testing import assert_frame_equal

assert quality["input_rows"] == 8
assert quality["accepted_rows"] == 2
assert quality["rejected_rows"] == 6
assert clean["order_id"].tolist() == ["001", "007"]
assert clean["amount"].tolist() == [125000, 0]
assert sum(quality["issue_counts"].values()) == 7

# Same raw input, same typed result.
again, _, again_quality = clean_orders(raw)
assert_frame_equal(clean, again)
assert quality == again_quality

assert_frame_equal checks more than whether printed values look similar. Preserve tests for valid zeros, malformed strings, conflicting duplicate keys, missing fields, and impossible dates. These small cases reveal rule changes early.

Raw input
Preserve evidence→Rules
Version decisions→Outputs
Clean + exceptions→Report
Explain coverage

Save outputs only after reviewing the audit, using filenames distinct from the raw source. Record who approved ambiguous resolutions and why. Do not put unnecessary personal data into logs just to make the workflow observable.

Good cleaning does not make uncomfortable data disappear. It turns assumptions into explicit rules, isolates exceptions, and leaves enough evidence for someone else to reproduce and challenge the result.