Reshaping & Aggregating Data
import numpy as np, pandas as pd
import matplotlib.pyplot as plt
plt.rcParams.update({"figure.dpi":110,"axes.grid":True,"grid.alpha":0.25,"font.size":11})
TL, TL2, AM, RD = "#0f766e", "#2dd4bf", "#d97706", "#dc2626"
BASE = "https://raw.githubusercontent.com/johnfisher-ai/Statistics-Data-Science-AI-Visual-Book/main/data/"
fn = "python-for-data-analysis--store-orders.xlsx"
def load(sheet="Orders"):
try: return pd.read_excel("../../data/" + fn, sheet_name=sheet)
except FileNotFoundError: return pd.read_excel(BASE + fn, sheet_name=sheet)
DEMO 1 · groupby: split, apply, combine¶
The single most useful pattern in pandas. Split the rows into groups by a column, compute a summary for each group, and combine the results into a new table. Group by one key or several, and aggregate one column or many.
orders = load()
print("revenue by region:")
print(orders.groupby("region").revenue.sum().sort_values(ascending=False).round(2).to_string())
print("\nseveral stats at once, by channel:")
print(orders.groupby("channel").agg(orders=("order_id", "size"),
revenue=("revenue", "sum"),
avg_order=("revenue", "mean")).round(2).to_string())
revenue by region:
region
North 56668.25
West 55815.10
South 43835.75
East 41703.95
Central 38085.55
several stats at once, by channel:
orders revenue avg_order
channel
mobile 1046 80310.65 76.78
store 457 35118.40 76.85
web 1497 120679.55 80.61
DEMO 2 · merge: join two tables on a key¶
Facts live in one table, descriptions in another. merge joins them on a shared key, exactly like a SQL join, so you can bring product categories and costs alongside each order and compute profit.
products = load("Products")
m = orders.merge(products, on="product_id", how="left") # bring product, category, unit_cost, list_price
m["profit"] = (m.revenue - m.quantity * m.unit_cost).round(2)
print(m[["order_id", "product", "category", "revenue", "unit_cost", "profit"]].head(4).to_string(index=False))
print("\nprofit by category:")
print(m.groupby("category").profit.sum().sort_values(ascending=False).round(2).to_string())
order_id product category revenue unit_cost profit 100001 Subscription Box Beans 45.0 24.0 21.0 100002 Espresso Beans 1kg Beans 22.0 12.0 10.0 100003 Espresso Beans 1kg Beans 110.0 12.0 50.0 100004 Espresso Beans 1kg Beans 110.0 12.0 50.0 profit by category: category Equipment 56861.4 Beans 33850.2 Accessories 23498.0 Other 8132.5
DEMO 3 · pivot_table: a spreadsheet cross-tab¶
A pivot table summarizes one value across two dimensions, one down the rows and one across the columns. Here: total revenue for every region-by-category combination, in a single grid.
pt = m.pivot_table(index="region", columns="category", values="revenue", aggfunc="sum", fill_value=0)
print(pt.round(0).to_string())
print("\nrow (region) totals:")
print(pt.sum(axis=1).sort_values(ascending=False).round(0).to_string())
category Accessories Beans Equipment Other region Central 5175.0 13870.0 17753.0 1288.0 East 6426.0 10624.0 22943.0 1710.0 North 9297.0 19037.0 26732.0 1602.0 South 7486.0 14377.0 20487.0 1485.0 West 8867.0 16898.0 28003.0 2048.0 row (region) totals: region North 56668.0 West 55815.0 South 43836.0 East 41704.0 Central 38086.0
DEMO 4 · Long vs wide: melt and pivot¶
The same data can be wide (a column per category) or long (one row per region-category value). melt goes wide to long, pivot goes back. Long form is what most plotting and modeling tools want.
wide = pt.reset_index()
long = wide.melt(id_vars="region", var_name="category", value_name="revenue")
print("long form (one row per region-category):")
print(long.head(6).to_string(index=False))
back = long.pivot(index="region", columns="category", values="revenue")
print("\npivoted back to wide, identical to the pivot table:", np.allclose(back.values, pt.values))
long form (one row per region-category): region category revenue Central Accessories 5175.25 East Accessories 6426.15 North Accessories 9296.70 South Accessories 7486.50 West Accessories 8866.90 Central Beans 13869.50 pivoted back to wide, identical to the pivot table: True
DEMO 5 · Working with dates¶
Parse a date column once and the .dt accessor unlocks the year, month, weekday, and more. Grouping by month turns a pile of orders into a monthly trend, the backbone of any time-based report.
orders["order_date"] = pd.to_datetime(orders.order_date)
orders["month"] = orders.order_date.dt.to_period("M")
orders["weekday"] = orders.order_date.dt.day_name()
monthly = orders.groupby("month").revenue.sum().round(2)
print("monthly revenue (2024):"); print(monthly.to_string())
print("\npeak month:", monthly.idxmax(), "at", f"{monthly.max():,.2f}")
print("\nrevenue by weekday:")
print(orders.groupby("weekday").revenue.sum().round(0).to_string())
monthly revenue (2024): month 2024-01 19404.40 2024-02 20670.00 2024-03 21877.35 2024-04 18003.20 2024-05 17464.20 2024-06 17455.85 2024-07 22179.55 2024-08 20891.50 2024-09 22765.90 2024-10 18355.70 2024-11 17436.25 2024-12 19604.70 Freq: M peak month: 2024-09 at 22,765.90 revenue by weekday: weekday Friday 31920.0 Monday 35251.0 Saturday 36992.0 Sunday 33263.0 Thursday 30799.0 Tuesday 32775.0 Wednesday 35109.0
DEMO 6 · Method chaining: a pipeline in one expression¶
pandas methods return DataFrames, so you can chain them into a readable top-to-bottom pipeline: filter, then group, then sort, without a pile of temporary variables. This is how experienced analysts write.
result = (load()
.query("channel == 'web' and revenue > 50")
.groupby("region")
.agg(orders=("order_id", "size"), revenue=("revenue", "sum"))
.sort_values("revenue", ascending=False)
.round(2))
print("web orders over 50 dollars, by region:")
print(result.to_string())
web orders over 50 dollars, by region:
orders revenue
region
North 186 24918.80
West 171 23474.80
South 157 18882.95
East 134 17242.05
Central 123 15032.40
Wrap-up¶
This is the heart of the craft: groupby to split-apply-combine, merge to join tables, pivot_table and melt to reshape between wide and long, the .dt accessor for dates, and method chaining to write it all as one clean pipeline. With these you can answer almost any tabular question. Last stop: turning these tables into pictures.