01Brief11 / 12

Every month, including the quiet ones

monthly_totals(frame) takes a frame of transactions — a date as text, an amount — and returns the total per calendar month, with every month between the first and the last present even if nothing happened in it.

Function	monthly_totals()
Track	Tables with pandas
Difficulty
Time	~30 min
Concepts	to_datetime and the .dt accessor grouping by a period reindex puts back the rows the data never had
Cases	11
Needs	pandas about 9 MB, because pandas brings numpy with it, fetched once and then cached by your browser

02The contract

Return a dict of 'YYYY-MM' to the total amount for that month, rounded to 2 places.
The date column arrives as ISO text and may be in any row order.
Every month from the earliest to the latest appears, including months with no rows, which total 0.0.
Keys come back in chronological order.
A row whose date cannot be read is excluded from the totals and does not widen the range.
An empty frame gives an empty dict.
The caller’s frame is not modified.

03Why this one

A date column read from a file is text until you convert it, and text months group by whatever the string happens to look like. Convert it and pandas gives you a whole calendar vocabulary through .dt — but grouping still only produces the months that had rows in them, which means a month where nothing happened is missing rather than zero. A chart drawn from that skips straight from March to May, and the line looks continuous.

—The concept behind it

Dictionaries on pythonmastery.io →

That is the reading half. Read it there if the idea is new, then come back here — explanation is what makes something make sense, and practice is what makes it stay.

—Worth doing first

One row per channel, and a cost per click

Not a gate — start this one whenever you like. These cover ideas it leans on, and a tick means you have already done them.

04Study first: from a string to a month, and back to a key

Four steps, each of which can be skipped without an error: convert, take the month, group, and put the empty months back.

import pandas as pd
when = pd.Series(['2026-01-15', '2026-03-02', '2026-01-31'])
when.dtype                  # -> object   still text

stamps = pd.to_datetime(when)
stamps.dtype                # -> datetime64[ns]

# .dt is the accessor for everything calendar-shaped:
stamps.dt.year.tolist()     # -> [2026, 2026, 2026]
stamps.dt.month.tolist()    # -> [1, 3, 1]      an int, so 1 sorts before 10
stamps.dt.to_period('M').astype(str).tolist()
# -> ['2026-01', '2026-03', '2026-01']   a month as a first-class value

# Grouping by it gives only the months that had rows:
amounts = pd.Series([1.0, 2.0, 3.0])
totals = amounts.groupby(stamps.dt.to_period('M')).sum()
# 2026-01    4.0
# 2026-03    2.0        February is not missing — it was never there

# period_range builds the calendar, and reindex lays the data over it:
months = pd.period_range('2026-01', '2026-03', freq='M')
totals.reindex(months, fill_value=0.0)
# 2026-01    4.0
# 2026-02    0.0
# 2026-03    2.0

# And what an unreadable date becomes:
pd.to_datetime(pd.Series(['2026-13-01']), errors='coerce')
# -> [NaT]     'not a time' — NaN's cousin, and just as quiet

A period is a month, not a moment
to_period('M') turns a timestamp into a value whose identity is the month itself — so two dates in January are equal, grouping by it needs no string formatting, and sorting it sorts chronologically. Grouping by .dt.strftime('%Y-%m') gets you the same keys and throws the type away, which is fine until you need the month *before* the first one and find yourself doing arithmetic on text.
A groupby can only produce keys the data contains
This is the same shape of bug as a breakdown that drops rows with no key, seen from the other side: nothing is lost, but something that should be there is absent. It matters most in time series, because a reader assumes the rows are consecutive — a bar chart with no February simply looks like a chart where February was a bad month, right up until somebody counts the bars.
reindex is 'make the index be this', and fill_value decides the gaps
Given an index you build yourself — from period_range, or a list of the categories you expect — reindex keeps the values whose labels match, inserts fill_value where the new index has a label the data does not, and drops anything the new index leaves out. That last part is the one to watch: reindexing onto too narrow a range silently discards data.

your_code.py

05One way to write it

import pandas as pd


def monthly_totals(frame):
    # coerce: an unreadable date becomes NaT rather than taking the whole
    # frame down with it.
    stamps = pd.to_datetime(frame["when"], errors="coerce")
    months = stamps.dt.to_period("M")

    # Two things are excluded here, and both have to be: the rows whose date
    # would not parse, and the case where that leaves nothing at all.
    readable = months.notna()
    if not readable.any():
        return {}

    # Grouping the amounts by the month Series — the frame itself is never
    # touched, and the mask lines up because they share an index.
    totals = frame.loc[readable, "amount"].groupby(months[readable]).sum()

    # The calendar the data should have had, and the data laid over it.
    calendar = pd.period_range(
        totals.index.min(), totals.index.max(), freq="M"
    )
    totals = totals.reindex(calendar, fill_value=0.0)

    return {
        str(month): round(float(value), 2)
        for month, value in totals.items()
    }

reindex, not fillna, for a row that was never there

fillna can only fill a cell that exists; February has no cell at all. The two are answers to different questions — fillna is 'this row has no value', reindex is 'this row is missing' — and reaching for the wrong one is why gap-filling code so often ends up as a loop over a manually built list of months. Build the index you want and reindex onto it.

The range comes from the data, and that is a decision

period_range over the first and last month present means a quiet month at the start or the end of the report simply does not appear — the range shrinks to fit. Sometimes that is right; often what a caller wants is the current quarter, or the last twelve months, regardless of when the transactions happened. Passing the range in as an argument is the version that scales, and it turns this function’s one hidden assumption into a visible one.

str(period) gives 'YYYY-MM' without any formatting

A monthly Period’s string form is already the key most reports want, and it is zero-padded, so it sorts as text as well as chronologically. That is why the conversion to strings can wait until the very last line: everything before it gets to work with real month objects, which subtract, compare and range properly, and the text only appears where the data leaves pandas.

06When not to write this at all

For a frame with a DatetimeIndex — which is what a time series usually is — resample('MS') does the grouping and the gap-filling in one call, and it is the idiom to reach for there; this version exists because the dates are an ordinary column and the index is a row number. errors='coerce' is also the wrong default for many pipelines: silently dropping unparseable dates is right for a report that is meant to survive messy input and wrong for an import that should refuse a broken file. And if the unreadable rows matter — they have amounts in them, after all — returning a count of them alongside the totals is a small change that stops the report quietly under-stating its own total.

07Take it further

Take first_month and last_month arguments so the report covers a fixed window, and see what reindex does with a range narrower than the data.
Report the count of unreadable rows and their total alongside the monthly figures.
Do it by week instead of by month, and then by quarter — changing only the freq.