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

Cross Tabulation in Pandas

Learn how to summarize categorical data using the Pandas crosstab() function. Explore frequency tables, row and column totals, normalized percentages, multiple categories, and practical applications in data analysis.

1. Introduction to Cross Tabulation

Cross tabulation is a technique used to summarize the relationship between two or more categorical variables. It displays how often combinations of category values occur in a dataset.

In Pandas, the pd.crosstab() function creates a frequency table that counts the occurrences of combinations between categories. It is useful for analyzing survey responses, student results, customer segments, product preferences, and business data.

Key Concepts

  • Rows: Represent categories from one variable.
  • Columns: Represent categories from another variable.
  • Frequency: The number of observations for a category combination.
  • Margins: Optional row and column totals.
  • Normalization: Converts frequency counts into proportions or percentages.
Remember: Cross tabulation is especially useful when you want to understand how categorical variables are distributed together rather than examining each variable separately.

2. Understanding a Cross Tabulation Table

Consider a survey containing information about customers' preferred products and their membership status. A cross tabulation table can show how many customers in each membership group prefer each product.

Product Preference by Membership Membership Laptop Phone Tablet Total Gold 12 18 10 40 Silver 8 15 7 30 Total 20 33 17 70 Each cell shows the frequency of a category combination.

A cross tabulation table summarizes category combinations and can include row and column totals.

3. The pd.crosstab() Function

Pandas provides the pd.crosstab() function to create cross tabulation tables.

pd.crosstab(
    index,
    columns,
    values=None,
    rownames=None,
    colnames=None,
    aggfunc=None,
    margins=False,
    margins_name="All",
    dropna=True,
    normalize=False
)

Important Parameters

Parameter Description
index One or more arrays or Series used to create row categories.
columns One or more arrays or Series used to create column categories.
values Optional values to aggregate instead of simply counting observations.
aggfunc Aggregation function used when values is provided, such as sum or mean.
rownames Optional names for the row index levels.
colnames Optional names for the column levels.
margins If True, adds row and column totals.
margins_name Sets the label used for the totals, such as All.
normalize Normalizes counts by rows, columns, or the complete table.
dropna Controls handling of all-NaN columns in applicable crosstab operations.

4. Creating a Sample DataFrame

First, create a dataset containing student names, subjects, scores, and class groups. Each row represents one student-subject observation.

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

The Subject and Class columns contain categorical information. We can use them to examine how observations are distributed across subjects and classes.

5. Basic Cross Tabulation

To count how many observations belong to each combination of class and subject, pass the class Series as the index and the subject Series as the columns.

result = pd.crosstab(
    df["Class"],
    df["Subject"]
)

print(result)

Expected Output

Subject  Math  Science
Class
A           2        2
B           1        1

Class A contains two Math observations and two Science observations. Class B contains one observation for each subject.

Remember: When values and aggfunc are not supplied, crosstab() counts the occurrences of each category combination.

6. Adding Row and Column Totals

Use margins=True to add totals for each row and column. The default total label is All.

result = pd.crosstab(
    df["Class"],
    df["Subject"],
    margins=True
)

print(result)

Expected Output

Subject  Math  Science  All
Class
A           2        2    4
B           1        1    2
All         3        3    6

The All row displays the total count for each subject. The All column displays the total number of observations in each class.

You can customize the total label using margins_name.

result = pd.crosstab(
    df["Class"],
    df["Subject"],
    margins=True,
    margins_name="Total"
)

print(result)
Practical use: Totals make it easier to compare category counts and verify the overall number of observations.

7. Normalizing Cross Tabulation Results

A frequency table displays counts. Normalization converts those counts into proportions so that distributions can be compared more easily.

7.1 Normalize the Entire Table

Set normalize="all" to divide every count by the total number of observations.

result = pd.crosstab(
    df["Class"],
    df["Subject"],
    normalize="all"
)

print(result.round(2))

Expected Output

Subject  Math  Science
Class
A          0.33     0.33
B          0.17     0.17

Each value represents the proportion of the complete dataset belonging to a particular class-subject combination.

7.2 Normalize by Row

Set normalize="index" to divide each count by its row total. Each row will sum to 1 when its total is nonzero.

result = pd.crosstab(
    df["Class"],
    df["Subject"],
    normalize="index"
)

print(result.round(2))

Expected Output

Subject  Math  Science
Class
A          0.50     0.50
B          0.50     0.50

7.3 Normalize by Column

Set normalize="columns" to divide each count by its column total. Each nonempty column will sum to 1.

result = pd.crosstab(
    df["Class"],
    df["Subject"],
    normalize="columns"
)

print(result.round(2))

Expected Output

Subject  Math  Science
Class
A          0.67     0.67
B          0.33     0.33
Important: Normalized results are proportions between 0 and 1. Multiply them by 100 when you want percentages. For example, 0.67 represents approximately 67%.

8. Working with Multiple Categorical Variables

The index and columns parameters can accept multiple arrays or Series. This allows cross tabulation across more than two categorical variables.

result = pd.crosstab(
    [df["Class"], df["Student"]],
    df["Subject"]
)

print(result)

Expected Output

Subject            Math  Science
Class Student
A     Pradnya         1        1
      Samadhan        1        1
B     Samiksha        1        1

The result uses a MultiIndex for the rows. The first level identifies the class, and the second identifies the student.

Each student has one observation for Math and one for Science, so the corresponding counts are 1 in this example.

9. Renaming Row and Column Labels

Use rownames and colnames to provide descriptive names for the row and column index levels.

result = pd.crosstab(
    df["Class"],
    df["Subject"],
    rownames=["Student Class"],
    colnames=["Exam Subject"]
)

print(result)

Expected Output

Exam Subject   Math  Science
Student Class
A                  2        2
B                  1        1

Descriptive labels make tables easier to understand when they are displayed in reports or shared with other analysts.

10. Aggregating Numeric Values with crosstab()

By default, crosstab() counts observations. You can also summarize numeric values by supplying the values parameter together with an aggfunc.

For example, calculate the total score for each class and subject.

result = pd.crosstab(
    df["Class"],
    df["Subject"],
    values=df["Score"],
    aggfunc="sum"
)

print(result)

Expected Output

Subject  Math  Science
Class
A          177      178
B           78       95

Class A has a combined Math score of 177 and a combined Science score of 178. Class B has totals of 78 and 95 respectively.

You can use other aggregation functions, such as "mean", to calculate averages rather than sums.

average_scores = pd.crosstab(
    df["Class"],
    df["Subject"],
    values=df["Score"],
    aggfunc="mean"
)

print(average_scores.round(2))

Expected Output

Subject  Math  Science
Class
A         88.5     89.0
B         78.0     95.0
Note: When using values, specify a suitable aggregation function. Without one, Pandas cannot determine how to summarize multiple numeric values for the same category combination.

11. Cross Tabulation with Survey Data

Cross tabulation is commonly used to analyze survey responses. Consider a dataset containing customer membership types and preferred payment methods.

customers = pd.DataFrame({
    "Membership": [
        "Gold", "Gold", "Silver",
        "Gold", "Silver", "Silver",
        "Gold", "Silver"
    ],
    "Payment": [
        "Card", "Cash", "Card",
        "Card", "Cash", "Card",
        "Cash", "Cash"
    ]
})

result = pd.crosstab(
    customers["Membership"],
    customers["Payment"],
    margins=True
)

print(result)

Expected Output

Payment     Card  Cash  All
Membership
Gold           2     2    4
Silver         2     2    4
All            4     4    8

The table shows that four customers belong to each membership group. Within each group, two customers prefer card payments and two prefer cash.

The same approach can be applied to survey responses, customer satisfaction categories, purchase preferences, and other categorical data.

12. Understanding Missing and Unused Categories

Categorical datasets can contain missing values or categories that do not appear in the observed rows. The resulting cross tabulation depends on the input data types and the options used.

data = pd.DataFrame({
    "Department": ["IT", "HR", "IT", "Finance"],
    "Status": ["Active", "Active", "Inactive", "Active"]
})

result = pd.crosstab(
    data["Department"],
    data["Status"]
)

print(result)

Expected Output

Status      Active  Inactive
Department
Finance          1         0
HR               1         0
IT               1         1

Only department-status combinations represented in the data contribute to the counts. Combinations without observations are shown as zero in the displayed table.

When working with Pandas categorical data, inspect the categories and the behavior of the dropna parameter if you need unused categories to appear in the result.

Best practice: Check missing values before creating the table, especially when missing categories have an important meaning in the analysis.

13. Cross Tabulation vs. GroupBy

Both crosstab() and groupby() can summarize data, but they are often used for different purposes.

Feature crosstab() groupby()
Main purpose Builds frequency tables for combinations of categories. Groups records and applies aggregation functions.
Typical result A table with categories arranged as rows and columns. A Series or DataFrame grouped by one or more keys.
Frequency counts Available by default. Can be calculated using methods such as size().
Numeric aggregation Supported using values and aggfunc. Supported using methods such as sum() and mean().
Best suited for Comparing combinations of categorical variables. Flexible aggregation and analysis of grouped records.
Tip: Use crosstab() when you want a category-by-category summary table. Use groupby() when you need more flexible grouping and aggregation.

14. Common Mistakes and Best Practices

Common Mistakes

  • Confusing frequency counts with numeric sums or averages.
  • Using values without supplying an appropriate aggfunc.
  • Misinterpreting normalized proportions as raw counts.
  • Forgetting that margins=True adds totals to the table.
  • Ignoring missing or unused categories.
  • Using unclear row and column labels in reports.

Best Practices

  • Identify which categorical variables belong on the rows and columns.
  • Use margins=True when totals are useful.
  • Choose the correct normalization method for your analysis.
  • Use values and aggfunc for numeric summaries.
  • Round normalized values for readability when appropriate.
  • Inspect the output and verify that the counts match the source data.

15. Revision Questions

  1. What is cross tabulation?
  2. What does pd.crosstab() do by default?
  3. What is the purpose of the margins parameter?
  4. How does normalize="index" differ from normalize="columns"?
  5. When should you use the values and aggfunc parameters?
  6. How can you create a cross tabulation table using multiple row variables?
  7. What is the difference between crosstab() and groupby()?
  8. Why should missing values be checked before interpreting a frequency table?

16. Practical Exercises

Exercise 1: Student Results

Create a DataFrame with student classes and subject names. Use pd.crosstab() to count how many students belong to each class-subject combination.

Goal: Create a basic frequency table.

Exercise 2: Add Totals

Extend Exercise 1 by adding row and column totals. Change the default total label from All to Total.

Goal: Practice margins and margins_name.

Exercise 3: Compare Percentages

Normalize the frequency table by rows and then by columns. Compare the results and explain what each percentage represents.

Goal: Understand normalized cross tabulation.

Exercise 4: Analyze Sales

Create a DataFrame containing product categories, sales regions, and sales amounts. Use values and aggfunc to calculate total sales for each category-region combination.

Goal: Build a numeric summary using cross tabulation.

Summary

The Pandas pd.crosstab() function creates frequency tables that summarize combinations of categorical variables. By using index and columns, you can organize categories into a readable table. The margins option adds totals, while normalize converts counts into proportions. When numeric values need to be summarized, use values with an appropriate aggfunc. Cross tabulation is a useful technique for analyzing survey responses, student performance, customer behavior, and business categories.