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

# Question

How many sale records in the provider's export (data/sales.csv, simulated) 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 compare with the provider's control totals (data/metadata.json)?

# Data and exclusions

• sales.csv has 1,200 data lines plus a header. The control count n_records is also 1,200, so the file has one line per expected record.
• I parsed the file with a standard CSV reader that respects quotes. A line enters the analytic dataset only if all of the following hold: it has exactly 5 fields; sale_id matches S + 5 digits; the municipality is one of the five named in the control totals; the date is a valid ISO date; price and area are numeric; area is greater than 0; and sale_id is unique.
• Exclusion ledger (out/exclusions.csv):
  1. Structural readability: 1,200 → 1,164. I removed 36 garbled lines. They contain random characters, the wrong number of fields and malformed IDs. The surviving IDs show that these lines are the contiguous block S00700–S00735. They cannot be read unambiguously, so they were not reconstructed.
  2. Field parsing: 1,164 → 1,164 (no rows removed).
  3. Duplicate sale_id: 1,164 → 1,164 (no rows removed).
• Another issue: 25 readable records write price_cad as a quoted string with comma thousands separators (e.g. "280,000"). Because the field is quoted, its boundaries are clear and the value is unambiguous. I removed the separator and kept these records. Treating them as unreadable would wrongly shrink the dataset.

# Method

These are descriptive totals over the analytic dataset: count, sum, mean, and the median of price_cad / area_m2. The reconciliation is control total minus analytic value, for the record count, the price sum and the count per municipality. Nothing is random; random_seed is null. out/analysis.py regenerates every output from data/.

# Results

Reconciliation by municipality (control minus valid = unreadable): Gatineau 346−335=11, Laval 282−274=8, Sherbrooke 216−209=7, Longueuil 194−187=7, Levis 162−159=3. These add up to 36, so the count reconciles exactly once the 36 garbled lines are taken into account. Feasibility: partial. 36 of the 1,200 control records could not be analysed because their lines are garbled and unreadable.

# Uncertainty

These figures summarise the readable records completely; they are not sample estimates, so no sampling interval applies. The real uncertainty is how far the valid-record statistics differ from the full 1,200-record table. The control totals pin down the count and sum of the missing 36 exactly. Their mean, 414,361 CAD, is higher than the valid mean. As a result, the mean over all 1,200 records implied by the control totals is 392,739.17 CAD, against 392,070.45 CAD over the valid records. The median price per m² over all 1,200 records cannot be determined, because the areas and individual prices of the 36 records are unknown.

# Limitations and open issues

• I did not reconstruct the 36 garbled records. All valid-only statistics (mean, median per m²) leave out a contiguous block of IDs. If that block is systematically different (it is if anything priced higher), these statistics are biased by a small amount.
• I parsed comma-formatted prices on the assumption that the commas are thousands separators. This is consistent with the magnitudes of the other prices.
• The data are simulated. The report does not support conclusions about real markets.