Which week is January 1 in? ISO weeks in pandas and DuckDB
Label weeks that cross into a new year without splitting them in two: with the ISO week and its ISO year, or with the date of the week's Monday.
Free course. Your Maestro, an AI, .
The weekly report
Café Altura reviews its sales every Monday. With months, nobody argues about where each one starts, but with weeks they do: does the week start on Sunday or on Monday? Which one is week 1 of the year? January 1, 2026 was a Thursday. Is it in week 1 of 2026, or the last week of 2025?
Make a prediction with Python before you read the rule.
Two programs about the week of New Year's 2025. Write what you think each one prints and why.
Two programs about the week of New Year's 2025. Write what you think each one prints and why.
isocalendar for four dates
Example 1 of 2
from datetime import date
for d in [date(2025, 12, 29), date(2026, 1, 1), date(2026, 1, 4), date(2026, 1, 5)]:
print(d, d.isocalendar())
A guess is fine: it's kept as you wrote it, so you can compare. Only you and your Maestro see your predictions.
Example 2 of 2, opens once you explain this one
The ISO rule
The ISO 8601 standard defines weeks like this: they start on Monday, and week 1 of a year is the one that contains the year's first Thursday. Put another way, the one that contains January 4. The Python documentation says: "The first week of an ISO year is the first (Gregorian) calendar week of a year containing a Thursday."
Two surprising things follow from that:
- Some December days can belong to week 1 of the next year, and some January days can belong to week 52 or 53 of the previous year. January 1, 2027 is in week 53 of 2026.
- That's why the ISO week has its own year, the ISO year. A week label needs the ISO year, not the calendar year. The DuckDB documentation warns about this:
%Vis incompatible with%Y; use%Ginstead.
If you're used to US calendars, which usually start the week on Sunday, check which convention your report uses before you compare it with ISO weeks.
In ventas_diarias the mistake has real consequences. Labeling with %Y-W%V splits the week of December 29 into two groups: 2025-W01 with three December days (110,910 pesos) and 2026-W01 with the January days (128,150 pesos). That week actually sold 239,060 pesos. If you can't see why it splits in two, ask your Maestro.
Start the week on Monday
It's the ISO rule, and it's what
date_trunc('week', ...)uses in DuckDB andisocalendar()uses in Python. If your company counts Sunday to Saturday, say so in the report.Give it the ISO year
A week is named by its ISO year and ISO week:
%G-W%Vin a format string,isoyearandweekin DuckDB,yearandweekfromisocalendar()in pandas.Or name it by its Monday
The label that's hardest to misread is the date of the Monday the week starts on:
2025-12-29. It sorts correctly and anyone can understand it.
iso = data["fecha"].dt.isocalendar() # columns year, week, day
data["semana"] = data["fecha"].dt.to_period("W").dt.start_timeto_period("W") uses Monday-to-Sunday weeks; start_time gives you the Monday. For January 4, 2026 it gives 2025-12-29.
SELECT
date_trunc('week', fecha)::DATE AS semana,
strftime(fecha, '%G-W%V') AS semana_iso
FROM ventas_diarias;date_trunc('week', ...) moves each date back to its Monday. isoyear(fecha) and week(fecha) give the ISO year and week as numbers.
Try it
The query adds up sales for three weeks around New Year's with the %Y-W%V label, and one of the weeks comes out split in two. Change it so each week is a single group: a semana (week) column with the date of its Monday and a semana_iso column with %G-W%V. A good answer has three rows, and the one for 2025-12-29 adds up to 239,060. If your weeks at work run Sunday to Saturday, talk with your Maestro about how to label them so nobody confuses them with ISO weeks.
Add up sales by week from December 22, 2025 to January 11, 2026, with semana as the date of the Monday and semanaiso with the ISO year.
Your turn
Add up sales by week from December 22, 2025 to January 11, 2026, with semana as the date of the Monday and semana_iso with the ISO year.
Tables: ventas_diarias (fecha, sucursal, ventas)
ventas_diarias · 1,598 rows
| Column | Type |
|---|---|
| fecha El día de la venta. | DATE |
| sucursal Roma, Coyoacán o Del Valle. | VARCHAR |
| ventas Ventas del día en pesos. | BIGINT |
First rows
| fecha | sucursal | ventas |
|---|---|---|
| 2025-01-02 | Roma | 12120 |
| 2025-01-02 | Coyoacán | 10650 |
| 2025-01-02 | Del Valle | 8880 |
Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your query.
Try it on your own
Now in Python, with no data: print the ISO week label (2026-W53, for example) for December 28, 2026, January 1, 2027, and January 4, 2027. Before you run it, think about which ones share a week. A good answer gives the same week for the first two dates and a different one for the third.
Print the ISO week, with its ISO year, for December 28, 2026, January 1, 2027, and January 4, 2027.
Print the ISO week, with its ISO year, for December 28, 2026, January 1, 2027, and January 4, 2027.
Run your code to see what it prints and the value of its last expression. Every run starts fresh. Ctrl+Enter runs it. Tab indents; to leave the editor, press Escape, then Tab.
Ready to run your code.
Run your code to see its output here.