Reading and Writing Data

In the previous lesson the dataset was produced inside the code, and the type of every column was clear where it was written. A real dataset, on the other hand, comes from a file, and a file split by a delimiter has no such thing as a type: every cell is a piece of text. The file does not carry the difference between 10001 and "10001"; the reading side produces it.

This production job is called type inference, and it is the quietest decision of a dataframe interface. A single read call is written, a frame with typed columns comes back in return, and which column fell to which type, and why, is visible nowhere. The structural parsing lesson of the Data Collection and Preparation course had measured the place of a field shifting; there the selector read from the wrong field. Here the field is in place and what shifts is the type. The measure of this lesson is two numbers: how many columns inference returned with the wrong type, and how many cells changed when the same file was read and written back.

• DF6. The file format carries no type. In a file split by a delimiter every cell is text; the type is produced at read time by a decision. The dataset is fiction and is modeled with python and only the standard library.
• DF7. Type inference is choosing a type for a column if all of its values parse to the same type. If a single value does not parse, the whole column falls to text; the rule is column-wide, not per cell.
• DF8. A wrong type is defined operationally here: a type that raises no type error but produces a meaningless result, or that silently returns empty from a legitimate operation, is wrong.
• DF9. The round-trip check is reading the same file and writing it back. The number of changed cells directly counts the information the read decision lost.

There Is No Type in the File

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

import csv, os

COLUMNS = ["subscriber_id", "meter_id", "period", "meter_reading", "consumption_m3",
           "tariff_code", "region"]
region_of = {k["subscriber_id"]: k["region"] for k in subscribers}

def meter_code(no: int) -> str:
    """The numbering scheme switched to a lettered code after subscriber 10900."""
    return f"A{no - 9000:05d}" if no >= 10900 else f"{no - 9000:06d}"

def reading_cell(no: int, d: int, cumulative: float) -> str:
    """The meter reading from the field form: it may have been left empty or written as 'n/a'."""
    u = rng(SEED + 9100 + no * 4 + d)()
    return "" if u < 0.033 else "n/a" if u < 0.055 else str(100000 + (no - 10000) * 37 + int(cumulative))

rows, cumulative = [], {}
for o in LONG:
    no, t = o["subscriber_id"], o["consumption_m3"]
    cumulative[no] = cumulative.get(no, 0.0) + t
    rows.append([str(no), meter_code(no), o["period"],
                 reading_cell(no, PERIODS.index(o["period"]), cumulative[no]), f"{t:.2f}",
                 "10" if t <= 10 else "25" if t <= 25 else "40", region_of[no]])

with open("readings.csv", "w", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(COLUMNS)
    writer.writerows(rows)
print(f"readings.csv: {len(rows)} rows x {len(COLUMNS)} columns = "
      f"{len(rows) * len(COLUMNS)} cells, {os.path.getsize('readings.csv')} bytes")
print(f"meter_reading column: {sum(1 for s in rows if s[3] == '')} empty, "
      f"{sum(1 for s in rows if s[3] == 'n/a')} written as 'n/a'")
first = next(i for i, s in enumerate(rows) if s[1].startswith("A"))
print(f"first lettered code in the meter_id column is on row {first + 1}: {rows[first][1]}")

readings.csv: 3199 rows x 7 columns = 22393 cells, 139856 bytes
meter_reading column: 111 empty, 80 written as 'n/a'
first lettered code in the meter_id column is on row 2077: A01900

The file carries seven columns, and three of them are built to strain inference. The meter number is a six-digit code padded with zeros, but the numbering scheme changed at some point and the subscribers after 10,900 received a code that starts with a letter; because the file is sorted by subscriber number, the first lettered code appears only on row 2,077. The meter reading column comes from the field form and carries two separate forms of missingness: 111 cells are empty, and 80 cells have “n/a” written in by hand. The tariff tier is a category but is coded with digits.

None of these is corrupt data. All three are valid, meaningful and readable records at their source. The problem is that all of them will pass in front of a single inference rule.

The write side decides in the same silence. When a frame is written to a file, at least four things are chosen: the field delimiter, the decimal separator, how a value containing the delimiter is quoted, and with what text a missing value is represented. In this lesson a missing value is written as an empty cell; this is a decision that collapses all the forms of missingness at the source into a single form. Reading produces a type, writing turns that type back into text, and the two conversions do not have to be inverses of each other. The cheap and complete way to measure the difference is to compare the result with the source.

The Decision Inference Makes

# infer.py — type inference: if all values of a column parse to the same type,
# that type is chosen; if they do not, the column stays text. Only an empty cell counts as missing.
def parse(h: str):
    if h == "":
        return None, "missing"
    try:
        return int(h), "int"
    except ValueError:
        pass
    try:
        return float(h), "float"
    except ValueError:
        return h, "text"

def infer(rows, sample=None):
    """Infers the column types from a sample; if sample is None, all rows are scanned."""
    slice_ = rows if sample is None else rows[:sample]
    kinds, passes = [], 0
    for j in range(len(COLUMNS)):
        seen = set()
        for s in slice_:
            passes += 1
            seen.add(parse(s[j])[1])
        seen.discard("missing")
        kinds.append("int" if seen == {"int"}
                     else "float" if seen and seen <= {"int", "float"} else "text")
    return kinds, passes

def read(rows, kinds):
    """Reads with the inferred types; a cell that does not parse to the column type silently becomes missing."""
    table, dropped = [], 0
    for s in rows:
        record = []
        for j, h in enumerate(s):
            d, t = parse(h)
            if d is None or kinds[j] == "text":
                record.append(None if d is None else h)
            elif t == "text" or (kinds[j] == "int" and t == "float"):
                record.append(None)
                dropped += 1
            else:
                record.append(int(h) if kinds[j] == "int" else float(h))
        table.append(record)
    return table, dropped

def write_back(table):
    return [["" if v is None else repr(v) if isinstance(v, float) else str(v)
             for v in k] for k in table]

full_kinds, full_passes = infer(rows)
sample_kinds, sample_passes = infer(rows, 100)
table, dropped = read(rows, full_kinds)
s_table, s_dropped = read(rows, sample_kinds)
back, s_back = write_back(table), write_back(s_table)

WIDTH = [14, 12, 12, 12, 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(["column", "all rows", "first 100", "changed", "changed/100"]))
for j, name in enumerate(COLUMNS):
    c1 = sum(1 for i in range(len(rows)) if back[i][j] != rows[i][j])
    c2 = sum(1 for i in range(len(rows)) if s_back[i][j] != rows[i][j])
    print(show([name, full_kinds[j], sample_kinds[j], c1, c2]))
print(f"\nfull scan {full_passes} cell passes, inference from the first 100 rows {sample_passes} passes")
print(f"reading with the full types silently turned {dropped} cells into missing, "
      f"reading with the sample types {s_dropped} cells")

column            all rows   first 100     changed changed/100
subscriber_id          int         int           0           0
meter_id              text         int           0        3199
period                text        text           0           0
meter_reading         text        text           0           0
consumption_m3       float       float         422         422
tariff_code            int         int           0           0
region                text        text           0           0

full scan 22393 cell passes, inference from the first 100 rows 700 passes
reading with the full types silently turned 0 cells into missing, reading with the sample types 1123 cells

There are two separate events in the table. The first is in the last columns: the meter number comes out as text when all rows are scanned and as an integer when the first 100 rows are scanned. Inference does not get it wrong; in the first 100 rows all the values of that column truly parse to integers. What the sample saw is correct, but it is wrong for the whole dataset, and only row 2,077 knows the difference.

The cost of this slip shows in two numbers. When the meter number is read as an integer, all 3,199 cells change in the round trip: 2,076 cells lose their leading zeros, and the 1,123 lettered cells, since they parse to no type, silently become missing. There is no error line; the column was read, the frame was built, the row count stayed at 3,199. The only trace of the loss is that the meter number of those subscribers is now empty.

The second event is common to all inferences: 422 cells of the consumption column change in the round trip. In the source the values are written in a fixed format with two decimals; when read they become numbers, and when written back they take the number’s own short representation. The 0.00 cell becomes 0.0, the 20.10 cell becomes 20.1. The value is the same; the text is not. A round-trip check catches this and says that it stems not from the type itself but from its format; telling the two apart is the first step of deciding which loss matters.

Gain and the Hidden Default

# symptom.py — the symptom of a wrong type: an operation that raises no error but is
# meaningless or silently returns empty. The byte measure is taken from the file size in the round trip.
import io

col = lambda j: [k[j] for k in table]
print(f"subscriber_id read as int : average subscriber number "
      f"{round(sum(col(0)) / len(rows), 1)}")
print(f"tariff_code read as int   : average tariff tier "
      f"{round(sum(col(5)) / len(rows), 1)} m3")
readings = [v for v in col(3) if v is not None]
print(f"meter_reading read as text: {sum(1 for v in readings if v != 'n/a')} numeric values "
      f"turned into text, the largest of the column {max(readings)!r}")

def byte_count(records) -> int:
    b = io.StringIO()
    writer = csv.writer(b)
    writer.writerow(COLUMNS)
    writer.writerows(records)
    return len(b.getvalue().encode())

print(f"\nbytes  : source {byte_count(rows)}, write-back with full inference {byte_count(back)}, "
      f"with sample inference {byte_count(s_back)}")
print(f"passes : full scan {full_passes}, first 100 rows {sample_passes}")
print(f"code   : manual conversion per column 7 rules, inferred read 1 call")
wrong = sum(1 for j in (0, 3, 5) if full_kinds[j] in ("int", "text"))
print(f"result : {len(COLUMNS)} columns, {wrong} read with the wrong type; when taken from a "
      f"sample of 100 rows it became {wrong + 1} and {s_dropped} cells silently became missing")

subscriber_id read as int : average subscriber number 10690.5
tariff_code read as int   : average tariff tier 27.4 m3
meter_reading read as text: 3008 numeric values turned into text, the largest of the column 'n/a'

bytes  : source 139856, write-back with full inference 139434, with sample inference 128544
passes : full scan 22393, first 100 rows 700
code   : manual conversion per column 7 rules, inferred read 1 call
result : 7 columns, 3 read with the wrong type; when taken from a sample of 100 rows it became 4 and 1123 cells silently became missing

The first three lines show the symptom of a wrong type, and none of the three has an error message. The average of the subscriber number comes out as 10,690.5; this is a number, it can be computed, it can be reported, and it means nothing. The average of the tariff tier comes out as 27.4 m³; the tiers are 10, 25 and 40, and there is no tier called 27.4. The 3,008 numeric values of the meter reading column have turned into text, and the largest value of the column becomes 'n/a', because text comparison counts a letter as greater than a digit.

What the three have in common is that the wrongness shows up in use, not in the frame. When the frame is built nothing looks broken: the row count is right, the column names are in place, the cells are full. A wrong type speaks only when an operation is applied to that column, and when it speaks it produces a number, not an error. That is why the type is checked next to the read decision, not after the frame is built.

The gain side is clear: instead of writing a separate conversion rule for each of the seven columns, a single read call is written and the frame comes back typed. The full scan takes 22,393 cell passes; if inference is limited to the first 100 rows, the passes drop to 700, that is, one in thirty-two. The gain is visible on the byte side too: the file written back with full inference is 138,816 bytes, and with sample inference it drops to 127,926 bytes.

The hidden default is exactly inside that byte drop. The saving of 10,890 bytes is not a compression but a loss: 1,123 meter numbers were not written. The sample size was chosen without ever being written, the inference decided according to that choice, and the read produced no warning. A read call whose default is not written is a call that does not even say how many rows it looked at, and by the rule of this course it counts as unmeasured.

Summary

• A file split by a delimiter carries no type; the type is produced at read time by inference, and the rule is column-wide: a single value that does not parse drops the whole column to text.
• When all rows are scanned, 3 of the 7 columns arrive with the wrong type: the average of the subscriber number can be computed as 10,690.5 and that of the tariff tier as 27.4 m³, the 3,008 numeric values of the meter reading turn into text and the largest of the column becomes 'n/a'.
• When inference is made from the first 100 rows, the pass count drops from 22,393 to 700, but the meter number slides to integer: 1,123 cells of the lettered codes that start at row 2,077 become missing without a warning.
• The round-trip check counts the loss directly: with sample inference 3,199 cells change, the file drops from 139,238 bytes to 127,926 bytes, and 10,890 bytes of the drop are the meter numbers that were not written.
• The format loss is a separate item from the type: the fixed two-decimal notation turning into the short representation changes 422 cells under both inferences and corrupts no value.

Next Step

The dataset is now in a frame and its columns have been read with known types. The next question is how to reach a row. When “the subscribers between 10,100 and 10,200” is requested from a frame, there are two separate paths: access by name through the label index, and access by position through the order. The two paths are written as if they describe the same range, they carry the same name, they stand side by side. The next lesson runs the same slice in both access forms and asks a single number: how many rows does each return, and is the last end of the range included in the result.