Join Operations

Grouping stays within a single table. Most questions of analysis, however, ask for more than one table: the field visits have to come next to the reading table. The join lesson of the Data Collection and Preparation course did this job with a hand-written matching loop and kept the row calculation item by item; 3,471 readings and 1,329 subscribers gave 3,302 rows, 169 readings did not match, and 69 subscribers never appeared in the output. That calculation is the input of this lesson and is not repeated.

In a dataframe interface the same job is a single call, and that call chooses a join type. The measure of this lesson is this: the same two tables, the same key, four modes and four different row counts. A second number comes next to it: when the key grain is not verified, how many rows multiply and how much the total inflates.

• DF22. The join type is the decision that determines the row count of the output. The inner join keeps only the matching rows, the left outer also keeps the unmatched left rows, the full outer also keeps the unmatched ones on both sides. The cross join never reads the key and produces all the pairs of the two tables.
• DF23. Key grain is how many rows a key value corresponds to on each side. The relationship can be one-to-one, one-to-many or many-to-many, and the join does this without verifying it.
• DF24. Fan-out is a left row being repeated as many times as it has matches on the right. Fan-out is not an error; the error is taking a total over a multiplied table.
• DF25. Even when the output row count is lower than the input, no elimination may have been made: within the same operation some rows may drop while others multiply, and the net change hides both.

Two Tables and Three Sets

# dataset.py — MODEL: the long-format reading table of the previous lesson is
# regenerated from the same seed. The dataset is fiction; the detail of the
# generator is in the first lesson of the topic.
import math

M32 = 0xFFFFFFFF
SEED, PERIODS = 20260218, ["2026-01", "2026-02", "2026-03"]
REGION = [("north", 0.28, 21), ("south", 0.22, 17), ("east", 0.18, 26),
          ("west", 0.14, 14), ("central", 0.18, 23)]

def rng(seed: int):
    s = ((seed ^ (seed >> 16)) * 2246822507) & M32
    s = ((s ^ (s >> 13)) * 3266489909) & M32
    s ^= s >> 16
    def next_value() -> float:
        nonlocal s
        s = (s * 1664525 + 1013904223) & M32
        return s / 4294967296
    return next_value

def pick(u: float, weights: list[float]) -> int:
    t = 0.0
    for i, p in enumerate(weights):
        t += p
        if u < t:
            return i
    return len(weights) - 1

subscribers, dropped = [], []
for i in range(1400):
    r = rng(SEED + i)
    name, _, base = REGION[pick(r(), [b[1] for b in REGION])]
    r(); r()  # the household and satisfaction draws are not used in this lesson
    (dropped if r() < 0.046 else subscribers).append(
        {"subscriber_id": 10001 + i, "region": name, "base": base})

valid = {k["subscriber_id"] for k in subscribers}
pairs: dict[tuple[int, str], float] = {}
for k in subscribers + dropped:
    r = rng(SEED + 7000 + k["subscriber_id"])
    for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])):
        t = 0.0 if r() < 0.038 else math.floor(k["base"] * math.exp(
            (r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100 + 0.5) / 100
        if k["subscriber_id"] in valid:
            pairs.setdefault((k["subscriber_id"], PERIODS[d]), t)
        r()
LONG = [{"subscriber_id": a, "period": d, "consumption_m3": t} for (a, d), t in pairs.items()]

# Field visits table: one row is one visit and it is not tied to a period.
visits = []
for k in subscribers:
    r = rng(SEED + 31000 + k["subscriber_id"])
    for z in range(pick(r(), [0.58, 0.27, 0.11, 0.04])):
        visits.append({"subscriber_id": k["subscriber_id"],
                       "outcome": ["positive", "repeat", "unreachable"][
                           pick(r(), [0.60, 0.28, 0.12])]})

readers = {o["subscriber_id"] for o in LONG}
visited = {z["subscriber_id"] for z in visits}
print(f"reading table {len(LONG)} rows, {len(readers)} subscribers")
print(f"visit table {len(visits)} rows, {len(visited)} subscribers")
print(f"{len(readers & visited)} subscribers on both sides; only in readings "
      f"{len(readers - visited)}, only in visits {len(visited - readers)}")

reading table 3199 rows, 1260 subscribers
visit table 818 rows, 559 subscribers
534 subscribers on both sides; only in readings 726, only in visits 25

Three numbers are known before the join, and these three numbers explain in advance all the row counts that will come out. 534 subscribers are in both tables, 726 subscribers only in the reading table, 25 subscribers only in the visit table. The sum of these three sets is 1,285, and choosing a join type is in fact choosing which of these three sets will be represented in the output.

One more detail directly affects the row count: the two tables do not carry the same grain. In the reading table a row is one period of a subscriber, in the visit table a row is one visit and it is not tied to a period. When only the subscriber number is used as the key, three readings and two visits of a subscriber produce six rows. This is a property that can be read from the tables themselves and it is known before the join call.

The Same Two Tables, Four Modes

# join_tables.py — the same two tables with four join types. The mode is the
# decision that determines the row count of the output.
PASSES = {"count": 0}

def join_tables(left, right, field: str, mode: str = "inner"):
    """Puts the two sides next to each other on the key. mode: inner, left_outer, full_outer, cross."""
    if mode == "cross":
        PASSES["count"] += len(left) * len(right)
        return len(left) * len(right), 0, 0
    index: dict = {}
    for s in right:
        index.setdefault(s[field], []).append(s)
    PASSES["count"] += len(right) + len(left)
    produced, empty_left, matched = 0, 0, set()
    for l in left:
        e = index.get(l[field])
        if not e:
            empty_left += 1
            produced += 1 if mode in ("left_outer", "full_outer") else 0
            continue
        produced += len(e)
        matched.add(l[field])
    empty_right = len({s[field] for s in right} - matched)
    if mode == "full_outer":
        produced += sum(1 for s in right if s[field] not in matched)
    return produced, empty_left, empty_right

WIDTH = [22, 12, 12, 12, 12]
show = lambda h: "".join(str(v).rjust(WIDTH[k]) if k else str(v).ljust(WIDTH[0])
                         for k, v in enumerate(h))
print(show(["join type", "left in", "right in", "output", "unmatched"]))
result = {}
for mode, name in [("inner", "inner"), ("left_outer", "left outer"), ("full_outer", "full outer"),
                   ("cross", "cross")]:
    PASSES["count"] = 0
    produced, el, er = join_tables(LONG, visits, "subscriber_id", mode)
    result[mode] = (produced, el, er, PASSES["count"])
    print(show([name, len(LONG), len(visits), produced, el if mode != "cross" else 0]))
print(f"\ninner join drops {result['inner'][1]} of {len(LONG)} readings and produces "
      f"{result['inner'][0]} rows")
print(f"cross join never reads the key: {len(LONG)} x {len(visits)} = "
      f"{result['cross'][0]} rows")

join type                  left in    right in      output   unmatched
inner                         3199         818        1965        1855
left outer                    3199         818        3820        1855
full outer                    3199         818        3856        1855
cross                         3199         818     2616782           0

inner join drops 1855 of 3199 readings and produces 1965 rows
cross join never reads the key: 3199 x 818 = 2616782 rows

Four modes, the same two tables, four different row counts: 1,965, 3,820, 3,856 and 2,616,782. The ratio between the smallest and the largest is 1,332 times, and the only thing that makes the choice is a mode name that is written or not written in the call.

The number of the inner join is interesting in two directions at once. The 1,965 rows that come out are fewer than what went in, and at first sight this looks like an elimination. It is not: 1,855 readings have dropped, and the remaining 1,344 readings have opened into 1,965 rows. Because the net change is negative, the fan-out is not visible inside the number. This is the same behavior that was measured in the Data Collection and Preparation course; what is new here is that the whole operation fits into a single call and that none of the numbers 1,855 and 621 comes back from the call.

The left outer join gives 3,820 rows and keeps all the rows of the reading table: 1,965 matched rows and 1,855 with an empty visit side. The full outer, with 3,856 rows, also adds the 36 visit rows of the 25 subscribers who are only in the visit table. The cross join, on the other hand, never reads the key; the 2,616,782 rows it produces are not the answer to a question, they are all the pairs of the two tables.

Fan-Out and the Key Grain

# fan_out.py — if the uniqueness of the key is not verified, the join counts the same
# row more than once. The measure is the multiplied rows and the inflated total.
counts: dict[int, int] = {}
for z in visits:
    counts[z["subscriber_id"]] = counts.get(z["subscriber_id"], 0) + 1
matched = [o for o in LONG if o["subscriber_id"] in counts]
produced = sum(counts[o["subscriber_id"]] for o in matched)
total_of = lambda a: round(sum(x["consumption_m3"] for x in a), 2)
inflated = round(sum(o["consumption_m3"] * counts[o["subscriber_id"]] for o in matched), 2)
print(f"dataset {len(LONG)} rows, {total_of(LONG)} m3")
print(f"matched {len(matched)} rows, {total_of(matched)} m3")
print(f"inner join output {produced} rows, {inflated} m3; "
      f"{produced - len(matched)} rows of fan-out, {round(inflated - total_of(matched), 2)} m3 excess counting")

def relationship(left, right, field: str) -> str:
    """The key grain: is it unique on both sides, or is it many on one side."""
    left_unique = len({s[field] for s in left}) == len(left)
    right_unique = len({s[field] for s in right}) == len(right)
    return ("one-to-one" if left_unique and right_unique else
            "one-to-many" if right_unique or left_unique else "many-to-many")

many = sum(1 for o in matched if counts[o["subscriber_id"]] > 1)
print(f"\nkey grain: {relationship(LONG, visits, 'subscriber_id')}; on the left side "
      f"the key of {many} rows corresponds to more than one row on the right")
print(f"when no verification is made, these {many} rows silently open into {produced - len(matched) + many} "
      f"rows")
print(f"\npasses: hand-written nested loop {len(LONG) * len(visits)}, indexed join "
      f"{len(LONG) + len(visits)}")
print(f"rows  : the same two tables produce {result['inner'][0]}, {result['left_outer'][0]}, "
      f"{result['full_outer'][0]} and {result['cross'][0]} rows in four modes")

dataset 3199 rows, 63060.63 m3
matched 1344 rows, 26450.81 m3
inner join output 1965 rows, 38701.46 m3; 621 rows of fan-out, 12250.65 m3 excess counting

key grain: many-to-many; on the left side the key of 468 rows corresponds to more than one row on the right
when no verification is made, these 468 rows silently open into 1089 rows

passes: hand-written nested loop 2616782, indexed join 4017
rows  : the same two tables produce 1965, 3820, 3856 and 2616782 rows in four modes

The price of the fan-out is read on the second line. The total of the 1,344 matched readings is 26,450.81 m³; the total of the same readings in the join output is 38,701.46 m³. The 12,250.65 m³ in between is not new consumption, it is the same readings being counted a second and a third time. The January reading of a subscriber has entered the total twice because that subscriber has two visits.

The danger of this number is not in its size but in its plausibility. 38,701.46 m³ is smaller than the true total of the dataset, that is, 63,060.63 m³; if an audit looked at “the total does not exceed the dataset total”, it would pass this table. Yet the table carries a total that is both undercounted and overcounted: 1,855 readings have not been counted at all, 468 readings have been counted more than once.

The last two lines put the gain and the hidden default side by side. The gain is clear: while a hand-written nested loop makes 2,616,782 comparisons, a join that builds a key index on the right side and walks the left side once finishes in 4,017 passes, which is 651 times fewer. The hidden default, on the other hand, is that the relationship is many-to-many and that this is verified nowhere. The join call does not ask what the relationship is; had it been asked, it would have been known before the call that 468 rows would open into 1,089 rows.

Fixing the Key Grain

# grain_fix.py — what prevents the fan-out is not the join type but bringing the right side
# to the grain: aggregate first, then join.
visit_summary = [{"subscriber_id": no, "visit_count": n} for no, n in counts.items()]
unique = len({z["subscriber_id"] for z in visit_summary}) == len(visit_summary)
print(f"visit table fell from {len(visits)} rows to {len(visit_summary)} rows; "
      f"is the key unique: {'yes' if unique else 'no'}")
print(f"key grain is now {relationship(LONG, visit_summary, 'subscriber_id')}")
inner2, empty_left2, empty_right2 = join_tables(LONG, visit_summary, "subscriber_id", "inner")
left2, _, _ = join_tables(LONG, visit_summary, "subscriber_id", "left_outer")
print(f"\ninner join {inner2} rows (not {result['inner'][0]}), total {total_of(matched)} m3")
print(f"left outer join {left2} rows, total {total_of(LONG)} m3: the incoming rows and the incoming "
      f"total did not change")
print(f"fan-out fell from {result['inner'][0] - len(matched)} rows to {inner2 - len(matched)} rows, "
      f"excess counting from {round(inflated - total_of(matched), 2)} m3 to 0.0 m3")

visit table fell from 818 rows to 559 rows; is the key unique: yes
key grain is now one-to-many

inner join 1344 rows (not 1965), total 26450.81 m3
left outer join 3199 rows, total 63060.63 m3: the incoming rows and the incoming total did not change
fan-out fell from 621 rows to 0 rows, excess counting from 12250.65 m3 to 0.0 m3

What removes the fan-out is not changing the join type but bringing the right side to the grain of the left side. When the visit table is aggregated per subscriber and falls from 818 rows to 559 rows, the key becomes unique, the relationship stops being many-to-many and returns to one-to-many, and the same join call no longer multiplies any row. The excess counting falls from 12,250.65 m³ to zero, and this fall is not a correction; it never occurred.

The last line gives the check that this lesson was looking for. The left outer join made with the right grain closes at 3,199 rows and 63,060.63 m³: the incoming row count and the incoming total have not changed. This is the only cheap sign that a join was done correctly: if the row count and the total of the left table are preserved in the output, the left side has neither dropped nor multiplied. If they are not preserved, the difference is written in two items, and these two items are numbers that the call itself never reports.

Aggregating first and joining afterwards also has a price, and it has to be written down: the detail that the 818 visit rows carried, that is, the outcome of each visit, is lost inside the summary. The grain decision therefore depends on the question to be asked. The question “how many visits per subscriber” is answered with the summary table, while the question “how many visits closed as unreachable” is asked of the visit table itself and is never joined with the reading table.

Summary

• The join type determines the row count of the output: the same two tables give 1,965 rows in the inner join, 3,820 in the left outer, 3,856 in the full outer and 2,616,782 in the cross join.
• That the inner join gives fewer rows than went in does not mean an elimination: 1,855 readings have dropped, the remaining 1,344 readings have opened into 1,965 rows, and the net change hides both.
• Fan-out inflates the total: while the true total of the matched readings is 26,450.81 m³, the total in the output is 38,701.46 m³, and the 12,250.65 m³ in between is readings that have been counted a second time.
• Because the inflated total stays below the dataset total, it passes an upper-bound check; the table has undercounting and overcounting at the same time.
• The relationship of the key is many-to-many and is not verified: 468 rows silently open into 1,089 rows. The indexed join finishes in 4,017 passes, the nested loop makes 2,616,782 comparisons.

Next Step

The join put the tables next to each other and the row calculation was written down. One axis has not been handled in the dataset yet: time. In the reading table the period is a text column and it can be sorted, but a question between periods such as “how many weeks have passed in between” or “in which week is there no reading at all” cannot be asked in this form. When the time axis is moved to the label index these questions become askable, and a new decision arises: in a resample, is a period that carries no record counted as zero, or counted as never having existed. The next lesson calculates these two answers separately and measures how they change the total and the average.