Forward-Fill Blank Cells in Grouped Excel Reports with pandas

Reports exported from finance and ERP systems are laid out for reading, not for analysis. The region appears once, on the first row of its block, and the rows below are blank — sometimes because the cells are merged, sometimes because the report writer suppressed repeated values. Read that sheet with pandas and most rows have no region, so any grouping or filter by region is wrong. The fix is a forward fill, and it is easy to get subtly wrong. This guide, part of Handling Missing Data in Excel Reports, fills labels down safely, including multi-level hierarchies, merged cells and total rows.

Prerequisites

pip install pandas openpyxl

A sample report with a merged region cell, a city missing at the start of a group, and a total row:

import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
for row in [["Region", "City", "Product", "Sales"],
            ["North", "Leeds", "Tea", 10], [None, None, "Coffee", 12], [None, "York", "Tea", 7],
            ["South", None, "Tea", 5], [None, "Bath", "Coffee", 9],
            ["Total", None, None, 43]]:
    ws.append(row)
ws.merge_cells("A2:A4")
wb.save("grouped.xlsx")

df = pd.read_excel("grouped.xlsx")
print(df)

The merged North cell is read as a value in the first row and blanks below — exactly like a suppressed repeat — so one technique handles both. The South group has no city on its first row: a real gap in the source that the fill must not paper over.

Step 1 — Remove total rows first

Total and subtotal rows sit inside the same columns as the labels. Fill before removing them and "Total" becomes a region that is carried down, or the last region is carried into the total row. Drop them first:

raw = df.copy()
is_total = df["Region"].astype("string").str.contains(r"^\s*(?:sub)?total\b", case=False, na=False)
df = df[~is_total].reset_index(drop=True)

Totals in exports take many shapes; Remove Subtotal and Total Rows from an Excel Export covers detecting them by label, by position and by checking the sums.

Step 2 — The naive fill, and why it is wrong

The obvious approach fills every label column in one call:

naive = df.copy()
naive[["Region", "City"]] = naive[["Region", "City"]].ffill()
print(naive)

Look at the first South row: it now says York. The city column was blank because the source omitted it, and a plain forward fill does not know that a new region started, so it carries the last North city across the boundary. Nothing fails, the frame has no gaps, and a report by city now counts a South sale under York.

Step 3 — Fill hierarchically

Fill the top level of the hierarchy with a plain forward fill, then fill each lower level within the groups of the level above it:

def fill_hierarchy(frame, levels):
    out = frame.copy()
    out[levels[0]] = out[levels[0]].ffill()
    for depth in range(1, len(levels)):
        parents = out[levels[:depth]]
        block = parents.ne(parents.shift()).any(axis=1).cumsum()   # new block when a parent changes
        out[levels[depth]] = out.groupby(block)[levels[depth]].ffill()
    return out

filled = fill_hierarchy(df, ["Region", "City"])
print(filled)
print(filled[filled["City"].isna()])

groupby(...).ffill() restarts at the first row of every group, so a label can never leak into a neighbouring group. The groups are consecutive blocks — a new block starts whenever any parent label changes — rather than the parent values themselves. That matters when a region appears twice in one report, say North at the top and again after a page break: grouping by the value would let the second North block inherit a city from the first, while grouping by block keeps them apart. The same function handles three or four levels — region, city, store — by listing them in order. The remaining gap is now an honest one: the South row without a city is reported instead of silently filled, and the owner of the export can tell you whether it should be Bath.

Step 4 — Leave the data columns alone

Only label columns follow the "shown once, applies to the rows below" rule. A blank in Sales means no figure was recorded, and filling it would copy another row's number. Name the label columns explicitly rather than filling the whole frame:

LABELS = ["Region", "City"]
assert not set(LABELS) & set(df.select_dtypes("number").columns), "a label column is numeric"
clean = fill_hierarchy(df, LABELS)
print(clean["Sales"].isna().sum())   # 0 — and any NaN here would be real

Gaps in numeric columns deserve their own decision — zero, interpolation or leave as missing — covered in Fill Missing Values in Excel with pandas fillna.

Step 5 — Check the result against the report's own totals

The total row you dropped in Step 1 is a free test. After filling, the sum of the data rows should equal it, and the sums per region should match any subtotals the report printed:

reported_total = raw.loc[is_total, "Sales"].sum()
print(clean["Sales"].sum(), reported_total)          # 43 43
assert clean["Sales"].sum() == reported_total, "rows lost or duplicated while cleaning"
print(clean.groupby("Region")["Sales"].sum().to_dict())
# {'North': 29, 'South': 14}

A mismatch means a data row was mistaken for a total, a total survived as data, or the fill assigned rows to the wrong group. Checking the per-group sums against the subtotals catches the last case, which the grand total alone cannot. Keeping raw — the frame exactly as read — makes the check possible after the cleaning steps have changed df.

Step 6 — Blocks separated by blank rows

Some reports start each group after an entirely blank row and put the group name alone on a header line. Use the blank rows to number the blocks, take the label from each block's first line, then drop the separator and header lines:

blocks = pd.DataFrame({
    "A": ["North", "Leeds", "York", None, "South", "Bath"],
    "B": [None, 10, 7, None, None, 9],
})
block_id = blocks.isna().all(axis=1).cumsum()
blocks["Region"] = blocks.groupby(block_id)["A"].transform("first")
records = blocks[blocks["B"].notna()].rename(columns={"A": "City", "B": "Sales"})
print(records.values.tolist())
# [['Leeds', 10.0, 'North'], ['York', 7.0, 'North'], ['Bath', 9.0, 'South']]

cumsum over the "row is blank" flag gives every block its own number, so the header of one block can never be applied to another. The header rows — where the value column is empty — are dropped at the end.

Step 7 — Put the blanks back for output

When the cleaned data goes back into a report, readers expect the grouped look again. Blank each label wherever it repeats the row above within the same parent:

def blank_repeats(frame, levels):
    out = frame.copy()
    key = pd.Series("", index=out.index)
    for level in levels:
        key = key + "|" + out[level].astype(str)
        repeat = key.eq(key.shift())
        out[level] = out[level].mask(repeat)
    return out

report = blank_repeats(filled, ["Region", "City"])
report.to_excel("grouped_clean.xlsx", index=False)
print(report.fillna("").values.tolist())

Comparing the combined key rather than the single column means a city is shown again whenever its region changes, even if two regions share a city name. Keep the filled frame for any further calculation; the blanked version is for presentation only.

Common pitfalls

• Filling before sorting changes. A forward fill relies on the original row order. Fill first, then sort; sorting a grouped report before filling scatters the blanks away from their labels.
• Whitespace that looks blank. A cell containing a space is not NaN, so the fill stops there. Convert whitespace-only cells to missing values first.
• Filling across sheets. When you combine several sheets, fill each one before concatenating so the last label of one sheet does not flow into the next.
• Merged cells spanning columns. A title merged across A1:D1 is not a group label; skip title rows with header= or skiprows= before filling.

Conclusion

Grouped Excel reports hide their labels in blank and merged cells. Drop total rows, forward-fill the top label level, fill each lower level within its parent group so labels never cross boundaries, and leave numeric columns alone. Report any label that is still blank instead of guessing, and restore the grouped look only when writing the finished report.

Frequently asked questions

How do I fill merged cells when reading Excel with pandas? pandas reads only the top-left cell of a merged range and leaves the rest empty, so the same forward fill that handles blank cells also fills merged ones. Fill only the label columns that were merged.

Why did forward fill put a city under the wrong region? A plain ffill on the lower-level column carries the last value down regardless of groups. Fill the higher level first, then fill the lower level within each higher-level group with groupby(...).ffill().

Should I forward-fill numeric columns too? Almost never. A blank amount in a grouped report is usually a genuine missing value or a zero, not a repeat of the row above. Fill label columns only and treat numeric gaps separately.

How do I put the blanks back when writing the report? Blank a label wherever it equals the value in the row above within the same parent group, using mask with shift. That restores the grouped look without changing the data.

Related

• Up one level: Handling Missing Data in Excel Reports — everything about gaps in Excel data.
• Unmerge Cells and Fill Values with openpyxl — fixing merged cells in the workbook itself.
• Read Excel with Multi-Row Headers in pandas — the same problem in the header rows.
• Interpolate Missing Numeric Values in Excel Data — filling numbers, which follows different rules.