Hands-on lab — Day 124: Merging and Reshaping

"""Shared fixtures. pytest finds this file by itself -- nothing imports it.

Each fixture returns a FRESH copy of its table, so one test's mutation
(there should not be any, but fixtures should not have to trust that) can
never leak into the next test.
"""

import pytest

from data import (
    build_dup_index_col,
    build_int_keyed,
    build_left_dup,
    build_left_keys,
    build_price_left,
    build_price_right,
    build_right_dup,
    build_right_keys,
    build_str_keyed,
    build_wide,
)


@pytest.fixture
def left_dup():
    return build_left_dup()


@pytest.fixture
def right_dup():
    return build_right_dup()


@pytest.fixture
def left_keys():
    return build_left_keys()


@pytest.fixture
def right_keys():
    return build_right_keys()


@pytest.fixture
def int_keyed():
    return build_int_keyed()


@pytest.fixture
def str_keyed():
    return build_str_keyed()


@pytest.fixture
def price_left():
    return build_price_left()


@pytest.fixture
def price_right():
    return build_price_right()


@pytest.fixture
def wide():
    return build_wide()


@pytest.fixture
def dup_index_col():
    return build_dup_index_col()

"""The tables every exercise in this lab is built from.

Nothing here is randomised or loaded from a file -- every table is a small
literal so a reader can check every asserted number by eye against the
source.

`left_dup` / `right_dup` -- exercises 1 and 2. Each has a duplicated key,
so a merge between them is many-to-many by construction.

`left_keys` / `right_keys` -- exercises 3 and 5. Each key is unique on
both sides, so the four join types differ only in which rows survive, not
in how many copies of a row a duplicated key would produce.

`int_keyed` / `str_keyed` -- exercise 4. Same digits, different dtypes.

`price_left` / `price_right` -- exercise 6. Both have a `price` column,
so a plain merge collides on the name.

`wide` -- exercise 8's melt/pivot round trip.

`dup_index_col` -- exercise 9's pivot-versus-pivot_table contrast.
"""

from __future__ import annotations

import pandas as pd

# --------------------------------------------------------------------------
# `left_dup` / `right_dup` -- exercises 1 and 2. Duplicated keys on both
# sides, so an inner merge is a per-key Cartesian product.
#
# Key 'A': 3 rows on the left, 2 on the right -> 3*2 = 6 matched rows.
# Key 'B': 2 rows on the left, 4 on the right -> 2*4 = 8 matched rows.
# Key 'C': 1 row on the left, 0 on the right  -> 0 matched rows.
# Key 'D': 0 rows on the left, 1 on the right -> 0 matched rows.
# Total inner rows: 6 + 8 = 14.
# --------------------------------------------------------------------------


def build_left_dup() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "cust_id": ["A", "A", "A", "B", "B", "C"],
            "order_id": [1, 2, 3, 4, 5, 6],
            "amount": [10.0, 20.0, 30.0, 40.0, 50.0, 60.0],
        }
    )


def build_right_dup() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "cust_id": ["A", "A", "B", "B", "B", "B", "D"],
            "contact_id": [101, 102, 103, 104, 105, 106, 107],
            "channel": ["email", "phone", "email", "phone", "sms", "email", "phone"],
        }
    )


# --------------------------------------------------------------------------
# `left_keys` / `right_keys` -- exercises 3 and 5. Every key is unique on
# both sides: left has A, B, C, D; right has B, C, D, E. Overlap is
# exactly {B, C, D}.
# --------------------------------------------------------------------------


def build_left_keys() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "cust_id": ["A", "B", "C", "D"],
            "region": ["North", "South", "East", "West"],
        }
    )


def build_right_keys() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "cust_id": ["B", "C", "D", "E"],
            "plan": ["basic", "pro", "pro", "basic"],
        }
    )


# --------------------------------------------------------------------------
# `int_keyed` / `str_keyed` -- exercise 4. Same three ids, one column
# int64, the other the same digits stored as a pandas Categorical -- the
# read-one-way, read-another-way trap from Day 121 arriving as a join.
#
# A plain str/object key against an int64 key is caught by pandas 3.0.5's
# own dtype check and RAISES a ValueError -- verified separately below and
# documented as an honest correction to the classic "silent zero rows"
# story. A CATEGORICAL key of the same digits (exactly what you get from
# reading a column with dtype="category", a common CSV-loading choice)
# slips past that check and reproduces the classic silent failure: an
# inner join that matches nothing and raises nothing.
# --------------------------------------------------------------------------


def build_int_keyed() -> pd.DataFrame:
    return pd.DataFrame({"id": pd.array([1001, 1002, 1003], dtype="int64"), "name": ["Ann", "Bo", "Cy"]})


def build_str_keyed() -> pd.DataFrame:
    return pd.DataFrame({"id": pd.Categorical(["1001", "1002", "1003"]), "score": [88, 91, 77]})


# --------------------------------------------------------------------------
# `price_left` / `price_right` -- exercise 6. Both carry a `price` column,
# which collides under a plain merge and needs `suffixes=`.
# --------------------------------------------------------------------------


def build_price_left() -> pd.DataFrame:
    return pd.DataFrame({"sku": ["X1", "X2", "X3"], "price": [9.99, 14.50, 3.25]})


def build_price_right() -> pd.DataFrame:
    return pd.DataFrame({"sku": ["X1", "X2", "X3"], "price": [10.99, 13.00, 3.75]})


# --------------------------------------------------------------------------
# `wide` -- exercise 8. Three measurement columns beside an id column, the
# shape aggregation and plotting want turned into long form and back.
# --------------------------------------------------------------------------


def build_wide() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "student_id": [1, 2, 3],
            "math": [88, 72, 95],
            "reading": [91, 85, 79],
            "science": [76, 90, 83],
        }
    )


# --------------------------------------------------------------------------
# `dup_index_col` -- exercise 9. Two rows share the same (student, subject)
# pair with different scores, so a plain `pivot` cannot place them both --
# `pivot_table` averages them instead.
# --------------------------------------------------------------------------


def build_dup_index_col() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "student": ["Ann", "Ann", "Bo", "Ann"],
            "subject": ["math", "reading", "math", "math"],
            "score": [80.0, 91.0, 70.0, 90.0],
        }
    )

"""The worked reference suite for Day 124 -- "Joins That Keep Their Shape".

Nine exercises, each proving one real pandas 3.0.5 behaviour by running
code and reading real values -- never by reading source. Run it:

    pytest examples

Every table these tests use comes from `data.py`, imported through the
fixtures in `conftest.py`. Read `starter/00_brief.md` for the exercise-by-
exercise explanation; this file is the answer key.
"""

import numpy as np
import pandas as pd
import pytest

# --------------------------------------------------------------------------
# Exercise 1 -- the explosion. A many-to-many merge on duplicated keys
# produces exactly the product of the per-key group sizes, on each side,
# summed across keys. Not an error -- a Cartesian product within each key
# group, exactly as merge is defined to do.
# --------------------------------------------------------------------------


def test_1_many_to_many_merge_produces_the_per_key_product(left_dup, right_dup):
    assert left_dup.shape == (6, 3)
    assert right_dup.shape == (7, 3)

    merged = left_dup.merge(right_dup, on="cust_id", how="inner")

    # Compute the expected row count directly from the per-key group
    # sizes on each side -- the definition of what an inner merge does
    # with a duplicated key, not a number copied from a prior run.
    left_counts = left_dup["cust_id"].value_counts()
    right_counts = right_dup["cust_id"].value_counts()
    common_keys = set(left_counts.index) & set(right_counts.index)
    expected_rows = sum(left_counts[k] * right_counts[k] for k in common_keys)

    assert expected_rows == 14  # 3*2 (key A) + 2*4 (key B)
    assert merged.shape[0] == expected_rows
    assert merged.shape == (14, 5)  # cust_id + 2 left cols + 2 right cols

    # Per-key check: key A alone produces 3*2 = 6 rows.
    assert (merged["cust_id"] == "A").sum() == 6
    # Key B alone produces 2*4 = 8 rows.
    assert (merged["cust_id"] == "B").sum() == 8


# --------------------------------------------------------------------------
# Exercise 2 -- validate=. Stating a cardinality assumption and having
# pandas enforce it is the single best habit in this lesson.
# --------------------------------------------------------------------------


def test_2_validate_one_to_one_raises_on_duplicated_keys(left_dup, right_dup):
    with pytest.raises(pd.errors.MergeError):
        left_dup.merge(right_dup, on="cust_id", how="inner", validate="one_to_one")


def test_2_validate_one_to_one_passes_on_genuinely_unique_keys(left_keys, right_keys):
    # left_keys and right_keys each have a unique cust_id -- validate
    # should raise nothing at all, and the merge proceeds normally.
    result = left_keys.merge(right_keys, on="cust_id", how="inner", validate="one_to_one")
    assert result.shape == (3, 3)  # B, C, D


def test_2_validate_one_to_many_raises_when_the_many_side_is_actually_duplicated_on_both(left_dup, right_dup):
    # left_dup is NOT one-to-many against right_dup either: key A repeats
    # on BOTH sides (3 on the left, 2 on the right), so "one" (left) to
    # "many" (right) is violated on the left side too.
    with pytest.raises(pd.errors.MergeError):
        left_dup.merge(right_dup, on="cust_id", how="inner", validate="one_to_many")


# --------------------------------------------------------------------------
# Exercise 3 -- indicator=True. Adds a _merge column recording match
# provenance; the three counts must reconcile exactly with the inputs.
# --------------------------------------------------------------------------


def test_3_indicator_counts_reconcile_with_the_inputs(left_keys, right_keys):
    result = left_keys.merge(right_keys, on="cust_id", how="outer", indicator=True)

    counts = result["_merge"].value_counts()
    assert counts["left_only"] == 1  # A
    assert counts["right_only"] == 1  # E
    assert counts["both"] == 3  # B, C, D

    # Reconciliation: left_only + both accounts for every left row, since
    # every key on both sides here is unique (no duplication to explode).
    assert counts["left_only"] + counts["both"] == left_keys.shape[0] == 4
    assert counts["right_only"] + counts["both"] == right_keys.shape[0] == 4

    # And the three categories account for every output row exactly.
    assert counts["left_only"] + counts["right_only"] + counts["both"] == result.shape[0] == 5


# --------------------------------------------------------------------------
# Exercise 4 -- the silent dtype-mismatch join. An int64 key merged
# against a str key of the same digits matches nothing, and an inner join
# returns zero rows rather than raising.
# --------------------------------------------------------------------------


def test_4_dtype_mismatch_join_returns_zero_rows(int_keyed, str_keyed):
    assert int_keyed["id"].dtype == np.int64
    assert str(str_keyed["id"].dtype) == "category"

    result = int_keyed.merge(str_keyed, on="id", how="inner")

    assert result.shape[0] == 0  # no exception, no warning -- just nothing
    assert list(result.columns) == ["id", "name", "score"]


def test_4_casting_one_side_fixes_it(int_keyed, str_keyed):
    fixed = str_keyed.astype({"id": "int64"})
    result = int_keyed.merge(fixed, on="id", how="inner")

    assert result.shape[0] == 3
    assert result.loc[result["id"] == 1002, "score"].iloc[0] == 91


def test_4_plain_str_key_against_int64_raises_instead_of_matching_nothing(int_keyed):
    # An honest correction to the "always silent" story: pandas 3.0.5
    # checks a merge key's dtype compatibility BEFORE joining, and a
    # plain str (or legacy object) key against an int64 key raises a
    # clear ValueError rather than silently returning zero rows. The
    # categorical case above is the one that still slips through.
    plain_str_keyed = pd.DataFrame({"id": ["1001", "1002", "1003"], "score": [88, 91, 77]})
    assert str(plain_str_keyed["id"].dtype) == "str"

    with pytest.raises(ValueError, match="You are trying to merge on"):
        int_keyed.merge(plain_str_keyed, on="id", how="inner")


# --------------------------------------------------------------------------
# Exercise 5 -- the four join types on one pair of frames, so the
# differences are visible at a glance. left_keys: A, B, C, D. right_keys:
# B, C, D, E. Overlap: B, C, D.
# --------------------------------------------------------------------------


def test_5_inner_keeps_only_the_overlap(left_keys, right_keys):
    result = left_keys.merge(right_keys, on="cust_id", how="inner")
    assert result.shape[0] == 3
    assert set(result["cust_id"]) == {"B", "C", "D"}


def test_5_left_keeps_every_left_row(left_keys, right_keys):
    result = left_keys.merge(right_keys, on="cust_id", how="left")
    assert result.shape[0] == 4
    assert set(result["cust_id"]) == {"A", "B", "C", "D"}
    assert result.loc[result["cust_id"] == "A", "plan"].isna().all()


def test_5_right_keeps_every_right_row(left_keys, right_keys):
    result = left_keys.merge(right_keys, on="cust_id", how="right")
    assert result.shape[0] == 4
    assert set(result["cust_id"]) == {"B", "C", "D", "E"}
    assert result.loc[result["cust_id"] == "E", "region"].isna().all()


def test_5_outer_keeps_every_row_from_both(left_keys, right_keys):
    result = left_keys.merge(right_keys, on="cust_id", how="outer")
    assert result.shape[0] == 5
    assert set(result["cust_id"]) == {"A", "B", "C", "D", "E"}


def test_5_row_counts_in_one_table(left_keys, right_keys):
    counts = {
        how: left_keys.merge(right_keys, on="cust_id", how=how).shape[0]
        for how in ("inner", "left", "right", "outer")
    }
    assert counts == {"inner": 3, "left": 4, "right": 4, "outer": 5}


# --------------------------------------------------------------------------
# Exercise 6 -- suffixes. The default _x/_y on overlapping non-key
# columns is how price_x ends up in production; explicit suffixes fix it.
# --------------------------------------------------------------------------


def test_6_default_suffixes_are_x_and_y(price_left, price_right):
    result = price_left.merge(price_right, on="sku", how="inner")
    assert "price_x" in result.columns
    assert "price_y" in result.columns
    assert "price" not in result.columns
    assert result.loc[result["sku"] == "X1", "price_x"].iloc[0] == 9.99
    assert result.loc[result["sku"] == "X1", "price_y"].iloc[0] == 10.99


def test_6_explicit_suffixes_rename_as_asked(price_left, price_right):
    result = price_left.merge(price_right, on="sku", how="inner", suffixes=("_catalog", "_live"))
    assert "price_catalog" in result.columns
    assert "price_live" in result.columns
    assert "price_x" not in result.columns
    assert result.loc[result["sku"] == "X2", "price_catalog"].iloc[0] == 14.50
    assert result.loc[result["sku"] == "X2", "price_live"].iloc[0] == 13.00


def test_6_on_versus_left_on_right_on_give_the_same_result(price_left, price_right):
    renamed_right = price_right.rename(columns={"sku": "sku_code"})
    via_on = price_left.merge(price_right, on="sku", how="inner", suffixes=("_l", "_r"))
    via_left_right_on = price_left.merge(
        renamed_right, left_on="sku", right_on="sku_code", how="inner", suffixes=("_l", "_r")
    )
    assert via_on.shape[0] == via_left_right_on.shape[0] == 3
    assert list(via_on["price_l"]) == list(via_left_right_on["price_l"])


def test_6_join_on_index_matches_merge_on_column(price_left, price_right):
    left_indexed = price_left.set_index("sku")
    right_indexed = price_right.set_index("sku")

    via_join = left_indexed.join(right_indexed, how="inner", lsuffix="_l", rsuffix="_r")
    via_merge = price_left.merge(price_right, on="sku", how="inner", suffixes=("_l", "_r")).set_index("sku")

    assert via_join.sort_index().equals(via_merge.sort_index())


# --------------------------------------------------------------------------
# Exercise 7 -- concat. axis=0 stacks rows; axis=1 stacks columns.
# Alignment fills unmatched columns or labels with NaN rather than
# erroring.
# --------------------------------------------------------------------------


def test_7_concat_axis_0_with_mismatched_columns_fills_nan():
    frame_a = pd.DataFrame({"a": [1, 2], "b": [3, 4]})
    frame_b = pd.DataFrame({"b": [5, 6], "c": [7, 8]})

    result = pd.concat([frame_a, frame_b], axis=0, ignore_index=True)

    assert result.shape == (4, 3)
    assert list(result.columns) == ["a", "b", "c"]

    # frame_a's rows (0, 1) have no 'c' -- must be NaN there.
    assert result.loc[0:1, "c"].isna().all()
    # frame_b's rows (2, 3) have no 'a' -- must be NaN there.
    assert result.loc[2:3, "a"].isna().all()
    # 'b' is present in both, so it is never NaN here.
    assert not result["b"].isna().any()
    assert list(result["b"]) == [3, 4, 5, 6]


def test_7_concat_axis_1_with_mismatched_index_fills_nan():
    frame_a = pd.DataFrame({"x": [10, 20]}, index=["r1", "r2"])
    frame_b = pd.DataFrame({"y": [30, 40]}, index=["r2", "r3"])

    result = pd.concat([frame_a, frame_b], axis=1)

    assert result.shape == (3, 2)
    assert result.loc["r1", "y"] != result.loc["r1", "y"]  # NaN != NaN
    assert pd.isna(result.loc["r1", "y"])
    assert pd.isna(result.loc["r3", "x"])
    assert result.loc["r2", "x"] == 20
    assert result.loc["r2", "y"] == 30


# --------------------------------------------------------------------------
# Exercise 8 -- melt to go long, pivot to come back. The round trip must
# recover the original.
# --------------------------------------------------------------------------


def test_8_melt_then_pivot_round_trips_to_the_original(wide):
    long = wide.melt(id_vars="student_id", var_name="subject", value_name="score")

    assert long.shape == (9, 3)  # 3 students x 3 subjects
    assert set(long["subject"]) == {"math", "reading", "science"}

    recovered = (
        long.pivot(index="student_id", columns="subject", values="score")
        .reset_index()
        .rename_axis(columns=None)
    )
    # pivot sorts columns alphabetically -- put the original column order back.
    recovered = recovered[["student_id", "math", "reading", "science"]]

    pd.testing.assert_frame_equal(recovered, wide, check_dtype=True)


# --------------------------------------------------------------------------
# Exercise 9 -- pivot raises on duplicate index/column pairs; pivot_table
# aggregates them instead.
# --------------------------------------------------------------------------


def test_9_pivot_raises_on_duplicate_index_column_pairs(dup_index_col):
    # ("Ann", "math") appears twice -- pivot has nowhere to put both.
    with pytest.raises(ValueError):
        dup_index_col.pivot(index="student", columns="subject", values="score")


def test_9_pivot_table_aggregates_the_duplicates(dup_index_col):
    result = dup_index_col.pivot_table(index="student", columns="subject", values="score", aggfunc="mean")

    # Ann's two math scores, 80.0 and 90.0, average to 85.0.
    assert result.loc["Ann", "math"] == 85.0
    assert result.loc["Ann", "reading"] == 91.0
    assert result.loc["Bo", "math"] == 70.0

lesson_id: D124
day: 124
kind: guided-build
languages: [python, bash]
setup_commands:
  - cd labs/sections/math-statistics-and-data/day-124-merging-and-reshaping
  - python3 -m venv .venv
  - .venv/bin/pip install -r requirements/requirements.txt
  - .venv/bin/python3 -c "import pandas; print(pandas.__version__)"
run_commands:
  - .venv/bin/pytest examples
  - .venv/bin/pytest starter
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, pytest 9.1.1, bash 3.2.57 -- bash tests/run_tests.sh -> 13 checks, 0 failure(s), exit 0. pytest examples -> 22 passed. pytest starter -> 22 skipped (untouched checkout). Section 5 of the harness solves every exercise in a scratch copy (22 passed), deliberately breaks exercise 1''s exact row-count assertion (14 -> 999), confirms the run exits non-zero with a printed FAIL, restores the file, and confirms 22 passed again -- so the suite is demonstrated to be capable of failing rather than merely claimed to be. Separately, the coordinator broke the same assertion directly in examples/test_merge.py (not a scratch copy) and re-ran the full harness: 1 failed, 21 passed from pytest, and 6 of the harness''s own 13 checks failed with overall exit 1; restoring the line returned the harness to 13 checks, 0 failure(s), exit 0. Everything was run through a real lab-local .venv created by the documented setup commands. Three honesty notes from this run. FIRST: pandas 3.0.5 now infers a plain Python-string column as its own `str` extension dtype by default (not the historical `object`), and -- more importantly -- pandas 3.0.5''s merge() explicitly detects an int64-vs-string/object key mismatch and RAISES `ValueError: You are trying to merge on int64 and str columns...` rather than silently returning zero rows; this is a real, verified correction to the classic "silent empty join" story the day brief described, and exercise 4 covers both: a genuinely silent zero-row case (int64 vs a pandas Categorical of the same digits, which is NOT caught by that dtype check) and the now-loud plain-string case, side by side. SECOND: running `pytest examples starter` in one invocation was tested directly in this lab and does NOT silently let one directory''s tests shadow the other -- it aborts collection outright with `import file mismatch` (both directories define a module named test_merge.py) and pytest exits non-zero before running anything, which is an even stronger reason never to do it, stated accurately rather than assuming the softer "silent shadow" failure mode without checking. THIRD: matplotlib, scipy and polars are not installed in this environment; polars'' join and its explicit validate argument are described in the lesson''s Tools section from public documentation only, and no output attributed to polars, scipy or matplotlib is reproduced anywhere in this lab or its lesson.'

# What is installed, why, and what it costs

Four 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 `merge`, `concat`, `melt`, `pivot` and `pivot_table` call in this lab. |
| `pyarrow` | 25.0.1 | Apache 2.0 | pandas 3.0's default backend for several nullable and string dtypes; installed for parity with Days 120-123 even though this lab's tables are plain numeric, object and categorical columns throughout. |
| `numpy` | 2.5.2 | BSD 3-Clause | `np.nan`-related comparisons in the `concat` alignment exercise. |
| `pytest` | 9.1.1 | MIT | The test harness every exercise is written against. |

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

## 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
and test after that runs completely offline.

## What is deliberately *not* installed

**matplotlib**, **scipy** and **polars** are not installed in this
environment. The lesson's Tools section describes polars' `join` and its
explicit `validate` argument from its public documentation as a design
contrast to pandas — no output from polars, scipy or matplotlib is
reproduced anywhere in this lab or its lesson; every place they are
mentioned says so plainly. SQL joins are demonstrated with the standard
library's `sqlite3`, which needs nothing beyond Python itself.

## If you cannot install anything at all

pandas is not in the Python standard library, and there is no reduced
path through this lab without it. If pandas genuinely cannot be
installed, read the lesson's captured output and `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
pytest==9.1.1

# Day 124 lab — the brief

Nine exercises, in order. Work top to bottom in `test_merge.py`. Every
table comes from a fixture defined in `conftest.py` (`left_dup`,
`right_dup`, `left_keys`, `right_keys`, `int_keyed`, `str_keyed`,
`price_left`, `price_right`, `wide`, `dup_index_col`) — read `data.py`
once to see exactly what each one contains before you start.

Check yourself at any point:

```bash
.venv/bin/pytest starter -v
```

On an untouched checkout that prints `22 skipped`. A **skip** means "not
attempted". Replace a `pytest.skip(...)` line with real assertions and
delete it — when every skip is gone and the suite is green, you are
finished:

```bash
.venv/bin/pytest starter -q
echo $?
```

Assert exact values everywhere in this lab. Unlike Day 123, nothing here
is timing-dependent.

---

## Exercise 1 — the explosion (`left_dup`, `right_dup`)

`left_dup` has key `A` three times and key `B` twice. `right_dup` has key
`A` twice and key `B` four times. An **inner merge on `cust_id`** is a
per-key Cartesian product: key `A` alone produces `3 * 2 = 6` rows, key
`B` alone produces `2 * 4 = 8` rows. Compute the expected total from each
side's `.value_counts()` — do not hardcode `14`, derive it — and assert
the merged shape matches. Then assert the per-key row counts (6 for `A`,
8 for `B`) directly.

## Exercise 2 — `validate=` (`left_dup`, `right_dup`, `left_keys`, `right_keys`)

`validate='one_to_one'` on `left_dup`/`right_dup` must raise
`pandas.errors.MergeError` — key `A` and key `B` are each duplicated on
both sides, violating "one-to-one" from either direction. The same
`validate='one_to_one'` on `left_keys`/`right_keys` (every key unique on
both sides) must raise nothing and return 3 rows. A third test:
`validate='one_to_many'` on `left_dup`/`right_dup` must **also** raise —
key `A` repeats on the LEFT side (3 times), which violates "one" even
though you asked for "many" on the right.

## Exercise 3 — `indicator=True` (`left_keys`, `right_keys`)

`left_keys` has keys A, B, C, D. `right_keys` has keys B, C, D, E. Outer
merge with `indicator=True`, then read the `_merge` column's
`.value_counts()`: `left_only` should be 1 (key A), `right_only` should be
1 (key E), `both` should be 3 (B, C, D). Assert the reconciliation
explicitly: `left_only + both` equals `left_keys`' row count (4);
`right_only + both` equals `right_keys`' row count (4); all three sum to
the merged row count (5).

## Exercise 4 — the silent dtype-mismatch join (`int_keyed`, `str_keyed`)

`int_keyed['id']` is `int64`. `str_keyed['id']` is a pandas
**Categorical** of the same digits as strings. Inner-merging them on `id`
returns **0 rows** — no exception, no warning. Confirm both dtypes, run
the merge, assert `result.shape[0] == 0`. Then cast `str_keyed['id']` to
`int64` with `.astype({'id': 'int64'})`, merge again, and assert 3 rows
come back with id `1002`'s score equal to `91`.

**A third test, and it matters:** build a fresh frame with a *plain* `str`
`id` column (not categorical) holding the same digits, and merge it
against `int_keyed`. In pandas 3.0.5 this does **not** silently return
zero rows — it **raises `ValueError`**, telling you plainly that you are
merging incompatible key dtypes. Assert that raise. This is the honest
correction: the categorical case above is the one that still slips
through silently; the plain-string case is now caught for you.

## Exercise 5 — the four join types (`left_keys`, `right_keys`)

Same pair of frames as exercise 3. Write four tests asserting the row
count and surviving keys for `how='inner'` (3 rows, B/C/D),
`how='left'` (4 rows, A's `plan` all `NaN`), `how='right'` (4 rows, E's
`region` all `NaN`), and `how='outer'` (5 rows, A through E). A fifth test
builds a `{how: row_count}` dict for all four in one place and asserts it
equals `{'inner': 3, 'left': 4, 'right': 4, 'outer': 5}`.

## Exercise 6 — suffixes, `on`/`left_on`/`right_on`, and `join()` (`price_left`, `price_right`)

Both frames have a `price` column. A plain `merge(on='sku')` produces
`price_x` and `price_y` — assert both exist, `price` does not, and X1's
values are `9.99`/`10.99`. Repeat with `suffixes=('_catalog', '_live')`
and assert those names instead. Third test: rename `price_right`'s `sku`
to `sku_code`, merge once with `on='sku'` (after renaming back, or just
merge `price_left` against the *unrenamed* `price_right` for the `on=`
case and against the *renamed* copy for `left_on`/`right_on`) and confirm
both give the same row count and values. Fourth test: `set_index('sku')`
on both and use `.join(how='inner', lsuffix='_l', rsuffix='_r')`; assert
it matches the equivalent `.merge(on='sku')` result once both are sorted
by index.

## Exercise 7 — `concat` alignment

Build two small frames by hand with partially overlapping columns.
`pd.concat([frame_a, frame_b], axis=0, ignore_index=True)`: assert the
combined shape, and assert **exactly which cells** are `NaN` — the column
missing from `frame_a` is `NaN` on `frame_a`'s rows, and vice versa; the
shared column is never `NaN`. Then build two frames with partially
overlapping **index labels** and `pd.concat([...], axis=1)`: assert the
combined shape and exactly which cells are `NaN` this time.

## Exercise 8 — melt → pivot round trip (`wide`)

`wide` has `student_id`, `math`, `reading`, `science`. `.melt(id_vars=
'student_id', var_name='subject', value_name='score')` gives 9 rows (3
students × 3 subjects). `.pivot(index='student_id', columns='subject',
values='score')` should bring back the original — but you will need to
`.reset_index()`, drop the columns' index name, and reorder the columns
back to `wide`'s original order before `pd.testing.assert_frame_equal`
passes, since `pivot` sorts columns alphabetically.

## Exercise 9 — `pivot` versus `pivot_table` (`dup_index_col`)

`dup_index_col` has two rows for `('Ann', 'math')` — scores 80 and 90.
`.pivot(index='student', columns='subject', values='score')` cannot place
both, and must raise `ValueError`. `.pivot_table(index='student',
columns='subject', values='score', aggfunc='mean')` aggregates them
instead: assert Ann/math is `85.0` (the mean of 80 and 90), Ann/reading is
`91.0`, and Bo/math is `70.0`.

---

Prove your suite is not vacuous once you are green: re-break one assertion
on purpose (flip a comparison, change an expected number), confirm the run
exits non-zero with a printed `FAIL`, then restore it and confirm green
again.

"""Shared fixtures. pytest finds this file by itself -- nothing imports it.

Each fixture returns a FRESH copy of its table, so one test's mutation
(there should not be any, but fixtures should not have to trust that) can
never leak into the next test.
"""

import pytest

from data import (
    build_dup_index_col,
    build_int_keyed,
    build_left_dup,
    build_left_keys,
    build_price_left,
    build_price_right,
    build_right_dup,
    build_right_keys,
    build_str_keyed,
    build_wide,
)


@pytest.fixture
def left_dup():
    return build_left_dup()


@pytest.fixture
def right_dup():
    return build_right_dup()


@pytest.fixture
def left_keys():
    return build_left_keys()


@pytest.fixture
def right_keys():
    return build_right_keys()


@pytest.fixture
def int_keyed():
    return build_int_keyed()


@pytest.fixture
def str_keyed():
    return build_str_keyed()


@pytest.fixture
def price_left():
    return build_price_left()


@pytest.fixture
def price_right():
    return build_price_right()


@pytest.fixture
def wide():
    return build_wide()


@pytest.fixture
def dup_index_col():
    return build_dup_index_col()

"""The tables every exercise in this lab is built from.

Nothing here is randomised or loaded from a file -- every table is a small
literal so a reader can check every asserted number by eye against the
source.

`left_dup` / `right_dup` -- exercises 1 and 2. Each has a duplicated key,
so a merge between them is many-to-many by construction.

`left_keys` / `right_keys` -- exercises 3 and 5. Each key is unique on
both sides, so the four join types differ only in which rows survive, not
in how many copies of a row a duplicated key would produce.

`int_keyed` / `str_keyed` -- exercise 4. Same digits, different dtypes.

`price_left` / `price_right` -- exercise 6. Both have a `price` column,
so a plain merge collides on the name.

`wide` -- exercise 8's melt/pivot round trip.

`dup_index_col` -- exercise 9's pivot-versus-pivot_table contrast.
"""

from __future__ import annotations

import pandas as pd

# --------------------------------------------------------------------------
# `left_dup` / `right_dup` -- exercises 1 and 2. Duplicated keys on both
# sides, so an inner merge is a per-key Cartesian product.
#
# Key 'A': 3 rows on the left, 2 on the right -> 3*2 = 6 matched rows.
# Key 'B': 2 rows on the left, 4 on the right -> 2*4 = 8 matched rows.
# Key 'C': 1 row on the left, 0 on the right  -> 0 matched rows.
# Key 'D': 0 rows on the left, 1 on the right -> 0 matched rows.
# Total inner rows: 6 + 8 = 14.
# --------------------------------------------------------------------------


def build_left_dup() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "cust_id": ["A", "A", "A", "B", "B", "C"],
            "order_id": [1, 2, 3, 4, 5, 6],
            "amount": [10.0, 20.0, 30.0, 40.0, 50.0, 60.0],
        }
    )


def build_right_dup() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "cust_id": ["A", "A", "B", "B", "B", "B", "D"],
            "contact_id": [101, 102, 103, 104, 105, 106, 107],
            "channel": ["email", "phone", "email", "phone", "sms", "email", "phone"],
        }
    )


# --------------------------------------------------------------------------
# `left_keys` / `right_keys` -- exercises 3 and 5. Every key is unique on
# both sides: left has A, B, C, D; right has B, C, D, E. Overlap is
# exactly {B, C, D}.
# --------------------------------------------------------------------------


def build_left_keys() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "cust_id": ["A", "B", "C", "D"],
            "region": ["North", "South", "East", "West"],
        }
    )


def build_right_keys() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "cust_id": ["B", "C", "D", "E"],
            "plan": ["basic", "pro", "pro", "basic"],
        }
    )


# --------------------------------------------------------------------------
# `int_keyed` / `str_keyed` -- exercise 4. Same three ids, one column
# int64, the other the same digits stored as a pandas Categorical -- the
# read-one-way, read-another-way trap from Day 121 arriving as a join.
#
# A plain str/object key against an int64 key is caught by pandas 3.0.5's
# own dtype check and RAISES a ValueError -- verified separately below and
# documented as an honest correction to the classic "silent zero rows"
# story. A CATEGORICAL key of the same digits (exactly what you get from
# reading a column with dtype="category", a common CSV-loading choice)
# slips past that check and reproduces the classic silent failure: an
# inner join that matches nothing and raises nothing.
# --------------------------------------------------------------------------


def build_int_keyed() -> pd.DataFrame:
    return pd.DataFrame({"id": pd.array([1001, 1002, 1003], dtype="int64"), "name": ["Ann", "Bo", "Cy"]})


def build_str_keyed() -> pd.DataFrame:
    return pd.DataFrame({"id": pd.Categorical(["1001", "1002", "1003"]), "score": [88, 91, 77]})


# --------------------------------------------------------------------------
# `price_left` / `price_right` -- exercise 6. Both carry a `price` column,
# which collides under a plain merge and needs `suffixes=`.
# --------------------------------------------------------------------------


def build_price_left() -> pd.DataFrame:
    return pd.DataFrame({"sku": ["X1", "X2", "X3"], "price": [9.99, 14.50, 3.25]})


def build_price_right() -> pd.DataFrame:
    return pd.DataFrame({"sku": ["X1", "X2", "X3"], "price": [10.99, 13.00, 3.75]})


# --------------------------------------------------------------------------
# `wide` -- exercise 8. Three measurement columns beside an id column, the
# shape aggregation and plotting want turned into long form and back.
# --------------------------------------------------------------------------


def build_wide() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "student_id": [1, 2, 3],
            "math": [88, 72, 95],
            "reading": [91, 85, 79],
            "science": [76, 90, 83],
        }
    )


# --------------------------------------------------------------------------
# `dup_index_col` -- exercise 9. Two rows share the same (student, subject)
# pair with different scores, so a plain `pivot` cannot place them both --
# `pivot_table` averages them instead.
# --------------------------------------------------------------------------


def build_dup_index_col() -> pd.DataFrame:
    return pd.DataFrame(
        {
            "student": ["Ann", "Ann", "Bo", "Ann"],
            "subject": ["math", "reading", "math", "math"],
            "score": [80.0, 91.0, 70.0, 90.0],
        }
    )

"""YOUR test suite for Day 124 -- "Joins That Keep Their Shape". Nine exercises.

Run it from the lab directory, not from here:

    pytest starter -v

Every exercise below ends in a `pytest.skip(...)` line. pytest reports a
skip as `s` in the dot line and moves on, so an unfinished suite still
exits 0. Replace each skip with real assertions -- deleting the skip line
is part of the exercise. `starter/00_brief.md` explains each exercise in
full; the fixtures you need (`left_dup`, `right_dup`, `left_keys`,
`right_keys`, `int_keyed`, `str_keyed`, `price_left`, `price_right`,
`wide`, `dup_index_col`) come from `conftest.py` and are described there
too.

Assert exact values everywhere in this lab -- there is no timing
assertion here the way Day 123 had one.
"""

import numpy as np
import pandas as pd
import pytest

# --------------------------------------------------------------------------
# EXERCISE 1 -- the explosion. See starter/00_brief.md exercise 1.
#
# Check with:   pytest starter -v -k test_1
# --------------------------------------------------------------------------


def test_1_many_to_many_merge_produces_the_per_key_product(left_dup, right_dup):
    pytest.skip(
        "exercise 1: merge left_dup and right_dup inner on cust_id; compute the "
        "expected row count from each side's per-key value_counts product and "
        "assert the merged shape matches it exactly (14 rows); also assert key "
        "A alone produces 6 rows and key B alone produces 8"
    )


# --------------------------------------------------------------------------
# EXERCISE 2 -- validate=. See starter/00_brief.md exercise 2.
#
# Check with:   pytest starter -v -k test_2
# --------------------------------------------------------------------------


def test_2_validate_one_to_one_raises_on_duplicated_keys(left_dup, right_dup):
    pytest.skip(
        "exercise 2a: assert merging left_dup and right_dup with "
        "validate='one_to_one' raises pandas.errors.MergeError"
    )


def test_2_validate_one_to_one_passes_on_genuinely_unique_keys(left_keys, right_keys):
    pytest.skip(
        "exercise 2b: assert merging left_keys and right_keys with "
        "validate='one_to_one' raises nothing and returns 3 rows"
    )


def test_2_validate_one_to_many_raises_when_the_many_side_is_actually_duplicated_on_both(left_dup, right_dup):
    pytest.skip(
        "exercise 2c: assert validate='one_to_many' on left_dup/right_dup also "
        "raises MergeError, because key A repeats on the LEFT side too"
    )


# --------------------------------------------------------------------------
# EXERCISE 3 -- indicator=True. See starter/00_brief.md exercise 3.
#
# Check with:   pytest starter -v -k test_3
# --------------------------------------------------------------------------


def test_3_indicator_counts_reconcile_with_the_inputs(left_keys, right_keys):
    pytest.skip(
        "exercise 3: outer merge left_keys/right_keys with indicator=True; assert "
        "left_only=1, right_only=1, both=3, and that left_only+both == 4 "
        "(left_keys row count), right_only+both == 4 (right_keys row count), and "
        "all three sum to the merged row count (5)"
    )


# --------------------------------------------------------------------------
# EXERCISE 4 -- the silent dtype-mismatch join. See starter/00_brief.md
# exercise 4.
#
# Check with:   pytest starter -v -k test_4
# --------------------------------------------------------------------------


def test_4_dtype_mismatch_join_returns_zero_rows(int_keyed, str_keyed):
    pytest.skip(
        "exercise 4a: confirm int_keyed['id'] is int64 and str_keyed['id'] is "
        "category; inner-merge them on 'id' and assert the result has 0 rows "
        "and the correct columns, with no exception raised"
    )


def test_4_casting_one_side_fixes_it(int_keyed, str_keyed):
    pytest.skip(
        "exercise 4b: cast str_keyed['id'] to int64 with .astype, merge again, "
        "and assert 3 rows with id 1002's score equal to 91"
    )


def test_4_plain_str_key_against_int64_raises_instead_of_matching_nothing(int_keyed):
    pytest.skip(
        "exercise 4c: build a fresh DataFrame with a plain str 'id' column of "
        "the same digits ('1001','1002','1003') and a 'score' column, and "
        "assert merging it against int_keyed on 'id' raises ValueError -- this "
        "is a real correction to the classic silent-zero-rows story"
    )


# --------------------------------------------------------------------------
# EXERCISE 5 -- the four join types on one pair of frames. See
# starter/00_brief.md exercise 5.
#
# Check with:   pytest starter -v -k test_5
# --------------------------------------------------------------------------


def test_5_inner_keeps_only_the_overlap(left_keys, right_keys):
    pytest.skip("exercise 5a: how='inner' on left_keys/right_keys gives 3 rows, keys B, C, D")


def test_5_left_keeps_every_left_row(left_keys, right_keys):
    pytest.skip(
        "exercise 5b: how='left' gives 4 rows, keys A-D, with A's 'plan' all NaN"
    )


def test_5_right_keeps_every_right_row(left_keys, right_keys):
    pytest.skip(
        "exercise 5c: how='right' gives 4 rows, keys B-E, with E's 'region' all NaN"
    )


def test_5_outer_keeps_every_row_from_both(left_keys, right_keys):
    pytest.skip("exercise 5d: how='outer' gives 5 rows, keys A-E")


def test_5_row_counts_in_one_table(left_keys, right_keys):
    pytest.skip(
        "exercise 5e: build a dict of {how: row_count} for inner/left/right/outer "
        "and assert it equals {'inner': 3, 'left': 4, 'right': 4, 'outer': 5}"
    )


# --------------------------------------------------------------------------
# EXERCISE 6 -- suffixes, on/left_on/right_on, and join(). See
# starter/00_brief.md exercise 6.
#
# Check with:   pytest starter -v -k test_6
# --------------------------------------------------------------------------


def test_6_default_suffixes_are_x_and_y(price_left, price_right):
    pytest.skip(
        "exercise 6a: merge price_left/price_right on 'sku'; assert 'price_x' "
        "and 'price_y' exist and 'price' does not, with X1's values 9.99/10.99"
    )


def test_6_explicit_suffixes_rename_as_asked(price_left, price_right):
    pytest.skip(
        "exercise 6b: repeat with suffixes=('_catalog', '_live'); assert those "
        "column names exist instead and X2's values are 14.50/13.00"
    )


def test_6_on_versus_left_on_right_on_give_the_same_result(price_left, price_right):
    pytest.skip(
        "exercise 6c: rename price_right's 'sku' to 'sku_code', merge once with "
        "on='sku' and once with left_on='sku'/right_on='sku_code', and assert "
        "both give the same row count and the same price_l values"
    )


def test_6_join_on_index_matches_merge_on_column(price_left, price_right):
    pytest.skip(
        "exercise 6d: set_index('sku') on both frames and use .join(how='inner', "
        "lsuffix='_l', rsuffix='_r'); assert it equals the equivalent .merge(on='sku') "
        "result, once both are sorted by index"
    )


# --------------------------------------------------------------------------
# EXERCISE 7 -- concat alignment. See starter/00_brief.md exercise 7.
#
# Check with:   pytest starter -v -k test_7
# --------------------------------------------------------------------------


def test_7_concat_axis_0_with_mismatched_columns_fills_nan():
    pytest.skip(
        "exercise 7a: build frame_a={'a':[1,2],'b':[3,4]} and frame_b={'b':[5,6],'c':[7,8]}; "
        "pd.concat([frame_a, frame_b], axis=0, ignore_index=True); assert shape (4,3), "
        "frame_a's rows have NaN in 'c', frame_b's rows have NaN in 'a', and 'b' is "
        "never NaN"
    )


def test_7_concat_axis_1_with_mismatched_index_fills_nan():
    pytest.skip(
        "exercise 7b: build frame_a indexed ['r1','r2'] and frame_b indexed ['r2','r3']; "
        "pd.concat([frame_a, frame_b], axis=1); assert shape (3,2) and exactly which "
        "cells are NaN (r1's y, r3's x)"
    )


# --------------------------------------------------------------------------
# EXERCISE 8 -- melt then pivot round trip. See starter/00_brief.md
# exercise 8.
#
# Check with:   pytest starter -v -k test_8
# --------------------------------------------------------------------------


def test_8_melt_then_pivot_round_trips_to_the_original(wide):
    pytest.skip(
        "exercise 8: melt wide on id_vars='student_id' to long form (assert shape "
        "(9,3)); pivot it back with index='student_id', columns='subject', "
        "values='score'; reset_index, drop the columns' name, reorder columns to "
        "match wide, and assert pd.testing.assert_frame_equal(recovered, wide)"
    )


# --------------------------------------------------------------------------
# EXERCISE 9 -- pivot versus pivot_table. See starter/00_brief.md
# exercise 9.
#
# Check with:   pytest starter -v -k test_9
# --------------------------------------------------------------------------


def test_9_pivot_raises_on_duplicate_index_column_pairs(dup_index_col):
    pytest.skip(
        "exercise 9a: dup_index_col.pivot(index='student', columns='subject', "
        "values='score') must raise ValueError -- ('Ann','math') appears twice"
    )


def test_9_pivot_table_aggregates_the_duplicates(dup_index_col):
    pytest.skip(
        "exercise 9b: dup_index_col.pivot_table(index='student', columns='subject', "
        "values='score', aggfunc='mean') must return Ann/math = 85.0 (mean of 80 and "
        "90), Ann/reading = 91.0, Bo/math = 70.0"
    )

#!/usr/bin/env bash
# Tests for the Day 124 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 many-to-many merge on duplicated keys produces exactly the product
#     of the per-key group sizes on each side, summed across keys;
#   * validate='one_to_one' raises MergeError the moment that assumption
#     is violated, and passes silently when the assumption genuinely holds;
#   * indicator=True's left_only/right_only/both counts reconcile exactly
#     with the two input row counts;
#   * an int64 key merged against a categorical key of the same digits
#     matches nothing and returns zero rows, silently; casting fixes it;
#     and a plain str key against int64 now RAISES instead, in this
#     pandas version -- an honest correction, checked directly;
#   * inner/left/right/outer row counts on one pair of frames;
#   * default _x/_y suffixes on overlapping columns, and that explicit
#     suffixes rename them as asked; on= vs left_on/right_on agree; join()
#     on the index matches merge() on the column;
#   * concat's axis-0 and axis-1 alignment fills unmatched columns/labels
#     with NaN in exactly the cells that should be NaN, never elsewhere;
#   * melt -> pivot round-trips back to the original frame exactly;
#   * pivot raises on duplicate index/column pairs; pivot_table aggregates
#     them instead, reporting the correct mean;
#   * the reference suite (`examples/`) passes in full;
#   * the exercise suite (`starter/`) is all-skip on an untouched checkout,
#     and the harness proves it can genuinely FAIL by solving every
#     exercise in a scratch copy, breaking one assertion on purpose,
#     confirming a non-zero exit and a printed FAIL, then restoring it;
#   * nothing is left behind on disk.
#
# Everything after the one-time install runs offline. Nothing binds a port,
# nothing writes outside the lab, nothing needs a key. 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. The README
# documents `pytest starter -q` and `pytest examples -q` separately, and
# running either writes .pyc files that would then fail the cleanliness
# check at the end of this script -- failing the reader for following the
# instructions. Clearing them here makes that final check measure what it
# claims to: what THIS run left behind. `.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 pytest: 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
}

pytest_bin="$(resolve_tool pytest "${PYTEST:-}")" || {
  echo "FAIL: pytest 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 pytest:" >&2
  echo "    PYTEST=/path/to/pytest bash tests/run_tests.sh" >&2
  exit 1
}

python_bin="$(dirname "${pytest_bin}")/python3"
if [ ! -x "${python_bin}" ]; then
  python_bin="$(command -v python3 || true)"
fi
if [ -z "${python_bin}" ]; then
  echo "FAIL: python3 not found on PATH." >&2
  exit 1
fi

if ! "${python_bin}" -c "import pandas" >/dev/null 2>&1; then
  echo "FAIL: pandas is 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 124 — Joins That Keep Their Shape"
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", "pytest"):
    try:
        print(f"{name:<8} {version(name)}")
    except Exception as exc:  # pragma: no cover
        print(f"{name:<8} NOT INSTALLED ({exc})")
PY
)"
echo "${versions}"
echo

pandas_version="$("${python_bin}" -c "import pandas; print(pandas.__version__)" 2>/dev/null || echo "")"
pinned_pandas="$(grep -m1 '^pandas==' "${lab_dir}/requirements/requirements.txt" | cut -d= -f3)"
check_eq "installed pandas matches requirements.txt exactly" "${pinned_pandas}" "${pandas_version}"
echo

# --------------------------------------------------------------------------
echo "2. Reference suite -- examples/ must pass in full"
# --------------------------------------------------------------------------

examples_output="$(cd "${lab_dir}" && "${pytest_bin}" examples -q 2>&1)"
examples_status=$?
echo "${examples_output}" | tail -5
check "examples/ exits 0" "$( [ ${examples_status} -eq 0 ] && echo yes || echo no )"

examples_passed_line="$(echo "${examples_output}" | grep -E '^[0-9]+ passed' || true)"
check "examples/ reports 22 passed, 0 failed" "$( echo "${examples_passed_line}" | grep -qE '^22 passed' && echo yes || echo no )"
echo

# --------------------------------------------------------------------------
echo "3. Exercise suite -- starter/ is all-skip on an untouched checkout"
# --------------------------------------------------------------------------

starter_output="$(cd "${lab_dir}" && "${pytest_bin}" starter -q 2>&1)"
starter_status=$?
echo "${starter_output}" | tail -5
check "starter/ (untouched) exits 0" "$( [ ${starter_status} -eq 0 ] && echo yes || echo no )"
check "starter/ (untouched) reports 22 skipped, 0 failed" "$( echo "${starter_output}" | grep -qE '^22 skipped' && echo yes || echo no )"
echo

# --------------------------------------------------------------------------
echo "4. Never run 'pytest examples starter' in one invocation -- same"
echo "   module name in both directories means the second collected can"
echo "   shadow the first. Documented and checked separately, above."
# --------------------------------------------------------------------------
echo

# --------------------------------------------------------------------------
echo "5. Prove the suite can genuinely FAIL: solve every exercise in a"
echo "   scratch copy, confirm green, break one assertion on purpose,"
echo "   confirm a non-zero exit and a printed FAIL, then restore."
# --------------------------------------------------------------------------

scratch_dir="$(mktemp -d "${TMPDIR:-/tmp}/d124-scratch.XXXXXX")"
cleanup_scratch() { rm -rf "${scratch_dir}"; }
trap cleanup_scratch EXIT

cp "${lab_dir}/examples/test_merge.py" "${scratch_dir}/test_merge.py"
cp "${lab_dir}/examples/data.py" "${scratch_dir}/data.py"
cp "${lab_dir}/examples/conftest.py" "${scratch_dir}/conftest.py"

solved_output="$("${pytest_bin}" "${scratch_dir}" -q 2>&1)"
solved_status=$?
check "scratch copy of the solved suite exits 0" "$( [ ${solved_status} -eq 0 ] && echo yes || echo no )"
check "scratch copy reports 22 passed" "$( echo "${solved_output}" | grep -qE '^22 passed' && echo yes || echo no )"

# Break test_1's exact row-count assertion on purpose: 14 -> 999.
sed -i.bak 's/assert expected_rows == 14/assert expected_rows == 999/' "${scratch_dir}/test_merge.py"

broken_output="$("${pytest_bin}" "${scratch_dir}" -q 2>&1)"
broken_status=$?
check "broken scratch copy exits non-zero" "$( [ ${broken_status} -ne 0 ] && echo yes || echo no )"
check "broken scratch copy prints a FAIL/failed line" "$( echo "${broken_output}" | grep -qiE 'failed|assert' && echo yes || echo no )"

mv "${scratch_dir}/test_merge.py.bak" "${scratch_dir}/test_merge.py"
restored_output="$("${pytest_bin}" "${scratch_dir}" -q 2>&1)"
restored_status=$?
check "restored scratch copy exits 0 again" "$( [ ${restored_status} -eq 0 ] && echo yes || echo no )"
check "restored scratch copy reports 22 passed again" "$( echo "${restored_output}" | grep -qE '^22 passed' && echo yes || echo no )"

cleanup_scratch
trap - EXIT
echo

# --------------------------------------------------------------------------
echo "6. Nothing in examples/ or starter/ opens a network connection"
# --------------------------------------------------------------------------

url_hits="$(grep -rEl 'https?://|ftp://' "${lab_dir}/examples" "${lab_dir}/starter" 2>/dev/null || true)"
check "no URLs inside examples/ or starter/" "$( [ -z "${url_hits}" ] && echo yes || echo no )"
echo

# --------------------------------------------------------------------------
echo "7. Cleanliness -- nothing left behind by THIS run"
# --------------------------------------------------------------------------

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__' -print -o -type d -name '.pytest_cache' -print \) 2>/dev/null || true)"
check "no __pycache__ or .pytest_cache left behind" "$( [ -z "${stray}" ] && echo yes || echo no )"
echo

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