Back to the lessonLesson 7 of 8Producing and handing over

Dates, ages and reporting periods

The same deck as the downloads, rendered as a page. Start the slideshow to present it full screen — arrow keys or a click advance one slide, Escape leaves.

Slides · PDF Slides · PowerPoint

Slide 1 / 24
What this lesson covers
- Why this gets its own lesson
- Parsing, once, explicitly
- Age in completed months
- Reporting periods
- Resampling a time series
- Durations
- Time zones, briefly
- What comes next
Speaker notes
Age in completed months computed correctly rather than by dividing days, reporting periods that survive a partial month, and the boundary arithmetic a growth standard depends on.
Slide 2 / 24
Why this gets its own lesson
- Date arithmetic looks like the easy part and produces some of the most expensive errors in this sector.
Speaker notes
Date arithmetic looks like the easy part and produces some of the most expensive errors in this sector. An age in months decides which growth standard a child is compared against. A reporting period decides whether a facility counts as having reported. A month boundary decides whether a distribution falls in this quarter's figures or next. None of those fail loudly.

Slide 3 / 24

Parsing, once, explicitly — In Python

import pandas as pd

muac = pd.read_csv(path)
muac["screening_date"] = pd.to_datetime(
    muac["screening_date"], format="%Y-%m-%d", errors="raise"
)

Slide 4 / 24
Parsing, once, explicitly
- State the format. An inferred format can change between files as the mix of values changes — the same column parsed…
- Use errors="raise". The alternative, errors="coerce", turns every unparseable date into missing, so a file in…
Speaker notes
Two habits worth forming: 01/02/2024 is either 1 February or 2 January and the file will not tell you which. If an export gives you ambiguous dates, dayfirst=True is a decision you document, not a default you accept.
Slide 5 / 24
Age in completed months — In Python
```
# Wrong often enough to matter.
age_months = ((visit_date - date_of_birth).dt.days / 30.4375).astype(int)
```
Speaker notes
The obvious approach divides days by an average month:
Slide 6 / 24
Age in completed months — In Python
```
def age_in_months(dob: pd.Series, visit: pd.Series) -> pd.Series:
    months = (visit.dt.year - dob.dt.year) * 12 + (visit.dt.month - dob.dt.month)
    # Not yet reached the day-of-month, so the last month is not complete.
    return months - (visit.dt.day < dob.dt.day).astype(int)
```
Speaker notes
Over the first two thousand days of life this disagrees with the correct answer on 54 of them — always by one month, always at a boundary. A child at 60 days is reported as 1 month old when they are 2. Compute completed months directly:

Slide 7 / 24

Age in completed months — In Python

dob   = pd.to_datetime(["2022-03-15", "2022-03-16", "2022-02-28"])
visit = pd.to_datetime(["2024-03-15", "2024-03-15", "2024-03-01"])

age_in_months(pd.Series(dob), pd.Series(visit)).tolist()   # [24, 23, 24]

Slide 8 / 24
Age in completed months
- Why one month matters here
- WHO growth standards switch measurement at exactly 24 months — Below 24 months a child is measured lying down and…
- Age bands are the other place — 0-5, 6-23, 24-59 months are the standard nutrition bands, and a child at exactly…
Speaker notes
The second child is one day short of two years and is 23 months, not 24. That single day is the whole reason this function exists. WHO growth standards switch measurement at exactly 24 months. Below 24 months a child is measured lying down and compared against the weight-for-length curve; at 24 months and above, standing, against weight-for-height. The two measurements differ by about 0.7 cm and the two curves are different tables. A child pushed from 23 to 24 months by a rounding rule is compared against the wrong standard, and their z-score moves. Do that to a few hundred children near the boundary and the prevalence estimate moves with them — which is why the SMART survey analysis project reports age heaping on whole years as a plausibility finding rather than a curiosity. Age bands are the other place. 0-5, 6-23, 24-59 months are the standard nutrition bands, and a child at exactly 24 months belongs to the third:

Slide 9 / 24

Age in completed months — In Python

BANDS = [0, 6, 24, 60]
LABELS = ["0-5m", "6-23m", "24-59m"]

muac["age_band"] = pd.cut(
    muac["age_months"], bins=BANDS, labels=LABELS, right=False, include_lowest=True
)

Slide 10 / 24
Age in completed months — In Python
```
check = muac.loc[muac["age_months"].isin([5, 6, 23, 24, 59, 60]),
                 ["age_months", "age_band"]].drop_duplicates().sort_values("age_months")
print(check)
```
Speaker notes
right=False makes each interval [low, high) — closed on the left, open on the right — so 24 months falls in 24-59m and not in 6-23m. The default is the other way round, and it is wrong for every age band this sector uses. Check the boundaries rather than trusting them:
Slide 11 / 24
Reporting periods — In Python
```
muac["month"] = muac["screening_date"].dt.to_period("M")

by_month = muac.groupby("month").size()
```
Speaker notes
A period is a month, quarter or year that data is reported for, and it is not the same as a date.

Slide 12 / 24

Reporting periods — Example

2024-01    207
2024-02    391
...
2024-11    375
2024-12    156

Slide 13 / 24
Reporting periods — In Python
```
current = pd.Period("2024-06", freq="M")
print(current - 1)                 # 2024-05
print(current.start_time, current.end_time)
```
Speaker notes
Periods sort correctly, print readably, and — unlike a string like "2024-01" — support arithmetic:
Slide 14 / 24
Reporting periods
- Partial periods are the trap
Speaker notes
Strings sort correctly too if you use ISO order, which is a good reason to use %Y-%m and never %m/%Y. But a string cannot answer "the previous month", and that question comes up in every trend calculation. This register runs from 15 January to 13 December. January carries 207 screenings and December 156, against a monthly average near 375. Neither month is a decline; both are partial.

Slide 15 / 24

Reporting periods — In Python

first, last = muac["screening_date"].min(), muac["screening_date"].max()
print(first.date(), last.date())

muac["partial_period"] = muac["month"].isin(
    [first.to_period("M"), last.to_period("M")]
)

Slide 16 / 24
Reporting periods
- Label them rather than dropping them — A reader who sees December fall off a chart concludes the programme collapsed; a…
- Aligning two sources on periods
Speaker notes
Label them rather than dropping them. A reader who sees December fall off a chart concludes the programme collapsed; a reader who sees no December at all wonders where the data went. Naming the month partial is the only option that answers both. The same logic applies to a period that has not finished. A month-to-date figure compared against completed months is the commonest way a dashboard shows a fictional decline every month, and it happens because both are called "the monthly total". The vaccination extract records a period start date rather than a date of service:
Slide 17 / 24
Reporting periods — In Python
```
epi = pd.read_csv(path, parse_dates=["period"])
epi["month"] = epi["period"].dt.to_period("M")
print(epi["month"].nunique())      # 12
```
Speaker notes
Converting both sources to Period before joining is what makes them line up. Joining a date to a period-start silently produces no matches for every month where one source used the last day and the other the first.
Slide 18 / 24
Resampling a time series — In Python
```
daily = muac.set_index("screening_date")
weekly = daily.resample("W")["child_id"].size()
monthly = daily.resample("MS")["child_id"].size()      # MS = month start
```
Speaker notes
When the index is a datetime, resample aggregates by period without a manual grouping column: resample fills gaps: a week with no screening appears as zero rather than being absent. That is right for a time series and wrong for a denominator — a school closed for a fortnight has no attendance days, not zero attendance, and the School attendance project shows what filling those in costs.

Slide 19 / 24

Durations — In Python

referrals["days_to_service"] = (
    referrals["service_date"] - referrals["referral_date"]
).dt.days

Speaker notes

Subtracting two datetimes gives a Timedelta:

Slide 20 / 24
Durations — In Python
```
hours = (referrals["service_date"] - referrals["referral_date"]) / pd.Timedelta(hours=1)
referrals["same_day"] = hours < 24
```
Speaker notes
.dt.days truncates toward zero, so a 23-hour gap is 0 days. If same-day matters — and for a protection referral it does — say so explicitly rather than letting the truncation decide:
Slide 21 / 24
Durations — In Python
```
impossible = referrals["days_to_service"] < 0
print(f"service recorded before referral: {impossible.sum()}")
```
Speaker notes
A negative duration is a data-quality finding, not a value to take an absolute value of:
Slide 22 / 24
Time zones, briefly — In Python
```
submissions["submitted_at"] = pd.to_datetime(
    submissions["submitted_at"], utc=True
).dt.tz_convert("America/Port-au-Prince")

submissions["submission_date"] = submissions["submitted_at"].dt.date
```
Speaker notes
Programme data is usually naive local dates and should stay that way. Attaching a time zone to a screening date invites a conversion that moves a record across a midnight boundary into the wrong reporting month. Where timestamps genuinely carry a zone — server-side form submission times from CommCare or Kobo — convert once, at the boundary, and store the local date: The submission timestamp and the date of the visit it records are different things, and reporting on the first when you mean the second shifts every evening submission into the following day.
Slide 23 / 24
What comes next
- Every column now means what it says, including the ones made of dates.
Speaker notes
Every column now means what it says, including the ones made of dates. The last lesson turns the analysis into a script another officer can run next quarter on a different export, without editing anything but its arguments.
Slide 24 / 24
Where this goes next
Read the full lesson, with runnable code Back to the lesson

What this lesson covers

Why this gets its own lesson

Parsing, once, explicitly — In Python

Parsing, once, explicitly

Age in completed months — In Python

Age in completed months — In Python

Age in completed months — In Python

Age in completed months

Age in completed months — In Python

Age in completed months — In Python

Reporting periods — In Python

Reporting periods — Example

Reporting periods — In Python

Reporting periods

Reporting periods — In Python

Reporting periods

Reporting periods — In Python

Resampling a time series — In Python

Durations — In Python

Durations — In Python

Durations — In Python

Time zones, briefly — In Python

What comes next

Where this goes next