Why this matters
The sales system at Lakeside Office Supply (made up) exports one file per month. For the quarterly review someone copies July, August and September under each other, deletes the repeated header rows and hopes nothing was skipped.
pandas stacks tables in one line, without repeated headers and without skipping anything. In this module you make three small monthly tables in Colab, label each with its month, stack them into one quarter and total it by month and by customer.
What you’ll build
One quarter table with six orders and a month column, plus totals by month and by customer.
With the sample data: July $2,110, August $2,950 and September $2,870 ($7,930 in all); Cobalt Legal $3,170, Maple Dental $2,230, Northside Realty $1,890 and Harbor Accounting $640.
What you need
- Nothing to start the practiceThe practice is built from real Colab screens and does not touch any account.
- A free Google account, to do it for realColab is free at colab.research.google.com.
Concepts
3 ideas you’ll use in the practice.
Label before you stack
Once three months are in one table, nothing says which row came from which file. july['month'] = 'Jul' adds a month column with the same value on every row, so you can still total by month later.
pd.concat stacks tables
pd.concat([july, august, september], ignore_index=True) puts the tables under each other, as long as they have the same columns. ignore_index=True numbers the rows 0 to 5 again instead of 0, 1, 0, 1, 0, 1.
With real files, each table is one line: july = pd.read_csv('july.csv'). To match two different tables side by side, like a VLOOKUP, pandas uses merge instead.
Totals in the order you want
groupby sorts its groups A to Z by default, which would put Aug before Jul. groupby('month', sort=False) keeps them in the order they appear in the data: Jul, Aug, Sep.
Practice
Do every step yourself in a practice copy of the real tool. It checks your work, and “Do it for me” finishes any step you get stuck on.
Do it for real
Now repeat it in your own account. Tick each step as you go.
Quick check
3 questions to check you’ve got it.
1. Why add a month column before stacking?
- pandas needs it to stack
- Otherwise you cannot tell which month each row came from
- It sorts the rows
Show the answer
Otherwise you cannot tell which month each row came from. Stacked rows look alike; the month column keeps where each came from, so you can total by month.
2. What does pd.concat do?
- Matches rows side by side, like a VLOOKUP
- Stacks tables with the same columns under each other
- Deletes duplicate rows
Show the answer
Stacks tables with the same columns under each other. concat stacks; merge matches side by side; drop_duplicates removes copies.
3. With the sample data, which month sold the most?
- July, $2,110
- August, $2,950
- September, $2,870
Show the answer
August, $2,950. August: $2,310 + $640 = $2,950.
Python: Clean a Messy Invoice Export Before You Total It
Next: an export that is messy, and the three lines that clean it before you total it.
Start module 3 →