Summarizing and Cross-Tabulating Categorical Data in pandas: value_counts, crosstab, and Percentage Breakdowns

Introduction

Picture this: you've just loaded a customer survey dataset into pandas. You've got 50,000 rows with columns like region, product_category, satisfaction_rating, and churned. Your manager wants to know: Which product categories have the highest churn rate? Does satisfaction differ by region? You could stare at those 50,000 rows, or you could summarize them intelligently in about five lines of code.

That's exactly what this lesson is about. Categorical data — data that falls into distinct groups or labels, like "North," "South," "Electronics," "Satisfied" — is everywhere in real-world analysis. Before you can visualize it or model it, you need to be able to count it, compare it across groups, and express those counts as percentages that tell a story. pandas gives you two workhorses for exactly this: value_counts() and pd.crosstab(). By the end of this lesson, you'll use both fluently.

What you'll learn:

• How value_counts() works and how to customize it with normalization, sorting, and binning
• How to build cross-tabulations (contingency tables) with pd.crosstab() — the pandas equivalent of a two-way pivot table
• How to add row and column totals (margins) to cross-tabs
• How to convert raw counts to percentage breakdowns for meaningful comparison
• How to combine these tools to answer multi-dimensional categorical questions

---

Prerequisites

You should be comfortable loading a DataFrame and understanding its basic structure. If you haven't done that yet, start with Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data. You should also know how to filter rows — the Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks lesson covers that thoroughly.

---

Setting Up: Our Working Dataset

Throughout this lesson we'll build a realistic customer dataset directly in Python so you can follow along without downloading a file. Run this in a Jupyter notebook or script:

import pandas as pd
import numpy as np

np.random.seed(42)

n = 2000

df = pd.DataFrame({
    "customer_id": range(1, n + 1),
    "region": np.random.choice(["Northeast", "Southeast", "Midwest", "West"], size=n,
                                p=[0.30, 0.25, 0.20, 0.25]),
    "product_category": np.random.choice(["Electronics", "Apparel", "Home & Garden", "Sports"],
                                          size=n, p=[0.35, 0.30, 0.20, 0.15]),
    "satisfaction": np.random.choice(["Very Satisfied", "Satisfied", "Neutral",
                                       "Dissatisfied", "Very Dissatisfied"],
                                      size=n, p=[0.25, 0.35, 0.20, 0.12, 0.08]),
    "churned": np.random.choice([True, False], size=n, p=[0.22, 0.78]),
    "tenure_years": np.random.randint(1, 11, size=n),
})

print(df.shape)
print(df.head())

You should see a 2000-row DataFrame with a mix of categorical string columns, a boolean column, and a numeric column. This is the kind of dataset that lands in your lap from a CRM export or a survey tool output.

---

Counting Categories with value_counts()

value_counts() is the simplest entry point into categorical summarization. Call it on any Series (a single column) and it returns a count of each unique value, sorted from most to least common by default.

df["region"].value_counts()

Output:

Northeast    612
West         510
Southeast    494
Midwest      384
Name: region, dtype: int64

That's it. You immediately know Northeast is your largest region and Midwest is your smallest. This is your go-to check whenever you encounter a new categorical column — run value_counts() before anything else to understand what you're working with.

Sorting and Displaying All Values

By default, results are sorted by count descending. If you want alphabetical order (useful when categories have a natural order you want to preserve in output), pass sort=False:

df["satisfaction"].value_counts(sort=False)

This is helpful when you want to eyeball the data in a logical sequence rather than by frequency.

Normalizing to Proportions

Raw counts are fine, but percentages are usually more meaningful — especially when you're comparing across groups of different sizes. Pass normalize=True and pandas returns proportions instead of counts:

df["satisfaction"].value_counts(normalize=True)

Output:

Satisfied           0.353
Very Satisfied      0.254
Neutral             0.196
Dissatisfied        0.122
Very Dissatisfied   0.075
Name: satisfaction, dtype: float64

To get clean percentages, multiply by 100 and round:

(df["satisfaction"].value_counts(normalize=True) * 100).round(1)

Now you have a percentage distribution. 35.3% of customers are "Satisfied" — that's a number you can put in a slide.

Handling Missing Values

By default, value_counts() excludes NaN values from the count. If your column might have missing data (and after loading real-world data, it often does), pass dropna=False to include them:

df["region"].value_counts(dropna=False)

This ensures you don't silently miss the fact that 15% of your records have no region assigned — which is exactly the kind of thing that bites you in a presentation. For more on handling missing data systematically, see Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types.

Binning a Numeric Column with value_counts()

Here's a less-known trick: value_counts() works on numeric columns too, if you pass bins=. This is useful when you want to treat a continuous variable like tenure_years as a categorical one:

df["tenure_years"].value_counts(bins=4, sort=False)

Output (approximate):

(0.991, 3.25]    482
(3.25, 5.5]      521
(5.5, 7.75]      498
(7.75, 10.0]     499
Name: tenure_years, dtype: int64

pandas automatically creates equal-width bins and counts how many values fall into each. This is a quick way to understand distribution without reaching for a histogram.

---

Building Cross-Tabulations with pd.crosstab()

value_counts() shows you one column at a time. But the interesting questions are almost always relational: How does satisfaction break down by region? Does churn rate differ by product category? For that, you need a cross-tabulation — a table that shows counts (or percentages) across two or more categorical variables simultaneously.

In Excel, you'd reach for a PivotTable. In pandas, you use pd.crosstab().

Basic Syntax

pd.crosstab(df["region"], df["product_category"])

Output:

product_category  Apparel  Electronics  Home & Garden  Sports
region                                                        
Midwest               119          133             77      55
Northeast             181          215            130      86
Southeast             156          176             97      65
West                  147          173            101      89

The rows are your first argument (index), the columns are your second argument. Each cell contains the count of rows where that row-column combination occurs. This is called a contingency table — a standard tool in statistics and business analysis for understanding how two categorical variables relate to each other.

Adding Totals with margins

A cross-tab without totals is like a spreadsheet without subtotals — useful, but incomplete. Add row and column totals with margins=True:

pd.crosstab(df["region"], df["product_category"], margins=True)

Output:

product_category  Apparel  Electronics  Home & Garden  Sports   All
region                                                              
Midwest               119          133             77      55   384
Northeast             181          215            130      86   612
Southeast             156          176             97      65   494
West                  147          173            101      89   510
All                   603          697            405     295  2000

The All row and column show totals. You can rename that label from "All" to something more descriptive:

pd.crosstab(df["region"], df["product_category"], margins=True, margins_name="Total")

Now it reads cleanly in a report.

Cross-Tabulating with a Boolean Column

Boolean columns like churned work perfectly in pd.crosstab():

pd.crosstab(df["product_category"], df["churned"])

Output:

churned         False  True 
product_category              
Apparel           472   131
Electronics       545   152
Home & Garden     316    89
Sports            229    66

This tells you raw churn counts by product category — but counts alone are misleading if the categories have different sizes. A product with 152 churned customers might still have a lower churn rate than one with 89, if it also has far more total customers.

---

Percentage Breakdowns: The Real Power Move

Raw counts answer "how many." Percentages answer "compared to what." Most analytical questions are about comparison, so you need to learn how to normalize cross-tabs.

normalize Parameter in crosstab

pd.crosstab() has a built-in normalize parameter that takes three values:

• normalize="index" — percentages within each row (each row sums to 1.0)
• normalize="columns" — percentages within each column (each column sums to 1.0)
• normalize=True (or "all") — percentages of the grand total (everything sums to 1.0)

The most common use case is normalize="index" — it answers "within each row category, what's the breakdown across columns?"

pd.crosstab(df["product_category"], df["churned"], normalize="index").round(3) * 100

Output:

churned          False   True
product_category              
Apparel           78.3   21.7
Electronics       78.2   21.8
Home & Garden     78.0   22.0
Sports            77.6   22.4

Now you can compare churn rates across product categories on equal footing. Sports has the highest churn rate (22.4%), Apparel the lowest (21.7%) — though with a random dataset like ours, these differences aren't statistically meaningful. In real data, this kind of breakdown often reveals significant patterns.

Formatting Percentages for Readability

The output above multiplies by 100 but still shows decimal points. For a clean report-ready table:

churn_pct = pd.crosstab(df["product_category"], df["churned"], normalize="index") * 100
churn_pct = churn_pct.round(1)
churn_pct.columns = ["Retained (%)", "Churned (%)"]
churn_pct

Output:

                  Retained (%)  Churned (%)
product_category                           
Apparel                   78.3         21.7
Electronics               78.2         21.8
Home & Garden             78.0         22.0
Sports                    77.6         22.4

That's a table you could paste directly into a report. Renaming the boolean column values (False → "Retained (%)", True → "Churned (%)") makes the output immediately understandable to anyone reading it.

Combining Counts and Percentages Side by Side

Sometimes you want both counts and percentages in the same view — the count gives context to the percentage. Here's a practical pattern:

counts = pd.crosstab(df["region"], df["satisfaction"])
pcts = pd.crosstab(df["region"], df["satisfaction"], normalize="index") * 100

# Build a combined view for the "Very Dissatisfied" column only
summary = pd.DataFrame({
    "Count": counts["Very Dissatisfied"],
    "Pct (%)": pcts["Very Dissatisfied"].round(1)
})
summary

Output:

           Count  Pct (%)
region                   
Midwest       30      7.8
Northeast     45      7.4
Southeast     39      7.9
West          36      7.1

This is a focused view answering: which region has the highest proportion of "Very Dissatisfied" customers? Southeast edges out with 7.9%, though again the differences here are small because we used random data.

---

Multi-Level Cross-Tabulations

Real analysis often requires breaking data down across three variables. pd.crosstab() supports multiple row and column variables by passing lists.

pd.crosstab(
    index=[df["region"], df["churned"]],
    columns=df["product_category"]
)

This creates a table with a two-level row index (region and churn status) and product categories across the columns. The result is a MultiIndex table — each region has a False (not churned) and True (churned) row.

For deeper work with MultiIndex structures, the lesson on Reshaping and Analyzing Multi-Level Data in pandas: Working with MultiIndex Columns and Rows After groupby and pivot_table covers how to navigate and flatten them.

---

crosstab vs. groupby: When to Use Which

You might wonder: can't I do all of this with groupby? Yes — but crosstab() is more convenient for contingency tables, while groupby() is more flexible for custom aggregations.

Use pd.crosstab() when:

• You want a count-based (or count-normalized) matrix of two or more categorical variables
• You need a publication-ready contingency table quickly
• You're computing churn rates, conversion rates, or response distributions by segment

Use groupby() when:

• You want to aggregate something other than counts (mean spend, total revenue, max tenure)
• You need to apply multiple different aggregation functions at once

For a thorough treatment of groupby, see Grouping and Aggregating in pandas: groupby as the PivotTable Replacement. And if you want a full pivot table (which can aggregate any numeric column), check out Reshaping Data with pivot_table, melt, and stack in pandas.

---

Hands-On Exercise

Work through these tasks using the dataset we built at the start of the lesson. Try each one before checking the solution approach.

Task 1: Find the percentage breakdown of satisfaction levels. Which single satisfaction level represents the largest share of customers?

# Your code here
df["satisfaction"].value_counts(normalize=True).mul(100).round(1)

Task 2: Build a cross-tab of region vs satisfaction. Normalize by row so each region sums to 100%. Which region has the highest proportion of "Very Satisfied" customers?

pd.crosstab(df["region"], df["satisfaction"], normalize="index").mul(100).round(1)

Task 3: Use value_counts(bins=3) on tenure_years to split customers into three tenure groups. What proportion of customers fall in the longest-tenure bin?

df["tenure_years"].value_counts(bins=3, normalize=True, sort=False).mul(100).round(1)

Task 4 (stretch): Build a cross-tab showing churn rate (as a percentage) broken down by both region and product category. Use normalize="index" and display only the True (churned) column. Rename that column to "Churn Rate (%)".

result = pd.crosstab(
    index=[df["region"], df["product_category"]],
    columns=df["churned"],
    normalize="index"
).mul(100).round(1)

result = result[[True]].rename(columns={True: "Churn Rate (%)"})
result

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting that normalize gives proportions, not percentages

normalize=True or normalize="index" returns values between 0 and 1. If you're reporting "21.7%", you need to multiply by 100. Failing to do so leads to reporting "0.217%" — a number that will confuse everyone in the room.

Mistake 2: Using the wrong normalize direction

normalize="index" makes each row sum to 100%. normalize="columns" makes each column sum to 100%. Choose based on your analytical question. "What percentage of Northeast customers bought Electronics?" requires normalize="index". "What percentage of Electronics buyers are from the Northeast?" requires normalize="columns".

Mistake 3: Not handling NaN values

If your categorical columns have missing values, pd.crosstab() silently excludes them by default. This can make your totals look smaller than expected. Pass dropna=False to include NaN as its own category:

pd.crosstab(df["region"], df["product_category"], dropna=False)

Mistake 4: Confusing pd.crosstab() with DataFrame.pivot_table()

Both create two-dimensional summaries. The key difference: crosstab() counts rows by default and is designed for categorical comparisons. pivot_table() aggregates a numeric column and is more flexible. If you find yourself writing pd.crosstab(..., aggfunc=...) to aggregate something other than counts, switch to pivot_table() — it's the right tool for that job.

Mistake 5: Calling value_counts() on the DataFrame instead of a Series

value_counts() is a Series method. It works on one column at a time. df.value_counts() does exist (it counts unique combinations of all columns), but that's almost never what beginners intend. Always specify the column: df["column_name"].value_counts().

---

Summary & Next Steps

You now have the core tools for categorical summarization in pandas:

• value_counts() — count or proportionally rank the values in a single column, with options for sorting, normalization, NaN inclusion, and binning
• pd.crosstab() — build two-dimensional (or multi-dimensional) contingency tables with built-in normalization for percentage breakdowns
• normalize parameter — controls whether you see raw counts, row percentages, column percentages, or grand total percentages
• margins=True — adds row and column totals to any cross-tab

These tools answer the "who has what, and what percentage does that represent?" questions that come up in nearly every analytical project. They're especially powerful during the early exploration phase — running value_counts() on every categorical column and pd.crosstab() on your key variable pairs gives you a comprehensive picture of your data's structure in minutes.

Where to go next:

Once you can summarize categorical data, the natural next step is visualizing it. Bar charts, stacked bar charts, and heatmaps are the visual counterparts to the tables you've built here — the lesson on Visualizing Data with matplotlib and seaborn: Charts That Explain Your Analysis shows you how to turn these DataFrames directly into charts.

If your analysis involves more complex aggregations beyond counts — like average order value by region and category — revisit Grouping and Aggregating in pandas: groupby as the PivotTable Replacement to add that capability to your toolkit.

And when you're ready to share your cross-tab results as formatted Excel reports, Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work shows you how to style and export them professionally.