Roll Up Totals Through a Parent-Child Hierarchy

Organisations describe their structures as hierarchies: cost centres roll up into departments, departments into divisions, divisions into the company; accounts roll up into account groups; products into categories. In Excel such a hierarchy is usually a simple two-column table — each node and its parent — while the amounts, budgets or headcounts are recorded against the bottom-level nodes. The report everyone wants shows a total at every level: each team, each department as the sum of its teams, each division as the sum of its departments. Because the depth varies — one branch might have four levels, another two — a fixed number of groupby calls does not work. This page turns the parent-child table into each node's path to the top, rolls amounts up to every ancestor in one join, handles amounts posted to intermediate nodes, checks the result, and writes an indented report. It belongs to Merging and Joining Excel DataFrames.

Prerequisites

pip install pandas openpyxl

hierarchy.xlsx lists fourteen cost centres with their codes, names and parent codes; the Group has no parent. Branches have different depths: Warehouse North sits four levels below the Group, Finance three. costs.xlsx holds cost postings by cost centre — mostly to the bottom level, but one to "Sales Europe", an intermediate node, as happens when a cost belongs to a region rather than a country.

Step 1 — Each node's path to the top

The key move is to compute, for every node, the list of its ancestors including itself. Walk up the parent links until there is no parent, with a guard against loops:

import pandas as pd

h = pd.read_excel("hierarchy.xlsx", sheet_name="Hierarchy")
parent = {c: (p if isinstance(p, str) else None) for c, p in zip(h["Code"], h["Parent"])}
name = dict(zip(h["Code"], h["Name"]))

def path_to_top(code):
    path, seen = [code], {code}
    while parent.get(path[-1]):
        nxt = parent[path[-1]]
        if nxt in seen:
            raise ValueError(f"cycle in hierarchy at {nxt}")
        if nxt not in parent:
            raise ValueError(f"{path[-1]} has unknown parent {nxt}")
        path.append(nxt)
        seen.add(nxt)
    return path

paths = {code: path_to_top(code) for code in h["Code"]}
print(" → ".join(paths["WH-N"]))
print(" → ".join(paths["FIN"]))

Warehouse North's path is WH-N → WH → OPS → GRP; Finance's is FIN → CORP → GRP. The walk raises an error on a cycle (a node that is its own ancestor) or on a parent code that does not exist, because either would make every total wrong; validating a hierarchy more thoroughly is covered in Validate Parent-Child Codes in a Hierarchy. Blank parent cells arrive from Excel as missing values (NaN), so the dictionary converts anything that is not a string to None; testing for missing values with isinstance is more reliable than where, which in pandas 3 keeps NaN in text columns. For hierarchies of a few thousand nodes, this simple walk is instantaneous.

Step 2 — Roll amounts up to every ancestor with one join

Turn the paths into a bridge table with one row per node-and-ancestor pair. Joining the postings to it copies each amount to every level above the node it was posted to, and a group-by on the ancestor gives every node's total:

bridge = pd.DataFrame([(node, anc, depth) for node, path in paths.items() for depth, anc in enumerate(path)],
                      columns=["Code", "Ancestor", "Steps up"])
postings = pd.read_excel("costs.xlsx", sheet_name="Postings").rename(columns={"Cost centre": "Code"})

unknown = set(postings["Code"]) - set(parent)
assert not unknown, f"postings to unknown cost centres: {unknown}"
rolled = (postings.merge(bridge, on="Code")
                  .groupby("Ancestor")["Amount"].sum()
                  .rename("Total"))
print(rolled.reindex(["GRP", "OPS", "WH", "WH-N", "SAL", "SAL-EU"]).to_string())

Each posting appears once for every node on its path — a cost to Warehouse North counts towards WH-N, WH, OPS and GRP — so summing by ancestor gives every node's rolled-up total in one step, whatever the depth. The bridge (sometimes called a closure table) is the whole trick: it turns a recursive question into an ordinary join and group-by. Postings to unknown cost centres are rejected before the join, because an inner join would otherwise drop them silently and the totals would quietly be too low.

Step 3 — Direct amounts versus rolled-up totals

Some amounts are posted to intermediate nodes — the 15,000 on Sales Europe. A report should show both what was posted directly to a node and its rolled-up total:

direct = postings.groupby("Code")["Amount"].sum().rename("Direct")
report = h.set_index("Code")[["Name", "Parent"]].join(direct).join(rolled).fillna({"Direct": 0, "Total": 0})
report["Depth"] = [len(paths[c]) - 1 for c in report.index]
report["Children total"] = report["Total"] - report["Direct"]
print(report.loc[["SAL-EU", "SAL-DE", "SAL-FR", "WH-N"], ["Name", "Direct", "Children total", "Total"]].to_string())

Sales Europe has 15,000 posted directly and 160,200 from its two countries, for a total of 175,200. Warehouse North received two postings, which the direct sum combines. Showing the direct column makes amounts on intermediate nodes visible — often they are perfectly legitimate, sometimes they are postings that should have gone to a lower level, and either way someone reading the report should see them rather than wonder why a parent's total exceeds the sum of its children.

Step 4 — Check the roll-up

Two simple checks prove the roll-up is complete: the top node's total equals the sum of all postings, and every parent's total equals its direct amount plus its children's totals:

top = [c for c, p in parent.items() if p is None]
assert len(top) == 1, f"expected one top node, found {top}"
print("top total:", report.loc[top[0], "Total"], "| all postings:", postings["Amount"].sum())

children_sum = report.groupby("Parent")["Total"].sum()
mismatch = [(p, report.loc[p, "Total"], report.loc[p, "Direct"] + children_sum[p]) for p in children_sum.index
            if abs(report.loc[p, "Total"] - (report.loc[p, "Direct"] + children_sum[p])) > 0.005]
print("parents whose total ≠ direct + children:", mismatch or "none")

The Group's total equals the sum of every posting, so nothing was lost or double-counted, and every parent's total equals its own direct postings plus its children's totals. The first check catches postings to codes outside the hierarchy and broken links; the second catches a node attached to the wrong parent or a duplicated row in the bridge. Both are cheap enough to run on every refresh.

Step 5 — An indented report with subtotals

Order the nodes so each parent is followed by its children, indent names by depth, and write the result to Excel with the totals bold for parents:

from openpyxl.styles import Alignment, Font

children = report.reset_index().groupby("Parent")["Code"].apply(sorted).to_dict()
order = []
def visit(code):
    order.append(code)
    for child in children.get(code, []):
        visit(child)
visit(top[0])

out = report.loc[order, ["Name", "Depth", "Direct", "Total"]].reset_index()
with pd.ExcelWriter("cost_rollup.xlsx", engine="openpyxl") as xw:
    out[["Code", "Name", "Direct", "Total"]].to_excel(xw, sheet_name="Roll-up", index=False)
    ws = xw.sheets["Roll-up"]
    for r, (depth, code) in enumerate(zip(out["Depth"], out["Code"]), start=2):
        ws.cell(r, 2).alignment = Alignment(indent=int(depth) * 2)
        for c in (3, 4):
            ws.cell(r, c).number_format = "#,##0"
        if code in children:
            for c in range(1, 5):
                ws.cell(r, c).font = Font(bold=True)
    ws.column_dimensions["B"].width = 26
print(out.assign(Name=["  " * d + n for d, n in zip(out["Depth"], out["Name"])])[["Name", "Direct", "Total"]].to_string(index=False))

A depth-first visit lists the Group, then Corporate and its teams, then Operations and its branches, and so on, so each subtotal appears directly above the lines it summarises. In Excel the names are indented with the cell's indent setting rather than with spaces — the text stays clean for filtering and lookups — and parent rows are bold. The same layout technique is covered in Indent Cells to Show a Hierarchy with openpyxl; adding Excel outline groups lets readers collapse levels, as in Group and Outline Rows in Excel with openpyxl.

Common pitfalls

• One groupby per level. Uneven depths break it; use paths and a bridge table.
• Inner joins that drop unknown codes. Check postings against the hierarchy first.
• Ignoring amounts on parent nodes. Show direct amounts beside rolled-up totals.
• Cycles and orphans. Detect them while building paths, before any totals are computed.
• No reconciliation. The top total must equal the sum of all postings.

Conclusion

To roll amounts up a parent-child hierarchy of any depth, build each node's path to the top by walking parent links, expand the paths into a bridge table of node and ancestor pairs, join the postings to it and group by ancestor — every node's total in one step. Keep direct amounts beside rolled-up totals so postings to intermediate nodes are visible, check that the top equals the sum of all postings and each parent equals its direct amount plus its children, and write an indented report ordered depth-first.

Frequently asked questions

How do I sum a parent-child hierarchy in pandas? Compute each node's list of ancestors, build a table with one row per node and ancestor, merge the amounts onto it by node, and group by ancestor to get every node's total.

Why can't I just use groupby for each level? Hierarchies often have branches of different depths, so there is no fixed set of level columns. The ancestor bridge handles any depth.

How do I handle amounts posted to a parent node? Keep a direct-amount column alongside the rolled-up total. The total includes the direct amount plus all descendants.

How do I check a hierarchy roll-up is correct? The top node's total should equal the sum of all postings, and each parent's total should equal its direct amount plus its children's totals.

Related

• Up one level: Merging and Joining Excel DataFrames — combining tables.
• Validate Parent-Child Codes in a Hierarchy — checking the hierarchy first.
• Allocate Shared Costs Across Departments — the costs before roll-up.
• Add Subtotals and Grand Totals to a pandas Pivot Table — subtotals for fixed levels.