Summarize order-level CSV data by month with pandas, then reconcile the grouped counts, units, and amounts against the source. A small synthetic example makes every expected result easy to verify by hand.
Who this is forThis guide is for people who need a repeatable monthly CSV summary and want explicit checks that aggregation did not lose records.
This example produces one row per calendar month with three measures: order count, total units, and total amount. The synthetic source uses one row per order, so counting rows is equivalent to counting orders in this specific dataset.
The important step is reconciliation. After groupby, the monthly values are added again and compared with the source totals. A summary can look reasonable even when rows were accidentally filtered, duplicated, or dropped.
The following dataset is synthetic and was written specifically for this article. Save it as monthly_orders.csv. It contains seven orders across January, February, and March 2026.
order_id,order_date,team,units,amount
O001,2026-01-05,North,2,40
O002,2026-01-20,South,3,60
O003,2026-02-02,North,1,25
O004,2026-02-18,South,4,100
O005,2026-02-28,North,2,50
O006,2026-03-03,South,5,125
O007,2026-03-15,North,1,30
The source totals are simple enough to check manually. There are 7 orders. Units total 18: 2 + 3 + 1 + 4 + 2 + 5 + 1. Amount totals 430: 40 + 60 + 25 + 100 + 50 + 125 + 30.
| Measure | Source total |
|---|---|
| Orders | 7 |
| Units | 18 |
| Amount | 430 |
January contains O001 and O002. February contains O003, O004, and O005. March contains O006 and O007. Adding each group gives the expected monthly table.
| month | orders | units | amount |
|---|---|---|---|
| 2026-01 | 2 | 5 | 100 |
| 2026-02 | 3 | 7 | 175 |
| 2026-03 | 2 | 6 | 155 |
The monthly totals reconcile with the source: 2 + 3 + 2 = 7 orders, 5 + 7 + 6 = 18 units, and 100 + 175 + 155 = 430 amount.
Save the following script as monthly_summary_pandas.py. It validates required columns, rejects missing or duplicate order IDs, parses dates with one explicit format, converts numeric columns, groups by month, reconciles the totals, and writes the result only after those checks pass.
from pathlib import Path
import pandas as pd
SOURCE = Path("monthly_orders.csv")
OUTPUT_DIR = Path("outputs") / "monthly_summary_result"
OUTPUT = OUTPUT_DIR / "monthly_summary.csv"
REQUIRED = ["order_id", "order_date", "units", "amount"]
def main() -> None:
if not SOURCE.is_file():
raise FileNotFoundError(f"Source CSV not found: {SOURCE}")
if OUTPUT_DIR.exists():
raise FileExistsError(f"Output folder already exists: {OUTPUT_DIR}")
data = pd.read_csv(SOURCE, dtype={"order_id": "string"})
missing = [name for name in REQUIRED if name not in data.columns]
if missing:
raise ValueError(f"Missing required columns: {missing}")
if data.empty:
raise ValueError("Source CSV contains no data rows.")
if data[REQUIRED].isna().any().any():
raise ValueError("A required field contains a missing value.")
if data["order_id"].str.strip().eq("").any():
raise ValueError("order_id contains a blank value.")
if data["order_id"].duplicated().any():
raise ValueError("Duplicate order_id found.")
data["order_date"] = pd.to_datetime(
data["order_date"], format="%Y-%m-%d", errors="raise"
)
data["units"] = pd.to_numeric(data["units"], errors="raise")
data["amount"] = pd.to_numeric(data["amount"], errors="raise")
data["month"] = data["order_date"].dt.to_period("M").astype(str)
summary = (
data.groupby("month", as_index=False, sort=True)
.agg(
orders=("order_id", "count"),
units=("units", "sum"),
amount=("amount", "sum"),
)
)
source_totals = (len(data), data["units"].sum(), data["amount"].sum())
summary_totals = (
summary["orders"].sum(),
summary["units"].sum(),
summary["amount"].sum(),
)
if summary_totals != source_totals:
raise RuntimeError("Monthly totals do not reconcile with the source.")
OUTPUT_DIR.parent.mkdir(parents=True, exist_ok=True)
OUTPUT_DIR.mkdir()
summary.to_csv(OUTPUT, index=False)
check = pd.read_csv(OUTPUT)
if check.to_dict("records") != summary.to_dict("records"):
raise RuntimeError("Saved CSV does not match the calculated summary.")
print(f"Source rows: {len(data)}.")
print(f"Months: {len(summary)}.")
print(f"Verified totals: units={source_totals[1]}, amount={source_totals[2]}.")
print(f"Output: {OUTPUT.as_posix()}")
if __name__ == "__main__":
main()
python monthly_summary_pandas.pyThe output should contain three data rows sorted by month. The expected CSV contents are shown below.
month,orders,units,amount
2026-01,2,5,100
2026-02,3,7,175
2026-03,2,6,155
The expected console output below was derived by hand from the synthetic data and code. It is not a captured execution log.
Source rows: 7.
Months: 3.
Verified totals: units=18, amount=430.
Output: outputs/monthly_summary_result/monthly_summary.csv| Symptom | What to check |
|---|---|
| ModuleNotFoundError for pandas | Install pandas into the same Python environment with python -m pip install pandas. |
| Missing required columns | Check the CSV header before changing the expected schema. |
| Date parsing error | Confirm that every order_date uses YYYY-MM-DD. |
| Numeric conversion error | Inspect units and amount for text, currency symbols, or separators that require an explicit cleanup rule. |
| Duplicate order_id found | Determine whether the repeated ID is an accidental duplicate or represents a different data model. |
| FileExistsError | Review the previous result and use a fresh output folder rather than overwriting it. |
Matching grand totals do not prove that every month is correct because two monthly errors can offset each other. For important reports, also inspect several known source rows and month-level subtotals.
The example assumes one row per order and whole-number amounts. If one order spans several line-item rows, use a rule such as order_id.nunique() instead of row count. Real monetary data may also need fixed-point integer units or Decimal-based processing instead of ordinary floating-point arithmetic.
Month is derived directly from a date with no time-zone handling. If real data contains timestamps from several zones, define the business time zone before grouping. Refunds, cancellations, filters, and missing records also need explicit business rules; arithmetic reconciliation alone cannot determine whether those rows belong in the report.
ChatGPT Python tool · Python 3.13.5 · pandas 2.2.3
The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.