Data Analytics / Chapter 12

The reproducible workbench

A result deserves a path back to its question.

Constructed data. Local developmental lab; public QR destination pending review.

In this chapter
  1. Investigation · One file, two questions
  2. Audit the record chain
  3. Reproduce the calculation
  4. Code and captured output

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.

Pooled order rate
Mean of six batch rates

B01–B05 remain 60 orders, 9 late; their five rates sum to 0.6.

Two sound calculations, pooled order rate 21 percent and average batch rate 15 percent, answer different questions.
Choose the unit before trusting agreement between programs. · Select the figure to enlarge.

Audit the record chain

Question, input and hash, validation, transformation, and output and claim form a traceable chain.
The checksum identifies bytes; source validity needs separate evidence. · Select the figure to enlarge.
  1. Duplicate B06. Which check should stop the run?
  2. Set B06 late orders to 42 while orders remain 40. Why is zero substitution unacceptable?
  3. 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.

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 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