How to Group Sales by Month in Python with pandas

Group sales by a full year-month key, then fill missing reporting months explicitly. In pandas, a monthly period distinguishes January 2025 from January 2026. Grouping by the month number alone would combine them.

The hypothetical example below includes a refund, a year boundary, and a month with no rows. It was executed with Python 3.11.3 and pandas 2.3.3. Values are stored as whole cents so the aggregation does not introduce binary decimal rounding.

Use a clearly defined amount and date

These five rows represent signed transaction amounts on reporting dates. A negative amount is a refund. They are not a statement about accounting recognition, taxes, or the correct reporting period for a particular business.

Run the complete example

from io import StringIO
import pandas as pd

sales = pd.read_csv(StringIO("""order_id,date,amount_cents
A001,2025-12-31,12500
A002,2026-01-02,9000
A003,2026-01-18,-2000
A004,2026-03-07,15000
A005,2026-03-08,5000
"""), dtype={"order_id": "string", "amount_cents": "int64"})
sales["date"] = pd.to_datetime(sales["date"], format="%Y-%m-%d", errors="raise")
if sales["date"].isna().any():
    raise ValueError("Every sale needs a valid reporting date.")
sales["month"] = sales["date"].dt.to_period("M")
monthly = sales.groupby("month")["amount_cents"].sum().sort_index()
all_months = pd.period_range(monthly.index.min(), monthly.index.max(), freq="M")
monthly = monthly.reindex(all_months, fill_value=0)

def dollars(cents):
    cents = int(cents)
    return ("-" if cents < 0 else "") + f"${abs(cents) // 100}.{abs(cents) % 100:02d}"

report = monthly.rename("amount_cents").to_frame()
report["display"] = report["amount_cents"].map(dollars)
print(report.to_string())
print("Total:", dollars(monthly.sum()))

Reconcile the monthly totals

The resulting report has December 2025 at $125.00, January 2026 at $70.00, February 2026 at $0.00, and March 2026 at $200.00. Their combined total is $395.00, equal to the sum of the five signed transaction amounts.

January is $90.00 minus the $20.00 refund, not $110.00. Keeping signs throughout the calculation is essential; formatting a negative number differently must not change its contribution.

Keep the year with the month

dt.to_period("M") converts a parsed date to its monthly period, such as 2026-01. Sorting these periods gives chronological order. A group key based only on dt.month would combine the same month across different years.

The date strings use an explicit year-month-day format, and invalid strings raise an exception. The separate missing-date check prevents undated rows from quietly disappearing from a grouping. If your source contains timestamps rather than dates, convert them to the chosen reporting time zone before assigning months.

A missing month needs an interpretation

period_range creates every month between the first and last observed month. Reindexing fills the absent February with zero. This is appropriate only if the source is complete and no rows really means no activity.

If an export failed or a month is incomplete, a blank or explicit missing-data flag is more honest than zero. The code cannot infer that distinction from an absent row. An empty dataset also needs a defined reporting range instead of deriving endpoints from observations.

Retain usable numbers

The report keeps the integer-cent column alongside the display string. Use the numeric column for further calculations and the formatted column for presentation. For much larger datasets, check integer ranges and total magnitudes before aggregation.

When the source spans multiple exports, combine and validate them before grouping. MathBlog’s CSV combination guide covers stacking rows while retaining source information.