Pandas — Series, DataFrame, indexing, groupby, merge, pivot: Exercises

Before you start, keep the one-sentence model from the parent in mind: pandas aligns data by label, not by position. Almost every trap below is a place where a beginner secretly assumes position.

Level 1 — Recognition

Goal: given a task, name the correct tool. No code output needed yet.

L1.1

You have one column of numbers with named row labels "a","b","c". What pandas object is this — a Series or a DataFrame?

Recall Solution

A Series. A Series is exactly a 1-D labelled array: a (values, index) pair. One column + an index = Series. The moment you have two or more columns sharing one row index, it becomes a DataFrame.

L1.2

You want "all rows from the left table, filling NaN where the right table has no match." Which how do you pass to pd.merge?

Recall Solution

how="left". It keeps every key of the left table and inserts NaN for missing right-side columns. Compare: inner = intersection of keys, outer = union, right = mirror of left.

L1.3

You must select the row labelled "u2" and the column labelled "age" by name. Which of df["age"], df.loc[...], df.iloc[...]?

Recall Solution

df.loc["u2", "age"]. .loc is the label door. .iloc would need integer positions; plain df["age"] only grabs a column, it can't also pick a row by name in one step.

Figure — Pandas — Series, DataFrame, indexing, groupby, merge, pivot

Level 2 — Application

Goal: run one tool once and read the result correctly.

L2.1

a = pd.Series([1, 2, 3], index=["x", "y", "z"])
b = pd.Series([100, 200], index=["y", "x"])
result = a + b

Write the full result including any NaN.

Recall Solution

Pandas takes the union of the labels {x, y, z}, reindexes both sides onto it, then adds by label:

x : $1 + 100 = 101$ (both have x; note b's x value is 100, sitting in position 1, but position is irrelevant).
y : $2 + 200 = 202$ .
z : NaN (only a has z; no partner in b).

$(a + b) [k] = {a [k] + b [k] NaN k \in idx (a) \cap idx (b) otherwise$ So the result is x -> 101.0, y -> 202.0, z -> NaN (all floats, because NaN forces float). See Missing Data / NaN.

L2.2

df = pd.DataFrame({"region":["N","N","S","S","S"],
                   "rev":[10,30,5,15,10]})
result = df.groupby("region")["rev"].sum()

Give both group totals.

Recall Solution

groupby("region") first splits the rows into buckets by key: {N:[row0,row1], S:[row2,row3,row4]}. Then .sum() is applied per bucket, and results are combined with the keys as the new index:

N : $10 + 30 = 40$ .
S : $5 + 15 + 10 = 30$ .

This is the Split-Apply-Combine pattern in action.

L2.3

L = pd.DataFrame({"id":[1,2,3], "name":["a","b","c"]})
R = pd.DataFrame({"id":[2,3,4], "score":[88,90,77]})
result = pd.merge(L, R, on="id", how="inner")

Which id rows survive, and why?

Recall Solution

inner keeps the intersection of key values. L has ids {1,2,3}, R has {2,3,4}. Intersection = {2,3}. So exactly two rows survive:

id=2 → name=b, score=88
id=3 → name=c, score=90

id=1 (only in L) and id=4 (only in R) are dropped. Think of it like a SQL inner join.

Level 3 — Analysis

Goal: predict subtle output where position vs label collides.

L3.1

df = pd.DataFrame({"age":[25,32,47]}, index=[10, 20, 30])
part_loc  = df.loc[10:20]
part_iloc = df.iloc[0:2]

How many rows does each return? Are they the same rows?

Recall Solution

Here the labels 10,20,30 are numbers but not positions — that is the whole trick.

df.loc[10:20] slices by label and is inclusive of the stop → labels 10 and 20 → 2 rows (ages 25, 32).
df.iloc[0:2] slices by position and excludes the stop → positions 0,1 → 2 rows (ages 25, 32).

Here they coincidentally match (both 2 rows). But df.loc[10:30] would give 3 rows (inclusive of label 30) while df.iloc[0:3] gives 3 rows too — try df.loc[10:20] vs df.iloc[0:3] and they diverge. The safe reading: .loc counts named endpoints, .iloc counts Python-style half-open positions.

L3.2

df = pd.DataFrame({"region":["N","N","S"], "rev":[10, None, 20]})
result = df.groupby("region")["rev"].mean()

What does group N evaluate to? (There is a None in it.)

Recall Solution

The None becomes NaN. Pandas aggregation functions skip NaN by default (skipna=True).

Group N = values [10, NaN] → mean of the non-missing values = $10/1 = 10.0$ .
Group S = [20] → $20.0$ .

Key insight: the count in the denominator is the number of non-NaN entries, not the number of rows. See Missing Data / NaN.

L3.3

L = pd.DataFrame({"id":[1,2,2], "name":["a","b","c"]})
R = pd.DataFrame({"id":[2,2],   "score":[88,90]})
result = pd.merge(L, R, on="id", how="inner")

How many rows are in the result?

Recall Solution

Merge forms, for each shared key, the Cartesian product of matching rows on each side.

Key 1: not in R → contributes 0 rows.
Key 2: L has 2 rows (b,c), R has 2 rows (88, 90) → $2 \times 2 = 4$ rows.

Total = 4 rows: (b,88),(b,90),(c,88),(c,90). This "row explosion" from many-to-many keys is the classic surprise — the figure shows it visually.

Level 4 — Synthesis

Goal: chain two or more tools to answer one real question.

L4.1

df = pd.DataFrame({
    "store":["A","A","B","B","A"],
    "month":["Jan","Feb","Jan","Feb","Jan"],
    "sales":[10, 20, 30, 40, 5]})
result = df.pivot_table(index="store", columns="month",
                        values="sales", aggfunc="sum")

Fill the full 2×2 grid. Why must this be pivot_table and not pivot?

Recall Solution

Notice store A in month Jan appears twice (rows 0 and 4: sales 10 and 5). Plain pivot would raise an error because the (A, Jan) cell has two candidate values and pivot refuses to guess. pivot_table aggregates them with aggfunc="sum".

(A, Jan) = $10 + 5 = 15$
(A, Feb) = $20$
(B, Jan) = $30$
(B, Feb) = $40$

store	Feb	Jan
A	20	15
B	40	30

This is Tidy Data (long) → wide reshaping.

L4.2

Using the tables from L2.3 (L with ids 1,2,3; R with ids 2,3,4), do a left merge, then report how many NaN values appear in the score column.

Recall Solution

pd.merge(L, R, on="id", how="left") keeps all left keys {1,2,3}:

id=1 → no match in R → score=NaN
id=2 → score=88
id=3 → score=90

So exactly 1 NaN appears in score (only id=1).

L4.3

df = pd.DataFrame({
    "region":["N","N","S","S"],
    "product":["x","y","x","y"],
    "rev":[10, 20, 30, 40]})

Compute total revenue per region, then find the region with the larger total.

Recall Solution

Step 1 — group and sum (split-apply-combine): df.groupby("region")["rev"].sum() →

N = $10 + 20 = 30$
S = $30 + 40 = 70$

Step 2 — .idxmax() returns the index label of the maximum, not the value: df.groupby("region")["rev"].sum().idxmax() → "S" (since $70 > 30$ ).

Larger total: region S with 70.

Level 5 — Mastery

Goal: design a small pipeline and reason about every step.

L5.1

You have orders and prices:

orders = pd.DataFrame({
    "item":["apple","banana","apple","cherry"],
    "qty": [2, 5, 3, 1]})
prices = pd.DataFrame({
    "item":["apple","banana"],
    "price":[4, 2]})

Compute total spend per item that has a known price, then the grand total. Explain each design choice.

Recall Solution

Step 1 — join prices onto orders. Use an inner merge so items with no price (cherry) are dropped cleanly (design choice: we only want known-price spend): m = pd.merge(orders, prices, on="item", how="inner") Surviving rows: (apple,2,4), (banana,5,2), (apple,3,4). cherry gone (no price).

Step 2 — line total per row. Add a column: m["spend"] = m["qty"] * m["price"] → apple: $2 \times 4 = 8$ , banana: $5 \times 2 = 10$ , apple: $3 \times 4 = 12$ .

Step 3 — group by item and sum (split-apply-combine): per_item = m.groupby("item")["spend"].sum()

apple = $8 + 12 = 20$
banana = $10$

Step 4 — grand total: per_item.sum() = $20 + 10 = 30$ .

Design summary: merge (inner) → derive → groupby → reduce. Choosing inner was deliberate — a left join here would have kept cherry with price=NaN, and qty*NaN=NaN would silently poison the total unless handled.

L5.2

Same m from L5.1. You now want a store-style grid: items as rows, a single column "spend". When you run m.pivot_table(index="item", values="spend", aggfunc="sum"), what do you get, and why is it equivalent to the groupby in L5.1 Step 3?

Recall Solution

With only an index (no columns), pivot_table degenerates into exactly a groupby-sum:

apple → $20$
banana → $10$

Why equivalent: pivot_table(index="item", aggfunc="sum") splits rows by item, applies sum to spend, and combines with item as the index — the identical split-apply-combine machinery. A pivot without a columns argument is just a groupby wearing a table costume.

Score yourself

Recall Rubric

L1 (name the tool): 3 questions — aim 3/3.
L2 (run once): if you got the NaN in L2.1, alignment clicked.
L3 (predict): the row-explosion in L3.3 is the real filter — 4 rows, not 2.
L4 (combine): L4.1 must be pivot_table, cell (A,Jan)=15.
L5 (design): could you justify why inner in L5.1? Then you understand joins, not just their syntax.