Time Series Operations

The join put the tables next to each other and the row calculation was written down. There is an axis in the dataset that has not been handled yet: time. In the reading table the period is a text column. It can be sorted, grouped, put into an equality; but questions such as “how many weeks have passed in between”, “in which week is there no reading at all” or “what is the average of the last three periods” cannot be asked in this form, because a text column does not know the distance between two values.

When the time axis is moved to the label index these questions become askable, and a new decision comes along with them. A time axis is continuous, while the data is discrete: every period on the axis does not have to have a counterpart in the dataset. For a period that carries no record there are two answers, and both are defensible: the consumption was zero in that period, or that period was never measured. The measure of this lesson is the place where these two answers part.

• DF26. The time axis is the labels of the index being dates. A date index knows the distance between two labels, and this knowledge makes sorting, taking differences and resampling possible.
• DF27. Resampling is reducing the records to a coarser step on the time axis. What determines the number of periods that come out is not the data but the step decision.
• DF28. The empty period is a period that is on the axis but carries no record. It can be counted as zero or not produced in the output at all; the difference between the two is a row count.
• DF29. The empty period decision does not change the total, because zero adds nothing to a sum. It changes every calculation that comes after the total: the denominator of the average, the denominator of the change ratio and the periods covered by the moving window.

The Time Axis

# dataset.py — MODEL: the long-format reading table of the previous lesson is
# regenerated from the same seed. The dataset is fiction; the detail of the
# generator is in the first lesson of the topic.
import math

M32 = 0xFFFFFFFF
SEED, PERIODS = 20260218, ["2026-01", "2026-02", "2026-03"]
REGION = [("north", 0.28, 21), ("south", 0.22, 17), ("east", 0.18, 26),
          ("west", 0.14, 14), ("central", 0.18, 23)]

def rng(seed: int):
    s = ((seed ^ (seed >> 16)) * 2246822507) & M32
    s = ((s ^ (s >> 13)) * 3266489909) & M32
    s ^= s >> 16
    def next_value() -> float:
        nonlocal s
        s = (s * 1664525 + 1013904223) & M32
        return s / 4294967296
    return next_value

def pick(u: float, weights: list[float]) -> int:
    t = 0.0
    for i, p in enumerate(weights):
        t += p
        if u < t:
            return i
    return len(weights) - 1

subscribers, dropped = [], []
for i in range(1400):
    r = rng(SEED + i)
    name, _, base = REGION[pick(r(), [b[1] for b in REGION])]
    r(); r()  # the household and satisfaction draws are not used in this lesson
    (dropped if r() < 0.046 else subscribers).append(
        {"subscriber_id": 10001 + i, "region": name, "base": base})

valid = {k["subscriber_id"] for k in subscribers}
pairs: dict[tuple[int, str], float] = {}
for k in subscribers + dropped:
    r = rng(SEED + 7000 + k["subscriber_id"])
    for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])):
        t = 0.0 if r() < 0.038 else math.floor(k["base"] * math.exp(
            (r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100 + 0.5) / 100
        if k["subscriber_id"] in valid:
            pairs.setdefault((k["subscriber_id"], PERIODS[d]), t)
        r()
LONG = [{"subscriber_id": a, "period": d, "consumption_m3": t} for (a, d), t in pairs.items()]

# Reading date: the field team works in the reading window of each month. The
# period text is moved to a date axis this way.
from datetime import date, timedelta

START, END = date(2026, 1, 1), date(2026, 3, 31)
MONTH = {"2026-01": 1, "2026-02": 2, "2026-03": 3}

def reading_date(no: int, period: str) -> date:
    """The reading window is between the 18th and the 28th of the month."""
    r = rng(SEED + 77000 + no * 4 + MONTH[period])
    return date(2026, MONTH[period], 18 + pick(r(), [0.09] * 10 + [0.10]))

records = [{"subscriber_id": o["subscriber_id"], "date": reading_date(o["subscriber_id"], o["period"]),
            "consumption_m3": o["consumption_m3"]} for o in LONG]
print(f"{len(records)} readings, date range {min(k['date'] for k in records)} .. "
      f"{max(k['date'] for k in records)}")
print(f"axis between {START} and {END} is {(END - START).days + 1} days, "
      f"{(END - START).days // 7 + 1} seven-day periods")
print(f"total {round(sum(k['consumption_m3'] for k in records), 2)} m3")

3199 readings, date range 2026-01-18 .. 2026-03-28
axis between 2026-01-01 and 2026-03-31 is 90 days, 13 seven-day periods
total 63060.63 m3

The mismatch between the two numbers sets up the whole subject of the lesson. The axis is 90 days long and is divided into 13 periods with a seven-day step. The data, on the other hand, consists of 3,199 readings, and all of the readings are gathered between the 18th and the 28th of the month, because the field team works in a certain reading window each month.

This is not a flaw of the dataset, it is the way the work is done. Its consequence is this: some periods of the axis carry no reading at all. These periods are empty because no data was collected, not because there was no consumption. The water kept flowing, the meter kept turning, but that week nobody went to read it.

What an Empty Period Is

# resample.py — reduces the time axis to seven-day periods. For a period that
# carries no record there are two answers: zero or nonexistent.
def resample(rows, step: int, empty_as_zero=True):
    """If empty_as_zero is true a period that carries no record is produced with 0.0;
    if false that period does not appear in the output at all."""
    bucket: dict[int, list[float]] = {}
    for k in rows:
        bucket.setdefault((k["date"] - START).days // step, []).append(k["consumption_m3"])
    out = []
    for i in range((END - START).days // step + 1):
        v = bucket.get(i, [])
        if not v and not empty_as_zero:
            continue
        out.append({"period": START + timedelta(days=i * step),
                    "readings": len(v), "total_m3": round(sum(v), 2)})
    return out

skipped = resample(records, 7, empty_as_zero=False)
zero_filled = resample(records, 7, empty_as_zero=True)
WIDTH = [14, 10, 14, 10, 14]
show = lambda h: "".join(str(v).rjust(WIDTH[k]) if k else str(v).ljust(WIDTH[0])
                         for k, v in enumerate(h))
print(show(["period start", "readings", "total m3", "readings", "total m3"]))
d = {c["period"]: c for c in skipped}
for c in zero_filled:
    e = d.get(c["period"])
    print(show([str(c["period"]), c["readings"], c["total_m3"],
                e["readings"] if e else "-", e["total_m3"] if e else "-"]))
total_zero = round(sum(c["total_m3"] for c in zero_filled), 2)
total_skip = round(sum(c["total_m3"] for c in skipped), 2)
print(f"\nrows : {len(zero_filled)} when the empty period is counted as zero, {len(skipped)} when skipped")
print(f"total: {total_zero} m3 and {total_skip} m3 — the total does not change")
print(f"average: {round(total_zero / len(zero_filled), 2)} m3 and {round(total_skip / len(skipped), 2)} m3")

period start    readings      total m3  readings      total m3
2026-01-01             0             0         -             -
2026-01-08             0             0         -             -
2026-01-15           460       9260.91       460       9260.91
2026-01-22           800       16777.6       800       16777.6
2026-01-29             0             0         -             -
2026-02-05             0             0         -             -
2026-02-12           106        2124.1       106        2124.1
2026-02-19           690      13715.37       690      13715.37
2026-02-26           305       5829.46       305       5829.46
2026-03-05             0             0         -             -
2026-03-12            87       1665.23        87       1665.23
2026-03-19           521       9477.26       521       9477.26
2026-03-26           230        4210.7       230        4210.7

rows : 13 when the empty period is counted as zero, 8 when skipped
total: 63060.63 m3 and 63060.63 m3 — the total does not change
average: 4850.82 m3 and 7882.58 m3

Two series come out of the same dataset, and at first sight both say the same thing: the total is 63,060.63 m³. This equality gives a misleading confidence, because zero adds nothing to a sum and the empty period decision leaves no trace in the total.

The difference starts in the row count: 13 rows against 8 rows. In the series filled with zeros, five periods carry 0 consumption, and these five rows are drawn on a chart as five points that sink to the floor. Whoever reads the chart sees this: the consumption was zero in the first two weeks of the year, then it rose, and at the end of January it fell to zero again. None of this happened. What is zero is not the consumption but the number of readings made that week, and the second column of the table already says so.

The difference in the average is the numerical counterpart of this reading. The same total gives 4,850.82 m³ when divided by 13 and 7,882.58 m³ when divided by 8; the second number is 1.63 times the first. The number to be reported as “weekly average consumption” is one of these two, and which one it is is determined by an option that is never written down.

Every Calculation After the Total

# change.py — counting the empty period as zero does not change the total, but it
# changes every calculation that comes after the total.
def change(series):
    """Percent change from period to period; if the denominator is zero the ratio is undefined."""
    o, undefined = [], 0
    for i in range(1, len(series)):
        previous = series[i - 1]["total_m3"]
        if previous == 0:
            undefined += 1
            o.append(None)
        else:
            o.append(round(100 * (series[i]["total_m3"] - previous) / previous, 1))
    return o, undefined

def moving(series, window=3):
    """For each period the average of the last window periods."""
    o = {}
    for i in range(len(series)):
        v = [c["total_m3"] for c in series[max(0, i - window + 1):i + 1]]
        o[series[i]["period"]] = round(sum(v) / len(v), 2)
    return o

c1, u1 = change(zero_filled)
c2, u2 = change(skipped)
print(f"zero-filled series: {len(c1)} change ratios, {u1} of them undefined (denominator zero)")
print(f"skipped series: {len(c2)} change ratios, {u2} of them undefined")
m1, m2 = moving(zero_filled), moving(skipped)
common = [g for g in m1 if g in m2]
farthest = max(common, key=lambda g: abs(m1[g] - m2[g]))
print(f"three-period moving average is compared in {len(common)} common periods; "
      f"the largest difference is {farthest}: zero-filled {m1[farthest]} m3, skipped {m2[farthest]} m3")
thirty_day = resample(records, 30, empty_as_zero=True)
print(f"\nthe same dataset gives {len(thirty_day)} periods with a thirty-day step and none of them is empty; "
      f"what creates the empty period is not the data itself, it is the step decision")
print(f"\npasses: a dataset scan per period {13 * len(records)}, a single bucket pass "
      f"{len(records) + 13}")
print(f"code  : hand-written period loop 9 lines, resample 1 call")

zero-filled series: 12 change ratios, 5 of them undefined (denominator zero)
skipped series: 7 change ratios, 0 of them undefined
three-period moving average is compared in 8 common periods; the largest difference is 2026-02-12: zero-filled 708.03 m3, skipped 9387.54 m3

the same dataset gives 3 periods with a thirty-day step and none of them is empty; what creates the empty period is not the data itself, it is the step decision

passes: a dataset scan per period 41587, a single bucket pass 3212
code  : hand-written period loop 9 lines, resample 1 call

The change ratio is the harshest counterpart of the empty period decision. In the series filled with zeros, 5 of the 12 ratios are undefined, because there is a zero in the denominator. In a spreadsheet these cells either stay empty or carry an error value; both are noise that has to be explained in the middle of a report, and it comes not from the data itself but from a crude default. In the skipped series none of the 7 ratios of the same calculation is undefined.

The moving average, on the other hand, drifts silently. The three-period window gives 708.03 m³ in the period of 2026-02-12 in the zero-filled series and 9,387.54 m³ in the skipped series. The ratio between them is thirteen times, and the reason is simple: in the zero-filled series the window covers two empty periods and divides the average by three, while in the skipped series the window covers three real reading periods. Both numbers are calculated correctly, both carry the same window name, and which one will be reported is not written even in a sentence.

The last two lines show where the decision comes from. The same dataset gives three periods with a thirty-day step and none of them is empty. The empty period is not a property of the data; it is a product of the step decision. The moment the seven-day step is chosen, five empty periods have been created, and this comes from the reading window not carrying a meaningful signal at weekly resolution.

The gain side has to be written down as well. An approach that scans the dataset from the start for every period costs 41,587 passes; the resample that walks the records once and throws them into the period bucket finishes in 3,212 passes, and the nine-line loop falls to a single call. The two decisions stand exactly inside this shortened call: the step and what the empty period is counted as.

A Shift Does Not Know the Group Boundary

# shift.py — to take a difference the series is shifted by one step. A shift knows
# the table order, it does not know the group boundary.
ordered = sorted(LONG, key=lambda o: (o["subscriber_id"], o["period"]))
raw_diff = [(ordered[i]["subscriber_id"], ordered[i - 1]["subscriber_id"],
             round(ordered[i]["consumption_m3"] - ordered[i - 1]["consumption_m3"], 2))
            for i in range(1, len(ordered))]
same = [f for f in raw_diff if f[0] == f[1]]
boundary = [f for f in raw_diff if f[0] != f[1]]
print(f"{len(raw_diff)} differences were produced from {len(ordered)} rows")
print(f"same subscriber's two periods: {len(same)} differences, average "
      f"{round(sum(f[2] for f in same) / len(same), 2)} m3")
print(f"between two subscribers      : {len(boundary)} differences, average "
      f"{round(sum(f[2] for f in boundary) / len(boundary), 2)} m3")
print(f"a shift that does not know the group boundary calculates {len(boundary)} of "
      f"{len(raw_diff)} differences from two separate subscribers")

3198 differences were produced from 3199 rows
same subscriber's two periods: 1939 differences, average -1.12 m3
between two subscribers      : 1259 differences, average 1.72 m3
a shift that does not know the group boundary calculates 1259 of 3198 differences from two separate subscribers

Taking a difference means shifting the series by one step and subtracting it from itself, and in a long-format table this operation follows the table order. The table is in subscriber and period order, so most of the differences come out right: 1,939 differences compare two consecutive periods of the same subscriber. The remaining 1,259 differences are between the last period of one subscriber and the first period of the next subscriber, and they measure nothing.

The difference between the two averages gives the sign of this contamination: while the average of the real period differences is -1.12 m³, the average of the boundary differences is +1.72 m³. If the two are reported together, the period-by-period downward trend in consumption weakens. A shift does not know the group boundary by itself; for it to know, the shift has to be done per subscriber, that is, a grouping decision has to be written before taking the difference.

Summary

• A date index knows the distance between two labels; a text column does not. Resampling, sorting and taking differences become possible with this knowledge.
• The same dataset gives 13 periods with a seven-day step and 3 periods with a thirty-day step; what creates the empty period is not the data but the step decision.
• When the empty period is counted as zero the series has 13 rows, when skipped it has 8 rows; the total is 63,060.63 m³ in both and the decision leaves no trace in the total.
• Every calculation after the total changes: the average moves between 4,850.82 and 7,882.58 m³, 5 of the 12 change ratios are left undefined because of a zero denominator, and the three-period moving average splits between 708.03 and 9,387.54 m³ in the period of 2026-02-12.
• A series filled with zeros produces points that sink to the floor on a chart and is read as “consumption stopped”; what is zero is not the consumption but the number of readings made in that period.

Next Step

Numeric columns, dates and keys have been handled. The last kind of field left in the dataset is text: the region name, the visit outcome, the address code, the field note. Text columns stand in the frame in the same place as numeric columns and seem to be processable in the same way; a string method is applied to the whole column with a single call and there is no need to write a loop. What that call does on a missing cell is visible nowhere. The next lesson handles vectorized string operations, counts what they return on a missing value, and closes the topic.