Dates, ages and reporting periods

Why this gets its own lesson

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.

Parsing, once, explicitly

import pandas as pd

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

Two habits worth forming:

• State the format. An inferred format can change between files as the mix of values changes — the same column parsed as day-first in one export and month-first in the next.
• Use errors="raise". The alternative, errors="coerce", turns every unparseable date into missing, so a file in the wrong date format imports “successfully” with an entirely empty date column.

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.

Age in completed months

The obvious approach divides days by an average month:

# Wrong often enough to matter.
age_months = ((visit_date - date_of_birth).dt.days / 30.4375).astype(int)

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:

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)

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]

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.

Why one month matters here

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:

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
)

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:

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)

Reporting periods

A period is a month, quarter or year that data is reported for, and it is not the same as a date.

muac["month"] = muac["screening_date"].dt.to_period("M")

by_month = muac.groupby("month").size()

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

Periods sort correctly, print readably, and — unlike a string like "2024-01" — support arithmetic:

current = pd.Period("2024-06", freq="M")
print(current - 1)                 # 2024-05
print(current.start_time, current.end_time)

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.

Partial periods are the trap

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.

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")]
)

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”.

Aligning two sources on periods

The vaccination extract records a period start date rather than a date of service:

epi = pd.read_csv(path, parse_dates=["period"])
epi["month"] = epi["period"].dt.to_period("M")
print(epi["month"].nunique())      # 12

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.

Resampling a time series

When the index is a datetime, resample aggregates by period without a manual grouping column:

daily = muac.set_index("screening_date")
weekly = daily.resample("W")["child_id"].size()
monthly = daily.resample("MS")["child_id"].size()      # MS = month start

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.

Durations

Subtracting two datetimes gives a Timedelta:

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

.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:

hours = (referrals["service_date"] - referrals["referral_date"]) / pd.Timedelta(hours=1)
referrals["same_day"] = hours < 24

A negative duration is a data-quality finding, not a value to take an absolute value of:

impossible = referrals["days_to_service"] < 0
print(f"service recorded before referral: {impossible.sum()}")

Time zones, briefly

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:

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

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.

What comes next

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.