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

# Question

How many sale records in the provider's export (data/sales.csv) can be read unambiguously? What are the sum and mean of price_cad and the median price per m² over those records? How do the record count and the price sum compare with the provider's control totals (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).
• Corrupted lines: 36 data lines (file lines 701–736) contain only random symbols and have the wrong number of fields (diagnostics.n_lines_wrong_field_count, data_issues[0]). The sale_id sequence S00001–S01200 is missing exactly the 36 ids S00700–S00735 (diagnostics.n_missing_ids_in_sequence). Compared with the control, the municipality counts are short by Gatineau 11, Laval 8, Levis 3, Longueuil 7 and Sherbrooke 7, which sums to 36 (diagnostics.municipality_count_gap_control_minus_valid). I excluded these lines. Following the information rules, I did not reconstruct or impute them.
• Thousands separators: 25 rows store the price as a quoted string with comma separators, such as "280,000" (diagnostics.n_price_thousands_separator_parsed, data_issues[1]). I kept these rows. CSV quoting makes the field boundaries unambiguous, and the pattern d{1,3}(,ddd)+ can only be read as one integer. A sensitivity analysis that excludes them is reported below.
• No row with five fields had a malformed field (diagnostics.n_rows_bad_format = 0), and no sale_id was duplicated (diagnostics.n_rows_duplicate_id = 0).
• Exclusion ledger (out/exclusions.csv): 1200 → 1164 (wrong field count, −36) → 1164 (format check, −0) → 1164 (unique id, −0). The analytic dataset has 1164 records (n_analysed, n_valid_records). Sale dates run from 2021-01-01 to 2022-12-31 (diagnostics.sale_date_min, diagnostics.sale_date_max).

# Method

out/analysis.py reads the file with Python's csv module. It keeps a line only if all of these hold: the line has exactly 5 fields; sale_id matches S\d{5}; municipality is text; the date is a valid ISO date; the price is an integer, either plain or comma-grouped; the area is a positive number; and the sale_id is unique. Over the kept records it computes the sum and mean of price_cad and the median of price_cad/area_m2. The reconciliation is n_unreadable = control count − valid count, and implied unreadable sum = control sum − valid sum. The baseline is the provider's control totals (n_records_control, sum_price_control). The script uses no randomness (random_seed = null). It is deterministic: a clean re-run reproduced results.json byte for byte (out/state/checks.md).

# Results

• Valid records: 1164 of 1200 (n_valid_records, n_records_control). Unreadable: 36 (n_unreadable_records).
• Sum of price over valid records: 456,370,000 CAD (sum_price_valid). Implied sum over the unreadable records: 14,917,000 CAD (sum_price_unreadable_implied), which works out to an implied mean of 414,361 CAD per unreadable record (diagnostics.mean_price_unreadable_implied). That figure falls within the range of valid prices.
• Mean price: 392,070.45 CAD (mean_price_valid).
• Median price per m²: 2,984.94 CAD/m² (median_price_per_m2_valid).
• Feasibility: partial (feasibility, feasibility_reason). 36 records in the control count could not be analysed because their lines in the export are corrupted.

# Uncertainty

These are exact descriptive totals over a fixed file, not estimates from a sample, so I report no sampling standard errors. The main source of uncertainty is how the data were handled:

• If the 25 comma-formatted prices were excluded, the results would be n = 1139, sum 447,035,000 CAD, mean 392,480.25 CAD and median 2,983.32 CAD/m² (diagnostics.sensitivity_excluding_thousands_separator_rows). The mean and median barely change; the sum and count change mechanically.
• sum_price_unreadable_implied assumes the provider's control sum is correct and that every valid record was exported without changes. I cannot verify either assumption from these files.
• The 1164-record summaries describe only the readable records. Because the corrupted block is one contiguous range of ids, it may not be a random subset. The implied mean of the missing records (414,361 CAD) is above the valid mean, so the full-table mean would be somewhat higher. I did not impute it.

# Limitations and open issues

• The task cannot be completed in full: 36 of the 1200 control records are unreadable, so feasibility = "partial". Recovering these records would need a clean re-export from the provider.
• My guess that the 36 corrupted lines are ids S00700–S00735 comes from the gap in the id sequence and the matching municipality deficits. I did not use it to reconstruct any content.
• Keeping the comma-formatted prices is a judgment call, documented above along with its sensitivity analysis.
• The data are synthetic (simulated), as stated in TASK.md.