Why this matters
Real exports are messy. At Lakeside Office Supply (made up) the invoice export has "maple dental " with a trailing space, "COBALT LEGAL" in capitals next to "Cobalt Legal", amounts stored as text like "1,250", and one invoice exported twice.
Total it as it is and Cobalt Legal shows up as two clients and the copy is counted twice. In this module you clean the export in Colab with three pandas lines (tidy the names, turn the amounts into numbers, drop the duplicate) and only then total each client.
What you’ll build
A clean invoice table (three invoices, tidy names, amounts as numbers) and a total per client.
With the sample data: Maple Dental $1,250, Cobalt Legal $860 and Harbor Accounting $640, $2,750 in all.
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
2 ideas you’ll use in the practice.
Why messy data gives wrong totals
To a computer, "Cobalt Legal" and "COBALT LEGAL" are two different clients, and "1,250" in quotes is text, not a number. Totals built on that either split one client in two or fail to add up.
Three cleaning lines
invoices['client'].str.strip().str.title() removes spaces at both ends and gives every name the same capitals: "maple dental " becomes "Maple Dental".
invoices['amount'].str.replace(',', '').astype(int) removes the thousands commas and turns the text into whole numbers.
invoices.drop_duplicates() removes rows that match in every column. Run it after the names are tidied, or the two Cobalt Legal rows would not match and the copy would stay in.
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 run drop_duplicates after cleaning the names?
- It is faster
- Before cleaning, the two copies are spelled differently and do not match
- drop_duplicates only works on numbers
Show the answer
Before cleaning, the two copies are spelled differently and do not match. drop_duplicates removes exact copies. "Cobalt Legal" and "COBALT LEGAL" are not exact copies until title() makes them the same.
2. What does astype(int) do to the amount column?
- Rounds it
- Turns the text into whole numbers you can add up
- Adds a currency sign
Show the answer
Turns the text into whole numbers you can add up. Text cannot be added up; after removing the commas, astype(int) makes the values numbers.
3. Without the clean-up, what would have gone wrong?
- Nothing
- Cobalt Legal would appear twice and invoice 102 would be counted twice
- Harbor Accounting would be missing
Show the answer
Cobalt Legal would appear twice and invoice 102 would be counted twice. Two spellings make two groups, and the duplicate invoice adds $860 that was only billed once.
You reached the end of Python for Spreadsheet Work Jump Start
That completes the Python jump start: totals, combined monthly exports and a clean-up you can run on every new export.
Pick your next course →