# 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.