Module 7: Loading, Running SQL and Checking a Reproduction

class: center, middle, inverse, title-slide

.title[
# Module 7: Loading, Running SQL and Checking a Reproduction
]
.subtitle[
## The Python around one SQL file in a real replication
]

---

# Course Map

<table>
<tr><th>#</th><th>Module</th><th>Status</th></tr>
<tr><td>1</td><td><a href="../module-01/slides.html">Python for R Users</a></td><td>✓ done</td></tr>
<tr><td>2</td><td><a href="../module-02/slides.html">pandas basics</a></td><td>✓ done</td></tr>
<tr><td>3</td><td><a href="../module-03/slides.html">Joins, merges, group-by recipes</a></td><td>✓ done</td></tr>
<tr><td>4</td><td><a href="../module-04/slides.html">Regression and A/B tests with statsmodels</a></td><td>✓ done</td></tr>
<tr><td>5</td><td><a href="../module-05/slides.html">End-to-end interview scenario</a></td><td>✓ done</td></tr>
<tr><td>6</td><td><a href="../module-06/slides.html">Reading and Reviewing Someone Else's Python</a></td><td>✓ done</td></tr>
<tr><td><b>7</b></td><td><b>Loading, Running SQL and Checking a Reproduction</b> <i>(you are here)</i></td><td>← current</td></tr>
</table>

---

# Why This Module Breaks the Pattern

Module 6 read a codebase you did not write. This one is about the **Python around a SQL
file** in a real reproduction: [`fhoces/opa-prop40`](https://github.com/fhoces/opa-prop40),
folder `bsz-analysis/py/`, plus its R twin `R/run_sql.R`.

The raw data there (daily Forbes billionaire snapshots, about 6 million rows) are
confidential. The drills run on a **synthetic panel in the same shape**:
`python data/build_rtb_sample.py` (60 invented people, random-walk fortunes).

The SQL itself is a black box here: it turns one big table into four smaller ones.
(The SQL course, sql-industry-prep module 6, walks through it.)

The point: **plumbing is where silent errors live**. An empty string that should have
been NULL, a float rounded on the way out, rows that came back in a different order.

---

# The Pipeline, File by File

| File | Job | R mental model | Drill |
|---|---|---|---|
| `bundle_paths.py` | where every file lives; useful errors | `here::here()`, `Sys.getenv()` | |
| `load_bundle.py` | raw CSV → SQLite, in batches, NULLs explicit | `read_csv_chunked()` + `dbAppendTable()` | Q1 |
| `run_sql.py` | run the shared `.sql`; export sorted CSVs | `dbExecute()` + `dbGetQuery()` | Q2 |
| `check_rtb_ca.py` | align on keys, compare with tolerance, exit code | `full_join()` + `all.equal()` | Q3, Q4 |
| `R/run_sql.R` | the same `.sql` from R, for the parity check | | |

**One SQL file, two languages.** If Python's and R's exports agree, any bug left is in the
SQL or the data, not in the plumbing. In the real run they agreed to exactly zero.

---

# `bundle_paths.py`: One Place That Knows Where Things Are

```python
*BSZ_DIR = Path(__file__).resolve().parent.parent   # the folder holding py/

def bundle_dir(must_exist=True):
*   raw = os.environ.get(ENV_VAR, "")                # Sys.getenv("BSZ_BUNDLE_DIR", "")
    path = Path(raw) if raw else DEFAULT_BUNDLE_DIR
    if must_exist and not path.is_dir():
*       raise FileNotFoundError(
            f"Author bundle not found at {path}. It is confidential and not in git; "
            f"place it there or set the {ENV_VAR} environment variable to its root."
        )
    return path

def sqlite_path():
*   return BSZ_DIR / "data-raw" / "bundle.sqlite"   # file.path(), but an object
```

.small[
- **`Path` and `/`**: the operator joins path pieces; the object has `.exists()`, `.is_dir()`, `.rglob()`, `.read_text()`.
- **An env var with a default** lets a co-author or a worktree point elsewhere without editing code.
- **The error names the path it tried and the fix.** `find_in_bundle()` also insists on exactly one match, so a duplicated file fails loudly.
]

---
name: load-main

# `load_bundle.py`: Reading a Big File in Pieces

```python
with open(path, newline="", encoding="utf-8") as f:
    reader = csv.reader(f)
    header = next(reader)
*   if header != names:
        raise ValueError(f"Unexpected header in {path.name}: {header}")
    batch = []
    for row in reader:
        batch.append([cast(v) for cast, v in zip(casts, row)])
*       if len(batch) >= BATCH:                    # BATCH = 100_000
*           con.executemany(insert, batch)
            n += len(batch)
            batch = []
*   con.executemany(insert, batch)                 # the leftover rows
    n += len(batch)
```

.small[
- **Memory holds one batch**, whatever the file size (about 5.9 million rows in the real file).
- **pandas form (drill 1):** `pd.read_csv(path, dtype=str, keep_default_na=False, chunksize=5000)` returns an iterator of DataFrames. R: `readr::read_csv_chunked(path, callback)`.
- **Check the header**: a column inserted in the middle of a new file version fails here, not silently later.
]

---

# Empty String, NaN and None: What Reaches SQLite

The bundle writes a missing value as an empty field. Three ways it can arrive:

.small[
| You read with | An empty field becomes | In SQLite |
|---|---|---|
| `csv.reader`, no conversion | `""` | the text `''`: **not NULL**, counted by `COUNT(col)` |
| `pd.read_csv` defaults | `NaN` | NULL, but `"NA"`, `"null"`, `"NaN"` also become NaN |
| `dtype=str, keep_default_na=False`, then `""` → `None` | `None` | NULL, and nothing else touched |
]

```python
def _text(v):
*   v = v.strip(" \t")          # readr::read_csv trims (trim_ws = TRUE); Python does not
*   return v if v != "" else None

def _real(v):
    v = v.strip(" \t")
    return float(v) if v != "" else None
```

Many `source` values in the real file end in a space: without the strip, "software" and
"software " are two industries. NULLs matter because SQL aggregates skip them, like R's
`na.rm = TRUE`.

---

# Parameter Binding, and Indexes After the Load

```python
insert = (
    f"INSERT INTO rtb_all_combined ({', '.join(names)}) "
*   f"VALUES ({', '.join('?' * len(names))})"          # one ? per column
)
*con.executemany(insert, batch)                         # values travel separately
...
# Indexes after the load: building them once is faster than
# updating them on every insert.
*con.execute("CREATE INDEX idx_rtb_all_combined_date ON rtb_all_combined (date)")
*con.execute("CREATE INDEX idx_rtb_all_combined_id ON rtb_all_combined (forbes_id)")
```

.small[
- **`?` placeholders, not f-strings in `VALUES`.** Formatting values into SQL breaks on `O'Dunmore`, turns `None` into the text `'None'`, and allows injection. SQLite also parses the statement once. R: `dbExecute(con, sql, params = ...)`, `dbAppendTable()`.
- **Indexes at the end**, on the columns the SQL filters and joins on. `PRAGMA journal_mode = OFF` and `synchronous = OFF` trade crash safety for speed: fine for a rebuildable file.
- **Then count**: `main()` compares `SELECT COUNT(*)` with the rows inserted and raises if they differ, and prints the seconds each loader took.
]

---

# `run_sql.py`: `executescript` Versus `execute`

```python
def run_sql_file(con, sql_file):
*   con.executescript(Path(sql_file).read_text(encoding="utf-8"))
    con.commit()

def main(argv=None):
    p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
*   p.add_argument("sql_file")                                   # positional
*   p.add_argument("--export", nargs="*", default=[], metavar="TABLE")
    p.add_argument("--db", default=None, help="SQLite file (default data-raw/bundle.sqlite)")
    p.add_argument("--out", default=None, help="export directory (default data-raw/sql-out/py)")
*   a = p.parse_args(argv)                                       # None = the real command line
```

.small[
- **`execute`** runs exactly one statement ("You can only execute one statement at a time"). **`executescript`** runs a whole file in order, stops at the first error (earlier statements stay done), and commits first.
- `executescript` takes **no parameters**: trusted files only, never user input.
- **R has no `executescript`**: `DBI::dbExecute()` runs one statement, so `run_sql.R` splits the file itself.
- **`argparse`**: `main(argv=None)` lets a test pass a list instead of a shell command line. R: `commandArgs(trailingOnly = TRUE)` and parse by hand.
]

---
name: export-main

# Exports That Never Change by Accident

```python
EXPORT_ORDER = {                                 # keep in step with R/run_sql.R
*   "rtb_ca_eoy": "date, forbes_worth DESC, forbes_id",
    "rtb_ca_aggregate": "date",
}
def _fmt(v):
    if v is None:
*       return ""                                # NULL is an empty field
    if isinstance(v, float):
*       return repr(v)                           # shortest text that reads back exactly
    return str(v)

def export_table(con, table, out_dir):
    ...
*   rows = con.execute(f"SELECT * FROM {table} ORDER BY {order}").fetchall()
    with open(path, "w", newline="", encoding="utf-8") as f:
*       w = csv.writer(f, lineterminator="\n")
```

.small[
- **Always sort by a key, with a tie-breaker** (`forbes_id`). A table has no guaranteed row order.
- **`repr(0.1 + 0.2)` is `'0.30000000000000004'`**; `f"{x:.6f}"` would lose digits, more digits would churn the file.
- Goal: two runs, two machines, two languages, **byte-identical files**. Drill 2 is graded byte by byte.
]

---
name: r-main

# The R Twin: What R Has to Build by Hand

```r
run_sql_file <- function(con, sql_file) {
  sql <- paste(readLines(sql_file, warn = FALSE, encoding = "UTF-8"), collapse = "\n")
* stmts <- sql_split_statements(sql)        # a state machine: ; only when it is code
* for (s in stmts) DBI::dbExecute(con, s)   # one statement per call
  invisible(length(stmts))
}
.format_double <- function(x) {              # R's stand-in for Python's repr()
  ...
*   s <- sprintf("%.15g", v)
*   if (as.numeric(s) != v) s <- sprintf("%.16g", v)
*   if (as.numeric(s) != v) s <- sprintf("%.17g", v)
    if (!grepl("[.eEn]", s)) s <- paste0(s, ".0")
```

.small[
- R's text-to-number parser is not always correctly rounded in the 16th digit, so R sometimes writes **17 digits where Python writes 16**: same doubles, different text. Hence a parity check that **parses numbers** (drill 4).
- `if (sys.nframe() == 0L && !interactive())` guards the entry point: R's `if __name__ == "__main__":`.
]

<a href="#r-detail" class="nav-btn">The statement splitter →</a>

---
name: check-main

# `check_rtb_ca.py`: Align on Keys, Then Compare

The real checker keys dicts on `forbes_id` / `industries` / `date`. The pandas form, drill 3:

```python
m = ours.merge(key, on=["date"], how="outer",
*              suffixes=("_ours", "_key"), indicator=True)
*m["_merge"].value_counts()          # both / left_only / right_only
both = m[m["_merge"] == "both"]
diff = (both["forbes_worth_total_ours"] - both["forbes_worth_total_key"]).abs()
*cells_off = int((diff > 1e-6).sum())
```

.small[
- **Never compare row 1 with row 1**: one extra row shifts every later one. R: `full_join()` plus `anti_join()` both ways.
- **Rows on one side only are not equal**: dates only in ours are reported, not failed (a newer data file has more days); dates only in the key fail.
- **Tolerance**: counts and text exactly; numbers within `1e-6` (answer keys), `1e-9` (R vs Python), `0.005` (cells typed with 2 decimals). Summation order changes last digits. A missing person changes a count, which has no tolerance. R: `max(abs(a - b)) <= 1e-6` (`all.equal()` compares a mean relative difference).
- **Exit code**: `return 1 if failures else 0`, then `sys.exit(main())`. R: `quit(status = 1)`.
]

<a href="#check-detail" class="nav-btn">The real comparison →</a>

---
class: center, middle, inverse

# Part 2: An R Pipeline and Its Python Twin

The paper's second step, the public workbook to every exported number, in both languages

---
name: twin-main

# Function by Function, File by File

`bsz-analysis/py/` holds one Python file per R file, **with the same function names**:

| R file | Python twin | What it does |
|---|---|---|
| `R/ingest_excel.R` | `py/ingest_excel.py` | `read_sheet()`: a sheet by position |
| `R/excel_cells.R` | `py/excel_cells.py` | `xls_cell()`, `xls_cells_row()`: cells as numbers |
| `R/workbook_db.R` | `py/workbook_db.py` | load two sheets into SQLite, run the shared `.sql` files |
| `R/compute_*.R`, `R/tables.R`, `R/figures.R` | `py/compute_*.py`, ... | every computation; the **data** behind each table and figure |
| `_targets.R` | `py/run_export.py` | run it all, write `export/py/` |

.small[
- **Mirror, do not redesign.** Same column names and order; R's named list becomes a dict; spreadsheet quirks the R code reproduces are reproduced again. The twin exists to be compared.
- **Keep the R index in a comment.** A 2018-based panel's 2021 slot is `x[4]` in R and `x[3]` in numpy: `# R: anchor_i is a + 1`.
- **Say what is not twinned**: gt and ggplot styling, the report's code listings. The numbers behind them are twinned.
- **Varying literals in one file**: `vintage.py` / `R/vintage.R`, switched by the `BSZ_VINTAGE` environment variable.
]

---
name: openpyxl-main

# openpyxl: Reading a Sheet the Way readxl Does

```python
@lru_cache(maxsize=4)
def _workbook(path):
    # data_only: the cached values of formula cells, as readxl reads them.
*   return openpyxl.load_workbook(path, data_only=True, read_only=True)

def sheet_grid(path, sheet):
    ws = _workbook(str(path))[sheet]
    rows = []
*   for row in ws.iter_rows(values_only=True):
*       rows.append(tuple(excel_serial(v) if isinstance(v, datetime.datetime) else v
                          for v in row))
    # readxl drops trailing empty rows and columns.
*   while rows and all(v is None for v in rows[-1]):
        rows.pop()
    ...
```

.small[
- **`data_only=True`**: the number Excel last computed, not `"=SUM(B4:B9)"`. A file no program ever calculated has no cached values (you get `None`).
- **Positional frame**: columns `A, B, ..., Z, AA, AB` and `df.index = range(1, n + 1)`, so `df.at[16, "G"]` is G16, like R's `df$G[16]`.
- **readxl's habits**: trailing empty rows dropped (a formatted empty cell makes openpyxl report more rows); a date becomes its serial number (2018-12-31 is 43465).
- **Last digit**: readxl keeps the 17-digit text and R parses it; openpyxl uses Python's correctly rounded `float()`. Gaps of about 1e-16.
]

---

# A Cell as R's `as.numeric()` Sees It

.pull-left[
```python
def to_num(v):
    """R's suppressWarnings(as.numeric(v))"""
*   if v is None or isinstance(v, bool):
        return math.nan
    if isinstance(v, (int, float)):
        return float(v)
    s = str(v).strip()
*   if "_" in s:  # float() reads "1_000";
*       return math.nan  # as.numeric() does not
    try:
        return float(s)
    except ValueError:
        return math.nan
```
]

.pull-right[
.small[
| Cell | R | `float()` | `to_num()` |
|---|---|---|---|
| `" 97.293 "` | 97.293 | 97.293 | 97.293 |
| `"1_000"` | NA | 1000.0 | NaN |
| `"n/a"` | NA | error | NaN |
| a `TRUE` cell | NA | 1.0 | NaN |
| empty | NA | error | NaN |
]

R reads a mixed column as text, so a `TRUE` cell arrives as `"TRUE"` and becomes `NA`.
`xls_cell(df, "G16")` splits the address with `^([A-Z]+)([0-9]+)$` and returns NaN
outside the sheet, as `df$G[999]` is `NA` in R.
]

---
name: idioms-main

# pandas Twins of the R Idioms (Drill 7)

```python
def compute_data_sec_agg(data_sec_all, exclude_ids, cols):
*   d = data_sec_all[~data_sec_all["forbes_id"].isin(list(exclude_ids))]       # !%in%
*   d = d[~d.duplicated(subset=["year", "forbes_id", "forbes_worth"], keep="first")]
    g = d.groupby("year", sort=True)
*   out = g[list(cols)].sum() / 1000          # sum(x, na.rm = TRUE): all-NaN gives 0
*   out.insert(0, "n", g.size())              # n(): rows, not non-missing values
    return out.reset_index()
```

.small[
| R | Python | The trap |
|---|---|---|
| `!duplicated(pick(a, b, c))` | `~df.duplicated(subset=[...])` | `drop_duplicates()` with no subset compares every column |
| `n()` | `.size()` | `.count()` skips NaN (one kept row has no worth) |
| `sum(x, na.rm = TRUE)` | `.sum()` or `np.nansum()` | `.sum(min_count=1)` and `np.sum` give NaN |
| `c(x[-1], 0)` | `np.append(x[1:], 0)` | Python's `x[-1]` is the **last** element |
| `seq(0, 0.2, by = 0.001)` | `0 + np.arange(n + 1) * 0.001` | `np.arange(0, 0.2, 0.001)` has no 0.2: 200 values, not 201 |
]

---
name: parity-main

# The Parity Test: Relative Tolerance, Strict on Everything Else

```r
# condensed from compare_parity_frames() in tests/testthat/helper-py-parity.R
if (!identical(names(r), names(py))) problems <- c(problems, "columns differ")
if (nrow(r) != nrow(py)) problems <- c(problems, "rows differ")
...
* if (!identical(is.na(a), is.na(b))) { problems <- c(problems, "missing values differ"); next }
  d <- abs(a - b)[!is.na(a)]
* rel <- d / pmax(1, abs(a[!is.na(a)]))
* if (any(rel > PY_PARITY_TOL)) problems <- c(problems, "max rel diff above 1e-9")
```

.small[
- **Relative**: one unit in the last place grows with the number. At 2e12 it is about 2e-4, so an absolute 1e-9 fails on pure rounding.
- **`max(1, |r|)` floor**: a check that should be 0 and holds 1e-13 would turn a 3e-16 gap into a 0.3% "difference".
- **1e-9**: real gaps are about 1e-16 (17-digit parsing, R's two-pass `mean()`, numpy's pairwise sums); the paper prints to 1e-3.
- **Strict elsewhere**: names, row counts, missing cells and text must match; a Python file with no R counterpart fails.
- **Skips, never fails**, when `export/py/` is absent or from the other vintage. Result on the real run: 50 outputs, 53,855 numbers, largest gap 5e-15 relative.
]

<a href="#parity-detail" class="nav-btn">The real comparison →</a>

---

# The Drills

.small[
| Drill | You write | Graded on |
|---|---|---|
| Q1 | `load_csv(con, csv_path, table, columns, index_on, chunksize)` | rows, NULL counts, distinct values (trimming), sums, types, indexes |
| Q2 | `run_and_export(con, sql_file, tables, out_dir, order_by)` | byte-identical CSVs; a newest-first re-export proves the sort is yours |
| Q3 | `compare(ours, key, key_cols, num_cols, tol)` | a pretend authors' sheet: 5 dates short, one cell off by 1e-9, one by 0.25 |
| Q4 | `parity(dir_a, dir_b, tables, tol)` | an R-style export (17 digits) passes; one value moved by 1e-6 fails |
| Q5 | `read_sheet(path, sheet)` | shape, letters A to AB, index from 1, a date as 43465.0 |
| Q6 | `xls_cell(df, addr)` | 18 cells: text numbers, `"1_000"`, `"n/a"`, `TRUE`, outside the sheet |
| Q7 | `compute_data_sec_agg(data_sec_all, exclude_ids, cols)` | the real SQL file's result, 1e-9 relative |
| Q8 | `parity_rel(dir_r, dir_py, tol)` | rounding noise at 2e12 and near 0 passes; four real problems fail |
]

```bash
python data/build_rtb_sample.py                  # once: data/*.csv + data/expected/
python module-07/check.py                        # exercise.py as delivered: 0 of 8
python module-07/check.py module-07/solution.py  # the reference: 8 of 8
```

Then the audio lesson (`module-07/lesson/`), the walking quiz (`module-07/quiz/`), and the
real files in `opa-prop40/bsz-analysis/py/`.

---
class: inverse

# The Key Takeaways

### 1. Own your NULLs: read as text, strip, turn `""` into `None`, bind with `?`.

### 2. `executescript` for a trusted file of statements; sort every export by a key and print floats with `repr()`.

### 3. Check by aligning on keys, with a tolerance for numbers and none for counts, and let the exit code carry the verdict.

### 4. A twin mirrors its R file function by function, and parity is strict on names, rows, NAs and text, relative on numbers.

---

# Course Map

<table>
<tr><th>#</th><th>Module</th><th>Status</th></tr>
<tr><td>1</td><td><a href="../module-01/slides.html">Python for R Users</a></td><td>✓ done</td></tr>
<tr><td>2</td><td><a href="../module-02/slides.html">pandas basics</a></td><td>✓ done</td></tr>
<tr><td>3</td><td><a href="../module-03/slides.html">Joins, merges, group-by recipes</a></td><td>✓ done</td></tr>
<tr><td>4</td><td><a href="../module-04/slides.html">Regression and A/B tests with statsmodels</a></td><td>✓ done</td></tr>
<tr><td>5</td><td><a href="../module-05/slides.html">End-to-end interview scenario</a></td><td>✓ done</td></tr>
<tr><td>6</td><td><a href="../module-06/slides.html">Reading and Reviewing Someone Else's Python</a></td><td>✓ done</td></tr>
<tr><td>7</td><td>Loading, Running SQL and Checking a Reproduction <i>(just finished)</i></td><td>✓ done</td></tr>
</table>

---
class: center, middle, inverse

# Backup Slides

---
name: r-detail

# Backup: `sql_split_statements()` in `R/run_sql.R`

.small[
```r
sql_split_statements <- function(sql) {
  ch <- strsplit(sql, "", fixed = TRUE)[[1]]
  n <- length(ch); out <- character(); state <- "code"; start <- 1L; has_code <- FALSE; i <- 1L
  while (i <= n) {
    c1 <- ch[i]; c2 <- if (i < n) ch[i + 1L] else ""
    if (state == "code") {
      if (c1 == "-" && c2 == "-") { state <- "line"; i <- i + 1L }
      else if (c1 == "/" && c2 == "*") { state <- "block"; i <- i + 1L }
      else if (c1 == "'") { state <- "squote"; has_code <- TRUE }
      else if (c1 == ";") {
        if (has_code) out <- c(out, paste(ch[start:i], collapse = ""))
        start <- i + 1L; has_code <- FALSE
      } else if (!grepl("^\\s$", c1)) has_code <- TRUE
    } else if (state == "line") { if (c1 == "\n") state <- "code" }
    else if (state == "block") { if (c1 == "*" && c2 == "/") { state <- "code"; i <- i + 1L } }
    else if (state == "squote") { if (c1 == "'") state <- "code" }
    i <- i + 1L
  }
  ...
}
```
]

Condensed from the real file, which also handles double-quoted and backtick-quoted names.
A `;` ends a statement only in the `code` state, never inside a comment or a string.

---
name: parity-detail

# Backup: `compare_parity_frames()` in `helper-py-parity.R`

.small[
```r
for (col in names(r)) {
  a <- r[[col]]; b <- py[[col]]
  if (is.factor(a)) a <- as.character(a)
  if (inherits(a, "Date")) { a <- as.character(a); b <- as.character(b) }
  if (is.numeric(a) && !is.logical(a)) {
    b <- suppressWarnings(as.numeric(b))
    if (!identical(is.na(a), is.na(b))) {
      problems <- c(problems, paste0(col, ": missing values differ"))
      next
    }
    ok <- !is.na(a)
    same_inf <- is.infinite(a) & is.infinite(b) & sign(a) == sign(b)
    d <- ifelse(same_inf, 0, abs(a - b))[ok]
    rel <- d / pmax(1, abs(a[ok]))
    n_num <- n_num + sum(ok)
    if (length(d)) {
      max_abs <- max(max_abs, d); max_rel <- max(max_rel, rel)
      if (any(rel > PY_PARITY_TOL)) problems <- c(problems, sprintf("%s: max rel diff %.3g", col, max(rel)))
    }
  } else {
    if (!identical(as.character(a), as.character(b))) problems <- c(problems, paste0(col, ": text differs"))
  }
}
```
]

Column type comes from the R side: a column R holds as numbers is compared as numbers, anything else as text.

---
name: check-detail

# Backup: The Real Daily-Aggregate Comparison

.small[
```python
def cmp_aggregate(agg, key_rows, label):
    o = {r["date"]: r for r in agg}
    k, dup_rows, dup_differ = {}, 0, 0
    for r in key_rows:
        d = _iso(r["date"])
        if d in k:                       # a date listed twice in the key: compare the copies
            dup_rows += 1
            dup_differ += any(k[d][c] != r[c] for c in r if c != "date")
        else:
            k[d] = r
    common = sorted(set(o) & set(k))
    maxd, n_bad, dates_bad = 0.0, 0, 0
    for d in common:
        bad = int(o[d]["n_billionaires"]) != int(k[d]["n_billionaires"])   # counts: exact
        n_bad += bad
        for c in AGG_NUM:
            diff = abs(float(o[d][c]) - float(k[d][c]))
            maxd = max(maxd, diff)
            bad = bad or diff > TOL                                       # numbers: tolerance
        dates_bad += bad
    only_o, only_k = sorted(set(o) - set(k)), sorted(set(k) - set(o))
    ok = dates_bad == 0 and not only_k and dup_differ == 0               # extra dates in ours are fine
```
]