Merge and Group: a Wholesale Ledger in pandas

Built-in AI tutor. It can see the code in the cell you are working on and the error you got. It points at the line and asks questions. It does not write the answer.

Ridgeline Coffee Roasters sells beans to four cafes on account. Three small tables: the cafes, the orders, and the receipts that paid them. This page joins them the way XLOOKUP does, checks the row count after every join, totals by group the way a PivotTable does, and ends on the open balance: what each cafe still owes. Lab 7 asks you to do the same on the Summit Gear ledger.

The three tables

cafes: C1 Pine Street (North), C2 Harbor (South), C3 Mill Creek (North), C4 Lakeside (East).
orders: six orders, O1 to O6. O5 is for a cafe id, C5, that is not in the cafes table.
receipts: five payments. Order O4 was paid in two pieces. Order O6 was overpaid.

Count rows after every merge. A left merge keeps every row of the left table, so the row count should not change. If it goes up, a key repeats on the right. If you used an inner merge and it went down, you just lost the rows with no match, like O5.

The notebook

Run the cells top to bottom. Each one uses names from the cells above it, so skipping one gives a NameError further down. Edit any cell and run it again.

Check yourself

Two functions on this ledger. Inside these boxes orders, cafes and receipts exist, and the tests also call your function on other tables.