Mini-Project: Clean and Visualize a Real Dataset

Two people are given the same messy CSV and a week. Both hand something in.

The first hands over a notebook with 240 cells. Cells 40 to 90 are commented out. There are eleven charts, several of which plot the same thing with different bin counts. Somewhere around cell 130 a row filter was applied and never explained. Asked "so what did you find?", the answer is "well, there's a lot in there".

The second hands over a folder. Inside: the original file, untouched; a script that turns it into a cleaned file; a notebook with fourteen cells that tells a story; four charts, each answering a stated question; and a one-page summary that opens with three findings and the decisions they support. Asked the same question, the answer takes forty seconds and is specific.

Both did roughly the same amount of analysis. Only one produced work anybody can use. This project is about producing the second thing — the full path from a raw file to a defensible conclusion, done once, end to end.

Choosing Something Worth Analysing

Pick a dataset with at least 1,000 rows, at least eight columns, a mix of numeric and categorical types, and — this is the part people skip — real mess in it. A dataset that has already been cleaned for teaching purposes gives you nothing to practise on.

Before writing any code, write down the questions. Three or four specific ones, in a text file, dated. "Which product categories have the highest return rate, and does that change by region?" is a question. "Explore the sales data" is not — it has no answer, so it can never be finished.

Write the questions before you see the data. Questions invented after you have looked are questions the data was always going to answer, which is not the same as questions worth asking.

Setting Up So the Work Survives

project/  data/    raw/          # never edited, never overwritten    processed/    # outputs of cleaning, safe to delete and rebuild  notebooks/    01_explore.ipynb    02_analysis.ipynb  src/    clean.py      # the cleaning logic, importable and testable  figures/  README.md       # the questions, the findings, how to reproduce

The rule that matters most is that data/raw/ is read-only. Every transformation lives in code, so deleting data/processed/ and re-running the script reproduces exactly what you had. The moment you fix a value by hand in Excel, your results stop being reproducible and nobody — including you in three months — can verify anything.

1from pathlib import Path2import pandas as pd3import numpy as np4import matplotlib.pyplot as plt5import seaborn as sns67ROOT = Path.cwd().parent8RAW, PROC, FIGS = ROOT/"data"/"raw", ROOT/"data"/"processed", ROOT/"figures"9for p in (PROC, FIGS):10    p.mkdir(parents=True, exist_ok=True)1112pd.set_option("display.max_columns", 60)13pd.set_option("display.width", 160)14sns.set_theme(style="whitegrid", palette="deep")15plt.rcParams["figure.dpi"] = 110

The First Twenty Minutes

Load with intent rather than defaults, then find out what you actually received.

1raw = pd.read_csv(2    RAW/"sales.csv",3    dtype={"customer_id": "string", "postcode": "string"},4    na_values=["", "NA", "N/A", "-", "null", "unknown"],5    parse_dates=["order_date"],6)78print(f"{raw.shape[0]:,} rows x {raw.shape[1]} columns")9print(f"{raw.memory_usage(deep=True).sum()/1e6:.1f} MB")10print(f"{raw.duplicated().sum():,} exact duplicate rows")1112overview = pd.DataFrame({13    "dtype": raw.dtypes.astype(str),14    "missing_pct": (raw.isna().mean()*100).round(1),15    "unique": raw.nunique(),16    "example": [raw[c].dropna().iloc[0] if raw[c].notna().any() else None17                for c in raw.columns],18})19print(overview.sort_values("missing_pct", ascending=False))

Reading identifiers as strings prevents postcodes and customer IDs losing their leading zeros. Declaring the missing-value markers explicitly stops the string "unknown" from turning an entire numeric column into text.

Now write down what you see, in prose, in the notebook, before you fix anything. Something like: "48,213 rows. discount_pct is 62% missing — need to find out whether blank means zero. region has 14 distinct values but only 9 real regions; casing and whitespace issues. Three orders dated 2031. quantity has negative values, probably returns."

These notes become the cleaning plan, and later they become the section of your report that explains why the numbers are what they are.

Cleaning, With a Reason for Each Decision

Duplicates

1exact = raw.duplicated().sum()2by_key = raw.duplicated(subset=["order_id"]).sum()3print(f"exact: {exact:,}   duplicate order_id: {by_key:,}")45# Inspect before deleting - are they truly identical, or updates?6dupe_ids = raw.loc[raw.duplicated("order_id", keep=False), "order_id"].unique()[:3]7print(raw[raw["order_id"].isin(dupe_ids)].sort_values("order_id"))89df = raw.sort_values("order_date").drop_duplicates("order_id", keep="last")

Exact duplicates are usually an export artefact and safe to drop. Rows sharing a key but differing elsewhere are a different animal — they may be revisions, where keeping the most recent is right, or a broken join upstream that has multiplied your rows. Look at examples before choosing; dropping revision history and dropping join artefacts require opposite handling.

Missing values

1# Does missingness carry information?2flag = df["discount_pct"].isna()3print(df.groupby(flag)[["order_total", "quantity"]].mean().round(2))4print(df.groupby(flag)["channel"].value_counts(normalize=True).round(3))

If orders with a missing discount average £42 and those with one average £115, the blank is not random — it almost certainly means "no discount applied", and filling it with 0 is correct. If instead the two groups look identical, the field is probably missing at random and a median fill is defensible. The test takes one line and changes what you should do.

1df["discount_pct"] = df["discount_pct"].fillna(0.0)          # blank means none23df["delivery_days_was_missing"] = df["delivery_days"].isna().astype("int8")4df["delivery_days"] = (df.groupby("region")["delivery_days"]5                         .transform(lambda s: s.fillna(s.median())))6df["delivery_days"] = df["delivery_days"].fillna(df["delivery_days"].median())78df = df.dropna(subset=["order_id", "order_date", "order_total"])   # unusable without

Outliers and impossible values

Separate the two. Impossible values are errors and should go. Extreme but possible values are data.

1impossible = (2    (df["order_total"] < 0) |3    (df["order_date"] > pd.Timestamp.today()) |4    (df["quantity"] == 0)5)6print(f"{impossible.sum():,} impossible rows removed")7df = df[~impossible]89q1, q3 = df["order_total"].quantile([0.25, 0.75])10iqr = q3 - q111extreme = (df["order_total"] < q1 - 3*iqr) | (df["order_total"] > q3 + 3*iqr)12print(f"{extreme.sum():,} extreme orders ({extreme.mean():.2%})")13print(df.loc[extreme, ["order_id", "order_total", "quantity", "customer_type"]].head())

Print the extreme rows and read them. If the £48,000 orders all come from customers marked "wholesale", they are real and removing them would delete an entire customer segment. Flag them instead:

df["is_bulk_order"] = extreme.astype("int8")

Types and text

1for col in df.select_dtypes(["object", "string"]).columns:2    df[col] = df[col].str.strip().str.replace(r"\s+", " ", regex=True)34df["region"] = df["region"].str.title()5print(df["region"].value_counts())     # confirm 14 values collapsed to 967for col in ["region", "channel", "product_category"]:8    df[col] = df[col].astype("category")910print(f"memory after cleaning: {df.memory_usage(deep=True).sum()/1e6:.1f} MB")

Keep a running log so the report can state exactly what happened:

1log = {2    "rows_raw": len(raw),3    "duplicates_removed": int(by_key),     # every exact duplicate also repeats its order_id4    "impossible_removed": int(impossible.sum()),5    "rows_missing_essential": len(raw) - len(df) - int(by_key) - int(impossible.sum()),6    "rows_final": len(df),7    "retained_pct": round(100 * len(df) / len(raw), 1),8}9df.to_parquet(PROC/"sales_clean.parquet")10pd.Series(log).to_json(PROC/"cleaning_log.json", indent=2)

If the retained percentage is below about 90%, stop and account for the gap. Losing 10% of your data is a finding in its own right and belongs in the report, not in a footnote.

Exploration That Answers the Questions

Work outwards: one variable, then pairs, then combinations. At each stage tie what you see back to the questions you wrote down.

One variable at a time

1num_cols = df.select_dtypes("number").columns[:9]2axes = df[num_cols].hist(bins=40, figsize=(14, 9), edgecolor="white")3plt.tight_layout()4plt.savefig(FIGS/"distributions.png", dpi=150, bbox_inches="tight")56print(df[num_cols].agg(["mean", "median", "std", "skew"]).T.round(2))

Compare each mean with its median. Where they diverge sharply, the mean is not a description of a typical row and you should report medians. Where a histogram shows two humps, you have two populations mixed together and every aggregate over them is describing nobody.

Pairs

1fig, ax = plt.subplots(1, 2, figsize=(14, 5))23sns.violinplot(data=df, x="region", y="order_total", inner="quartile", ax=ax[0])4ax[0].set_yscale("log")5ax[0].tick_params(axis="x", rotation=30)67corr = df[num_cols].corr()8sns.heatmap(corr, mask=np.triu(np.ones_like(corr, dtype=bool)),9            cmap="RdBu_r", center=0, vmin=-1, vmax=1, annot=True, fmt=".2f", ax=ax[1])10plt.tight_layout()11plt.savefig(FIGS/"relationships.png", dpi=150, bbox_inches="tight")

The log scale on a skewed monetary variable is what makes the comparison readable. Without it, a handful of wholesale orders stretch the axis so far that every region's distribution collapses into a line at the bottom.

Combinations

1pivot = df.pivot_table(values="order_total", index="region",2                       columns="product_category", aggfunc="median")3sns.heatmap(pivot, annot=True, fmt=".0f", cmap="YlGnBu")45df["month"] = df["order_date"].dt.month6g = sns.FacetGrid(df, col="channel", height=3.5, col_wrap=3, sharey=False)7g.map_dataframe(sns.lineplot, x="month", y="order_total", estimator="median")

Small multiples like that facet grid are where the most interesting findings usually appear, because an overall trend can reverse within every subgroup. Seeing that in one aggregate line is impossible; seeing it across five panels is immediate.

Features That Encode What You Learned

1d = df["order_date"].dt2df["month"] = d.month3df["day_of_week"] = d.dayofweek4df["is_weekend"] = d.dayofweek.ge(5).astype("int8")56df["unit_price"] = df["order_total"] / df["quantity"].replace(0, np.nan)7df["order_total_log"] = np.log1p(df["order_total"].clip(lower=0))89cust = df.groupby("customer_id").agg(10    orders=("order_id", "count"),11    total_spend=("order_total", "sum"),12    first_order=("order_date", "min"),13    last_order=("order_date", "max"),14)15cust["days_active"] = (cust["last_order"] - cust["first_order"]).dt.days16cust["value_tier"] = pd.qcut(cust["total_spend"], 4,17                             labels=["low", "medium", "high", "top"])

Every derived column should trace back to something you observed. unit_price exists because the histogram of order_total turned out to be dominated by quantity rather than by price. is_weekend exists because the day-of-week plot showed a genuine step change. Features invented without an observation behind them are noise you will have to explain later.

Turning It Into Something Someone Reads

The deliverable is not the notebook. It is a short document with the notebook attached as evidence.

Findings go first. A report that builds up methodology for four pages before reaching a conclusion will be read to page one. Write each finding so it survives being quoted alone:

Weak:   "Region affects order value."Better: "Median order value in the North is 34% lower than in the South         (GBP 41 vs GBP 62, n = 18,400 and 22,100). The gap is entirely         driven by product mix: within each category the regions are         within 4% of each other."

The second version states the size, the direction, the sample behind it, and — critically — the check that rules out the obvious alternative explanation. That last clause is what separates an observation from an analysis.

The limitations section is not modesty, it is precision. "Returns are not recorded before March, so pre-March return rates are unknown, not zero" prevents a reader from acting on a comparison that cannot be made. Every dataset has three or four of these, and finding them is part of the work.

A finding that cannot be stated with a number, a direction and a sample size is not yet a finding. It is an impression.

Judging Your Own Work

Before you call it finished, check it against how it will actually be assessed.

A useful final test: delete data/processed/ and figures/, then rerun everything from the raw file. If anything fails to regenerate, some step exists only in your notebook's memory or in a manual edit, and the work is not reproducible. Fix it now, while you still remember what it was.

What Separates This From an Exercise

Sizing the work realistically matters. Roughly a fifth of the time goes to loading and understanding what you have, two fifths to cleaning, a fifth to exploration, and the remaining fifth to writing it up. People consistently plan for the opposite distribution, budget almost nothing for cleaning, and then discover on the final day that the analysis rests on a column they never checked.

Whatever domain you pick, the shape stays the same, only the questions change. Retail data asks which categories drive returns and how seasonality moves revenue. Health data asks which factors travel with outcomes and where recording practices differ between sites. Financial data asks how volatility clusters and which series move together. Public transport data asks where delays concentrate and what predicts them. In each case the work is the same: state the questions, get the file, find out what is wrong with it, fix what you can defend fixing, look at the data properly, and write down what you found in a way that someone can act on.

The finished artefact is a folder someone else can open, run, and understand without you in the room. That is the standard — not the number of charts, not the sophistication of the methods, but whether the path from the raw file to the conclusion is visible and repeatable by another person.