Level 5 · Coupon orders by status

A two-level index

Group by two columns and count, and pandas returns a Series whose index has two levels:

counts = orders.groupby(["channel", "status"]).size()
# channel      status
# app          cancelled     265
#              completed    3885
#              pending        50
#              refunded      120
# marketplace  cancelled     108
# ...

That index is a MultiIndex: each entry is a pair like ("app", "completed"). It’s a long table in disguise, one row per pair.

unstack: from rows to columns

.unstack() takes the innermost index level and turns its values into columns:

counts.unstack()
# status       cancelled  completed  pending  refunded
# channel
# app                265       3885       50       120
# marketplace        108       1597       19        43
# web                353       5367       52       141

This is the same grid pivot_table builds; groupby plus unstack is just another route there, handy when you already have the grouped Series. To move a different level, name it: counts.unstack("channel") puts channels across the top and statuses down the side.

Missing combinations

A two-key groupby only lists pairs that actually occur. When you unstack, any pair that never happened leaves a hole, filled with NaN:

orders.groupby(["coupon_code", "status"]).size().unstack()
# status       cancelled  completed  pending  refunded
# HOLIDAY20         12.0      174.0      NaN       7.0

None of the HOLIDAY20, SPRING15 or SUMMER15 orders is pending, because almost every pending order was placed in the last days of 2025, after those campaigns ended. For a count, an empty cell is truly zero, so pass fill_value=0. It fills the holes and keeps the counts as integers.

Notice that orders without a coupon (10,277 of them) don’t appear at all: groupby drops NaN keys by default. Pass dropna=False to groupby if you want them as their own row.

stack: back to one row per pair

.stack() is the reverse: it moves the columns back into the innermost index level.

grid = counts.unstack()
grid.stack()          # the long Series again, one entry per channel and status

stack drops NaN cells by default, so after an unstack and stack you get the original pairs back.

Managing the index

Grids and grouped results keep their keys in the index. Three methods move them around:

long = grid.stack().reset_index(name="n_orders")   # index levels → ordinary columns
by_status = long.set_index("status")               # a column → the index
by_status.sort_index()                             # sort rows by their index labels

reset_index() is what you call before merging, melting or exporting, so the keys are real columns. set_index() goes the other way when you want to look rows up with .loc["completed"]. sort_index() orders rows by label, the way groupby and pivot_table already do.

Common mistakes

• Unstacking without fill_value=0 on counts, leaving NaN and float columns like 174.0.
• Unstacking the wrong level. With no argument it’s the last key you grouped by; name the level to be sure.
• Filling with 0 when the cell is an average. An empty average is unknown, not zero.
• Forgetting that groupby drops missing keys, then wondering where the no-coupon orders went.