Group Excel Rows by Month and Quarter with pandas

Almost every recurring Excel report is the same shape: a sheet of daily transactions in, a table of monthly or quarterly totals out. pandas does this in one expression, but three details separate a summary that is correct from one that quietly misleads — periods with no rows disappearing, fiscal years that do not start in January, and month labels that sort alphabetically so October comes before September. This guide covers the grouping mechanics and the write-back. It is the aggregation companion to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl xlsxwriter

A sample workbook to work against, with a deliberate gap in July so the gap-filling section has something to demonstrate:

import pandas as pd

sales = pd.DataFrame({
    "date": pd.to_datetime([
        "2026-06-03", "2026-06-19", "2026-06-28",
        "2026-08-02", "2026-08-15", "2026-09-07", "2026-09-30",
    ]),
    "region": ["North", "South", "North", "West", "North", "South", "West"],
    "amount": [159.92, 247.50, 137.44, 412.10, 96.35, 188.00, 301.75],
})
sales.to_excel("sales.xlsx", index=False)

Step 1 — Read and make sure the column is really a date

Every grouping technique below fails on an object column, usually with a message about the key not being datetime-like. Convert once, at the top:

import pandas as pd

df = pd.read_excel("sales.xlsx")
df["date"] = pd.to_datetime(df["date"], errors="coerce")

missing = df["date"].isna().sum()
if missing:
    print(f"warning: dropping {missing} rows with an unparseable date")
    df = df.dropna(subset=["date"])

Dropping unparseable rows silently is how a monthly total ends up understated. Print the count, or better, route them to a rejects file — the full treatment is in parsing Excel dates with pandas.

Step 2 — Group by month

Two tools, two purposes. Grouper produces a real timestamp key:

monthly = (
    df.groupby(pd.Grouper(key="date", freq="MS"))
      .agg(revenue=("amount", "sum"), orders=("amount", "size"))
)
print(monthly)
#             revenue  orders
# date
# 2026-06-01   544.86       3
# 2026-07-01     0.00       0
# 2026-08-01   508.45       2
# 2026-09-01   489.75       2

Note that Grouper does emit the empty July here, because it builds a continuous range between the first and last date. That is a genuine difference from grouping on a derived label, which does not:

df["month"] = df["date"].dt.to_period("M")
by_label = df.groupby("month")["amount"].sum()
print(by_label)
# month
# 2026-06    544.86
# 2026-08    508.45      <- July is simply absent
# 2026-09    489.75

The frequency aliases you will use most:

Prefer the start aliases (MS, QS, YS) for report keys. A month-end key of 2026-06-30 sorts identically but reads worse in a chart axis, and it makes joining against other month-keyed tables fiddly because not every system agrees on which end of the month labels it.

Step 3 — Fill the periods that have no rows

A missing month is the difference between "we sold nothing in July" and "July is not in this report". Only one of those is visible to a reader.

monthly = (
    df.groupby(pd.Grouper(key="date", freq="MS"))
      .agg(revenue=("amount", "sum"), orders=("amount", "size"))
      .asfreq("MS", fill_value=0)
)

asfreq fills gaps inside the observed range. To cover a fixed reporting window regardless of what the data contains — the usual requirement for a monthly report that must always show twelve rows — reindex against an explicit range instead:

import pandas as pd

window = pd.date_range("2026-01-01", "2026-12-01", freq="MS")

monthly = (
    df.groupby(pd.Grouper(key="date", freq="MS"))
      .agg(revenue=("amount", "sum"), orders=("amount", "size"))
      .reindex(window, fill_value=0)
)
monthly.index.name = "month"
print(len(monthly))     # 12, always

The distinction matters for charts especially. A line chart drawn from a series with a missing month connects straight across the gap, implying a smooth trend through a period where nothing happened — see adding a line chart to an Excel report for the plotting side.

Step 4 — Quarters, weeks and fiscal years

Calendar quarters are a frequency change and nothing more:

quarterly = (
    df.groupby(pd.Grouper(key="date", freq="QS"))
      .agg(revenue=("amount", "sum"), orders=("amount", "size"))
      .asfreq("QS", fill_value=0)
)

Fiscal years need an anchor. A year ending 31 March is Q-MAR, and the anchor names the month the fiscal year ends in:

import pandas as pd

df["fiscal_quarter"] = df["date"].dt.to_period("Q-MAR")

print(df.loc[df["date"] == "2026-06-03", "fiscal_quarter"].iloc[0])
# 2027Q1   — June 2026 is Q1 of the fiscal year ending March 2027

fiscal = (
    df.groupby("fiscal_quarter")["amount"]
      .agg(revenue="sum", orders="size")
      .sort_index()
)

Weeks carry their own convention question — which day starts the week:

# ISO weeks start on Monday; W-SUN if your business week starts Sunday.
weekly = (
    df.groupby(pd.Grouper(key="date", freq="W-MON", label="left"))["amount"]
      .sum()
)

label="left" makes the key the Monday that starts the week rather than the one that ends it, which is what most people expect when they read a weekly report.

Step 5 — Group by period and another column

Real reports want a region-by-month grid, not a single series. Add the second key and unstack:

grid = (
    df.groupby([pd.Grouper(key="date", freq="MS"), "region"])["amount"]
      .sum()
      .unstack("region", fill_value=0)
      .reindex(pd.date_range("2026-06-01", "2026-09-01", freq="MS"), fill_value=0)
)
grid.index.name = "month"
print(grid)

That is a pivot table in all but name, and if the output is destined for a spreadsheet a native pivot is often the better shape — see creating pivot tables from Excel data.

Step 6 — Write the summary back as a report

Keep the index as timestamps until the moment you write, so sorting stays correct, then let the number format handle the display:

import pandas as pd

out = monthly.reset_index().rename(columns={"index": "month"})

with pd.ExcelWriter("monthly_report.xlsx", engine="xlsxwriter") as writer:
    out.to_excel(writer, sheet_name="Monthly", index=False)

    book, sheet = writer.book, writer.sheets["Monthly"]
    month_fmt = book.add_format({"num_format": "mmm yyyy"})
    money = book.add_format({"num_format": "#,##0.00"})
    header = book.add_format({"bold": True, "bg_color": "#EEF2FF", "border": 1})

    for col, name in enumerate(out.columns):
        sheet.write(0, col, name, header)

    sheet.set_column("A:A", 12, month_fmt)
    sheet.set_column("B:B", 14, money)
    sheet.set_column("C:C", 10)
    sheet.freeze_panes(1, 0)

Writing the month as a formatted timestamp rather than the string "2026-08" is what keeps Excel's own sorting and filtering working. A text month column sorts alphabetically, which puts April first and October before September — the single most common complaint about generated period reports.

Common pitfalls and fixes

Performance and scale notes

Grouping is fast; getting to a groupable column is where time goes. On a workbook of a million rows, parsing dominates — so parse once with an explicit format, as covered in the parsing guide, and never inside a loop.

Three habits that matter at scale:

Read only the columns you aggregate. A summary over date and amount has no reason to materialise thirty other columns:

df = pd.read_excel("sales.xlsx", usecols=["date", "amount", "region"])

Prefer named aggregation to apply. The named form dispatches to vectorised C implementations; a lambda runs Python per group:

# Fast — one vectorised pass per statistic.
summary = df.groupby(pd.Grouper(key="date", freq="MS")).agg(
    revenue=("amount", "sum"),
    orders=("amount", "size"),
    largest=("amount", "max"),
)

Aggregate chunk by chunk for files that do not fit in memory. Monthly sums are additive, so partial results combine cleanly:

import pandas as pd

totals = None
for chunk in pd.read_csv("huge_export.csv", parse_dates=["date"],
                         usecols=["date", "amount"], chunksize=200_000):
    part = chunk.groupby(pd.Grouper(key="date", freq="MS"))["amount"].sum()
    totals = part if totals is None else totals.add(part, fill_value=0)

monthly = totals.sort_index().asfreq("MS", fill_value=0)

That pattern works for sums, counts, minimums and maximums. Means need care — accumulate the sum and the count separately and divide at the end, rather than averaging the chunk averages, which weights small chunks equally with large ones.

Conclusion

Grouping Excel rows into periods is a one-liner surrounded by three decisions. Use Grouper with a start-anchored frequency for report keys and to_period for display labels. Fill the empty periods explicitly, with asfreq for the observed range or reindex for a fixed reporting window, so a quiet month reads as zero rather than disappearing. Anchor fiscal quarters with the month the fiscal year ends in. Then write the period column back as a real timestamp with a mmm yyyy format, so Excel's own sorting keeps working for whoever opens the file.

Frequently asked questions

What is the difference between to_period and Grouper?to_period produces a Period label such as 2026-08, which is compact and reads well in a report column. Grouper with freq="MS" produces a real Timestamp at the start of each month, which sorts correctly and joins with other date-keyed data. Use to_period for display and Grouper for keys.

Why are months with no data missing from my summary?groupby only emits groups that exist. Call asfreq("MS", fill_value=0) or reindex against a full date_range afterwards so quiet months appear as zero instead of vanishing.

How do I group by a fiscal year that ends in March? Use the anchored frequency Q-MAR with to_period, or the offset alias "QE-MAR" with Grouper. April then falls into the first quarter of the following fiscal year, which is what accounting expects.

Should I use resample instead of groupby?resample is groupby with a datetime index and gap filling built in. It is the cleaner choice for a single continuous series; groupby with Grouper is better when you are also grouping by another column such as region.

My month column sorts alphabetically — how do I fix it? The column is text. Sort on the underlying Period or Timestamp before converting to a string for display, or keep the Period dtype until the moment you write the file.

Related

• Up to the parent: Working with Dates and Times in Excel Data — the date model behind the grouping keys.
• Parse Excel Dates into Python datetimes with pandas — getting the column groupable in the first place.
• Create a Pivot Table from Excel with pandas — the two-dimensional version of this summary.
• Add a Line Chart to an Excel Report with Python — plotting the series, gaps included.
• Schedule Recurring Excel Reports with APScheduler — running this summary every month unattended.