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

## Question
Summarise the (simulated) municipal property-sales export `data/sales.csv` and reconcile it to the provider's control totals in `data/metadata.json`. The estimands are the number of records that can be analysed, the sum and mean of `price_cad` over those records, the median price per m² over them, and the gap between these figures and the control count and price sum.

## Data and exclusions
- `data/sales.csv` has 1,200 data lines after the header (`n_input_rows`). The control count is 1,200 records (`n_records_control`) and the control price sum is 471,287,000 CAD (`sum_price_control`). SHA-256 hashes and the full audit are in `out/state/data_audit.md`.
- **Corrupted lines.** 36 lines (file lines 701–736) are garbled. They contain random symbols, have the wrong number of fields and carry no valid `sale_id`. Sale IDs S00700–S00735 are missing from the readable records. These lines were excluded and **not reconstructed** (`data_issues`, `n_unreadable_records`).
- **Thousands separators.** 25 records have the price written as a quoted string with a comma separator, e.g. `"280,000"` (`n_price_thousands_separator`). Standard CSV quoting makes these fields unambiguous, so the records were kept and the price converted to an integer.
- Among the readable records there are no duplicate IDs, no missing values, no invalid dates (all fall in 2021-01-01..2022-12-31) and no non-positive prices or areas.
- Exclusion ledger (`out/exclusions.csv`): 1,200 lines read → 1,164 lines that parse into exactly 5 fields (−36) → 1,164 with every field valid (−0) → 1,164 with a unique ID (−0) = `n_analysed`.

## Method
`out/analysis.py` reads only `data/`. It splits the file into physical lines and parses each one with RFC-4180 CSV rules. It then validates every field with explicit patterns (ID `S` + 5 digits, alphabetic municipality, ISO date, integer price with an optional comma thousands separator, numeric area, both values > 0) and drops any duplicated IDs. Totals, the mean and the median of `price_cad / area_m2` are computed over the 1,164 valid records. The reconciliation subtracts the analysed figures from the control totals. The script uses no randomness (`random_seed` = null). The baseline is the control totals themselves (`n_records_control`, `sum_price_control`).

## Results
- Records analysed: **1,164** (`n_valid_records`, `n_analysed`). Unreadable records: **36** (`n_unreadable_records`). 1,164 + 36 = 1,200 = `n_records_control`.
- Price sum over analysed records: **456,370,000 CAD** (`sum_price_valid`). Implied price sum of the unreadable records: **14,917,000 CAD** (`sum_price_unreadable_implied`), i.e. an implied mean of 414,361 CAD per unreadable record (`implied_mean_price_unreadable`).
- Mean sale price over analysed records: **392,070.45 CAD** (`mean_price_valid`).
- Median price per m²: **2,984.94 CAD/m²** (`median_price_per_m2_valid`).
- Readable counts by municipality fall short of the control counts by Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7 and Levis 3, which sums to 36 (`n_by_municipality_gap`, `n_by_municipality_valid`).
- Feasibility: **partial** (`feasibility`), because 36 of the 1,200 control records could not be read (`feasibility_reason`).

## Uncertainty
These are exact descriptive totals over a fixed file, not sample estimates, so no sampling standard errors are reported. The uncertainty that matters comes from the missing data. The 36 unreadable records are excluded, so `mean_price_valid` and `median_price_per_m2_valid` describe the readable records only, not the full 1,200-record table. Mixing the control sum with the observed records would give a mean over all 1,200 records of 471,287,000 / 1,200 = 392,739 CAD (`control_mean_price_all_records`). This is a reconciliation figure computed from the control totals, not an estimate from records. The full-table median price per m² cannot be bounded tightly, because the areas of the unreadable records are unknown.

## Limitations and open issues
- The task is only **partially** feasible: 36 records (3% of the control count) cannot be read. By rule they are not reconstructed, so they enter only through the control-total arithmetic (`sum_price_unreadable_implied`).
- The municipality breakdown of the unreadable records (`n_by_municipality_gap`) comes from the control totals. It is not observed in the records.
- Keeping the 25 comma-separated prices is a judgement call: I treat standard CSV quoting as unambiguous. That reading is supported by every price being a multiple of 1,000 and by the 1,164 readable records accounting for the full 1,200 apart from the 36 garbled lines. If a stricter reading excluded them, the analysed count would be 1,139 (`n_valid_if_thousands_sep_excluded`). Statistics for that alternative are not computed.
- The data are synthetic, so no substantive conclusions about real property markets follow.