1# F1-conf-04 — Reconciling the property-sales export to control totals23## Question4How many sale records in `data/sales.csv` (simulated export) can be analysed? For those records, what are the price sum, the mean price and the median price per m²? And how do the record count and the price sum reconcile to the provider's control totals in `data/metadata.json`?56## Data and exclusions7- `sales.csv` has 1200 data lines plus a header. The provider's control count is also 1200 records, with a control price sum of 471,287,000 CAD.8- **Garbled lines:** 36 data lines (file lines 701–736) are corrupted. They contain random characters, the wrong number of fields and an invalid `sale_id`. The readable rows are missing exactly the IDs S00700–S00735, which makes these 36 lines the records that are absent. The fields in these lines cannot be read unambiguously, so they are **excluded and were not reconstructed or imputed**.9- **Formatted prices:** 25 readable records have `price_cad` exported as a quoted string with comma thousands separators (e.g. `"280,000"`). Each value matches the strict pattern `d{1,3}(,ddd)+`, so the separators were removed and the records were kept.10- **Other checks:** these found no problems. Every municipality name belongs to the control list, every date is a valid ISO date (2021-01-01 to 2022-12-31), every area is positive and numeric, and there are no duplicate `sale_id`s.11- **Exclusion ledger** (`out/exclusions.csv`):12 - Step 1 (structural parse): 1200 → 1164, removing 36.13 - Step 2 (field validity): 1164 → 1164.14 - Step 3 (duplicate IDs): 1164 → 1164.15- **Municipality shortfalls:** the per-municipality shortfalls against the control counts sum to 36. They are Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7 and Levis 3.1617## Method18- I parsed the file with Python's `csv` module, which handles quoted fields correctly, and validated it field by field.19- Statistics are computed over the 1164 valid records.20- The reconciliation compares the valid count and price sum with the control totals. The unreadable price sum is *implied* only, as the control sum minus the valid sum.21- `out/analysis.py` regenerates every output deterministically. The bootstrap uses the fixed seed 20261030.2223## Results24| quantity | value |25|---|---|26| control records | 1200 |27| valid / analysed records | 1164 |28| unreadable records | 36 |29| control price sum | 471,287,000 CAD |30| valid price sum | 456,370,000 CAD |31| implied sum of unreadable records | 14,917,000 CAD (mean ≈ 414,361 CAD per record) |32| mean price (valid) | 392,070.45 CAD |33| median price per m² (valid) | 2,984.94 CAD/m² |3435- The record count reconciles exactly: 1164 + 36 = 1200.36- The price sum reconciles only by construction, because the 14,917,000 CAD gap is attributed to the 36 unreadable records. That attribution cannot be checked, since those records are unreadable.37- For reference, the mean over all 1200 control records is 471,287,000 / 1200 = 392,739.17 CAD. This comes from the control totals, not from the records.3839**Feasibility: partial.** 36 of the 1200 control records could not be analysed because their export lines are corrupted.4041## Uncertainty42- I ran a nonparametric bootstrap over the valid records (B = 2000, seed 20261030), which gives these 95% percentile intervals:43 - mean price: [382,427; 402,005] CAD;44 - median price per m²: [2,937.4; 3,028.6] CAD/m².45- These intervals reflect sampling variability only. They do not cover the bias that could come from losing the 36 records.46- The implied mean of the lost records (≈ 414k CAD) is above the valid mean. This suggests the lost records are not a random sample by price, although this is a single aggregate comparison.4748## Limitations and open issues49- The 36 unreadable records form one contiguous block (S00700–S00735). The estimates therefore describe the 1164 readable records, not the full table. The median price per m² for the lost records cannot be bounded, because their areas are unknown.50- The price-sum reconciliation depends on the provider's control sum being correct. The valid records cannot be checked independently against it, because the gap absorbs any discrepancy.51- The thousands-separator prices were interpreted as integer CAD amounts. This interpretation is consistent with the plain prices in the file, which are all whole thousands.52- The data are simulated.53