Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Generate One Excel Report per Region in a Loop

Split one dataset into many workbooks safely: a filename that cannot collide or break a filesystem, one failure that does not stop the batch, atomic writes, an empty-group decision, and a manifest of what was produced.

A dataset that produces one report for the whole business usually produces twelve for the regions, or forty for the branches, or two hundred for the account managers. The loop itself is three lines. Everything that makes it survivable in a scheduled job is the rest of this page: a filename that cannot collide, a failure that does not take the other eleven with it, and a record of what was actually produced.

This guide is part of Generating Excel Reports from Templates, and it applies whether each workbook is built from scratch or filled from a template.

One failing group should not end the batch Without per-group error handling, an exception in the third region ends the loop and the last nine reports are never produced, while the two already written are left with no record. With a try around each group, the failure is recorded, the remaining reports are generated, and the run ends with a summary naming exactly which group failed and why. no isolation North South East ✗ nine never run two files exist, ten do not and nothing records which two, so the rerun starts from the beginning try per group North South East ✗ nine more ✓ eleven files, one named failure the manifest says which group failed, why, and what the rerun needs to cover The job still exits non-zero — it just delivers everything it could first

Prerequisites

Bash
pip install pandas openpyxl

The examples generate their own data, so the guide runs end to end.

Step 1: Group the data

Python
from pathlib import Path

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "South", "East/West", "North", "South", "Nordics & Baltics"],
    "product": ["A", "B", "A", "C", "A", "B"],
    "amount": [150.25, 274.75, 75.0, 190.4, 88.1, 320.0],
    "order_date": pd.to_datetime(["2026-07-02", "2026-07-05", "2026-07-09",
                                  "2026-07-14", "2026-07-21", "2026-07-28"]),
})

OUT = Path("reports/2026-07")
OUT.mkdir(parents=True, exist_ok=True)

groups = dict(tuple(sales.groupby("region")))
print(list(groups))
# ['East/West', 'Nordics & Baltics', 'North', 'South']

East/West and Nordics & Baltics are there on purpose. Both are perfectly reasonable region names and both break a naive filename — the first introduces a directory separator, the second an ampersand and spaces.

Step 2: Build a filename that cannot hurt you

Python
import re
import unicodedata

ILLEGAL = re.compile(r'[<>:"/\\|?*\x00-\x1f]')
WINDOWS_RESERVED = {"CON", "PRN", "AUX", "NUL",
                    *(f"COM{i}" for i in range(1, 10)),
                    *(f"LPT{i}" for i in range(1, 10))}


def slugify(name, max_length=60):
    """A filename fragment that is safe on every filesystem."""
    text = unicodedata.normalize("NFKD", str(name))
    text = text.encode("ascii", "ignore").decode()      # drop accents
    text = ILLEGAL.sub("-", text)
    text = re.sub(r"[\s&]+", "-", text).strip("-. ")
    text = re.sub(r"-{2,}", "-", text)
    if text.upper().split(".")[0] in WINDOWS_RESERVED:
        text = f"{text}-report"
    return (text[:max_length].strip("-. ") or "unnamed").lower()


def unique_path(directory, stem, suffix=".xlsx", used=None):
    """A path that does not collide, even if two names slugify identically."""
    used = used if used is not None else set()
    candidate, n = stem, 1
    while candidate in used or (directory / f"{candidate}{suffix}").exists():
        n += 1
        candidate = f"{stem}-{n}"
    used.add(candidate)
    return directory / f"{candidate}{suffix}"

The collision check is the part that is easy to skip and expensive to skip. East/West and East-West both slugify to east-west, and without the check the second silently overwrites the first — a region receives another region's numbers, which is the worst failure mode this whole page exists to prevent.

Stripping trailing dots and spaces matters on Windows, where a file ending in either is legal to create through some APIs and then impossible to open or delete.

Step 3: Loop with per-group isolation

Each group gets its own try, so one bad region costs one report rather than twelve:

Python
import logging
import os
from datetime import datetime

log = logging.getLogger("batch")


def build_workbook(frame, region, path):
    """Write one region's workbook. Atomic: a failure leaves no partial file."""
    tmp = path.with_name(f".{path.stem}.tmp{path.suffix}")
    summary = (frame.groupby("product", as_index=False)["amount"]
                    .sum().sort_values("amount", ascending=False))

    with pd.ExcelWriter(tmp, engine="openpyxl",
                        datetime_format="yyyy-mm-dd") as writer:
        summary.to_excel(writer, sheet_name="Summary", index=False, startrow=2)
        frame.to_excel(writer, sheet_name="Detail", index=False)

        ws = writer.sheets["Summary"]
        ws["A1"] = (f"{region} — July 2026 · {len(frame):,} orders · "
                    f"generated {datetime.now():%Y-%m-%d %H:%M}")
        ws.freeze_panes = "A4"
        for cell in ws["B"][3:]:
            cell.number_format = '#,##0.00'

    os.replace(tmp, path)              # atomic: readers never see a partial file
    return path


def generate_all(groups, out_dir):
    written, failed, used = [], [], set()

    for region, frame in sorted(groups.items()):
        path = unique_path(out_dir, slugify(region), used=used)
        try:
            build_workbook(frame, region, path)
            written.append({"region": region, "path": str(path),
                            "rows": len(frame),
                            "amount": round(float(frame["amount"].sum()), 2)})
            log.info("wrote %s (%d rows)", path.name, len(frame))
        except Exception as exc:                       # one region, one failure
            failed.append({"region": region, "error": f"{type(exc).__name__}: {exc}"})
            log.error("FAILED %s: %s", region, exc)

    return written, failed

Two details do the work. The temporary file plus os.replace means a crash mid-write leaves no half-formed workbook for someone to open or email — the same atomic-publish pattern used when refreshing a report on a schedule. And catching broadly inside the loop only is the one place a bare except Exception is right: the point is that no single group's problem can end the batch, and every failure is recorded rather than swallowed.

Step 4: Decide what an empty group means

A region with no rows produces no group at all from groupby, so it silently vanishes from the output. That is almost never what anyone wants — a missing file reads as "the job broke", not "there was no activity":

Python
ALL_REGIONS = ["North", "South", "East/West", "Nordics & Baltics", "Highlands"]


def with_empty_groups(groups, expected, columns):
    """Ensure every expected group is present, with an empty frame if need be."""
    complete = dict(groups)
    for name in expected:
        if name not in complete:
            complete[name] = pd.DataFrame(columns=columns)
    return complete


full = with_empty_groups(groups, ALL_REGIONS, sales.columns)

Then say so on the sheet rather than shipping an empty grid:

Python
if frame.empty:
    ws["A3"] = "No activity recorded for this region in the period."
How a region with no rows is read by the person expecting it groupby drops a region with no rows, so no file is produced and the recipient reads the absence as a broken job. Reindexing against the expected list produces a workbook carrying a no-activity note, which reads as an answer instead of a fault. Highlands had no orders this period groupby alone no highlands.xlsx in the folder read as "the job failed" chased on Monday, explained on Thursday reindexed against the expected list highlands.xlsx — "no activity this period" read as "the answer is zero" no chase, and the manifest still balances

Whichever way you decide, decide once and write it in the code. The failure this prevents is a regional manager assuming their report was forgotten, chasing it, and discovering three days later that the answer was zero all along.

Step 5: Write a manifest and fail loudly at the end

The batch's own record of what it produced is what makes the run auditable and the rerun cheap:

Python
import json


def run_batch(groups, out_dir=OUT):
    written, failed = generate_all(groups, out_dir)

    manifest = {
        "generated_at": datetime.now().isoformat(timespec="seconds"),
        "period": "2026-07",
        "written": written,
        "failed": failed,
        "totals": {"reports": len(written),
                   "rows": sum(w["rows"] for w in written),
                   "amount": round(sum(w["amount"] for w in written), 2)},
    }
    (out_dir / "manifest.json").write_text(json.dumps(manifest, indent=2))

    if failed:
        names = ", ".join(f["region"] for f in failed)
        raise SystemExit(f"{len(written)} report(s) written; "
                         f"{len(failed)} failed: {names}")
    return manifest

Raising SystemExit after everything else has been written is the shape that serves both audiences: the eleven regions get their reports, and the scheduler still sees a non-zero exit code so the failure is alerted rather than lost in a log. The manifest also gives the delivery step an exact list of files to send, instead of globbing a directory that may still contain last month's output.

Turning a group name into a safe, unique filename The raw name passes through four steps: accents are stripped to ASCII, characters the filesystem rejects are replaced, whitespace and ampersands collapse to hyphens, and the result is capped in length. A final collision check appends a counter, because two different region names can reduce to the same slug and would otherwise overwrite each other. raw name "Nordics & Baltics" ASCII + strip illegal no / \ : * ? " < > | collapse + cap "nordics-baltics" collision check append -2 if taken Why the last step is not optional "East/West" and "East-West" both reduce to east-west; without the check, the second write overwrites the first, and one region receives another's figures — a failure nothing detects, because both files exist and both open.

Step 6: Parallelise only if it pays

Writing a workbook is CPU-bound in the Excel writer, so threads help little and processes help a lot:

Python
from concurrent.futures import ProcessPoolExecutor, as_completed


def generate_parallel(groups, out_dir, workers=4):
    written, failed, used = [], [], set()
    plan = {region: unique_path(out_dir, slugify(region), used=used)
            for region in sorted(groups)}                  # paths assigned up front

    with ProcessPoolExecutor(max_workers=workers) as pool:
        futures = {pool.submit(build_workbook, frame, region, plan[region]): region
                   for region, frame in groups.items()}
        for future in as_completed(futures):
            region = futures[future]
            try:
                written.append({"region": region, "path": str(future.result())})
            except Exception as exc:
                failed.append({"region": region, "error": str(exc)})
    return written, failed

Assigning every path in the parent process before submitting anything is the necessary part: workers cannot see each other's used set, so two of them could otherwise pick the same filename. Keep the pool at four to eight — the writers are memory-hungry, and a pool the size of your core count on a large dataset will hit swap before it hits a speed-up. Process Multiple Excel Files in Parallel with Python covers the trade-offs in more depth.

Common pitfalls and gotchas

SymptomCauseFix
A report contains another region's dataTwo names slugified identicallyCheck for collisions before writing
FileNotFoundError on a valid nameA / in the group name made it a pathStrip path separators in the slug
The batch stops at the third regionNo per-group tryCatch inside the loop, fail at the end
A region's file is missing entirelyThe group was empty, so groupby skipped itReindex against the expected list
Half-written file gets emailedWritten in placeTemp file plus os.replace
Last month's files sent againDelivery globbed the directorySend from the manifest
Scheduler reports success despite failuresErrors only loggedSystemExit non-zero at the end
Machine swaps during a parallel runPool too large for memoryFour to eight workers

Performance and scale notes

The cost is per workbook, not per row, so 200 small reports take noticeably longer than one report with 200 times the rows. Build the shared parts once — a template loaded once, formats created once, the source read once — and keep only the per-group work inside the loop.

Past a few hundred outputs, the delivery becomes the real problem rather than the generation: two hundred emails with attachments will trip most SMTP rate limits. Publishing to a shared folder and sending one message with a link scales where attachments do not, and it also means a correction replaces a file instead of chasing an email.

Conclusion

The loop is easy; the batch is not. Slugify every group name and check for collisions so no region can receive another's numbers, wrap each group in its own try so one failure costs one report, write each workbook atomically, decide deliberately what an empty group produces, and finish by writing a manifest and exiting non-zero if anything failed. That turns a per-region loop into something a scheduler can run unattended and someone can audit afterwards.

Frequently asked questions

Should one failing region stop the whole batch? No. Catch per group, record the failure, and carry on — then fail the job at the end with a summary. Eleven delivered reports and one named failure is a far better outcome than nothing at all.

How do I stop a group name breaking the filename? Slugify it — strip path separators and characters the filesystem rejects, collapse whitespace, and cap the length. Then check for collisions, because two different names can slugify to the same string.

What should happen when a group has no rows? Decide explicitly and write it down. Usually: produce the workbook with a visible "no activity this period" note, so the absence is a statement rather than a missing file nobody chases.

Is it faster to generate the reports in parallel? Often, yes — the work is CPU-bound in the Excel writer, so a process pool helps where threads would not. Keep the pool small and make sure each worker writes to its own file.

Up to the parent guide:

Related guides: