pandas · Chapter 22 of 22
Capstone: Shabnam's year-end report
By the end of this chapter you have rebuilt Kargha's year, end to end, from the raw files: the website's lines cleaned in one chain and checked with assertions, the three channels in one table with one set of columns, every weaver's payout for what was really delivered, a daily calendar and each month against Harpreet's target, and the weekly rhythm in the order people read it. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Real work
- 5 tasks
- pandas
- In the all labs plan
The first 2 chapters of pandas are free. This one comes with the all labs plan.
Why you need this
It is October 2026, and Kargha's first full year of selling in three places is over. Shabnam wants the year on one page before the weavers meet: which designs sold, where and when, how each month did against the targets, and which days of the week carry the shop. Harpreet pays the weavers from the same numbers, so they must be right to the rupee, and she wants to know they are right before the money leaves.
A year ago all of this was one spreadsheet, copied by hand every month: a total that stopped at row 1,201, a column sorted on its own. This time it is a program, and Devika has one rule for it: "Nothing new to learn. It is the whole lab at once, and every step checks itself."
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
clean.py: the website's file, cleaned and checked
Write two functions in clean.py.
clean_site(raw) is given the website's file as read_csv reads orders_site.csv, and must not change it. Return the cleaned lines, by Harpreet's rules, in this order: drop the exact duplicate lines as the file has them (before changing anything); drop the test lines (customer_id TEST); drop the lines whose qty is blank. Then: city with its spaces stripped, in title case, and the six old names replaced (Bangalore to Bengaluru, Bombay to Mumbai, Calcutta to Kolkata, Gurgaon to Gurugram, Banaras to Varanasi, New Delhi to Delhi); sku stripped and in capitals; placed_at as dates (datetime64); qty as whole numbers; a blank discount_rs is 0; a blank payment is the text unknown; and a new column line_rs, price_rs times qty minus discount_rs.
The result has exactly these columns in this order: order_id, placed_at, customer_id, city, sku, size, qty, price_rs, discount_rs, line_rs, payment, status (pincode, delivered_on and rating are left out). Its rows are in the file's order, labelled 0, 1, 2 and so on. The Check tries the real file and 8 messy lines (a line twice, a test line, a blank qty, cities like " bombay", "GURGAON" and "new delhi", skus like "kg-stl-011", a blank discount and a blank payment).
check_clean(lines, catalogue) is given a cleaned table and the catalogue (products.csv). With assert, it stops with an AssertionError when any line appears twice (every column the same), when any sku is not in the catalogue's sku column, or when any qty is blank; on a clean table it returns nothing (None). The Check runs it on the cleaned lines, then on them with one line twice, with one sku KG-SAR-099 and with one blank qty.
- Task 2
channels.py: three channels, one table
Write all_channels(site, market, store) in channels.py. Return one DataFrame of every order line of the three channels with exactly these columns in this order: channel, order_id, placed_at, city, sku, qty, price_rs, line_rs, status. The rows are the website's first, then the marketplace's, then the store's, each in its own order, labelled 0, 1, 2 and so on.
site is the website's lines as task 1's clean_site returns them: channel is website, and the other columns are site's own (line_rs already has the discount taken off).
market is the marketplace's Orders sheet as read_excel reads it with sheet_name="Orders" and skiprows=2 (columns Order Id, Order Date, SKU, Qty, Selling Price, Ship City, Status, Commission). A re-sent order appears twice: keep only the last row of each Order Id, where the last one stands in the file. channel is marketplace; order_id is Order Id; placed_at is Order Date as dates, day first (01-10-2025 is 1 October); city is Ship City in title case, with the same six old names replaced as in task 1; sku is SKU in capitals; qty is Qty; price_rs is Selling Price as decimals ("₹1,299.00" is 1299.0); line_rs is price_rs times qty; status is Status in lower case.
store is the store's items as pd.json_normalize reads orders_store.json with record_path="items" and meta=["bill", "time", "staff", "payment"] (columns sku, qty, price, bill, time, staff, payment). channel is store; order_id is bill; placed_at is time as dates; city is Varanasi; price_rs is price; line_rs is price_rs times qty; status is delivered.
Kinds: placed_at dates, qty whole numbers, price_rs and line_rs decimals, the rest text. The Check tries the real three files and a small set: 5 website lines, 4 marketplace rows (one order re-sent, cities NEW DELHI and BANGALORE) and 3 store items.
- Task 3 · interview question
Interview: payouts.py, paid for what was never delivered
Harpreet pays each weaver a share of what their products sold for, and only for lines that were delivered: not cancelled, not returned, not still shipped. Her weaver_payouts(orders, catalogue, weavers, rate) in payouts.py adds it up, and her check, the payouts' total against rate times the delivered revenue, says she would pay more than ₹76 lakh too much.
orders is task 2's table of all three channels (status is delivered, returned, cancelled or shipped; every store line is delivered). catalogue is products.csv and weavers is weavers.csv, as read_csv reads them; rate is the share, 0.4 for 40 %.
Fix weaver_payouts so it returns a DataFrame with one row per weaver in weavers, in weavers' order, labelled 0, 1, 2 and so on, with exactly these columns in this order: weaver_id, name, revenue_rs (the sum of line_rs over the delivered lines of the weaver's products, 0 for a weaver with none) and payout_rs (rate times revenue_rs, rounded to 2 decimals). A product whose weaver is not in weavers (W40) is paid by hand and is not in the table. The Check tries the year's orders at 0.4 and 11 order lines with 11 weavers at 0.35, and accepts rupees within half a paisa.
Devika will ask: where did the extra money come from, and what would you assert about the payouts before anyone is paid?
- Task 4
timeseries.py: the daily calendar and the months against target
Write two functions in timeseries.py.
daily_calendar(orders) is given task 2's table of all three channels. Return a Series of the sum of line_rs for every calendar day, over every line that is not cancelled, labelled by the day (dates at midnight), from the first such line's day to the last, in date order; a day with no lines has 0.
month_report(daily, targets_wide) is given such a daily Series and Harpreet's targets as read_csv reads targets_wide.csv: a column category (saree, dupatta, stole, kurta) and one column per month named like 2025-10, each a target in rupees. Return a DataFrame labelled by the first day of each calendar month of daily (2025-10-01 for October), in date order, with exactly four columns in this order: revenue (the month's sum of daily), target (the month's targets of every category added up), achieved_pct (revenue divided by target, times 100) and growth_pct (the change of revenue from the month before, as a percent; missing (NaN) for the first month). All four are decimals. A month of targets_wide that daily does not have is not in the result.
The Check tries the year's orders and targets_wide.csv, and 11 order lines from 1 to 11 March 2026 with targets for March and April only.
- Task 5 · interview question
Interview: rhythm.py, a week that starts on Friday
Shabnam wants the year's weekly rhythm: the average daily revenue of each weekday. weekday_rhythm(daily) in rhythm.py gives the right numbers, but in the order Friday, Monday, Saturday, Sunday, Thursday, Tuesday, Wednesday, and her report reads them as Monday to Sunday.
daily is a Series of revenue for every calendar day, labelled by the day (dates at midnight, in date order). Fix weekday_rhythm so it returns a Series of the mean revenue of each weekday, labelled by the weekday's name, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, in that order. A weekday that daily has no day of is still there, with a missing value (NaN). The labels may be text or an ordered category.
The Check tries the year's daily revenue, Monday 2 to Wednesday 11 March 2026, and Monday 9 to Friday 13 March 2026 (no Saturday or Sunday).
Devika will ask: why did the week start on Friday, and which two ways from this lab put Monday first?
What you will learn
- Cleaning (chapters 3, 8, 9 and 10): duplicates, test orders, blanks by their meaning, text, dtypes, as one chain (21).
- Checking (new habit, old tools): assert with masks and counts.
- Combining (4, 7, 15 and 16): read each channel its own way, rename to one set of columns, concat, merge with validate.
- Aggregating (12, 13 and 17): groupby, totals by weaver, melt.
- Time (18, 19 and 20): resample, the full calendar, pct_change.
- Order (2 and 21): reindex, or an ordered category.
More in Real work
Build "Capstone: Shabnam's year-end report" yourself
Kargha, a Varanasi handloom brand, sells through a website, a marketplace and a store. You make one true picture of its sales. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of pandas are free. This one comes with the all labs plan.