Pandas · Lesson 12 of 12

Project: End-to-End Sales Analysis

An end-to-end pandas sales analysis project: load raw CSV data, clean it, join lookups, then build KPIs, monthly trends, rankings and an exportable report.

Intermediate
25 min read
4 objectives

Before this lessonLesson 11: Performance and Large Datasets

What you will learn

Plan an analysis around concrete business questions
Chain loading, cleaning, joining and feature steps into a repeatable pipeline
Answer questions with groupby, pivot tables, windows and rankings
Package results into a tidy report ready to export

Your Progress

0 of 12 lessons 0%

Lessons0 / 12
Completed0
Est. time left~ 3 hours

Create a free account to keep your progress on every device.

Tip: pressing Next marks this lesson complete automatically.

You now know the individual pieces of pandas. Real work is about stringing them together: raw data comes in messy, someone asks a handful of business questions, and you need answers you can trust and rerun next month. In this project you will analyze a small online store's orders from start to finish.

The dataset is tiny on purpose so every output fits on screen, but the steps are exactly what you would do with a million rows. Each code block below is self-contained so you can run it in the browser, which means the data setup repeats at the top. In a real project it would be one script or notebook.

The questions

Always start by writing down what you need to answer. It keeps the analysis focused and tells you which columns matter. The store manager wants to know:

What were total revenue, order count and average order value?
How did revenue develop month by month, and how fast is it growing?
Which product categories and regions drive the most revenue?
Who are the top customers, and what share of revenue do they bring?

Step 1: load and inspect

The raw export has the usual problems: prices as text with a currency symbol, inconsistent capitalization, a duplicated order, a missing quantity and a refund row. Load it with the right types for the columns that are already clean, then inspect.

import pandas as pd
from io import StringIO

raw = """order_id,date,customer,product_id,qty,price,status
1001,2026-01-05,Ada,P1,2,$25.00,paid
1002,2026-01-17,grace ,P2,1,$300.00,paid
1003,2026-01-17,grace ,P2,1,$300.00,paid
1004,2026-02-02,Linus,P3,,$8.00,paid
1005,2026-02-14,Ada,P2,1,$300.00,paid
1006,2026-02-20,Ken,P1,4,$25.00,refunded
1007,2026-03-03,Margaret,P4,2,$150.00,paid
1008,2026-03-09,Ada,P3,10,$8.00,paid
1009,2026-03-21,Ken,P4,1,$150.00,paid
1010,2026-03-28,Grace,P1,3,$25.00,paid
"""
orders = pd.read_csv(StringIO(raw), parse_dates=["date"], dtype={"order_id": "string"})
print(orders.shape)
print(orders.isna().sum()[lambda s: s > 0])
print(orders["status"].value_counts())

Output

(10, 7)
qty    1
dtype: int64
status
paid        9
refunded    1
Name: count, dtype: int64

Step 2: clean with a pipeline

Put the cleaning in a function so it is documented, testable and rerunnable. Each step fixes one problem: parse the price, normalize names, drop exact duplicate orders (same everything except the ID), fill the missing quantity and remove refunds.

import pandas as pd
from io import StringIO

raw = """order_id,date,customer,product_id,qty,price,status
1001,2026-01-05,Ada,P1,2,$25.00,paid
1002,2026-01-17,grace ,P2,1,$300.00,paid
1003,2026-01-17,grace ,P2,1,$300.00,paid
1004,2026-02-02,Linus,P3,,$8.00,paid
1005,2026-02-14,Ada,P2,1,$300.00,paid
1006,2026-02-20,Ken,P1,4,$25.00,refunded
1007,2026-03-03,Margaret,P4,2,$150.00,paid
1008,2026-03-09,Ada,P3,10,$8.00,paid
1009,2026-03-21,Ken,P4,1,$150.00,paid
1010,2026-03-28,Grace,P1,3,$25.00,paid
"""
orders = pd.read_csv(StringIO(raw), parse_dates=["date"], dtype={"order_id": "string"})

def clean(df):
    return (
        df
        .assign(
            price=lambda d: d["price"].str.replace("$", "", regex=False).astype(float),
            customer=lambda d: d["customer"].str.strip().str.title(),
            qty=lambda d: d["qty"].fillna(1).astype(int),
        )
        .drop_duplicates(subset=["date", "customer", "product_id", "qty", "price"])
        .query("status == 'paid'")
        .drop(columns="status")
        .reset_index(drop=True)
    )

clean_orders = clean(orders)
print(clean_orders)

Output

  order_id       date  customer product_id  qty  price
0     1001 2026-01-05       Ada         P1    2   25.0
1     1002 2026-01-17     Grace         P2    1  300.0
2     1004 2026-02-02     Linus         P3    1    8.0
3     1005 2026-02-14       Ada         P2    1  300.0
4     1007 2026-03-03  Margaret         P4    2  150.0
5     1008 2026-03-09       Ada         P3   10    8.0
6     1009 2026-03-21       Ken         P4    1  150.0
7     1010 2026-03-28     Grace         P1    3   25.0

Step 3: enrich with a lookup

The orders only have product IDs. A product table holds names and categories, and a customer table holds regions. Join them with merge and add the revenue and month columns every later question needs. validate="many_to_one" makes pandas raise an error if a lookup table accidentally has duplicate keys, which would silently double revenue.

import pandas as pd

clean_orders = pd.DataFrame({
    "order_id": ["1001", "1002", "1004", "1005", "1007", "1008", "1009", "1010"],
    "date": pd.to_datetime(["2026-01-05", "2026-01-17", "2026-02-02", "2026-02-14",
                            "2026-03-03", "2026-03-09", "2026-03-21", "2026-03-28"]),
    "customer": ["Ada", "Grace", "Linus", "Ada", "Margaret", "Ada", "Ken", "Grace"],
    "product_id": ["P1", "P2", "P3", "P2", "P4", "P3", "P4", "P1"],
    "qty": [2, 1, 1, 1, 2, 10, 1, 3],
    "price": [25.0, 300.0, 8.0, 300.0, 150.0, 8.0, 150.0, 25.0],
})
products = pd.DataFrame({
    "product_id": ["P1", "P2", "P3", "P4"],
    "product": ["Mouse", "Desk", "Cable", "Chair"],
    "category": ["accessories", "furniture", "accessories", "furniture"],
})
customers = pd.DataFrame({
    "customer": ["Ada", "Grace", "Linus", "Margaret", "Ken"],
    "region": ["EMEA", "AMER", "EMEA", "AMER", "APAC"],
})

sales = (
    clean_orders
    .merge(products, on="product_id", how="left", validate="many_to_one")
    .merge(customers, on="customer", how="left", validate="many_to_one")
    .assign(
        revenue=lambda d: d["qty"] * d["price"],
        month=lambda d: d["date"].dt.to_period("M"),
    )
)
print(sales[["order_id", "customer", "product", "category", "region", "revenue", "month"]])
print("unmatched:", sales[["product", "region"]].isna().sum().sum())

Output

  order_id  customer product     category region  revenue    month
0     1001       Ada   Mouse  accessories   EMEA     50.0  2026-01
1     1002     Grace    Desk    furniture   AMER    300.0  2026-01
2     1004     Linus   Cable  accessories   EMEA      8.0  2026-02
3     1005       Ada    Desk    furniture   EMEA    300.0  2026-02
4     1007  Margaret   Chair    furniture   AMER    300.0  2026-03
5     1008       Ada   Cable  accessories   EMEA     80.0  2026-03
6     1009       Ken   Chair    furniture   APAC    150.0  2026-03
7     1010     Grace   Mouse  accessories   AMER     75.0  2026-03
unmatched: 0

Step 4: answer the questions

With a clean, enriched table, each question is a few lines. Start with the headline KPIs, then the monthly trend with growth, then the breakdowns.

import pandas as pd

sales = pd.DataFrame({
    "date": pd.to_datetime(["2026-01-05", "2026-01-17", "2026-02-02", "2026-02-14",
                            "2026-03-03", "2026-03-09", "2026-03-21", "2026-03-28"]),
    "customer": ["Ada", "Grace", "Linus", "Ada", "Margaret", "Ada", "Ken", "Grace"],
    "category": ["accessories", "furniture", "accessories", "furniture",
                 "furniture", "accessories", "furniture", "accessories"],
    "region": ["EMEA", "AMER", "EMEA", "EMEA", "AMER", "EMEA", "APAC", "AMER"],
    "revenue": [50.0, 300.0, 8.0, 300.0, 300.0, 80.0, 150.0, 75.0],
})
sales["month"] = sales["date"].dt.to_period("M")

# 1. Headline KPIs
print(f"revenue: {sales['revenue'].sum():.0f}, orders: {len(sales)}, "
      f"avg order: {sales['revenue'].mean():.2f}")

# 2. Monthly trend with growth and running total
monthly = sales.groupby("month")["revenue"].sum().to_frame()
monthly["growth_pct"] = (monthly["revenue"].pct_change() * 100).round(1)
monthly["ytd"] = monthly["revenue"].cumsum()
print(monthly)

# 3. Category by region
print(sales.pivot_table(index="category", columns="region", values="revenue",
                        aggfunc="sum", fill_value=0, margins=True, margins_name="total"))

Output

revenue: 1263, orders: 8, avg order: 157.88
         revenue  growth_pct     ytd
month                               
2026-01    350.0         NaN   350.0
2026-02    308.0       -12.0   658.0
2026-03    605.0        96.4  1263.0
region        AMER   APAC   EMEA   total
category                                
accessories   75.0    0.0  138.0   213.0
furniture    600.0  150.0  300.0  1050.0
total        675.0  150.0  438.0  1263.0

February dipped 12% after the big January desk order, then March almost doubled thanks to two chair orders. One large order can swing a small store's month, so look at order counts alongside revenue. Furniture is fewer orders but most of the money, which is worth pointing out: a campaign on accessories would need a lot of volume to matter.

Step 5: rank customers

"Top customers" needs total revenue per customer, a rank and a cumulative share, which tells you how concentrated the business is.

import pandas as pd

sales = pd.DataFrame({
    "customer": ["Ada", "Grace", "Linus", "Ada", "Margaret", "Ada", "Ken", "Grace"],
    "revenue": [50.0, 300.0, 8.0, 300.0, 300.0, 80.0, 150.0, 75.0],
})

top = (
    sales.groupby("customer")
    .agg(revenue=("revenue", "sum"), orders=("revenue", "size"))
    .sort_values("revenue", ascending=False)
    .assign(
        rank=lambda d: d["revenue"].rank(ascending=False, method="min").astype(int),
        share_pct=lambda d: (d["revenue"] / d["revenue"].sum() * 100).round(1),
        cum_share_pct=lambda d: d["share_pct"].cumsum().round(1),
    )
)
print(top)

Output

          revenue  orders  rank  share_pct  cum_share_pct
customer                                                 
Ada         430.0       3     1       34.0           34.0
Grace       375.0       2     2       29.7           63.7
Margaret    300.0       1     3       23.8           87.5
Ken         150.0       1     4       11.9           99.4
Linus         8.0       1     5        0.6          100.0

Two customers bring in nearly two thirds of revenue. On a real dataset you would check whether that concentration is a risk worth flagging.

Step 6: package the report

Finally, collect the answers into tidy tables with clear column names and export them. For people, an Excel workbook with one sheet per question works well. For downstream code or dashboards, write Parquet. Here we print a CSV to show the shape.

import pandas as pd
from io import StringIO

monthly = pd.DataFrame({
    "month": ["2026-01", "2026-02", "2026-03"],
    "revenue": [350.0, 308.0, 605.0],
})
monthly["growth_pct"] = (monthly["revenue"].pct_change() * 100).round(1)

buf = StringIO()
monthly.to_csv(buf, index=False)
print(buf.getvalue().strip())

# In a real project:
# with pd.ExcelWriter("sales_report.xlsx") as xl:
#     monthly.to_excel(xl, sheet_name="monthly", index=False)
#     top.to_excel(xl, sheet_name="customers")

Output

month,revenue,growth_pct
2026-01,350.0,
2026-02,308.0,-12.0
2026-03,605.0,96.4

Recap

Start from written business questions; they decide which columns and steps matter.
Put cleaning in a function built from chained steps so it is repeatable and reviewable.
Use merge with validate to enrich data without silently duplicating rows.
groupby, pivot_table, pct_change, cumsum and rank answer most business questions.
Record assumptions and export tidy, clearly named tables for your audience.

# Write your solution here

Finished reading? Mark this lesson complete to track your progress.

Last lessonFinish PandasMark this lesson complete and pick your next course.