Create a Pivot Table in pandas
Turn long row-by-row data into a compact summary report with pivot_table.
- index, columns and values
- aggfunc: mean, sum, count
- Pivot tables versus groupby
team A85.0team B85.0Rows become a summary
The problem
Long tables with one row per record are hard to read. Managers and reports want a summary: one row per team, one number per cell. A pivot table builds that summary in one call.
Think of it like this
A pivot table is like collapsing a long list of match results into a league table. Instead of reading every match, you see one row per team with its average score, calculated for you.
index="team"🏟️ One row per teamvalues="score"📈 The number to summarizeaggfunc="mean"🧮 Average each team's scores85.0📋 The summary cell for each teamA pivot table summarizes a DataFrame by grouping rows by one or more columns and applying an aggregate function, such as mean or sum, to a value column.
Key points
index="team"makes one row in the report per team.values="score"chooses the column to summarize.aggfunc="mean"averages the scores of each team: 85.0 for both.- Each team appears once, so the report has one row for A and one for B.
When should you use it?
- Summary reports: sales by region and month, scores by class and subject.
- Turning long data into a compact grid for reading or charting.
- Quick comparisons across categories.
When not to use it
- Only one grouping and one result:
groupbyis just as clear. - You need the original rows: a pivot table only keeps the summary.
Example code
import pandas as pd
results = pd.DataFrame({
"team": ["A", "A", "B", "B"],
"score": [80, 90, 70, 100],
})
print(results.pivot_table(index="team", values="score", aggfunc="mean"))
Result of running the code
score
team
A 85.0
B 85.0
Code explanation
index="team" decides that the report has one row per team, and values="score" chooses the column to summarize. aggfunc="mean" averages the scores in each group, so team A gets (80 + 90) / 2 and team B gets (70 + 100) / 2, both 85.0.
Change the values and run the program in the CodeUtility online Python compiler without installing Python locally.
Practice exercises
Modify the runnable example with the exercises below to build understanding beyond copying the result.
- Add rows, columns, and missing values.
- Combine filtering, sorting, and groupby into a small report.
- Load a real file, inspect dtypes, and handle invalid data.
Common mistakes
Forgetting the aggregate function
pivot_table averages by default, which is wrong when you need totals.
results.pivot_table(index="team", values="score") # mean
results.pivot_table(index="team", values="score", aggfunc="sum")
Finished this lesson?
Mark it as learned to track your progress.