Level 1 · Orders by channel and status, as a grid

Long tables and wide grids

A groupby on two keys gives you a long table: one row per combination.

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

Every number is there, but it’s hard to compare across channels. A wide grid puts one key down the side and the other across the top, so each cell is one combination. That is what people mean by a pivot table in a spreadsheet, and pandas has the same tool: pivot_table.

Four arguments, four questions

Each argument answers one question about the grid you want:

orders.pivot_table(
    values="order_id",     # WHAT do I measure?
    index="channel",       # what goes DOWN the side (one row each)?
    columns="status",      # what goes ACROSS the top (one column each)?
    aggfunc="count",       # WHICH operation combines the rows in a cell?
)

• values is the column you aggregate. For counting rows, an ID column with no gaps is clearest.
• index becomes the row labels, sorted A to Z; columns the column labels, also sorted.
• aggfunc is the calculation: "count", "sum", "mean", "median", "max", "nunique". aggfunc="size" counts rows without needing values at all.

Leave out columns and you get a one-dimensional summary: one row per channel, one column.

The result has channel as its index and the statuses as columns. When you check the table or export it, the index name channel travels along as the first column.

Empty cells and fill_value

When a combination never happens, the cell is empty, and pandas shows it as NaN. Products by category and active flag is a good example: every Lighting product is still active.

products.pivot_table(index="category", columns="is_active",
                     values="product_id", aggfunc="count")
# is_active  False  True
# category
# Decor        3.0   17.0
# ...
# Lighting     NaN   20.0

The NaN also turns the counts into floats (3.0, 17.0). For a count, “no rows” means 0, so pass fill_value=0: the empty cell becomes 0 and the counts stay whole numbers.

products.pivot_table(index="category", columns="is_active",
                     values="product_id", aggfunc="count", fill_value=0)
# Lighting       0    20

Only fill with 0 when zero is the truth. For an average price in a cell with no products, 0 would be a lie; leave it NaN.

pivot_table or groupby?

Both compute the same numbers. Pick by the shape you need next:

• pivot_table when the result is a report: rows × columns for a person to read, export or chart on two dimensions. On the full orders table, completed orders read 5,367 for web, 3,885 for the app and 1,597 for the marketplace, side by side in one column.
• groupby when the result is a calculation: one value per group that you’ll filter, merge or feed into the next step.

Common mistakes

• Leaving out aggfunc. The default is "mean", which averages order_id values: numbers, but nonsense.
• Swapping index and columns, which gives statuses as rows and channels across the top.
• Counting a column that has missing values. "count" skips NaN, so use an ID column or aggfunc="size".
• Filling with 0 when the aggregate is an average or a rate: an empty cell is unknown, not zero.