How to create a pivot table with pandas in Python

A pivot table summarises dozens or thousands of records into an easy-to-read grid, crossing two dimensions and aggregating the values at each intersection. In Python, the pandas library does exactly this with a single function, pivot_table. If you have used pivot tables in Excel, you will recognise the idea — except now it lives in code that is reproducible and easy to automate. It is one of the most useful everyday skills for anyone who works with data.

Prerequisites

• Python 3.9 or later installed.
• The pandas library installed (pip install pandas).
• Basic knowledge of a DataFrame: rows, columns and data types.
• A code editor or Jupyter Notebook to run the examples.

Step 1: Prepare the data

We will start from a sample DataFrame of sales. Each row represents one sale and has four columns: the region, the product, the month and the value. In a real project this data would come from a CSV file or a database; to learn the concept, creating it by hand is enough.

import pandas as pd

vendas = pd.DataFrame({
    "regiao":  ["Norte", "Norte", "Sul", "Sul", "Centro", "Norte"],
    "produto": ["A", "B", "A", "B", "A", "A"],
    "mes":     ["Jan", "Jan", "Jan", "Fev", "Fev", "Fev"],
    "valor":   [100, 150, 90, 200, 120, 130],
})

print(vendas)

Step 2: Create your first pivot table

With the data ready, we call pivot_table. There are three essential parameters: index sets what appears in the rows, columns sets what appears in the columns, and values is the numeric column to aggregate. Let's sum the sales by region (rows) and product (columns).

tabela = vendas.pivot_table(
    values="valor",
    index="regiao",
    columns="produto",
    aggfunc="sum",
)

print(tabela)

The result is a grid where each cell shows the total sales of a product in a region. Notice how we went from a long list of sales to a summary with just a few rows — that is the power of a pivot table.

Step 3: Choose the aggregation function

The aggfunc parameter decides how the values in each group are combined. If you do not set it, pandas computes the mean (mean) by default, which often surprises people expecting a sum. You can use "sum", "mean", "count", "min" or "max", depending on the question you want to answer.

media = vendas.pivot_table(
    values="valor",
    index="regiao",
    columns="produto",
    aggfunc="mean",
)

print(media)

Tip: always set aggfunc explicitly. A sum answers "how much did we sell in total"; a mean answers "what is the typical value of each sale".

Step 4: Fill blanks and show totals

When a combination does not exist in the data — the Centro region did not sell product B — the cell shows NaN. The fill_value=0 parameter replaces those blanks with zero, making the table easier to read. With margins=True, pandas also adds a row and a column of totals, which here we name Total. This way you immediately see how each region and each product performed.

tabela = vendas.pivot_table(
    values="valor",
    index="regiao",
    columns="produto",
    aggfunc="sum",
    fill_value=0,
    margins=True,
    margins_name="Total",
)

print(tabela)

Step 5: Cross more than one dimension

A pivot table is not limited to one dimension per axis. If you pass a list to index, you create hierarchical levels: first the region and, within each region, the month. The products stay in the columns.

detalhe = vendas.pivot_table(
    values="valor",
    index=["regiao", "mes"],
    columns="produto",
    aggfunc="sum",
    fill_value=0,
)

print(detalhe)

This layout is excellent for reports, because it lets you move from the overview to the detail without writing loops or complex queries.

Check the result

Run the full script. In the Step 4 table you should see the three regions in the rows, products A and B in the columns, and a row and a column named Total. To confirm everything is correct, sum the valor column of the original DataFrame: it equals 790. That value must match the cell in the bottom-right corner of the table (the grand total). If it matches, your pivot table is correct.

Conclusion

With just a few lines of code you turned loose data into a summary ready for analysis, controlling the rows, the columns, the aggregation and the totals. The pivot table is a tool you will reach for again and again in reports and analyses. The natural next step is to export the result with tabela.to_excel("resumo.xlsx") or build a chart from it. Which dimension will you cross first in your own data?