Series and Dataframe

The previous topic made the numbers faster. When the same summation job was done with a plain list, a separate pass was needed for every element; with the array interface it came down to a single operation, and the bytes set aside dropped together with the type choice. But those arrays shared a gap: none of them had a label. Nothing but the code said that the third column was consumption or that the fifth row was subscriber 10,412. When two arrays were added, the only basis was position — the first element was added to the first, the second to the second.

This topic adds the label to the array, and the first lesson measures what the label takes on. The measure is this: when two series are added, how many rows the result has. In a positional addition this question is not asked, because the answer is known in advance. In a labeled addition the answer depends on how much the two indexes at hand overlap, and the addition makes an alignment decision that was never written, on its own.

• DF1. The dataset is the state Data Collection and Preparation left it in and is fiction: a long-format reading table of 3,199 rows, 1,329 subscribers, three periods. The dataset is modeled with python and only the standard library; no library is called, no interface is imitated. What is measured is not the tool itself but the behavior of the abstraction.
• DF2. A series carries a list of values and a label index of equal length. This index is something different from the database index introduced in the Databases curriculum: it exists not to speed up a query but to address a row by its name rather than its order.
• DF3. A dataframe is an ordered dictionary of series that share the same label index. If the columns do not share the same index, the dataframe cannot be built; alignment comes before the dataframe.
• DF4. Alignment is matching values by label rather than by position when an operation is performed on two series. The row count of the result comes from this matching.
• DF5. The join mode of an alignment is a decision: the inner mode keeps only the labels found in both indexes, the outer mode produces the union of the labels and leaves a one-sided label missing. If the mode is not written, the abstraction picks one, and that choice determines the row count.

What the Label Index Takes On

# series.py — MODEL: the labeled series is realized in its own model with the
# standard library. The dataset is fiction and is regenerated from the same seed
# as the long-format reading table left by Data Collection and Preparation.
import math

M32 = 0xFFFFFFFF

def mix(t: int) -> int:
    t = ((t ^ (t >> 16)) * 2246822507) & M32
    t = ((t ^ (t >> 13)) * 3266489909) & M32
    return t ^ (t >> 16)

def rng(seed: int):
    s = mix(seed)
    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

SEED = 20260218
PERIODS = ["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)]

subscribers, dropped = [], []
for i in range(1400):
    r = rng(SEED + i)
    name, _, base = REGION[pick(r(), [b[1] for b in REGION])]
    k = {"subscriber_id": 10001 + i, "region": name, "base": base,
         "household_size": pick(r(), [0.06, 0.24, 0.30, 0.24, 0.11, 0.05]),
         "satisfaction": pick(r(), [0.08, 0.14, 0.27, 0.34, 0.17]) + 1}
    (dropped if r() < 0.046 else subscribers).append(k)

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()]
print(f"dataset (seed {SEED}): subscribers {len(subscribers)} rows, long-format reading "
      f"table {len(LONG)} rows, {len({o['subscriber_id'] for o in LONG})} subscribers")

MISSING = None

class Series:
    """A list of values and a label index of equal length. The label index is
    not a database index: it finds a row by its name, not by its order."""

    def __init__(self, labels, values, name="value"):
        self.labels, self.values, self.name = list(labels), list(values), name
        self.position = {e: i for i, e in enumerate(self.labels)}

    def __len__(self):
        return len(self.values)

    def get(self, e):
        return self.values[self.position[e]]

    def total(self):
        return round(sum(v for v in self.values if v is not MISSING), 2)

    def missing_count(self):
        return sum(1 for v in self.values if v is MISSING)

def period_series(period: str, name: str) -> Series:
    s = [o for o in LONG if o["period"] == period]
    return Series([o["subscriber_id"] for o in s], [o["consumption_m3"] for o in s], name)

january, february = period_series("2026-01", "january"), period_series("2026-02", "february")
print(f"january series {len(january)} labels, {january.total()} m3")
print(f"february series {len(february)} labels, {february.total()} m3")
print(f"are the label indexes of the two series the same: "
      f"{'yes' if january.labels == february.labels else 'no'}")

dataset (seed 20260218): subscribers 1329 rows, long-format reading table 3199 rows, 1260 subscribers
january series 1260 labels, 26038.51 m3
february series 1101 labels, 21668.93 m3
are the label indexes of the two series the same: no

The last line sets up the whole problem of the lesson. The January series has 1,260 subscribers, the February series 1,101. The two series came from the same dataset, the same column, with the same subscriber numbers; even so, their indexes are not the same. The 159 subscribers in between were never read in February. This is a deficiency of the data itself and a continuation of the silent loss measured in Data Collection and Preparation; what is new here is that the two series look summable even though they are not summable.

The label index takes on exactly this. When two unlabeled arrays are added, the first element is added to the first, and this removes the question of who is added to whom entirely. The label index brings the question back: two values are added only if they belong to the same label. The price is that addition is no longer always defined — if a label has no counterpart on the other side, a decision is needed for that row.

The Same Addition, Three Alignments

# alignment.py — the same two series are added in three join modes, and the row
# count, missing count and total of the three results are printed side by side.
PASSES = {"count": 0}

def align(a: Series, b: Series, mode: str) -> Series:
    """Adds two series. 'position' never reads the label; 'inner' keeps only the
    labels found in both indexes; 'outer' produces the union of the labels and
    fills a one-sided label with MISSING."""
    if mode == "position":
        n = min(len(a), len(b))
        PASSES["count"] += 2 * n
        return Series(a.labels[:n], [a.values[i] + b.values[i] for i in range(n)])
    PASSES["count"] += len(a) + len(b)
    if mode == "inner":
        common = [e for e in a.labels if e in b.position]
        return Series(common, [a.get(e) + b.get(e) for e in common])
    labels = a.labels + [e for e in b.labels if e not in a.position]
    return Series(labels, [a.get(e) + b.get(e) if e in a.position and e in b.position
                           else MISSING for e in labels])

def reindex(s: Series, labels) -> Series:
    """Moves the series to the given label index; a label with no counterpart becomes MISSING."""
    return Series(labels, [s.get(e) if e in s.position else MISSING for e in labels], s.name)

class Dataframe:
    """An ordered dictionary of series that share the same label index. If they
    do not share it, the dataframe is not built: alignment comes before the dataframe."""

    def __init__(self, columns: dict[str, Series]):
        first = next(iter(columns.values()))
        for s in columns.values():
            if s.labels != first.labels:
                raise ValueError(f"the label index of column '{s.name}' differs")
        self.columns, self.labels = columns, first.labels

    def shape(self) -> tuple[int, int]:
        return len(self.labels), len(self.columns)

WIDTH = [24, 8, 8, 12]
show = lambda h: "".join(str(v).rjust(WIDTH[j]) if j else str(v).ljust(WIDTH[0])
                         for j, v in enumerate(h))
print(show(["join mode", "rows", "missing", "total m3"]))
results = {}
for mode, name in [("position", "positional"), ("inner", "label inner"), ("outer", "label outer")]:
    results[mode] = align(january, february, mode)
    print(show([name, len(results[mode]), results[mode].missing_count(), results[mode].total()]))

matching = sum(1 for i in range(min(len(january), len(february)))
               if january.labels[i] == february.labels[i])
print(f"\nof the {len(results['position'])} rows of positional addition, {matching} show the "
      f"same subscriber under both labels; the difference between the two totals is "
      f"{round(results['inner'].total() - results['position'].total(), 2)} m3")

try:
    Dataframe({"january": january, "february": february})
except ValueError as error:
    print(f"dataframe could not be built: {error}")
union = results["outer"].labels
frame = Dataframe({"january": reindex(january, union),
                   "february": reindex(february, union),
                   "total": results["outer"]})
print(f"after alignment the dataframe shape is {frame.shape()[0]} rows x "
      f"{frame.shape()[1]} columns, {frame.columns['february'].missing_count()} missing "
      f"in the february column")

join mode                   rows missing    total m3
positional                  1101       0    44271.89
label inner                 1101       0    44391.02
label outer                 1260     159    44391.02

of the 1101 rows of positional addition, 4 show the same subscriber under both labels; the difference between the two totals is 119.13 m3
dataframe could not be built: the label index of column 'february' differs
after alignment the dataframe shape is 1260 rows x 3 columns, 159 missing in the february column

Three rows add the same two series and produce three different tables. The striking thing is that the first two rows give the same row count: 1,101. Positional addition pairs the lists from the start, so it stops at the length of the shorter one; inner alignment also yields 1,101 rows because the February index is a subset of the January index. A check that looks at the row count cannot tell these two results apart.

The difference is in the content. Of the 1,101 rows of positional addition, only 4 show the same subscriber under both labels; the remaining 1,097 rows added one subscriber’s January reading to another subscriber’s February reading. The first subscriber unread in February shifts every pairing after it by one, and the shift accumulates along the index. That this produces no error is no accident: both lists carry numbers, addition is defined, the result is a number.

The most dangerous number is the last one. The wrongly paired total is 44,271.89 m³, the correctly paired total 44,391.02 m³; the difference is 119.13 m³, less than three in a thousand. The total allows an operation that mixes up individual rows to give nearly the same result, because summation does not care about order and the non-shared part of the two indexes is small. If a report carries only the total, the error is invisible; the first question that looks at it per subscriber gives a wrong answer in 1,097 rows.

The third row shows the decision itself. Outer alignment produces 1,260 rows and 159 of them are missing. Its total is the same as that of inner alignment, because the missing rows did not take part in the addition. That is, outer alignment does not delete the loss, it makes it visible: it writes it into the output as 159 countable rows.

That the dataframe could not be built is another face of the same rule. As long as two series carry different label indexes, they cannot be placed in a side-by-side table; first they must be moved to a common index. Once they are moved to the union index, the dataframe has 1,260 rows and three columns, and 159 missing cells are visible in the february column.

Gain and the Hidden Default

# measurement.py — the gain of alignment is measured in pass count, the decision
# it hides in how the missing side is counted.
def manual_align(a: Series, b: Series):
    """Alignment without building a label index: for every left label the right
    label list is scanned from the start. Same result, different path."""
    labels, values, passes = [], [], 0
    for i, e in enumerate(a.labels):
        for j, f in enumerate(b.labels):
            passes += 1
            if e == f:
                labels.append(e)
                values.append(a.values[i] + b.values[j])
                break
    return Series(labels, values), passes

PASSES["count"] = 0
manual, manual_passes = manual_align(january, february)
aligned = align(january, february, "inner")
print(f"manual alignment: {len(manual)} rows, {manual_passes} label comparisons, 11 lines of code")
print(f"series addition : {len(aligned)} rows, {PASSES['count']} label passes, 1 line of code")
print(f"same result in both: {'yes' if manual.values == aligned.values else 'no'}")

def zero_filled_outer(a: Series, b: Series) -> Series:
    """The same outer alignment, with one difference: the missing side is counted as zero."""
    labels = a.labels + [e for e in b.labels if e not in a.position]
    return Series(labels, [(a.get(e) if e in a.position else 0.0)
                           + (b.get(e) if e in b.position else 0.0) for e in labels])

outer, zero_filled = align(january, february, "outer"), zero_filled_outer(january, february)
print(f"\nouter alignment produced {len(outer)} rows, {outer.missing_count()} of them missing")
print(f"if missing is skipped the total is {outer.total()} m3, if the missing side is counted "
      f"as zero it is {zero_filled.total()} m3; difference {round(zero_filled.total() - outer.total(), 2)} m3")

manual alignment: 1101 rows, 781710 label comparisons, 11 lines of code
series addition : 1101 rows, 2361 label passes, 1 line of code
same result in both: yes

outer alignment produced 1260 rows, 159 of them missing
if missing is skipped the total is 44391.02 m3, if the missing side is counted as zero it is 47707.44 m3; difference 3316.42 m3

The gain is read in two numbers. Manual alignment takes 11 lines of code and makes 781,710 label comparisons, because for every left label the right list is scanned from the start. Adding the series reaches the same result in 2,361 passes: the left index is read once, the right index is read once, and the lookup goes through a dictionary. The comparison count drops by a factor of 331 and the code comes down to a single line. The measure is not a time but a number, and it does not change from run to run.

The hidden default is in the second output. When the 159 missing rows of the outer alignment do not take part in the addition, the result is 44,391.02 m³; when the missing side is counted as zero, it is 47,707.44 m³; the difference is 3,316.42 m³, that is, 7% of the total. Both results are defensible. To say “as much as the January reading” for the two-month total of a subscriber never read in February is to assume that the subscriber used no water at all in February. To say “unknown” is not to report that subscriber’s total at all.

The decision has been made here without ever being written. Neither the call align(january, february, "outer") nor the line that totals says anything like “skip the missing”; skipping is the default behavior of the totaling function. The rule of this course comes from here: an abstraction hides the decision as much as it shortens the job, and a function whose default you do not know is a function that decides on your behalf.

Summary

• A series carries a list of values and a label index of equal length; a dataframe is an ordered dictionary of series that share the same label index, and if the indexes differ the dataframe cannot be built.
• Adding the same two series in three join modes gives three results: positional 1,101 rows, inner alignment 1,101 rows, outer alignment 1,260 rows with 159 missing.
• Of the 1,101 rows of positional addition, only 4 pair the correct subscriber, yet its total deviates from the correct answer by 119.13 m³; because addition does not care about order, the error is lost inside the summary.
• The gain of the label index shows in pass count: a manual scan makes 781,710 comparisons while adding the series reaches the same result in 2,361 passes.
• The hidden default of alignment is what the missing side is counted as: 44,391.02 m³ when skipped, 47,707.44 m³ when counted as zero, a difference of 3,316.42 m³.

Next Step

In this lesson the dataset was produced inside the code, and the type of every column was clear where it was written. A real dataset, however, comes from a file, and a file has no types: every cell is text. The read operation has to convert that text to a type, and it does so by looking at the column, without anyone saying so. The subscriber number becomes a number, a meter number starting with zero loses its zeros, a column that looks like integers in the first hundred rows carries decimals in the nine hundredth. The next lesson covers reading and writing and asks a single number: how many columns of a dataset read with one call came with the wrong type, and how many cells changed in a file that goes out and comes back.