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.
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.
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.
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)
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
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
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.
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. |
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
valueswithout supplying an appropriateaggfunc. - Misinterpreting normalized proportions as raw counts.
- Forgetting that
margins=Trueadds 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=Truewhen totals are useful. - Choose the correct normalization method for your analysis.
- Use
valuesandaggfuncfor numeric summaries. - Round normalized values for readability when appropriate.
- Inspect the output and verify that the counts match the source data.
15. Revision Questions
- What is cross tabulation?
- What does
pd.crosstab()do by default? - What is the purpose of the
marginsparameter? - How does
normalize="index"differ fromnormalize="columns"? - When should you use the
valuesandaggfuncparameters? - How can you create a cross tabulation table using multiple row variables?
- What is the difference between
crosstab()andgroupby()? - 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.