Reshaping: pivot_table, melt & crosstab

The same data can be laid out in different shapes. A weather table with one row per city per day is easy to filter and group. A table with one row per month and one column per city is easier to read and to chart. Moving between these shapes is called reshaping.

In this lesson you will build summary tables with pivot_table(), turn wide tables into long ones with melt(), count combinations with pd.crosstab(), and see how stack() and unstack() connect the two.

Long and wide data

weather.csv is long: each row is one city on one day. Most raw data arrives long, and most reports want wide.

pivot_table: summary tables like a spreadsheet

pivot_table() works like a pivot table in Excel. You choose what goes down the side (index), what goes across the top (columns), which numbers fill the cells (values) and how to combine them (aggfunc).

import pandas as pd

weather = pd.read_csv("weather.csv", parse_dates=["date"])
weather = weather[weather["temp_max"] < 60]                 # drop the two sensor errors
weather["month"] = weather["date"].dt.month

temps = weather.pivot_table(index="month", columns="city", values="temp_max", aggfunc="mean").round(1)
print(temps)

rain = weather.pivot_table(index="month", columns="city", values="rainfall_mm",
                           aggfunc="sum", margins=True, margins_name="Total").round(0)
print(rain.loc[[6, 7, 8, 10, 11, "Total"]])

city   Chennai  Delhi  Jaipur  Kochi  Mumbai
month                                       
1         29.0   20.0    23.1   31.1    31.6
2         31.2   24.5    26.4   31.5    32.7
3         33.0   29.7    30.3   32.5    33.0
4         35.5   35.8    36.5   32.9    32.8
5         36.6   39.7    40.5   30.6    33.6
6         35.6   38.3    39.3   27.9    30.8
7         35.0   34.7    36.6   28.5    28.1
8         34.3   34.2    32.6   27.8    28.7
9         33.5   32.1    34.4   28.8    29.3
10        30.3   31.9    32.7   29.0    31.3
11        28.2   27.5    29.7   30.8    33.5
12        29.2   22.3    24.3   30.5    32.5
city   Chennai   Delhi  Jaipur   Kochi  Mumbai   Total
month                                                 
6         44.0   108.0    80.0   765.0   319.0  1315.0
7        175.0   342.0   156.0   650.0   497.0  1820.0
8        120.0   312.0   130.0   458.0   480.0  1501.0
10       259.0     0.0     4.0   278.0    84.0   626.0
11       340.0    19.0     2.0   104.0    14.0   480.0
Total   1320.0  1046.0   436.0  2946.0  1766.0  7514.0

The first table compares five cities month by month at a glance: Delhi and Jaipur swing from cool winters to about 40 °C in May, while Mumbai and Kochi stay within a few degrees all year. margins=True adds row and column totals, named here Total. The rain table shows the two monsoons: the south-west monsoon soaks Mumbai and Kochi in June to August, while Chennai gets most of its rain in October and November from the north-east monsoon.

pivot: reshaping without aggregating

pivot() only rearranges; it does not aggregate. Use it when each index-column pair appears exactly once, and use pivot_table() when there may be several values to combine.

import pandas as pd

weather = pd.read_csv("weather.csv")
week = weather[weather["date"] <= "2025-01-04"]

wide = week.pivot(index="date", columns="city", values="temp_min")
print(wide)

try:
    weather.assign(month=weather["date"].str[:7]).pivot(index="month", columns="city", values="temp_min")
except ValueError as err:
    print("pivot failed:", err)

city        Chennai  Delhi  Jaipur  Kochi  Mumbai
date                                             
2025-01-01     21.3    8.9     7.0   24.1    18.2
2025-01-02     21.3    8.5     6.8   23.8    17.5
2025-01-03     21.6    8.7     8.4   22.4    17.8
2025-01-04     21.7    8.7     7.8   23.5    18.4
pivot failed: Index contains duplicate entries, cannot reshape

Each date and city pair occurs once, so pivot() works. Grouping by month gives about 30 rows per month and city, so pivot() cannot decide which value to put in the cell and raises an error. That is your signal to use pivot_table() with an aggfunc.

melt: from wide back to long

melt() does the opposite of pivot. It takes several columns and stacks them into two: one holding the old column names and one holding the values. Marks sheets are a classic case: one column per subject is easy to read, but to compare subjects with groupby or a chart you need one row per student per subject.

import pandas as pd

students = pd.read_csv("students.csv", na_values=["AB"]).drop_duplicates()
subjects = ["maths", "science", "english", "hindi", "social_science"]

long = students.melt(id_vars=["student_id", "school_type"], value_vars=subjects,
                     var_name="subject", value_name="marks")
print(long.head(6))
print(long.shape)

print(long.groupby("subject")["marks"].agg(["mean", "std"]).round(1).sort_values("mean"))

  student_id school_type subject  marks
0      S1001     Private   maths   50.0
1      S1002  Government   maths   52.0
2      S1003  Government   maths   64.0
3      S1004  Government   maths   63.0
4      S1005     Private   maths   55.0
5      S1006     Private   maths   33.0
(2400, 4)
                mean   std
subject                   
maths           58.1  17.9
science         59.1  16.3
social_science  59.8  15.3
english         62.1  14.2
hindi           66.1  13.4

id_vars are the columns to keep as identifiers on every row, value_vars are the columns to stack, and var_name and value_name name the two new columns. 480 students times 5 subjects gives 2,400 rows. In long form a single groupby compares all subjects: maths has the lowest average and the widest spread, Hindi the highest average.

crosstab: counting combinations

pd.crosstab() counts how often each combination of two categories occurs, a contingency table. With normalize, it shows proportions instead.

import pandas as pd

sales = pd.read_csv("sales.csv").drop_duplicates()
pay = sales["payment_mode"].str.strip().str.lower().map({
    "upi": "UPI", "card": "Card", "cod": "COD", "cash on delivery": "COD",
    "net banking": "Net Banking", "netbanking": "Net Banking", "wallet": "Wallet"})

table = pd.crosstab(sales["category"], pay)
print(table)

share = pd.crosstab(sales["category"], pay, normalize="index").round(2)
print(share)

payment_mode    COD  Card  Net Banking  UPI  Wallet
category                                           
Books            13    14           11   64       8
Electronics      46    56           19  156      23
Fashion          40    55           20  131      25
Groceries        37    33            9   88      17
Home & Kitchen   28    33           16  119      17
Sports           22    17           14   57      10
payment_mode     COD  Card  Net Banking   UPI  Wallet
category                                             
Books           0.12  0.13         0.10  0.58    0.07
Electronics     0.15  0.19         0.06  0.52    0.08
Fashion         0.15  0.20         0.07  0.48    0.09
Groceries       0.20  0.18         0.05  0.48    0.09
Home & Kitchen  0.13  0.15         0.08  0.56    0.08
Sports          0.18  0.14         0.12  0.48    0.08

normalize="index" makes each row add up to 1, so you can compare categories of different sizes fairly. UPI is the top choice in every category. Card payments are most common in Electronics and Fashion (about one order in five), and cash on delivery is most common for Groceries. Crosstabs like this are also the input to the chi-square test in Hypothesis Testing Basics.

stack and unstack

unstack() moves the innermost level of a row index into columns (long to wide), and stack() moves columns into the row index (wide to long). They are most useful after a groupby with several keys.

import pandas as pd

sales = pd.read_csv("sales.csv", parse_dates=["order_date"]).drop_duplicates()
sales["quarter"] = "Q" + sales["order_date"].dt.quarter.astype(str)

counts = sales.groupby(["category", "quarter"]).size()
print(counts.head(5))

wide = counts.unstack(fill_value=0)
print(wide)
print(wide.stack().head(3))

category     quarter
Books        Q1         38
             Q2         19
             Q3         32
             Q4         23
Electronics  Q1         55
dtype: int64
quarter         Q1  Q2  Q3   Q4
category                       
Books           38  19  32   23
Electronics     55  56  77  113
Fashion         58  42  73   99
Groceries       36  28  56   67
Home & Kitchen  46  45  52   72
Sports          31  39  19   33
category  quarter
Books     Q1         38
          Q2         19
          Q3         32
dtype: int64

The groupby result is a Series with a two-level index. unstack() turns the quarter level into columns, giving a compact table; fill_value=0 would fill any missing combination with 0. stack() takes it back to the long form. Notice that Books peak in Q1 (exam season), Sports in Q2 (summer holidays) and Electronics in Q4 (festive season).

Common mistakes

• Using pivot() when keys repeat. It raises ValueError; use pivot_table() with an aggfunc.
• Forgetting aggfunc. pivot_table() uses the mean by default. For revenue or rainfall you usually want "sum".
• Melting without id_vars. You lose the column that identifies each row, such as the student ID.
• Comparing raw counts of unequal groups. Use normalize="index" in crosstab() to compare proportions.

Key points

• Long data has one row per observation; wide data spreads one variable across columns.
• pivot_table(index, columns, values, aggfunc) builds summary tables; margins=True adds totals.
• pivot() reshapes without aggregating and fails on duplicate index-column pairs.
• melt(id_vars, value_vars, var_name, value_name) turns wide columns into long rows.
• pd.crosstab() counts category combinations; normalize gives proportions.
• unstack() and stack() move index levels to columns and back.

import pandas as pd

emp = pd.read_csv("employees.csv").drop_duplicates()
emp["department"] = emp["department"].str.strip().str.title().replace({"Engg": "Engineering", "Hr": "HR"})

pay = emp.pivot_table(index="department", columns="work_mode", values="salary", aggfunc="median")
print(pay.round(-3))
print(pd.crosstab(emp["department"], emp["left"], normalize="index").round(2))

work_mode        Hybrid     Office     Remote
department                                   
Data Science  1396000.0  1104000.0   880000.0
Engineering   1059000.0   978000.0  1019000.0
Finance        765000.0   682000.0   862000.0
HR             911000.0   819000.0   606000.0
Sales          794000.0   692000.0   728000.0
Support        548000.0   524000.0   460000.0
left            No   Yes
department              
Data Science  0.87  0.13
Engineering   0.84  0.16
Finance       0.72  0.28
HR            0.96  0.04
Sales         0.83  0.17
Support       0.86  0.14