Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Build a Crosstab from Excel Data with pandas

Count how often categories occur together in an Excel sheet with pandas.crosstab — frequency tables, row and column percentages, totals, sums instead of counts, banded numbers, categories with zero counts and missing values that silently drop rows — then write the result to Excel with percentage formats.
Pivots & summariespandas

"How many orders from each channel were refunded?" "What share of complaints in each region were resolved?" Questions like these need a cross-tabulation: one category down the side, another across the top, and a count in every cell. In Excel you would build a pivot table with a count of rows; in pandas, pd.crosstab does it in one call and adds percentages, totals and banding without helper columns. This guide, part of Creating Pivot Tables from Excel Data, builds crosstabs from an Excel sheet and covers the traps that make counts quietly wrong.

From one row per order to a table of counts Eight order rows each carry a channel and a status. The crosstab counts every channel and status combination - Phone has one paid order, Store two paid and one refunded, Web three paid and one refunded - with totals of six paid and two refunded. A B 1 Channel Status 2 Web Paid 3 Web Refunded 4 Store Paid 5 Phone Paid 6 Web Paid 7 Store Paid 8 Store Refunded 9 Web Paid A B C D 1 Channel Paid Refunded Total 2 Phone 1 0 1 3 Store 2 1 3 4 Web 3 1 4 5 Total 6 2 8 before after pd.crosstab

Prerequisites

Bash
pip install pandas openpyxl
Python
import pandas as pd

orders = pd.DataFrame({
    "Channel": ["Web", "Web", "Store", "Phone", "Web", "Store", "Store", "Web"],
    "Status": ["Paid", "Refunded", "Paid", "Paid", "Paid", "Paid", "Refunded", "Paid"],
    "Amount": [50, 20, 300, 80, 65, 120, 40, 95],
    "Rating": [5, 2, 4, None, 4, 5, 1, 3],
})
orders.to_excel("orders.xlsx", index=False)
df = pd.read_excel("orders.xlsx")

Step 1 — Counts with totals

Pass the column for the rows and the column for the columns. margins=True adds totals on both axes:

Python
counts = pd.crosstab(df["Channel"], df["Status"], margins=True, margins_name="Total")
print(counts)

Every cell is the number of rows with that combination, and the combinations that never occur — Phone orders that were refunded — show 0 rather than being left out. That is the main practical difference from groupby(["Channel", "Status"]).size(), which lists only the combinations that exist.

Step 2 — Percentages with normalize

Counts answer "how many"; most crosstab questions are really "what share". The normalize argument divides by the right total:

Python
by_row = pd.crosstab(df["Channel"], df["Status"], normalize="index")
print(by_row.round(3))
# Status    Paid  Refunded
# Phone    1.000     0.000
# Store    0.667     0.333
# Web      0.750     0.250

normalize="index" makes each row add up to 1, answering "what share of Web orders were refunded?" (25%). normalize="columns" makes each column add up to 1, answering "what share of refunds came from Web?" (50%). normalize="all" divides by the grand total. Picking the wrong one produces numbers that look plausible and answer a different question, so name the direction in the sheet title: "Refund rate by channel" for rows, "Refunds by channel" for columns.

Which total each normalize option divides by normalize index divides each cell by its row total and answers what share of a channel's orders had each status. normalize columns divides by the column total and answers what share of each status came from each channel. normalize all divides by the grand total. Divides by Web, Refunded normalize="index" row total 1 of 4 = 25% normalize="columns" column total 1 of 2 = 50% normalize="all" grand total 1 of 8 = 12.5%

Step 3 — Sums and averages instead of counts

To put a measure in the cells rather than a count, pass values and aggfunc. Empty combinations then come back as NaN, because there is nothing to sum — fill them explicitly when zero is the right answer:

Python
revenue = pd.crosstab(df["Channel"], df["Status"], values=df["Amount"], aggfunc="sum").fillna(0)
print(revenue)
# Status    Paid  Refunded
# Phone     80.0       0.0
# Store    420.0      40.0
# Web      210.0      20.0

For an average, leave the gaps as NaN: an average of no orders is not zero. At this point crosstab and pivot_table are interchangeable; Create a Pivot Table from Excel with pandas covers the pivot_table form.

Step 4 — Band numbers before counting

A crosstab of raw amounts would have one column per distinct value. Group numbers into bands with pd.cut first:

Python
bands = pd.cut(df["Amount"], bins=[0, 50, 100, 500], labels=["up to 50", "51-100", "over 100"])
print(pd.crosstab(bands, df["Status"]))
# Status    Paid  Refunded
# Amount
# up to 50     1         2
# 51-100       3         0
# over 100     2         0

The bins are right-inclusive by default, so 50 falls in "up to 50" and 100 in "51-100". State the edges in the labels so readers do not have to guess, and make sure the outer edges cover every value — anything outside the bins becomes NaN and, as the next step shows, drops out of the count.

Step 5 — Do not lose rows to missing values

crosstab silently drops any row where one of the grouping values is missing. The order with no rating disappears from a channel-by-rating table — and with it, the only Phone order:

Python
print(pd.crosstab(df["Channel"], df["Rating"]).index.tolist())         # ['Store', 'Web']
rating = df["Rating"].astype("Int64").astype("string").fillna("No rating")
full = pd.crosstab(df["Channel"], rating, margins=True)
print(full.loc["All", "All"], len(df))                                 # 8 8

Turning the missing values into an explicit label keeps every record in the table, and comparing the grand total with the row count is a one-line check that nothing went missing. Make that check part of every crosstab you publish. The same trap applies to the amount bands from Step 4: a negative amount or a refund above 500 falls outside the bins, becomes NaN and vanishes from the table without any warning.

Step 6 — Show categories with no records

A table that should always list Paid, Refunded and Cancelled loses the Cancelled column in a month with no cancellations. Declare the categories once and pass dropna=False:

Python
status = pd.Categorical(df["Status"], categories=["Paid", "Refunded", "Cancelled"])
fixed = pd.crosstab(df["Channel"], status, dropna=False)
print(fixed.columns.tolist())      # ['Paid', 'Refunded', 'Cancelled']

Fixed categories keep the layout identical from month to month, so formulas and charts that point at the sheet do not break, and the column order follows the business order rather than the alphabet.

Two ways a crosstab quietly changes shape A missing value in a grouping column removes the whole record from the table, so totals fall short of the row count. A category with no records this month disappears from the columns. Labelling missing values and declaring categories keeps both totals and layout stable. Missing grouping values record dropped silently total below row count fix: fill with Unknown check: All equals len(df) Categories with no rows column disappears charts and formulas break fix: pd.Categorical pass dropna=False

Step 7 — Two variables down the side

Pass a list of columns to either axis to nest them — channel and amount band down the side, for example — which answers "is the refund pattern different for small web orders?" in one table:

Python
nested = pd.crosstab([df["Channel"], bands.rename("Band")], df["Status"])
print(nested)
flat = nested.reset_index()
flat.columns.name = None
print(flat.columns.tolist())      # ['Channel', 'Band', 'Paid', 'Refunded']
print(len(flat))                  # 5 — only the combinations that occur

Nested crosstabs list only the row combinations that actually occur, so a channel with no large orders simply has no "over 100" row. That keeps the table short, but it also means two months can have different rows; if the layout must stay fixed, make both columns categorical, as in Step 6. Resetting the index turns the nested labels into ordinary columns, which is the easiest shape to filter in Excel and to join with other tables. Keep nesting to two levels in a report; beyond that, a long table with a filter is easier to read than a crosstab.

Step 8 — Write counts and percentages to Excel

Write the counts and the row percentages side by side, and let Excel format the percentages instead of multiplying by 100 in pandas:

Python
from openpyxl import load_workbook

shares = pd.crosstab(df["Channel"], df["Status"], normalize="index")
with pd.ExcelWriter("crosstab.xlsx") as writer:
    counts.to_excel(writer, sheet_name="Orders by status", startrow=1)
    shares.to_excel(writer, sheet_name="Orders by status", startrow=1, startcol=5)

wb = load_workbook("crosstab.xlsx")
ws = wb["Orders by status"]
ws["A1"], ws["F1"] = "Order count", "Share of channel's orders"
for row in ws.iter_rows(min_row=3, min_col=7, max_col=8):
    for cell in row:
        cell.number_format = "0.0%"
wb.save("crosstab.xlsx")
print(ws["G5"].value, ws["G5"].number_format)     # 0.75 0.0%

Keeping the stored values as fractions means anyone who builds on the sheet can use them in formulas directly, while readers see 75.0%. The startrow and startcol offsets leave room for a title above each table.

Common pitfalls

  • Normalising a table that has margins. With margins=True and normalize, the All row and column are proportions too; label them clearly or add totals after normalising.
  • Counting text that differs only in spelling. "web", "Web " and "WEB" become three rows. Normalise the text first — see Strip Whitespace and Normalise Text Columns with pandas.
  • Comparing crosstabs from different months by position. Row and column order follow the data unless the categories are fixed; align tables by label with reindex before subtracting one from another.
  • Averaging percentages across rows. A mean of row shares ignores how many orders each row has. Recompute from counts.

Conclusion

pd.crosstab builds frequency tables straight from Excel columns, fills impossible combinations with zero and adds totals with margins=True. Choose normalize by the question you are answering, fill gaps explicitly when using values and aggfunc, band numbers with pd.cut, label missing values so no record is dropped, and declare categories so the layout stays stable. Write fractions and format them as percentages in Excel.

Frequently asked questions

What is the difference between crosstab and pivot_table? crosstab starts from separate columns and counts combinations by default, which suits frequency tables. pivot_table starts from a DataFrame and aggregates a value column. With values and aggfunc, crosstab can aggregate too, so the choice is mostly about which reads better.

How do I show percentages instead of counts? Pass normalize="index" for percentages within each row, normalize="columns" for each column, or normalize="all" for shares of the whole table. Multiply by 100 or apply an Excel percentage format when writing.

Why did some rows disappear from my crosstab? crosstab drops any record with a missing value in one of the grouping columns. Fill missing values with a label such as "Unknown" before building the table so every record is counted.

How do I include categories that have no records? Convert the column to a pandas Categorical with the full list of categories and pass dropna=False. Categories with no matching rows then appear with a count of zero.

More on pivots & summaries

More on pivots & summaries →