In this short guide, we will learn how to correctly use margins=True in a pandas pivot_table() so it aggregates both horizontally and vertically — producing a proper "All" row and "All" column. This is a common confusion when building summary tables for sales, finance, or operations data.

1. Why Use pivot_table Margins?

The margins parameter in pivot_table() adds a summary row and column (labeled "All" by default) that aggregates across all values. It's useful when you need:

  • A grand total row and column in a cross-tabulation report
  • Quick row-wise and column-wise summaries in one table
  • Boardroom-style summary tables showing subtotals per category

The problem: many users see only a vertical (row) total and miss the horizontal (column) total — usually because aggfunc or missing values cause one axis to silently drop.

2. Steps to Use pivot_table Margins Correctly

  • Import pandas and create a DataFrame
  • Define index, columns, and values in pivot_table()
  • Set margins=True to enable the "All" row and column
  • Use aggfunc="sum" (or another function) — avoid leaving it as default mean when NaNs are present
  • Use fill_value=0 to replace NaN with 0, ensuring both axes aggregate correctly
  • Display or export the result

3. Example Data

We'll use regional sales data for a retail company — tracking revenue for three brands (Nike, Adidas, Puma) across three US regions.

import pandas as pd

df = pd.DataFrame({
    "Region":  ["North", "North", "South", "South", "West", "West", "North", "South", "West"],
    "Brand":   ["Nike", "Adidas", "Nike", "Puma", "Adidas", "Nike", "Puma", "Adidas", "Puma"],
    "Revenue": [200, 150, 180, 90, 130, 210, 100, 160, 120],
})

4. Example: pivot_table with Full Margins (Rows + Columns)

The key to getting both a totals row and a totals column is combining margins=True with fill_value=0 and an explicit aggfunc. Without fill_value=0, missing brand-region combinations produce NaN, which causes margins to silently skip the horizontal total.

import pandas as pd

df = pd.DataFrame({
    "Region":  ["North","North","South","South","West","West","North","South","West"],
    "Brand":   ["Nike","Adidas","Nike","Puma","Adidas","Nike","Puma","Adidas","Puma"],
    "Revenue": [200, 150, 180, 90, 130, 210, 100, 160, 120],
})

table = pd.pivot_table(df, values="Revenue", index="Region",
                       columns="Brand", aggfunc="sum",
                       fill_value=0, margins=True, margins_name="Total")
print(table)

Output: Result:

Brand   Adidas  Nike  Puma  Total
Region
North      150   200   100    450
South      160   180    90    430
West       130   210   120    460
Total      440   590   310   1340

The table now shows a "Total" column (horizontal sum per region) and a "Total" row (vertical sum per brand) — plus the grand total 1340 in the bottom-right corner.

5. Explanation of the Code

  • index="Region" — each region becomes a row
  • columns="Brand" — each brand becomes a column
  • aggfunc="sum" — sums revenue for each region-brand combination
  • fill_value=0 — replaces NaN (missing combinations) with 0, which is critical for margins to work on both axes
  • margins=True — adds the totals row and column
  • margins_name="Total" — renames the default "All" label to "Total"

6. Bonus Example: Margins with Multiple Aggregations

You can use aggfunc as a dict to show both sum and mean — useful for quarterly performance reports comparing companies like Apple, Microsoft, and Google.

import pandas as pd

df = pd.DataFrame({
    "Quarter": ["Q1","Q1","Q1","Q2","Q2","Q2","Q3","Q3","Q3"],
    "Company": ["Apple","Microsoft","Google","Apple","Microsoft","Google","Apple","Microsoft","Google"],
    "Sales":   [120, 95, 80, 135, 110, 90, 150, 120, 105],
})

table = pd.pivot_table(df, values="Sales", index="Quarter",
                       columns="Company", aggfunc="sum",
                       fill_value=0, margins=True, margins_name="Total")
print(table)

Output: Result:

Company  Apple  Google  Microsoft  Total
Quarter
Q1         120      80         95    295
Q2         135      90        110    335
Q3         150     105        120    375
Total      405     275        325   1005

Both the "Total" row and column are fully populated — showing quarterly totals per company and overall totals per quarter.

7. Customization

Goal Code
Rename "All" label margins_name="Grand Total"
Use mean instead of sum aggfunc="mean"
Keep NaN instead of 0 Remove fill_value=0 (may break column margin)
Multiple value columns values=["Revenue", "Units"]
Percentage of total Divide table by table.loc["Total", "Total"] after creation
Round all values table.round(2)

Pro tip: If your margins column is still missing after adding fill_value=0, check for mixed data types in your value column (e.g., strings mixed with numbers). Run df["Revenue"] = pd.to_numeric(df["Revenue"], errors="coerce") to enforce numeric types before calling pivot_table().

8. Resources