1# F1-conf-04 — Reconciling the property-sales export to its control totals23## Question4How many sale records in the provider's export (`data/sales.csv`) can be read unambiguously? What are the sum and mean of `price_cad` and the median price per m² over those records? How do the record count and the price sum compare with the provider's control totals (`data/metadata.json`)?56## Data and exclusions7- `sales.csv` has 1200 data lines after the header (`n_input_rows`). The provider's control count is 1200 records (`n_records_control`) and the control price sum is 471,287,000 CAD (`sum_price_control`).8- **Corrupted lines:** 36 data lines (file lines 701–736) contain only random symbols and have the wrong number of fields (`diagnostics.n_lines_wrong_field_count`, `data_issues[0]`). The sale_id sequence S00001–S01200 is missing exactly the 36 ids S00700–S00735 (`diagnostics.n_missing_ids_in_sequence`). Compared with the control, the municipality counts are short by Gatineau 11, Laval 8, Levis 3, Longueuil 7 and Sherbrooke 7, which sums to 36 (`diagnostics.municipality_count_gap_control_minus_valid`). I excluded these lines. Following the information rules, I did not reconstruct or impute them.9- **Thousands separators:** 25 rows store the price as a quoted string with comma separators, such as `"280,000"` (`diagnostics.n_price_thousands_separator_parsed`, `data_issues[1]`). I **kept** these rows. CSV quoting makes the field boundaries unambiguous, and the pattern `d{1,3}(,ddd)+` can only be read as one integer. A sensitivity analysis that excludes them is reported below.10- No row with five fields had a malformed field (`diagnostics.n_rows_bad_format` = 0), and no sale_id was duplicated (`diagnostics.n_rows_duplicate_id` = 0).11- Exclusion ledger (`out/exclusions.csv`): 1200 → 1164 (wrong field count, −36) → 1164 (format check, −0) → 1164 (unique id, −0). The analytic dataset has 1164 records (`n_analysed`, `n_valid_records`). Sale dates run from 2021-01-01 to 2022-12-31 (`diagnostics.sale_date_min`, `diagnostics.sale_date_max`).1213## Method14`out/analysis.py` reads the file with Python's `csv` module. It keeps a line only if all of these hold: the line has exactly 5 fields; `sale_id` matches `S\d{5}`; municipality is text; the date is a valid ISO date; the price is an integer, either plain or comma-grouped; the area is a positive number; and the sale_id is unique. Over the kept records it computes the sum and mean of `price_cad` and the median of `price_cad/area_m2`. The reconciliation is n_unreadable = control count − valid count, and implied unreadable sum = control sum − valid sum. The baseline is the provider's control totals (`n_records_control`, `sum_price_control`). The script uses no randomness (`random_seed` = null). It is deterministic: a clean re-run reproduced `results.json` byte for byte (`out/state/checks.md`).1516## Results17- Valid records: **1164** of 1200 (`n_valid_records`, `n_records_control`). Unreadable: **36** (`n_unreadable_records`).18- Sum of price over valid records: **456,370,000 CAD** (`sum_price_valid`). Implied sum over the unreadable records: **14,917,000 CAD** (`sum_price_unreadable_implied`), which works out to an implied mean of 414,361 CAD per unreadable record (`diagnostics.mean_price_unreadable_implied`). That figure falls within the range of valid prices.19- Mean price: **392,070.45 CAD** (`mean_price_valid`).20- Median price per m²: **2,984.94 CAD/m²** (`median_price_per_m2_valid`).21- Feasibility: **partial** (`feasibility`, `feasibility_reason`). 36 records in the control count could not be analysed because their lines in the export are corrupted.2223## Uncertainty24These are exact descriptive totals over a fixed file, not estimates from a sample, so I report no sampling standard errors. The main source of uncertainty is how the data were handled:25- If the 25 comma-formatted prices were excluded, the results would be n = 1139, sum 447,035,000 CAD, mean 392,480.25 CAD and median 2,983.32 CAD/m² (`diagnostics.sensitivity_excluding_thousands_separator_rows`). The mean and median barely change; the sum and count change mechanically.26- `sum_price_unreadable_implied` assumes the provider's control sum is correct and that every valid record was exported without changes. I cannot verify either assumption from these files.27- The 1164-record summaries describe only the readable records. Because the corrupted block is one contiguous range of ids, it may not be a random subset. The implied mean of the missing records (414,361 CAD) is above the valid mean, so the full-table mean would be somewhat higher. I did not impute it.2829## Limitations and open issues30- The task cannot be completed in full: 36 of the 1200 control records are unreadable, so `feasibility` = "partial". Recovering these records would need a clean re-export from the provider.31- My guess that the 36 corrupted lines are ids S00700–S00735 comes from the gap in the id sequence and the matching municipality deficits. I did not use it to reconstruct any content.32- Keeping the comma-formatted prices is a judgment call, documented above along with its sensitivity analysis.33- The data are synthetic (simulated), as stated in TASK.md.34