Tips for using Python for data preparation

Most of the work in any data project is not the analysis. It is the cleaning: turning a folder of mismatched files into one tidy table you can actually reason about. This guide walks through that process in Python, using a scenario you may recognise — pulling scattered machine-learning experiment results into a single, trustworthy dataset.

You do not need to be a data scientist to follow along. Where a term is technical, this guide explains it in plain language first, then shows the code. If you write Python already, you can skim the prose and read the examples.

The scenario: a folder full of late-night results

Imagine you have spent a semester training models to classify medical images. Every experiment wrote a small results file — accuracy, the settings you used, when it ran. You now have something like this:

results/
├── run_2024-01-12.csv
├── run_2024-01-19.csv
├── resnet_sweep/
│   ├── lr0.001.csv
│   └── lr0.01.csv
└── final_runs_USE_THESE.csv

Different folders. Different naming. Some columns spelled accuracy, some acc. A few files have a stray blank row. To write up your findings, you need one clean table — a leaderboard — with one row per experiment. That consolidation is a data-preparation task, and it is exactly the kind of work Python handles well.

The goal shapes everything that follows: one row per experiment, consistent columns, correct types, no silent errors.

Tip 1: Gather every file before you touch the data

The first mistake is hard-coding file paths one at a time. Instead, let Python find the files for you. The pathlib module models the filesystem as objects, and its rglob method searches a folder recursively — meaning it looks inside subfolders too.

from pathlib import Path
import pandas as pd

results_dir = Path("results")
csv_paths = sorted(results_dir.rglob("*.csv"))

print(f"Found {len(csv_paths)} files")
for path in csv_paths:
    print(" -", path)

When you load each file, record where it came from. That extra column is called provenance — a trail back to the source. It is the single most useful habit in data preparation, because the moment a number looks wrong, you can find the file that produced it.

frames = []
for path in csv_paths:
    df = pd.read_csv(path)
    df["source_file"] = path.name        # provenance: remember the origin
    frames.append(df)

raw = pd.concat(frames, ignore_index=True)
print(raw.shape)   # (rows, columns)

A DataFrame — usually shortened to df — is just a table in memory, with named columns and numbered rows. pd.concat stacks all the per-file tables into one.

Note: Loading everything into one table first, then cleaning, is easier to reason about than cleaning each file separately. You write the logic once and apply it everywhere.

Tip 2: Make your column names boring and predictable

Humans name columns inconsistently: Accuracy, acc, test accuracy. Code does not forgive that — df["acc"] and df["acc "] are different columns to Python. So normalise the names once: lowercase them, trim whitespace, and replace spaces with underscores.

def tidy_columns(frame):
    frame = frame.copy()
    frame.columns = (
        frame.columns
        .str.strip()
        .str.lower()
        .str.replace(r"\s+", "_", regex=True)
    )
    return frame

raw = tidy_columns(raw)

Then map the variants onto one canonical name. A small dictionary keeps this explicit and easy to audit later.

COLUMN_ALIASES = {
    "acc": "accuracy",
    "test_accuracy": "accuracy",
    "learning_rate": "lr",
}

raw = raw.rename(columns=COLUMN_ALIASES)

Now there is exactly one accuracy column, whatever the original spelling. Predictable names are what let every later step stay simple.

Tip 3: Fix types early, and let bad values surface

Every column has a type — the kind of value it holds, such as a number, a date, or text. When pandas reads a CSV, it guesses. A column with one stray word like "failed" in it gets read as text, so "0.91" becomes the string "0.91", and arithmetic silently breaks.

Convert types deliberately. Use errors="coerce" so that anything unconvertible becomes a missing value (NaN, “not a number”) instead of crashing — but in a place you can find it.

# Parse the run date from the file name, then the metrics as real numbers.
raw["run_date"] = pd.to_datetime(
    raw["source_file"].str.extract(r"(\d{4}-\d{2}-\d{2})")[0],
    errors="coerce",
)
raw["accuracy"] = pd.to_numeric(raw["accuracy"], errors="coerce")
raw["lr"] = pd.to_numeric(raw["lr"], errors="coerce")

# Surface the rows that failed to convert, instead of ignoring them.
bad_accuracy = raw[raw["accuracy"].isna()]
print(f"{len(bad_accuracy)} rows have no usable accuracy — inspect them:")
print(bad_accuracy[["source_file", "accuracy"]])

The point is not to hide problems. It is to make them visible while you still remember the context.

Tip 4: Decide what “missing” means before you fill it

A common reflex is to replace every blank with zero. Resist it. A missing accuracy is not an accuracy of zero — it means the experiment did not report one, which is a different fact. Filling it with 0 would drag your averages down and quietly corrupt the analysis.

Handle each case on its own terms:

# A missing learning rate genuinely means "used the default", so fill it.
raw["lr"] = raw["lr"].fillna(0.001)

# A missing accuracy is unknown, not zero — drop those rows from the leaderboard.
clean = raw.dropna(subset=["accuracy"]).copy()

The rule of thumb: fill a value only when you can state, in one sentence, what the fill means. If you cannot, leave it missing or drop the row.

Tip 5: Remove duplicates on purpose, not by accident

You re-ran some experiments, so the same configuration appears more than once. You do not want a blunt “remove identical rows” — you want one row per configuration, keeping the best result.

Sort so the best row comes first, then keep the first occurrence of each configuration:

clean = (
    clean
    .sort_values("accuracy", ascending=False)
    .drop_duplicates(subset=["lr"], keep="first")
    .reset_index(drop=True)
)

Being explicit about which duplicate you keep is what separates a defensible result from a lucky one.

Tip 6: Validate with assertions, so mistakes fail loudly

An assertion is a line that says “this must be true; stop everything if it is not.” Assertions turn silent corruption into an immediate, obvious error — far better than a wrong number in a published paper.

Put the rules you believe about your data into code:

assert clean["accuracy"].between(0, 1).all(), "Accuracy must be a fraction in [0, 1]"
assert clean["lr"].gt(0).all(), "Learning rate must be positive"
assert not clean["lr"].duplicated().any(), "Each configuration should appear once"
assert len(clean) > 0, "Leaderboard is empty — check the loading step"

print(f"Validation passed: {len(clean)} clean experiments")

If a future file breaks one of these rules, you find out the second you run the script — not three weeks later in review.

Tip 7: Save a reproducible artifact, and record how you made it

Finally, write the clean table to disk so you never repeat this work, and so a colleague (or future you) can rebuild it. Here is the whole pipeline assembled into one runnable file. Notice the filename in the code block’s header bar — naming your scripts is part of making work reproducible.

"""Consolidate scattered experiment CSVs into one clean leaderboard."""
from pathlib import Path
import pandas as pd

COLUMN_ALIASES = {"acc": "accuracy", "test_accuracy": "accuracy", "learning_rate": "lr"}


def load_all(results_dir: Path) -> pd.DataFrame:
    frames = []
    for path in sorted(results_dir.rglob("*.csv")):
        df = pd.read_csv(path)
        df["source_file"] = path.name
        frames.append(df)
    return pd.concat(frames, ignore_index=True)


def build_leaderboard(results_dir: Path) -> pd.DataFrame:
    df = load_all(results_dir)
    df.columns = df.columns.str.strip().str.lower().str.replace(r"\s+", "_", regex=True)
    df = df.rename(columns=COLUMN_ALIASES)

    df["accuracy"] = pd.to_numeric(df["accuracy"], errors="coerce")
    df["lr"] = pd.to_numeric(df["lr"], errors="coerce").fillna(0.001)

    leaderboard = (
        df.dropna(subset=["accuracy"])
          .sort_values("accuracy", ascending=False)
          .drop_duplicates(subset=["lr"], keep="first")
          .reset_index(drop=True)
    )

    assert leaderboard["accuracy"].between(0, 1).all()
    assert not leaderboard["lr"].duplicated().any()
    return leaderboard


if __name__ == "__main__":
    board = build_leaderboard(Path("results"))
    board.to_parquet("leaderboard.parquet")          # fast, type-safe artifact
    board.to_csv("leaderboard.csv", index=False)      # human-readable copy
    print(board.head())

Saving in Parquet — a binary format that remembers each column’s type — means the next person who loads the file gets real numbers and real dates back, with no re-cleaning. The CSV copy is there for anyone who just wants to open it in a spreadsheet.

The shape of good data preparation

Step back and the pattern is the same in any field, not just machine learning:

Stage	What you are really doing
Gather	Find every source and remember where each row came from
Standardise	Make names and types boring and predictable
Decide	Treat missing values as facts, not zeros
Deduplicate	Keep the right record, on purpose
Validate	Encode your assumptions as assertions
Save	Produce a reproducible artifact others can trust

Whether you are consolidating research results, monthly sales exports, or survey responses, the discipline carries over. Clean inputs are what make the interesting work — the analysis, the writing, the conclusions — possible.

And once your data is in order, the writing is the next thing worth getting right. If you are turning these results into a thesis chapter or a paper, our editors can help your prose match the rigour of your pipeline.