Pandas & NumPy Academy · Lesson

pivot_table: Cross-Tabulation

Create Excel-style pivot tables with pd.pivot_table, setting index, columns, values, and aggfunc.

pivot_table: Cross-Tabulation is a free Pandas & NumPy Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Pandas & NumPy Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Is a Pivot Table?

A pivot table is a cross-tabulation that summarises data by two categorical dimensions — one forms the rows, another forms the columns — with an aggregated value in each cell. If you have ever built a pivot table in Excel, you will recognise the concept immediately. Pandas provides pd.pivot_table() (and the DataFrame method version) to create these summaries entirely in Python.

Basic pivot_table Call

The four key parameters of pd.pivot_table() are: values (the column to aggregate), index (which column becomes the rows), columns (which column becomes the columns), and aggfunc (the aggregation function, defaulting to 'mean'). The result is a DataFrame where each cell contains the aggregated value for that row-column combination.

import pandas as pd

df = pd.DataFrame({
    'region':  ['East', 'West', 'East', 'West', 'East', 'West'],
    'product': ['A',    'A',    'B',    'B',    'A',    'B'],
    'revenue': [200,    340,    150,    290,    310,    410]
})

table = pd.pivot_table(df,
                       values='revenue',
                       index='region',
                       columns='product',
                       aggfunc='sum')
print(table)
# product    A    B
# region
# East     510  150
# West     340  700

Choosing the aggfunc

The aggfunc parameter accepts any string name of a NumPy/Pandas aggregation function, a callable, or a list of callables. Common choices: 'sum' for totals, 'mean' for averages, 'count' for frequencies, 'min'/'max' for extremes. When you pass a list, each aggregation gets its own level in the column MultiIndex.

# Using mean (default)
table_mean = pd.pivot_table(df, values='revenue',
                            index='region', columns='product',
                            aggfunc='mean')
print(table_mean.round(1))
# product      A      B
# region
# East     255.0  150.0
# West     340.0  350.0

# Using count
table_count = pd.pivot_table(df, values='revenue',
                             index='region', columns='product',
                             aggfunc='count')
print(table_count)

Handling Missing Cells with fill_value

When a combination of row and column labels has no data, the cell contains NaN by default. Pass fill_value to replace those missing cells with a meaningful default, such as 0 for counts or revenue. This makes the table easier to read and prevents downstream arithmetic operations from silently producing NaN results.

df2 = pd.DataFrame({
    'region':  ['East', 'West', 'East'],
    'product': ['A',    'A',    'B'],
    'revenue': [200,    340,    150]
})

# West-B combination has no data -> NaN by default
table = pd.pivot_table(df2, values='revenue', index='region',
                       columns='product', aggfunc='sum', fill_value=0)
print(table)
# product    A    B
# region
# East     200  150
# West     340    0  <- filled with 0 instead of NaN

Adding Subtotals with margins

Pass margins=True to add row and column totals to the pivot table. Pandas appends an 'All' row at the bottom and an 'All' column on the right, containing the aggregated values across all categories. You can rename the margin label with margins_name. This mirrors the 'Grand Total' row in Excel pivot tables.

table = pd.pivot_table(df, values='revenue', index='region',
                       columns='product', aggfunc='sum',
                       fill_value=0, margins=True, margins_name='Total')
print(table)
# product    A     B  Total
# region
# East     510   150    660
# West     340   700   1040
# Total    850   850   1700

Multiple Values in pivot_table

The values parameter can be a list of column names to aggregate several metrics at once. The result has a column MultiIndex where the outer level is the value column and the inner level is the category. This lets you create a comprehensive summary table in a single call instead of building and merging multiple separate pivot tables.

df['units'] = [10, 15, 8, 12, 14, 18]

table = pd.pivot_table(df,
                       values=['revenue', 'units'],
                       index='region',
                       columns='product',
                       aggfunc='sum')
print(table)
# Columns: (revenue, A), (revenue, B), (units, A), (units, B)
print(table.columns.tolist())

Hierarchical index and columns

When you pass a list to index or columns, the pivot table has a MultiIndex on the corresponding axis. This lets you create more granular summaries — for example, breaking down revenue by region and quarter on the rows, and by product on the columns. Access sub-levels with .loc and index tuples as usual.

df['quarter'] = ['Q1', 'Q1', 'Q2', 'Q2', 'Q1', 'Q2']

table = pd.pivot_table(df, values='revenue',
                       index=['region', 'quarter'],
                       columns='product',
                       aggfunc='sum', fill_value=0)
print(table)
# product         A    B
# region quarter
# East   Q1     510    0
#        Q2       0  150
# West   Q1     340    0
#        Q2       0  700

Flattening the Result

After creating a pivot table, the index is the grouping column and the column MultiIndex (when using multiple values) can be unwieldy. A common clean-up pattern is to call reset_index() to flatten the row index, then collapse the column MultiIndex into a single level by joining the levels with an underscore using a list comprehension.

table = pd.pivot_table(df, values=['revenue', 'units'],
                       index='region', columns='product', aggfunc='sum')

# Flatten MultiIndex columns
table.columns = ['_'.join(str(c) for c in col) for col in table.columns]
table = table.reset_index()
print(table.columns.tolist())
# ['region', 'revenue_A', 'revenue_B', 'units_A', 'units_B']

pivot_table vs groupby().agg()

Both pivot_table and groupby().agg() produce summary statistics. The difference is in the output shape: groupby().agg() returns a long format DataFrame with one row per group combination, while pivot_table returns a wide format table where one dimension becomes columns. For dashboards and reports, the wide format of pivot tables is often more readable; for further machine learning or statistical analysis, long format is preferred.

# Long format (groupby)
long = df.groupby(['region', 'product'])['revenue'].sum().reset_index()
print(long)
# region product  revenue
# East        A      510
# East        B      150

# Wide format (pivot_table)
wide = pd.pivot_table(df, 'revenue', 'region', 'product', 'sum', fill_value=0)
print(wide)
# product    A    B
# region
# East     510  150

Sorting and Styling the Pivot Table

A pivot table is a regular DataFrame, so you can sort it, compute derived columns, and format it exactly like any other Pandas DataFrame. For example, sort by a specific product column descending to rank regions by that product's performance, or add a percentage column by dividing each cell by the row total.

table = pd.pivot_table(df, 'revenue', 'region', 'product', 'sum', fill_value=0)

# Sort by product A revenue descending
table_sorted = table.sort_values('A', ascending=False)
print(table_sorted)

# Add percentage of row total
table['A_pct'] = (table['A'] / table.sum(axis=1) * 100).round(1)
print(table)

Practical Use: Sales Dashboard Table

Pivot tables are a core tool for building management reports and dashboards. A typical workflow: load raw transaction data, create a pivot table with months as columns and product categories as rows, add margin totals, then export to Excel. The entire workflow from raw data to boardroom-ready table takes just a handful of Pandas calls.

# Simulated monthly product revenue pivot
result = pd.pivot_table(
    df,
    values='revenue',
    index='product',
    columns='region',
    aggfunc='sum',
    fill_value=0,
    margins=True,
    margins_name='Grand Total'
)
print(result)
# Export to Excel
# result.to_excel('sales_pivot.xlsx')

Quick Check

Test your understanding of pd.pivot_table from this lesson.

Lesson Recap

In this lesson you learned: pd.pivot_table() creates cross-tabulation summaries with one dimension as rows and another as columns; fill_value handles missing cells and margins=True adds totals; you can aggregate multiple values at once; and pivot tables produce wide-format output ideal for reports. Next up we explore the reverse operation: melt() to convert wide data back to long format.

Frequently asked questions

Is the “pivot_table: Cross-Tabulation” lesson free?

Yes — the full text of “pivot_table: Cross-Tabulation” is free to read here on the web, and the Pandas & NumPy Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Pandas & NumPy Academy course, upgrade to CoddyKit PRO.

What will I learn in “pivot_table: Cross-Tabulation”?

Create Excel-style pivot tables with pd.pivot_table, setting index, columns, values, and aggfunc. You practise Pandas & NumPy Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Pandas & NumPy Academy?

No prior experience is required. Pandas & NumPy Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “pivot_table: Cross-Tabulation” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Pandas & NumPy Academy lesson?

Yes. Every Pandas & NumPy Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Pandas & NumPy Academy