← All lessons

Pivot, melt, and crosstab

Wide vs long tables: pivot_table, melt, stack, unstack, explode, crosstab.

Tables come long (one fact per row, many rows) or wide (one column per category). pivot_table goes long → wide. melt goes wide → long. crosstab counts combinations.

Goal

Build a city × product grid, melt it back, and tabulate frequencies.

pivot_table

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Mombasa", "Kisumu"],
        "product": ["A", "B", "A", "B", "A"],
        "units": [12, 7, 9, 6, 3],
        "revenue": [126.0, 154.0, 94.5, 132.0, 31.5],
    }
)
wide = df.pivot_table(
    index="city",
    columns="product",
    values="revenue",
    aggfunc="sum",
    fill_value=0,
)
print(wide)
print()
print("columns:", list(wide.columns))
print("index:", list(wide.index))

If a city–product pair can appear more than once, aggfunc is required. pivot (no _table) errors on duplicates.

Margins

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Mombasa", "Kisumu"],
        "product": ["A", "B", "A", "B", "A"],
        "revenue": [126.0, 154.0, 94.5, 132.0, 31.5],
    }
)
print(
    df.pivot_table(
        index="city",
        columns="product",
        values="revenue",
        aggfunc="sum",
        fill_value=0,
        margins=True,
        margins_name="total",
    )
)

melt back to long

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Mombasa"],
        "product": ["A", "B", "A", "B"],
        "revenue": [126.0, 154.0, 94.5, 132.0],
    }
)
wide = (
    df.pivot_table(
        index="city", columns="product", values="revenue", aggfunc="sum", fill_value=0
    )
    .reset_index()
)
print(wide)
print()
print(wide.melt(id_vars="city", var_name="product", value_name="revenue"))

crosstab

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Mombasa", "Kisumu"],
        "product": ["A", "B", "A", "B", "A"],
        "units": [12, 7, 9, 6, 3],
    }
)
print(pd.crosstab(df["city"], df["product"]))
print()
print(pd.crosstab(df["city"], df["product"], values=df["units"], aggfunc="sum").fillna(0))
print()
print(pd.crosstab(df["city"], df["product"], normalize="index").round(2))

normalize="index" is row percentages.

stack / unstack

df = pd.DataFrame(
    {
        "city": ["Nairobi", "Nairobi", "Mombasa", "Mombasa"],
        "product": ["A", "B", "A", "B"],
        "revenue": [126.0, 154.0, 94.5, 132.0],
    }
)
g = df.groupby(["city", "product"])["revenue"].sum()
print(g)
print()
print(g.unstack(fill_value=0))
print()
print(g.unstack(fill_value=0).stack())

explode lists

tags = pd.DataFrame({"order": [1, 2], "sku": [["A", "B"], ["A"]]})
print(tags.explode("sku").reset_index(drop=True))
Pitfall

After pivot_table, column labels may be a MultiIndex. reset_index() plus melt, or wide.columns.name = None, keeps later to_csv headings readable.