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), herecityvalue_vars: columns to melt, hereMonandTue(default: all others)var_name: name of the new column that holds the old headers (defaultvariable)value_name: name of the new column that holds the numbers (defaultvalue)
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
- 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).
pivotvspivot_table?pivotonly reshapes and fails on duplicate index/column pairs;pivot_tableaggregates duplicates withaggfuncand can add margins.- Default
aggfuncofpivot_table?mean. - What does
margins=Trueadd? AnAllrow and column with the aggregate over each row/column and the grand total. - What is
crosstabused for? Frequency (contingency) tables of two categorical variables;normalizegives proportions. - How do you turn columns into rows?
melt(id_vars=..., value_vars=...), orstack()for column labels into the index. stackvsunstack?stackmoves columns into the row index;unstackmoves an index level into the columns.- What does
explodedo? Turns each element of a list-like cell into its own row, repeating the other columns. - Why do charting libraries prefer long data? One column per variable maps directly to x, y, colour; wide data needs a column per series.
- SQL equivalent of
pivot_table?GROUP BYwithSUM(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.
pivotonly moves values and fails on duplicates;pivot_tableaggregates (aggfunc, default mean) and reshapes, withfill_valueandmargins.crosstabcounts category pairs;normalizegives shares.meltgoes back from wide to long (id_vars,value_vars,var_name,value_name).stack/unstackmove index levels to and from the columns.str.split+explodegive every list element its own row.- Next episode: time series in pandas with resample, rolling and shift.