Pivot Tables in Pandas
Learn how to summarize, reorganize, and analyze datasets using pivot(), pivot_table(), aggregation functions, multiple indexes, and multiple columns.
Learning objectives: Understand the difference between pivot() and pivot_table(), calculate totals and averages, handle duplicate combinations, work with missing values, and create practical summary reports.
1. What Is a Pivot Table?
A pivot table summarizes data by arranging values into rows and columns. It helps you compare categories and calculate statistics such as totals, averages, counts, minimums, and maximums.
Real-world example
Samadhan maintains examination records for students across several subjects. Pradnya wants to compare each student's scores in Math and Science. A pivot table can place each student in a row and each subject in a separate column.
A pivot table reorganizes data into a compact summary for comparison.
2. Create a Sample Dataset
We will use a student examination dataset with repeated student-subject combinations to understand both pivoting and aggregation.
import pandas as pd
df = pd.DataFrame({
"Student": [
"Samadhan", "Samadhan",
"Pradnya", "Pradnya",
"Samiksha", "Samiksha"
],
"Subject": [
"Math", "Science",
"Math", "Science",
"Math", "Science"
],
"Score": [85, 90, 92, 88, 78, 95],
"Class": ["A", "A", "A", "A", "B", "B"]
})
print(df)
Expected output
Student Subject Score Class 0 Samadhan Math 85 A 1 Samadhan Science 90 A 2 Pradnya Math 92 A 3 Pradnya Science 88 A 4 Samiksha Math 78 B 5 Samiksha Science 95 B
Each row represents a student-subject observation. The Student column identifies the student, Subject identifies the category, and Score contains the numeric measurement.
3. Understand the pivot_table() Parameters
The pivot_table() method groups data and calculates a summary for each combination of row and column categories.
pd.pivot_table(
data=df,
values="Score",
index="Student",
columns="Subject",
aggfunc="mean"
)
| Parameter | Purpose | Example |
|---|---|---|
data |
The DataFrame to summarize. | data=df |
values |
The column containing the measurements. | values="Score" |
index |
The row grouping column or columns. | index="Student" |
columns |
The categories that become output columns. | columns="Subject" |
aggfunc |
The aggregation function. | aggfunc="mean" |
fill_value |
Replaces missing values in the resulting pivot table. | fill_value=0 |
margins |
Adds summary totals or aggregates. | margins=True |
4. Create Your First Pivot Table
Use Student as the row index, Subject as the column categories, and Score as the measured value.
pivot = df.pivot_table(
values="Score",
index="Student",
columns="Subject"
)
print(pivot)
Expected output
Subject Math Science Student Pradnya 92.0 88.0 Samadhan 85.0 90.0 Samiksha 78.0 95.0
The default aggregation function is the mean. Because each student-subject combination occurs only once in this dataset, each cell contains the original score.
5. Difference Between pivot() and pivot_table()
Both methods reshape data, but they handle repeated combinations differently.
Using pivot()
simple_pivot = df.pivot(
index="Student",
columns="Subject",
values="Score"
)
print(simple_pivot)
This works because each Student-Subject combination has exactly one score.
Using pivot_table()
summary = df.pivot_table(
index="Student",
columns="Subject",
values="Score",
aggfunc="mean"
)
print(summary)
This also works and calculates the mean for each combination. It can handle repeated Student-Subject combinations by aggregating their scores.
| Feature | pivot() |
pivot_table() |
|---|---|---|
| Purpose | Reshape data. | Reshape and aggregate data. |
| Duplicate combinations | Raises an error. | Combines repeated values using an aggregation function. |
| Aggregation parameter | Not supported. | Supports aggfunc. |
| Typical use | Unique category combinations. | Repeated observations and summaries. |
6. Handle Duplicate Combinations
Suppose Samadhan takes the Math examination twice. The dataset now contains two scores for the same student and subject.
attempts = pd.DataFrame({
"Student": [
"Samadhan",
"Samadhan",
"Samadhan",
"Pradnya"
],
"Subject": [
"Math",
"Math",
"Science",
"Math"
],
"Score": [80, 90, 95, 92]
})
print(attempts)
Expected output
Student Subject Score 0 Samadhan Math 80 1 Samadhan Math 90 2 Samadhan Science 95 3 Pradnya Math 92
Calling pivot() on this data would fail because Samadhan has two Math records. Use pivot_table() to summarize them.
result = attempts.pivot_table(
index="Student",
columns="Subject",
values="Score",
aggfunc="mean"
)
print(result)
Expected output
Subject Math Science Student Pradnya 92.0 NaN Samadhan 85.0 95.0
Samadhan's Math average is calculated as (80 + 90) / 2 = 85. The Science value is 95.
7. Use Different Aggregation Functions
The aggfunc parameter determines how repeated values are summarized.
Calculate the average
average_scores = attempts.pivot_table(
index="Student",
columns="Subject",
values="Score",
aggfunc="mean"
)
print(average_scores)
Calculate the sum
total_scores = attempts.pivot_table(
index="Student",
columns="Subject",
values="Score",
aggfunc="sum"
)
print(total_scores)
Expected output for the sum
Subject Math Science Student Pradnya 92 NaN Samadhan 170 95.0
Samadhan's two Math scores are added together to produce 170. Missing combinations remain missing by default.
Calculate the maximum
maximum_scores = attempts.pivot_table(
index="Student",
columns="Subject",
values="Score",
aggfunc="max"
)
print(maximum_scores)
Expected output
Subject Math Science Student Pradnya 92 NaN Samadhan 90 95.0
Common aggregation functions
| Function | Meaning |
|---|---|
"mean" |
Calculate the average. |
"sum" |
Add values together. |
"count" |
Count non-missing values. |
"min" |
Find the minimum. |
"max" |
Find the maximum. |
"median" |
Calculate the median. |
8. Use Multiple Aggregation Functions
You can calculate several statistics for the same value column by passing a list of aggregation functions.
result = attempts.pivot_table(
index="Student",
columns="Subject",
values="Score",
aggfunc=["mean", "max", "min"]
)
print(result)
Expected output
mean max min Subject Math Science Math Science Math Science Student Pradnya 92.0 NaN 92 NaN 92 NaN Samadhan 85.0 95.0 90 95.0 80 95.0
The result has multiple column levels: the aggregation function and the subject. This structure is a MultiIndex on the columns.
9. Group by Multiple Row Categories
You can pass multiple columns to index to create groups based on more than one category.
report = df.pivot_table(
values="Score",
index=["Class", "Student"],
columns="Subject",
aggfunc="mean"
)
print(report)
Expected output
Subject Math Science
Class Student
A Pradnya 92.0 88.0
Samadhan 85.0 90.0
B Samiksha 78.0 95.0
The output groups students under their class labels. The index now has two levels: Class and Student.
10. Use Multiple Column Categories
Passing multiple column categories creates a hierarchical column structure. This is helpful when the output needs to compare several dimensions.
df["Term"] = ["Term 1", "Term 1", "Term 1",
"Term 1", "Term 2", "Term 2"]
result = df.pivot_table(
values="Score",
index="Student",
columns=["Term", "Subject"],
aggfunc="mean"
)
print(result)
The columns are organized into multiple levels: Term and Subject. Because the example assigns different terms to some records, the result may contain missing combinations for subjects that do not occur within a given term.
result.columns if you need to select a particular group of columns.
11. Add Row and Column Totals with margins
Set margins=True to add an All row and column. The aggregation function is applied to the totals as well.
result = df.pivot_table(
values="Score",
index="Student",
columns="Subject",
aggfunc="mean",
margins=True
)
print(result)
Expected output
Subject Math Science All Student Pradnya 92.0 88.0 90.000000 Samadhan 85.0 90.0 87.500000 Samiksha 78.0 95.0 86.500000 All 85.0 91.0 88.000000
The All row displays the average score for each subject, and the All column displays each student's average across subjects.
margins=True, the All totals are calculated from the underlying data. For a mean, the overall mean is not necessarily the unweighted average of subgroup means when group sizes differ.
12. Replace Missing Values with fill_value
When a student has no score for a subject, the corresponding pivot-table cell is normally NaN. Use fill_value when a replacement value makes sense for your analysis.
result = attempts.pivot_table(
index="Student",
columns="Subject",
values="Score",
aggfunc="mean",
fill_value=0
)
print(result)
Expected output
Subject Math Science Student Pradnya 92 0 Samadhan 85 95
The missing Science score for Pradnya is replaced with zero in the resulting table.
fill_value=0 only when zero is the correct interpretation for your report.
13. Control the Output with dropna and observed
Two additional parameters can affect which categories appear in a pivot table.
Use dropna
The dropna parameter controls how missing values affect the result. By default, all-NA columns may be dropped, and rows with missing values in grouping keys can be excluded.
result = df.pivot_table(
values="Score",
index="Student",
columns="Subject",
aggfunc="mean",
dropna=True
)
print(result)
Use observed with categorical data
If a grouping column has the categorical data type, the observed parameter controls whether unobserved category combinations are included. Current Pandas versions use observed=True by default; set it explicitly when the intended behavior matters.
df["Subject"] = pd.Categorical(
df["Subject"],
categories=["Math", "Science", "English"]
)
result = df.pivot_table(
values="Score",
index="Student",
columns="Subject",
aggfunc="mean",
observed=True
)
print(result)
With observed=True, the result focuses on categories actually observed in the data. Category behavior can depend on the Pandas version and the other pivot-table parameters.
14. Sort and Select Pivot Table Results
Pivot tables return a DataFrame, so you can use standard DataFrame operations to sort, select, and filter the results.
Sort students by Math score
pivot = df.pivot_table(
values="Score",
index="Student",
columns="Subject",
aggfunc="mean"
)
result = pivot.sort_values(
by="Math",
ascending=False
)
print(result)
Expected output
Subject Math Science Student Pradnya 92.0 88.0 Samadhan 85.0 90.0 Samiksha 78.0 95.0
Select one subject
print(pivot["Math"])
Expected output
Student Pradnya 92.0 Samadhan 85.0 Samiksha 78.0 Name: Math, dtype: float64
Filter students with Math scores above 80
result = pivot[pivot["Math"] > 80] print(result)
Expected output
Subject Math Science Student Pradnya 92.0 88.0 Samadhan 85.0 90.0
15. Practical Mini Project: Student Performance Dashboard
Create a summary that reports average scores by class and subject, including class totals and the overall average.
import pandas as pd
scores = pd.DataFrame({
"Student": [
"Samadhan", "Samadhan",
"Pradnya", "Pradnya",
"Samiksha", "Samiksha",
"Aarav", "Aarav"
],
"Class": ["A", "A", "A", "A", "B", "B", "B", "B"],
"Subject": [
"Math", "Science",
"Math", "Science",
"Math", "Science",
"Math", "Science"
],
"Score": [85, 90, 92, 88, 78, 95, 76, 82]
})
dashboard = scores.pivot_table(
values="Score",
index="Class",
columns="Subject",
aggfunc="mean",
margins=True
)
print(dashboard)
Expected output
Subject Math Science All Class A 88.5 89.0 88.75 B 77.0 88.5 82.75 All 82.75 88.75 85.75
The report compares the average scores for each class and subject. The All row and column provide overall summaries.
Export the report to CSV
dashboard.to_csv("student_performance_report.csv")
The summary is saved to a CSV file in the current working directory.
16. Common Mistakes and Best Practices
Mistake 1: Using pivot() with duplicate keys
Use pivot_table() when repeated index-column combinations need aggregation.
Mistake 2: Choosing the wrong aggregation
Use mean for averages, sum for totals, and count for the number of non-missing observations. The correct function depends on the business question.
Mistake 3: Replacing missing values without checking
Do not automatically replace every missing value with zero. Missing data may represent an unrecorded observation rather than a zero measurement.
Mistake 4: Misinterpreting the All row
When using margins=True, totals are calculated from the underlying records. For averages, subgroup means may not contribute equally if their group sizes differ.
Mistake 5: Forgetting MultiIndex columns
Multiple aggregation functions or multiple column categories can create hierarchical columns. Inspect pivot.columns when selecting the output.
17. Practice Exercises
Exercise 1: Basic pivot table
Create a pivot table with Student as the index, Subject as columns, and Score as values.
Hint: Use pivot_table().
Exercise 2: Calculate average scores
Create repeated scores for one student and calculate the average for each subject.
Hint: Use aggfunc="mean".
Exercise 3: Calculate totals
Summarize all scores for each student and subject.
Hint: Use aggfunc="sum".
Exercise 4: Add margins
Create a pivot table with subject averages and overall totals.
Hint: Use margins=True.
Exercise 5: Use multiple grouping columns
Group a dataset by Class and Student, with subjects as columns.
Hint: Use index=["Class", "Student"].
Exercise 6: Handle missing values
Create a pivot table with missing subject scores and compare the result with and without fill_value=0.
Exercise 7: Export a report
Build a class-level performance dashboard and save it as a CSV file.
Hint: Use pivot_table() followed by to_csv().
18. Revision Questions
- What is a pivot table in Pandas?
- What is the difference between
pivot()andpivot_table()? - What does the
valuesparameter specify? - How do the
indexandcolumnsparameters differ? - What is the purpose of
aggfunc? - How can you calculate multiple statistics in one pivot table?
- What does
margins=Truedo? - When should you use
fill_value? - Why can pivot tables create MultiIndex columns?
- How can you sort and filter a pivot table?
Summary
Pivot tables organize and summarize datasets by grouping rows and columns. Use pivot() when every index-column combination identifies a single value. Use pivot_table() when you need aggregation, multiple statistics, totals, or handling of repeated combinations. The index, columns, values, and aggfunc parameters define the structure and calculation, while margins and fill_value provide additional reporting options.
Key concepts: pivot(), pivot_table(), aggfunc, index, columns, margins, fill_value, and MultiIndex columns.