Python Lesson 10: pandas — Summaries and Pivot Tables

Python Lesson 10: pandas — Summaries and Pivot Tables featured illustration

📎 This article includes 3 downloadable practice files ↓

⏱ 2 min read

📘 Python Beginner Course · Lesson 10 of 12

Advertisement
In this article
  1. groupby: totals per group
  2. Several summaries at once
  3. pivot_table: rows × columns
  4. Top-N and share
  5. Merge: VLOOKUP for tables
  6. Where beginners go wrong
  7. Practice
  8. Continue learning

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.

Advertisement

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
💡 Add .reset_index() after a groupby to turn the group labels back into a normal column, which makes the result easier to save or merge.

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

Problem Fix
Months sort as Apr, Aug, Jul… Group by “YYYY-MM” text or real dates
Counting the wrong column count skips missing values; use size() for rows
Merge creates extra rows Duplicate keys in the lookup table

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.

Advertisement

📎 Practice files for this article

⬇ Download all 3 files (ZIP · 2 KB)

  • 🐍
    Lesson 10 scriptThe complete script from this lesson, tested.
    ⬇ PY · 693 B
  • 📄
    Expected outputWhat the script prints when you run it.
    ⬇ TXT · 676 B
  • 🧾
    sales.csv120 orders (July-September 2026) used by the scripts.
    ⬇ CSV · 7 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free · AI can be wrong