1# F1-conf-04 — Reconciling a property-sales export to its control totals23## Question4How many records of the provider's (simulated) property-sales export can be analysed, what are the sum and mean of the sale price 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?56## Data and exclusions7- `data/sales.csv` has 1200 data lines after the header (`n_input_rows`). The provider's control count is also 1200 (`n_records_control`; `baseline_n_lines_minus_control` = 0). So the file has the expected number of lines, but some of them cannot be read.8- 36 consecutive lines (sale_id positions S00700–S00735) contain random characters, have between 1 and 12 CSV fields, and their sale_ids do not match the required format. Their fields cannot be read without ambiguity, so they were excluded and **not** reconstructed (`data_issues`, `out/exclusions.csv` step 1_structure: 1200 → 1164).9- 25 records store the price as a quoted string with a thousands separator, e.g. `"280,000"` (`n_price_thousands_separator`). Standard CSV quoting makes these unambiguous, so I parsed them as integers by removing the commas and kept the records.10- I found no other field-level problems (step 2_fields: 0 removed) and no duplicate sale_ids (step 3_dedup: 0 removed). The analytic dataset has 1164 records (`n_analysed`, `n_valid_records`), and `out/analytic.csv` contains these rows.1112## Method13`out/analysis.py` uses Python's `csv` module to parse the file. It keeps a line only if all of the following hold:14- the line has exactly 5 fields;15- the sale_id matches `S` followed by 5 digits;16- the municipality is one of the five in the control totals;17- the date is a valid ISO date;18- the price is a positive integer, written either plainly or with quoted thousands separators;19- the area is a positive number.2021Duplicate sale_ids would be dropped (none were found). On the retained records the script computes the price sum and mean, and the median of price_cad/area_m2. It then compares the record count and price sum with `metadata.json`. No randomness is used (`random_seed` = null).2223## Results24| quantity | value | key |25|---|---|---|26| control record count | 1200 | `n_records_control` |27| analysed records | 1164 | `n_valid_records` / `n_analysed` |28| unreadable records | 36 | `n_unreadable_records` |29| control price sum | 471,287,000 CAD | `sum_price_control` |30| price sum, analysed | 456,370,000 CAD | `sum_price_valid` |31| implied price sum of unreadable records | 14,917,000 CAD | `sum_price_unreadable_implied` |32| mean price, analysed | 392,070.45 CAD | `mean_price_valid` |33| median price per m², analysed | 2,984.94 CAD/m² | `median_price_per_m2_valid` |34| feasibility | partial | `feasibility` |3536Reconciliation by municipality (`n_by_municipality_shortfall`): Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7 and Levis 3 records short of the control counts. These add up to 36, which matches `n_unreadable_records`. The implied mean price of the unreadable records is 414,361 CAD (`mean_price_unreadable_implied`). This is close to the analysed mean, so nothing suggests that the shortfall comes from anything other than the 36 corrupted lines. Sale dates in the analysed set run from 2021-01-01 to 2022-12-31 (`sale_date_min_valid`, `sale_date_max_valid`).3738## Uncertainty39These are exact descriptive totals over a finite export, not sample estimates, so I report no sampling uncertainty. The real uncertainty is about the 36 unreadable records. Their total price is known only as the residual against the control (`sum_price_unreadable_implied`). Their areas are unknown, so the median price per m² over all 1200 records cannot be determined. A median only shifts across at most 36 ranks out of 1200, but I did not compute bounds.4041## Limitations and open issues42- The result is **partial** (`feasibility`, `feasibility_reason`): 36 of the 1200 control records cannot be analysed because their export lines are corrupted. All summaries cover only the 1164 readable records.43- The implied unreadable sum assumes that the control total is correct and that the prices of the readable records are exported correctly.44- I identified the unreadable records as S00700–S00735 from the gap in otherwise sequential sale_ids and from the position of the garbled lines. The garbled lines themselves do not contain readable IDs.45- Treating the 25 thousands-separator prices as readable is a judgement call. I consider it unambiguous because of the CSV quoting.46