Py LearnPythonForExcelThe Excel-user's path to Python
Home › Course › Module 12 › Splitting one file into many

Splitting one file into many

Module 12 · Automating Boring Excel Tasks7 min readBeginner

What you'll learn

  • Iterate over groupby groups
  • Write each to a named file
  • Create the output folder if needed

The pattern

from pathlib import Path
import pandas as pd

df = pd.read_excel("all_orders.xlsx")
out = Path("per_region"); out.mkdir(exist_ok=True)

for region, group in df.groupby("region"):
    safe = region.replace("/", "_")
    group.to_excel(out / f"{safe}.xlsx", index=False)
    print(f"  wrote {safe}.xlsx ({len(group):,} rows)")

One workbook with one sheet per group

with pd.ExcelWriter("by_region.xlsx") as writer:
    for region, group in df.groupby("region"):
        safe = region.replace("/", "_")[:31]   # Excel sheet name limit
        group.to_excel(writer, sheet_name=safe, index=False)
⚠️ Sheet name limit
Excel sheet names cap at 31 characters and can't contain / \ ? * [ ]. Always sanitise.

Key takeaways

  • df.groupby("col") yields (key, group_df) tuples.
  • One file per group: loop and .to_excel().
  • One workbook, many sheets: pd.ExcelWriter + sheet_name=.

Split a big workbook

Take a multi-region orders file and produce one workbook per salesperson, each containing only their rows.

📹 Video walkthrough
A video walkthrough of this lesson will be embedded here. Until then, the written walkthrough above mirrors what the video will cover step-for-step.