Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Validate Parent-Child Codes in a Hierarchy

Check a parent-child table maintained in Excel — an account tree, cost-centre structure or product category list — before anything is rolled up through it: duplicate codes, parents that do not exist, more than one top node, cycles and self-references, unreachable branches, depth limits, code-format rules that tie children to parents, and postings to non-leaf nodes, all reported with the Excel row to fix.
Validation & data quality

Chart-of-accounts trees, cost-centre structures and product category lists are usually maintained in Excel as two columns: a code and its parent's code. People add, rename and move rows by hand, and the table slowly accumulates errors that break everything built on it: a code that appears twice, a parent that was renamed so its children point at nothing, two rows that point at each other, a second "top" node created by accident. A roll-up through a broken hierarchy produces totals that look plausible and are wrong — postings under an orphaned branch simply vanish from the top. This page checks a hierarchy for each of these problems with pandas, adds the business rules a real structure usually has — a maximum depth, child codes that start with the parent's code, postings only to bottom-level nodes — and writes a report that tells the maintainer exactly which Excel row to fix. It belongs to Validating Excel Data with Python.

Prerequisites

Bash
pip install pandas openpyxl

accounts.xlsx holds a small chart of accounts with nineteen rows: code, name and parent code, read as text so leading zeros would survive. It contains the usual accumulated problems — deliberately all at once. postings.xlsx holds a few balances by account, including one posted to an intermediate account and one to a code that does not exist.

Step 1 — Load the table and keep the Excel row numbers

Every finding should point at a row the maintainer can find, so keep the Excel row number with each record:

Python
import pandas as pd

tree = pd.read_excel("accounts.xlsx", sheet_name="Tree", dtype=str)
tree["Code"] = tree["Code"].str.strip()
tree["Parent"] = tree["Parent"].str.strip()
tree["Excel row"] = tree.index + 2                     # header is row 1
findings = []

def flag(rows, check, detail):
    for r in rows:
        findings.append({"Excel row": int(r), "Check": check, "Detail": detail})

print(len(tree), "rows read")

Reading codes as text and stripping spaces avoids two classic false alarms: 1000 and "1000" not matching, and "1100 " with a trailing space looking like a different code. The flag helper collects every problem as a row number, a check name and a readable detail, so all checks feed one report.

Step 2 — Duplicates, orphans and roots

The structural basics come first: each code once, every parent present, exactly one top node — or the expected number, for a chart of accounts with separate trees for assets, liabilities and so on:

Python
dup = tree[tree["Code"].duplicated(keep=False)]
for code, grp in dup.groupby("Code"):
    flag(grp["Excel row"], "duplicate code", f"{code} appears on rows {', '.join(map(str, grp['Excel row']))}")

codes = set(tree["Code"])
orphans = tree[tree["Parent"].notna() & ~tree["Parent"].isin(codes)]
for _, r in orphans.iterrows():
    flag([r["Excel row"]], "missing parent", f"{r['Code']} points to {r['Parent']}, which does not exist")

self_ref = tree[tree["Code"] == tree["Parent"]]
flag(self_ref["Excel row"], "self-reference", "a code cannot be its own parent")

roots = tree[tree["Parent"].isna()]
EXPECTED_ROOTS = {"1000", "2000"}
unexpected = roots[~roots["Code"].isin(EXPECTED_ROOTS)]
flag(unexpected["Excel row"], "unexpected top node", "has no parent but is not an expected root")
print(pd.DataFrame(findings).to_string(index=False))

Account 1120 appears twice, on rows 5 and 13. Retained earnings (3100) points to a parent 3000 that does not exist — probably a top node someone forgot to add — and 5100 is its own parent. The list of expected roots is part of the hierarchy's definition: an account tree may legitimately have several top nodes, but they should be the ones you intend. Missing-parent errors are the most damaging: every posting under that branch would drop out of the rolled-up totals.

The structural checks and what they catch Duplicate codes catch a code entered twice. Missing parents catch children pointing at a code that does not exist. Self-references catch a code that is its own parent. Cycles catch two or more codes pointing at each other. Unexpected roots catch an accidental extra top node. rule example in this tree duplicate code each code once 1120 on rows 5 and 13 missing parent parent must exist 3100 → 3000 (absent) self-reference code ≠ parent 5100 → 5100 cycle no loops 4100 → 4200 → 4100 unexpected root only listed tops none here

Step 3 — Cycles and unreachable branches

A cycle — 4100's parent is 4200 and 4200's parent is 4100 — never reaches a top node, so a roll-up would loop forever or skip the branch. Walk each code's chain of parents and record where it ends:

Python
parent = {r.Code: r.Parent for r in tree.drop_duplicates("Code").itertuples()}
row_of = dict(zip(tree.drop_duplicates("Code")["Code"], tree.drop_duplicates("Code")["Excel row"]))

def chain(code, limit=50):
    seen = [code]
    while isinstance(parent.get(seen[-1]), str):
        nxt = parent[seen[-1]]
        if nxt in seen:
            return seen + [nxt], "cycle"
        if nxt not in parent:
            return seen + [nxt], "broken"
        seen.append(nxt)
        if len(seen) > limit:
            return seen, "too deep"
    return seen, "ok"

reported = set()
for code in parent:
    path, status = chain(code)
    if status == "cycle" and len(set(path)) > 1:          # a code pointing at itself is reported above
        loop = frozenset(path[path.index(path[-1]):-1])
        if loop not in reported:
            reported.add(loop)
            flag([row_of[c] for c in loop], "cycle", " → ".join(path[path.index(path[-1]):]))
print([f for f in findings if f["Check"] == "cycle"])

Following parents from 4100 leads to 4200 and back to 4100: a cycle, reported once with both rows and the loop spelled out. A cycle can be longer than two codes, and the walk catches any length. Codes whose chain ends at a missing parent are already reported as orphans; their descendants are affected too, and the same walk shows which codes cannot reach a top node — useful for explaining why a whole branch is missing from a report. The limit guards against pathological chains.

Step 4 — Business rules: depth, code format and leaf postings

Real hierarchies have conventions that a generic check cannot know. Encode yours explicitly; three common ones:

Python
MAX_DEPTH = 4
depths = {}
for code in parent:
    path, status = chain(code)
    if status == "ok":
        depths[code] = len(path) - 1
        if depths[code] > MAX_DEPTH - 1:
            flag([row_of[code]], "too deep", f"{code} is at level {depths[code] + 1}, maximum is {MAX_DEPTH}")

def prefix_ok(code, par):
    return code[:len(par.rstrip('0'))] == par.rstrip('0')

for r in tree.drop_duplicates("Code").itertuples():
    if isinstance(r.Parent, str) and r.Parent in parent and not prefix_ok(r.Code, r.Parent):
        flag([r._4], "code format", f"{r.Code} does not start with its parent's prefix {r.Parent.rstrip('0')}")

postings = pd.read_excel("postings.xlsx", dtype={"Account": str})
has_children = set(tree["Parent"].dropna())
for i, p in postings.iterrows():
    if p["Account"] not in parent:
        findings.append({"Excel row": i + 2, "Check": "posting to unknown code", "Detail": f"postings.xlsx: {p['Account']}"})
    elif p["Account"] in has_children:
        findings.append({"Excel row": i + 2, "Check": "posting to parent", "Detail": f"postings.xlsx: {p['Account']} has child accounts"})
print([f for f in findings if f["Check"] in ("too deep", "code format", "posting to parent", "posting to unknown code")])

The depth rule flags 1213 and 1214, at levels five and six of a structure meant to have at most four — vehicles were subdivided twice beyond the agreed depth. The prefix rule — a child's code starts with its parent's code minus trailing zeros, so 1210's children are 121x — catches 2120 Accruals filed under 1100 Current assets, almost certainly a mistake. It also flags 1213 and 1214 again, because four-digit codes leave no room for a fifth level — a sign that the depth and the code scheme have to agree — and the two codes in the 4100/4200 cycle. One underlying error often trips several rules; the report groups findings by row so that is easy to see. The posting checks catch balances on 1100 and 1210, which both have child accounts (in a strict chart only leaf accounts take postings), and a balance on 9999, which is not in the tree at all. Each rule is a few lines because the structure has already been reduced to a parent dictionary.

Generic structure checks and business rules Generic checks apply to any hierarchy, such as duplicates, missing parents, cycles and roots. Business rules depend on the organisation, such as a maximum depth, child codes starting with the parent's prefix, and postings allowed only on leaf nodes. generic structure each code once parents exist, no cycles expected top nodes only business rules max depth 4 levels child starts with parent prefix postings on leaf accounts only The order of the checks Read codes as text and keep Excel row numbers, run the structural checks for duplicates, missing parents, self-references and roots, walk parent chains to find cycles and unreachable codes, apply business rules for depth, code format and postings, then write a findings list and a highlighted copy of the sheet. Read as text keep Excel row numbers Structure duplicates, orphans, roots Chains cycles, unreachable Rules + report depth, prefix, postings

Step 5 — A report the maintainer can act on

Write all findings to Excel, sorted by row, and highlight the rows in a copy of the original sheet:

Python
from openpyxl import load_workbook
from openpyxl.comments import Comment
from openpyxl.styles import PatternFill

report = pd.DataFrame(findings).sort_values(["Check", "Excel row"])
report.to_excel("hierarchy_findings.xlsx", index=False)

wb = load_workbook("accounts.xlsx")
ws = wb["Tree"]
for row, grp in report[~report["Check"].str.startswith("posting")].groupby("Excel row"):
    for c in ws[row]:
        c.fill = PatternFill("solid", fgColor="FFC7CE")
    ws.cell(row, 1).comment = Comment("; ".join(f"{a}: {b}" for a, b in zip(grp["Check"], grp["Detail"])), "validator")
wb.save("accounts_checked.xlsx")
print(report.groupby("Check").size().to_dict())
print("hierarchy valid:", report.empty)

The findings workbook lists every problem with its check and detail; the checked copy of the tree highlights each faulty row in red with a comment explaining what is wrong, so the maintainer fixes the problems in place without cross-referencing two files. The final line is the gate for anything downstream: a roll-up or a database load should run only when the report is empty. Once the hierarchy is clean, rolling amounts up through it is covered in Roll Up Totals Through a Parent-Child Hierarchy, and highlighting invalid cells in general in Highlight Invalid Cells in Excel with Python.

Common pitfalls

  • Codes read as numbers. Leading zeros vanish and "1000" stops matching 1000; read as text.
  • Rolling up before validating. Orphaned branches silently drop out of the totals.
  • Assuming one top node. List the expected roots explicitly.
  • Checking only two-node cycles. Walk the full parent chain to catch loops of any length.
  • Reports without row numbers. Point at the Excel row so fixes are quick.

Conclusion

A parent-child hierarchy maintained by hand needs checking before anything is rolled up through it. Read codes as text, keep Excel row numbers, and test for duplicate codes, parents that do not exist, self-references, unexpected top nodes and cycles of any length by walking each code's chain of parents. Add the organisation's own rules — maximum depth, child codes matching their parent's prefix, postings only on leaf nodes — and write the findings both as a list and as highlighted rows with comments in a copy of the sheet, so the maintainer can fix them where they are.

Frequently asked questions

How do I find orphan codes in a hierarchy with pandas? Rows whose parent is not blank and does not appear in the Code column are orphans; test the Parent column with notna and isin against the set of codes.

How do I detect cycles in a parent-child table? Follow each code's parent links and keep a list of codes seen; if a parent is already in the list, the chain contains a cycle.

How do I check that postings only go to bottom-level accounts? Collect the set of codes that appear as someone's parent; a posting to any code in that set is a posting to a non-leaf node.

What should a hierarchy validation report contain? The Excel row, the check that failed and a readable detail for each problem, ideally with the faulty rows highlighted in a copy of the sheet.

More on validation & data quality

More on validation & data quality →