Calculating Running Totals, Subtotals, and Grand Totals in pandas for Boardroom-Ready Summary Tables

When your manager asks for "a quick summary table" before Tuesday's board meeting, they're not asking for a raw data dump. They want a table that tells a story: regional sales building toward a company total, quarterly revenue that accumulates into an annual figure, product lines with subtotals per category and a grand total at the bottom. That kind of table — the kind you'd present to executives — requires running totals, subtotals, and grand totals.

In Excel, you might reach for AutoSum or a PivotTable to build these. In pandas, you have precise, programmable control over every row and column, which means your summary table can be regenerated in seconds whenever the data changes. By the end of this lesson, you'll know exactly how to construct publication-quality summary tables in Python — the kind that belong in a boardroom slide deck or a formatted Excel report.

What you'll learn:

• What running totals, subtotals, and grand totals are and when to use each
• How to calculate cumulative sums with cumsum() for running totals
• How to add subtotal rows per group using groupby() and pd.concat()
• How to append a grand total row to any summary table
• How to control row ordering and formatting so the output looks polished

---

Prerequisites

You should be comfortable loading data into a DataFrame and performing basic aggregations. If you're new to pandas or haven't worked with groupby yet, start with Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data and then Grouping and Aggregating in pandas: groupby as the PivotTable Replacement before continuing here.

---

Setting Up the Example Dataset

We're going to work with a realistic retail sales dataset across four regions (North, South, East, West), three product categories (Electronics, Apparel, Home Goods), and three quarters. We'll build it directly so you can follow along without needing an external file.

import pandas as pd
import numpy as np

data = {
    "Region": [
        "North", "North", "North",
        "South", "South", "South",
        "East",  "East",  "East",
        "West",  "West",  "West",
    ],
    "Category": [
        "Electronics", "Apparel", "Home Goods",
        "Electronics", "Apparel", "Home Goods",
        "Electronics", "Apparel", "Home Goods",
        "Electronics", "Apparel", "Home Goods",
    ],
    "Q1_Sales": [142000, 38000, 61000, 98000, 52000, 44000,
                 175000, 29000, 55000, 110000, 41000, 67000],
    "Q2_Sales": [158000, 41000, 58000, 104000, 49000, 51000,
                 182000, 33000, 60000, 119000, 44000, 71000],
    "Q3_Sales": [163000, 44000, 62000, 111000, 55000, 53000,
                 190000, 37000, 64000, 125000, 47000, 75000],
}

df = pd.DataFrame(data)
df["Annual_Sales"] = df["Q1_Sales"] + df["Q2_Sales"] + df["Q3_Sales"]
print(df)

Output:

    Region    Category  Q1_Sales  Q2_Sales  Q3_Sales  Annual_Sales
0    North Electronics    142000    158000    163000        463000
1    North     Apparel     38000     41000     44000        123000
2    North  Home Goods     61000     58000     62000        181000
3    South Electronics     98000    104000    111000        313000
...

This is your raw detail data. The goal is to transform it into a structured summary table with subtotals by region and a grand total at the bottom.

---

What Are Running Totals, Subtotals, and Grand Totals?

Before we write a single line of aggregation code, let's be precise about what we're building — because conflating these three concepts is a common source of confusion.

Running total (also called a cumulative sum): a column where each row's value is the sum of all preceding rows up to and including the current one. Imagine a column of monthly revenue where each row shows how much you've sold so far this year. The number keeps growing as you move down the table.

Subtotal: a summary row inserted into the table at a group boundary. When you're looking at sales by region, a subtotal for "North" appears after all North rows and shows the combined figure for that region before moving on to South.

Grand total: a single summary row at the very bottom of the table that aggregates everything — every group, every row.

These are three different tools for three different purposes. Running totals are great in time-series tables. Subtotals and grand totals are essential in category-grouped summary reports.

---

Running Totals with `cumsum()`

A running total is the simplest of the three to calculate. pandas has a built-in method called cumsum() (short for cumulative sum) that does exactly this.

Let's say we want to show how annual sales accumulate as we move through the regions in our dataset:

df_sorted = df.sort_values("Annual_Sales", ascending=False).reset_index(drop=True)
df_sorted["Running_Total"] = df_sorted["Annual_Sales"].cumsum()
print(df_sorted[["Region", "Category", "Annual_Sales", "Running_Total"]])

Output:

    Region    Category  Annual_Sales  Running_Total
0     East Electronics        547000         547000
1    North Electronics        463000        1010000
2     West Electronics        354000        1364000
...

Each row in Running_Total shows the cumulative sum of Annual_Sales from the first row to the current row. This is useful in a "top contributors" table where you want to show what percentage of the total each line accounts for cumulatively.

Running Totals Within Groups

Often, running totals are more meaningful within a group. If you want a running total of quarterly sales within each region, you use groupby() combined with cumsum():

df_sorted = df.sort_values(["Region", "Q1_Sales"])
df_sorted["Cumulative_Q1_by_Region"] = (
    df_sorted.groupby("Region")["Q1_Sales"].cumsum()
)
print(df_sorted[["Region", "Category", "Q1_Sales", "Cumulative_Q1_by_Region"]])

The groupby("Region")["Q1_Sales"].cumsum() call resets the cumulative counter at the start of each new region. This is the pattern you'll use most often in business reporting. For a deeper look at window-based calculations like this, see Ranking and Window Calculations in pandas: Running Totals, Cumulative Averages, and Percent Rank with groupby and transform.

---

Adding Subtotal Rows with `groupby()` and `pd.concat()`

This is where things get interesting — and where pandas users sometimes get stuck. Adding subtotal rows means you need to insert new rows into your DataFrame. Those rows aren't in the original data; they're computed summaries that need to be stitched in at the right position.

The pattern has three steps:

1. Compute the group aggregations (the subtotals)
2. Label those aggregation rows so they're identifiable
3. Concatenate the detail rows and subtotal rows in the right order

Step 1: Aggregate to Get Subtotals

region_totals = df.groupby("Region")[["Q1_Sales", "Q2_Sales", "Q3_Sales", "Annual_Sales"]].sum()
region_totals = region_totals.reset_index()
region_totals["Category"] = "** TOTAL **"
print(region_totals)

Output:

  Region     Category  Q1_Sales  Q2_Sales  Q3_Sales  Annual_Sales
0   East  ** TOTAL **    264000    275000    291000        830000
1  North  ** TOTAL **    241000    257000    269000        767000
2  South  ** TOTAL **    194000    204000    219000        617000
3   West  ** TOTAL **    218000    234000    247000        699000

Step 2: Interleave Detail Rows and Subtotal Rows

Now we need to combine each group's detail rows with its subtotal row. We'll loop through each region, grab its rows, append the subtotal row for that region, and collect everything into a list:

pieces = []

for region in sorted(df["Region"].unique()):
    # Detail rows for this region
    region_detail = df[df["Region"] == region].copy()
    pieces.append(region_detail)
    
    # Subtotal row for this region
    region_subtotal = region_totals[region_totals["Region"] == region].copy()
    pieces.append(region_subtotal)

summary_df = pd.concat(pieces, ignore_index=True)
print(summary_df[["Region", "Category", "Q1_Sales", "Annual_Sales"]])

Output:

    Region    Category  Q1_Sales  Annual_Sales
0     East Electronics    175000        547000
1     East     Apparel     29000         99000
2     East  Home Goods     55000        179000
3     East  ** TOTAL **   264000        830000
4    North Electronics    142000        463000
5    North     Apparel     38000        123000
6    North  Home Goods     61000        181000
7    North  ** TOTAL **   241000        767000
...

That's the structure of a real subtotal table. Each group's rows appear, followed immediately by a clearly labeled summary row.

---

Appending a Grand Total Row

A grand total is conceptually simpler than subtotals — it's just one row at the very bottom. But adding it correctly requires the same concat pattern:

grand_total = df[["Q1_Sales", "Q2_Sales", "Q3_Sales", "Annual_Sales"]].sum()
grand_total_row = pd.DataFrame({
    "Region": ["GRAND TOTAL"],
    "Category": [""],
    "Q1_Sales": [grand_total["Q1_Sales"]],
    "Q2_Sales": [grand_total["Q2_Sales"]],
    "Q3_Sales": [grand_total["Q3_Sales"]],
    "Annual_Sales": [grand_total["Annual_Sales"]],
})

final_df = pd.concat([summary_df, grand_total_row], ignore_index=True)
print(final_df.tail(5))

Output:

     Region    Category  Q1_Sales  Q2_Sales  Q3_Sales  Annual_Sales
19     West  Home Goods     67000     71000     75000        213000
20     West  ** TOTAL **   218000    234000    247000        699000
21  GRAND TOTAL              868000    970000   1030000       2868000... 

---

Putting It All Together: A Clean, Reusable Function

Let's wrap the entire process into a function. This is how professional data code looks — not scattered steps, but a clean callable you can use on any dataset.

def build_summary_table(df, group_col, category_col, value_cols):
    """
    Build a summary DataFrame with subtotal rows per group
    and a grand total row at the bottom.
    
    Parameters
    ----------
    df : pd.DataFrame
    group_col : str  — column to group by (e.g., "Region")
    category_col : str — secondary label column (e.g., "Category")
    value_cols : list — numeric columns to sum
    
    Returns
    -------
    pd.DataFrame with detail rows, subtotal rows, and grand total
    """
    pieces = []
    
    # Compute subtotals for each group
    group_totals = df.groupby(group_col)[value_cols].sum().reset_index()
    group_totals[category_col] = "** SUBTOTAL **"
    
    for group_val in sorted(df[group_col].unique()):
        # Detail rows
        detail = df[df[group_col] == group_val].copy()
        pieces.append(detail)
        
        # Subtotal row
        subtotal = group_totals[group_totals[group_col] == group_val].copy()
        pieces.append(subtotal)
    
    result = pd.concat(pieces, ignore_index=True)
    
    # Grand total row
    grand_vals = {group_col: ["GRAND TOTAL"], category_col: [""]}
    for col in value_cols:
        grand_vals[col] = [df[col].sum()]
    
    grand_row = pd.DataFrame(grand_vals)
    result = pd.concat([result, grand_row], ignore_index=True)
    
    return result


# Usage
value_columns = ["Q1_Sales", "Q2_Sales", "Q3_Sales", "Annual_Sales"]
final_table = build_summary_table(df, "Region", "Category", value_columns)
print(final_table.to_string(index=False))

This function is general enough to reuse across different reports. Change the group_col to "Category" and you get subtotals by product line instead of region. No copy-paste, no manual adjustment.

---

Formatting Numbers for Human Readers

A table full of raw integers like 2868000 looks like gibberish to a non-technical audience. Real boardroom tables show $2,868,000. Let's add formatting as a final step before export.

The safest approach is to apply formatting at display or export time, not by converting your numeric columns to strings (which would break any downstream math):

# Create a formatted copy for display only
display_df = final_table.copy()

for col in value_columns:
    display_df[col] = display_df[col].apply(
        lambda x: f"${x:,.0f}" if pd.notna(x) else ""
    )

print(display_df.to_string(index=False))

For export to Excel — where formatting belongs in cell styles, not string values — keep the numeric types intact and use openpyxl or xlsxwriter to apply number formats. The article Building Summary Reports with pandas pivot_table and to_excel: Turning Aggregated Data into a Formatted, Multi-Sheet Workbook covers this in detail.

---

Hands-On Exercise

Use the following dataset to practice what you've learned:

exercise_data = {
    "Department": ["Sales", "Sales", "Sales",
                   "Marketing", "Marketing", "Marketing",
                   "Engineering", "Engineering", "Engineering"],
    "Month": ["January", "February", "March",
               "January", "February", "March",
               "January", "February", "March"],
    "Headcount": [12, 13, 13, 8, 8, 9, 25, 27, 28],
    "Budget_Spent": [84000, 91000, 91000,
                     62000, 65000, 70000,
                     210000, 228000, 236000],
}
exercise_df = pd.DataFrame(exercise_data)

Tasks:

1. Add a Cumulative_Budget column that shows the running total of Budget_Spent for each department (reset per department using groupby + cumsum).

2. Use build_summary_table() (the function from this lesson) to build a summary with subtotals per department and a grand total. Use ["Headcount", "Budget_Spent"] as value_cols.

3. Add an Avg_Cost_Per_Head column to exercise_df before calling the function (calculated as Budget_Spent / Headcount). Then modify build_summary_table() to handle this column with mean instead of sum for the subtotal and grand total rows.

Challenge: The third task requires you to think about mixed aggregations — some columns should be summed, others averaged. This is a real-world problem in every summary report.

---

Common Mistakes & Troubleshooting

Mistake: Subtotals don't appear in the right order

If you use df["Region"].unique() without sorted(), the order depends on insertion order in the DataFrame, which can be unpredictable on some pandas versions. Always sort explicitly when row order matters for a presentation table.

Mistake: Grand total counts NaN as zero

If your value columns contain NaN, .sum() by default skips them (skipna=True). This is usually what you want, but be aware that a NaN in the detail rows will silently disappear from the grand total. Use .sum(skipna=False) if you want NaN to propagate to the total (signaling that the sum is unreliable).

Mistake: pd.concat() produces duplicate column names

This happens when the DataFrames being concatenated have different columns. The subtotal DataFrame only has [group_col, category_col] + value_cols — if your detail DataFrame has extra columns (like a row ID or a date), they'll appear as NaN in the subtotal rows. That's usually acceptable, but make sure you're selecting only the columns you need before concatenation if you want a clean output.

Mistake: Treating subtotal rows as data in downstream analysis

Once you've added subtotal and grand total rows, the DataFrame is no longer "tidy" data. If you pass it to another aggregation or merge operation, those labeled rows will corrupt your results. Build the summary table as the last step of your pipeline, after all analysis is done. For more on structuring clean pipelines, see Building a Reusable ETL Pipeline in pandas: Extract, Transform, and Load Data from Multiple Sources into a Clean, Analysis-Ready Output.

Mistake: Using append() instead of pd.concat()

DataFrame.append() was removed in pandas 2.0. If you see it in older tutorials, replace it with pd.concat([df1, df2], ignore_index=True).

---

Summary & Next Steps

Here's what you've built in this lesson:

• Running totals using cumsum(), both globally and within groups using groupby().cumsum()
• Subtotal rows by looping through groups, aggregating with groupby().sum(), labeling the result, and interleaving it with detail rows using pd.concat()
• Grand total rows by creating a single-row DataFrame from the overall sum and appending it at the end
• A reusable function that encapsulates the entire pattern
• Safe display formatting that keeps numeric types intact until the final output step

These patterns cover the vast majority of boardroom-ready summary tables you'll encounter in practice. The logic is always the same: aggregate at the right level, label the aggregated rows clearly, concatenate in the right order.

Where to go from here:

Once your summary tables are correct, the next challenge is making them look polished in Excel. Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work will show you how to apply fonts, borders, and number formats so your subtotal rows are visually distinct from detail rows.

If your data lives in a database rather than a flat file, you can generate these same summary tables directly from SQL queries loaded into pandas — Reading from SQL Databases into pandas with SQLAlchemy walks through the connection and query workflow.

And if you want to go deeper on the time-series version of running totals — things like month-over-month cumulative revenue or rolling 12-month sums — see Calculating Month-over-Month and Year-over-Year Changes in pandas: pct_change, shift, and Period Comparisons for Business Reporting.