Master Pandas From Scratch

Learn Pandas fundamentals, Series, DataFrames, data selection, filtering, cleaning, sorting, grouping, merging, reshaping, string operations, time series, data analysis, performance optimization, visualization, and practical projects through structured lessons.

COURSE SYLLABUS
1. Pandas Introduction and Environment Setup
Introduction to Pandas Installation and Environment Setup
2. Pandas Core Data Structures
Data Structures Overview Pandas Series DataFrames Indexes, Rows and Columns Shape, Size and Dimensions
3. Pandas Data Input and Output
Data Input and Output Overview Reading CSV, Excel and JSON Files Writing and Exporting Files
4. Initial Data Inspection
Data Inspection Overview Head and Tail Data Info and Types Descriptive Statistics
5. Selection and Indexing
Selection and Indexing Overview Column Selection loc and iloc Index Management
6. Filtering and Conditional Selection
Filtering Overview Boolean Filtering Multiple Conditions isin, between and str.contains
7. Basic Data Cleaning
Data Cleaning Overview Missing Values Duplicate Data Rename and Replace Data Type Conversion
8. Vectorized Operations and Sorting
Operations and Sorting Overview Column Operations Sorting Values and Indexes Sum, Mean, Max and Min
9. GroupBy and Aggregation
GroupBy Overview Grouping Data Aggregation Functions Group Filtering Group Transform
10. Merging, Joining and Concatenating
Merge, Join and Concat Overview Concatenation Merging DataFrames Joining by Index
11. Reshaping and Pivoting
Reshaping Overview Pivot Tables Melting Data Stack and Unstack Cross Tabulation
12. String and Categorical Data
String and Categorical Overview String Operations Categorical Data One-Hot Encoding
13. Time Series Analysis
Time Series Overview DateTime Operations Time-Based Indexes Resampling Data Shift and Rolling Windows
14. Custom Functions and Mapping
Apply and Mapping Overview Apply and Lambda Functions Map Operations Element-Wise Operations
15. Performance and Large Datasets
Performance Overview Memory Optimization Processing Files in Chunks PyArrow, Dask and Polars
16. Pandas Data Visualization
Visualization Overview Basic Plotting Histograms, Lines, Scatter and Box Plots Matplotlib and Seaborn

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.

From Raw Data to a Pivot Table Raw Data Student | Subject | Score Samadhan | Math | 85 Samadhan | Science | 90 Pradnya | Math | 92 pivot_table() Summarize Pivot Table Student | Math | Science Samadhan | 85 | 90 Pradnya | 92 | 88 Rows identify groups; columns identify categories; values provide the measurements.

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.

Remember: The Student values become row labels, Subject values become column labels, and Score provides the cell values.

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.

Tip: Multiple aggregation functions are useful for performance reports that need an average, maximum, and minimum in the same table.

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.

Important: A MultiIndex column structure is normal when you use multiple column fields or multiple aggregation functions. Inspect 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.

Important: When using 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.

Data-quality note: A missing score does not automatically mean the score is zero. Use 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

  1. What is a pivot table in Pandas?
  2. What is the difference between pivot() and pivot_table()?
  3. What does the values parameter specify?
  4. How do the index and columns parameters differ?
  5. What is the purpose of aggfunc?
  6. How can you calculate multiple statistics in one pivot table?
  7. What does margins=True do?
  8. When should you use fill_value?
  9. Why can pivot tables create MultiIndex columns?
  10. 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.