Hands-on lab — Day 121: Loading and Inspecting Data

"""Exercise 1 -- the Namibia trap: read_csv() as an inference engine.

Run: python3 01_the_namibia_trap.py

pandas' default na_values list includes the literal string "NA". A country
code column containing "NA" for Namibia is read, by default, as a MISSING
value -- not an error, not a warning, just a quiet substitution. The country
does not disappear loudly; every downstream count of "missing" data is now
wrong in a way no test catches unless you already knew to look for it.
"""

import tempfile
from pathlib import Path

import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


tmpdir = Path(tempfile.mkdtemp(prefix="d121-ex01-"))
try:
    csv_path = tmpdir / "country_codes.csv"
    csv_path.write_text("code,country\nNA,Namibia\nUS,United States\nFR,France\n")

    default = pd.read_csv(csv_path)
    print("default read:")
    print(default)

    kept = pd.read_csv(csv_path, keep_default_na=False)
    print("\nkeep_default_na=False:")
    print(kept)

    check(
        "the default read turns Namibia's 'NA' into a real missing value",
        pd.isna(default.loc[0, "code"]),
    )
    check(
        "the default read leaves the OTHER two codes untouched",
        default.loc[1, "code"] == "US" and default.loc[2, "code"] == "FR",
    )
    check(
        "keep_default_na=False keeps 'NA' as the literal string",
        kept.loc[0, "code"] == "NA" and isinstance(kept.loc[0, "code"], str),
    )
    check(
        "keep_default_na=False does not turn anything else into a string it wasn't",
        kept.loc[1, "code"] == "US" and kept.loc[2, "code"] == "FR",
    )
    # The row count is identical either way -- the row never disappeared,
    # only the value inside one cell of it silently changed meaning.
    check("both reads keep all three rows", len(default) == 3 and len(kept) == 3)
finally:
    for f in tmpdir.iterdir():
        f.unlink()
    tmpdir.rmdir()

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("01_the_namibia_trap.py: every assertion held.")

"""Exercise 2 -- leading zeros: an identifier column silently becomes a number.

Run: python3 02_leading_zeros.py

An identifier column like "00123" LOOKS like text -- it has meaningful
leading zeros, the way a ZIP code, an account number or a barcode does. By
default read_csv() infers it as int64, because every character in it is a
digit, and the leading zeros are simply gone: "00123" becomes 123. Nothing
raises. The join against another system that still has "00123" fails
silently, one row at a time, with no exception naming which row.
"""

import tempfile
from pathlib import Path

import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


tmpdir = Path(tempfile.mkdtemp(prefix="d121-ex02-"))
try:
    csv_path = tmpdir / "customers.csv"
    csv_path.write_text("id,name\n00123,Alice\n00456,Bob\n00789,Carla\n")

    default = pd.read_csv(csv_path)
    print("default read:")
    print(default)
    print(default.dtypes)

    typed = pd.read_csv(csv_path, dtype={"id": "str"})
    print("\ndtype={'id': 'str'}:")
    print(typed)
    print(typed.dtypes)

    check("the default read infers id as int64", default["id"].dtype == "int64")
    check(
        "the default read silently drops the leading zeros: '00123' becomes 123",
        int(default.loc[0, "id"]) == 123,
    )
    check("dtype={'id': 'str'} keeps id as the str dtype", str(typed["id"].dtype) == "str")
    check(
        "dtype={'id': 'str'} preserves the leading zeros exactly",
        typed.loc[0, "id"] == "00123",
    )
    check(
        "the two reads disagree on the very same cell",
        str(default.loc[0, "id"]) != typed.loc[0, "id"],
    )
finally:
    for f in tmpdir.iterdir():
        f.unlink()
    tmpdir.rmdir()

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("02_leading_zeros.py: every assertion held.")

"""Exercise 3 -- precision: an ID above 2**53 cannot survive a trip through float64.

Run: python3 03_precision_loss.py

read_csv() infers a purely-numeric column as int64, which represents every
integer in this example exactly. The corruption happens one step LATER, the
moment anything casts that column to float64 -- a join, an arithmetic
operation, a naive "convert everything numeric to float" cleanup step.
float64 has a 53-bit mantissa: it can represent every integer up to 2**53
exactly, and past that boundary it silently rounds to the nearest value it
CAN represent, with no error and no warning.
"""

import tempfile
from pathlib import Path

import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


BIG_ID = 2**53 + 1  # 9007199254740993 -- one past the exact-integer boundary
print(f"2**53 + 1 = {BIG_ID}")

tmpdir = Path(tempfile.mkdtemp(prefix="d121-ex03-"))
try:
    csv_path = tmpdir / "orders.csv"
    csv_path.write_text(f"order_id\n{BIG_ID}\n")

    df = pd.read_csv(csv_path)
    print("\nread_csv() infers:")
    print(df.dtypes)
    read_value = int(df.loc[0, "order_id"])
    print("value as read:", read_value)

    check("read_csv() infers the column as int64", df["order_id"].dtype == "int64")
    check("int64 preserves the ID exactly, digit for digit", read_value == BIG_ID)

    promoted = df.astype({"order_id": "float64"})
    promoted_value = int(promoted.loc[0, "order_id"])
    print("\nafter .astype('float64'):")
    print(promoted.dtypes)
    print("value after float64 round-trip:", promoted_value)
    print(f"exact:    {BIG_ID}")
    print(f"corrupted:{promoted_value}")

    check(
        "the float64 round-trip silently changes the value",
        promoted_value != BIG_ID,
    )
    check(
        "the corrupted value is exactly one less than the true ID -- the last digit collapsed",
        BIG_ID - promoted_value == 1,
    )
    # Show the exact differing digits, not just "it's wrong".
    exact_str = str(BIG_ID)
    corrupt_str = str(promoted_value)
    check(
        "both numbers have the same number of digits (16)",
        len(exact_str) == len(corrupt_str) == 16,
    )
    first_diff = next(i for i, (a, b) in enumerate(zip(exact_str, corrupt_str)) if a != b)
    print(f"\nfirst differing digit is at position {first_diff}: "
          f"'{exact_str[first_diff]}' (exact) vs '{corrupt_str[first_diff]}' (corrupted)")
    check(
        "the two numbers agree on every digit except the very last one",
        exact_str[:-1] == corrupt_str[:-1] and exact_str[-1] != corrupt_str[-1],
    )
finally:
    for f in tmpdir.iterdir():
        f.unlink()
    tmpdir.rmdir()

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("03_precision_loss.py: every assertion held.")

"""Exercise 4 -- dates: what parse_dates changes, and why a string date lies.

Run: python3 04_dates.py

Without parse_dates, a date column is just text -- the pandas-3.0 str
dtype, sorted the way any string sorts: character by character. That looks
fine as long as every date is written in the same fixed-width format. The
moment one row is written without a leading zero, the string sort silently
puts it in the wrong place, and nothing about the column's dtype warns you
this could happen.
"""

import tempfile
from pathlib import Path

import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


tmpdir = Path(tempfile.mkdtemp(prefix="d121-ex04-"))
try:
    csv_path = tmpdir / "events.csv"
    csv_path.write_text("event,date\nfirst,2024-01-05\nsecond,2024-01-20\nthird,2024-1-9\n")

    unparsed = pd.read_csv(csv_path)
    print("without parse_dates:")
    print(unparsed)
    print(unparsed.dtypes)

    parsed = pd.read_csv(csv_path, parse_dates=["date"])
    print("\nwith parse_dates=['date']:")
    print(parsed)
    print(parsed.dtypes)

    check("without parse_dates the column is the str dtype", str(unparsed["date"].dtype) == "str")
    check(
        "with parse_dates the column becomes a real datetime64 dtype",
        str(parsed["date"].dtype).startswith("datetime64"),
    )

    string_sorted = unparsed.sort_values("date")["event"].tolist()
    parsed_sorted = parsed.sort_values("date")["event"].tolist()
    print("\nstring-sorted event order:  ", string_sorted)
    print("parsed-datetime event order:", parsed_sorted)

    # "third" is 2024-01-09 -- chronologically between "first" (Jan 5) and
    # "second" (Jan 20). Written without a leading zero as "2024-1-9", it
    # sorts as a STRING after "2024-01-20", because the character '1' (from
    # "2024-1-9") is greater than '0' (from "2024-01-...") at that position.
    check(
        "the true chronological order is first, third, second",
        parsed_sorted == ["first", "third", "second"],
    )
    check(
        "the raw string sort gets this WRONG -- it puts 'third' last",
        string_sorted == ["first", "second", "third"],
    )
    check(
        "the two sort orders genuinely disagree, proving the string sort is unsafe",
        string_sorted != parsed_sorted,
    )
finally:
    for f in tmpdir.iterdir():
        f.unlink()
    tmpdir.rmdir()

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("04_dates.py: every assertion held.")

"""Exercise 5 -- encoding: pandas assumes UTF-8, and a mismatch fails loudly.

Run: python3 05_encoding.py

read_csv()'s `encoding` parameter defaults to UTF-8. A file actually
written in a different encoding -- latin-1 (ISO-8859-1) is common in older
exports from Windows and legacy databases -- either raises a
UnicodeDecodeError on the first byte sequence it cannot interpret as valid
UTF-8, or, when the byte sequence HAPPENS to also be valid (but different)
UTF-8, decodes into visibly wrong characters ("mojibake") with no error at
all. Only naming the correct encoding round-trips the text exactly.
"""

import tempfile
from pathlib import Path

import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


ORIGINAL_NAME = "José"  # "José"
ORIGINAL_CITY = "São Paulo"  # "São Paulo"

tmpdir = Path(tempfile.mkdtemp(prefix="d121-ex05-"))
try:
    csv_path = tmpdir / "customers_latin1.csv"
    # Written deliberately in latin-1, the encoding this file actually is.
    csv_path.write_text(f"name,city\n{ORIGINAL_NAME},{ORIGINAL_CITY}\n", encoding="latin-1")

    error_class = None
    error_message = ""
    try:
        pd.read_csv(csv_path, encoding="utf-8")
    except UnicodeDecodeError as exc:
        error_class = type(exc).__name__
        error_message = str(exc)
    print(f"reading with encoding='utf-8' (WRONG for this file): {error_class}: {error_message}")

    check(
        "reading a latin-1 file as UTF-8 raises UnicodeDecodeError -- it does not silently succeed",
        error_class == "UnicodeDecodeError",
    )

    correct = pd.read_csv(csv_path, encoding="latin-1")
    print("\nreading with encoding='latin-1' (correct for this file):")
    print(correct)

    check(
        "reading with the correct encoding recovers the accented name exactly",
        correct.loc[0, "name"] == ORIGINAL_NAME,
    )
    check(
        "reading with the correct encoding recovers the accented city exactly",
        correct.loc[0, "city"] == ORIGINAL_CITY,
    )

    # pandas' default (no encoding= given) is UTF-8, and fails the same way
    # on this file -- confirming the parameter's documented default rather
    # than merely asserting it.
    default_error_class = None
    try:
        pd.read_csv(csv_path)
    except UnicodeDecodeError as exc:
        default_error_class = type(exc).__name__
    check(
        "the undecorated default (no encoding= argument) behaves identically to encoding='utf-8'",
        default_error_class == "UnicodeDecodeError",
    )
finally:
    for f in tmpdir.iterdir():
        f.unlink()
    tmpdir.rmdir()

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("05_encoding.py: every assertion held.")

"""Exercise 6 -- reading in chunks: a file larger than memory, one piece at a time.

Run: python3 06_chunking.py

`chunksize` turns read_csv() into an iterator of DataFrames instead of one
big DataFrame, so you can process a file far larger than available memory
by never holding more than one chunk of it at once. The claim this exercise
proves: an aggregate computed chunk-by-chunk must equal the whole-file
answer EXACTLY -- chunking changes how the data arrives, never what it
adds up to.
"""

import tempfile
from pathlib import Path

import numpy as np
import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


tmpdir = Path(tempfile.mkdtemp(prefix="d121-ex06-"))
try:
    csv_path = tmpdir / "readings.csv"
    rng = np.random.default_rng(42)
    n_rows = 50_000
    values = rng.integers(1, 1000, size=n_rows)
    pd.DataFrame({"value": values}).to_csv(csv_path, index=False)
    print(f"wrote {n_rows} rows to a temporary CSV")

    whole_sum = int(pd.read_csv(csv_path)["value"].sum())
    print("whole-file sum:", whole_sum)

    chunk_sum = 0
    n_chunks = 0
    n_rows_seen = 0
    for chunk in pd.read_csv(csv_path, chunksize=1_000):
        chunk_sum += int(chunk["value"].sum())
        n_rows_seen += len(chunk)
        n_chunks += 1
    print(f"chunked sum ({n_chunks} chunks of up to 1,000 rows): {chunk_sum}")

    check("chunking visits every row exactly once", n_rows_seen == n_rows)
    check("chunking produces the expected number of chunks", n_chunks == 50)
    check(
        "the chunk-by-chunk sum equals the whole-file sum exactly",
        chunk_sum == whole_sum,
    )

    # A second aggregate, to show it's not a coincidence of sum() specifically:
    # the row count is also exact when accumulated chunk by chunk.
    counted = 0
    for chunk in pd.read_csv(csv_path, chunksize=777):  # a chunk size that does not divide evenly
        counted += len(chunk)
    check(
        "row count accumulated over odd-sized chunks (777) still matches exactly",
        counted == n_rows,
    )
finally:
    for f in tmpdir.iterdir():
        f.unlink()
    tmpdir.rmdir()

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("06_chunking.py: every assertion held.")

"""Exercise 7 -- the day's headline claim: CSV loses dtypes, Parquet keeps them.

Run: python3 07_csv_vs_parquet.py

CSV is plain text. Every value that goes into a CSV file becomes a
character string on disk, and every value that comes back out is
RE-INFERRED from scratch by read_csv()'s type-inference engine -- the same
engine exercises 1-4 spent proving is not infallible. Parquet is a typed,
columnar binary format: the dtype of every column is written into the file
itself, so reading it back is a lookup, not a guess. This exercise writes
the same DataFrame -- including a nullable Int64 column with a genuine
missing value -- through both formats and compares the dtypes side by side.
"""

import tempfile
from pathlib import Path

import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


original = pd.DataFrame(
    {
        "order_id": pd.array([1001, 1002, pd.NA], dtype="Int64"),
        "price": pd.array([19.99, 44.50, 7.25], dtype="float64"),
        "in_stock": pd.array([True, False, True], dtype="bool"),
        "category": pd.array(["books", "tools", "books"], dtype="str"),
    }
)
print("original dtypes:")
print(original.dtypes)

tmpdir = Path(tempfile.mkdtemp(prefix="d121-ex07-"))
try:
    csv_path = tmpdir / "orders.csv"
    original.to_csv(csv_path, index=False)
    round_tripped_csv = pd.read_csv(csv_path)
    print("\ndtypes after a CSV round-trip:")
    print(round_tripped_csv.dtypes)
    print(round_tripped_csv)

    pq_path = tmpdir / "orders.parquet"
    original.to_parquet(pq_path)
    round_tripped_parquet = pd.read_parquet(pq_path)
    print("\ndtypes after a Parquet round-trip:")
    print(round_tripped_parquet.dtypes)
    print(round_tripped_parquet)

    csv_changed = [
        col for col in original.columns
        if str(original[col].dtype) != str(round_tripped_csv[col].dtype)
    ]
    parquet_changed = [
        col for col in original.columns
        if str(original[col].dtype) != str(round_tripped_parquet[col].dtype)
    ]
    print("\ncolumns whose dtype changed via CSV:    ", csv_changed)
    print("columns whose dtype changed via Parquet:", parquet_changed)

    check(
        "the CSV round-trip changes at least one column's dtype",
        len(csv_changed) >= 1,
    )
    check(
        "specifically, the nullable Int64 order_id column is not what it started as after CSV",
        str(round_tripped_csv["order_id"].dtype) != "Int64",
    )
    check(
        "the missing order_id survives the CSV round-trip only as a float NaN, not pd.NA",
        round_tripped_csv["order_id"].isna().sum() == 1
        and str(round_tripped_csv["order_id"].dtype) == "float64",
    )
    check(
        "the Parquet round-trip preserves every column's dtype EXACTLY",
        parquet_changed == [],
    )
    check(
        "the Parquet round-trip keeps order_id as the nullable Int64 dtype, missing value and all",
        str(round_tripped_parquet["order_id"].dtype) == "Int64"
        and round_tripped_parquet["order_id"].isna().sum() == 1,
    )
    check(
        "the Parquet round-trip's actual values equal the originals exactly, not just the dtypes",
        round_tripped_parquet.equals(original),
    )
finally:
    for f in tmpdir.iterdir():
        f.unlink()
    tmpdir.rmdir()

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("07_csv_vs_parquet.py: every assertion held.")

"""Exercise 8 -- the inspection battery you run on any unfamiliar frame.

Run: python3 08_inspection_battery.py

In order: .head(), .info(), .dtypes, .describe(), .isna().sum(),
.nunique(), .value_counts(), memory_usage(deep=True). Each answers a
different question about data you have not seen before, and running all
eight, in this order, costs seconds and catches most of the silent
failures the earlier exercises demonstrate one at a time. This exercise
builds a frame with KNOWN properties -- an exact missing-value count, an
exact number of distinct values per column, and one column whose most
common value is known in advance -- and checks the battery's answers
against those known values.
"""

import io

import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


df = pd.DataFrame(
    {
        "region": ["north", "south", "north", "east", "north", "south", None, "north"],
        "amount": [10, 20, None, 15, 30, 20, 25, 10],
    }
)
print("the frame under inspection:")
print(df)

# 1. .head() -- the fastest sanity check that the data loaded the way you expected.
print("\n1. .head(3):")
print(df.head(3))
check(".head(3) returns exactly 3 rows", len(df.head(3)) == 3)

# 2. .info() -- row count, columns, non-null counts and dtypes in one block.
print("\n2. .info():")
buf = io.StringIO()
df.info(buf=buf)
info_text = buf.getvalue()
print(info_text)
check(".info() reports the correct row count (8 entries)", "8 entries" in info_text)
check(".info() names both columns", "region" in info_text and "amount" in info_text)

# 3. .dtypes -- what kind of thing is actually in each column.
print("3. .dtypes:")
print(df.dtypes)
check("region is the pandas-3.0 str dtype", str(df["region"].dtype) == "str")
check("amount, with a None in it, is float64 (int64 has no missing-value slot)", df["amount"].dtype == "float64")

# 4. .describe() -- summary statistics for the numeric columns.
print("\n4. .describe():")
desc = df.describe()
print(desc)
check(".describe() counts only the 7 non-missing amount values", desc.loc["count", "amount"] == 7.0)

# 5. .isna().sum() -- exactly how much is missing, per column.
print("\n5. .isna().sum():")
na_counts = df.isna().sum()
print(na_counts)
check("region has exactly 1 missing value", na_counts["region"] == 1)
check("amount has exactly 1 missing value", na_counts["amount"] == 1)

# 6. .nunique() -- how many distinct values, per column (missing values not counted).
print("\n6. .nunique():")
uniq = df.nunique()
print(uniq)
check("region has exactly 3 distinct non-missing values (north, south, east)", uniq["region"] == 3)
check("amount has exactly 5 distinct non-missing values (10, 20, 15, 30, 25)", uniq["amount"] == 5)

# 7. .value_counts() -- the actual distribution, most common first.
print("\n7. region.value_counts():")
vc = df["region"].value_counts()
print(vc)
check("the single most common region is 'north'", vc.index[0] == "north")
check("'north' appears exactly 4 times", vc.iloc[0] == 4)

# 8. memory_usage(deep=True) -- the real byte cost, including string storage.
print("\n8. memory_usage(deep=True):")
mem = df.memory_usage(deep=True)
print(mem)
check("memory_usage(deep=True) reports a positive byte count for every column", (mem > 0).all())

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("08_inspection_battery.py: every assertion held.")

"""Exercise 9 -- category memory: converting a low-cardinality string column.

Run: python3 09_category_memory.py

A "category" dtype stores each distinct value ONCE and represents every row
as a small integer code pointing at it. For a column with few distinct
values repeated many times -- region names, status flags, product
categories -- that trades a large amount of repeated string storage for a
small lookup table plus one integer per row. This exercise measures the
actual reduction on the authoring machine and asserts the RATIO clears a
stated factor, never a byte count, because byte counts are a fact about one
machine's malloc behaviour on one day.
"""

import numpy as np
import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


rng = np.random.default_rng(42)
n_rows = 20_000
regions = rng.choice(["north", "south", "east", "west"], size=n_rows)
df = pd.DataFrame({"region": regions})
print(f"{n_rows} rows, {df['region'].nunique()} distinct region values")
print("region dtype:", df["region"].dtype)

mem_str = int(df["region"].memory_usage(deep=True))
df["region_cat"] = df["region"].astype("category")
mem_cat = int(df["region_cat"].memory_usage(deep=True))

print(f"\nmemory_usage(deep=True) as {df['region'].dtype}:  {mem_str:>8,} bytes")
print(f"memory_usage(deep=True) as category:          {mem_cat:>8,} bytes")
ratio = mem_str / mem_cat
print(f"reduction ratio: {ratio:.2f}x")

check("the category dtype has the same 4 distinct values as the original", df["region_cat"].nunique() == 4)
check(
    "the category column is genuinely smaller than the string column",
    mem_cat < mem_str,
)
check(
    "the reduction ratio clears at least 5x on this 4-category, 20,000-row column",
    ratio >= 5.0,
)
check(
    "converting to category does not change the actual values, only the storage",
    (df["region_cat"].astype("str") == df["region"]).all(),
)

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("09_category_memory.py: every assertion held.")

"""Supplementary -- JSON, SQL (sqlite3) and the stdlib csv module.

Run: python3 10_other_formats.py

Not one of the nine graded exercises -- this script exists to back the
lesson's Tools section with real, captured output for the formats it
covers beyond CSV and Parquet. openpyxl (Excel) is deliberately NOT run
here because it is not installed in this environment; the lesson describes
it from documentation only and says so plainly.
"""

import csv
import io
import json
import sqlite3
import tempfile
from pathlib import Path

import pandas as pd

checks = 0
failures = 0


def check(label, condition):
    global checks, failures
    checks += 1
    if condition:
        print(f"  ok: {label}")
    else:
        print(f"  FAIL: {label}")
        failures += 1


tmpdir = Path(tempfile.mkdtemp(prefix="d121-ex10-"))
try:
    # --- JSON ---------------------------------------------------------
    records = [
        {"id": 1, "name": "Alice", "active": True},
        {"id": 2, "name": "Bob", "active": False},
    ]
    json_path = tmpdir / "users.json"
    json_path.write_text(json.dumps(records))
    df_json = pd.read_json(json_path)
    print("read_json() on a list of records:")
    print(df_json)
    print(df_json.dtypes)
    check("read_json() recovers all 2 records", len(df_json) == 2)
    check("read_json() infers 'active' as a real boolean column", df_json["active"].dtype == "bool")

    # --- sqlite3 --------------------------------------------------------
    db_path = tmpdir / "orders.db"
    conn = sqlite3.connect(db_path)
    orders = pd.DataFrame({"order_id": [1, 2, 3], "amount": [19.99, 44.50, 7.25]})
    orders.to_sql("orders", conn, index=False, if_exists="replace")
    from_sql = pd.read_sql("SELECT * FROM orders WHERE amount > 10", conn)
    print("\nread_sql() against a real sqlite3 connection:")
    print(from_sql)
    check("read_sql()'s WHERE filter runs inside the database, not in pandas", len(from_sql) == 2)
    check("read_sql() returns the correct rows", set(from_sql["order_id"]) == {1, 2})
    conn.close()

    # --- stdlib csv module ------------------------------------------------
    # Where the stdlib module beats pandas: streaming one row at a time with
    # NO type inference at all -- every field arrives as exactly the string
    # that was in the file, which is sometimes exactly what you want.
    csv_path = tmpdir / "raw.csv"
    csv_path.write_text("id,code\n001,00A\n002,00B\n")
    rows = []
    with open(csv_path, newline="") as fh:
        reader = csv.DictReader(fh)
        for row in reader:
            rows.append(row)
    print("\nstdlib csv.DictReader (no type inference at all):")
    print(rows)
    check("csv.DictReader keeps every field as a plain str, leading zeros included", rows[0]["id"] == "001")
    check(
        "read_csv(), by contrast, would infer 'id' as an integer and drop those zeros",
        int(pd.read_csv(csv_path)["id"].iloc[0]) == 1,
    )
finally:
    for f in tmpdir.iterdir():
        f.unlink()
    tmpdir.rmdir()

print(f"\n{checks} checks, {failures} failure(s).")
if failures:
    raise SystemExit(1)
print("10_other_formats.py: every assertion held.")

lesson_id: D121
day: 121
kind: guided-build
languages: [python, bash]
setup_commands:
  - cd labs/sections/math-statistics-and-data/day-121-loading-and-inspecting-data
  - python3 -m venv .venv
  - .venv/bin/pip install -r requirements/requirements.txt
  - .venv/bin/python3 -c "import pandas; print(pandas.__version__)"
run_commands:
  - 'cd examples && ../.venv/bin/python3 01_the_namibia_trap.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 02_leading_zeros.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 03_precision_loss.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 04_dates.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 05_encoding.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 06_chunking.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 07_csv_vs_parquet.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 08_inspection_battery.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 09_category_memory.py && cd ..'
  - 'cd examples && ../.venv/bin/python3 10_other_formats.py && cd ..'
  - .venv/bin/python3 starter/check_progress.py
test_commands:
  - bash tests/run_tests.sh
cleanup_commands:
  - "find . -path ./.venv -prune -o -type d -name '__pycache__' -print -exec rm -rf -- {} +"
  - rm -rf .pytest_cache
  - 'rm -rf .venv  # optional: removes the lab virtual environment'
  - 'git checkout -- starter/  # optional: reset your work'
requires_network: true
requires_api_key: false
estimated_minutes: 40
last_executed: '2026-08-19'
executed_on: 'macOS 26.5.2 (Apple Silicon, arm64), Python 3.14.0, pandas 3.0.5, pyarrow 25.0.1, numpy 2.5.2, bash 3.2.57 -- bash tests/run_tests.sh -> 42 checks, 0 failure(s), exit 0. All ten reference scripts in examples/ (nine graded exercises plus 10_other_formats.py, a supplementary script demonstrating JSON, sqlite3 and the stdlib csv module for the lessons Tools section) exit 0 with every internal assertion holding, run individually and via the harness. starter/check_progress.py reports 0 of 9 exercises complete on the untouched checkout (exit 1), and the harness separately solves every blank in a scratch copy and confirms 9 of 9 complete with exit 0, without modifying the real starter/exercises.py on disk. Section 5 of the harness deliberately breaks the assertion inside 03_precision_loss.py (flips read_value == BIG_ID to !=), confirms the run exits non-zero with a printed FAIL line, restores the file, and confirms it passes again -- so the suite is demonstrated to be capable of failing rather than merely claimed to be. Section 6 confirms no .csv, .parquet or .db file and no __pycache__ directory is left anywhere in the lab after a full run. Everything was run through a real lab-local .venv created by the documented setup commands. Four honesty notes from this run. FIRST: openpyxl, matplotlib, scipy and polars are not installed in this environment; the lessons Tools section describes openpyxl (Excel) and polars scan_csv from their public documentation only, and says so plainly -- no output attributed to either is reproduced anywhere. SECOND: exercise 5s encoding trap produced a hard UnicodeDecodeError on this run rather than silent mojibake, because the specific latin-1 byte for the accented e in Jose (0xE9) is not a valid UTF-8 continuation byte in that position; the lesson states both possible failure modes honestly and reports which one this specific example actually produced, rather than claiming mojibake occurred when it did not. THIRD: exercise 9s category-memory reduction measured 12.40x on this run (250,204 bytes as the str dtype versus 20,183 bytes as category, on a seeded 20,000-row, 4-category column); the lab asserts the ratio clears 5x, a bar comfortably below what this run measured, and expected-output/FIELDS.md records the exact byte counts as machine-specific while the ratio claim is the portable one. FOURTH: exercise 7s CSV-vs-Parquet comparison uses a column with a genuine missing value (pd.NA in a nullable Int64 column) specifically because that is where the two formats diverge most sharply -- the CSV round-trip promotes the column to float64 and prints the surviving IDs with a trailing .0, while Parquet preserves the exact Int64 dtype and the pd.NA marker; both are captured directly from this run, not assumed from documentation.'

# What is installed, why, and what it costs

Three packages, all free and open source, installed into a lab-local
virtual environment that `rm -rf .venv` completely undoes.

| Package | Version pinned | Licence | What this lab uses it for |
| --- | --- | --- | --- |
| `pandas` | 3.0.5 | BSD 3-Clause | Every `read_csv`, `read_json`, `read_sql`, `read_parquet`, `to_csv` and `to_parquet` call in this lab. Pinned exactly because this day's captured output — dtypes, error messages, the `str` default — is version-specific. |
| `pyarrow` | 25.0.1 | Apache 2.0 | The engine behind `to_parquet()` / `read_parquet()` (exercise 7) and the storage backing pandas 3.0's default `str` dtype and `Int64` nullable arrays. |
| `numpy` | 2.5.2 | BSD 3-Clause | The random number generator behind the synthetic columns in exercises 6 and 9. |

`sqlite3`, `csv`, `json` and `io`, used in exercise 10 and the inspection
battery, are all standard library — nothing extra to install for them.

There is no paid tier of anything in this lab, no account, no key and no
signup, personally or commercially.

## Why the versions are pinned exactly, not just floored

This lab's central claims are pandas-3.0-specific and would print
different, equally-correct values on pandas 2.x:

- A plain integer column of digits like `"00123"` is inferred as `int64`
  here; the exact inference rules for `dtype=` overrides have not changed
  across recent pandas majors, but the default string dtype it prints when
  you check `.dtypes` elsewhere in this lab (`str` versus `object`) has.
- The CSV-versus-Parquet dtype comparison in exercise 7 depends on the
  nullable `Int64` dtype's exact round-trip behaviour through
  `to_csv()`/`read_csv()`, which is consistent across recent pandas
  releases but is asserted here against the specific 3.0.5 install this
  lab was authored and captured on.
- `pd.Series(["a", "b"]).dtype` prints `str` on 3.0.5 (used throughout the
  inspection battery in exercise 8); it prints `object` on any pandas
  release before 3.0.

Running this lab's suite against a different pandas major version may
produce differences that are not bugs — `expected-output/FIELDS.md` states
precisely which values are version-specific and which are not.

## The one time the network is needed

```bash
.venv/bin/pip install -r requirements/requirements.txt
```

That is the only command in the lab that opens a connection. Every script
this lab runs writes its own tiny CSV, JSON, SQLite database or Parquet
file into a temporary directory it created itself and deletes before
returning — nothing is downloaded, and no external dataset is fetched.

## What is deliberately *not* installed

**openpyxl**, **matplotlib**, **scipy** and **polars** are not installed in
this environment. The lesson's Tools section describes Excel support via
`openpyxl` from pandas' own documentation, since it is not installed here
— it says so plainly, and no output attributed to `read_excel()` is
reproduced anywhere in this lab or its lesson. polars' lazy `scan_csv` is
covered the same way, from its public documentation, as a design contrast.

## If you cannot install anything at all

pandas is not in the Python standard library, and there is no reduced path
through most of this lab without it. Exercise 10's stdlib-`csv` half is the
one part that would still run on a bare Python installation; everything
else genuinely needs pandas 3.0.5's specific behaviour, which nothing else
on your system will reproduce. If pandas cannot be installed, read the
lesson's captured output and this lab's `expected-output/` directory
instead; every number there came from a real run and is not invented.

pandas==3.0.5
pyarrow==25.0.1
numpy==2.5.2

"""Run from this directory (or `python3 starter/check_progress.py` from the
lab root): reports how many of the nine exercises in exercises.py are
complete and correct, against the same expected values the reference
examples/ scripts assert.

Exit code is 0 only when all nine are correct, matching the convention the
rest of this lab's tests use.
"""

import math
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent))

import exercises as ex

passed = 0
total = 9


def report(number, description, fn, expected_check):
    """Run one exercise function, catching the NameError an unfilled blank
    raises, and check its return value against expected_check(value)."""
    global passed
    try:
        result = fn()
    except NameError as exc:
        print(f"  {number}. {description}: NOT YET COMPLETE ({exc})")
        return
    except Exception as exc:  # noqa: BLE001 -- report any other mistake too
        print(f"  {number}. {description}: ERROR -- {exc!r}")
        return
    try:
        ok = expected_check(result)
    except Exception as exc:  # a malformed return value shouldn't crash the report
        print(f"  {number}. {description}: WRONG    (return value {result!r} raised {exc!r})")
        return
    if ok:
        passed += 1
        print(f"  {number}. {description}: correct  (got {result!r})")
    else:
        print(f"  {number}. {description}: WRONG    (got {result!r})")


report(
    1,
    "the Namibia trap",
    ex.ex01_the_namibia_trap,
    lambda r: (isinstance(r[0], float) and math.isnan(r[0])) and r[1] == "NA",
)
report(
    2,
    "leading zeros",
    ex.ex02_leading_zeros,
    lambda r: r[0] == 123 and r[1] == "00123",
)
report(
    3,
    "precision loss",
    ex.ex03_precision_loss,
    lambda r: r[0] == 2**53 + 1 and r[1] == 2**53,
)
report(
    4,
    "dates",
    ex.ex04_dates,
    lambda r: r[0] == "str" and r[1].startswith("datetime64"),
)
report(
    5,
    "encoding",
    ex.ex05_encoding,
    lambda r: r == "UnicodeDecodeError",
)
report(
    6,
    "chunking",
    ex.ex06_chunking,
    lambda r: r[0] == 28 and r[1] == 28,
)
report(
    7,
    "CSV vs Parquet",
    ex.ex07_csv_vs_parquet,
    lambda r: r[0] != "Int64" and r[1] == "Int64",
)
report(
    8,
    "inspection battery",
    ex.ex08_inspection_battery,
    lambda r: r == (1, "north"),
)
report(
    9,
    "category memory",
    ex.ex09_category_memory,
    lambda r: r[0] > r[1] and (r[0] / r[1]) >= 2.0,
)

print(f"\n{passed} of {total} exercises complete.")
sys.exit(0 if passed == total else 1)

"""Day 121 starter -- nine exercises, one function each.

Each function below is a working skeleton: the setup -- including writing
whatever small file the exercise needs into a temporary directory -- is
written for you, and exactly one line is left for you to write, marked
with the sentinel name `_FILL_THIS_IN`. Replace that name with real code.
Leaving it as-is raises a clear NameError when the function runs, which
`check_progress.py` catches and reports -- it does not crash the whole
script.

Every function cleans up the temporary file(s) it wrote before returning,
so running this file, complete or not, leaves nothing behind.

Read `../examples/` for the fully worked reference AFTER you have tried
each one yourself; that is where every one of these ideas is explained in
comments. Run your progress with:

    python3 check_progress.py

from inside this `starter/` directory (or `python3 starter/check_progress.py`
from the lab root).
"""

import tempfile
from pathlib import Path

import pandas as pd


def _scratch_file(name, content, encoding="utf-8"):
    """Write `content` into a fresh temp directory and return its path."""
    tmpdir = Path(tempfile.mkdtemp(prefix="d121-starter-"))
    path = tmpdir / name
    path.write_text(content, encoding=encoding)
    return path


def _cleanup(path):
    path.unlink()
    path.parent.rmdir()


def ex01_the_namibia_trap():
    """Read a country-code CSV containing "NA" for Namibia. Return
    (default_code, kept_code) -- the value read by default, and the value
    read with keep_default_na=False."""
    path = _scratch_file("countries.csv", "code,country\nNA,Namibia\nUS,United States\n")
    try:
        default_df = pd.read_csv(path)
        kept_df = _FILL_THIS_IN  # pd.read_csv(path, keep_default_na=False)
        return default_df.loc[0, "code"], kept_df.loc[0, "code"]
    finally:
        _cleanup(path)


def ex02_leading_zeros():
    """Read an id column of "00123" two ways. Return (default_id, typed_id)."""
    path = _scratch_file("customers.csv", "id,name\n00123,Alice\n")
    try:
        default_df = pd.read_csv(path)
        typed_df = pd.read_csv(path, dtype=_FILL_THIS_IN)  # {'id': 'str'}
        return int(default_df.loc[0, "id"]), typed_df.loc[0, "id"]
    finally:
        _cleanup(path)


def ex03_precision_loss():
    """Read an integer above 2**53 and round-trip it through float64.
    Return (as_int64, as_float64) as plain Python ints."""
    big_id = 2**53 + 1
    path = _scratch_file("orders.csv", f"order_id\n{big_id}\n")
    try:
        df = pd.read_csv(path)
        as_int64 = int(df.loc[0, "order_id"])
        promoted = df.astype({"order_id": _FILL_THIS_IN})  # 'float64'
        as_float64 = int(promoted.loc[0, "order_id"])
        return as_int64, as_float64
    finally:
        _cleanup(path)


def ex04_dates():
    """Read a date column with and without parse_dates. Return
    (unparsed_dtype, parsed_dtype) as strings."""
    path = _scratch_file("events.csv", "event,date\nfirst,2024-01-05\n")
    try:
        unparsed = pd.read_csv(path)
        parsed = pd.read_csv(path, parse_dates=_FILL_THIS_IN)  # ['date']
        return str(unparsed["date"].dtype), str(parsed["date"].dtype)
    finally:
        _cleanup(path)


def ex05_encoding():
    """Write a latin-1 file and read it back with the WRONG encoding.
    Return the name of the exception class raised (a string), or None if
    nothing was raised."""
    path = _scratch_file("name.csv", "name\nJosé\n", encoding="latin-1")
    try:
        try:
            pd.read_csv(path, encoding=_FILL_THIS_IN)  # 'utf-8'
            return None
        except UnicodeDecodeError as exc:
            return type(exc).__name__
    finally:
        _cleanup(path)


def ex06_chunking():
    """Sum a column two ways: all at once, and via chunksize=3. Return
    (whole_sum, chunked_sum) as plain ints."""
    path = _scratch_file("readings.csv", "value\n1\n2\n3\n4\n5\n6\n7\n")
    try:
        whole_sum = int(pd.read_csv(path)["value"].sum())
        chunked_sum = 0
        for chunk in pd.read_csv(path, chunksize=_FILL_THIS_IN):  # 3
            chunked_sum += int(chunk["value"].sum())
        return whole_sum, chunked_sum
    finally:
        _cleanup(path)


def ex07_csv_vs_parquet():
    """Round-trip a nullable-Int64 column through CSV and through Parquet.
    Return (csv_dtype, parquet_dtype) as strings."""
    tmpdir = Path(tempfile.mkdtemp(prefix="d121-starter-"))
    df = pd.DataFrame({"order_id": pd.array([1001, 1002, pd.NA], dtype="Int64")})
    csv_path = tmpdir / "orders.csv"
    pq_path = tmpdir / "orders.parquet"
    try:
        df.to_csv(csv_path, index=False)
        df.to_parquet(pq_path)
        csv_dtype = str(pd.read_csv(csv_path)["order_id"].dtype)
        parquet_dtype = str(_FILL_THIS_IN["order_id"].dtype)  # pd.read_parquet(pq_path)
        return csv_dtype, parquet_dtype
    finally:
        csv_path.unlink()
        pq_path.unlink()
        tmpdir.rmdir()


def ex08_inspection_battery():
    """On a frame with a known missing value and a known most-common
    region, return (na_count_for_region, top_region) using .isna().sum()
    and .value_counts()."""
    df = pd.DataFrame({"region": ["north", "north", "south", None, "north"]})
    na_count = df["region"].isna().sum()
    top_region = _FILL_THIS_IN  # df["region"].value_counts().index[0]
    return int(na_count), top_region


def ex09_category_memory():
    """Convert a string column to category and return (str_bytes,
    category_bytes) from memory_usage(deep=True), as plain ints."""
    df = pd.DataFrame({"region": ["north", "south"] * 500})
    str_bytes = int(df["region"].memory_usage(deep=True))
    cat_bytes = int(df["region"].astype(_FILL_THIS_IN).memory_usage(deep=True))  # "category"
    return str_bytes, cat_bytes

#!/usr/bin/env bash
# Tests for the Day 121 lab. Run from the lab directory:
#   bash tests/run_tests.sh
#
# The harness proves the lesson's claims by running code and reading real
# values, never by reading source:
#
#   * a country-code CSV containing "NA" for Namibia is read, by default,
#     as a MISSING value -- keep_default_na=False keeps it as the string
#     "NA" instead;
#   * an id column "00123" is inferred as the integer 123 by default, and
#     dtype={"id": "str"} preserves the leading zeros exactly;
#   * an integer above 2**53 survives read_csv()'s int64 inference exactly,
#     but a float64 round-trip silently corrupts its last digit;
#   * a date column left unparsed sorts lexicographically -- and gets the
#     chronological order WRONG the moment one row drops a leading zero --
#     while parse_dates=[...] sorts correctly;
#   * reading a latin-1 file with encoding="utf-8" raises a real
#     UnicodeDecodeError, and only the correct encoding round-trips exactly;
#   * an aggregate computed chunk-by-chunk (chunksize=1000, then again with
#     an odd chunksize=777) equals the whole-file aggregate exactly;
#   * a CSV round-trip changes at least one dtype (a nullable Int64 column
#     becomes float64), while a Parquet round-trip preserves every dtype,
#     and every value, exactly;
#   * the eight-command inspection battery (.head, .info, .dtypes,
#     .describe, .isna().sum(), .nunique(), .value_counts(),
#     memory_usage(deep=True)) reports exact, independently-known values on
#     a frame built for this test;
#   * converting a low-cardinality string column to category reduces
#     memory_usage(deep=True) by at least 5x, asserted as a ratio;
#   * JSON, SQL (sqlite3) and the stdlib csv module are also demonstrated
#     end to end;
#   * nothing is left behind on disk -- no stray .csv, .parquet or .db file,
#     no __pycache__.
#
# Everything after the one-time install runs offline. Nothing binds a port,
# nothing writes outside a temporary directory each script creates and
# removes itself. Deterministic, non-interactive, exits 0 only if every
# check passes.
set -u

export PYTHONDONTWRITEBYTECODE=1

lab_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"

# Bytecode left by an EARLIER command is not this run's litter. `.venv` is
# untouched, because the packages' own bytecode is theirs, not ours.
find "${lab_dir}" -name '.venv' -prune -o -type d -name '__pycache__' -exec rm -rf {} + 2>/dev/null || true
find "${lab_dir}" -name '.venv' -prune -o -type d -name '.pytest_cache' -exec rm -rf {} + 2>/dev/null || true

failures=0
checks=0

check() {
  local label="$1" ok="$2"
  checks=$((checks + 1))
  if [ "${ok}" = "yes" ]; then
    echo "  ok: ${label}"
  else
    echo "  FAIL: ${label}"
    failures=$((failures + 1))
  fi
}

check_eq() {
  # check_eq <label> <expected> <actual>
  if [ "$2" = "$3" ]; then
    check "$1" "yes"
  else
    check "$1 (expected [$2], got [$3])" "no"
  fi
}

# Resolve python: an explicit override, then this lab's .venv, then PATH.
# Fails loudly with instructions rather than silently skipping checks.
resolve_tool() {
  local tool="$1" override="$2"
  if [ -n "${override}" ] && [ -x "${override}" ]; then echo "${override}"; return 0; fi
  if [ -x "${lab_dir}/.venv/bin/${tool}" ]; then echo "${lab_dir}/.venv/bin/${tool}"; return 0; fi
  if command -v "${tool}" >/dev/null 2>&1; then command -v "${tool}"; return 0; fi
  return 1
}

python_bin="$(resolve_tool python3 "${PYTHON:-}")" || {
  echo "FAIL: python3 not found." >&2
  echo "  Install the lab's dependencies with:" >&2
  echo "    python3 -m venv .venv" >&2
  echo "    .venv/bin/pip install -r requirements/requirements.txt" >&2
  echo "  Or point this suite at an existing python3:" >&2
  echo "    PYTHON=/path/to/python3 bash tests/run_tests.sh" >&2
  exit 1
}

if ! "${python_bin}" -c "import pandas, pyarrow" >/dev/null 2>&1; then
  echo "FAIL: pandas/pyarrow are not importable from ${python_bin}." >&2
  echo "  Install the lab's dependencies with:" >&2
  echo "    python3 -m venv .venv" >&2
  echo "    .venv/bin/pip install -r requirements/requirements.txt" >&2
  exit 1
fi

echo "Day 121 — Read It Right"
echo

# --------------------------------------------------------------------------
echo "1. The tools and the versions this lab was written against"
# --------------------------------------------------------------------------

versions="$("${python_bin}" - <<'PY'
import platform
import sys
from importlib.metadata import version

print(f"python   {platform.python_version()}")
for name in ("pandas", "pyarrow", "numpy"):
    print(f"{name:<8} {version(name)}")
print(f"platform {platform.platform()}")
print(f"exe      {sys.executable.rsplit('/', 3)[-1]}")
PY
)"
echo "${versions}" | sed 's/^/  /'

pinned_pandas="$(grep -E '^pandas==' "${lab_dir}/requirements/requirements.txt" | cut -d= -f3)"
installed_pandas="$("${python_bin}" -c "from importlib.metadata import version; print(version('pandas'))")"
check_eq "installed pandas matches requirements.txt" "${pinned_pandas}" "${installed_pandas}"

pandas_major="$("${python_bin}" -c "import pandas; print(pandas.__version__.split('.')[0])")"
check_eq "pandas is version 3 or later (this lab's captured output is 3.0.5-specific)" "3" "${pandas_major}"

# --------------------------------------------------------------------------
echo
echo "2. Every reference script runs and every assertion inside it holds"
# --------------------------------------------------------------------------

for script in 01_the_namibia_trap 02_leading_zeros 03_precision_loss 04_dates \
              05_encoding 06_chunking 07_csv_vs_parquet 08_inspection_battery \
              09_category_memory 10_other_formats; do
  out="$(cd "${lab_dir}/examples" && "${python_bin}" "${script}.py" 2>&1)"
  status=$?
  if [ "${status}" -ne 0 ]; then
    check "${script}.py exits 0" "no"
    echo "${out}" | tail -8 | sed 's/^/      /'
  else
    check "${script}.py exits 0" "yes"
  fi
  case "${out}" in
    *"${script}.py: every assertion held."*)
      check "${script}.py reports every assertion held" "yes" ;;
    *) check "${script}.py reports every assertion held" "no" ;;
  esac
done

# --------------------------------------------------------------------------
echo
echo "3. The starter checker: honest progress, both directions"
# --------------------------------------------------------------------------

starter_out="$(cd "${lab_dir}" && "${python_bin}" starter/check_progress.py 2>&1)"
starter_status=$?
echo "${starter_out}" | tail -3 | sed 's/^/  /'
case "${starter_out}" in
  *"0 of 9 exercises complete."*)
    check "an untouched starter checkout reports 0 of 9 complete" "yes" ;;
  *)
    check "an untouched starter checkout reports 0 of 9 complete" "no" ;;
esac
if [ "${starter_status}" -ne 0 ]; then
  check "the starter checker exits non-zero when incomplete" "yes"
else
  check "the starter checker exits non-zero when incomplete" "no"
fi

# Prove the checker can also report success: solve every exercise in a
# scratch copy, confirm 9 of 9 and exit 0, then discard the copy. The real
# starter/exercises.py on disk is never modified by this.
solved_dir="$(mktemp -d)"
trap 'rm -rf "${solved_dir}"' EXIT
cp "${lab_dir}/starter/exercises.py" "${solved_dir}/exercises.py"
cp "${lab_dir}/starter/check_progress.py" "${solved_dir}/check_progress.py"
"${python_bin}" - "${solved_dir}/exercises.py" <<'PY'
import sys
path = sys.argv[1]
s = open(path).read()
replacements = [
    ('kept_df = _FILL_THIS_IN  # pd.read_csv(path, keep_default_na=False)',
     'kept_df = pd.read_csv(path, keep_default_na=False)'),
    ('typed_df = pd.read_csv(path, dtype=_FILL_THIS_IN)  # {\'id\': \'str\'}',
     'typed_df = pd.read_csv(path, dtype={\'id\': \'str\'})'),
    ('promoted = df.astype({"order_id": _FILL_THIS_IN})  # \'float64\'',
     'promoted = df.astype({"order_id": \'float64\'})'),
    ('parsed = pd.read_csv(path, parse_dates=_FILL_THIS_IN)  # [\'date\']',
     'parsed = pd.read_csv(path, parse_dates=[\'date\'])'),
    ('pd.read_csv(path, encoding=_FILL_THIS_IN)  # \'utf-8\'',
     'pd.read_csv(path, encoding=\'utf-8\')'),
    ('for chunk in pd.read_csv(path, chunksize=_FILL_THIS_IN):  # 3',
     'for chunk in pd.read_csv(path, chunksize=3):'),
    ('parquet_dtype = str(_FILL_THIS_IN["order_id"].dtype)  # pd.read_parquet(pq_path)',
     'parquet_dtype = str(pd.read_parquet(pq_path)["order_id"].dtype)'),
    ('top_region = _FILL_THIS_IN  # df["region"].value_counts().index[0]',
     'top_region = df["region"].value_counts().index[0]'),
    ('cat_bytes = int(df["region"].astype(_FILL_THIS_IN).memory_usage(deep=True))  # "category"',
     'cat_bytes = int(df["region"].astype("category").memory_usage(deep=True))'),
]
for old, new in replacements:
    if old not in s:
        raise SystemExit(f"pattern not found, starter/exercises.py has drifted: {old!r}")
    s = s.replace(old, new)
open(path, "w").write(s)
PY
solved_out="$(cd "${solved_dir}" && "${python_bin}" check_progress.py 2>&1)"
solved_status=$?
echo "${solved_out}" | tail -3 | sed 's/^/  /'
case "${solved_out}" in
  *"9 of 9 exercises complete."*)
    check "a fully solved copy reports 9 of 9 complete" "yes" ;;
  *)
    check "a fully solved copy reports 9 of 9 complete" "no" ;;
esac
check_eq "the checker exits 0 once every exercise is correct" "0" "${solved_status}"
rm -rf "${solved_dir}"
trap - EXIT

# --------------------------------------------------------------------------
echo
echo "4. The lesson's sharpest claims, checked one value at a time"
# --------------------------------------------------------------------------

facts="$(cd "${lab_dir}/examples" && "${python_bin}" - <<'PY'
import tempfile
from pathlib import Path

import pandas as pd

tmpdir = Path(tempfile.mkdtemp())

# Namibia trap.
p1 = tmpdir / "c.csv"
p1.write_text("code,country\nNA,Namibia\n")
default_na = pd.isna(pd.read_csv(p1).loc[0, "code"])
kept_na = pd.read_csv(p1, keep_default_na=False).loc[0, "code"]
p1.unlink()
print("default_is_missing", default_na)
print("kept_value", kept_na)

# Leading zeros.
p2 = tmpdir / "id.csv"
p2.write_text("id\n00123\n")
default_id = int(pd.read_csv(p2).loc[0, "id"])
typed_id = pd.read_csv(p2, dtype={"id": "str"}).loc[0, "id"]
p2.unlink()
print("default_id", default_id)
print("typed_id", typed_id)

# Precision.
big_id = 2**53 + 1
p3 = tmpdir / "big.csv"
p3.write_text(f"order_id\n{big_id}\n")
df3 = pd.read_csv(p3)
exact = int(df3.loc[0, "order_id"])
corrupted = int(df3.astype({"order_id": "float64"}).loc[0, "order_id"])
p3.unlink()
print("precision_exact", exact == big_id)
print("precision_corrupted_by_one", big_id - corrupted)

# CSV vs Parquet.
df4 = pd.DataFrame({"order_id": pd.array([1001, 1002, pd.NA], dtype="Int64")})
csv_path = tmpdir / "o.csv"
pq_path = tmpdir / "o.parquet"
df4.to_csv(csv_path, index=False)
df4.to_parquet(pq_path)
csv_dtype = str(pd.read_csv(csv_path)["order_id"].dtype)
parquet_dtype = str(pd.read_parquet(pq_path)["order_id"].dtype)
csv_path.unlink()
pq_path.unlink()
print("csv_dtype", csv_dtype)
print("parquet_dtype", parquet_dtype)

# Chunking.
p5 = tmpdir / "chunks.csv"
p5.write_text("value\n" + "\n".join(str(i) for i in range(1, 1001)) + "\n")
whole = int(pd.read_csv(p5)["value"].sum())
chunked = sum(int(c["value"].sum()) for c in pd.read_csv(p5, chunksize=97))
p5.unlink()
print("chunking_matches", whole == chunked)

tmpdir.rmdir()
PY
)"
echo "${facts}" | sed 's/^/  /'

get_fact() { printf '%s\n' "${facts}" | grep "^$1 " | cut -d' ' -f2-; }

check_eq "the default read turns Namibia's NA into a real missing value" "True" "$(get_fact default_is_missing)"
check_eq "keep_default_na=False keeps the literal string 'NA'" "NA" "$(get_fact kept_value)"
check_eq "the default read silently drops the leading zeros: '00123' becomes 123" "123" "$(get_fact default_id)"
check_eq "dtype={'id': 'str'} preserves the leading zeros exactly" "00123" "$(get_fact typed_id)"
check_eq "int64 preserves an ID past 2**53 exactly" "True" "$(get_fact precision_exact)"
check_eq "a float64 round-trip corrupts that ID by exactly 1" "1" "$(get_fact precision_corrupted_by_one)"
check_eq "a nullable Int64 column survives a CSV round-trip as float64, not Int64" "float64" "$(get_fact csv_dtype)"
check_eq "the same column survives a Parquet round-trip as Int64, unchanged" "Int64" "$(get_fact parquet_dtype)"
check_eq "an aggregate computed chunk-by-chunk (chunksize=97) equals the whole-file aggregate" "True" "$(get_fact chunking_matches)"

# --------------------------------------------------------------------------
echo
echo "5. Prove the harness can fail, then restore it"
# --------------------------------------------------------------------------

broken_script="${lab_dir}/examples/03_precision_loss.py"
cp "${broken_script}" "${broken_script}.bak"
sed -i.tmp 's/read_value == BIG_ID/read_value != BIG_ID/' "${broken_script}"
rm -f "${broken_script}.tmp"
broken_out="$(cd "${lab_dir}/examples" && "${python_bin}" 03_precision_loss.py 2>&1)"
broken_status=$?
mv "${broken_script}.bak" "${broken_script}"
if [ "${broken_status}" -ne 0 ]; then
  check "a deliberately wrong assertion makes 03_precision_loss.py exit non-zero" "yes"
else
  check "a deliberately wrong assertion makes 03_precision_loss.py exit non-zero" "no"
fi
case "${broken_out}" in
  *FAIL:*) check "the broken run reports a FAIL line" "yes" ;;
  *) check "the broken run reports a FAIL line" "no" ;;
esac
restored_out="$(cd "${lab_dir}/examples" && "${python_bin}" 03_precision_loss.py 2>&1)"
restored_status=$?
check_eq "the script is restored and exits 0 again" "0" "${restored_status}"
case "${restored_out}" in
  *"03_precision_loss.py: every assertion held."*)
    check "the restored script reports every assertion held" "yes" ;;
  *) check "the restored script reports every assertion held" "no" ;;
esac

# --------------------------------------------------------------------------
echo
echo "6. Nothing left behind, and no network dependency baked into the lab"
# --------------------------------------------------------------------------

if grep -rInE 'https?://' "${lab_dir}/examples" "${lab_dir}/starter" >/dev/null 2>&1; then
  check "no URL appears in examples/ or starter/" "no"
else
  check "no URL appears in examples/ or starter/" "yes"
fi

find "${lab_dir}" -name '.venv' -prune -o -type d -name '__pycache__' -exec rm -rf {} + 2>/dev/null || true
find "${lab_dir}" -name '.venv' -prune -o -type d -name '.pytest_cache' -exec rm -rf {} + 2>/dev/null || true

stray="$(find "${lab_dir}" -name '.venv' -prune -o -type d \( -name '__pycache__' -o -name '.pytest_cache' \) -print 2>/dev/null)"
if [ -z "${stray}" ]; then
  check "no __pycache__ or .pytest_cache directories were left behind" "yes"
else
  check "no __pycache__ or .pytest_cache directories were left behind" "no"
  echo "${stray}" | sed 's/^/      /'
fi

stray_data="$(find "${lab_dir}" -name '.venv' -prune -o -type f \( -name '*.csv' -o -name '*.parquet' -o -name '*.db' \) -print 2>/dev/null)"
if [ -z "${stray_data}" ]; then
  check "no .csv, .parquet or .db file was left behind anywhere in the lab" "yes"
else
  check "no .csv, .parquet or .db file was left behind anywhere in the lab" "no"
  echo "${stray_data}" | sed 's/^/      /'
fi

echo
echo "${checks} checks, ${failures} failure(s)."
if [ "${failures}" -ne 0 ]; then
  exit 1
fi
exit 0