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

## Question
How many sale records in the provider's export (`data/sales.csv`, simulated) 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 compare with the provider's control totals (`data/metadata.json`)?

## Data and exclusions
- `sales.csv` has 1,200 data lines plus a header. The control count `n_records` is also 1,200, so the file has one line per expected record.
- I parsed the file with a standard CSV reader that respects quotes. A line enters the analytic dataset only if all of the following hold: it has exactly 5 fields; `sale_id` matches `S` + 5 digits; the municipality is one of the five named in the control totals; the date is a valid ISO date; price and area are numeric; area is greater than 0; and `sale_id` is unique.
- Exclusion ledger (`out/exclusions.csv`):
  1. Structural readability: 1,200 → 1,164. I removed **36 garbled lines**. They contain random characters, the wrong number of fields and malformed IDs. The surviving IDs show that these lines are the contiguous block **S00700–S00735**. They cannot be read unambiguously, so they were not reconstructed.
  2. Field parsing: 1,164 → 1,164 (no rows removed).
  3. Duplicate `sale_id`: 1,164 → 1,164 (no rows removed).
- Another issue: **25 readable records** write `price_cad` as a quoted string with comma thousands separators (e.g. `"280,000"`). Because the field is quoted, its boundaries are clear and the value is unambiguous. I removed the separator and kept these records. Treating them as unreadable would wrongly shrink the dataset.

## Method
These are descriptive totals over the analytic dataset: count, sum, mean, and the median of `price_cad / area_m2`. The reconciliation is control total minus analytic value, for the record count, the price sum and the count per municipality. Nothing is random; `random_seed` is null. `out/analysis.py` regenerates every output from `data/`.

## 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 (implied mean 414,361.11 CAD) |
| mean price (valid) | 392,070.45 CAD |
| median price per m² (valid) | 2,984.94 CAD/m² |

Reconciliation by municipality (control minus valid = unreadable): Gatineau 346−335=11, Laval 282−274=8, Sherbrooke 216−209=7, Longueuil 194−187=7, Levis 162−159=3. These add up to 36, so the count reconciles exactly once the 36 garbled lines are taken into account. **Feasibility: partial.** 36 of the 1,200 control records could not be analysed because their lines are garbled and unreadable.

## Uncertainty
These figures summarise the readable records completely; they are not sample estimates, so no sampling interval applies. The real uncertainty is how far the valid-record statistics differ from the full 1,200-record table. The control totals pin down the count and sum of the missing 36 exactly. Their mean, 414,361 CAD, is higher than the valid mean. As a result, the mean over all 1,200 records implied by the control totals is 392,739.17 CAD, against 392,070.45 CAD over the valid records. The median price per m² over all 1,200 records cannot be determined, because the areas and individual prices of the 36 records are unknown.

## Limitations and open issues
- I did not reconstruct the 36 garbled records. All valid-only statistics (mean, median per m²) leave out a contiguous block of IDs. If that block is systematically different (it is if anything priced higher), these statistics are biased by a small amount.
- I parsed comma-formatted prices on the assumption that the commas are thousands separators. This is consistent with the magnitudes of the other prices.
- The data are simulated. The report does not support conclusions about real markets.