lesson 213 pandas Transformations · advanced

Pivot tables

A pivot table puts one key down the side, another across the top, and an aggregate in each cell: revenue by state and channel, orders by status and channel.

You'll be able to

Build a two-way summary with pivot_table(index=, columns=, values=, aggfunc=, fill_value=)
Count combinations with pd.crosstab, and turn counts into row shares with normalize="index"
Explain why pivot refuses duplicate entries and pivot_table aggregates them

The idea

groupby(["ship_state", "channel"]) gives one row per pair, which is hard to compare by eye. A pivot table lays the same numbers out as a grid: states down the side (index), channels across the top (columns), and in each cell the aggfunc of the values column for that pair. Pairs with no orders would be missing; fill_value=0 writes 0 instead.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · Revenue by state and channel

import pandas as pd

orders = pd.DataFrame({
    "ship_state": ["TX", "CA", "NY", "TX", "NY", "TX", "FL", "TX"],
    "channel": ["web", "web", "web", "web", "phone", "marketplace", "app", "app"],
    "total": [35.84, 91.47, 55.72, 246.6, 54.55, 17.75, 64.17, 39.15],
})
table = orders.pivot_table(index="ship_state", columns="channel", values="total", aggfunc="sum", fill_value=0)
print(table)
print(table.loc["TX", "web"], table["web"].sum())

example-2.py · pivot with two values for one cell

import pandas as pd

orders = pd.DataFrame({"ship_state": ["TX", "TX"], "channel": ["web", "web"], "total": [35.84, 246.6]})
print(orders.pivot(index="ship_state", columns="channel", values="total"))

example-3.py · Order status by channel

import pandas as pd

orders = pd.DataFrame({
    "channel": ["web", "web", "web", "web", "phone", "app", "app", "app"],
    "status": ["delivered", "returned", "returned", "delivered", "delivered", "delivered", "delivered", "cancelled"],
})
print(pd.crosstab(orders["channel"], orders["status"]))
shares = pd.crosstab(orders["channel"], orders["status"], normalize="index")
print(shares.loc["web"].to_dict())

When to use it: comparing a measure across two categories at once, or building the table behind a heatmap or a grouped bar chart. Mistakes to avoid: pivot on data with repeated pairs (use pivot_table); forgetting fill_value, then treating NaN as zero later; and aggfunc="mean" when the question asks for totals (the default aggfunc is the mean).

Review

pivot_table(index, columns, values, aggfunc, fill_value) builds a two-way summary; the default aggfunc is the mean.
crosstab counts combinations; normalize="index" turns rows into shares.
div(row_totals, axis=0) divides each row by its own total.
Next: timestamps: parse them, take them apart, and group by month.

Practise this

Departed flights per airline and airport