Write Grouped Data with Blank Rows Between Groups

df.to_excel() writes one long table, which is right for data that other programs will read and wrong for a report that people will read. A printed list of three hundred orders is far easier to follow when it is broken up by region: a heading for each region, its orders, a subtotal, a blank line, then the next region. People often get there by writing the DataFrame and then inserting rows with openpyxl, which is slow, shifts every row below each insert and breaks any formula already written. The better approach is to plan the layout first — work out which row each heading, detail line, subtotal and gap goes in — and then write everything in a single pass. This page does that with pandas and openpyxl, uses live SUBTOTAL formulas so the totals stay correct if a figure is edited, adds a grand total that does not count the subtotals twice, groups the detail rows into collapsible outline levels, and checks the result by recalculating it. It belongs to Writing DataFrames to Excel with pandas.

Prerequisites

pip install pandas openpyxl

The input is a flat list of fourteen orders with region, order number, customer, units and revenue. The report groups them by region.

Step 1 — Plan the layout before writing anything

import pandas as pd

orders = pd.read_excel("orders.xlsx").sort_values(["Region", "Order"])
COLUMNS = ["Order", "Customer", "Units", "Revenue"]
SUM_COLS = ["Units", "Revenue"]

layout, r = [], 2                                   # row 1 is the column header
for region, g in orders.groupby("Region", sort=True):
    layout.append(("heading", r, region)); r += 1
    first = r
    for rec in g[COLUMNS].itertuples(index=False):
        layout.append(("detail", r, rec)); r += 1
    layout.append(("subtotal", r, (region, first, r - 1))); r += 1
    layout.append(("blank", r, None)); r += 1
layout.append(("grand", r, (2, r - 1)))
print(f"{len(orders)} orders in {orders['Region'].nunique()} groups -> {r} rows")  # header + 3 rows per group + details + grand total
for kind, row, payload in layout[:6]:
    print(row, kind, payload if kind != "detail" else tuple(payload)[:2])

The plan is a list of (kind, row, payload) entries built in one walk over the sorted groups. Each group contributes a heading row, its detail rows, a subtotal row and a blank row; the subtotal remembers the first and last detail row of its group, which is what its formula will need. The row counter is the only state, so the positions are correct by construction — there is no inserting and no shifting. Sorting by region and then by order number first makes the report predictable from run to run. The same plan could be written by any library; separating where things go from how they are written is what keeps the code simple. For fourteen orders in three regions the plan has 25 rows: the column header, three extra rows per group for the heading, subtotal and gap, the fourteen detail rows and the grand total. East, with six orders, occupies rows 2 to 10.

Step 2 — Write the plan in one pass

from openpyxl import Workbook
from openpyxl.styles import Border, Font, PatternFill, Side

wb = Workbook()
ws = wb.active
ws.title = "Orders by region"
ws.append(COLUMNS)
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1F3864")
col = {name: i + 1 for i, name in enumerate(COLUMNS)}
letter = {name: ws.cell(1, i).column_letter for name, i in col.items()}
top = Border(top=Side(style="thin"))

for kind, row, payload in layout:
    if kind == "heading":
        ws.cell(row, 1, payload).font = Font(bold=True, size=12, color="1F3864")
    elif kind == "detail":
        for name, value in zip(COLUMNS, payload):
            ws.cell(row, col[name], value)
    elif kind == "subtotal":
        region, first, last = payload
        ws.cell(row, 1, f"{region} total").font = Font(bold=True)
        for name in SUM_COLS:
            c = ws.cell(row, col[name], f"=SUBTOTAL(9,{letter[name]}{first}:{letter[name]}{last})")
            c.font, c.border = Font(bold=True), top
    elif kind == "grand":
        first, last = payload
        ws.cell(row, 1, "Grand total").font = Font(bold=True, size=12)
        for name in SUM_COLS:
            c = ws.cell(row, col[name], f"=SUBTOTAL(9,{letter[name]}{first}:{letter[name]}{last})")
            c.font = Font(bold=True, size=12)
            c.border = Border(top=Side(style="thin"), bottom=Side(style="double"))
for row in ws.iter_rows(min_row=2, min_col=col["Revenue"], max_col=col["Revenue"]):
    row[0].number_format = "£#,##0"
ws.column_dimensions["A"].width, ws.column_dimensions["B"].width = 14, 12
ws.freeze_panes = "A2"

Writing is a loop over the plan with one branch per kind of row. Detail values are written as they are, so numbers stay numbers. The subtotal rows use SUBTOTAL(9, …) — function 9 is a sum — over their group's detail rows. The grand total uses the same function over the whole block, headings, subtotals and all, and that is the reason to use SUBTOTAL rather than SUM: SUBTOTAL ignores cells that are themselves SUBTOTAL results, so the grand total adds each order once even though the group subtotals sit inside its range. A SUM over the same range would count everything twice. A thin line above each subtotal and a double line under the grand total follow the usual accounting convention. Because the totals are formulas rather than numbers computed in Python, someone who corrects an order in the sheet sees the subtotal and grand total update immediately.

Step 3 — Collapsible groups

for kind, row, payload in layout:
    if kind == "detail":
        ws.row_dimensions[row].outline_level = 1
ws.sheet_properties.outlinePr.summaryBelow = True
wb.save("orders_by_region.xlsx")
print("saved")

Giving the detail rows an outline level of 1 adds Excel's grouping controls in the left margin: a reader can collapse every group to see just the headings and subtotals, then expand one region to see its orders. summaryBelow tells Excel that each group's summary row comes after its details, so the collapse button sits next to the subtotal. More on outlines in Group and Outline Rows in Excel with openpyxl.

Step 4 — Check the totals by recalculating

soffice --headless --convert-to 'csv:Text - txt - csv (StarCalc):44,34,76,1,,0,false,true,true' orders_by_region.xlsx > /dev/null 2>&1
grep -E "total|^East|^North|^South" orders_by_region.csv
python -c "
import pandas as pd
o = pd.read_excel('orders.xlsx')
print(o.groupby('Region')[['Units','Revenue']].sum().to_dict('index'))
print('all:', o[['Units','Revenue']].sum().to_dict())"

openpyxl writes formulas but does not calculate them, so the check recalculates the workbook in LibreOffice and prints the subtotal and grand total rows as displayed, next to the same sums computed directly in pandas. They agree group by group — East 159 units and £13,681, North 117 and £19,384, South 113 and £13,182 — and the grand total of 389 units and £46,247 equals the plain sum of all fourteen orders, confirming that the nested subtotals were not counted twice. A SUM over the same block would have shown £92,494, exactly double.

When not to add blank rows

Blank rows, group headings and subtotal rows make a sheet easier to read and harder to reuse. Sorting, filtering, pivot tables and read_excel all treat a blank row as the end of the data or a heading row as an order, so a sheet laid out this way is a report, not a data source. The usual answer is to write both: the grouped report for people, and the flat table on a second sheet for anyone who needs to analyse the data, which also gives the formulas in the report something to be checked against. For grouped summaries without the detail, a pandas pivot with margins is simpler — see Add Subtotals and Grand Totals to a pandas Pivot Table.

Common pitfalls

• Inserting rows after writing. It is slow and shifts formulas; plan the rows and write once.
• SUM for the grand total. It double counts the subtotals; use SUBTOTAL(9, …).
• Unsorted data. Sort by the group key first, or a group appears in several pieces.
• Numbers written as text. Write detail values directly so they stay numeric.
• Reports used as data. Keep a flat copy of the data on another sheet.

Conclusion

To write grouped data with blank rows between groups, plan the layout first — a heading, the detail rows, a subtotal and a blank row for each group, with the row numbers counted as you go — and then write the plan in one pass with openpyxl. Use SUBTOTAL(9, …) for group totals and for the grand total over the whole block, so nothing is counted twice, give detail rows an outline level for collapsible groups, and recalculate the file once to confirm the totals.

Frequently asked questions

How do I add a blank row between groups when writing Excel from pandas? Build a plan of rows — heading, details, subtotal, blank — while looping over groupby, then write each planned row with openpyxl.

Why use SUBTOTAL instead of SUM for group totals? A SUBTOTAL over a range ignores other SUBTOTAL cells inside it, so a grand total over groups with subtotals does not double count.

How do I make groups collapsible in Excel with openpyxl? Set ws.row_dimensions[row].outline_level = 1 on the detail rows and ws.sheet_properties.outlinePr.summaryBelow = True.

Is it better to insert rows with openpyxl afterwards? No; inserting shifts every row below and does not update formulas, so it is slower and riskier than planning rows first.

Related

• Up one level: Writing DataFrames to Excel with pandas — getting DataFrames into Excel.
• SUBTOTAL and AGGREGATE Equivalents in pandas — reading such reports back.
• Group and Outline Rows in Excel with openpyxl — outline levels.
• Add a Summary Sheet to an Excel Report with Python — totals on their own sheet.