Work with dates, time, gaps and repeated observations

An ordered list of dates is not automatically a complete time series. Time-based investigations require timestamp extraction, grouped counts, rolling summaries and carefully defined change calculations. This lesson develops those transferable skills using small transparent examples and a documented institutional snapshot. You should understand grouping, missing values and observation keys.

Parse an explicit time meaning

A timestamp can mean when an event occurred, when it was entered, or when it was retrieved. Timezones, ambiguous date formats and daylight-saving transitions can change the interpretation. State the intended timezone and preserve the original text when parsing needs investigation. The Pandas time-series guide documents parsing, timezone handling, resampling and rolling operations.

import pandas as pd
events = pd.DataFrame({
    "time": ["2023-01-01T10:00:00Z", "2023-01-01T12:00:00Z", "2023-01-03T11:00:00Z"],
    "institution": ["N", "S", "N"], "visits": [2, 3, 4],
})
events["time"] = pd.to_datetime(events["time"], utc=True, errors="raise")
events = events.sort_values("time")
print(events["time"].dt.day_name().tolist())

['Sunday', 'Sunday', 'Tuesday']

These are invented event records. All supplied timestamps explicitly use UTC. Applying utc=True to a naive local timestamp without knowing its timezone would impose an assumption, not recover missing information. An annual observation such as the World Bank population estimate is also different from a precise event timestamp.

Distinguish a missing period from a recorded zero

A resampling operation groups observations into time bins. Whether an empty bin means zero depends on collection coverage. In an event log known to be complete during an open day, no events may justify zero. In a log with unknown coverage, it does not.

daily = events.set_index("time")["visits"].resample("D").sum(min_count=1)
print(daily)
assert daily.iloc[0] == 5 and pd.isna(daily.iloc[1]) and daily.iloc[2] == 4
coverage = pd.Series([True, False, True], index=daily.index)
reported = daily.where(coverage)
assert pd.isna(reported.iloc[1])

time
2023-01-01 00:00:00+00:00    5.0
2023-01-02 00:00:00+00:00    NaN
2023-01-03 00:00:00+00:00    4.0
Freq: D, Name: visits, dtype: float64

The gap is retained rather than filled with a line or a fabricated count. A later figure must communicate it honestly. If you establish complete observation on the missing day, record that evidence before replacing the gap with zero.

Order within each entity before measuring change

For repeated country-year observations, sort by country and year before applying a lag. A one-row shift in the entire table would accidentally compare the first observation of one country with the final observation of another. Also verify that neighbouring rows represent the intended interval.

from pathlib import Path
population = pd.read_csv(Path("data/completion/worldbank/population.csv"))
population = population.sort_values(["country_code", "year"])
previous = population.groupby("country_code")["population"].shift(1)
previous_year = population.groupby("country_code")["year"].shift(1)
valid = previous.gt(0) & population["year"].sub(previous_year).eq(1)
population["annual_change_pct"] = (100 * (population["population"] / previous - 1)).where(valid)
print(population.groupby("country_code")["annual_change_pct"].count())
assert population["annual_change_pct"].notna().sum() == 15

country_code
DEU    5
FRA    5
GBR    5
Name: annual_change_pct, dtype: int64

For positive consecutive values, percentage change is $100(x_t/x_{t-1}-1)$. It is relative change, not a percentage-point change in an already expressed rate. Dividing by a zero baseline is undefined, and a gap of several years does not justify labelling the resulting change “annual.” A population stock should not be summed across years to claim a count of distinct people.

Use rolling summaries with a clear window

A three-row rolling mean and a three-day rolling mean are different if observations are irregular. Choose a window that matches the question. Centered windows use later observations and can be useful for retrospective description; they are inappropriate as prediction-time features if those future observations would be unavailable.

series = pd.Series([10., 20., 30., 40.], index=pd.date_range("2023-01-01", periods=4, freq="D"))
trailing = series.rolling(window=3, min_periods=3).mean()
print(trailing)
assert pd.isna(trailing.iloc[1]) and trailing.iloc[2] == 20.0

2023-01-01     NaN
2023-01-02     NaN
2023-01-03    20.0
2023-01-04    30.0
Freq: D, dtype: float64

The early unresolved values communicate that a complete window is unavailable. Choosing a smaller min_periods changes support and should be explained. Time dependence also affects uncertainty calculations: neighbouring records may share causes and cannot automatically be treated as independent replications.

Practice and independent check

Remove one country-year record and confirm that the code does not turn a two-year change into a one-year change. Compare three-observation and three-day windows on an irregular index. Write an acquisition-coverage statement that would justify zero-filling an event count, and another in which zero-filling would be misleading.

Your independent output is an ordered table with explicit keys, time meaning, missing-period flags and a documented change calculation. This is the reliable input that later temporal visualizations and modelling require.

Chapter exercisesWorked answers after an attempt