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

# Question

Summarise the provider's property-sales export (data/sales.csv, synthetic data) and reconcile it to the provider's control totals (data/metadata.json). Estimands: the number of analysable records, the sum and mean of sale price, the median price per m², and how the record count and price sum compare with the control totals.

# Data and exclusions

• sales.csv has 1200 data lines below 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).
• 36 consecutive data lines contain random characters and have the wrong number of fields (n_garbled_lines). They cannot be read unambiguously, so they were excluded and not reconstructed. The readable data has no sale_ids S00700–S00735.
• 25 records give the price as a quoted string with thousands separators, e.g. "280,000" (n_quoted_price_records). Standard CSV quoting makes these unambiguous, and each matches the strict pattern d{1,3}(,ddd)+. They were kept after removing the separators.
• No sale_id is duplicated. All dates are valid, and all prices and areas are positive and plausible (see out/state/data_audit.md).
• Exclusion ledger (out/exclusions.csv): 1200 lines → 1164 after the 5-field parse (36 removed) → 1164 after the format checks (0 removed) → 1164 after the duplicate check (0 removed). All issues and how each was handled are listed in data_issues.

# Method

out/analysis.py reads each line with Python's CSV reader. A record is kept only if: it has exactly 5 fields; the sale_id is S plus 5 digits; the municipality appears in the control list; the date is a valid ISO date; the price is a positive integer (plain, or with strict thousands separators); and the area is a positive number. Any sale_id that appeared more than once would have had every copy removed; none did. On the kept records the script computes the count, the price sum, the mean price and the median of price_cad/area_m2. The reconciliation subtracts these from the control totals. The baseline is the provider's control totals. No randomness is involved (random_seed = null).

# Results

• Analysable records: 1164 (n_valid_records, n_analysed) out of 1200 in the control total. 36 records could not be analysed (n_unreadable_records).
• Price sum over analysable records: 456,370,000 CAD (sum_price_valid). The gap to the control sum, which is the implied price sum of 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).
• Count gap by municipality, control minus readable (reconciliation_by_municipality_gap): Gatineau 11, Laval 8, Sherbrooke 7, Longueuil 7, Levis 3. These add up to 36, so the count gap is fully explained by the garbled lines.
• Feasibility: "partial" (feasibility), because 36 of the control records could not be analysed (feasibility_reason).

# Uncertainty

The figures are exact totals over the readable records, not estimates from a sample, so no sampling uncertainty applies. The main uncertainty is that the 36 unreadable records are missing. Only their combined price is known (14,917,000 CAD, sum_price_unreadable_implied), and it comes from the provider's control sum. Their areas, and so their price per m², are unknown. The full-table median price per m² could therefore differ from median_price_per_m2_valid. Adding 36 values to 1164 can move the median by at most 18 positions in the sorted list. The mean over all 1200 control records is implied by the control totals, but it was not the requested estimand.

# Limitations and open issues

• The analysis is partial: the 36 garbled lines were not reconstructed, as the information rules require. Every summary statistic covers only the 1164 readable records.
• The reconciliation relies on the provider's control totals being correct. It confirms only that the count gap and the municipality gaps are consistent with the 36 garbled lines. The price gap cannot be checked record by record.
• Keeping the 25 quoted, thousands-separated prices is a judgement call. They are unambiguous under standard CSV parsing. Treating them as unreadable instead would lower the count to 1139.
• Process note: during the session, workspace state files were briefly created by mistake in a subdirectory under data/. They were moved to out/state/. The input files were never changed, and their SHA-256 hashes match the bundle manifest.