How to Add Row Subtotals in a Pandas Pivot Table When Aggregating Multiple Columns

Use margins=True in pd.pivot_table() and provide an aggregation dictionary mapping each value column to its specific function to generate row subtotals for every aggregated column.

The pandas-dev/pandas repository provides comprehensive pivot table functionality through the DataFrame.pivot_table method. When analyzing data across multiple dimensions with several value columns, you need explicit configuration to generate accurate row subtotals. This guide explains how to leverage the margins parameter and aggregation dictionaries to achieve this.

Enabling Row Subtotals with the margins Parameter

The margins parameter controls subtotal calculation in pandas pivot tables. When set to True, pandas computes a grand total row (and column) and appends it to the result. According to the source code in pandas/core/reshape/pivot.py, this triggers the internal _add_margins helper function to compute and stitch the totals into the final DataFrame.

By default, the subtotal row appears with the label "All". You can customize this using the margins_name parameter (e.g., margins_name="Total").

Aggregating Multiple Columns with Different Functions

When your pivot table aggregates multiple value columns, you must specify how to calculate each one. Pass a dictionary to the aggfunc parameter where keys are column names and values are aggregation functions (e.g., {"sales": "sum", "qty": "mean"}). This ensures pandas knows how to compute the subtotal for each column independently.

Step 1: Define the Aggregation Dictionary

Create a mapping that assigns the appropriate aggregation to each value column. Without this dictionary, pandas cannot compute meaningful subtotals when mixing aggregation types across columns.

import pandas as pd
import numpy as np

# Sample data

df = pd.DataFrame({
    "region": ["North", "North", "South", "South", "East", "East"],
    "product": ["A", "B", "A", "B", "A", "B"],
    "sales": [100, 150, 200, 250, 120, 180],
    "qty":   [10, 15, 20, 25, 12, 18]
})

# Single value column with margins

single = pd.pivot_table(
    df,
    values="sales",
    index="region",
    columns="product",
    aggfunc="sum",
    margins=True,
    fill_value=0
)
print(single)

product   A    B
region           
East     120  180
North    100  150
South    200  250
All      420  580

Step 2: Enable Subtotals for Multiple Columns

Pass the aggregation dictionary to aggfunc and set margins=True. This generates a subtotal row for every value column using the specified aggregation function for each.

# Multiple value columns with margins

multi = pd.pivot_table(
    df,
    values=["sales", "qty"],
    index="region",
    columns="product",
    aggfunc={"sales": "sum", "qty": "mean"},
    margins=True,
    margins_name="Total",
    fill_value=0,
    dropna=False
)
print(multi)

               sales          qty       
product           A    B      A     B
region                                 
East            120  180   12.0  18.0
North           100  150   10.0  15.0
South           200  250   20.0  25.0
Total           420  580   14.0  19.5

How Pandas Internally Computes Subtotals

The subtotal implementation resides in pandas/core/reshape/pivot.py. The process follows this execution path:

1. pivot_table gathers arguments and forwards them to __internal_pivot_table (lines 58-71).
2. After grouping and aggregating via groupby(...).agg(aggfunc), the function checks the margins flag.
3. If enabled, it calls _add_margins (lines 299-313) to compute grand totals.
4. _add_margins invokes _compute_grand_margin to calculate a grand margin for each value column individually.
5. The helper stitches these margins onto the result, preserving the MultiIndex layout while inserting the new row labeled by margins_name.

Handling Missing Values and Edge Cases

To ensure accurate subtotals when your data contains gaps or empty groups, configure these additional parameters:

• Use fill_value=0 to replace missing cells with zeros before aggregation. This guarantees clean numeric totals even when some groups contain only NaNs.
• Set dropna=False to preserve groups that would otherwise be dropped because they contain all NaNs. The margin code respects dropna when deciding which rows to keep; disabling it ensures empty groups appear in the subtotal calculation.

Summary

• margins=True triggers the subtotal row calculation via the internal _add_margins function in pandas/core/reshape/pivot.py.
• When aggregating multiple columns, provide an aggregation dictionary to aggfunc specifying how to calculate each column's total.
• The subtotal row uses the label "All" by default, customizable via margins_name.
• Combine margins=True with fill_value=0 and dropna=False to handle missing data and ensure complete totals.
• The resulting DataFrame maintains its MultiIndex structure with the subtotal row integrated as part of the index.

Frequently Asked Questions

Can I customize the name of the subtotal row from "All" to something else?

Yes. Pass the margins_name parameter to pivot_table() with your desired label. For example, margins_name="Total" replaces the default "All" row label with "Total". This name is also applied to column subtotals if present.

Does margins=True work with hierarchical (MultiIndex) rows and columns?

Yes. The subtotal row appears at the top level of the row index, and subtotal columns appear at the top level of the column index. The internal _add_margins function preserves the existing MultiIndex structure while appending the grand margin entries, ensuring your hierarchical labels remain intact.

Why do my subtotals show NaN when some groups contain missing data?

This occurs when NaN values propagate through the aggregation. To fix this, pass fill_value=0 to replace NaNs with zeros before calculation. Additionally, use dropna=False to prevent pandas from dropping groups that contain only NaN values, which ensures these groups are included in the margin computation.

Is there a performance impact when using margins=True?

Yes, there is a modest overhead because pandas must compute an additional aggregation pass via _compute_grand_margin for each value column. However, for most datasets, this overhead is minimal compared to the initial pivot operation. If performance is critical, consider computing totals separately and concatenating them manually.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}