Introduction
The previous lessons each covered one tool. A real analysis uses them all, in an order dictated by a question someone needs answered. This lesson follows one analysis of a shop's sales from start to finish: "which product categories earn the most, and who are our best customers?". The pandas code is shown in steps; the runnable example and exercises model the key calculations in plain Python.
How it works
1. Frame the question. "Which categories earn the most?" needs revenue per category, so sales must be joined to a product catalogue that has categories and prices. "Best customers" needs a definition: here, the highest total spent.
2. Load and inspect.
sales = pd.read_csv("sales.csv", parse_dates=["date"])
products = pd.read_csv("products.csv")
sales.info()
sales.isna().sum()
Check the shape, the types, the missing values and the date range before trusting anything.
3. Clean. Normalise text keys (.str.strip().str.lower()), drop exact duplicates, and decide what to do with missing quantities.
4. Merge, with checks.
df = sales.merge(products, on="product", how="left", validate="many_to_one", indicator=True)
(df["_merge"] == "left_only").sum() # sales of products missing from the catalogue
Unmatched rows must be counted and reported, not silently dropped.
5. Compute.
df["revenue"] = df["qty"] * df["price"]
by_cat = df.groupby("category")["revenue"].sum().sort_values(ascending=False)
share = (100 * by_cat / by_cat.sum()).round(1)
top = df.groupby("customer").agg(orders=("revenue", "size"), total=("revenue", "sum"),
first=("date", "min"), last=("date", "max"))
top.nlargest(5, "total")
Named aggregations give each result column a clear name. nlargest(5, "total") returns the five biggest rows, keeping the first on ties.
6. Check. The category revenues must add up to the total revenue of the matched rows; the number of customers must match sales["customer"].nunique(). A sanity check that fails usually reveals a merge or cleaning problem.
7. Present. One short table or chart per question, with units, the period covered and what was excluded ("3 sales rows had unknown products and are excluded"). by_cat.plot.barh() draws a quick chart for a draft.
Example
# A small end-to-end analysis: merge sales with a catalogue, group, share and rank.
catalogue = {"pen": ("office", 1.5), "desk": ("furniture", 120), "lamp": ("furniture", 30)}
sales = [("pen", 10), ("desk", 1), ("lamp", 2), ("pen", 4), ("mug", 3)]
revenue, unmatched = {}, 0
for product, qty in sales:
if product not in catalogue: # how="left" with indicator: left_only rows
unmatched += 1
continue
category, price = catalogue[product]
revenue[category] = revenue.get(category, 0) + qty * price
total = sum(revenue.values())
for category in sorted(revenue, key=lambda c: (-revenue[c], c)):
print(f"{category}: {revenue[category]:.2f} ({100 * revenue[category] / total:.1f}%)")
print("unmatched sales rows:", unmatched)Line by line
- The catalogue maps each product to a category and a price, like the products table.
- Sales of unknown products are counted, like
left_onlyrows of the merge. - Revenue is added per category, like
groupby("category")["revenue"].sum(). - Categories are printed from largest to smallest with their share of the total.
Expected result
furniture: 180.00 (89.6%)
office: 21.00 (10.4%)
unmatched sales rows: 1
In the real world
Weekly sales reports, marketing dashboards and customer segmentations all follow this pattern: load, clean, merge, group, check, present. The code often runs on a schedule, so a well-structured script with checks saves hours of debugging later.
Common mistakes
- Starting with code instead of the question, and producing tables nobody needs.
- Dropping unmatched or invalid rows silently.
- Reporting numbers without checking that they add up.
- Presenting every intermediate table instead of the few that answer the question.
Best practices
- Write the question and the definition of each measure at the top of the script.
- Assert sanity checks in code, so a failure stops the report.
- Report exclusions and the period covered next to the results.
Summary
A complete analysis frames the question, loads and inspects the data, cleans it, merges with validation, computes grouped totals, shares and rankings with named aggregations and nlargest, checks that the numbers add up, and presents a few clear results with their context and exclusions.
Quick check
Key takeaways
- Start from the question and work back to the tables you need.
- Inspect, clean, merge, group, then check every total against the raw data.
- Present a few clear numbers with their context, not every intermediate table.
Practice problems for this lesson
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.