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

# Question

How many sale records in data/sales.csv (simulated data) can be analysed, what are the sum and mean of price_cad and the median price per m² over those records, and how do the record count and price sum reconcile to the provider's control totals in data/metadata.json?

# Data and exclusions

• sales.csv has 1,200 data lines plus a header. The provider's control total is also 1,200 records, with a price sum of 471,287,000 CAD.
• Corrupted block: file lines 701–736 (36 data lines) are garbled bytes. They have the wrong number of fields (between 1 and 12), malformed sale_ids and non-numeric content. Their fields cannot be read unambiguously, so all 36 were excluded. None were reconstructed or imputed. The readable records are missing sale_ids S00700–S00735, a contiguous run of 36. This is consistent with the corrupted block, but no id was assigned to any garbled line.
• Thousands separators: 25 readable records give price_cad as a quoted CSV string with a comma separator (e.g. "280,000"). Standard CSV quoting makes these unambiguous, so they were converted to integers and kept.
• No other checks removed any records. These were: sale_id format and uniqueness, municipality in the control list, a valid ISO date (all dates fall in 2021-01-01 to 2022-12-31), and positive price and area. The ledger is in out/exclusions.csv: 1,200 → 1,164.
• Reconciliation by municipality (control minus readable): Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7, Levis 3. These add up to 36, which matches the corrupted block exactly.

# Method

The file was parsed with Python's csv module, which handles quoted fields. Validation rules were applied in sequence. Statistics were computed on the records that passed every rule: count, price sum, arithmetic mean of price, and median of price_cad / area_m2. The implied price sum of the unreadable records is the control sum minus the valid sum. The analysis is deterministic and uses no randomness. Run ~/wp29/venv/bin/python out/analysis.py to regenerate everything.

# Results

The counts reconcile exactly: 1,164 readable + 36 unreadable = 1,200. The price sum reconciles only by construction: the 14,917,000 CAD gap is attributed to the 36 unreadable records. That implies a mean of about 414,361 CAD per unreadable record, which is plausible next to the valid mean.

# Uncertainty

The analysis covers all readable records; it is not a sample. There is therefore no sampling uncertainty to report for these descriptive statistics. The real uncertainty is about the missing 3% of records. Their total price is known only implicitly from the control sum. Their individual prices and areas are unknown, so the effect of their absence on the median price per m² cannot be determined. Because their implied mean (≈414k) is above the valid mean, the full-table mean price would be slightly higher: 471,287,000 / 1,200 ≈ 392,739 CAD.

# Limitations and open issues

• Feasibility is "partial". 36 of the 1,200 control records could not be analysed because their export rows are corrupted. They are excluded, not reconstructed.
• The implied unreadable sum assumes the control total and the export describe the same table and that the readable records contain no export errors. This cannot be checked independently.
• The data are synthetic, so no substantive conclusions about real property markets should be drawn.