Calendar Gaps: a day with no sale has no row, and it's dragging your average up

Before you start

New to this? A daily table has one row per day: a date and a number (sales that day, signups that day). The trap this project is about is what happens on a day when nothing happened. Many systems don’t write a 0 row for an empty day — they write no row at all. So a month with a few dead days looks like this: May 3, May 4, May 6, May 7… — May 5 is just gone. That gap is not corrupt data; it’s a real zero that was never recorded as one. Now ask a simple question: what were our average daily sales? If you average the rows you were handed, you divide by the number of days that had a row, not the number of days in the month — so every silent zero day is missing from the bottom of the fraction and the average comes out too high. The fix is one move: rebuild the full run of dates (the calendar), slot your data onto it, and fill the days that had no row with 0. This project teaches that move, and the judgment that goes with it: a missing row can mean genuinely zero (a quiet day) or not recorded (the till broke), and those two want opposite handling.

1. The Brief

You’re handed a bakery’s till export for the quarter and asked one innocent question: what were our average daily sales? One line of pandas gives you a confident, healthy number — and it’s about a third too high, because the till only writes a row on days it rang up a sale, and every quiet zero-sales day simply isn’t in the file. Average the rows that are there and you’ve answered “the average of a selling day,” not “the average per calendar day” — you divided by 46 when you should have divided by 61. This is the missing-row trap, and it shows up anywhere an export drops empty periods: point-of-sale, ad-signup analytics, sensor feeds, ticket queues. You’ll rebuild the full daily calendar so the silent zeros come back, compute the honest number, see exactly how far the naive one drifts, and — the part that separates a data engineer from a spreadsheet — decide what a blank day actually means before you fill it.

2. Difficulty & time

Difficulty 1/10. One tool (pandas), one file, one concept — rebuild the calendar and fill the zeros — and Part A’s path is fully specified. It is the floor of the scale. It sits below the difficulty-3 data-engineering projects idempotent-loads (make a re-run not double-load), data-contracts (validate a batch against schema, ranges, and cross-field rules), and three-way-match (reconcile invoices to POs and receipts): each of those has several moving parts, whereas this turns on a single reindex(...).fillna(0). It is a close cousin of the difficulty-1 missing-not-random (blanks that bias an average), but even more mechanical: there the hard part is diagnosing biased missingness, here the whole skill is one deterministic reshape. What keeps it off the very bottom is the judgment call — a missing row is not automatically a zero — which is a real decision, not a syntax drill.

3. What you’ll be able to do after

Spot a daily series with missing rows (dates that jump) and say, in one sentence, why a naive average or count over it is biased and in which direction.
Rebuild a complete daily calendar with pandas.date_range, reindex a gappy series onto it, and fill the absent days — the core move for any per-day metric.
State the two things a missing row can mean (a genuine zero vs an unrecorded day) and pick the right handling for each, instead of blindly filling 0.
Explain why the reporting period’s endpoints come from the business question, not from the first and last dates that happen to appear in the file.

4. Prereqs & time box

Part A: ~30 minutes. Part B: ~1.5 hours. Both are hard caps — if Part A runs long, you’re overthinking it. You need to read a CSV with pandas and do arithmetic on a column; nothing more.

For where to write and run Python (install, virtual environment, per-OS terminal, running a script step by step), see Start Here guide. No account and no GPU — requires_signup is empty.

5. Setup & data

The data is synthetic, generated locally by data/make_data.py from a fixed seed (byte-identical every run) — see Sources. Generate it once:

python data/make_data.py

That writes data/sales_a.csv (Part A) and data/signups_b.csv (Part B). Nothing is downloaded; there is no external source. Synthetic is the right call here: the lesson is whether your per-day figure matches the calendar-complete truth, and only generated data lets you know that truth — including the value of the days that never got a row — in advance.

6. Part A — Guided Build

Prefer the browser? If this project shows a ▶ Run button, you can run Part A right on the page — no setup. The commands below are for running on your own computer.

▶ Try it now — run the real Part A right here in your browser. This one button makes the data and runs Part A for you — you do not need to install anything, and you do not need to type any of the python … commands anywhere on this page (those are only for running on your own computer instead). First run loads Python + this project’s libraries: usually ~10–20 s, a little longer for machine-learning projects.

Run the guided script (or paste it cell-by-cell — each # %% is one step):

python part_a/analyze_sales.py

It walks six steps and writes part_a/calendar_filled_a.csv. The shape of the argument:

STEP 1. Load the till export. Checkpoint: 46 rows, and the dates jump — e.g. 2025-05-04 to 2025-05-06, with no 2025-05-05. Those gaps are zero-sales days that were never written.
STEP 2. The naive answer — the mean of the rows present. Checkpoint: about 585.74. Looks healthy. It’s the average of a selling day, not a calendar day.
STEP 3. Rebuild the calendar for the reporting quarter (1 May – 30 June), reindex onto it, and fill the gaps with 0. Checkpoint: 61 calendar days, of which 15 had no row.
STEP 4. The correct answer — the mean over all 61 days. Checkpoint: about 441.70, roughly a third below the naive number. Same money; honest denominator.
STEP 5. See why: the total is identical, only the denominator changed (÷46 vs ÷61). An assert proves the filled-calendar mean equals total / calendar days.
STEP 6. Save the rebuilt calendar (calendar_filled_a.csv) with a was_missing flag so you can eyeball the filled zeros.

Why this and not that

Why not just sales['sales'].mean()? Because it silently answers “the average on a day we made a sale.” The question was about the quarter, and a per-calendar-day average must divide by every day in the quarter — including the ones that sold nothing and so never wrote a row.
Why take the calendar from 1 May – 30 June, not from the file’s own first and last date? Because 1 May and the last days of June sold nothing, so they aren’t in the file. Build the calendar from min()/max() of the data and you’d recover the interior gaps but silently drop the zeros at the edges. The period comes from the business question, not from the rows you got.
Why fill 0 and not drop or interpolate? Only because here a blank day is a known zero (the shop was open and quiet). If instead the blanks were unrecorded days — the till broke, sales happened but weren’t logged — a 0 would understate, and you’d exclude those days and shrink the denominator instead. Deciding which world you’re in is the judgment; the fill is the easy part.

Functions you’ll meet (and where to read more)

Function	What it does here
`pandas.read_csv`	Load `sales_a.csv`, parsing `date` as real dates (STEP 1)
`pandas.date_range`	Build every day of the reporting quarter (STEP 3)
`Series.reindex`	Slot the sales onto the full calendar; absent days become `NaN` (STEP 3)
`Series.fillna`	Turn those `NaN` gap days into real `0`s (STEP 3)
`Series.mean`	The honest per-calendar-day average (STEP 4)

If something looks off

If your correct average equals your naive one, your reindex didn’t add any rows — check the dates really do jump in the file, and that you reindexed onto the full date_range, not the data’s own index.
If your “correct” number comes out lower than 441.70, you probably built the calendar from the data’s min()/max() (58 days), missing the edge zeros — use the stated quarter, 1 May – 30 June.
A number that surprises you is the point of this project, not a bug — read it against the checkpoints above before assuming you broke something.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already handles the naive version; your job is to finish the # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Same skill, new setting and a new twist. A SaaS landing page’s signups_b.csv has one row per day it got a signup. The paid ads run on weekdays only, so weekends (and the odd quiet weekday) draw nothing and have no row. Ops wants the peak 7-day moving total — the best rolling week — to size the launch. The starter prints the naive answer: a 7-row rolling sum over the rows present. It’s wrong twice: seven present rows really span nine-plus calendar days (so each “weekly” total is inflated), and it counts rows, not days in the quarter. The # TODO is the same move as Part A — rebuild the full daily calendar and fill the gaps with 0 — before you roll the window.

Acceptance criteria (checkable)

Your part_b/starter.py writes part_b/out/report.json with:

peak_7day_signups — the maximum 7-calendar-day moving total of signups,
total_days — the number of days in the reporting quarter (1 Jan – 31 Mar).

Then:

python part_b/starter.py
pytest part_b/test_solution.py -q

You’re done when the tests pass: total_days is exact and peak_7day_signups lands within 0.5% of the true calendar-aware value (the reference hits it exactly). The naive 7-row sum is about 38% too high and counts the wrong number of days — it fails both checks.

Constraints — what must be true of your solution, not how to get there

Every window must be seven calendar days, so days with no row must be present as 0 first.
total_days is every day in the quarter, not the number of rows in the file.
Don’t drop the zero days — they’re inside the windows; they just shouldn’t be skipped over.

Hints

Hint 1 — what "the same move as Part A" means here

Part A’s fix was: build the full date_range, reindex onto it, fillna(0). Do exactly that to the signups series before you compute anything. Everything downstream then works on a complete, gap-free daily series.

Hint 2 — why the naive peak is too high

rolling(7) counts seven rows. With weekends missing, seven present rows cover a Monday to the Wednesday of the following week — more than seven calendar days — so the sum piles up more than a real week’s signups. Once the zeros are filled, seven rows == seven calendar days again.

Hint 3 — the shape of the answer

daily = signups.set_index('date')['signups'].reindex(full_calendar).fillna(0), then peak = int(daily.rolling(7).sum().max()). total_days is len(full_calendar). The Series.rolling docs cover the windowing.

8. Self-check

Your calendar-aware peak should be lower than the naive 7-row sum (the real week includes the quiet weekend zeros), and total_days should be 90 — every day in the quarter, not the ~60 rows in the file.
Re-running python data/make_data.py never changes the data (it’s seeded), so your numbers shouldn’t move between runs.
If you can state, in one sentence, why filling the calendar changes the answer and which direction the naive figure drifts, you’ve got the transferable skill — the specific numbers are secondary.

9. Stretch

Add a by_weekday breakdown to Part A: after filling the calendar, group by day-of-week and see which days carry the zeros. Does the shop have a structural closed day, or are the gaps scattered?
In Part B, plot (or print) the naive 7-row rolling sum next to the calendar-aware one across the quarter and mark where they diverge most — you’ll see the gap open up around the weekends.
(Genuinely hard.) Suppose some of the missing bakery days weren’t quiet — the till was broken and real sales went unrecorded — but you don’t know which. You only know the shop is closed Sundays (genuine zeros) and that on any other missing day there’s a 20% chance the till was broken. Work out a defensible average daily sales figure and, more importantly, a range it could honestly fall in. What extra information would collapse the range to a single number?

10. Ship it

Put this in a repo README so it counts as a portfolio piece: one sentence on the trap (“an export that drops empty days made the daily average about a third too high”), the two numbers side by side (naive vs calendar-complete), and the one-line rule — rebuild the calendar and fill the true zeros before any per-day figure. Add a short note on Part B showing you transferred the same idea to a moving total (where the position of the gaps, not just their count, changes the answer). And say one sentence on the judgment call: a missing row can be a genuine zero or an unrecorded day, and you decided which before filling. That last part — recognising the same skill under a different surface, and knowing when not to fill — is what an interviewer is actually checking for.

11. Sources

See SOURCES.md. The data is synthetic, generated by data/make_data.py from a fixed seed; there is no external source, and nothing is downloaded or committed.

Next up

Finished this one? Continue the Data Engineering track:

Date Parsing Basics: a date stored as a string sorts and filters wrong → · Browse all courses