PHASE 14Intermediate ~31 min· topic 8 of 10

Topic 14.8

Grouping, Joining and Reshaping

In one line

groupby splits a table into groups and summarises each, merge joins two tables on a shared key like a SQL join, concat stacks tables, and pivot_table turns rows into a grid. Together they answer most business questions about a table.

Think of it like this

A shop's sales slips. "How much did each city sell?" means putting the slips in piles by city and adding up each pile. That is groupby. "Which customer name goes with this customer number?" means looking in another list, a join. "Show cities down the side and products across the top" is a pivot table.

Words you'll meet

New words in this topic, in plain English. Come back here whenever one feels fuzzy.

groupby: Split rows into groups by a column's values, so each group can be summarised.
Aggregation: A summary of a group, such as sum, mean or count.
merge (join): Combine two tables by matching a key column.
Inner join: Keep only rows whose key exists in both tables.
Left join: Keep all rows of the left table, with NaN where the right table has no match.
concat: Stack tables on top of each other or side by side.
pivot table: A grid with one dimension down the side, another across the top, and a summary in each cell.
Method chaining: Calling one method after another on the result, to write a pipeline in one expression.

Step by step

01Group and aggregate

The sales table has city, product, qty and price. First compute revenue = qty * price as a new column. Then groupby("city")["revenue"].sum() gives one total per city: Delhi 77.5, Mumbai 10.0, Pune 76.0.

The index of the result is the group key, in sorted order. agg with named aggregations adds a count, units and revenue. Sorting by revenue puts Delhi first.

Group and aggregatediagram

Rendering diagram…

02Pivot table

A pivot table is the same sum, but laid out as a grid. Pune sold 16 pens and 3 books. Mumbai sold no books, so fill_value=0 writes 0 instead of NaN.

03Merge with how=

orders has a customer number but no name; custs maps numbers to names. An inner merge on cust drops order 4, whose customer 99 does not exist in custs. A left merge keeps order 4 with NaN for the name. Customer 12 has no orders, so appears in neither result.

If both tables have a column named the same that is not the key, pandas adds suffixes _x and _y. Rename columns beforehand to stay readable.

main.pywhole filepython

inner = orders.merge(custs, on="cust", how="inner")
left = orders.merge(custs, on="cust", how="left")

04Concatenate and chain

concat adds order 5 as a new row; ignore_index=True renumbers. The chain then merges names, flags big orders with assign, keeps only those with query, sorts and picks columns. Read it from top to bottom.

05Check the shape after a join

Before the merge there are 4 orders, after the left merge still 4. If it had grown, one key would have appeared several times on the right side and multiplied the rows. merge(..., validate="many_to_one") makes pandas raise an error in that case.

Try it yourself

1
Two group keys
Group by both city and product: df.groupby(["city", "product"])["qty"].sum(). The result has a two-level index. Compare it with the pivot table.
2
Which join keeps the unmatched customer?
Predict the shape of orders.merge(custs, on="cust", how="right") and how="outer" before you run them.
3
Add a second summary
Extend agg with max_qty=("qty", "max"). Which city has the biggest single order?

Code & diagrams

groupby, agg and pivot_table New tab

Change sum to mean in the first groupby and predict the Pune value before running.

Expected output

city
Delhi     77.5
Mumbai    10.0
Pune      76.0
Name: revenue, dtype: float64
        orders  units  revenue
city
Delhi        2     12     77.5
Pune         3     19     76.0
Mumbai       1      4     10.0
product  book  pen
city
Delhi       5    7
Mumbai      0    4
Pune        3   16

merge, concat and a method chain New tab

Change how="left" to how="outer" in the second line and see customer 12 appear with no order.

Expected output

   order  cust  amount  name
0      1    10   250.0  Asha
1      2    11    80.0   Ben
2      3    10    40.0  Asha
   order  cust  amount  name
0      1    10   250.0  Asha
1      2    11    80.0   Ben
2      3    10    40.0  Asha
3      4    99    15.0   NaN
(5, 3)
 order name  amount
     1 Asha   250.0
     2  Ben    80.0
     5 Chen    60.0

The same question in SQL and pandasbash

Not run here. The SQL is shown so you can compare: a groupby is a GROUP BY and a merge is a JOIN.

-- SQL
SELECT city, SUM(qty * price) AS revenue
FROM sales
GROUP BY city
ORDER BY revenue DESC;

# pandas
df.assign(revenue=df.qty * df.price).groupby("city")["revenue"].sum().sort_values(ascending=False)

Join safety: duplicate keys, validate, indicator and transform New tab

A lookup table with a repeated key silently inflates the revenue total from 370 to 660. validate turns that into an error, and indicator=True labels every row of an outer join as both, left_only or right_only so you can audit the match. transform gives each row its group's mean, which is the base for 'difference from the city average' features.

Expected output

orders: 3 after merge: 5
total before: 370.0 after: 660.0
MergeError: Merge keys are not unique in right dataset; not a many-to-one merge
   order  cust  amount  name      _merge
0    1.0    10   250.0  Asha        both
1    3.0    10    40.0  Asha        both
2    2.0    11    80.0   Ben        both
3    NaN    12     NaN  Chen  right_only
{'both': 3, 'right_only': 1, 'left_only': 0}
    city  sales  city_avg  vs_avg
0   Pune   10.0      20.0   -10.0
1   Pune   30.0      20.0    10.0
2  Delhi   50.0      50.0     0.0
   count  size  sum
g
a      1     2  1.0
b      1     1  3.0

Break it on purpose

Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.

Break #1

A join key that repeats

Merge with a lookup table that has the same key twice.

main.pywhole filepython

import pandas as pd

orders = pd.DataFrame({"cust": [10, 11]})
names = pd.DataFrame({"cust": [10, 10, 11], "name": ["Asha", "Asha K", "Ben"]})
print(orders.merge(names, on="cust").shape)

terminal

$ python main.py

── what you'll see ──

(3, 2)

Myth vs fact

Myth

groupby returns a DataFrame of the groups.

Fact

It returns a lazy GroupBy object. Nothing is computed until you apply an aggregation.

Myth

merge and concat do the same.

Fact

merge matches rows by key and adds columns; concat just stacks tables.

Myth

pandas joins are as safe as SQL foreign keys.

Fact

pandas does not enforce uniqueness. Duplicate keys silently multiply rows unless you validate.

Pro corner

Extra depth for experienced readers. New to this? Skip it for now and come back later.

▸
groupby sorts the group keys by default and drops groups whose key is NaN. Pass sort=False to keep first-seen order and dropna=False to keep the NaN group.
▸
transform returns a result with the same length as the input, aligned to the original rows (for example the group mean next to each row), while agg returns one row per group. Use transform to build features like "difference from the city average".
▸
pandas executes eagerly and in memory. The same query in DuckDB or Polars (Topic 14.10) can be much faster on large files because they plan the whole query first and run in parallel.
▸
Join semantics in detail. One-to-many keys multiply rows (a key on the left that matches m rows on the right produces m rows), and many-to-many keys produce n * m rows per key, which is how a join can explode. how="cross" is the deliberate version (every pair). A left join keeps the left row order; an outer join sorts the keys. Unlike SQL, pandas matches NaN keys with each other, so clean or drop missing keys before merging. indicator=True and validate= ("one_to_one", "one_to_many", "many_to_one") are the audit tools, and suffixes=("_left", "_right") names clashing columns.
▸
**count is not size.** In a groupby, count() counts non-missing values in each column and size() counts rows per group, so a column with gaps gives different numbers. Aggregations like sum and mean skip NaN by default. For group-wise features, groupby(...).transform("mean") keeps the original row count, and .agg shrinks to one row per group.
▸
Grouping by a column that is not a plain value (a category column, or dates) adds options: with category dtype, the pandas 2.x default lists every possible category, even empty ones, unless you pass observed=True. Set observed explicitly so the result does not depend on the version. For dates, df.groupby(pd.Grouper(key="day", freq="MS")) or resample groups by month.

Remember this

1
Recall from Topic 14.6: a DataFrame has columns and an index, and you filter it with a boolean mask. This topic builds on those. A key below means a column whose values identify the thing a row is about, such as a customer number.
2
df.groupby("city")["revenue"].sum() follows split, apply, combine: split rows into groups by the key, apply an aggregation (sum, mean, count, max) to each, and combine the results into a new Series whose index is the group key. To get several summaries at once, use named aggregation: df.groupby("city").agg(orders=("qty", "count"), units=("qty", "sum")), which makes one column per name=(column, function).
3
sort_values("revenue", ascending=False) orders rows by a column. sort_index() orders by the index. Sorting returns a new DataFrame; it does not change the original unless you assign it.
4
pd.merge(left, right, on="cust"), or left.merge(right, on="cust"), joins rows that share a key. how controls unmatched rows: inner keeps only keys in both tables, left keeps every left row (missing right values become NaN), right and outer are the other two. If you know SQL (the language for asking questions of database tables), merge is JOIN ... ON and how= is INNER, LEFT, RIGHT or FULL OUTER JOIN. The Database course (/database-design) explains the relational model and joins in depth, and groupby is the GROUP BY clause.
5
pd.concat([a, b], ignore_index=True) stacks tables vertically (more rows) when the columns match. With axis=1 it places them side by side by index. Use concat to combine files of the same shape, merge to add columns from another table by key.
6
pivot_table(index="city", columns="product", values="qty", aggfunc="sum", fill_value=0) builds a grid: one row per city, one column per product, each cell the sum of qty (fill_value=0 replaces empty cells). It is groupby plus reshaping in one step. melt does the reverse, from a wide grid back to long rows.
7
Method chaining writes a pipeline as one expression: df.merge(...).assign(big=lambda d: d["amount"] > 50).query("big").sort_values(...). Each method returns a new DataFrame, so you read it top to bottom like a recipe. assign adds columns (a lambda is a tiny unnamed function, Topic 5.6, here receiving the table so far), query filters with a string condition. Chains are compact; break them with named variables when they get long.
8
For AI work these tools build features (the input columns a model learns from): counts and averages per user, per item, per day. A joined and grouped table is what a classical machine-learning model trains on. Joins also hide bugs: a join on a key with duplicates multiplies rows, so check shape before and after.

Explain it without notes

Explain split-apply-combine and write a groupby that gives the total revenue per city.

What is the difference between inner, left and outer merges? Give a case for each.

When do you use concat and when merge?

What does a pivot table give you that a groupby does not?

After a left merge your row count grows from 1,000 to 1,400 and revenue totals jump. What happened and how do you detect and prevent it?

What is the difference between groupby(...).agg(...) and groupby(...).transform(...), and between count and size?

Practice

Given sales with shop and amount columns, compute the average amount per shop.

Left-merge a students frame (id, name) with marks (id, mark) and list who has no mark.

Write a chain that keeps rows where amount > 10, sorts by amount descending and keeps the first row.

Given sales with columns shop (A, B, A, B) and amount (10, 20, 30, 60), add a column share holding each row's amount as a fraction of its own shop's total, using transform.

Trade-offs

↔
Long chains versus named steps. A chain reads like a recipe; named intermediates are easier to inspect and debug.
↔
pandas versus SQL. pandas is great for exploration in Python; SQL is better when the data already lives in a database and is big.
↔
Wide versus long tables. Pivoted grids are good for people and charts; long tables are better for further processing and models.

Done when you can

Done when you can group by one or two keys and use named aggregations.
Done when you can choose inner, left or outer merge and say why.
Done when you can stack tables with concat and reshape with pivot_table.
Done when you can read a method chain and check shapes before and after a join.
Done when you can write the matching SQL for a groupby or merge.

Command Palette

Words you'll meet

Step by step

01Group and aggregate

02Pivot table

03Merge with how=

04Concatenate and chain

05Check the shape after a join

Try it yourself

Two group keys

Which join keeps the unmatched customer?

Add a second summary

Code & diagrams

Break it on purpose

A join key that repeats

Myth vs fact

Pro corner

Remember this

Explain it without notes

Practice

Trade-offs

Done when you can