Level 4 · Orders by channel and status

Grouping on more than one key

Pass a list of columns and pandas makes a group for every combination that appears in the data:

orders.groupby(["channel", "status"]).size()

channel      status
app          cancelled     ...
             completed     ...
             pending       ...
             refunded      ...
marketplace  cancelled     ...
...
dtype: int64

The result has a MultiIndex: two levels of row labels, channel then status. It’s sorted by both. Only combinations that actually occur appear; if no marketplace order were pending, there would be no row for it.

From MultiIndex to a flat table

A two-level index is awkward to read and merge. reset_index moves both levels into columns. On a Series, name= sets the name of the values column:

orders.groupby(["channel", "status"]).size().reset_index(name="n_orders")

The named-aggregation route gives the same table:

orders.groupby(["channel", "status"], as_index=False).agg(n_orders=("order_id", "count"))

Comparing groups fairly

Raw counts mostly reflect channel size: web has the most orders, so it has the most of everything. To compare cancellation rates, divide by each channel’s total. normalize=True in value_counts does exactly this per group:

orders.groupby("channel")["status"].value_counts(normalize=True)

Each channel’s shares now add up to 1, and you can see whether one channel cancels more as a proportion, which is the question support is really asking.

Reshaping into a grid with unstack

People read a grid more easily than a long list. unstack moves the inner index level (status) into columns:

orders.groupby(["channel", "status"]).size().unstack(fill_value=0)

channel	cancelled	completed	pending	refunded
app	…	…	…	…
marketplace	…	…	…	…
web	…	…	…	…

fill_value=0 writes 0 for combinations that don’t occur, instead of NaN. pd.crosstab(orders["channel"], orders["status"]) builds the same grid directly, and pivot_table does it for any aggregation:

orders.pivot_table(index="channel", columns="status", values="shipping_fee", aggfunc="mean")

Use the long format (one row per combination) for further computation and merging, and the grid for showing people.

Common mistakes

• Using .count() on the whole grouped DataFrame. It returns one count column per original column, each skipping that column’s missing values, so coupon_code shows a much smaller number than order_id.
• Forgetting name= in reset_index, which gives a values column called 0.
• Drawing conclusions from raw counts across groups of very different sizes.

If you know SQL

This is GROUP BY channel, status with COUNT(*). unstack is a pivot, which SQL does with CASE WHEN inside SUM or a PIVOT clause.