From Messy Export to One-Page Report

Take a real, untidy sales export and turn it into a report someone would actually read: clean the columns, group the numbers, join a lookup table, chart the result, and write a conclusion that admits what the data cannot say.

Advanced 60–75 minutes

About this project

Someone sends you an export and asks “how did we do last month?” The file has a trailing space in a column name, the same three regions spelled ten different ways, and one blank cell that will quietly become a NaN and poison a total. This is what real data looks like.

Why it is worth building: the CSV Cleaner tidied a file with the standard library. This does the same job with pandas and then keeps going, grouping, joining a second table, and drawing a chart. That is the point at which a script stops being a cleaner and becomes an analysis. The last step is the one most tutorials skip: writing down what the numbers mean, and what they do not.

Every step here runs on this page, chart included.

Twelve untidy rows in, a three-row summary and a chart out. The cleaning is most of the work.

Build it step by step

The order matters: look at the data before touching it, clean it before summarizing it, and only chart something you have already checked as a number. Charting first is how people publish confident nonsense.

Step 0: The export you have been sent

Twelve orders. Read it with a suspicious eye: the region column header has a trailing space, region names appear in every casing imaginable, and order 1004 has no units at all.

Example · sales.csv 
order_id,date,region ,product,units,unit_price
1001,2026-04-02, north ,Widget,12,9.99
1002,2026-04-02,NORTH,Gadget,3,24.50
1003,2026-04-03,south,Widget,7,9.99
1004,2026-04-05, South,Doohickey,,4.75
1005,2026-04-06,north,Gadget,5,24.50
1006,2026-04-09,EAST,Widget,20,9.99
1007,2026-04-11,east ,Doohickey,14,4.75
1008,2026-04-12,south,Gadget,2,24.50
1009,2026-04-15,North,Doohickey,9,4.75
1010,2026-04-18,east,Widget,6,9.99
1011,2026-04-20,SOUTH,Widget,11,9.99
1012,2026-04-22,north,Widget,4,9.99
Three regions, spelled ten ways. One missing value.

Step 1: Look before you touch anything

The first thing to do with an unfamiliar file is not to clean it — it is to find out how bad it is. Shape, column names, and the actual distinct values of the column you plan to group by.

Example 
import pandas as pd

sales = pd.read_csv("sales.csv")

print("shape:  ", sales.shape)
print("columns:", sales.columns.tolist())
print("regions:", sales["region "].unique().tolist())
print("missing units:", int(sales["units"].isna().sum()))
Output 
shape:   (12, 6)
columns: ['order_id', 'date', 'region ', 'product', 'units', 'unit_price']
regions: [' north ', 'NORTH', 'south', ' South', 'north', 'EAST', 'east ', 'North', 'east', 'SOUTH']
missing units: 1
Ten distinct spellings of three regions. Grouping now would produce ten rows.

Step 2: Clean, deliberately

Three fixes, each a decision rather than a reflex.

Column names: strip them all, once. Region: strip and lower-case so the ten spellings collapse to three. Missing units: here, a blank means none were shipped, so 0 is right, but that is a judgment about this data, and it belongs in the conclusion.

Example 
sales.columns = [c.strip() for c in sales.columns]
sales["region"] = sales["region"].str.strip().str.lower()
sales["units"] = sales["units"].fillna(0).astype(int)
sales["revenue"] = (sales["units"] * sales["unit_price"]).round(2)

print("regions now:", sorted(sales["region"].unique()))
print("rows:", len(sales))
print("total revenue:", round(sales["revenue"].sum(), 2))
Output 
regions now: ['east', 'north', 'south']
rows: 12
total revenue: 953.65
Ten spellings became three regions, and no rows were lost.

Step 3: Summarize

Now the actual question. groupby with named aggregations produces a table whose columns are already called what you want them called, so nothing downstream has to rename anything.

Example 
by_region = (sales.groupby("region")
             .agg(orders=("order_id", "count"),
                  units=("units", "sum"),
                  revenue=("revenue", "sum"))
             .round(2)
             .reset_index())

print(by_region.to_string(index=False))
Output 
region  orders  units  revenue
  east       3     40   326.24
 north       5     33   398.59
 south       4     20   228.82
Twelve rows became three, which is what a summary is.

Step 4: Join what the export did not include

The export has no managers and no targets, because that lives in a different system. A merge joins the two on their shared column, which is the same operation as a SQL join and the reason pandas replaces so many spreadsheets.

how="left" keeps every summary row even if the lookup is missing one. Losing rows silently in a join is a genuinely common and expensive mistake.

Example 
regions = pd.read_csv("regions.csv")
print(regions.to_string(index=False))
print()

report = by_region.merge(regions, on="region", how="left")
report["hit_target"] = report["revenue"] >= report["target"]

print(report.to_string(index=False))
Output 
region manager  target
 north     Ada     350
 south     Sam     250
  east     Rae     300

region  orders  units  revenue manager  target  hit_target
  east       3     40   326.24     Rae     300        True
 north       5     33   398.59     Ada     350        True
 south       4     20   228.82     Sam     250       False
Two tables in, one report out, and now it says something.

Step 5: Chart the thing you already checked

Only now, with the numbers verified, is it safe to draw them. A bar chart compares separate categories; the target goes on as a marker so the chart answers the same question the table does.

Example 
import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(6, 3.5))
ax.bar(report["region"], report["revenue"], color="#2b7cd3", label="revenue")
ax.scatter(report["region"], report["target"], color="#d34b2b", zorder=3, label="target")

ax.set_title("Revenue by region vs target, April 2026")
ax.set_xlabel("region")
ax.set_ylabel("revenue ($)")
ax.legend()
plt.tight_layout()
plt.show()

print("regions charted:", len(report))
print("above target:   ", int(report["hit_target"].sum()), "of", len(report))
Output 
regions charted: 3
above target:    2 of 3
The chart appears below the output. It says the same thing as the table — deliberately.

Step 6: Say what it means, and what it does not

The step that turns output into a report. Two sentences of finding, one of caveat, and the caveat is not modesty: it is the difference between analysis and a confident guess.

Example 
lines = [
    "April 2026 — revenue by region",
    "",
    f"Total revenue: ${report['revenue'].sum():,.2f} across {int(report['orders'].sum())} orders.",
    f"Best region: {report.loc[report['revenue'].idxmax(), 'region']} "
    f"(${report['revenue'].max():,.2f}).",
    f"{int(report['hit_target'].sum())} of {len(report)} regions met target; "
    f"{', '.join(report.loc[~report['hit_target'], 'region'])} did not.",
    "",
    "Caveats: order 1004 had no units recorded and was counted as 0, which",
    "understates south. Twelve orders in one month is too few to call a trend.",
]
report_text = "\n".join(lines)
print(report_text)
Output 
April 2026 — revenue by region

Total revenue: $953.65 across 12 orders.
Best region: north ($398.59).
2 of 3 regions met target; south did not.

Caveats: order 1004 had no units recorded and was counted as 0, which
understates south. Twelve orders in one month is too few to call a trend.
The finding, then the honest limits of it.

The finished report script

The whole pipeline in one file, with a command-line argument so it works on any export with the same columns.

Example · report.py 
"""Turn a messy sales export into a one-page report."""
import argparse
from pathlib import Path

import matplotlib
matplotlib.use("Agg")            # no window needed; we only save files
import matplotlib.pyplot as plt
import pandas as pd


def load_and_clean(path):
    """Read the export and normalize the columns we group and join on."""
    sales = pd.read_csv(path)
    sales.columns = [c.strip() for c in sales.columns]
    sales["region"] = sales["region"].str.strip().str.lower()
    sales["units"] = sales["units"].fillna(0).astype(int)
    sales["revenue"] = (sales["units"] * sales["unit_price"]).round(2)
    return sales


def summarize(sales, regions):
    """One row per region, joined to its manager and target."""
    by_region = (sales.groupby("region")
                 .agg(orders=("order_id", "count"),
                      units=("units", "sum"),
                      revenue=("revenue", "sum"))
                 .round(2)
                 .reset_index())
    report = by_region.merge(regions, on="region", how="left")
    report["hit_target"] = report["revenue"] >= report["target"]
    return report


def chart(report, path):
    fig, ax = plt.subplots(figsize=(6, 3.5))
    ax.bar(report["region"], report["revenue"], color="#2b7cd3", label="revenue")
    ax.scatter(report["region"], report["target"], color="#d34b2b", zorder=3,
               label="target")
    ax.set_title("Revenue by region")
    ax.set_xlabel("region")
    ax.set_ylabel("revenue ($)")
    ax.legend()
    fig.tight_layout()
    fig.savefig(path, dpi=110)
    plt.close(fig)


def conclusion(report):
    missed = ", ".join(report.loc[~report["hit_target"], "region"]) or "none"
    return (f"Total revenue: ${report['revenue'].sum():,.2f} across "
            f"{int(report['orders'].sum())} orders.\n"
            f"Best region: {report.loc[report['revenue'].idxmax(), 'region']}.\n"
            f"{int(report['hit_target'].sum())} of {len(report)} met target; "
            f"{missed} did not.\n"
            "Caveat: missing unit counts were treated as 0.")


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("sales", nargs="?", default="sales.csv")
    parser.add_argument("--regions", default="regions.csv")
    parser.add_argument("--out", default="output")
    args = parser.parse_args()

    out = Path(args.out)
    out.mkdir(exist_ok=True)

    report = summarize(load_and_clean(args.sales), pd.read_csv(args.regions))
    report.to_csv(out / "summary.csv", index=False)
    chart(report, out / "revenue.png")
    (out / "conclusion.txt").write_text(conclusion(report), encoding="utf-8")

    print(report.to_string(index=False))
    print()
    print(conclusion(report))


if __name__ == "__main__":
    main()
Output 
$ python report.py
region  orders  units  revenue manager  target  hit_target
  east       3     40   326.24     Rae     300        True
 north       5     33   398.59     Ada     350        True
 south       4     20   228.82     Sam     250       False

Total revenue: $953.65 across 12 orders.
Best region: north.
2 of 3 met target; south did not.
Caveat: missing unit counts were treated as 0.

$ ls output/
conclusion.txt  revenue.png  summary.csv
One command, three output files, and a summary on screen.

Keep going, make it your own

The pipeline works. These are the upgrades that make it something a colleague would ask you to re-run.

Group by month as well as region

Parse the date and group by both. pd.to_datetime unlocks .dt.to_period("M"), and grouping by two columns is the same call with a list, which turns a one-month snapshot into a trend.

Example 
sales["date"] = pd.to_datetime(sales["date"])
sales["month"] = sales["date"].dt.to_period("M")
by_month = sales.groupby(["month", "region"])["revenue"].sum().round(2)

Flag the outliers rather than eyeballing them

An order far from the norm is usually either the interesting one or a data-entry error. Both are worth surfacing automatically instead of hoping someone spots them in a chart.

Example 
threshold = sales["revenue"].mean() + 2 * sales["revenue"].std()
outliers = sales[sales["revenue"] > threshold]
print(outliers[["order_id", "region", "revenue"]].to_string(index=False))

Write a real spreadsheet, with one sheet per table

People who ask for reports usually want Excel. ExcelWriter puts the summary and the raw rows on separate sheets in one file, which is far more useful than three loose CSVs.

Example 
with pd.ExcelWriter("report.xlsx") as writer:
    report.to_excel(writer, sheet_name="summary", index=False)
    sales.to_excel(writer, sheet_name="orders", index=False)

Read it straight out of the database

If you built the API tracker, its table is already the right shape. read_sql_query replaces read_csv and the rest of the pipeline is untouched, which is what a clean load step buys you.

Example 
import sqlite3

with sqlite3.connect("readings.db") as conn:
    sales = pd.read_sql_query("SELECT * FROM readings", conn)

Download the files

The messy export, the region lookup, and the finished report script. Run python report.py to produce summary.csv and revenue.png.

Mini exercise (medium)

Write the summarizing step yourself, on a different export. Build summarize(frame) returning one row per category with the number of orders, total revenue, and average order value rounded to two decimals, sorted by revenue, highest first, with category as an ordinary column rather than the index.

Practice here. Fill in the missing piece and click Run to try your answer in place.

import pandas as pd

orders = pd.DataFrame({
    "order_id": [1, 2, 3, 4, 5, 6],
    "category": ["tools", "toys", "tools", "books", "toys", "tools"],
    "revenue":  [40.0, 12.5, 30.0, 18.0, 7.5, 20.0],
})

def summarize(frame):
    """One row per category (orders, revenue, average), best revenue first."""
    return frame        # TODO

result = summarize(orders)
print(result.to_string(index=False))

Where to go next

You have taken data from raw export to a chart and a written conclusion, which is the full arc of Unit 12 applied to one messy file.

To feed this pipeline something you collected yourself, build the API tracker: its table drops straight into step 1. To turn the script into a command your colleagues can install, see Package and Ship a Command-Line Tool.