Investigation · One file, two questions
Six batches contain 100 orders and 21 late orders. The pooled order rate is 21%; the mean of six batch rates is 15%. Change the largest batch and watch which estimate moves.
B01–B05 remain 60 orders, 9 late; their five rates sum to 0.6.

Audit the record chain

- Duplicate B06. Which check should stop the run?
- Set B06 late orders to 42 while orders remain 40. Why is zero substitution unacceptable?
- At case opening, can total messages sent after resolution be a predictor?
Reproduce the calculation
Download the constructed CSV, executed Python script, executed R source, captured R output, and draft solutions. Run Python from this folder. The exact R source and CSV were executed in a local webR 0.6.1 R/WebAssembly runtime; the checked output is retained in the editorial review packet.
The local Python run produced 21.00%, 15.00%, six dispatch rows, seven raw joined rows, four case-grain rows and a SHA-256 input identity. The script checks all values before reporting success.
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 workbench.py
"""Reproducible constructed dispatch and case-message analysis; pandas 3.0 tested."""
from pathlib import Path
from hashlib import sha256
import pandas as pd
HERE=Path(__file__).resolve().parent
source=HERE/'dispatches.csv'
raw=source.read_bytes()
d=pd.read_csv(source,dtype={'Batch':'string','Region':'string'},parse_dates=['Date'])
required={'Batch','Date','Region','Orders','Late'}
assert set(d.columns)==required
assert d['Batch'].is_unique and not d['Batch'].isna().any()
assert (d['Orders']>0).all() and d['Orders'].notna().all()
assert ((d['Late']>=0)&(d['Late']<=d['Orders'])).all()
assert len(d)==6
d['LateRate']=d['Late']/d['Orders']
pooled=d['Late'].sum()/d['Orders'].sum()
batch_mean=d['LateRate'].mean()
assert abs(pooled-.21)<1e-12 and abs(batch_mean-.15)<1e-12
regional=d.groupby('Region',as_index=False,sort=True)[['Orders','Late']].sum()
assert regional.to_dict('records')==[{'Region':'North','Orders':45,'Late':8},{'Region':'South','Orders':55,'Late':13}]
cases=pd.DataFrame({'case_id':['A','B','C','D'],'resolved':pd.Series([1,0,1,pd.NA],dtype='Int64')})
messages=pd.DataFrame({'message_id':['A1','A2','A3','B1','C1','C2'],'case_id':['A','A','A','B','C','C']})
raw_join=cases.merge(messages,on='case_id',how='left',validate='one_to_many',indicator=True)
assert len(raw_join)==7 and (raw_join['_merge']=='left_only').sum()==1
assert raw_join['resolved'].notna().sum()==6
message_counts=messages.groupby('case_id',as_index=False).size().rename(columns={'size':'n_messages'})
case_view=cases.merge(message_counts,on='case_id',how='left',validate='one_to_one',indicator=True)
case_view['n_messages']=case_view['n_messages'].fillna(0).astype('int64')
assert len(case_view)==4 and case_view['resolved'].notna().sum()==3
assert case_view['n_messages'].tolist()==[3,1,2,0]
print('INPUT SHA-256',sha256(raw).hexdigest())
print('DISPATCH ROWS',len(d),'UNIQUE BATCHES',d['Batch'].nunique())
print('POOLED RATE',f'{pooled:.2%}','BATCH MEAN',f'{batch_mean:.2%}')
print('REGION TOTALS',regional.to_dict('records'))
print('RAW LEFT-JOIN ROWS',len(raw_join),'UNMATCHED CASES',int((raw_join['_merge']=='left_only').sum()))
print('CASE-GRAIN ROWS',len(case_view),'KNOWN OUTCOMES',int(case_view['resolved'].notna().sum()))
print('PYTHON/PANDAS CHECKS PASS')
R workbench.R
# Base-R version of the constructed Chapter 12 dispatch and join checks.
# Run from this directory: Rscript workbench.R
# Executed for editorial review in webR 0.6.1 (R/Wasm); see review/ch12-r-executed-output.txt.
d <- read.csv('dispatches.csv', stringsAsFactors = FALSE)
stopifnot(identical(names(d), c('Batch','Date','Region','Orders','Late')))
stopifnot(nrow(d) == 6L, length(unique(d$Batch)) == 6L)
stopifnot(all(d$Orders > 0L), all(d$Late >= 0L & d$Late <= d$Orders))
pooled <- sum(d$Late) / sum(d$Orders)
batch_mean <- mean(d$Late / d$Orders)
stopifnot(isTRUE(all.equal(pooled, .21)), isTRUE(all.equal(batch_mean, .15)))
regional <- aggregate(cbind(Orders,Late) ~ Region, data=d, FUN=sum)
regional <- regional[order(regional$Region),]
stopifnot(identical(as.integer(regional$Orders), c(45L,55L)))
stopifnot(identical(as.integer(regional$Late), c(8L,13L)))
cases <- data.frame(case_id=c('A','B','C','D'),resolved=c(1L,0L,1L,NA_integer_))
messages <- data.frame(message_id=c('A1','A2','A3','B1','C1','C2'),case_id=c('A','A','A','B','C','C'))
joined <- merge(cases,messages,by='case_id',all.x=TRUE,sort=TRUE)
stopifnot(nrow(joined)==7L, sum(is.na(joined$message_id))==1L)
counts <- aggregate(message_id ~ case_id,data=messages,FUN=length)
names(counts)[2] <- 'n_messages'
case_view <- merge(cases,counts,by='case_id',all.x=TRUE,sort=TRUE)
case_view$n_messages[is.na(case_view$n_messages)] <- 0L
stopifnot(nrow(case_view)==4L, identical(as.integer(case_view$n_messages),c(3L,1L,2L,0L)))
cat('DISPATCH ROWS',nrow(d),'UNIQUE BATCHES',length(unique(d$Batch)),'\n')
cat(sprintf('POOLED RATE %.2f%% BATCH MEAN %.2f%%\n',100*pooled,100*batch_mean))
print(regional,row.names=FALSE)
cat('RAW LEFT-JOIN ROWS',nrow(joined),'UNMATCHED CASES',sum(is.na(joined$message_id)),'\n')
cat('CASE-GRAIN ROWS',nrow(case_view),'KNOWN OUTCOMES',sum(!is.na(case_view$resolved)),'\n')
cat('BASE-R CHECKS PASS\n')
Output Checked chapter run
INPUT SHA-256 15d27568c481671f650fa02b78372f36e4a32132597d0f346eb708aea16e7a21
DISPATCH ROWS 6 UNIQUE BATCHES 6
POOLED RATE 21.00% BATCH MEAN 15.00%
REGION TOTALS [{'Region': 'North', 'Orders': 45, 'Late': 8}, {'Region': 'South', 'Orders': 55, 'Late': 13}]
RAW LEFT-JOIN ROWS 7 UNMATCHED CASES 1
CASE-GRAIN ROWS 4 KNOWN OUTCOMES 3
PYTHON/PANDAS CHECKS PASS