Data Analytics / Chapter 11

The table as a system

A table is a machine for deciding what belongs together.

Constructed teaching examples. Under review; no public QR destination yet.

In this chapter
  1. Investigation 1 · The join changes weight
  2. Investigation 2 · Which denominator?
  3. Reproduce and explain
  4. Code and captured output

Investigation 1 · The join changes weight

Cases A and C are resolved; B is not. Their message counts are 3, 1 and 2.

One vote per case
2 / 3 = 66.7%

A raw case-message join repeats the outcome label.

One vote per joined row
5 / 6 = 83.3%
Case-level 66.7 percent and joined-row 83.3 percent rates from unchanged outcomes.
The change is in the table's grain, not in the cases. · Select the figure to enlarge.

Investigation 2 · Which denominator?

Six constructed dispatch batches contain 100 orders and 21 late orders. Their six batch percentages average 15%; an order-weighted calculation gives 21%.

Updated order-weighted rate
Updated unweighted batch mean

Six batch late rates with pooled order-level 21 percent and unweighted batch average 15 percent.
Choose the unit before interpreting the rate. · Select the figure to enlarge.

Reproduce and explain

Download the executed Python/SQLite laboratory, its constructed CSV, and the draft solutions. Rebuild the CSV as an Excel Table and PivotTable; check counts, filters and refresh.

  1. Why does a one-to-many join change a case-level average?
  2. Does a blank late count mean zero, pending, or unknown?
  3. What would an unrefreshed pivot show after a new row is appended?

Download the sales ledger for the spreadsheet laboratory

Check the expected totals

The reproducible notebook

Inspect the work.

Read the programs, run them in your own environment, and compare the results. The captured output records the chapter’s checked practical run; changing an input may change its answer.

Python table_system_lab.py
"""Constructed Chapter 11 cases, joins and dispatch batches; Python 3 stdlib only."""
from fractions import Fraction
from pathlib import Path
import csv
import sqlite3

HERE = Path(__file__).resolve().parent
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE cases(case_id TEXT PRIMARY KEY, resolved INTEGER CHECK(resolved IN (0,1) OR resolved IS NULL));
CREATE TABLE messages(message_id TEXT PRIMARY KEY, case_id TEXT NOT NULL REFERENCES cases(case_id));
INSERT INTO cases VALUES ('A',1),('B',0),('C',1),('D',NULL);
INSERT INTO messages VALUES ('A1','A'),('A2','A'),('A3','A'),('B1','B'),('C1','C'),('C2','C');
""")
def one(query): return db.execute(query).fetchone()
case_rate = one("SELECT SUM(resolved)*1.0/COUNT(resolved) FROM cases WHERE case_id IN ('A','B','C')")[0]
joined_rate = one("SELECT SUM(c.resolved)*1.0/COUNT(c.resolved) FROM cases c JOIN messages m USING(case_id)")[0]
assert case_rate == 2/3 and joined_rate == 5/6
assert one("SELECT COUNT(*),COUNT(resolved) FROM cases") == (4,3)
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m USING(case_id)")[0] == 7
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id WHERE m.message_id IS NOT NULL")[0] == 6
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id AND m.message_id='A1'")[0] == 4
assert one("SELECT COUNT(*) FROM cases c LEFT JOIN messages m ON c.case_id=m.case_id WHERE m.message_id='A1'")[0] == 1

batches = [
    ('B01','2026-01-05','North',10,1),('B02','2026-01-12','North',20,4),
    ('B03','2026-01-20','South',5,0),('B04','2026-02-03','North',15,3),
    ('B05','2026-02-10','South',10,1),('B06','2026-02-24','South',40,12)]
db.execute("CREATE TABLE dispatches(batch TEXT PRIMARY KEY, date TEXT, region TEXT, orders INTEGER CHECK(orders>0), late INTEGER CHECK(late BETWEEN 0 AND orders))")
db.executemany("INSERT INTO dispatches VALUES (?,?,?,?,?)", batches)
pooled = Fraction(sum(row[4] for row in batches),sum(row[3] for row in batches))
batch_mean = sum((Fraction(row[4],row[3]) for row in batches),Fraction())/len(batches)
assert pooled == Fraction(21,100) and batch_mean == Fraction(3,20)
regions = db.execute("SELECT region,SUM(orders),SUM(late),COUNT(*) FROM dispatches GROUP BY region ORDER BY region").fetchall()
assert regions == [('North',45,8,3),('South',55,13,3)]
with (HERE/'dispatches.csv').open('w',newline='') as file:
    writer=csv.writer(file);writer.writerow(['Batch','Date','Region','Orders','Late']);writer.writerows(batches)
print('CASE RATE 2/3 =',format(case_rate,'.6f'))
print('JOINED MESSAGE-WEIGHTED RATE 5/6 =',format(joined_rate,'.6f'))
print('CASE COUNT / OBSERVED OUTCOME COUNT =',one('SELECT COUNT(*),COUNT(resolved) FROM cases'))
print('LEFT JOIN ROWS / INNER MATCHES =',7,6)
print('FILTER IN ON / FILTER IN WHERE ROWS =',4,1)
print('DISPATCH POOLED RATE =',f'{float(pooled):.2%}')
print('UNWEIGHTED BATCH MEAN =',f'{float(batch_mean):.2%}')
print('REGION orders, late, batches =',regions)
Output Checked chapter run
CASE RATE 2/3 = 0.666667
JOINED MESSAGE-WEIGHTED RATE 5/6 = 0.833333
CASE COUNT / OBSERVED OUTCOME COUNT = (4, 3)
LEFT JOIN ROWS / INNER MATCHES = 7 6
FILTER IN ON / FILTER IN WHERE ROWS = 4 1
DISPATCH POOLED RATE = 21.00%
UNWEIGHTED BATCH MEAN = 15.00%
REGION orders, late, batches = [('North', 45, 8, 3), ('South', 55, 13, 3)]