Pandas Pivot Table and Crosstab Practice Questions with Solutions

By /

Pivot tables and crosstabs are powerful data summarization tools in Pandas. They help transform large datasets into meaningful reports by grouping, aggregating, and comparing values across different categories. The pivot_table() function creates spreadsheet-like summary tables, while crosstab() is mainly used to calculate frequencies and relationships between categorical variables. These techniques are widely used in business intelligence, sales analysis, HR reporting, finance, and data analytics. Pandas Pivot Table and Crosstab practice questions with solutions help to understand the concepts.

1. Python Program to Create a Basic Pivot Table

Problem Statement

Write a Python program to create a pivot table showing the average marks for each department.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR"],
    "Marks": [85, 90, 78, 88]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Marks",
    index="Department",
    aggfunc="mean"
)

print(pivot)

Sample Output

            Marks
Department
HR           89.0
IT           81.5

Explanation

The pivot_table() function groups data by Department and calculates the average marks.

Concepts Covered

  • pivot_table()
  • Average Calculation
  • Data Summarization

2. Python Program to Calculate the Sum Using Pivot Table

Problem Statement

Write a Python program to calculate the total sales for each department.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR"],
    "Sales": [5000, 6000, 4500, 5500]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Sales",
    index="Department",
    aggfunc="sum"
)

print(pivot)

Sample Output

            Sales
Department
HR          11500
IT           9500

Explanation

Using aggfunc="sum" calculates the total sales for each department.

Concepts Covered

  • pivot_table()
  • sum
  • Aggregation

3. Python Program to Create a Pivot Table with Rows and Columns

Problem Statement

Write a Python program to create a pivot table showing average marks by Department and Gender.

Python Solution

import pandas as pd

data = {
    "Department": [
        "IT",
        "IT",
        "HR",
        "HR"
    ],
    "Gender": [
        "Male",
        "Female",
        "Male",
        "Female"
    ],
    "Marks": [85, 78, 90, 88]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Marks",
    index="Department",
    columns="Gender",
    aggfunc="mean"
)

print(pivot)

Sample Output

Gender      Female  Male
Department
HR            88.0  90.0
IT            78.0  85.0

Explanation

The columns parameter creates separate columns for each gender while grouping by department.

Concepts Covered

  • pivot_table()
  • Multiple Dimensions
  • Data Summary

4. Python Program to Create a Pivot Table with Multiple Aggregation Functions

Problem Statement

Write a Python program to calculate the sum, average, and maximum sales for each department.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR"],
    "Sales": [5000, 6000, 4500, 5500]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Sales",
    index="Department",
    aggfunc=["sum", "mean", "max"]
)

print(pivot)

Sample Output

              sum    mean    max
            Sales   Sales  Sales
Department
HR          11500  5750.0   6000
IT           9500  4750.0   5000

Explanation

The aggfunc parameter accepts a list of aggregation functions, allowing multiple summaries in a single pivot table.

Concepts Covered

  • Multiple Aggregations
  • pivot_table()
  • Summary Statistics

5. Python Program to Create a Basic Crosstab

Problem Statement

Write a Python program to display the number of employees in each department based on gender.

Python Solution

import pandas as pd

data = {
    "Department": [
        "IT",
        "IT",
        "HR",
        "HR",
        "IT"
    ],
    "Gender": [
        "Male",
        "Female",
        "Male",
        "Female",
        "Male"
    ]
}

df = pd.DataFrame(data)

result = pd.crosstab(
    df["Department"],
    df["Gender"]
)

print(result)

Sample Output

Gender      Female  Male
Department
HR               1     1
IT               1     2

Explanation

The crosstab() function counts the frequency of combinations between two categorical variables.

Concepts Covered

  • crosstab()
  • Frequency Table
  • Categorical Analysis

6. Python Program to Create a Crosstab with Margins

Problem Statement

Write a Python program to create a crosstab and display row, column, and grand totals.

Python Solution

import pandas as pd

data = {
    "Department": [
        "IT",
        "IT",
        "HR",
        "HR",
        "IT"
    ],
    "Gender": [
        "Male",
        "Female",
        "Male",
        "Female",
        "Male"
    ]
}

df = pd.DataFrame(data)

result = pd.crosstab(
    df["Department"],
    df["Gender"],
    margins=True
)

print(result)

Sample Output

Gender      Female  Male  All
Department
HR               1     1    2
IT               1     2    3
All              2     3    5

Explanation

Setting margins=True adds row totals, column totals, and the overall total.

Concepts Covered

  • crosstab()
  • margins=True
  • Frequency Summary

7. Python Program to Create a Pivot Table with Fill Values

Problem Statement

Write a Python program to replace missing values in a pivot table with 0.

Python Solution

import pandas as pd

data = {
    "Department": [
        "IT",
        "HR",
        "IT"
    ],
    "Gender": [
        "Male",
        "Male",
        "Female"
    ],
    "Marks": [85, 90, 78]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Marks",
    index="Department",
    columns="Gender",
    fill_value=0
)

print(pivot)

Sample Output

Gender      Female  Male
Department
HR               0    90
IT              78    85

Explanation

The fill_value parameter replaces missing values in the pivot table with the specified value.

Concepts Covered

  • pivot_table()
  • fill_value
  • Missing Value Handling

8. Python Program to Create a Pivot Table with Multiple Indexes

Problem Statement

Write a Python program to create a pivot table using Department and Gender as indexes.

Python Solution

import pandas as pd

data = {
    "Department": [
        "IT",
        "IT",
        "HR",
        "HR"
    ],
    "Gender": [
        "Male",
        "Female",
        "Male",
        "Female"
    ],
    "Marks": [85, 78, 90, 88]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Marks",
    index=["Department", "Gender"],
    aggfunc="mean"
)

print(pivot)

Sample Output

                   Marks
Department Gender
HR         Female   88.0
           Male     90.0
IT         Female   78.0
           Male     85.0

Explanation

Using multiple columns in the index parameter creates a hierarchical index.

Concepts Covered

  • Multiple Indexes
  • Hierarchical Index
  • pivot_table()

9. Python Program to Create a Pivot Table with Multiple Values

Problem Statement

Write a Python program to summarize both Sales and Profit using a pivot table.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR"],
    "Sales": [5000, 6000, 4500, 5500],
    "Profit": [1000, 1200, 900, 1100]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values=["Sales", "Profit"],
    index="Department",
    aggfunc="sum"
)

print(pivot)

Sample Output

            Profit  Sales
Department
HR            2300  11500
IT            1900   9500

Explanation

The values parameter accepts multiple columns, allowing multiple summaries in a single pivot table.

Concepts Covered

  • Multiple Values
  • pivot_table()
  • Data Summarization

10. Python Program to Normalize a Crosstab

Problem Statement

Write a Python program to display percentage values instead of counts in a crosstab.

Python Solution

import pandas as pd

data = {
    "Department": [
        "IT",
        "IT",
        "HR",
        "HR",
        "IT"
    ],
    "Gender": [
        "Male",
        "Female",
        "Male",
        "Female",
        "Male"
    ]
}

df = pd.DataFrame(data)

result = pd.crosstab(
    df["Department"],
    df["Gender"],
    normalize="index"
)

print(result)

Sample Output

Gender        Female      Male
Department
HR          0.500000  0.500000
IT          0.333333  0.666667

Explanation

The normalize="index" parameter converts row counts into percentages, making comparisons easier.

Concepts Covered

  • crosstab()
  • normalize
  • Percentage Distribution

11. Python Program to Create a Crosstab with Multiple Columns

Problem Statement

Write a Python program to create a crosstab using Department and Shift against Gender.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "IT", "HR", "HR", "IT"],
    "Shift": ["Morning", "Evening", "Morning", "Evening", "Morning"],
    "Gender": ["Male", "Female", "Male", "Female", "Male"]
}

df = pd.DataFrame(data)

result = pd.crosstab(
    [df["Department"], df["Shift"]],
    df["Gender"]
)

print(result)

Sample Output

Gender              Female  Male
Department Shift
HR         Evening       1     0
           Morning       0     1
IT         Evening       1     0
           Morning       0     2

Explanation

The crosstab() function can use multiple columns to create hierarchical frequency tables.

Concepts Covered

  • crosstab()
  • Multiple Columns
  • Hierarchical Crosstab

12. Python Program to Create a Pivot Table with Margins

Problem Statement

Write a Python program to create a pivot table with row and column totals.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR"],
    "Sales": [5000, 6000, 4500, 5500]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Sales",
    index="Department",
    aggfunc="sum",
    margins=True
)

print(pivot)

Sample Output

            Sales
Department
HR          11500
IT           9500
All         21000

Explanation

The margins=True parameter adds a grand total row named All.

Concepts Covered

  • pivot_table()
  • margins=True
  • Grand Total

13. Python Program to Count Records Using a Pivot Table

Problem Statement

Write a Python program to count the number of employees in each department.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR", "IT"],
    "Employee": ["Rahul", "Aman", "Priya", "Sneha", "Rohit"]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Employee",
    index="Department",
    aggfunc="count"
)

print(pivot)

Sample Output

            Employee
Department
HR                 2
IT                 3

Explanation

Using aggfunc="count" counts the number of records in each group.

Concepts Covered

  • pivot_table()
  • Count
  • Aggregation

14. Python Program to Find the Maximum Value Using a Pivot Table

Problem Statement

Write a Python program to find the highest sales value for each department.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR"],
    "Sales": [5000, 6000, 4500, 5500]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Sales",
    index="Department",
    aggfunc="max"
)

print(pivot)

Sample Output

            Sales
Department
HR           6000
IT           5000

Explanation

The max aggregation function returns the highest value for each group.

Concepts Covered

  • pivot_table()
  • Maximum Value
  • Aggregation

15. Python Program to Find the Minimum Value Using a Pivot Table

Problem Statement

Write a Python program to find the lowest sales value for each department.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR"],
    "Sales": [5000, 6000, 4500, 5500]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Sales",
    index="Department",
    aggfunc="min"
)

print(pivot)

Sample Output

            Sales
Department
HR           5500
IT           4500

Explanation

The min aggregation function returns the smallest value from each group.

Concepts Covered

  • pivot_table()
  • Minimum Value
  • Data Aggregation

16. Python Program to Create a Pivot Table with Multiple Aggregation Functions

Problem Statement

Write a Python program to calculate the sum, average, and maximum sales for each department.

Python Solution

import pandas as pd

data = {
    "Department": ["IT", "HR", "IT", "HR"],
    "Sales": [5000, 6000, 4500, 5500]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Sales",
    index="Department",
    aggfunc=["sum", "mean", "max"]
)

print(pivot)

Sample Output

              sum    mean    max
            Sales   Sales  Sales
Department
HR          11500  5750.0   6000
IT           9500  4750.0   5000

Explanation

Passing multiple aggregation functions to the aggfunc parameter generates multiple summary statistics in a single pivot table.

Concepts Covered

  • pivot_table()
  • Multiple Aggregation Functions
  • Data Summarization

17. Python Program to Analyze Monthly Sales Using a Pivot Table

Problem Statement

Write a Python program to calculate the total monthly sales for each department.

Python Solution

import pandas as pd

data = {
    "Department": [
        "IT",
        "IT",
        "HR",
        "HR",
        "IT"
    ],
    "Month": [
        "January",
        "February",
        "January",
        "February",
        "March"
    ],
    "Sales": [5000, 6000, 4500, 5500, 7000]
}

df = pd.DataFrame(data)

pivot = pd.pivot_table(
    df,
    values="Sales",
    index="Department",
    columns="Month",
    aggfunc="sum",
    fill_value=0
)

print(pivot)

Sample Output

Month       February  January  March
Department
HR               5500     4500      0
IT               6000     5000   7000

Explanation

This pivot table summarizes monthly sales by department. The fill_value=0 parameter replaces missing values with zero, making the report easier to read.

Concepts Covered

  • pivot_table()
  • Monthly Sales Analysis
  • Data Reporting

Chapter Summary

In this chapter, you learned how to summarize and analyze data using Pivot Tables and Crosstabs in Pandas. You created pivot tables with different aggregation functions, worked with multiple indexes and columns, handled missing values using fill_value, generated row and column totals using margins=True, and analyzed categorical data with crosstab(). These techniques are commonly used in business intelligence dashboards, financial reports, sales analysis, HR analytics, and data science projects.

Key Takeaways

  • pivot_table() summarizes data efficiently.
  • crosstab() analyzes relationships between categorical variables.
  • aggfunc supports functions like sum, mean, count, min, and max.
  • Multiple indexes and columns create detailed reports.
  • fill_value replaces missing values.
  • margins=True adds grand totals.
  • normalize converts counts into percentages.
  • Pivot tables simplify reporting and business analysis.
  • Crosstabs help analyze frequencies between categories.
  • Pivot tables are widely used in real-world data analytics.

Frequently Asked Questions (FAQs)

1. What is a Pivot Table in Pandas?

A Pivot Table summarizes data by grouping and applying aggregation functions.

pd.pivot_table(df)

2. What is the purpose of crosstab()?

crosstab() calculates the frequency of combinations between categorical variables.

pd.crosstab(df["Department"], df["Gender"])

3. Which aggregation functions can be used in a Pivot Table?

Common aggregation functions include:

  • sum
  • mean
  • count
  • min
  • max

4. How do you replace missing values in a Pivot Table?

Use the fill_value parameter.

pd.pivot_table(
    df,
    fill_value=0
)

5. What does margins=True do?

It adds row totals, column totals, and an overall grand total.

pd.pivot_table(
    df,
    margins=True
)

6. How do you display percentages in a Crosstab?

Use the normalize parameter.

pd.crosstab(
    df["Department"],
    df["Gender"],
    normalize="index"
)

7. Can a Pivot Table summarize multiple columns?

Yes. Pass multiple column names to the values parameter.

pd.pivot_table(
    df,
    values=["Sales", "Profit"]
)

8. Why are Pivot Tables and Crosstabs important in Pandas?

They simplify complex datasets into easy-to-read summaries, making them essential for business reporting, dashboards, sales analysis, HR analytics, financial reporting, and data science.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Related Posts

Scroll to Top