Text Operations

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 next to numeric columns and look as if they can be processed in the same way. A string method is applied to the whole column with a single call, the result becomes a new series, and there is no need to write a loop.

The price of this convenience is measured in this lesson. A string method is defined on a text; it is not defined on a missing cell. A hand-written loop reports this by raising an error. A vectorized operation does not report it: it does not pass the missing cell through the operation, it leaves it missing and keeps running. The measure is this — how many rows each produces, where each stops, and how many of the rows produced have silently turned into missing.

• DF30. A vectorized string operation is applying a string method to the whole column with a single call. A missing cell does not pass through the operation and stays missing in the result; the operation does not raise an error.
• DF31. A string chain propagates the missing value. A cell that turns missing at any step of the chain stays missing in all later steps, and how many rows are missing at the end of the chain is known only if they are counted separately.
• DF32. A malformed value is different from a missing value but at the end of the chain it falls into the same place: both become missing and both are reported in the same way.
• DF33. One step of the chain changes the input of the later steps. Decisions about case, whitespace and separators can make an equality filter further down the chain useless without producing any warning.

The Text Column and the Malformed Format

# 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()]

# The address code is produced in the field and has the form REGION-NNNN-L. Some
# of them were never assigned, in some the third part is missing.
MISSING = None

def address_code(no: int, region: str):
    r = rng(SEED + 61000 + no)
    if r() < 0.048:
        return MISSING
    if r() < 0.037:
        return f"{region.upper()}-{no - 10000:04d}"
    return f"{region.upper()}-{no - 10000:04d}-{'ABC'[pick(r(), [0.42, 0.35, 0.23])]}"

code_of = {k["subscriber_id"]: address_code(k["subscriber_id"], k["region"]) for k in subscribers}
records = [{"subscriber_id": o["subscriber_id"], "consumption_m3": o["consumption_m3"],
            "address_code": code_of[o["subscriber_id"]]} for o in LONG]
column = [k["address_code"] for k in records]
missing = sum(1 for v in column if v is MISSING)
malformed = sum(1 for v in column if v is not MISSING and v.count("-") < 2)
print(f"{len(records)} rows: {len(column) - missing - malformed} complete codes, {missing} missing, "
      f"{malformed} without a third part")
print(f"sample codes: {[v for v in column[:6]]}")

3199 rows: 2943 complete codes, 160 missing, 96 without a third part
sample codes: ['CENTRAL-0001-A', 'CENTRAL-0001-A', 'NORTH-0002-A', 'NORTH-0002-A', 'NORTH-0002-A', 'SOUTH-0003-A']

There are three separate cases in the column, and two of them are the subject of this lesson. The address code of 160 rows does not exist at all: the address record of the subscriber was never bound to a code. The code of 96 rows exists but is malformed: the region and number parts are in place, the third part, that is the building letter, was not written.

The two stand side by side in a dataset and they are different things. For a row that carries no code, nothing is known. For a row whose code is malformed, both the region and the number are known, only one part is missing. Whether this distinction is preserved to the very end is one of the numbers this lesson will count.

The Loop That Stops, the Chain That Does Not

# strings.py — the same string operation in two ways: a hand-written loop and a
# single call for the whole column. The difference shows on a missing cell.
import sys

def manual_lower(column):
    """Hand-written loop: the string method is applied directly to every cell."""
    return [v.lower() for v in column]

def vector(column, operation):
    """Vectorized operation: a missing cell does not pass through, it stays missing."""
    return [MISSING if v is MISSING else operation(v) for v in column]

try:
    manual_lower(column)
except AttributeError as error:
    first = next(i for i, v in enumerate(column) if v is MISSING)
    print(f"manual loop stopped at row {first + 1}: AttributeError {error}")
    print(f"rows produced: 0 ({len(column) - first - 1} remaining rows were never looked at)")

lower = vector(column, str.lower)
parts = vector(lower, lambda v: v.split("-"))
letter = [MISSING if p is MISSING or len(p) < 3 else p[2] for p in parts]
print(f"\nvectorized chain: produced {len(lower)} rows, raised 0 errors")
print(f"after the third part is extracted {sum(1 for h in letter if h is MISSING)} rows are missing: "
      f"{sum(1 for v in column if v is MISSING)} carrying no code at all, "
      f"{sum(1 for p in parts if p is not MISSING and len(p) < 3)} without a third part")
print(f"bytes: three intermediate series {sys.getsizeof(lower) + sys.getsizeof(parts) + sys.getsizeof(letter)}, "
      f"source column {sys.getsizeof(column)}")
print(f"code : manual safe loop 9 lines, vectorized chain 3 calls")

manual loop stopped at row 20: AttributeError 'NoneType' object has no attribute 'lower'
rows produced: 0 (3179 remaining rows were never looked at)

vectorized chain: produced 3199 rows, raised 0 errors
after the third part is extracted 256 rows are missing: 160 carrying no code at all, 96 without a third part
bytes: three intermediate series 78120, source column 26040
code : manual safe loop 9 lines, vectorized chain 3 calls

The two approaches do the same job and fail in exactly opposite ways. The manual loop stops at row 20, produces zero rows and never looks at the remaining 3,179 rows. In a production line this is an outage and work cannot continue until it is fixed; but the outage itself is information, because it announces within the first twenty rows that the dataset has rows that carry no code.

The vectorized chain never stops. It produces all 3,199 rows, raises zero errors and at the end of the chain 256 rows are missing. These 256 rows do not show up in the row count of the output; the output is still 3,199 rows and its length is the same as the source. They have to be counted separately to be seen, and the count in this lesson is that separate line itself.

The account of the loss has two items and both have merged at the end of the chain: 160 rows never had a code, 96 rows had a code but no third part. In the output of the chain the two are the same thing, that is, missing. The distinction preserved in the previous section is lost here, and the reason for losing it is not an error but that the step extracting the third part cannot tell a missing part from a missing record.

The gain side has to be written down as well. The manual safe loop, together with the error handling, takes nine lines; the vectorized chain is three calls. The price is in memory: the three intermediate series of the chain hold 78,120 bytes of pointers, while the source column itself is 26,040 bytes. Every step produces a new series and this triples as the chain gets longer.

The Filter After the Chain

# filter.py — the missing that comes out of the string operation silently falls
# to one side in the filter after it. An earlier step of the chain also affects
# the filter.
T = lambda m: round(sum(k["consumption_m3"] for k, b in zip(records, m) if b), 2)
upper = [h == "B" for h in letter]
lower_b = [h == "b" for h in letter]
not_equal = [h != "b" for h in letter]
unknown = [h is MISSING for h in letter]
WIDTH = [30, 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(["filter", "rows", "total m3"]))
print(show(["letter == 'B'", sum(upper), T(upper)]))
print(show(["letter == 'b'", sum(lower_b), T(lower_b)]))
print(show(["letter != 'b'", sum(not_equal), T(not_equal)]))
print(show(["letter unknown", sum(unknown), T(unknown)]))
print(show(["dataset", len(records), T([True] * len(records))]))
print(f"\nbecause the chain code converted to lowercase first, the 'B' filter returns {sum(upper)} rows "
      f"and produces no warning")
print(f"the equality filter leaves {sum(unknown)} unknown rows outside, the inequality filter takes "
      f"the same {sum(unknown)} rows in: "
      f"{sum(lower_b)} + {sum(not_equal)} = {sum(lower_b) + sum(not_equal)}")

row_bytes = (sys.getsizeof(column) + sys.getsizeof(lower) + sys.getsizeof(parts)
             + sys.getsizeof(letter)) / len(records)
print(f"\na single text column and three intermediate series hold {round(row_bytes, 1)} bytes of "
      f"pointers per row; {round(row_bytes * len(records) / 1024)} kibibytes at {len(records)} rows")
print(f"the same structure needs {round(row_bytes * 20_000_000 / 1024 ** 3, 2)} "
      f"gibibytes of pointers at 20 million rows and the strings themselves are not included")

filter                              rows      total m3
letter == 'B'                          0             0
letter == 'b'                       1004      20069.73
letter != 'b'                       2195       42990.9
letter unknown                       256       4980.99
dataset                             3199      63060.63

because the chain code converted to lowercase first, the 'B' filter returns 0 rows and produces no warning
the equality filter leaves 256 unknown rows outside, the inequality filter takes the same 256 rows in: 1004 + 2195 = 3199

a single text column and three intermediate series hold 32.6 bytes of pointers per row; 102 kibibytes at 3199 rows
the same structure needs 0.61 gibibytes of pointers at 20 million rows and the strings themselves are not included

The first line is the quietest error of the topic. The filter looks for 'B', there is no 'B' in the column, because the first step of the chain has converted all the code to lowercase. The result is zero rows, zero cubic meters and zero warnings. An empty result set means both “there is no such record” and “you looked in the wrong place”; there is no sign in the output that separates the two.

The second and third lines repeat the finding of the fourth lesson of the topic on text. The equality filter leaves the 256 unknown rows outside, the inequality filter takes the same 256 rows in, and 1,004 plus 2,195 add up to exactly 3,199. The dataset looks as if it has been split in two, yet there is a third set and it carries 4,980.99 m³ of consumption. A table split in two does not even open a row to write this third set.

The last two lines are the closing measure of the topic. A single text column, together with its three intermediate series, holds 32.6 bytes of pointers per row; at 3,199 rows this is 102 kibibytes and goes completely unnoticed. The same structure reaches 0.61 gibibytes at 20 million rows, and this is the cost of the pointers alone, excluding the content of the strings themselves. All the comfort built in eight lessons rests on the assumption that the dataset fits in memory.

Winning the Distinction Back

# pattern.py — using a pattern that validates the format instead of splitting on
# the separator keeps the missing record and the malformed format apart at the
# end of the chain.
import re

PATTERN = re.compile(r"^([a-z]+)-(\d{4})-([abc])$")

def status_of(v) -> str:
    if v is MISSING:
        return "missing"
    return "complete" if PATTERN.match(v) else "malformed"

counts: dict[str, int] = {}
for v in lower:
    counts[status_of(v)] = counts.get(status_of(v), 0) + 1
print(f"parsing with a pattern: {counts['complete']} complete, {counts['malformed']} malformed format, "
      f"{counts['missing']} missing record; total {sum(counts.values())}")
pattern_letter = [PATTERN.match(v).group(3) if v is not MISSING and PATTERN.match(v) else MISSING
                  for v in lower]
print(f"third part: {sum(1 for h in letter if h is MISSING)} missing by splitting, "
      f"{sum(1 for h in pattern_letter if h is MISSING)} missing by pattern; the same number, "
      f"the difference is in reportability")
print(f"two ways that give the same result: splitting {len(lower)} passes and 2 intermediate series, "
      f"pattern {len(lower)} passes and 1 intermediate series")

parsing with a pattern: 2943 complete, 96 malformed format, 160 missing record; total 3199
third part: 256 missing by splitting, 256 missing by pattern; the same number, the difference is in reportability
two ways that give the same result: splitting 3199 passes and 2 intermediate series, pattern 3199 passes and 1 intermediate series

The distinction lost by splitting can be won back and the cost is not even a pass. When a pattern that validates the format is used instead of splitting on the separator, the column is divided into three numbers: 2,943 complete codes, 96 malformed formats, 160 missing records. The sum of the three is 3,199, and this sum check is the counterpart on text of the single check repeated throughout the topic.

The third part extracted is the same on both ways: 256 rows are missing. The only thing that changes is that where the 256 come from can be written down. The 96 rows of malformed format are a correctable problem and can be asked back to their source; the 160 rows that have no code at all cannot be corrected, because there is no record to correct. A report that adds the two up into a single number erases this distinction and with it the work to be done.

The number of passes is 3,199 on both ways, so the pattern adds no cost; the number of intermediate series falls from two to one. What lost the distinction was not vectorization itself, it was splitting not doing any validation.

Summary

• A vectorized string operation does not raise an error on a missing cell, it propagates the missing value: while the manual loop stops at row 20 and produces 0 rows, the chain produces all 3,199 rows and silently turns 256 of them into missing.
• A missing record and a malformed format are different things in the dataset, they fall into the same place at the end of the chain: the 160 rows without a code and the 96 rows without a third part cannot be told apart in the output.
• One step of the chain can make the filter after it useless: a search for 'B' in a column converted to lowercase returns 0 rows and produces no warning.
• The equality filter leaves the 256 unknown rows outside and the inequality filter leaves them inside; the sum of 1,004 and 2,195 gives the dataset but the third set of 4,980.99 m³ does not appear in any row.
• The price of the chain is memory: three intermediate series hold 78,120 bytes of pointers, the source column is 26,040 bytes, and 32.6 bytes per row reaches 0.61 gibibytes at 20 million rows.

Next Step

In eight lessons the dataframe interface has been built completely, and at every step two numbers were written side by side: the passes the abstraction gained and the decision it hid. Alignment brought 781,710 comparisons down to 2,361 passes, the join brought 2,616,782 comparisons down to 4,017 passes, slicing brought 2,658 comparisons down to 21 passes. Every time, the code that got shorter left behind a default that became invisible at the same rate.

All of this gain had a single condition and it was not tested in any lesson: that the dataset fits in memory. The dataset of this topic was 3,199 rows and a single text column held 102 kibibytes with its intermediate series. When the measurement network grows to cover a whole city, the row count is measured in millions and every intermediate series asks for a separate copy. The next topic tests this condition: how long the same jobs take when the whole dataset does not fit in memory, which operations can be split into pieces, how many bytes each type choice saves and how the place where the calculation is done changes the number of rows that are carried.