Why this matters
Priya (made up) runs the office at Lakeside Office Supply. Every quarter she totals the sales export by customer with a calculator, and every quarter one total is off by a missed row.
Python, with its free pandas library, adds up any number of rows the same way every time, and Google Colab runs it in your browser with nothing to install. In this module you put six orders in a table, total the quarter and rank the customers, typing every line yourself, one or two at a time.
What you’ll build
A Colab notebook with five short cells: load pandas, two lists (customers and amounts), a DataFrame, the quarter total and a ranked total per customer.
With the sample data: $8,180 in all; Cobalt Legal $3,170, Maple Dental $2,230, Northside Realty $2,140 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; notebooks save to your Google Drive. The first time, Colab asks you to accept its terms.
Concepts
3 ideas you’ll use in the practice.
A notebook is code in small cells
A Colab notebook is a page of cells. Type a line or two, press Shift+Enter, and the answer appears under the cell; a new cell opens below. The first run takes a few seconds while Colab starts a computer for you.
import pandas as pd loads pandas, the free table library almost every Python data job uses, under the short name pd.
Lists and a DataFrame
A list holds values in order: customers = ['Maple Dental', ...] for text (in quotes) and amounts = [1250, ...] for numbers (no quotes). Colab adds closing brackets and quotes for you; type over them.
pd.DataFrame({'customer': customers, 'amount': amounts}) puts the two lists side by side as columns, like a spreadsheet. With a real export you would write pd.read_csv('sales.csv') instead.
Total and rank
sales['amount'].sum() adds one column. sales.groupby('customer')['amount'].sum() gives one total per customer, and .sort_values(ascending=False) puts the biggest first. np.int64(8180) is simply how pandas labels the whole number 8180.
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. What does Shift+Enter do in Colab?
- Adds a new line in the cell
- Runs the cell and opens a new one below
- Saves the notebook as a PDF
Show the answer
Runs the cell and opens a new one below. Enter adds a line inside a cell; Shift+Enter runs it.
2. Which line gives one total per customer?
- sales['amount'].sum()
- sales.groupby('customer')['amount'].sum()
- sales.sort_values()
Show the answer
sales.groupby('customer')['amount'].sum(). groupby makes one group per customer and sum adds each group. The first option adds the whole column.
3. With the sample data, who is the top customer?
- Maple Dental, $2,230
- Cobalt Legal, $3,170
- Northside Realty, $2,140
Show the answer
Cobalt Legal, $3,170. Cobalt Legal has two orders: $860 + $2,310 = $3,170.
Python: Combine Monthly Sales Exports into One Quarter
Next: three monthly exports instead of one, stacked into a quarter with pandas.
Start module 2 →