1# F1-conf-04 — Reconciling a property-sales export to its control totals23## Question4How many sale records in the provider's export (`data/sales.csv`) can be analysed? What are the sum and mean of `price_cad` and the median price per m² over those records? How do the record count and price sum compare with the provider's control totals (`data/metadata.json`: 1,200 records, 471,287,000 CAD)?56## Data and exclusions7- `sales.csv` has a header and **1,200 data lines**. That matches the control count, but not every line holds a readable record.8- **36 lines (file lines 701–736) are garbled.** They contain random characters, the wrong number of fields, and invalid IDs, dates and numbers. The sale_ids missing from the readable data are exactly **S00700–S00735**. I excluded these lines and did not reconstruct or impute them, as the task's rules require.9- **25 records write the price with a thousands separator inside CSV quotes** (e.g. `"280,000"`). Standard CSV quoting makes these values unambiguous, so I removed the comma, parsed them as integer CAD and kept the records.10- There are no duplicate sale_ids, no unknown municipalities, and no invalid dates (all fall between 2021-01-01 and 2022-12-31). All areas are positive (39.8–335.9 m²).11- Exclusion ledger (`out/exclusions.csv`): 1,200 lines read → 1,164 after dropping unparseable lines (36 removed) → 1,164 after the duplicate-ID check (0 removed).1213## Method14`out/analysis.py` reads each line with Python's `csv` module. It keeps a record only if all of these hold:15- the line has exactly 5 fields;16- `sale_id` matches `S` followed by 5 digits;17- the municipality is one of the five in the control totals;18- the date is a valid ISO date;19- price and area are numeric (a quoted thousands-separated price is accepted);20- area is greater than 0.2122The script then computes the statistics on the analytic set. The reconciliation compares the analytic count and sum with the control totals. The price sum of the unreadable records is *implied* as control minus analysable; it cannot be observed directly.2324## Results25| quantity | value |26|---|---|27| control records | 1,200 |28| analysable (valid) records | 1,164 |29| unreadable records | 36 |30| control price sum | 471,287,000 CAD |31| analysable price sum | 456,370,000 CAD |32| implied sum of unreadable records | 14,917,000 CAD (implied mean 414,361 CAD per record) |33| mean price (analysable) | 392,070.45 CAD |34| median price per m² (analysable) | 2,984.94 CAD/m² |3536- **Count by municipality:** the gaps between control and analysable counts are Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7 and Levis 3. They sum to 36, so the whole count gap is explained by the 36 garbled lines.37- **Feasibility: partial.** 36 of the 1,200 control records could not be analysed because their export lines are corrupted.3839## Uncertainty40The control totals cover a full table, so the counts and sums are descriptive, with no sampling error. As a guide only, treating the 1,164 records as iid:41- the mean price has a standard error of 5,282 CAD, giving a 95% CI of 381,719–402,422;42- a nonparametric bootstrap of the median price per m² (2,000 resamples, seed 20261030) gives a 95% CI of 2,937–3,029 CAD/m².4344The larger uncertainty is not sampling but the missing 36 records. Their implied mean price (≈414k) is above the analysable mean (≈392k), so the analysable mean and median may be slightly biased relative to the full table. Including those records at any price consistent with the control sum would move the full-table mean to 471,287,000 / 1,200 = 392,739 CAD. The full-table median price per m² cannot be determined because the areas of the missing records are unknown.4546## Limitations and open issues47- The 36 garbled records cannot be recovered from the files provided; the provider would need to re-export lines S00700–S00735.48- The 14,917,000 CAD implied sum depends on the control total being correct. It cannot be checked record by record.49- Keeping the 25 quoted thousands-separator prices is a judgement call. They are unambiguous under RFC 4180 quoting, and the reconciliation does not contradict this choice.50- The data are synthetic.51