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

# Question

Summarise the (simulated) municipal property-sales export data/sales.csv over the records that can be read without ambiguity, and reconcile its record count and price sum with the provider's control totals in data/metadata.json.

# Data and exclusions

• sales.csv has 1200 data lines (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).
• 36 lines (file lines 701–736) are garbled: random characters and the wrong number of fields. They cannot be read, so they are excluded and not reconstructed (n_unreadable_records = 36; first issue in data_issues). The sale IDs S00700–S00735 do not appear among the readable rows, which fits these lines being those records, but none of their content is used.
• In 25 readable rows the price is a quoted string with a thousands separator (e.g. "280,000"). Standard CSV quoting makes this value unambiguous, so these rows are kept and the price is parsed as an integer (second issue in data_issues).
• No other problems were found: no missing values, no duplicate IDs, all dates valid (2021-01-01 to 2022-12-31; sale_date_min_valid, sale_date_max_valid), and all municipalities match the provider's list.
• Exclusion ledger (out/exclusions.csv): 1200 → 1164 at the field-count step. The other six validity steps remove 0 rows. Analysed: 1164 (n_analysed).

# Method

out/analysis.py parses the file with an RFC-4180 CSV reader and then applies these validity rules in order: exactly 5 fields; ID matches S\d{5}; municipality is in the provider's list; date is a valid ISO date; price is an integer (comma thousands separators allowed); area is a positive number; ID is unique. It then computes sums, the mean, and the median of price/area over the valid rows, and reconciles them with the control totals by subtraction. Nothing is imputed and nothing is random (random_seed = null). Baseline: the control totals alone give a mean of 471,287,000/1200 = 392,739.17 CAD (computed in the checks, see out/state/checks.md).

# Results

• Valid records: 1164 (n_valid_records, n_analysed). Records in the control count that could not be analysed: 36 (n_unreadable_records).
• Sum of price over valid records: 456,370,000 CAD (sum_price_valid). Price sum implied for the unreadable records: 14,917,000 CAD (sum_price_unreadable_implied), which is 414,361.11 CAD per record on average (mean_price_unreadable_implied).
• Mean price: 392,070.45 CAD (mean_price_valid). Median price per m²: 2,984.94 CAD/m² (median_price_per_m2_valid).
• Count gap by municipality, readable vs control (n_by_municipality_gap_vs_control): Gatineau 11, Laval 8, Levis 3, Longueuil 7, Sherbrooke 7. These add up to 36. Every municipality's readable count is at or below its control count, so the count reconciles exactly once the 36 garbled lines are accounted for.
• Feasibility: partial (feasibility, feasibility_reason). 36 of the 1200 control records could not be read.

# Uncertainty

These are descriptive totals over a fixed file, not estimates from a sample, so no sampling uncertainty is reported. The main uncertainty is that the summaries cover only 1164 of the 1200 records. The unreadable records have a higher implied mean price (414,361 vs 392,070 CAD), so the valid-record mean is slightly below the full-table mean (392,739 CAD from the control totals). The full-table median price per m² cannot be determined, because the areas of the 36 unreadable records are unknown.

# Limitations and open issues

• The 36 garbled records are excluded by rule. Their implied price sum is known only in total, from the control totals; individual values and areas are not.
• Keeping the 25 quoted prices with thousands separators is a judgement call. It is justified because CSV quoting makes these values unambiguous.
• The data are synthetic, so these results say nothing about a real market.
• During the work, files were accidentally created in a stray data/out/ folder because the shell was in the wrong directory. They were moved to out/ and the folder was removed. The two input files were checked by SHA-256 and are unchanged.