What the raw data needed, and what we did about it.
Open data arrives broken in small, specific ways: a street written 40 ways, a vendor spelled 23 ways, a code with no codebook. Every Nshipyard project starts with a repair log. This page collects them, method by method, so the work behind the numbers is visible to everyone, including the government teams who publish the data.
$15.1B
in phantom spend caught by quarantine before it reached the headline total
86.86%
of 6.45M parking tickets matched to a real address point
4,082
canonical vendors resolved, with every fuzzy merge logged
687
rows in the 158-to-140 neighbourhood crosswalk other projects join on
99.95%
of 525,343 address points matched to a parcel
Filter by method
Canada
Drug Shortages, Ranked
Health Product Shortages Canada monthly bulk extract, archived 2025-12-20 (drugshortagescanada.ca, now healthproductshortages.ca), reference date 2025-12-01
Source vintage: December 2025
What was wrong
The raw extract's first row is a byte-order-mark disclaimer, not data.
DINs arrive as floats or digit-stripped strings, uncomparable as keys.
The same company appears under near-identical strings (ALK ABELLO A/S vs ALK-ABELLO INC, ALLERGAN INC vs ALLERGAN PHARMA CO.).
Missing dates are encoded as 0001-01-01, which would corrupt every duration.
25 rows have an actual end date before the actual start date: impossible durations.
The fix
Detect and drop the BOM disclaimer row before parsing: 0 data rows lost.
Strip non-digit characters from DINs and zero-pad to 8 digits (report 97054 carries DIN 02245669).
Deterministic normalization to canonical company names; 16 merge groups logged with row counts in company_merges.json: 239 canonical companies.
Treat 0001-01-01 as missing, never as a real date; affected rows keep null durations.
Quarantine the 25 impossible-duration rows from duration math (row kept, duration null) and log them in quarantine.json. Medians computed over 20,347 resolved reports with computable duration.
Result: 24,377 shortage reports and 3,221 discontinuation reports cleaned. 239 canonical companies from 16 merge groups. 25 rows quarantined. 21 reports with no company name, 96 with no ATC code, 4 with no DIN: counted in totals, excluded from rankings.
− Raw open data+ Nshipyard cleaned
ALLERGAN PHARMA CO. (6 rows)
→
ALLERGAN INC (126 + 6 rows)
ALK-ABELLO INC (7 rows)
→
ALK ABELLO A/S (12 + 7 rows)
Report 172409 (end before start)
→
Duration null, row kept, logged in quarantine.json
ECCC NPRI bulk files for all years plus geolocations master, and GHGRP emissions-by-gas 2004 to present, via open.canada.ca
Source vintage: October 2026 (2024 reporting year)
What was wrong
The two registries share no published facility key. The GHGRP asks reporters to self-report their NPRI ID, but 426 of 1,879 facilities (22.7%) write the bare digit 0, a placeholder, not an ID. 22 leave it blank. 9 report IDs absent from the NPRI master.
The NPRI geolocations master carries 24,553 facility rows with bilingual column headers in cp1252 encoding, 381 blank facility names, and 9 duplicate NPRI IDs.
The NPRI quantity files hold 3 facility IDs absent from the geolocations master (temporary or retired IDs with no published crosswalk).
Source units are mixed: tonnes (the large majority), kilograms, grams, and grams of toxic equivalent.
144 near-matches look like matches but score below the similarity floor or fail the spatial check.
The fix
Anchor matching on the Facility NPRI ID field: non-zero, non-blank, present in the master. 1,422 anchored at high confidence.
Entity resolution for the 457 unanchored: uppercase, strip accents, drop legal suffixes (Ltd, Inc, Corp, Ltée and variants), strip punctuation, score against same-province NPRI facilities. 0.90 or higher with coordinates within 25 km becomes a resolved match: 124 recovered.
Temp-ID check: quantity-file IDs absent from the master resolve only when exactly one master facility shares the normalized name and province. 3 found, 0 resolved, all 3 quarantined.
Quarantine: 144 ambiguous near-matches logged in quarantine.json and excluded from every ranking.
Unit normalization to kilograms. Grams of toxic equivalent excluded from kg sums and counted separately.
Result: 1,546 of 1,879 GHGRP facilities linked (82.3%): 1,422 anchored, 124 resolved, 333 honestly unmatched, 144 quarantined. The crosswalk ships as crosswalk.csv: NPRI ID, GHGRP ID, match method, confidence, temp-ID aliases.
− Raw open data+ Nshipyard cleaned
NPRI ID: 0 (426 facilities)
→
Placeholder recognized: 124 recovered by name and address, the rest unmatched, never guessed
NPRI ID: 500303 (absent from master)
→
Treated as unanchored, resolved by name and coordinates or quarantined
Canadian Anti-Fraud Centre Fraud Reporting System Dataset, publisher Royal Canadian Mounted Police / Canadian Anti-Fraud Centre (open.canada.ca). Source file raw/cafc.csv: 75.5 MB, 350,361 rows, coverage 2021-01-01 to 2025-09-30.
Source vintage: October 2026 (dataset extracted 2025-10-01, retrieved 2026-10-10)
What was wrong
Dollar losses arrive as text with dollar signs and thousands separators ('$20,000.00'), not as numbers.
Every victim age band carries a leading Excel quote, e.g. "'30 - 39", which breaks grouping.
Province spellings are inconsistent: 'North West Territories' vs 'Northwest Territories', 'Newfoundland And Labrador' vs 'Newfoundland and Labrador'.
2,399 rows carry Country = United States with US states (California, Texas, Florida) in the province field, which has no matching StatCan population denominator.
The 39 thematic categories arrive as bare labels ('Spear Phishing', 'Bank Investigator') with no definitions shipped.
The fix
A guarded currency parser accepts only plain currency text and quarantines anything else: zero rows rejected out of 350,361, proving the field is machine-clean.
Leading quotes and whitespace are stripped before grouping, so "'60 - 69" becomes '60 - 69'.
A canonical spelling map is applied before any province grouping, so per-capita math never splits a province in two.
US-state entries stay in national totals but are excluded from per-capita math, keeping the province ranking Canada-only.
Scam-type meanings were decoded from the CAFC definitions PDF on the same catalogue record, so every category is a documented fraud type, not a guess.
Result: 350,361 rows kept, 0 rows quarantined from totals, 39 scam types ranked, 19 quarters of trends. The quarantine log in public/data/meta.json records the empty quarantine plus the province-exclusion rule, so the honest limits are auditable: 37.7% of reported losses carry no victim age and 19.9% carry no province, and all $2.687B comes from Victim rows.
− Raw open data+ Nshipyard cleaned
'60 - 69 (raw age band)
→
60 - 69 (leading quote stripped)
North West Territories
→
Northwest Territories (canonical spelling)
Newfoundland And Labrador
→
Newfoundland and Labrador (canonical spelling)
Province 'California' (652 rows, $15.7M) in per-capita ranking
→
Kept in national totals, excluded from per-capita math (no StatCan denominator)
$20,000.00 (text with $ and commas)
→
20000.00 (guarded currency parse, zero rejections in 350,361 rows)
Canada Energy Regulator Electricity Trade Summary XLSX (updated 2026-09-25) plus archived quarterly imports-exports CSV 1990Q1-2018Q2 (open.canada.ca); US Energy Information Administration Form 861 annual files 2010-2025; Bank of Canada FXAUSDCAD annual average; FRED DEXCAUS.
Source vintage: October 2026 (CER file updated 2026-09-25, retrieved 2026-10-10)
What was wrong
No published key links a CER export destination to a US electricity market: the CER names destinations 41 different ways ('Pennsylvania Jersey Maryland Power Pool', 'Minn / N. Dakota', 'New England-ISO') with no region mapping.
The archived quarterly CER file (1990-2018) and the current trade summary XLSX (2010-2026) overlap for 2010-2018 with different destination naming, and neither maps to EIA state codes.
Two rows in the archived CSV carry a blank destination, and four province-to-region pairings (e.g. West to PJM) are one-off scheduling noise totaling under 1 TWh.
Prices arrive in CAD and US prices in USD with no shared currency series: the BoC annual average starts in 2017 and the FRED daily series uses a different column name.
The fix
Hand-curated corridor crosswalk (data/crosswalk.csv, 55 rows): 41 CER destination strings mapped to five market regions (US West, US Midwest, PJM, NYISO, ISO-NE), each region's price a sales-weighted average of its states' EIA-861 retail prices; 14 origin provinces grouped into CER West and East.
Month alignment: CER monthly West/East prices averaged to annual, EIA-861 annual state prices used as published, currency converted with the annual average CAD-per-USD rate (BoC 2017-2025, FRED DEXCAUS for 1990-2016, BoC monthly for 2026).
Quarantine: the 2 blank-destination rows are excluded from every aggregate; the 4 noise pairings under 1 TWh total are dropped and documented.
Validation: the pipeline reproduces the CER's published 2024 anchors exactly, $125.40 West and $58.87 East per MWh, to the cent.
Result: 96 margin rows (6 corridors x 16 years, 2010-2025), a 1990-2026 West/East inversion series, and the 55-row crosswalk, all downloadable as CSV. The build establishes what neither government publishes: the full spread between US retail prices and Canadian export prices, $59.6B cumulative from 2010 to 2025.
− Raw open data+ Nshipyard cleaned
'Pennsylvania Jersey Maryland Power Pool' (raw CER destination)
→
Market region PJM, grid operator PJM Interconnection
'Minn / N. Dakota' (raw CER destination)
→
Market region US Midwest
'New England-ISO' (raw CER destination)
→
Market region ISO-NE, grid operator ISO New England
2024 West price unverifiable against the raw files
→
Pipeline output $125.40, matching the CER's published anchor to the cent
Ontario MECP 'Greenhouse Gas Emissions Reporting By Facility' 2010-2024 (Reg 390/18, files.ontario.ca): 4,231 rows, 480 facility IDs, 420 reporting in 2024. ECCC federal GHGRP 'Emissions by Gas' 2004-2024 (Ontario, open.canada.ca catalogue API): 4,606 Ontario rows, 491 facility IDs, 418 reporting in 2024. Ontario Ministry of Energy and Mines EWRB large buildings 2018-2024: 30,693 rows, 9,250 buildings. Vintage: 2024 reporting year; retrieved 2026-10-10.
Source vintage: October 2026 (2024 reporting year; files retrieved 2026-10-10)
What was wrong
The two emissions programs name the same facilities differently and publish no shared facility key: 133 of 480 Ontario facility IDs changed names across years, and the two registries recorded renames in different years.
Company names collide across cities: Canada Brick and Hanson Brick both report plants in Aldershot, and two Ingredion plants sit in Cardinal and Port Colborne, so a naive name match would fuse different facilities.
Site suffixes equal city names: 'BUNGE CANADA - HAMILTON' and 'Hamilton' share only the city token, which cannot serve as a plant identifier. Two federal facilities in Woodstock share the name 'Woodstock Plant'.
The EWRB files publish no building names and no street addresses, only city and a 3-character postal code, so no record-level join is possible; inside, 3,002 rows (9.8%) report zero intensities and 508 report absurd GHG intensity above 1,000 kgCO2e/m2 (max 2,861,043, median 25.9).
Ontario's published total includes biomass CO2 and the federal total excludes it, so raw totals are not comparable.
The fix
Anchor matching: uppercase, strip accents, drop legal suffixes and punctuation, then require exact match on the normalized name plus the same normalized city. 458 of 513 canonical facilities anchored (89.3%), all name histories preserved as variants.
Merge-trap rules: a site suffix that equals the city can never anchor a merge, and conflicting site suffixes veto the merge. Anchored full-name matches are assigned before sub-form matches, so the Woodstock record paired with G10114 (Federal White Cement, 0.1% apart on emissions) instead of the Lafarge plant.
The fuzzy tier (difflib at 0.90 or above) produced zero merges after the strict rules; near-misses were left unmatched rather than guessed.
EWRB quarantine: all 30,693 rows quarantined with reason codes, plus city-level manufacturing-intensity context for 49 cities carried by 96 facilities, flagged ecological.
Biomass adjustment: the 2024 join subtracts biomass CO2 from the Ontario total before comparing to the federal total.
Result: 513 canonical facilities: 458 anchored, 0 resolved, 55 unmatched (22 Ontario-only, 33 federal-only, nearly all pre-2024 reporters). The agreement join over 416 pairs shows a largest gap of 3,392 t at Greenstone Mine (+2.2%), which independently validates the anchors. 128 year-over-year anomalies flagged. Ships as spine.json/spine.csv with per-program name histories and annual series.
− Raw open data+ Nshipyard cleaned
'Dofasco Hamilton' vs 'ArcelorMittal Dofasco Hamilton'
→
One anchored facility (ONF-000005), both name histories kept
'Canada Brick - Aldershot' vs 'Hanson Brick - Aldershot'
→
Correctly rejected, never merged (city-token rule)
'Ingredion Canada Corporation' (Cardinal) vs 'Ingredion Canada Incorporated - Port Colborne Plant'
→
Correctly rejected, never merged (site-suffix veto)
'Woodstock Plant' (ambiguous, two federal candidates)
→
Paired with G10114 (Federal White Cement), emissions agree to 0.1%
Thunder Bay Operations: 1,384.3 kt (Ontario) vs 202.5 kt (federal)
CMS Medicare Part D Spending by Drug 2024; Ontario Drug Benefit e-formulary (retrieved 2026-10-10); RxNorm; Bank of Canada FX
Source vintage: October 2026 (US: 2024 data published June 25, 2026; Canada: retrieved Oct 10, 2026)
What was wrong
CMS repeats every drug once per manufacturer plus an Overall rollup; summing all rows double-counts dollars.
Generic names are truncated mid-word ('bictegrav + emtricit + tenofov ala') and carry strength suffixes, so brand and generic rows for one molecule never match without repair.
The CMS file carries no molecule identity; identity must come from RxNorm (RxNorm concept unique identifiers, RxCUIs) per normalized name, and low-confidence matches must not be guessed.
Canadian prices live in a different unit system (per-DIN formulary rows, CAD) with no shared key to CMS molecules, and the PMPRB publishes no machine-readable molecule-level price file at all (verified 2026-10-10).
CMS dosage units are undefined per drug: comparing without resolving them produced nonsense ratios (observed: 392.9x for ustekinumab from a per-vial vs per-mL mixup).
The fix
Keep Overall rows only: 3,625 unique drugs for 2024, zero double-counted dollars.
Normalize to molecule keys and expand truncations with a documented 19-token map; 447 of 500 molecules resolved to an RxCUI with ATC (the WHO Anatomical Therapeutic Chemical drug-classification).
Query the ODB formulary per molecule with salt-stripped name variants, matching only when every ingredient token matches at word boundaries with identical ingredient counts; enforce form-family consistency (injectables vs injectables, inhaled vs inhaled).
Use the lowest per-unit Drug Benefit Price across surviving rows as the Canadian comparator: the floor of what Ontario's public plan pays.
Quarantine 16 molecules with documented reasons instead of ranking them on mismatched units; convert currency at the Bank of Canada 2024 daily average, 1 USD = 1.3698 CAD.
Result: 500 molecules attempted; 210 with a Canadian formulary price; 16 quarantined; 194 ranked. Median US-to-Canada ratio 4.4x; max 66.7x (rivaroxaban); $125.5B of 2024 Part D spending covered. US amounts are gross of confidential manufacturer rebates, so every ratio is an upper bound.
− Raw open data+ Nshipyard cleaned
ustekinumab: 392.9x (per-vial US vs per-mL Canada)
The contracts file republishes every contract quarterly with contract_value as a running cumulative total; summing rows overcounts the true total by 2.56x ($1.185T instead of $463.2B).
Vendor names are entered by 100+ reporting offices with no canonical key: 211,090 distinct raw strings for roughly 140,000 real vendors. One vendor appeared under 65 spellings sharing the same first eight letters.
CanadaBuys changed its column schema in June 2023 and restructured it again in March-April 2026; column names are bilingual hyphenated pairs (supplierName-nomFournisseur) with no shipped crosswalk.
Corporations Canada split the Federal Corporations bulk file into four subsets in two languages in April 2026, with no merged file and no key documentation.
458 rows carried negative amounts, phone-number-like vendor fields, out-of-range years, or blank vendors.
The fix
Contract-level dedup on (reporting office, procurement ID), keeping the row with the maximum cumulative value: procurement IDs collide across departments, so the office is part of the key.
Vendor normalization (uppercase, legal suffixes stripped, punctuation removed, whitespace collapsed), exact dedup on normalized keys (148,823), then fuzzy union-find at difflib ratio 0.93+ within identical-first-token blocks: 5,706 merge groups, every merge logged with ratio and variants.
Hand-built 53-row CanadaBuys crosswalk mapping legacy columns to restructured columns, bilingual pairs harmonized to single English keys.
Merged the 8 corporation files into one table keyed on corporation number: 1,572,149 unique corporations, active and inactive unioned.
Quarantine rules caught 458 rows before any aggregate: 155 negative amounts, 137 phone-number-like vendors, 89 out-of-range years, 77 blank vendors.
Result: 140,525 canonical vendors; 11,816 matched to a federal corporation (8.4% of vendors, 34.5% of dollars). $159.9B of $463.2B is provably Canadian-registered; the remaining $303.3B is labelled unknown, never guessed, because no free bulk source of provincial registrations or ultimate corporate parents exists in Canada.
− Raw open data+ Nshipyard cleaned
contract_value summed naively = $1.185T
→
contract-level dedup = $463.2B across 1,099,940 distinct contracts
'BGIS GLOBAL INTEGRATED SOLUTIONS CANADA' vs 'BGIS GLOBAL SOLUTIONS LTD' vs 3 more spellings, counted separately
→
one canonical vendor, $19.2B total, merge logged
8 corporation files, no shared key
→
corporations_merged.csv, 1,572,149 rows, one row per corporation number
CanadaBuys pre-2023 and post-2023 files unjoinable
→
53-row crosswalk, 16.8% conservative join rate to TBS contracts
BLS Job Openings and Labor Turnover Survey via FRED (monthly, seasonally adjusted); CES employment via FRED (PAYEMS, USGOVT); StatCan table 14-10-0400-01, Job Vacancy and Wage Survey (quarterly, seasonally adjusted)
Source vintage: October 2026 (US through August 2026, Canada through 2026Q2)
What was wrong
JOLTS is monthly, JVWS is quarterly: a side-by-side of raw releases compares a month against a quarter.
The published US total includes government (about 731,000 openings in August 2026); the two surveys draw the public-sector boundary differently, so the comparison targets the business labour market.
Both agencies define the vacancy rate as openings / (employment + openings) x 100, but the published US rate bakes in government employment, so averaging published monthly rates after a scope change compounds the error.
JOLTS publishes 'trade, transportation, and utilities' as one industry while JVWS splits it into four sectors; JOLTS 'private education and health services' is private-only while JVWS includes the public sector.
The fix
Each JOLTS and CES quarter is the mean of its three months; JVWS needed no aggregation.
Scope cut: US openings minus JOLTS government openings (JTS9000JOL), US employment minus CES government employment (USGOVT), quarantined from every harmonized aggregate.
The rate is recomputed from quarterly levels after the cut, never by averaging published rates.
Ten-industry NAICS (North American Industry Classification System) crosswalk: where one JOLTS industry spans several JVWS sectors, the Canadian side is composited by summing vacancies and payroll employees first, then recomputing the rate, with a bilingual note on every imperfect match.
Result: 44 quarters (2015Q1 to 2026Q2) on identical definitions; 436 sector-quarter rows across 10 NAICS pairs. The US is tighter in all 44 quarters; the gap peaked at 2.46 points in 2021Q2. In 2026Q2: US 4.67% ex-government vs Canada 2.80%.
− Raw open data+ Nshipyard cleaned
JOLTS August 2026: 7,079K openings, 4.3% beside JVWS 2026Q2: 510,220 vacancies, 2.8% (a month against a quarter, government in on one side)
→
2026Q2: US 4.67% ex-government vs Canada 2.80%, gap 1.87 points (same quarter, same formula, government out)
US published total 2026Q2 4.47% (government included)
→
US harmonized 2026Q2 4.67% (government excluded); the scope cut moves the US rate up 0.2 points
JOLTS 'trade, transportation, and utilities' as one industry against four JVWS sectors
→
10 NAICS pairs; composite Canadian sectors summed then re-rated, every imperfect match with a bilingual note
Statistics Canada table 14-10-0443 (Job Vacancy and Wage Survey, quarterly, unadjusted), 2015Q1 to 2026Q2, via keyless StatCan endpoints
Source vintage: October 2026
What was wrong
67,508,672 raw rows mix the current series with its archived predecessor (table 14-10-0328, 2015Q1 to 2023Q3): 35 overlapping quarters would double-count.
322,588 cells are suppressed or flagged unreliable by StatCan (too few respondents to publish).
Occupation labels carry trailing classification codes (Health occupations [0]) that break name matching.
The fix
Current-series-wins dedup over the archived predecessor: the 35 overlapping quarters contribute zero observations from the old table.
Quarantine every suppressed or unreliable cell before aggregation: 322,588 cells excluded, logged in quarantine counts.
Strip trailing classification codes from occupation names after mapping, so labels read as plain names in both languages.
Result: 393 unit-group occupations with vacancies, duration bands, and offered wages for 2015Q1 to 2026Q2. The computed 2022Q4 long-term share (39.5%) matches StatCan's published figure exactly; 2026Q2 national vacancies (548,400 unadjusted) sit plausibly above the published 510,200 seasonally adjusted.
− Raw open data+ Nshipyard cleaned
Health occupations [0]
→
Health occupations
35 overlapping quarters counted twice
→
Current series wins: zero observations from the archived table
Suppressed cells in averages
→
322,588 suppressed or unreliable cells quarantined
Ontario Health Time Spent in Emergency Departments monthly per-site tables (HTML, no bulk file), scraped live October 2026, plus CIHI NACRS provincial context
Source vintage: October 2026 (August 2026 latest month)
What was wrong
Ontario Health publishes no bulk file: monthly per-site wait tables exist only as HTML pages.
Hospital names change across months, which would split one hospital's history into two rows.
A December 2025 reporting refresh added 43 sites, breaking before/after mover comparisons.
153 scraped cells were malformed or missing.
The fix
Scrape the monthly site tables into one ranked, downloadable time series: 159 hospital ERs, 4 wait-time measures, 13 months (2025-08 to 2026-08), 8,268 rows.
Key every row on stable Ontario Health site IDs, so renames never split a hospital's history.
Restrict mover comparisons to the post-refresh window (Feb to Aug 2026), where both months use the same site list.
Quarantine the 153 malformed or missing cells: 8,115 clean rows shipped in er_waits_ranked.csv.
Geocode all 159 sites via Nominatim with tiered fallbacks, so every hospital gets a nearest-ER comparison (straight-line distances).
Result: 8,115 clean rows across 159 ERs and 13 months. Biggest movers, Feb to Aug 2026: Ottawa Hospital General Site got slower by 1.9 hours (1.9 to 3.8); Health Sciences North-Laurentian in Sudbury got faster by 0.6 hours (2.6 to 2.0). Admitted stays: Haliburton Highlands averages 45.1 hours in the ER before a bed, against a provincial average of 18.5 hours.
− Raw open data+ Nshipyard cleaned
HTML tables, one page per month
→
One ranked time series: er_waits_ranked.csv, 8,115 rows
City of Toronto open data: Bids Awarded Contracts, Non-Competitive Contracts, Consulting Services Expenditures
Source vintage: October 2026
What was wrong
One vendor, 23 spellings. D Crupi & Sons Limited appears 23 ways in the raw files.
Typos in the source: “Black & McDdnald Limited”, “WCAG Cpmpliance Inc.”
Three award amounts are bare 10-digit phone numbers. No commodity codes on awards.
The fix
Normalize every vendor name: uppercase, & to AND, strip punctuation, drop legal suffixes (LTD, INC), collapse whitespace. Exact dedup on the normalized keys.
Fuzzy merge with union-find at difflib ratio 0.93 or higher and identical first token. Every merge logged to vendor_merges.json. Canonical name is the most frequent raw variant.
Quarantine: a ^\d{10}$ regex keeps phone-number amounts out of every aggregate.
Result: 4,240 normalized keys resolved to 4,082 canonical vendors. 174 fuzzy merges logged. Without quarantine, the headline spend was overstated by $15.1B. Final headline: $21.5B across 13,084 award records.
City of Toronto open data: Neighbourhoods (158-model), Neighbourhoods historical (140), City Wards (25)
Source vintage: October 2026
What was wrong
No official 140-to-158 neighbourhood crosswalk existed. Analysts hand-derived it, usually wrong at the edges.
Old names are stale: “Mimico (includes Humber Bay Shores) (17)”.
64 of 158 neighbourhoods touch more than one ward, so a single parent ward is wrong.
The fix
Pairwise polygon intersection of every 158 polygon against every 140 polygon, recording the overlap as a share of each side.
Primary parent is the 140 polygon with the largest share. Sub-1% overlaps kept as slivers and flagged, not silently dropped.
Ward crosswalk records every share; the primary is the majority share.
Result: 687-row 158-to-140 crosswalk. 124 one-to-one, 16 old neighbourhoods split into 34 new ones, every primary pair covers at least 99.34% of the child. This crosswalk is the join key the other projects use.
− Raw open data+ Nshipyard cleaned
“Mimico (includes Humber Bay Shores) (17)”
→
160 Mimico-Queensway (77.64%) + 161 Humber Bay Shores (22.36%)
City of Toronto open data: Property Boundaries; Toronto One Address Repository address points
Source vintage: October 2026
What was wrong
Multi-row parcels need one spine row. STATEDAREA arrives as text (“18544.72 sq.m”).
Condo buildings put dozens of address points inside one parcel. 258 address points fall outside every parcel.
The fix
Exact-key dedup on integer PARCELID: majority-vote feature type, first-seen area, per-row lineage of source OBJECTIDs.
Point-in-polygon spatial join of address points to parcels on a bounding-box grid index. Numeric area via shoelace on an equirectangular projection centred on Toronto.
Parcels with 10 or more addresses flagged stacked. Ward from matched addresses, else area-weighted centroid.
City of Toronto open data: 311 Service Requests, Customer Initiated (2022-2026)
Source vintage: October 2026
What was wrong
Leading whitespace baked into 171,986 rows: “ Forestry & Recreation” 98,077 times.
A double space inside one request type. 43 rows with ward literally “Unknown”. 8 blank request types.
Renames break year-over-year history: the same service under a new name looks like a new service.
The fix
Whitespace-only normalization: strip plus collapse, no lowercasing. Exact-match counting on normalized (division, section, type) triples.
Stable codes (T311-D/S/R) so renames stop breaking history: the code persists while the label changes.
Ward parsed from “Name (NN)” strings; non-matching becomes UNK.
Result: 952 request types, 9 divisions, 26 ward rows from 2,225,151 requests. The cleaner shows its limit honestly: “Not PIcked Up” vs “Not Picked Up” stays two rows, because the normalizer does not lowercase.
City of Toronto open data: Watermains (distribution 46,923 segments; transmission 2,491 segments)
Source vintage: October 2026
What was wrong
No codebook ships with the data. Pipes arrive as bare codes: CI, DIP, DICL, CONC vs CONP.
830 segments marked UNK, 21 literally “None”. 731 segments (1.5%) with blank construction year. Diameter units unlabeled. Type codes 0/1/2 undefined. No ward column, no lead flag.
The fix
Hand-authored lookup tables: 18 material codes, 3 type codes, diameters, with “Unlisted code” fallbacks.
Lead rule: built before 1955 means lead-era. Blanks become unknown_vintage, never a guess.
Ward by midpoint-in-polygon on the 25 ward boundaries.
Result: 49,414 segments decoded, zero duplicates. 16,949 lead-era segments (2,014.9 km, 91.6% cast iron). 31,734 modern, 731 unknown vintage.
StatCan NHPI 18-10-0205-01, construction union wage rates 18-10-0139-01 / 18-10-0140-01, IPPI 18-10-0266-01, BCPI 18-10-0289-01
Source vintage: October 2026
What was wrong
The published analysis cited table 18-10-0205-02 for index levels. That table holds only month-over-month and year-over-year percent changes from February 1981: no January 1981 observation, no levels at all.
Four driver tables, four base years: NHPI on December 2016=100, BCPI on 2023=100, archived NHPI vintages on 1981=100 and 1992=100.
Frequencies are mixed: NHPI, IPPI, and wage rates are monthly. BCPI is quarterly. Productivity is annual. Toronto CMA rows sit beside City of Toronto and CMA-part rows with near-identical names.
No open series exists at all for development charges, code-compliance costs, or Toronto-residential productivity.
The fix
Source correction: NHPI index levels now cite table 18-10-0205-01, which starts in January 1981. Table -02 is cited only where a percent-change statistic is actually used.
Rebased every series to 1981=100 for presentation. Analysis runs on log changes where base years cancel out.
Fixed timing rule: January value for monthly series, Q1 for BCPI (labelled Q1, never January), annual drivers lagged one year against the January panel.
Joined strictly on the Toronto CMA geographic identifier. City of Toronto and CMA-part rows excluded. National IPPI series labelled as national input-price shocks, never as Toronto builder costs.
Gaps called out in the open: fee, code-cost, and productivity drivers carry a 'no open series' flag instead of being modelled from proxies.
Result: Nine driver series, 1981-2026, one observation per year, all on 1981=100. Union wages rose 4.55x, almost matching the 4.64x construction-cost rise. Materials sit below: fabricated metal 4.01x, concrete 3.66x, lumber 3.11x.
US Medicare Part D Drug Spending Dashboard (2024); Ontario Drug Benefit Formulary
Source vintage: October 2026
What was wrong
US prices are Medicare Part D 2024 amounts, gross of confidential manufacturer rebates, so every published ratio is an upper bound on the true gap.
No machine-readable molecule-level price file exists in Canada. The PMPRB publishes no such file.
Dosage units do not always match across the border: 16 molecules could not be compared on identical units.
The fix
One molecule per row: each US molecule matched to its Canadian counterpart with dosage units aligned first.
Canada uses the Ontario public-plan formulary price, labelled as Ontario, never as a national list price.
16 molecules excluded from the ranking over unmatched dosage units, each one documented, not silently dropped.
Every ratio published as an upper bound, with the rebate caveat on the page.
Result: 194 matched molecules. The median molecule costs 4.4x more in the US. Widest gap: rivaroxaban at 66.7x ($17.30 vs $0.26 per tablet). The 194 molecules cover $125.5B of 2024 Medicare Part D spending.
Ontario Health: Emergency Department wait time tables, monthly per-site releases
Source vintage: October 2026
What was wrong
Ontario Health publishes per-site monthly tables but never ranks them.
43 sites joined reporting with the December 2025 refresh, changing the comparison set mid-series.
Monthly averages only. No open source carries visit-level timestamps, so the worst hour to arrive is unanswerable.
Ontario Health's own warning: data exported after December 2025 should not be compared to earlier exports.
The fix
Scraped the per-site tables into one ranked, downloadable time series: 159 ERs, 13 months, 4 wait measures.
Movers computed over 6 and 12 months on a consistent site set.
The December 2025 comparability break is documented on the page, not smoothed over.
Result: 159 hospital ERs ranked on four measures (first assessment, low-urgency stay, high-urgency stay, admitted stay) from August 2025 to August 2026, with movers and a nearest-ER lookup.
Canadian Anti-Fraud Centre: fraud reports, January 2021 to September 2025
Source vintage: September 2025
What was wrong
37.7% of reported losses carry no victim age. 19.9% carry no province.
Losses are self-reported, never independently verified.
Geographic grain is province only. Data stops September 30, 2025.
The fix
Scam types normalized across 350,361 reports. Age and province views published as floors, never totals.
1,829 precomputed slices: scam type by province, by age band, by quarter.
Every chart labelled with what is missing, not just what is shown.
Result: $2.687B in reported losses. Investment fraud is 51.5% ($1.382B). Spear phishing averages $102,911 per victim. Job scams are the fastest growing: quarterly losses up 7.5x since Q1 2021.
− Raw open data+ Nshipyard cleaned
Losses with no age or province: dropped silently
→
Published as floors, with the missing share labelled
350,361 reports, no ranking
→
Scam types ranked by dollars, per-victim, and growth
Canada Energy Regulator: electricity exports (2010-2025); US Energy Information Administration: state retail electricity prices
Source vintage: October 2026
What was wrong
Neither government publishes the export margin. The spread has to be built.
The archived province-by-destination export file ends in 2018Q2.
US retail prices include distribution, so a naive margin overstates the wholesale spread.
The fix
Six export corridors built from CER regional prices joined to EIA state prices.
2019-2025 corridor prices use CER regional West/East prices. The pipeline reproduces the CER's published 2024 anchors exactly ($125.40 CAD/MWh West, $58.87 CAD/MWh East).
The distribution caveat is stated on the page: the margin overstates the pure wholesale spread.
Result: $59.6B cumulative margin across six corridors, 2010-2025. Western export prices moved above eastern in 2018 and stayed there, peaking at +$51.54/MWh in 2023.
− Raw open data+ Nshipyard cleaned
Export price and US price: two solitudes
→
Six corridors, one margin series each
Province-by-destination file ends 2018Q2
→
CER regional prices carry 2019-2025, validated to the 2024 anchors
Methods, code, and versioned releases live in each project's repository.
For government
If you publish the data, talk to the people who clean it.
Every fix on this page is documented in the open: the method, the code, the versioned releases. If your team publishes open data and wants to discuss a fix, a crosswalk, or this kind of cleaning for your own datasets, reach out.