Reshaping: Pivot Tables, Melt and Crosstab

What you'll build

A country-by-product revenue matrix (like a spreadsheet pivot table) with totals, the same data back in "long" format for plotting or databases, and a count of orders per country and product.

The idea

• Long format: one row per observation (country, product, value). Wide format: one row per entity, one column per category.
• pivot_table(index, columns, values, aggfunc, fill_value, margins) aggregates into wide format.
• melt(id_vars, var_name, value_name) converts wide back to long.
• pd.crosstab(a, b) counts combinations.

The code

from io import StringIO
import pandas as pd

csv = StringIO("""order_id,date,customer,country,product,quantity,unit_price
1001,2026-01-03,ana,DE,mug,2,8.50
1002,2026-01-03,ben,US,teapot,1,24.00
1003,2026-01-04,chen,DE,kettle,1,31.90
1004,2026-01-05,ana,DE,teapot,1,24.00
1005,2026-01-07,dara,FR,mug,4,8.50
1006,2026-01-07,ben,US,mug,1,8.50
""")
orders = pd.read_csv(csv, parse_dates=["date"])

orders["total"] = orders["quantity"] * orders["unit_price"]

matrix = orders.pivot_table(index="country", columns="product", values="total",
                            aggfunc="sum", fill_value=0, margins=True, margins_name="all")
print(matrix)

long = matrix.drop(index="all", columns="all").reset_index().melt(
    id_vars="country", var_name="product", value_name="revenue")
print(long[long["revenue"] > 0].sort_values(["country", "product"]).to_string(index=False))

print(pd.crosstab(orders["country"], orders["product"]))

product  kettle   mug  teapot    all
country                             
DE         31.9  17.0    24.0   72.9
FR          0.0  34.0     0.0   34.0
US          0.0   8.5    24.0   32.5
all        31.9  59.5    48.0  139.4
country product  revenue
     DE  kettle     31.9
     DE     mug     17.0
     DE  teapot     24.0
     FR     mug     34.0
     US     mug      8.5
     US  teapot     24.0
product  kettle  mug  teapot
country                     
DE            1    1       1
FR            0    1       0
US            0    1       1

Common mistakes

• pivot (no aggregation) on data with duplicate index/column pairs, which raises an error; use pivot_table.
• Forgetting fill_value=0, leaving NaN for missing combinations.
• Storing wide tables in databases; long format is easier to query and append to.

Key points

• pivot_table aggregates long data into wide summaries.
• melt converts wide data back into long format.
• crosstab counts combinations of two columns.

Further reading: Reshaping and pivot tables