Pandas interview question: fill missing dates with zeros
Problem
Shared data rulesFor each city and each day of Sep 1–27, 2026, count cancelled orders placed that day and their total amount. A day with no cancellations is a row of zeros.
Example
orders| order_id | created_at | status | city | amount_usd | Row note |
|---|---|---|---|---|---|
| 301 | 2026-09-01 11:20 | cancelled | Boston | 28 | |
| 302 | 2026-09-01 19:30 | cancelled | Boston | 18 | |
| 303 | 2026-09-02 13:00 | delivered | Boston | 50 | ← delivered — not a cancellation |
| 304 | 2026-09-03 18:00 | delivered | Washington | 22 | ← no cancellations in the city — zeros |
- Order 303: delivered — not a cancellation
- Order 304: no cancellations in the city — zeros
| city | order_date | cancelled_orders | cancelled_amount_usd |
|---|---|---|---|
| Boston | 2026-09-01 | 2 | 46 |
| Boston | 2026-09-02 | 0 | 0 |
| Boston | 2026-09-03 | 0 | 0 |
| Washington | 2026-09-01 | 0 | 0 |
| Washington | 2026-09-02 | 0 | 0 |
| Washington | 2026-09-03 | 0 | 0 |
This is an excerpt for Sep 1–3; the full answer has 2 × 27 = 54 rows. Boston has two cancellations on Sep 1 for $46, and every other cell is zero.
Solve it free
Your code runs on real pandas in your browser. No sign-up needed.
Skills it tests
More interview questions
- Pandas: ARPU and ARPPU by channelEasy · Product metrics
- Pandas: Rolling 7-day revenueEasy · Rolling aggregates
- Pandas: Cart-to-order conversionMedium · Funnels and conversion
- Pandas: Find the bugs in an average order value notebookEasy · Code review
- Pandas: Wide-to-long reshapingEasy · Pivot and unpivot