Split a master sheet into one Excel file per branch, safely

The real risk is sending the wrong rows

Splitting a sheet sounds mechanical, and the code is short. The part that matters is what happens when it goes wrong: Noida's outstanding list, including customer names and amounts, lands in the Ghaziabad manager's inbox. The checks in this article are designed around that failure, not around speed.

Clean the key before you group by it

Group-by treats every distinct spelling as a separate branch. In the synthetic master below, "Noida" and " noida" would become two files, one of them with a single row, and the Noida manager would never see it. Trim and title-case the column, and list the distinct values before splitting. If a spelling cannot be fixed mechanically, such as "Noida Sec 62" versus "Noida 62", map it with a small lookup table that the business agrees.

Rows with no branch are set aside as unassigned. They go back to whoever maintains the master, not into any branch file.

Writing one workbook per branch

The customers, invoices and amounts below are synthetic. Writing .xlsx from pandas uses the openpyxl package, which is the default Excel engine and is included in most Python installations used for analysis; if it is missing, pip install openpyxl adds it.

import re
import shutil
from pathlib import Path
import pandas as pd

# Synthetic master sheet: outstanding invoices for all branches.
master = pd.DataFrame({
    "branch": ["Noida", "Ghaziabad", "Noida", "Gurugram / Manesar", "Ghaziabad",
               "Noida", "Gurugram / Manesar", " noida", None],
    "customer": ["Kiran Stores", "Mehta & Co", "Sai Traders", "Apex Auto", "Vardhman Retail",
                 "Om Agencies", "Delta Parts", "Nav Durga Mart", "Unknown"],
    "invoice": ["NS/101", "GZ/044", "NS/102", "GM/310", "GZ/045", "NS/103", "GM/311", "NS/104", "X/1"],
    "outstanding": [48500, 112000, 23750, 305000, 9800, 61200, 77400, 15600, 4000],
})

master["branch"] = master["branch"].str.strip().str.title()
unassigned = master[master["branch"].isna()]
work = master.dropna(subset=["branch"])

def safe_name(text: str) -> str:
    """Branch name to a file name that works on Windows and in email."""
    return re.sub(r"[^A-Za-z0-9]+", "_", text).strip("_")

out_dir = Path("branch_files")
if out_dir.exists():
    shutil.rmtree(out_dir)            # start clean: no stale files from last month
out_dir.mkdir()

manifest = []
for branch, part in work.groupby("branch", sort=True):
    path = out_dir / f"outstanding_{safe_name(branch)}_2026-09.xlsx"
    part = part.sort_values("outstanding", ascending=False)
    part.to_excel(path, index=False, sheet_name="Outstanding")
    back = pd.read_excel(path, dtype={"invoice": str})
    assert set(back["branch"]) == {branch}            # no other branch's rows inside
    manifest.append({"branch": branch, "file": path.name,
                     "rows": len(back), "outstanding": int(back["outstanding"].sum())})

manifest = pd.DataFrame(manifest)
print(manifest.to_string(index=False))
print("unassigned rows:", unassigned[["customer", "invoice", "outstanding"]].to_dict("records"))

# Control totals: every row of the master is in exactly one file or on the unassigned list.
assert manifest["rows"].sum() + len(unassigned) == len(master)
assert manifest["outstanding"].sum() + unassigned["outstanding"].sum() == master["outstanding"].sum()
manifest.to_csv(out_dir / "manifest.csv", index=False)

Eight assigned rows become three files. " noida" joined the other Noida rows after cleaning, so the Noida file has four rows and ₹1,49,050 outstanding. The branch "Gurugram / Manesar" contains a slash, which would be read as a folder separator, so the file name becomes outstanding_Gurugram_Manesar_2026-09.xlsx. One row has no branch and is listed rather than guessed.

The checks, and why each exists

Read each file back. The script opens every workbook it just wrote and asserts that the only branch value inside is the expected one. This is the check that catches the wrong-rows mistake. It costs a fraction of a second per file.

Rebuild the folder each run. Deleting the output folder at the start means last month's outstanding_Faridabad.xlsx cannot be sent again this month by mistake, just because it was still sitting in the folder. Point this at a dedicated output folder only; never at a folder that holds anything else.

Control totals. Rows in all files plus unassigned rows must equal the master's row count, and the same for the rupee total. If a branch name had a trailing character that the cleaning missed, rows would still add up, but the manifest would show an unexpected extra branch, which is why you read it before sending.

Keep the invoice number as text. dtype={"invoice": str} on read-back protects identifiers that look numeric. For identifiers with leading zeros, set the column type explicitly on the way in as well.

The manifest is for people

manifest.csv lists each branch, its file, rows and total outstanding. Send it to the person who approves the distribution before any branch file goes out, or paste its figures into the covering email. A branch manager who sees "4 invoices, ₹1,49,050" in the email and opens a file with the same figures can trust the process; one who sees a mismatch knows to stop.

Variations you may need

One workbook, many sheets. If the recipient is a single regional head rather than branch managers, write one workbook with a sheet per branch using pd.ExcelWriter. Excel limits sheet names to 31 characters and does not allow characters such as / or :, so apply the same safe-name function and truncate.

Different columns per audience. Branch managers may not need the margin or credit-limit columns. Select the columns for the output explicitly, with a list in the script, rather than dropping the ones you remember to drop. A new sensitive column added to the master next month is then excluded by default.

Sending the files. Once the manifest is approved, each file can be emailed to its branch using a lookup of branch to recipient; the sending step is covered in email a daily report with an attachment using Python smtplib. Keep the branch-to-email table in a sheet that the business maintains, and fail loudly if a branch has no recipient.

Splitting, checking and distributing data by branch, region or salesperson is routine MIS work, and it is where a small mistake travels furthest. Building these habits into ordinary scripts is part of the automation practice in our Data Analyst programme.