SyntaxPilot
Courses/Data Analytics with Python/Lesson 27

Data Analytics with Python · Lesson 27 of 90 · 11 min

Reshaping: pivot, pivot_table, melt and crosstab

wide formatlong formatpivotpivot_tableaggfuncfill_valuemarginscrosstabnormalizemeltid_varsvalue_varsstackunstack

These are the notes for Data Analytics with Python, episode 27. The same facts can be stored in different shapes. A long list of sales is perfect for pandas but painful for a person; a spreadsheet grid is perfect for a person but awkward for pandas. Here you invent the tools that change a table's shape without changing its facts: long vs wide format, pivot (and why it fails on duplicates), pivot_table with aggfunc, fill_value and margins, crosstab with normalize, melt with id_vars and value_vars, stack / unstack, and explode for lists inside cells. Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5); outputs are unedited.

The data: a sales log

One line per sale: the day, the city, the item and the amount (₹). Pune Chai appears three times (Monday 120, Tuesday 150 and 30).

import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """day,city,item,amount
Mon,Pune,Chai,120
Mon,Pune,Samosa,90
Mon,Nashik,Chai,80
Tue,Pune,Chai,150
Tue,Nashik,Chai,60
Tue,Nashik,Samosa,45
Tue,Pune,Chai,30
"""
sales = pd.read_csv(io.StringIO(raw))

Long vs wide format

Ramesh asks: which city sells more chai? With a list he has to scroll and add in his head. He wants a grid: cities down the side, items across the top, totals in the cells, like a school timetable where you find a cell by its row and column.

Shape One row per… Columns Best for
Long format observation (one sale) few: day, city, item, amount pandas: groupby, filters, charts
Wide format thing (one city) one per category (Chai, Samosa…) people: reports, Excel

Long:

city   item    amount
Pune   Chai    120
Pune   Samosa   90

Wide:

city   Chai  Samosa
Pune    300      90

Long format is the tidy data of episode 2 (each variable a column, each observation a row). Reshaping means moving between the two shapes. Pivoting is long → wide; melting is wide → long.

pivot, and why it fails

DataFrame.pivot(index=, columns=, values=): index = the column whose values become the rows, columns = the column whose values become the column headers, values = what goes into the cells.

print(sales.pivot(index="city", columns="item", values="amount"))
ValueError: Index contains duplicate entries, cannot reshape

Why: Pune Chai appears three times, so the (Pune, Chai) cell would need three numbers. pivot only moves values into place; it never adds them up. One cell, one value.

Fix 1: aggregate first so each (row, column) pair is unique, then pivot:

daily = sales.groupby(["day", "city"], as_index=False)["amount"].sum()
print(daily)
print(daily.pivot(index="day", columns="city", values="amount"))
   day    city  amount
0  Mon  Nashik      80
1  Mon    Pune     210
2  Tue  Nashik     105
3  Tue    Pune     180
city  Nashik  Pune
day               
Mon       80   210
Tue      105   180

pivot_table: aggregate and reshape in one step

Real-life picture: a shopkeeper with a pile of bills (long data) draws a monthly register (rows = city, columns = item) and tallies each bill into its box; bills that land in the same box are added. Then he writes row and column totals at the edges.

pivot_table = pivot + an aggregation function aggfunc applied to every box.

p = sales.pivot_table(index="city", columns="item",
                      values="amount", aggfunc="sum")
print(p)
item    Chai  Samosa
city                
Nashik   140      45
Pune     300      90

Pune Chai = 120 + 150 + 30 = 300. Careful: the default aggfunc is "mean", not "sum", so always say which one you want.

fill_value=0 fills empty boxes; margins=True adds an All row and column with totals (the margins), computed with the same aggfunc:

p = sales.pivot_table(index="city", columns="item", values="amount",
                      aggfunc="sum", fill_value=0, margins=True)
print(p)
item    Chai  Samosa  All
city                     
Nashik   140      45  185
Pune     300      90  390
All      440     135  575

aggfunc also takes a list; the result gets a two-level column header (function on top, column value below):

print(sales.pivot_table(index="day", columns="city",
                        values="amount", aggfunc=["sum", "count"]))
        sum       count     
city Nashik Pune Nashik Pune
day                         
Mon      80  210      1    2
Tue     105  180      2    2

A wrong column name:

print(sales.pivot_table(index="city", columns="item", values="Amount"))
KeyError: 'Amount'
pivot pivot_table
only moves values aggregates, then reshapes
fails on duplicate (index, column) pairs handles duplicates with aggfunc
no totals margins=True
keeps any dtype (text too) numeric aggregation (default mean)

crosstab: counts and percentages

Sometimes the question is how often, not how much. pd.crosstab(a, b) (cross tabulation) counts how many rows fall into each pair of categories: a frequency table (contingency table). margins=True adds totals; normalize turns counts into fractions: "index" = each row sums to 1, "columns" = each column sums to 1, "all" = the whole table sums to 1.

print(pd.crosstab(sales["city"], sales["item"], margins=True))
print(pd.crosstab(sales["city"], sales["item"], normalize="index").round(2))
item    Chai  Samosa  All
city                     
Nashik     2       1    3
Pune       3       1    4
All        5       2    7
item    Chai  Samosa
city                
Nashik  0.67    0.33
Pune    0.75    0.25

75% of Pune's sales (by count) are Chai, 67% of Nashik's. crosstab can also aggregate a value with values= and aggfunc=. The same grid also comes from groupby on two keys plus unstack (episode 25): three roads to one table.

print(pd.crosstab(sales["city"], sales["item"],
                  values=sales["amount"], aggfunc="sum"))
print(sales.groupby(["city", "item"])["amount"].sum().unstack())
item    Chai  Samosa
city                
Nashik   140      45
Pune     300      90
item    Chai  Samosa
city                
Nashik   140      45
Pune     300      90

melt: from a grid back to a list

The reverse problem: Ramesh's manager sends a spreadsheet with one column per day. To total per day, the obvious attempt groups by day:

wide = pd.DataFrame({"city": ["Pune", "Nashik"],
                     "Mon": [210, 80], "Tue": [180, 105]})
print(wide)
print(wide.groupby("day")["amount"].sum())
     city  Mon  Tue
0    Pune  210  180
1  Nashik   80  105
KeyError: 'day'

Why: there is no day column. The days are column headers, not values, and pandas can't group by headers.

melt turns columns into rows (wide → long):

  • id_vars: columns to keep as they are (identifiers), here city
  • value_vars: columns to melt, here Mon and Tue (default: all others)
  • var_name: name of the new column that holds the old headers (default variable)
  • value_name: name of the new column that holds the numbers (default value)
wide = pd.DataFrame({"city": ["Pune", "Nashik"],
                     "Mon": [210, 80], "Tue": [180, 105]})
long = wide.melt(id_vars="city", value_vars=["Mon", "Tue"],
                 var_name="day", value_name="amount")
print(long)
print(long.groupby("day")["amount"].sum())
     city  day  amount
0    Pune  Mon     210
1  Nashik  Mon      80
2    Pune  Tue     180
3  Nashik  Tue     105
day
Mon    290
Tue    285
Name: amount, dtype: int64

melt is the opposite of pivot. A round trip, melt then pivot, gives the sheet back (only the city order changes, because pivot sorts the index); no fact is lost:

wide = pd.DataFrame({"city": ["Pune", "Nashik"],
                     "Mon": [210, 80], "Tue": [180, 105]})
long = wide.melt(id_vars="city", var_name="day", value_name="amount")
back = long.pivot(index="city", columns="day", values="amount")
print(back)
day     Mon  Tue
city            
Nashik   80  105
Pune    210  180

stack and unstack

These move index levels (episode 25) between rows and columns:

  • stack: column labels → the innermost row index level (wide → long Series with a MultiIndex)
  • unstack: an index level → columns (long → wide); unstack(level=0) sends the outer level up
p = sales.pivot_table(index="city", columns="item",
                      values="amount", aggfunc="sum")
s = p.stack()
print(s)
print(s.unstack(level=0))
city    item  
Nashik  Chai      140
        Samosa     45
Pune    Chai      300
        Samosa     90
dtype: int64
city    Nashik  Pune
item                
Chai       140   300
Samosa      45    90

After unstack(level=0) the grid is turned around: items are rows, cities are columns (the same as p.T, the transpose, here).

From → to Column-based tool Index-based tool
long → wide pivot, pivot_table unstack
wide → long melt stack

explode: lists inside cells

Some apps store several items in one cell, separated by commas. Counting items with value_counts counts whole combinations:

o = pd.DataFrame({"order_id": [1, 2, 3],
                  "items": ["Chai,Samosa", "Chai", "Coffee,Chai,Samosa"]})
print(o["items"].value_counts())
items
Chai,Samosa           1
Chai                  1
Coffee,Chai,Samosa    1
Name: count, dtype: int64

Fix: str.split(",") (episode 21) turns each string into a list, then explode gives every list element its own row, copying the other columns (and the index label):

o = pd.DataFrame({"order_id": [1, 2, 3],
                  "items": ["Chai,Samosa", "Chai", "Coffee,Chai,Samosa"]})
o["items"] = o["items"].str.split(",")
e = o.explode("items")
print(e)
print(e["items"].value_counts())
   order_id   items
0         1    Chai
0         1  Samosa
1         2    Chai
2         3  Coffee
2         3    Chai
2         3  Samosa
items
Chai      3
Samosa    2
Coffee    1
Name: count, dtype: int64

Mini project: item_mix()

The share of each item in each city's sales. Pivot with sums; t.sum(axis=1) gives one total per row (city); div(..., axis=0) divides each row by its own total (row-wise division, lining the totals up with the rows); add_suffix appends text to every column name.

def item_mix(df):
    t = df.pivot_table(index="city", columns="item", values="amount",
                       aggfunc="sum", fill_value=0)
    share = (100 * t.div(t.sum(axis=1), axis=0)).round(1)
    return share.add_suffix(" %").reset_index()
print(item_mix(sales))
item    city  Chai %  Samosa %
0     Nashik    75.7      24.3
1       Pune    76.9      23.1

About three quarters of the money in both cities comes from Chai.

Common mistakes

Code / habit Real error / symptom Why Fix
sales.pivot(index="city", columns="item", values="amount") ValueError: Index contains duplicate entries, cannot reshape pivot can't add up repeats pivot_table(..., aggfunc="sum") or aggregate first
pivot_table without aggfunc no error; averages instead of totals default aggfunc is mean aggfunc="sum"
values="Amount" KeyError: 'Amount' names are case-sensitive values="amount"
wide.groupby("day") KeyError: 'day' the days are column headers melt(id_vars="city", var_name="day")
value_counts() on "Chai,Samosa" no error; counts combinations several items in one cell str.split(",") + explode
crosstab percentages that don't add up to 100 wrong normalize axis rows vs columns normalize="index" for row %
t / t.sum(axis=1) NaN everywhere aligns totals with the columns t.div(t.sum(axis=1), axis=0)

Practice

All practice questions use this marks table (long format):

import sys; sys.tracebacklimit = 0
import pandas as pd
m = pd.DataFrame({"student": ["Asha", "Asha", "Ravi", "Ravi", "Meena", "Meena", "Asha"],
                  "subject": ["Maths", "Science", "Maths", "Science", "Maths", "Science", "Maths"],
                  "test": [1, 1, 1, 1, 1, 1, 2],
                  "marks": [78, 82, 64, 70, 91, 88, 84]})

1. Try pivot with students as rows and subjects as columns. Why does it fail? Fix it with pivot_table using each student's best mark.

Solution
try:
    m.pivot(index="student", columns="subject", values="marks")
except ValueError as e:
    print("ValueError:", e)
print(m.pivot_table(index="student", columns="subject", values="marks", aggfunc="max"))
ValueError: Index contains duplicate entries, cannot reshape
subject  Maths  Science
student                
Asha        84       82
Meena       91       88
Ravi        64       70

Asha has two Maths tests, so the (Asha, Maths) cell would need two values.

2. Average marks per student and subject with totals (margins).

Solution
print(m.pivot_table(index="student", columns="subject", values="marks",
                    aggfunc="mean", margins=True).round(1))
subject  Maths  Science   All
student                      
Asha      81.0     82.0  81.3
Meena     91.0     88.0  89.5
Ravi      64.0     70.0  67.0
All       79.2     80.0  79.6

3. How many tests did each student take in each subject (crosstab)? Then as row percentages.

Solution
print(pd.crosstab(m["student"], m["subject"]))
print(pd.crosstab(m["student"], m["subject"], normalize="index").round(2))
subject  Maths  Science
student                
Asha         2        1
Meena        1        1
Ravi         1        1
subject  Maths  Science
student                
Asha      0.67     0.33
Meena     0.50     0.50
Ravi      0.50     0.50

4. A teacher's sheet is wide. Melt it, then find each subject's average.

Solution
sheet = pd.DataFrame({"student": ["Asha", "Ravi"], "Maths": [78, 64], "Science": [82, 70]})
long = sheet.melt(id_vars="student", var_name="subject", value_name="marks")
print(long)
print(long.groupby("subject")["marks"].mean())
  student  subject  marks
0    Asha    Maths     78
1    Ravi    Maths     64
2    Asha  Science     82
3    Ravi  Science     70
subject
Maths      71.0
Science    76.0
Name: marks, dtype: float64

5. Make the best-mark grid from question 1, stack it back to a long Series, and unstack the student level instead.

Solution
g = m.pivot_table(index="student", columns="subject", values="marks", aggfunc="max")
s = g.stack()
print(s)
print(s.unstack(level="student"))
student  subject
Asha     Maths      84
         Science    82
Meena    Maths      91
         Science    88
Ravi     Maths      64
         Science    70
dtype: int64
student  Asha  Meena  Ravi
subject                   
Maths      84     91    64
Science    82     88    70

6. Clubs are stored as comma-separated text. How many students are in each club?

Solution
c = pd.DataFrame({"student": ["Asha", "Ravi", "Meena"], "clubs": ["Chess,Music", "Music", "Chess,Art,Music"]})
e = c.assign(clubs=c["clubs"].str.split(",")).explode("clubs")
print(e["clubs"].value_counts())
clubs
Music    3
Chess    2
Art      1
Name: count, dtype: int64

Quick quiz

wide = pd.DataFrame({"city": ["A", "B", "C"],
                     "Mon": [1, 2, 3],
                     "Tue": [4, 5, 6]})
print(wide.melt(id_vars="city").shape)

A. (3, 3) B. (6, 3) C. (6, 2) D. (3, 2)

Answer

B. (6, 3). 3 cities × 2 day columns = 6 rows; the columns are city, variable, value.

Interview questions

  1. Long vs wide format? Long: one row per observation (tidy, best for analysis). Wide: one row per entity with a column per category (best for reading).
  2. pivot vs pivot_table? pivot only reshapes and fails on duplicate index/column pairs; pivot_table aggregates duplicates with aggfunc and can add margins.
  3. Default aggfunc of pivot_table? mean.
  4. What does margins=True add? An All row and column with the aggregate over each row/column and the grand total.
  5. What is crosstab used for? Frequency (contingency) tables of two categorical variables; normalize gives proportions.
  6. How do you turn columns into rows? melt(id_vars=..., value_vars=...), or stack() for column labels into the index.
  7. stack vs unstack? stack moves columns into the row index; unstack moves an index level into the columns.
  8. What does explode do? Turns each element of a list-like cell into its own row, repeating the other columns.
  9. Why do charting libraries prefer long data? One column per variable maps directly to x, y, colour; wide data needs a column per series.
  10. SQL equivalent of pivot_table? GROUP BY with SUM(CASE WHEN item='Chai' THEN amount END) AS chai, ... (or PIVOT in some databases).

Glossary

Term Meaning Example
Reshaping Changing a table's shape without changing its facts long ↔ wide
Long format One row per observation, few columns day, city, item, amount
Wide format One row per entity, a column per category city, Chai, Samosa
Tidy data Each variable a column, each observation a row the sales log
Pivot Long → wide: values of one column become headers items across the top
pivot Reshape only; one cell = one value fails on duplicates
pivot_table Aggregate + reshape aggfunc="sum"
index / columns / values Rows, headers and cell contents of a pivot city / item / amount
aggfunc The aggregation used per cell (default mean) "sum", ["sum", "count"]
fill_value Value for empty cells 0
Margins Row/column totals at the edges (All) margins=True
Cross tabulation (crosstab) Counts of each pair of categories city × item
Frequency / contingency table A table of counts for category combinations crosstab output
normalize Turn counts into fractions ("index", "columns", "all") row shares
Melt Wide → long: headers become values Mon/Tue → day
id_vars Columns kept as identifiers in melt city
value_vars Columns melted into rows ["Mon", "Tue"]
var_name / value_name Names of melt's new columns day / amount
stack Column labels → innermost row index level grid → long Series
unstack An index level → columns long Series → grid
explode One row per element of a list in a cell order 3 → 3 rows
str.split Split text into a list "Chai,Samosa" → ["Chai", "Samosa"]
div(axis=0) Divide each row by a matching value row shares
add_suffix Append text to every column name "Chai %"
Transpose (.T) Swap rows and columns items ↔ cities

Summary

  • Long format (one row per observation) is for pandas; wide format (a column per category) is for people.
  • pivot only moves values and fails on duplicates; pivot_table aggregates (aggfunc, default mean) and reshapes, with fill_value and margins.
  • crosstab counts category pairs; normalize gives shares.
  • melt goes back from wide to long (id_vars, value_vars, var_name, value_name).
  • stack / unstack move index levels to and from the columns.
  • str.split + explode give every list element its own row.
  • Next episode: time series in pandas with resample, rolling and shift.