Pandas — Series, DataFrame, indexing, groupby, merge, pivot: Worked examples

The scenario matrix

Every operation in pandas has a small number of "edge conditions" that change the output. Below is the full grid. Each cell is a scenario class; the worked examples that follow are tagged with the cell they cover.

Case class	The tricky sub-cases	Example that covers it
Alignment (`+`)	fully matched · partially matched · disjoint (all NaN)	Ex 1
Indexing slices	`.loc` inclusive vs `.iloc` exclusive · label ≠ position	Ex 2
Boolean masking	mask selects some · mask selects none (empty frame)	Ex 3
GroupBy	normal groups · a group with one row · `NaN`/`None` key dropped	Ex 4
GroupBy multi-agg	several functions at once · count vs size on missing data	Ex 5
Merge `how`	inner ( $\cap$ ) · left · right · outer ( $\cup$ ) · duplicate keys → row explosion	Ex 6, Ex 7
Merge edge cases	overlapping non-key columns (`suffixes=`) · multi-key joins	Ex 8
Pivot	unique cells (plain pivot) · repeated cells → must aggregate	Ex 9
Word problem	end-to-end: merge → groupby → pivot on real data	Ex 10
Exam twist	chained-indexing trap + degenerate empty result	Ex 11

Ex 1 — Alignment: matched, partial, disjoint

Worked example Add three Series with three different label overlaps

import pandas as pd
import numpy as np
a = pd.Series([1, 2, 3], index=["x", "y", "z"])
b = pd.Series([100, 200],  index=["y", "x"])   # partial overlap
c = pd.Series([7, 8],      index=["p", "q"])   # ZERO overlap with a

Compute a + b and then a + c.

Forecast: which labels come out as numbers, and which come out as NaN? (covers the Alignment cell)

Step 1 — Take the union of labels for a + b. Labels of a are ${x, y, z}$ , labels of b are ${y, x}$ . Union = ${x, y, z}$ (every name from either side). Why this step? Pandas never adds "first-to-first". It first lines both Series up on the union ( $\cup$ ) of their index labels — this is the whole point of a label axis (see Tidy Data).

Step 2 — Fill each label; missing partner → NaN.

$x$ : both have it → $1 + 200 = 201$
$y$ : both have it → $2 + 100 = 102$
$z$ : only a has it → NaN

Why this step? Because Step 1 already fixed the output index to be the union, the result must have a slot for every union label — including labels that live on only one side. A one-sided label has no partner to add, and pandas refuses to invent a number, so the honest answer for that slot is NaN. In short: union output ⇒ NaN for every unmatched label (see Missing Data / NaN).

Step 3 — Do a + c (disjoint case). Union = ${x, y, z, p, q}$ . No label appears in both, so every cell is NaN. Why this step? This is the degenerate extreme of alignment — zero intersection ( $\cap$ is empty). The result has 5 rows, all NaN. This surprises people who expect an error; pandas returns a valid but empty-of-numbers Series. See Missing Data / NaN.

Verify:

a + b  ->  x: 201.0   y: 102.0   z: NaN
a + c  ->  x,y,z,p,q  all NaN     (5 labels, 0 numbers)

Sanity check: number of non-NaN cells in a+b = size of the intersection $∣ {x, y, z} \cap {y, x} ∣ = 2$ . ✓

Ex 2 — `.loc` vs `.iloc` when label ≠ position

Worked example Same slice, two answers

df = pd.DataFrame({"v": [10, 20, 30, 40]}, index=[100, 200, 300, 400])

Evaluate df.loc[100:300] and df.iloc[0:2].

Forecast: how many rows does each return? (covers the Indexing-slices cell)

Step 1 — Read .loc[100:300] as labels. .loc slices by the name tag. It walks from the row labelled 100 to the row labelled 300, including the endpoint. Why this step? Labels need not be numbers — "one past the stop" has no meaning for a string label, so pandas defines label slices to include the named endpoint. Rows returned: labels $100, 200, 300$ → 3 rows.

Step 2 — Read .iloc[0:2] as positions. .iloc ignores the tags and counts $0, 1, 2, \dots$ like a Python list, excluding the stop. Positions $0, 1$ → labels $100, 200$ → 2 rows. Why this step? This is the exact trap from the parent's mistake box: with a default index the two coincide by accident; here labels ( $100 \dots$ ) ≠ positions ( $0 \dots$ ) so the difference is visible.

Verify:

df.loc[100:300]  -> labels 100,200,300  (3 rows, values 10,20,30)
df.iloc[0:2]     -> labels 100,200       (2 rows, values 10,20)

Count check: loc = 3, iloc = 2, difference exactly 1 (the included endpoint). ✓

Ex 3 — Boolean masking: some rows vs no rows

Worked example A mask that matches, and a mask that matches nothing

df = pd.DataFrame({"age": [25, 32, 47]}, index=["u1", "u2", "u3"])

Evaluate df[df["age"] > 30] and df[df["age"] > 100].

Forecast: the second one — error, or empty table? (covers the Boolean-masking cell)

Step 1 — Build the mask for > 30. df["age"] > 30 is a boolean Series [False, True, True] aligned to ["u1","u2","u3"]. Why this step? The mask is itself a label-aligned Series; pandas keeps only rows where the mask is True. Result: u2 (32), u3 (47) → 2 rows.

Step 2 — The degenerate mask > 100. Mask is [False, False, False]. No row survives. Why this step? We must know the empty case: pandas returns a valid DataFrame with 0 rows but the same columns — it does not raise. Downstream .mean() on it would give NaN, not a crash.

Verify:

df[df["age"] > 30]   -> 2 rows (u2, u3)
df[df["age"] > 100]  -> 0 rows, columns still ["age"]

Sanity: len of each result = number of True in the mask = 2 and 0. ✓

Ex 4 — GroupBy with a lone group and a `NaN` key

The figure below shows the whole split-apply-combine journey for this example. Read it left to right: on the left are the four raw rows (colour-coded by key); the arrows carry each row into its bucket (magenta box for N, violet box for S); the dashed grey arrow shows the row whose key is None being thrown away; inside each box you see the values collected and the mean computed on them. The takeaway: a key of None never reaches any bucket, and a bucket with a single row (S) is still perfectly valid.

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

Worked example Split-apply-combine when one group has a single row and one key is missing

df = pd.DataFrame({
    "region": ["N", "N", "S", None],   # None is coerced to NaN here
    "rev":    [10,  30,  5,   99]})
df.groupby("region")["rev"].mean()

Forecast: does the None-key row (rev=99) show up anywhere in the output? (covers the GroupBy cell)

Step 1 — Split rows into buckets by key (magenta and violet boxes in the figure). Pandas builds the map {N:[0,1], S:[2]}. The row with key None is dropped — follow the dashed grey arrow — by default groupby ignores rows whose key is NaN/None. (In this object-typed key column pandas treats the Python None as the missing marker NaN, so it obeys the same drop rule.) Why this step? A missing key means "we don't know which group this row belongs to", so it cannot be placed in any bucket. This is a Split-Apply-Combine design choice (pass dropna=False to keep it as its own group). See Missing Data / NaN.

Step 2 — Apply .mean() per bucket.

N: $(10 + 30) /2 = 20.0$
S: single row → $5/1 = 5.0$ (a one-element group is perfectly legal)

Why this step? The function runs independently on each bucket's values only — the dropped None row (rev=99) was never placed in a bucket, so its 99 can never leak into N or S's mean. That is exactly why pandas dropped the missing-key row before applying: including a row of unknown group would silently corrupt every group's statistic.

Step 3 — Combine, group keys become the index. Output index = ["N","S"] (no NaN row).

Why this step? On combine, pandas labels each answer with the key that produced it, so the group keys become the new row index — this is what makes the result self-describing (you can read "N → 20.0" directly). Because the None row never formed a group, no NaN key appears in that index.

Verify:

N -> 20.0
S ->  5.0     (lone-row group works)
(row rev=99 with key None is absent)

Sanity: total rows accounted for = 2 (N) + 1 (S) = 3, and we started with 4 → exactly 1 dropped for the missing key. ✓

Ex 5 — Multi-function agg + count-vs-size on missing data

Worked example

sum, mean, count when one value is NaN

df = pd.DataFrame({
    "region": ["N", "N", "S"],
    "rev":    [10, np.nan, 40]})
df.groupby("region")["rev"].agg(["sum", "mean", "count", "size"])

Forecast: for group N (values 10 and NaN), what are mean and count? (covers the GroupBy-multi-agg cell)

Step 1 — Compute per group, skipping NaN in the math. Group N has values [10, NaN].

sum = 10 (NaN skipped) — Why? pandas' numeric aggregators skip NaN by default.
mean = $10/1 = 10.0$ — divides by the count of non-NaN values, not by 2.

Step 2 — Distinguish count from size.

count = number of non-NaN values = 1 for group N.
size = number of rows in the group = 2 for group N (counts the NaN row too). Why this step? This is the single most confused pair in interviews: count measures data present, size measures rows present. On clean data they agree; on missing data they diverge.

Step 3 — Group S (single clean value 40). sum=40, mean=40, count=1, size=1. Why this step? A group that is both all-clean and single-row must still obey the exact same rules — there is no special case. With no NaN present, count and size coincide (both 1), which is precisely why on tidy data people never notice the two are different. Seeing them agree here confirms the divergence in Step 2 was caused solely by the missing value.

Verify:

        sum  mean  count  size
N        10  10.0     1     2
S        40  40.0     1     1

Sanity: size - count = number of NaN per group = 1 for N, 0 for S. ✓

Ex 6 — Merge: all four `how` modes on the same tables

The figure below is a Venn diagram of the two tables' id values. What to read from it: the left (magenta) circle holds L's ids, the right (violet) circle holds R's ids; the overlap in the middle is the intersection $\cap = {2, 3}$ ; the whole coloured area is the union $\cup = {1, 2, 3, 4}$ . Each how mode is just a rule that says which region to keep — inner keeps the overlap, left keeps the whole magenta circle, right keeps the whole violet circle, outer keeps everything. The orange line at the bottom lists all four resulting id-sets.

Worked example One pair of tables, four join results

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

Give the surviving ids for inner, left, right, outer.

Forecast: write the id set for each before reading on. (covers the Merge-how cell)

Step 1 — Find the key sets (see the Venn figure). $L ids = {1, 2, 3}$ , $R ids = {2, 3, 4}$ . Intersection $\cap = {2, 3}$ (the overlap), union $\cup = {1, 2, 3, 4}$ (the whole area). Why this step? how is literally a rule on these two sets — think SQL Joins.

Step 2 — Apply each rule.

inner → intersection ${2, 3}$ → 2 rows.
left → all of L ${1, 2, 3}$ ; id 1 has no R match → score = NaN. 3 rows.
right → all of R ${2, 3, 4}$ ; id 4 has no L match → name = NaN. 3 rows.
outer → union ${1, 2, 3, 4}$ ; id 1 lacks score, id 4 lacks name → both NaN. 4 rows.

Why this step? Each how mode is not magic — it is pandas choosing which key set to keep and then, for kept keys with no partner on the other side, filling the missing side's columns with NaN (the same union⇒NaN principle from Ex 1, now applied to whole rows). inner keeps $\cap$ ; left/right keep one full circle; outer keeps $\cup$ . The NaNs appear exactly where a kept key had no partner, which is why left/right/outer create them and inner never does. See SQL Joins and Missing Data / NaN.

Verify:

inner: ids {2,3}       (2 rows)
left:  ids {1,2,3}     (3 rows, id1 score NaN)
right: ids {2,3,4}     (3 rows, id4 name NaN)
outer: ids {1,2,3,4}   (4 rows)

Sanity: rows(outer) = rows(left) + rows(right) − rows(inner) = 3 + 3 − 2 = 4. ✓ (inclusion–exclusion on the key sets)

Ex 7 — Merge with duplicate keys: the row explosion

Worked example When a key repeats on both sides

L = pd.DataFrame({"id":[1,1],   "name":["a1","a2"]})
R = pd.DataFrame({"id":[1,1,1], "score":[10,20,30]})
pd.merge(L, R, on="id", how="inner")

Forecast: 2 rows? 3 rows? more? (covers the Merge duplicate-keys cell)

Step 1 — Count matches per key value. Key 1 appears 2 times in L and 3 times in R. Why this step? Merge doesn't pair rows one-to-one; for each shared key value it forms the Cartesian product of matching rows.

Step 2 — Multiply. For key 1: $2 \times 3 = 6$ output rows (every L-row with id=1 paired against every R-row with id=1). Why this step? This "row explosion" is the classic cause of a merge that unexpectedly balloons in size — always check key uniqueness before joining.

Verify:

result has 2 * 3 = 6 rows

Sanity: pairs (a1,10)(a1,20)(a1,30)(a2,10)(a2,20)(a2,30) = 6. ✓

Ex 8 — Merge edge cases: colliding column names and multi-key joins

Worked example Two overlapping columns, and a join that needs two keys

# (A) both tables carry a non-key column called "val"
L = pd.DataFrame({"id":[1,2], "val":[10, 20]})
R = pd.DataFrame({"id":[1,2], "val":[99, 88]})
pd.merge(L, R, on="id", suffixes=("_L", "_R"))
 
# (B) the true identity of a row is (store, month), not one column
sales = pd.DataFrame({"store":["A","A","B"], "month":["Jan","Feb","Jan"],
                      "qty":[3, 5, 7]})
targets = pd.DataFrame({"store":["A","A","B"], "month":["Jan","Feb","Jan"],
                        "goal":[2, 6, 4]})
pd.merge(sales, targets, on=["store", "month"])

Forecast: (A) what are the column names of the merged frame? (B) how many rows survive, and could a single-key join on store alone go wrong? (covers the Merge-edge-cases cell)

Step 1 — (A) Detect the name collision. Both L and R have a non-key column named val. After merging on id, pandas would end up with two columns both called val — an ambiguous table. Why this step? A DataFrame can physically hold duplicate column labels, but then df["val"] no longer picks one column — it is ambiguous. Pandas needs a rule to keep them distinguishable.

Step 2 — (A) Resolve with suffixes=. suffixes=("_L", "_R") renames the clashing columns to val_L and val_R. The key column id is not suffixed (it is shared, not duplicated). Why this step? The suffix tags each surviving copy with which table it came from, restoring unambiguous access: df["val_L"] is L's value, df["val_R"] is R's. (If you omit suffixes, pandas silently uses the defaults ("_x", "_y") — knowing this saves you from mysterious val_x/val_y columns.)

Step 3 — (B) Join on a pair of keys. Here a row's identity is the combination (store, month), so we pass on=["store", "month"]. Pandas matches a left row to a right row only when both fields agree. Why this step? If we joined on store alone, store A (which has two rows: Jan and Feb) would match every A row on the other side — a Cartesian explosion (the Ex 7 trap) and wrong pairings (A-Jan's qty stuck next to A-Feb's goal). The multi-key join treats the tuple $(store, month)$ as one composite key, so each real observation lines up with its true partner. Think of it as a two-column hash key.

Verify:

(A) columns -> ["id", "val_L", "val_R"];  id 1 -> val_L=10, val_R=99
(B) 3 rows: (A,Jan,qty=3,goal=2) (A,Feb,5,6) (B,Jan,7,4)

Sanity: (A) exactly one clashing name → exactly two suffixed columns, key un-suffixed → 3 columns total. (B) each (store,month) pair is unique on both sides → no explosion → rows = 3 = min matches. ✓

Ex 9 — Pivot: unique cells vs repeated cells

Worked example Plain pivot works; then a duplicate forces

pivot_table

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

Then a second frame where store A has two Jan rows.

Forecast: what does the first pivot's cell (A, Jan) hold, and what happens when a (store,month) pair repeats? (covers the Pivot cell)

Step 1 — Map each row to a cell. Rows → grid coordinates: (A,Jan)=10, (A,Feb)=20, (B,Jan)=30, (B,Feb)=40. Each coordinate is hit exactly once, so pivot drops the value straight in. Why this step? pivot is pure reshaping — it needs each (index, column) pair to be unique, or it can't decide what single value to put in the cell.

Step 2 — Break it with a duplicate.

df2 = pd.DataFrame({
  "store":["A","A","B"],
  "month":["Jan","Jan","Feb"],   # A/Jan appears TWICE
  "sales":[10, 5, 40]})
df2.pivot(index="store", columns="month", values="sales")
# -> ValueError: Index contains duplicate entries, cannot reshape

Why this step? Now the coordinate (A, Jan) is asked to hold two values 10 and 5. pivot performs no aggregation, so it cannot decide between them — it raises ValueError rather than guess. This is the exact failure the parent note warned about.

Step 3 — Fix it with pivot_table.

df2.pivot_table(index="store", columns="month",
                values="sales", aggfunc="sum")
#        Feb   Jan
#  A     NaN    15    <- (10 + 5) summed
#  B      40   NaN

Why this step? pivot_table is "groupby then reshape": it groups the rows landing in each (index, column) cell and applies aggfunc (here sum) to collapse them to one value — so (A,Jan) becomes 10+5=15. Cells with no rows at all (like (A,Feb) and (B,Jan)) are filled with NaN, the same missing-marker principle as everywhere else on this page. Choosing aggfunc is how you tell pandas the way to combine repeated cells.

Verify:

plain pivot   (A,Jan)=10, (A,Feb)=20, (B,Jan)=30, (B,Feb)=40
df2.pivot     -> ValueError (duplicate (A,Jan))
pivot_table   (A,Jan)=15  (10+5),  (B,Feb)=40

Sanity: sum of all cells is preserved — original df2 sales sum = 10+5+40 = 55, pivoted non-NaN cells 15+40 = 55. ✓

Ex 10 — Word problem: full pipeline merge → groupby → pivot

Worked example Revenue report from two raw tables

A shop keeps orders and a product price list. Build "total revenue per (category × month)".

orders = pd.DataFrame({
  "prod": ["pen","pen","mug","mug"],
  "month":["Jan","Feb","Jan","Feb"],
  "qty":  [3,     5,    2,    4]})
prices = pd.DataFrame({
  "prod": ["pen","mug"],
  "cat":  ["stationery","kitchen"],
  "price":[2, 6]})

Forecast: revenue of kitchen in Feb? (covers the Word-problem cell)

Step 1 — Merge orders with prices on prod.

m = pd.merge(orders, prices, on="prod", how="left")

Why this step? qty lives in orders, price/cat live in prices; a left join attaches price info to every order (keeps all orders even if a price were missing). Each prod key is unique in prices, so no row explosion happens (contrast Ex 7).

Step 2 — Compute revenue per row.

m["rev"] = m["qty"] * m["price"]

Rows: pen-Jan $3 \times 2 = 6$ , pen-Feb $5 \times 2 = 10$ , mug-Jan $2 \times 6 = 12$ , mug-Feb $4 \times 6 = 24$ . Why this step? Vectorised column multiply (a NumPy operation under the hood) — revenue is per-order, computed before we aggregate.

Step 3 — Aggregate to (category, month) with groupby.

g = m.groupby(["cat", "month"])["rev"].sum()
# (kitchen, Feb)    -> 24
# (kitchen, Jan)    -> 12
# (stationery, Feb) -> 10
# (stationery, Jan) ->  6

Why this step? This is the split-apply-combine heart of the report: split rows by the pair (cat, month), sum rev in each bucket, combine. Since each product maps to exactly one category, pen rows land in stationery and mug rows land in kitchen.

Step 4 — Pivot the grouped result into a category × month grid.

m.pivot_table(index="cat", columns="month",
              values="rev", aggfunc="sum")

Why this step? pivot_table does Step 3 and reshapes in one call — it groups by (index, column) = (cat, month), sums rev, then lays the sums out as a grid. Each (cat, month) cell here is a single summed value.

Verify:

             Feb  Jan
kitchen       24   12      <- kitchen/Feb = 24
stationery    10    6

Units check: qty (items) × price (₹/item) = ₹, correct dimension for "revenue". Kitchen-Feb = mug-Feb = 4×6 = 24, and the groupby total for that bucket matches the pivot cell. ✓

Ex 11 — Exam twist: chained-indexing trap + empty result

Worked example The write that silently does nothing, then an empty aggregate

df = pd.DataFrame({"a":[1, -1, 2], "b":[0, 0, 0]})
# (i) the trap
df[df["a"] > 0]["b"] = 5        # WRONG
# (ii) the fix
df.loc[df["a"] > 0, "b"] = 5    # RIGHT
# (iii) degenerate aggregate
df.loc[df["a"] > 100, "b"].mean()

Forecast: after line (i), is any b equal to 5? And what does line (iii) return? (covers the Exam-twist cell)

Step 1 — Diagnose the chained write (i). df[df["a"]>0] returns a copy; ["b"] = 5 writes into that throw-away copy, then it's discarded. df is unchanged (pandas raises SettingWithCopyWarning). Why this step? Two bracket operations in a row = "select, then assign to the selection's copy". The original never sees the write.

Step 2 — The single-.loc fix (ii). df.loc[mask, "b"] = 5 addresses rows and the column in one call, so pandas writes into the original. Rows with a>0 are positions 0 and 2 → their b becomes 5.

Step 3 — The degenerate aggregate (iii). Mask a > 100 selects no rows; .mean() of an empty Series is NaN (not an error, not 0). Why this step? You must know that "mean of nothing" is NaN — dividing a sum of 0 by a count of 0 is undefined, and pandas encodes that as NaN. See Missing Data / NaN.

Verify:

after (i):  df["b"] == [0,0,0]   (write lost)
after (ii): df["b"] == [5,0,5]   (rows where a>0)
line (iii): NaN                  (empty mean)

Sanity: only rows with a>0 (positions 0,2) changed; row 1 with a=-1 keeps b=0. ✓

Active recall

Recall Cover and answer

Q: a + c with disjoint indices — error or all-NaN? → all-NaN, valid Series.
Q: .loc[100:300] vs .iloc[0:2] on index [100,200,300,400]? → 3 rows vs 2 rows.
Q: A groupby key that is None — where does it go? → coerced to NaN and dropped by default (dropna=False to keep).
Q: count vs size on a group [10, NaN]? → count=1 (non-NaN), size=2 (rows).
Q: outer row count from left/right/inner? → left + right − inner (inclusion–exclusion).
Q: Merge key repeating 2× (L) and 3× (R)? → 2×3 = 6 rows (Cartesian per key).
Q: Two tables share a non-key column val — how do you keep both? → suffixes=("_L","_R") (defaults are _x,_y).
Q: Row identity is (store,month) — how do you merge? → on=["store","month"] (multi-key join).
Q: Duplicate (index,column) in pivot? → pivot raises ValueError; use pivot_table(aggfunc=...).
Q: .mean() of an empty selection? → NaN.
Q: What do $\cap$ and $\cup$ mean? → intersection (names in both) and union (names in either).