Grouping and Aggregating

Most business questions have the same shape. What's the total of this, for each of those? Revenue for each city. Profit for each product. Group by answers all of them, and once it clicks, you'll wonder how you ever lived without it.

• Total of this, for each of those
• One shape, every question
• Group by

Which cafe is losing out?

Group by splits the table by city. Agg computes several summaries at once. Assign adds a new margin column, and sort values ranks the result. Run it. Here's the founder's answer, on one screen. Look at the bottom of the table. Those are the weakest cafes. Reading code in this chained style, one step per line, is a habit that's worth building.

import pandas as pd

sales = pd.read_csv("sales.csv")

city = (sales.groupby("city")
        .agg(revenue=("revenue", "sum"), profit=("profit", "sum"), units=("units", "sum"))
        .assign(margin=lambda d: d["profit"] / d["revenue"])
        .sort_values("profit", ascending=False))

print(city.round(2).head(5).to_string())
print("...")
print(city.round(2).tail(2).to_string())

          revenue     profit   units  margin
city
Osaka   786350.73  510866.83  189299    0.65
Milan   674849.79  438426.99  162551    0.65
Bergen  647906.11  420927.91  155943    0.65
Tokyo   623701.22  405183.72  149942    0.65
Lima    603236.91  391718.11  145193    0.65
...
         revenue     profit  units  margin
city
Abuja  357365.78  232087.58  86029    0.65
Cairo  354756.38  230397.38  85314    0.65

Pivot tables

A pivot table shows two groupings at once, in a grid. Regions across the rows, and categories across the columns. The second table shows each row as percentages. Run it. Now compare the regions. It lets you see at a glance whether one region depends more on food. Anyone who's used Excel knows pivot tables. Pandas does the same, in one line.

import pandas as pd

sales = pd.read_csv("sales.csv")

table = sales.pivot_table(index="region", columns="category", values="profit",
                          aggfunc="sum").round(0)
print(table.to_string())
print()
print((table.div(table.sum(1), axis=0) * 100).round(1).to_string())

category     Coffee      Food       Tea
region
Africa     725392.0  249483.0   83557.0
Americas   509913.0  173681.0   58509.0
Asia       627482.0  215550.0   73019.0
Europe    1009652.0  348895.0  116763.0

category  Coffee  Food  Tea
region
Africa      68.5  23.6  7.9
Americas    68.7  23.4  7.9
Asia        68.5  23.5  8.0
Europe      68.4  23.6  7.9

Summary