1# F1-conf-04 — Reconciling the property-sales export to its control totals23## Question4How many sale records in `data/sales.csv` (simulated data) can be analysed, what are the sum and mean of `price_cad` and the median price per m² over those records, and how do the record count and price sum reconcile to the provider's control totals in `data/metadata.json`?56## Data and exclusions7- `sales.csv` has 1,200 data lines plus a header. The provider's control total is also 1,200 records, with a price sum of 471,287,000 CAD.8- **Corrupted block:** file lines 701–736 (36 data lines) are garbled bytes. They have the wrong number of fields (between 1 and 12), malformed `sale_id`s and non-numeric content. Their fields cannot be read unambiguously, so all 36 were excluded. None were reconstructed or imputed. The readable records are missing sale_ids S00700–S00735, a contiguous run of 36. This is consistent with the corrupted block, but no id was assigned to any garbled line.9- **Thousands separators:** 25 readable records give `price_cad` as a quoted CSV string with a comma separator (e.g. `"280,000"`). Standard CSV quoting makes these unambiguous, so they were converted to integers and kept.10- No other checks removed any records. These were: `sale_id` format and uniqueness, municipality in the control list, a valid ISO date (all dates fall in 2021-01-01 to 2022-12-31), and positive price and area. The ledger is in `out/exclusions.csv`: 1,200 → 1,164.11- Reconciliation by municipality (control minus readable): Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7, Levis 3. These add up to 36, which matches the corrupted block exactly.1213## Method14The file was parsed with Python's `csv` module, which handles quoted fields. Validation rules were applied in sequence. Statistics were computed on the records that passed every rule: count, price sum, arithmetic mean of price, and median of `price_cad / area_m2`. The implied price sum of the unreadable records is the control sum minus the valid sum. The analysis is deterministic and uses no randomness. Run `~/wp29/venv/bin/python out/analysis.py` to regenerate everything.1516## Results17| quantity | value |18|---|---|19| control records | 1,200 |20| valid / analysed records | 1,164 |21| unreadable records | 36 |22| control price sum | 471,287,000 CAD |23| valid price sum | 456,370,000 CAD |24| implied price sum of unreadable records | 14,917,000 CAD |25| mean price (valid) | 392,070.45 CAD |26| median price per m² (valid) | 2,984.94 CAD/m² |27| feasibility | partial |2829The counts reconcile exactly: 1,164 readable + 36 unreadable = 1,200. The price sum reconciles only by construction: the 14,917,000 CAD gap is attributed to the 36 unreadable records. That implies a mean of about 414,361 CAD per unreadable record, which is plausible next to the valid mean.3031## Uncertainty32The analysis covers all readable records; it is not a sample. There is therefore no sampling uncertainty to report for these descriptive statistics. The real uncertainty is about the missing 3% of records. Their total price is known only implicitly from the control sum. Their individual prices and areas are unknown, so the effect of their absence on the median price per m² cannot be determined. Because their implied mean (≈414k) is above the valid mean, the full-table mean price would be slightly higher: 471,287,000 / 1,200 ≈ 392,739 CAD.3334## Limitations and open issues35- **Feasibility is "partial".** 36 of the 1,200 control records could not be analysed because their export rows are corrupted. They are excluded, not reconstructed.36- The implied unreadable sum assumes the control total and the export describe the same table and that the readable records contain no export errors. This cannot be checked independently.37- The data are synthetic, so no substantive conclusions about real property markets should be drawn.38