If you produce the same Excel report every week, the work is not the analysis. The work is the copy-paste: open seven files, hunt for the revenue column, fix the ones where someone typed N/A, paste into a master sheet, and rebuild the chart by hand. Every one of those steps is where the errors come from, and every one of them is scriptable.
The fix is a script with four stages: read → normalize → merge → render. Here is the pattern that survives contact with real files, plus the four things that break first.
1. Read every file, one at a time
import pandas as pd, os
for fn in sorted(os.listdir(folder)):
if not fn.lower().endswith((".xlsx", ".xls", ".csv")) or fn.startswith("~$"):
continue # ~$ files are Excel lock files — reading them fails
df = pd.read_excel(os.path.join(folder, fn)) if not fn.endswith(".csv") \
else pd.read_csv(os.path.join(folder, fn), encoding="utf-8-sig")
sorted() matters more than it looks. Directory order is filesystem-dependent, so a report that lists "latest file first" will silently change ordering between machines. Sorting gives you the same sequence everywhere, and it makes the log readable when a file fails.
The ~$ check is not defensive padding. Excel creates ~$report.xlsx next to any open workbook, and read_excel on that file throws rather than returning something usable. One person leaving a file open should not kill the run.
encoding="utf-8-sig" handles the byte-order mark that Excel writes when it exports CSV. Without it, your first column name becomes \ufeffRevenue and every subsequent lookup on "Revenue" misses.
2. Normalize each file before merging
This is the step almost everyone gets wrong. People concatenate first, then try to map columns — and silently lose rows. Two files with headings Revenue and 销售额 are two different DataFrame columns. A groupby on one of them drops the other entirely, and you get a total that is missing an entire region with no error attached.
ALIASES = {
"amount": ["amount", "revenue", "sales", "total", "金额", "销售额", "营收"],
"date": ["date", "day", "日期", "订单日期", "时间"],
}
def normalize(df):
out = pd.DataFrame()
for canon, names in ALIASES.items():
for c in df.columns:
if str(c).strip().lower() in names:
out[canon] = df[c]
break
return out if "amount" in out else None
Send every file through normalize() first, then pd.concat. You keep all rows, and a column that only exists in one file becomes NaN elsewhere instead of vanishing.
Returning None when amount is missing is deliberate. A file without an amount column is not a file you can total, and dropping it with a logged reason is better than merging a column of nulls that makes your row count look healthy while your sum stays flat.
str(c).strip().lower() handles the other quiet killer: header whitespace. "Revenue " and "Revenue" are different keys in pandas, and nobody sees the trailing space in a spreadsheet.
3. Clean the amount column properly
The single most expensive bug in spreadsheet automation: one - or N/A in a revenue column turns the whole column into object dtype. .sum() then concatenates strings instead of adding numbers, so your grand total becomes something like 1,2003500.50, or the operation raises depending on what else is in there.
Force the type. Do not trust it.
out["amount"] = pd.to_numeric(
df["amount"].astype(str).str.replace(r"[^\d.\-]", "", regex=True),
errors="coerce"
)
Then decide explicitly what a NaN means. Does a missing amount mean zero, or does it mean the row is unusable and should be excluded and counted? Either answer is defensible. Silence is what makes reports wrong, because the default behavior is to skip nulls in the sum while still showing the full row count.
The regex strips currency symbols, thousands separators, and spaces in one pass. Note that it also turns "N/A" into an empty string, which to_numeric with errors="coerce" converts to NaN — exactly what you want, and exactly why you must handle that NaN on the next line.
A related trap: Excel stores 12% as 0.12, but many CSV exports store the literal string "12%". If you handle the xlsx path and not the CSV path, your percentage totals are off by a factor of 100× in one direction or the other. Check the dtype and the range before you trust either.
4. Render, don't paste
Write the summary with pandas.ExcelWriter, one sheet per dimension. Region totals on one sheet, monthly totals on another, and the raw normalized frame on a third so anyone can audit the numbers without rerunning your script.
Write the narrative with python-docx. Keep the text generated from the same variables that produced the numbers. A hand-edited paragraph that says "up 12%" while the table says 9% is the failure mode you are trying to eliminate, and it happens precisely because someone updates one and not the other.
For charts, use openpyxl.chart. Pasted images cannot be edited, and the first thing a manager asks for is an edit: change the axis, drop a series, relabel a bar. A chart object in the workbook answers that request. A PNG does not.
The four things that break first
Lock files. ~$report.xlsx exists while someone has the file open. Skip them, and log the skip.
Merged header rows. pandas reads the top row as headers, so a two-row heading block gives you Unnamed: 0 and columns named Unnamed: 5. Read with header=1, or unmerge upstream where the file is produced.
Dates as text. 2026年4月8日 is not ISO, and 03/12/2026 is ambiguous — 3 December or 12 March, and both parse successfully under the wrong assumption. Mixed conventions inside one column will sort wrong and group into the wrong month without raising anything.
Percentage columns. 0.12 and "12%" in the same column, as covered above. Handle both formats or your totals are off by 100×.
Every one of these four has the same shape: the script runs to completion and produces a number. That is why they cost more time than a crash. A crash tells you where to look; a wrong total in a finished workbook does not.
Making it survive next quarter
Log the file list, the row count after each stage, and the NaN count per column. When the numbers shift, that log tells you which stage changed.
Fail loudly on an empty result. A report with zero rows is a bug, not a quiet week.
Keep the normalization map in one place, and add aliases as you meet them. The first run against a new supplier's export will always find a column name you did not anticipate, and adding a string to a list is a much better afternoon than debugging a KeyError in a merge.
Working with messy spreadsheets? I build small browser-based tools for exactly these jobs — CSV merge, PII masking, date normalisation — all free to run locally, nothing uploaded.
If you'd rather not rebuild this from scratch, I packaged the exact scripts I use: Excel Report Toolkit — the Excel-report scripts I actually run.
Top comments (0)