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

# Question

Summarise the municipal property-sales export data/sales.csv (simulated data) and reconcile it to the provider's control totals in data/metadata.json: how many records can be analysed, the sum and mean sale price over those records, their median price per m², and how the record count and price sum compare with the control totals.

# Data and exclusions

• sales.csv has n_input_rows["sales.csv"] = 1200 data lines below the header. The provider's control count is n_records_control = 1200 and the control price sum is sum_price_control = 471,287,000 CAD.
• n_malformed_lines = 36 lines (file lines 701–736) are corrupted export lines. They contain random characters and split into 1, 6, 8–12 fields instead of 5. Sale IDs S00700–S00735 are missing from the readable rows (data_issues), so these lines are the corrupted records. Because the information rules forbid reconstructing records, these lines were excluded and not imputed.
• n_price_thousands_separator_parsed = 25 prices are CSV-quoted with a comma as thousands separator (e.g. "280,000"). Every one matches \d{1,3}(,\d{3})+, so the reading is unambiguous for whole-dollar CAD prices. Their values are in the same range as the other prices, so they were kept after removing the commas.
• No other problems were found in the readable rows. Sale IDs are unique, all dates are valid ISO dates (2021-01-01 to 2022-12-31), all municipalities are known, and all areas are positive.
• Exclusion ledger (out/exclusions.csv): step 1 (needs exactly 5 fields) 1200 → 1164; step 2 (every field parses) 1164 → 1164; step 3 (unique sale_id) 1164 → 1164. Result: n_analysed = 1164.

# Method

out/analysis.py reads both inputs with Python's csv module, applies the three exclusion steps, writes out/analytic.csv, and computes the sum, mean and median of price/area over the analytic dataset. Reconciliation is by subtraction from the control totals: n_unreadable_records = control count − valid count, and sum_price_unreadable_implied = control sum − valid sum. Per-municipality counts are compared with n_by_municipality. The script is deterministic and uses no randomness (random_seed = null). The control totals act as the baseline.

# Results

• Analysable records: n_valid_records = n_analysed = 1164 out of 1200. n_unreadable_records = 36.
• Sum of price over the analytic dataset: sum_price_valid = 456,370,000 CAD. Implied sum of the unreadable records: sum_price_unreadable_implied = 14,917,000 CAD.
• Mean price: mean_price_valid = 392,070.45 CAD.
• Median price per m²: median_price_per_m2_valid = 2,984.94 CAD/m².
• Shortfall per municipality against the control counts (n_shortfall_by_municipality): Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7, Levis 3. These add up to 36, which matches the record shortfall exactly.
• feasibility = "partial". The reason is given in feasibility_reason.

# Uncertainty

These are descriptive totals over a finite file, so there is no sampling uncertainty to report and no confidence intervals were computed. The uncertainty that matters is how the 36 unreadable records affect the results. Their total price is known only through the control-total difference (sum_price_unreadable_implied). Their individual prices and areas are unknown. As a result, the mean and median per m² describe the 1164 readable records and may differ from the values for all 1200 records. The size of that difference cannot be determined without guessing at the unreadable records.

# Limitations and open issues

• The task is only partially feasible. 36 of the 1200 control records could not be analysed because their lines are corrupted (n_unreadable_records, feasibility_reason). They were not reconstructed.
• The implied price sum of the missing records assumes that the provider's control totals are correct and that the readable rows were exported without error.
• Accepting prices with a comma thousands separator is a judgement call. If those 25 records were treated as unreadable instead, the count and sum would change.
• The data are simulated.