# F1-conf-04 — Reconciling the property-sales export to its control totals

## Question
How 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`?

## Data and exclusions
- `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.
- **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.
- **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.
- 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.
- 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.

## Method
The 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.

## Results
| quantity | value |
|---|---|
| control records | 1,200 |
| valid / analysed records | 1,164 |
| unreadable records | 36 |
| control price sum | 471,287,000 CAD |
| valid price sum | 456,370,000 CAD |
| implied price sum of unreadable records | 14,917,000 CAD |
| mean price (valid) | 392,070.45 CAD |
| median price per m² (valid) | 2,984.94 CAD/m² |
| feasibility | partial |

The 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.

## Uncertainty
The 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.

## Limitations and open issues
- **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.
- 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.
- The data are synthetic, so no substantive conclusions about real property markets should be drawn.