Chapter 18 — Week 2 Checkpoint Project

Lesson 18.1 — Checkpoint: Clean, Transform, Validate, Save, Test

Here's where all of Week 2 comes together — NumPy's vectorized thinking, pandas cleaning, grouping, merging, real file formats, and testing — into one small, complete, trustworthy pipeline. This mirrors exactly what you built at the end of Week 1: not a toy exercise, but a real, connected piece of work, built entirely by you.

The scenario: you've received a messy daily sales export, spread across two source files — an orders file and a customers file — and you need to clean it, calculate revenue by region, save the trustworthy result, and prove your work with tests.

Step 1 — Create the messy input.


python
import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 4, 5],
    "customer_id": [101, 102, 101, 103, 103, 999],
    "total": ["149.50", "89.99", "unknown", "310.25", "310.25", "45.00"],
    "order_date": ["2026-01-14", "2026/01/15", "2026-01-16", "Jan 17, 2026", "Jan 17, 2026", "2026-01-18"],
})

customers = pd.DataFrame({
    "customer_id": [101, 102, 103],
    "name": ["  priya shah  ", "RAJ KUMAR", "Amit Verma"],
    "region": ["East", "West", "East"],
})

orders.to_csv("raw_orders.csv", index=False)
customers.to_csv("raw_customers.csv", index=False)

Notice the problems, deliberately layered in, the way a real messy morning tends to look: order 4 is an exact duplicate, order 3 has a total that isn't a real number, three different date formats appear, customer names need cleaning, and order 5 references customer_id 999 — a customer that doesn't actually exist in the customers file.

Step 2 — Clean and merge.

Build this in cleaning.py, using tools directly from this week:


python
import pandas as pd

def load_and_clean(orders_path, customers_path):
    orders = pd.read_csv(orders_path)
    customers = pd.read_csv(customers_path)

    orders = orders.drop_duplicates()
    orders["total"] = pd.to_numeric(orders["total"], errors="coerce")
    orders["order_date"] = pd.to_datetime(orders["order_date"])

    customers["name"] = customers["name"].str.strip().str.title()

    merged = orders.merge(customers, on="customer_id", how="left")

    return merged

Notice how="left" — a deliberate choice, exactly the reasoning from Chapter 15.2. We're keeping every order, even the one from the missing customer_id 999, rather than silently dropping it. That row will come back with NaN for name and region — an honest, visible gap, not a quietly vanished order.

Run it:


python
from cleaning import load_and_clean

merged = load_and_clean("raw_orders.csv", "raw_customers.csv")
print(merged)
print("\nMissing values:")
print(merged.isna().sum())

Output / Note

order_id customer_id total order_date name region 0 1 101 149.50 2026-01-14 Priya Shah East 1 2 102 89.99 2026-01-15 Raj Kumar West 2 3 101 NaN 2026-01-16 Priya Shah East 3 4 103 310.25 2026-01-17 Amit Verma East 4 5 999 45.00 2026-01-18 NaN NaN

Missing values: total 1 name 1 region 1

Two honest gaps, both clearly visible: order 3's unreadable total, and order 5's unknown customer. Neither one is hidden, guessed at, or silently dropped — exactly the standard this whole course has been building toward.

Step 3 — Transform: revenue by region.


python
def revenue_by_region(merged_df):
    valid = merged_df.dropna(subset=["total", "region"])
    summary = valid.groupby("region")["total"].agg(["sum", "count"])
    summary = summary.rename(columns={"sum": "revenue", "count": "order_count"})
    return summary.sort_values("revenue", ascending=False)


python
from cleaning import revenue_by_region

summary = revenue_by_region(merged)
print(summary)

Output / Note
   revenue  order_count
region East 310.25 1 West 89.99 1

Notice both problem rows — the bad total and the missing customer — correctly fell out of this report on their own, because dropna(subset=["total", "region"]) filters on exactly the columns this specific calculation actually depends on. That's a deliberate, careful choice: dropping rows only when the gap actually matters for this particular question, rather than a blanket drop that might discard rows a different report could have used perfectly well.

Step 4 — Save the trustworthy output.


python
def save_clean_data(merged_df, summary_df, orders_output, summary_output):
    merged_df.to_parquet(orders_output, index=False)
    summary_df.to_parquet(summary_output)


python
from cleaning import save_clean_data

save_clean_data(merged, summary, "clean_orders.parquet", "revenue_summary.parquet")

Parquet, not CSV, for exactly the reasons from Chapter 16 — smaller, faster, and it remembers that order_date is a real date and total is a real number, ready for whatever pipeline stage picks this up next.

Step 5 — Prove it with tests.


python
import pandas as pd
from cleaning import load_and_clean, revenue_by_region

def test_load_and_clean_removes_duplicates_and_flags_missing(tmp_path):
    orders_path = tmp_path / "orders.csv"
    customers_path = tmp_path / "customers.csv"

    pd.DataFrame({
        "order_id": [1, 1],
        "customer_id": [101, 101],
        "total": ["100.00", "100.00"],
        "order_date": ["2026-01-01", "2026-01-01"],
    }).to_csv(orders_path, index=False)

    pd.DataFrame({
        "customer_id": [101],
        "name": ["  priya shah  "],
        "region": ["East"],
    }).to_csv(customers_path, index=False)

    result = load_and_clean(str(orders_path), str(customers_path))

    assert len(result) == 1
    assert result["name"].iloc[0] == "Priya Shah"

def test_revenue_by_region_excludes_rows_with_missing_total():
    merged = pd.DataFrame({
        "total": [100.0, None, 50.0],
        "region": ["East", "East", "West"],
    })

    result = revenue_by_region(merged)

    assert result.loc["East", "revenue"] == 100.0
    assert result.loc["West", "revenue"] == 50.0

def test_revenue_by_region_sorts_highest_first():
    merged = pd.DataFrame({
        "total": [50.0, 200.0],
        "region": ["West", "East"],
    })

    result = revenue_by_region(merged)

    assert result.index[0] == "East"

Output / Note

test_cleaning.py ....... [100%] 7 passed in 0.05s

Look at what you've actually built, end to end: two messy CSV files in, cleaned and merged data, an honest handling of a broken value and a missing relationship, a real business answer — revenue by region — calculated correctly around those gaps rather than despite them, saved in the format real pipelines actually use, and a test suite proving every piece of it works, on command, forever.

That's the whole arc of these two weeks, in one project. Week 1 gave you the raw material — variables, functions, files, the habit of testing instead of just looking. Week 2 gave you the tools built specifically for tables of real, messy data — NumPy's speed, pandas's cleaning and grouping power, real file formats, and a way to test not just a single value, but an entire table at once.

You started Week 1 with print("Hello, Data Engineer"). You're finishing Week 2 with a tested, working, honestly-handled data pipeline. That's not a small distance to have covered.

Try It Yourself: Add a third source file, products.csv, with a customer_id-linked favorite_category column, merge it into the pipeline as a third table, and extend revenue_by_region into a new function, revenue_by_region_and_category, grouping by both columns at once — exactly the two-level groupby you practiced back in Chapter 15.4's closing exercise. Write at least one test for it before considering it done.