pivot_table and crosstab

Group by two keys and you get one row per combination, with both keys stacked in the index:

orders.groupby(["city", "item"])["cups"].sum()

Correct, complete, and awkward to read — you can't compare cities at a glance because they're spread down the page. pivot_table runs the same aggregation and lays the second key across the top instead.

orders.pivot_table(
    index="city",       # down the side
    columns="item",     # across the top
    values="cups",      # what gets aggregated
    aggfunc="sum",      # how
)

Same numbers, in a grid you can actually read. If you know groupby, you already know pivot_table — only the layout is new.

Four things worth knowing

aggfunc defaults to "mean". Not sum. This is the single most common surprise, and it produces plausible-looking numbers that are quietly wrong. Say what you mean, every time.

Empty cells become NaN. A combination that never occurred — Pune never sold elaichi — has nothing to aggregate. fill_value=0 is usually right, and it also stops the column being promoted to float.

margins=True adds totals as an All row and column.

Several keys work. index=["city", "item"] or values=["cups", "revenue"] both give you a MultiIndex — the subject of the next module.

crosstab

When you just want to count combinations, pd.crosstab is the shorthand:

pd.crosstab(orders["city"], orders["item"])                    # counts
pd.crosstab(orders["city"], orders["item"], normalize="index") # row proportions

It takes Series rather than a frame and a column name, which reads oddly at first but means you can cross-tabulate things that aren't in the same table.

pivot versus pivot_table

There's also a plain .pivot(), and the difference catches people out:

df.pivot(index=, columns=, values=)   # reshape ONLY — raises on duplicates
df.pivot_table(...)                   # aggregates — duplicates are fine

pivot assumes each combination appears exactly once and raises ValueError: Index contains duplicate entries if not. Since our table has two Delhi orders on the same day, it would fail here. Use pivot_table unless you're certain — it does the same job and handles the duplicate case by aggregating.

Getting back

A pivot table is wide. .stack() folds the columns back down into the index, and .melt() returns to fully long form — both in the next module. A pivot table is for reading; long form is for computing.