Pandas pivot_table(): Build and Verify Sales Summaries

pivot_table() groups records, applies an aggregation and arranges the results as a spreadsheet-style summary. Choose the row groups, column groups and measure, then select an aggregation that answers your question. Average units per sale, total units and number of records are different measures.

Create the Sales DataFrame 🔝

Each row is a recorded sale, with a product, store and quantity. Repeated product/store pairs are intentional: the pivot table will aggregate them. This sample contains 20 units across nine records. The sales sample-data page provides related practice data.

import pandas as pd

def sales_data():
    return pd.DataFrame({
        "sale_id": [1, 2, 3, 4, 5, 6, 7, 8, 9],
        "c_id": [2, 2, 1, 4, 2, 3, 2, 3, 2],
        "p_id": [3, 4, 3, 2, 3, 3, 2, 2, 3],
        "product": ["Monitor", "CPU", "Monitor", "RAM", "Monitor", "Monitor", "RAM", "RAM", "Monitor"],
        "qty": [2, 1, 3, 2, 3, 2, 3, 2, 2],
        "store": ["ABC", "DEF", "ABC", "DEF", "ABC", "DEF", "ABC", "DEF", "ABC"]
    })

sales = sales_data()
print(sales)
assert sales["qty"].sum() == 20

Expected output

   sale_id  c_id  p_id  product  qty store
0        1     2     3  Monitor    2   ABC
1        2     2     4      CPU    1   DEF
2        3     1     3  Monitor    3   ABC
3        4     4     2      RAM    2   DEF
4        5     2     3  Monitor    3   ABC
5        6     3     3  Monitor    2   DEF
6        7     2     2      RAM    3   ABC
7        8     3     2      RAM    2   DEF
8        9     2     3  Monitor    2   ABC

Default Aggregation: Mean Quantity per Product 🔝

The default aggfunc is mean. Select values explicitly so IDs are not aggregated accidentally. The mean answers average units per recorded sale, not total units sold. You can call the DataFrame method or the top-level pd.pivot_table function.

sales = sales_data()
report = sales.pivot_table(values="qty", index="product", aggfunc="mean", observed=True)
print(report)
assert report.loc["Monitor", "qty"] == 2.4
assert report.equals(pd.pivot_table(sales, values="qty", index="product",
                                  aggfunc="mean", observed=True))

Expected output

              qty
product          
CPU      1.000000
Monitor  2.400000
RAM      2.333333

Group by Product and Store in the Row Index 🔝

A list of index fields creates a hierarchical row index. Each row identifies one product/store group. Use this form when a long grouped report is clearer than a cross-tab. Monitor at ABC has mean quantity 2.5.

sales = sales_data()
report = sales.pivot_table(values="qty", index=["product", "store"],
                           aggfunc="mean", observed=True)
print(report)
assert report.loc[("Monitor", "ABC"), "qty"] == 2.5

Expected output

               qty
product store     
CPU     DEF    1.0
Monitor ABC    2.5
        DEF    2.0
RAM     ABC    3.0
        DEF    2.0

aggfunc="sum": Calculate Units Sold 🔝

Choose sum() for total quantities. String aggregation names make the intended Pandas operation explicit. This report retains repeated sales and adds them within each group. It is not a row count.

sales = sales_data()
report = sales.pivot_table(values="qty", index=["product", "store"],
                           aggfunc="sum", observed=True)
print(report)
assert report.loc[("Monitor", "ABC"), "qty"] == 10
assert report["qty"].sum() == sales["qty"].sum()

Expected output

               qty
product store     
CPU     DEF      1
Monitor ABC     10
        DEF      2
RAM     ABC      3
        DEF      4

Use columns to Put Stores Across the Table 🔝

index defines rows and columns defines the headings across the report. Without an ABC CPU record, the mean pivot contains NaN at that intersection. A scalar values argument produces simple store columns; a values list can add a column level.

sales = sales_data()
report = sales.pivot_table(index="product", columns="store", values="qty",
                           aggfunc="mean", observed=True)
print(report)
assert pd.isna(report.loc["CPU", "ABC"])
print("Values list gives column levels:")
print(sales.pivot_table(index="product", columns="store", values=["qty"],
                        aggfunc="mean", observed=True))

Expected output

store    ABC  DEF
product          
CPU      NaN  1.0
Monitor  2.5  2.0
RAM      3.0  2.0
Values list gives column levels:
         qty     
store    ABC  DEF
product          
CPU      NaN  1.0
Monitor  2.5  2.0
RAM      3.0  2.0

fill_value: Replace Missing Result Cells after Aggregation 🔝

fill_value operates on the completed pivot, not on the source measurements before aggregation. In this synthetic dataset, an absent product/store sale can be displayed as zero recorded units. That convention would be unsafe if missing records could mean incomplete collection. Keep NaN when the absence is genuinely unknown.

sales = sales_data()
report = sales.pivot_table(index="product", columns="store", values="qty",
                           aggfunc="sum", fill_value=0, observed=True)
print(report)
assert report.loc["CPU", "ABC"] == 0
assert report.to_numpy().sum() == 20

Expected output

store    ABC  DEF
product          
CPU        0    1
Monitor   10    2
RAM        3    4

Add Row and Column Margins 🔝

With margins=True, Pandas applies the chosen aggregation to the margin rows and columns. For sum, these are additive totals. margins_name changes the label. Do not add every displayed cell to check the source total: the margins already repeat the underlying amounts.

sales = sales_data()
report = sales.pivot_table(index="product", columns="store", values="qty",
                           aggfunc="sum", fill_value=0, margins=True,
                           margins_name="Total", observed=True)
print(report)
assert report.loc["Total", "Total"] == 20
assert report.loc["Total", "ABC"] == 13
assert report.loc["Total", "DEF"] == 7

Expected output

store    ABC  DEF  Total
product                 
CPU        0    1      1
Monitor   10    2     12
RAM        3    4      7
Total     13    7     20

A Mean Margin Is Recalculated from the Source 🔝

For aggfunc="mean", the grand margin is the mean of the contributing observations. It is not the sum of means or an unweighted mean of the displayed cells. Groups have different record counts in this dataset, so averaging their displayed means would answer a different question.

sales = sales_data()
report = sales.pivot_table(index="product", columns="store", values="qty",
                           aggfunc="mean", margins=True, observed=True)
print(report.round(3))
print("Source mean:", round(sales["qty"].mean(), 3))
assert abs(report.loc["All", "All"] - 20 / 9) < 1e-12

Expected output

store    ABC   DEF    All
product                  
CPU      NaN  1.00  1.000
Monitor  2.5  2.00  2.400
RAM      3.0  2.00  2.333
All      2.6  1.75  2.222
Source mean: 2.222

Compare Sum, Mean and Count 🔝

A list of aggregation names produces hierarchical columns. count counts non-missing quantity values, while sum adds quantities. With a complete qty column, the count total equals the number of recorded rows; with missing measurements, it can be smaller.

sales = sales_data()
report = sales.pivot_table(index="product", values="qty",
                           aggfunc=["sum", "mean", "count"], observed=True)
print(report)
assert report.loc["Monitor", ("sum", "qty")] == 12
assert report.loc["Monitor", ("count", "qty")] == 5

Expected output

        sum      mean count
        qty       qty   qty
product                    
CPU       1  1.000000     1
Monitor  12  2.400000     5
RAM       7  2.333333     3

pivot_table versus pivot: Repeated Pairs Need Aggregation 🔝

pivot() reshapes existing values and requires a unique value for each index/column pair. It does not aggregate repeated records. pivot_table handles repeated pairs by applying your selected aggregation. A duplicate-key error is a reason to inspect the data, not automatically discard records.

sales = sales_data()
try:
    sales.pivot(index="product", columns="store", values="qty")
except ValueError:
    print("Repeated product/store pairs require an aggregation or data review.")
report = sales.pivot_table(index="product", columns="store", values="qty",
                           aggfunc="sum", observed=True)
print(report)

Expected output

Repeated product/store pairs require an aggregation or data review.
store     ABC  DEF
product           
CPU       NaN  1.0
Monitor  10.0  2.0
RAM       3.0  4.0

Preserve Unknown Measurements in an All-Missing Group 🔝

A plain sum can return zero when every measurement in an observed group is missing. Use a callable with min_count=1 to keep that total unknown. Show counts alongside it when completeness matters. Do not apply fill_value=0 to this report unless the missing-value policy justifies it.

def known_sum(values):
    return values.sum(min_count=1)

data = pd.DataFrame({"product": ["CPU", "CPU", "RAM"],
                     "store": ["ABC", "ABC", "ABC"],
                     "qty": [float("nan"), float("nan"), 3]})
report = data.pivot_table(index="product", columns="store", values="qty",
                          aggfunc=known_sum, dropna=False, observed=True)
print(report)
assert pd.isna(report.loc["CPU", "ABC"])
assert report.loc["RAM", "ABC"] == 3

Expected output

store    ABC
product     
CPU      NaN
RAM      3.0

dropna: Missing Group Keys and Empty Result Columns 🔝

dropna=True removes all-NaN result columns and excludes missing grouping keys. When margins are calculated, missing data can also affect which observations contribute. Decide whether an unknown group should be excluded or explicitly labelled. Here the missing store is labelled Unknown before grouping so the sale stays visible.

sales = sales_data()
sales.loc[0, "store"] = None
sales["store"] = sales["store"].fillna("Unknown")
report = sales.pivot_table(index="product", columns="store", values="qty",
                           aggfunc="sum", fill_value=0, observed=True)
print(report)
assert report.loc["Monitor", "Unknown"] == 2
assert report.to_numpy().sum() == 20

Expected output

store    ABC  DEF  Unknown
product                   
CPU        0    1        0
Monitor    8    2        2
RAM        3    4        0

observed: Control Unused Categorical Groups 🔝

observed applies to categorical grouping fields. True includes observed categories; False can include declared but unused categories. Pandas 3.0 changed the default to True, so specify it when the output schema matters. Use an aggregation such as mean here to make unused categories visible as missing rather than implying recorded units.

sales = sales_data()
sales["product"] = pd.Categorical(sales["product"],
    categories=["CPU", "Monitor", "RAM", "Keyboard"])
observed = sales.pivot_table(index="product", values="qty", aggfunc="mean",
                             observed=True, dropna=False)
all_categories = sales.pivot_table(index="product", values="qty", aggfunc="mean",
                                   observed=False, dropna=False)
print("Observed categories:")
print(observed)
print("Declared categories:")
print(all_categories)
assert "Keyboard" not in observed.index
assert pd.isna(all_categories.loc["Keyboard", "qty"])

Expected output

Observed categories:
              qty
product          
CPU      1.000000
Monitor  2.400000
RAM      2.333333
Declared categories:
               qty
product           
CPU       1.000000
Monitor   2.400000
RAM       2.333333
Keyboard       NaN

Practical Revenue Summary by Category and Channel 🔝

The synthetic amounts below are already calculated revenue values. Use different aggregation rules for different measures: sum revenue, but count order IDs. All category/channel combinations are present, so no missing-cell replacement is required. See groupby() for a long-form alternative.

def revenue_data():
    return pd.DataFrame({
        "order_id": [1001, 1002, 1003, 1004, 1005, 1006],
        "category": ["Stationery", "Office", "Books", "Stationery", "Office", "Books"],
        "channel": ["Online", "Online", "Online", "Store", "Store", "Store"],
        "revenue": [120, 300, 180, 80, 250, 220]
    })

data = revenue_data()
report = data.pivot_table(index="category", columns="channel",
                          values=["revenue", "order_id"],
                          aggfunc={"revenue": "sum", "order_id": "count"},
                          observed=True)
print(report)
assert report["revenue"].to_numpy().sum() == 1150
assert report["order_id"].to_numpy().sum() == 6

Expected output

           order_id       revenue      
channel      Online Store  Online Store
category                               
Books             1     1     180   220
Office            1     1     300   250
Stationery        1     1     120    80

Calculate Channel Shares with a Clear Denominator 🔝

Divide each category row by its own revenue total to compare channel shares. Require positive row totals before division. A percentage table hides category size, so keep the amounts available alongside it. Follow the bar-chart guide to compare these amounts visually.

data = revenue_data()
amounts = data.pivot_table(index="category", columns="channel", values="revenue",
                           aggfunc="sum", observed=True)
row_totals = amounts.sum(axis=1)
assert row_totals.gt(0).all()
shares = amounts.div(row_totals, axis=0).mul(100)
print(shares.round(1))
assert shares.sum(axis=1).round(6).eq(100).all()

Expected output

channel     Online  Store
category                 
Books         45.0   55.0
Office        54.5   45.5
Stationery    60.0   40.0

Export a Pivot Summary to Excel 🔝

A pivot can have meaningful index labels or hierarchical columns. Preserve the category index when exporting this simple revenue table. Use to_excel() and read the workbook back to check totals. Download the runtime file from Colab to keep it.

from pathlib import Path

data = revenue_data()
report = data.pivot_table(index="category", columns="channel", values="revenue",
                          aggfunc="sum", observed=True)
folder = Path("pandas_pivot_practice")
folder.mkdir(exist_ok=True)
path = folder / "category_channels.xlsx"
report.to_excel(path, sheet_name="Revenue", index=True, engine="openpyxl")
restored = pd.read_excel(path, sheet_name="Revenue", index_col=0, engine="openpyxl")
print(restored)
assert restored.to_numpy().sum() == 1150
assert restored.index.tolist() == report.index.tolist()

Expected output

            Online  Store
category                 
Books          180    220
Office         300    250
Stationery     120     80

Common Pivot-Table Problems 🔝

Unexpected averages: set aggfunc explicitly; the default is mean. IDs were totalled: select values and choose a meaningful aggregation for each field. Missing cells: investigate absent combinations versus unknown measurements before filling zero. Grand total does not match: check missing group keys, dropna and whether the report contains margins. Complex columns: several values or aggregation functions produce hierarchical columns. Changing output categories: set observed explicitly for categorical keys. Order is unexpected: sort=True is the default; use sort=False for first-seen ordering or reindex to a defined report order.

Exercise: Quantity Totals by Store 🔝

Build a product-by-store quantity table with sum, fill missing combinations with zero under the sample assumption, and include a margin named Total. Predict the ABC, DEF and grand totals before running the solution: 13, 7 and 20.

Exercise Solution 🔝

Check the grand margin against the original quantity sum, then verify a known product/store group. These checks help detect lost records or an unintended aggregation.

sales = sales_data()
answer = sales.pivot_table(index="product", columns="store", values="qty",
                           aggfunc="sum", fill_value=0, margins=True,
                           margins_name="Total", observed=True)
print(answer)
assert answer.loc["Total", "ABC"] == 13
assert answer.loc["Total", "DEF"] == 7
assert answer.loc["Total", "Total"] == sales["qty"].sum() == 20
assert answer.loc["Monitor", "ABC"] == 10

Expected output

store    ABC  DEF  Total
product                 
CPU        0    1      1
Monitor   10    2     12
RAM        3    4      7
Total     13    7     20

Practice in Google Colab 🔝

Open in Google Colab View on GitHub
All synthetic sample data is included. Run the examples, compare sums and means, and try the exercise. Save a copy in Drive before making changes.

Continue with Data Exploration and Analysis, pivot(), groupby() and missing-value handling. Reference: Pandas pivot_table documentation.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: