Pandas Sales Exercises: Checked Merge and Groupby Reports
Build seven reports from synthetic sales, product and customer tables. Validate joins, preserve group labels and reconcile quantities and sample revenue before exporting.
Show Table of Contents ↓
Create the practice tables 🔝
One row in sales represents a sale line. Products and customers provide lookup information through p_id and c_id. Use the sample-data tutorial to explore these tables and create CSV files. No download is required for this notebook.
import pandas as pd
from io import StringIO
def sample_tables():
sales = pd.DataFrame({
"sale_id": [1, 2, 3, 4, 5, 6, 7, 8, 9],
"c_id": [2, 2, 1, 4, 2, 3, 2, 3, 2],
"p_id": [3, 4, 3, 2, 3, 3, 2, 2, 3],
"product": ["Monitor", "CPU", "Monitor", "RAM", "Monitor", "Monitor", "RAM", "RAM", "Monitor"],
"qty": [2, 1, 3, 2, 3, 2, 3, 2, 2],
"store": ["ABC", "DEF", "ABC", "DEF", "ABC", "DEF", "ABC", "DEF", "ABC"]
})
products = pd.DataFrame({
"p_id": [1, 2, 3, 4, 5, 6, 7, 8],
"product": ["Hard Disk", "RAM", "Monitor", "CPU", "Keyboard", "Mouse", "Motherboard", "Power supply"],
"price": [80, 90, 75, 55, 20, 10, 50, 20]
})
customers = pd.DataFrame({
"c_id": [1, 2, 3, 4, 5, 6, 7, 8],
"Customer": ["Rabi", "Raju", "Alex", "Rani", "King", "Ronn", "Jem", "Tom"]
})
return sales, products, customers
sales, products, customers = sample_tables()
print("Sales:", sales.shape, "Products:", products.shape, "Customers:", customers.shape)Expected output
Sales: (9, 6) Products: (8, 3) Customers: (8, 2)Seven reports to build 🔝
Find distinct products sold; quantities by product; quantities and revenue by product; quantities by product and store; store quantities and revenue; products with no sales; and customers with no purchases. Use merge() for lookup data and groupby() for summaries. Distinguish sale lines from units, and sample revenue from profit.
Validate keys before calculating reports 🔝
Lookup keys must be unique and known. Repeated p_id and c_id in sales are expected. This practice rule requires positive quantities and nonnegative prices. Real imports also need schema and numeric parsing checks.
assert sales["sale_id"].is_unique and sales["sale_id"].notna().all()
for frame, key in [(products, "p_id"), (customers, "c_id")]:
assert frame[key].is_unique and frame[key].notna().all()
assert sales[["p_id", "c_id"]].notna().all().all()
assert sales["qty"].gt(0).all() and products["price"].ge(0).all()
print("Source lines:", len(sales), "Units:", sales["qty"].sum())Expected output
Source lines: 9 Units: 20Report 1: products sold 🔝
Inspect all sale lines, then return one row per product ID and description. The sample has CPU, Monitor and RAM sales.
print(sales)
sold = sales[["p_id", "product"]].drop_duplicates().sort_values("p_id")
print("Products sold:")
print(sold)
assert sold["p_id"].tolist() == [2, 3, 4]Expected output
sale_id c_id p_id product qty store
0 1 2 3 Monitor 2 ABC
1 2 2 4 CPU 1 DEF
2 3 1 3 Monitor 3 ABC
3 4 4 2 RAM 2 DEF
4 5 2 3 Monitor 3 ABC
5 6 3 3 Monitor 2 DEF
6 7 2 2 RAM 3 ABC
7 8 3 2 RAM 2 DEF
8 9 2 3 Monitor 2 ABC
Products sold:
p_id product
3 2 RAM
0 3 Monitor
1 4 CPUReport 2: quantities by product 🔝
Sum qty rather than count rows. A Monitor sale line can contain several units.
quantities = sales.groupby(["product", "p_id"])[["qty"]].sum()
print(quantities)
assert quantities.loc[("Monitor", 3), "qty"] == 12
assert quantities["qty"].sum() == 20Expected output
qty
product p_id
CPU 4 1
Monitor 3 12
RAM 2 7Join prices while retaining each sale line 🔝
A left merge with validate="many_to_one" checks the lookup relationship. Inspect unmatched keys before calculating revenue. Preserve descriptions from both sources to detect disagreement.
enriched = sales.merge(products, on="p_id", how="left", validate="many_to_one",
suffixes=("_sale", "_catalog"), indicator=True)
print(enriched[["sale_id", "p_id", "product_sale", "product_catalog", "price", "_merge"]])
assert len(enriched) == len(sales)
assert enriched["_merge"].eq("both").all()
assert enriched["product_sale"].equals(enriched["product_catalog"])
enriched["revenue"] = enriched["qty"] * enriched["price"]Expected output
sale_id p_id product_sale product_catalog price _merge
0 1 3 Monitor Monitor 75 both
1 2 4 CPU CPU 55 both
2 3 3 Monitor Monitor 75 both
3 4 2 RAM RAM 90 both
4 5 3 Monitor Monitor 75 both
5 6 3 Monitor Monitor 75 both
6 7 2 RAM RAM 90 both
7 8 2 RAM RAM 90 both
8 9 3 Monitor Monitor 75 bothReport 3: quantity and revenue by product 🔝
This report aggregates across stores. Fixed lookup prices are appropriate for this exercise; a real sales report normally uses the price charged for each sale, with defined discount, return and tax rules.
product_report = enriched.groupby(["p_id", "product_catalog"], as_index=False).agg(
units=("qty", "sum"), revenue=("revenue", "sum")
)
print(product_report)
assert product_report["revenue"].sum() == 1585
assert product_report.loc[product_report["p_id"].eq(3), "revenue"].iloc[0] == 900Expected output
p_id product_catalog units revenue
0 2 RAM 7 630
1 3 Monitor 12 900
2 4 CPU 1 55Report 4: quantities by product and store 🔝
Keep store in the report when grouping by store. A MultiIndex result can be restored as columns with reset_index(). Joining only on p_id without restoring group labels can lose the report context.
grouped = sales.groupby(["product", "p_id", "store"])[["qty"]].sum()
print(grouped)
flat = grouped.reset_index()
with_price = flat.merge(products, on="p_id", how="left", validate="many_to_one",
suffixes=("_sale", "_catalog"))
with_price["total_sale"] = with_price["qty"] * with_price["price"]
print("Flat report with prices:")
print(with_price)
assert with_price["total_sale"].sum() == 1585
assert "store" in with_price.columnsExpected output
qty
product p_id store
CPU 4 DEF 1
Monitor 3 ABC 10
DEF 2
RAM 2 ABC 3
DEF 4
Flat report with prices:
product_sale p_id store qty product_catalog price total_sale
0 CPU 4 DEF 1 CPU 55 55
1 Monitor 3 ABC 10 Monitor 75 750
2 Monitor 3 DEF 2 Monitor 75 150
3 RAM 2 ABC 3 RAM 90 270
4 RAM 2 DEF 4 RAM 90 360Report 5: units and turnover by store 🔝
ABC has 13 units and sample revenue 1020; DEF has 7 units and revenue 565. Totals must reconcile with the sale-line table.
store_report = enriched.groupby("store", as_index=False).agg(
units=("qty", "sum"), revenue=("revenue", "sum"), sale_lines=("sale_id", "size")
)
print(store_report)
assert store_report["units"].tolist() == [13, 7]
assert store_report["revenue"].tolist() == [1020, 565]
assert store_report["sale_lines"].sum() == len(sales)Expected output
store units revenue sale_lines
0 ABC 13 1020 5
1 DEF 7 565 4Report 6: products with no sales 🔝
A right merge retains all lookup products. Unmatched sale_id values identify products without sale lines in this sample. A membership mask gives a simpler equivalent list. This sample has no dates, so the finding applies only to the supplied data.
right_join = sales.merge(products, on="p_id", how="right", validate="many_to_one",
suffixes=("_sale", "_catalog"))
no_sales = right_join.loc[right_join["sale_id"].isna(), ["p_id", "product_catalog", "price"]]
print(no_sales)
simple = products.loc[~products["p_id"].isin(sales["p_id"])]
assert no_sales["p_id"].tolist() == [1, 5, 6, 7, 8]
assert no_sales["p_id"].tolist() == simple["p_id"].tolist()Expected output
p_id product_catalog price
0 1 Hard Disk 80
10 5 Keyboard 20
11 6 Mouse 10
12 7 Motherboard 50
13 8 Power supply 20Report 7: customers without purchases 🔝
Retain all customers with a right join and select missing sale lines. The absent purchases do not prove that these customers have never bought; the dataset may cover only part of their history.
customer_join = sales.merge(customers, on="c_id", how="right", validate="many_to_one")
no_purchase = customer_join.loc[customer_join["sale_id"].isna(), ["c_id", "Customer"]]
print(no_purchase)
assert no_purchase["Customer"].tolist() == ["King", "Ronn", "Jem", "Tom"]Expected output
c_id Customer
9 5 King
10 6 Ronn
11 7 Jem
12 8 TomReview unknown keys separately from unsold products 🔝
An unknown product ID in sales is a lookup problem, while a valid lookup product with no sales is a reporting category. Keep those cases separate. See the Data Cleaning hub and isnull().
changed = sales.copy()
changed.loc[0, "p_id"] = 999
checked = changed.merge(products[["p_id", "price"]], on="p_id", how="left", validate="many_to_one", indicator=True)
review = checked.loc[checked["_merge"].eq("left_only")]
print(review)
assert review["sale_id"].tolist() == [1]
assert review["price"].isna().all()Expected output
sale_id c_id p_id product qty store price _merge
0 1 2 999 Monitor 2 ABC NaN left_onlyReconcile reports before exporting 🔝
Each report can use a different grouping while describing the same accepted sale lines. Compare sums rather than assuming matching table shapes imply correct totals.
print("Sale lines:", len(enriched))
print("Units:", enriched["qty"].sum())
print("Line revenue:", enriched["revenue"].sum())
print("Product revenue:", product_report["revenue"].sum())
print("Store revenue:", store_report["revenue"].sum())
assert enriched["revenue"].sum() == product_report["revenue"].sum() == store_report["revenue"].sum()Expected output
Sale lines: 9
Units: 20
Line revenue: 1585
Product revenue: 1585
Store revenue: 1585Export report labels as columns 🔝
Use index=False for flat reports. Keep product and customer identifiers so later joins do not depend on names. See Input and Output, read_csv() and to_excel().
print(store_report.to_csv(index=False).rstrip())
print(product_report.to_csv(index=False).rstrip())
# Optional Colab exports:
# store_report.to_csv("store_report.csv", index=False)
# product_report.to_csv("product_report.csv", index=False)
# no_purchase.to_csv("customers_without_purchases.csv", index=False)Expected output
store,units,revenue,sale_lines
ABC,13,1020,5
DEF,7,565,4
p_id,product_catalog,units,revenue
2,RAM,7,630
3,Monitor,12,900
4,CPU,1,55Common questions 🔝
Does quantity mean number of sales? No; count sale_id rows for sale lines and sum qty for units. Why validate the merge? Duplicate lookup keys can multiply sale rows. Why preserve store? It is part of the grouped report. Can lookup prices establish actual turnover? Only under the stated fixed-price exercise rule. Does no sales mean an error? Not for a valid lookup product; unknown sale keys need separate review. Continue with pivot tables and DataFrames.
Extra exercise: add one RAM sale at DEF 🔝
Add sale_id 10 for customer 1, product 2, quantity 2 and store DEF. Predict the new unit and revenue totals, and DEF revenue. Reuse the original tables without modifying them.
Extra exercise solution 🔝
Two RAM units add 180 revenue. New totals are 22 units and revenue 1765; DEF revenue becomes 745.
extra = pd.DataFrame({"sale_id": [10], "c_id": [1], "p_id": [2],
"product": ["RAM"], "qty": [2], "store": ["DEF"]})
practice = pd.concat([sales, extra], ignore_index=True)
priced = practice.merge(products[["p_id", "price"]], on="p_id", how="left", validate="many_to_one")
assert priced["price"].notna().all()
priced["revenue"] = priced["qty"] * priced["price"]
answer = priced.groupby("store")[["qty", "revenue"]].sum()
print(answer)
print("Total units:", priced["qty"].sum(), "Revenue:", priced["revenue"].sum())
assert priced["qty"].sum() == 22
assert priced["revenue"].sum() == 1765
assert answer.loc["DEF", "revenue"] == 745
assert len(sales) == 9Expected output
qty revenue
store
ABC 13 1020
DEF 9 745
Total units: 22 Revenue: 1765Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change quantities and lookup keys, then reconcile product and store reports. Save your own copy to keep edits. All sample tables are included in the notebook.
Reference: Pandas DataFrame.merge documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials