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

# Question

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

# Data and exclusions

• sales.csv has 1200 data lines after the header (n_input_rows), matching the control count of 1200 records (n_records_control).
• 36 lines (file lines 701–736) are corrupted: random characters, wrong field counts, and sale_ids that do not match S + 5 digits. Their fields cannot be read unambiguously, so they were excluded and not reconstructed (data_issues[0]). The readable IDs skip S00700–S00735, which fits with these lines, but no content was inferred from that gap.
• 25 readable records store the price as a quoted string with a thousands separator (e.g. "280,000"). Standard CSV quoting makes these values unambiguous, so I removed the separator, parsed them as integers and kept the records (data_issues[1]).
• No duplicate IDs, invalid dates, non-numeric prices or non-positive areas were found among the readable records.
• Exclusion ledger (out/exclusions.csv): 1200 lines read → 1164 after the structural check (36 removed) → 1164 after the field-validity check → 1164 after de-duplication = n_analysed.

# Method

out/analysis.py parses the file with Python's RFC-4180 csv reader. It keeps rows that have exactly 5 fields, a valid sale_id, an ISO date, an integer price (quoted thousands separators allowed), a positive area and a non-empty municipality, and it removes duplicate IDs. It then computes the count, sum and mean of price_cad and the median of price_cad/area_m2 over the analytic dataset. Reconciliation is done by subtraction from the control totals. The method is deterministic: no randomness is used (random_seed = null).

# Results

• Records analysed: 1164 (n_valid_records, n_analysed); 36 of the 1200 control records could not be analysed (n_unreadable_records).
• Price sum over the analysed records: 456,370,000 CAD (sum_price_valid), against a control total of 471,287,000 CAD (sum_price_control). The implied total for the unreadable records is 14,917,000 CAD (sum_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).
• By municipality, readable counts fall short of the control counts by Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7 and Levis 3; these shortfalls total 36 (control_minus_valid_by_municipality, valid_by_municipality). The count reconciles exactly once the 36 corrupted lines are taken into account.
• Feasibility: partial (feasibility, feasibility_reason).

# Uncertainty

The figures are exact totals over the readable records, not samples, so there is no sampling error and no confidence intervals were computed. The only uncertainty is about the 36 unreadable records. Their total price is known from the controls (14,917,000 CAD, about 414,361 CAD per record on average), but their individual prices and areas are not. The mean and median would therefore differ if computed over all 1200 records. Because the per-record prices are unknown, the size of that difference for the median price per m² cannot be determined.

# Limitations and open issues

• The 36 corrupted records are excluded, so the mean and median describe only 1164 of the 1200 records. As the rules require, I did not impute them.
• Each municipality's share of the unreadable records is known only from the control-count shortfalls; no record-level attribution was made.
• I treated quoted thousands-separated prices as readable. This judgement rests on standard CSV quoting rules.
• The data are simulated.