Level 7 · Why don't the targets line up?

A label is a date

resample groups rows into periods, but the result is still indexed by dates, one per period. Which date stands for “January 2025” depends on the frequency alias:

by_start = orders.set_index("order_date").resample("MS").size()
by_end = orders.set_index("order_date").resample("ME").size()
by_start.index[0]   # Timestamp('2023-01-01')
by_end.index[0]     # Timestamp('2023-01-31')

The counts are identical (192 orders in January 2023 either way). Only the labels differ. For pandas, 2023-01-01 and 2023-01-31 are simply two different dates, nothing more.

Where it bites: merges

Most monthly tables you’ll join against (targets, budgets, ledger exports) use the first day of the month. A merge compares key values exactly, so month-end labels match none of them:

targets.merge(monthly, left_on="month", right_on="order_date", how="left")
# n_orders is NaN on every row

A left join never errors when keys don’t match; it fills the right-hand columns with NaN. With an inner join you’d get an empty table instead. Both are easy to miss when you’re working fast.

Two fixes

Label months the way the other table does:

monthly = orders.set_index("order_date").resample("MS").size()

Or remove the day from both sides by converting dates to monthly periods. A period is a whole month rather than one day of it, so 2023-01-01 and 2023-01-31 both become 2023-01:

targets["month"] = targets["month"].dt.to_period("M")
monthly.index = monthly.index.to_period("M")

Periods are also what you want when the other table stores months as text like "2025-01": convert that with pd.PeriodIndex(..., freq="M") or turn your periods into text with .astype(str).

Check the keys before trusting a join

A quick habit catches this whole class of bug: before merging, print the first few keys of both sides, and after a left merge, count the NaN in a column that should always be filled. “Every row empty” is almost never real data; it’s nearly always mismatched keys, and the same goes for weekly data, where "W-SUN" labels a week with its Sunday and other systems often use the Monday.

Common mistakes

• Mixing "ME" and "MS" between two tables that will be joined.
• Trusting a left merge because it raised no error.
• Comparing a datetime column with month text such as "2025-01"; convert one side first.
• Forgetting that weeks have labels too: "W-SUN" and "W-MON" give the same weeks with different end days.