F1-conf-04 — Reconciling a property-sales export to its control totals
Question
How many records of the provider's property-sales export can be read unambiguously, what are the sum and mean sale price and the median price per m² over those records, and how do the record count and price sum reconcile with the provider's control totals?
Data and exclusions
data/sales.csv has 1200 data lines (n_input_rows), the same as the provider's control count of 1200 (n_records_control). Two export problems were found (data_issues):
- 36 lines (file lines 701–736; sale_ids S00700–S00735 are missing,
missing_sale_id_range) are garbled: random characters and the wrong number of fields. They cannot be read unambiguously, so they were excluded and not reconstructed. - 25 readable rows (
n_price_thousands_separator_parsed) give the price as a quoted string with thousands separators (e.g. "280,000"). Because of the CSV quoting the value is unambiguous, so these rows were kept and the price was parsed as an integer.
The exclusion ledger (out/exclusions.csv) goes from 1200 lines through 7 rules. The 5-field rule removes 36 lines and the later rules (id format, municipality, ISO date, positive price, positive area, unique id) remove 0, leaving 1164 analysed records (n_analysed, n_valid_records). These records have no missing values and no duplicates, and their dates run from 2021-01-01 to 2022-12-31 (sale_date_range_valid).
Method
out/analysis.py parses the file with an RFC-4180 CSV reader, applies the validation rules in order, writes the analytic dataset (out/analytic.csv, 1164 rows), and computes the sum, mean, and median of price_cad/area_m2 over that dataset. It then compares the count and the sum with metadata.json. No randomness is involved (random_seed = null). The first baseline was the control totals themselves (n_records_control, sum_price_control). The analysed quantities are reconciled against those totals.
Results
- Records analysed: 1164 of 1200. Unreadable records: 36 (
n_valid_records,n_unreadable_records). - Price sum over valid records: 456,370,000 CAD, against a control of 471,287,000 CAD (
sum_price_valid,sum_price_control). The implied total for the unreadable records is 14,917,000 CAD (sum_price_unreadable_implied), or an implied mean of 414,361 CAD per unreadable record (mean_price_unreadable_implied). - Mean price over valid records: 392,070.45 CAD (
mean_price_valid). - Median price per m² over valid records: 2,984.94 CAD/m² (
median_price_per_m2_valid). - Shortfall by municipality: Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7, Levis 3. These add up to 36 (
n_by_municipality_shortfall), consistent with the count gap. - Feasibility: partial (
feasibility,feasibility_reason). 36 of the 1200 control records could not be analysed because their export lines are corrupted.
Uncertainty
These are exact descriptive statistics of a finite file, not sample estimates, so no sampling standard errors are reported. The real uncertainty is about the missing records. The mean and median cover only the 1164 readable records. They would match the full 1200-record table only if the corrupted records were similar to the rest. The implied mean of the missing records (414,361 CAD, mean_price_unreadable_implied) is about 6% above mean_price_valid, so the full-table mean is slightly higher than the valid-record mean. The full-table median price per m² cannot be determined, because the areas of the missing records are unknown.
Limitations and open issues
- 36 records (3% of the control count) are unrecoverable from the file. The provider would need to re-export lines 701–736 (ids S00700–S00735) for the reconciliation to be complete.
- The reconciliation assumes the readable rows are correct as recorded. The only external check on them is the control totals: per-municipality counts reconcile exactly once the 36 missing records are taken into account, but there is no per-record control.
- The implied unreadable sum is a residual (control minus valid). It is not an observed quantity.