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

# Question

Summarise the property-sales export data/sales.csv (synthetic data) over the records that can be read unambiguously, 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 after the header (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). These control totals serve as the baseline.
• Corrupted lines: 36 data lines (file lines 701–736) contain random characters and the wrong number of fields (n_parse_failures, data_issues). Sale IDs S00700–S00735 (missing_sale_ids_range, n_missing_sale_ids) are missing from the readable records. These lines cannot be read unambiguously, so they are excluded and not reconstructed.
• Formatted prices: 25 records give price_cad as a quoted string with comma thousands separators, e.g. "280,000" (n_price_thousands_separator). Because the field is quoted, its value is unambiguous. These prices were converted to integers and the records kept.
• No sale_id appears twice (n_duplicate_ids = 0).
• Exclusion ledger (out/exclusions.csv): step 1, parse check: 1200 → 1164 (36 removed); step 2, unique ID: 1164 → 1164 (0 removed). The analysis therefore uses 1164 records (n_analysed, n_valid_records).

# Method

out/analysis.py reads the CSV with a standard CSV parser that respects quoting. A line is kept only if it meets all of these conditions:

• it has exactly 5 fields;
• sale_id matches S followed by 5 digits;
• the municipality is one of the five named in the metadata;
• the date is a valid ISO date;
• the price is an integer, written plainly or with comma thousands separators;
• the area is a positive number.

From the kept records the script computes the count, the sum and mean of price, and the median of price_cad / area_m2. It then compares the count and the sum with the control totals; the differences are the unreadable count and the implied price sum of the unreadable records. Counts are also compared within each municipality. The script uses no randomness (random_seed = null). It is deterministic: a clean re-run produced byte-identical outputs.

# Results

• Records analysed: 1164 of 1200 (n_valid_records, n_records_control). Records that could not be analysed: 36 (n_unreadable_records).
• Sum of price over the analysed records: 456,370,000 CAD (sum_price_valid). The control sum minus this gives an implied 14,917,000 CAD for the unreadable records (sum_price_unreadable_implied), i.e. a mean of about 414,361 CAD per unreadable record (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).
• Gaps by municipality (control minus analysed): Gatineau 11, Laval 8, Sherbrooke 7, Levis 3, Longueuil 7 (n_by_municipality_gap). These add up to 36, which equals the number of corrupted lines. So the whole count gap comes from the corrupted lines.
• Feasibility: partial (feasibility, feasibility_reason).

# Uncertainty

These figures are exact descriptive statistics of the readable records; no sampling inference is involved, so no standard errors or confidence intervals are reported. The main uncertainty is what the 36 unreadable records contain. Their count and total price are known exactly from the control totals (n_unreadable_records, sum_price_unreadable_implied). Their individual prices and areas are not known. As a result, the mean price and the median price per m² for the full 1200-record table cannot be determined. The median per m² is especially uncertain because the unreadable records' areas are unknown.

# Limitations and open issues

• Feasibility is partial: 36 of the 1200 control records could not be analysed because their lines in the export are corrupted (feasibility_reason). Following the task rules, they were not imputed.
• The analysis assumes the 25 comma-formatted prices are thousands separators, as is usual in Canadian English formatting. All of them are quoted and every one matches the d,ddd pattern.
• The reconciliation assumes the control totals describe the same table as the export. The matching per-municipality gaps support this but cannot prove it.
• The data are synthetic.