📎 This article includes 3 downloadable practice files ↓

📘 Python Beginner Course · Lesson 10 of 12

Everything you’d do with a pivot table in Excel, pandas does with groupby and pivot_table, and the script runs the same way next month on new data.

groupby: totals per group

df["amount"] = df["qty"] * df["rate"]
df.groupby("region")["amount"].sum().sort_values(ascending=False)
df.groupby(["region", "product"])["qty"].sum()      # two levels

Several summaries at once

df.groupby("product").agg(
    orders=("order_id", "count"),
    qty=("qty", "sum"),
    sales=("amount", "sum"),
    avg_order=("amount", "mean"))

Named aggregations give clear column names. Functions: sum, count, mean, min, max, nunique, median.

pivot_table: rows × columns

df["month"] = df["date"].dt.strftime("%Y-%m")
pd.pivot_table(df, values="amount", index="region", columns="month",
               aggfunc="sum", fill_value=0, margins=True, margins_name="Total")

Regions down, months across, totals added: exactly an Excel pivot.

Top-N and share

df.groupby("customer")["amount"].sum().nlargest(3)
region = df.groupby("region")["amount"].sum()
(region / region.sum() * 100).round(1)          # % of total

Merge: VLOOKUP for tables

targets = pd.DataFrame({"region": ["North", "South", "East", "West"], "target": [1500000, 1400000, 1300000, 1600000]})
summary = region.reset_index().merge(targets, on="region", how="left")
summary["achieved"] = summary["amount"] / summary["target"]

how="left" keeps every row of the left table, like a LEFT JOIN in SQL.

Where beginners go wrong

Practice

Download the script (and data file, if listed) below into one folder. Open a terminal in that folder and run python lesson-NN.py. Compare with the expected output, then change something and run it again: that’s how Python is learned.