Reshaping Data: Crosstabs, Pivot Tables, Wide and Long
Count and sum by two categories at once, move between wide and long tables, and learn which shares answer which question.
In this lesson you will
- Build a crosstab and a pivot table
- Move between wide and long form
- Choose the right base for a share
- Read small cells with care
About 28 minutes
Two questions at once
Sam, one of the mechanics, asks Leila a practical question: “Which kinds of bike come in with which problems? I want to stock the right parts.” A single count of repairs does not answer it, and neither does a single count of bikes. He needs both at once: for each repair, how many of each bike type. A table that counts by two categories is a crosstab, short for cross-tabulation. When it sums or averages something instead of counting, it is called a pivot table. If you have used pivot tables in a spreadsheet, the idea is the same.
Wide and long
There are two ways to lay out the same facts. In long form, each row is one fact: the repair, the bike and the number of visits. In wide form, one of the categories is spread across the columns. People find wide tables easier to read, and tools find long tables easier to process. Moving between them is called reshaping, and it is a skill worth having, because the tool you want to use often expects the other shape.
| Form | One row means |
|---|---|
| Long | One repair and one bike type |
| Wide | One repair, with a column per bike type |
The program below builds the wide table with a crosstab, prints it as short lines, checks that nothing was lost, and then adds up money in the same shape with a pivot table that includes totals. Finally it melts the wide table back to long form.
import pandas as pd
df = pd.read_csv("clean.csv")
wide = pd.crosstab(df["repair"], df["bike"])
for repair, row in wide.iterrows():
print(repair + ": " + " ".join(b + " " + str(n) for b, n in row.items()))
print("empty cells:", int((wide == 0).sum().sum()), "of", wide.size)
print("visits in table:", int(wide.values.sum()))
money = df.pivot_table(values="cost", index="repair", columns="bike", aggfunc="sum", fill_value=0, margins=True, margins_name="total")
print("money total:", money.loc["total", "total"])
print("money, gears row total:", money.loc["gears", "total"])
share = pd.crosstab(df["repair"], df["bike"], normalize="index").mul(100).round(0)
print("gears on hybrid bikes:", int(share.loc["gears", "hybrid"]), "percent of gears visits")
long = wide.reset_index().melt(id_vars="repair", var_name="bike", value_name="visits")
print("long table rows:", len(long), "| visits:", int(long["visits"].sum()))
top = long.sort_values(["visits", "repair", "bike"], ascending=[False, True, True]).iloc[0]
print("busiest pair:", top["repair"], "on", top["bike"], str(top["visits"]) + " visits")
brakes: hybrid 2 kids 0 mountain 2 road 4
chain: hybrid 2 kids 3 mountain 2 road 1
gears: hybrid 5 kids 3 mountain 2 road 0
puncture: hybrid 1 kids 3 mountain 2 road 2
wheel: hybrid 2 kids 0 mountain 1 road 1
empty cells: 3 of 20
visits in table: 38
money total: 515.5
money, gears row total: 193.5
gears on hybrid bikes: 50 percent of gears visits
long table rows: 20 | visits: 38
busiest pair: gears on hybrid 5 visits
Each line shows a repair and the number of visits for each bike type. The table holds 38 visits, the same as the clean file, which is the first check you should run on any reshaping: nothing gained and nothing lost. There are 3 empty cells out of 20. An empty cell is a true zero here, a combination that never happened in this small sample, and it is worth reading as “none seen” rather than “impossible”. With only 38 visits, a table of twenty cells is spread thin: the busiest pair has 5 visits.
Reading the table
The pivot table adds up the sample money. Its grand total is 515.5, matching the total from the first course, and the gears row alone totals 193.5. The margin row and column that hold the totals are added by one option, and they make a useful second check: the margins must agree with the sums you calculated another way. Next, the program turns counts into shares within each row, which is the right view for a question like “of the gears visits, what share were on hybrid bikes?”. The answer is 50 percent.
Counts and shares answer different questions, and the direction of the share matters. A share within each row answers “given this repair, which bikes?”. A share within each column answers “given this bike, which repairs?”. Percentages of the whole table answer neither well. Choose the direction that matches the question, and say which one you used in the heading.
Small cells and honesty
- Do not over-read small counts. A cell of two versus a cell of three is noise in 38 visits. Look for large and consistent differences, not tiny ones.
- Show the counts beside the shares. “50 percent” of ten visits and “50 percent” of two visits are very different claims.
- Beware of too many categories. The more rows and columns you make, the thinner each cell becomes. Group rare categories together when it makes sense.
- Pick the categories before looking. If you slice the data many ways until a pattern shows, you will find patterns that are only chance.
Melting back to long form
The last step in the program turns the wide table into a long one. Now it has 20 rows, one for each pair of repair and bike, and the visits still add up to 38. Long form is what most charting and modelling tools want. It also makes it easy to sort, which is how the program finds the busiest pair: gears on hybrid. Notice that the sort uses a tie-breaker, the names of the repair and the bike, so that the result cannot change if two pairs share a count.
Reading a share as if the whole table were the base. A row share and a column share are different numbers built from the same counts. Label which one you show.
The counts come from 38 made-up visits. The patterns here, such as gears on hybrid bikes, describe this small sample and are not facts about real workshops.
Practice for this lesson
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
What does a crosstab count?
- The size of the file
- How often each pair of two categories occurs together
- The average of one column
- The number of columns
It counted visits for each repair and bike pair.
In long form, what does one row hold?
- A whole table
- A column of totals
- The title of the file
- One fact, such as one repair and bike pair
Wide form spreads one category across the columns instead.
What is the first check after reshaping?
- That the colours match
- That the total number of visits has not changed
- That the table is sorted
- That the file is smaller
Nothing should be gained or lost.
A share within each row answers which question?
- Given this bike, which repairs?
- What share of the whole table is this?
- How many columns are there?
- Given this repair, which bikes?
Choose the direction that matches the question.
Why be careful with very small cells?
- A difference of one or two visits can be pure chance
- Small cells are always errors
- They cannot be sorted
- They change the totals
Thin cells in a small sample are noisy.
Summary
- A crosstab counts pairs of categories; a pivot table sums or averages.
- Wide is easier to read; long is easier for tools.
- Check that totals survive every reshaping.
- Name the base of every share and show the counts.
Questions
What is the difference between a crosstab and a pivot table?
A crosstab counts how often combinations occur. A pivot table summarises another column, for example the sum or mean of cost, for each combination. Both build on the same idea of grouping by two categories.
Why do tools like long form?
Because every fact has the same shape, so a tool can treat the category columns as options for colour, grouping or filtering without special cases.
Can I do this in a spreadsheet?
Yes. Pivot tables in a spreadsheet build the same crosstab, and the same cautions about small cells and the base of a share apply.
