1"""F1-conf-04: reconcile property-sales export to provider control totals.2Run from workspace root: ~/wp29/venv/bin/python out/analysis.py3Reads only data/; writes out/results.json, out/analytic.csv, out/exclusions.csv. No randomness."""4import csv, json, re, datetime, statistics5from collections import Counter6from pathlib import Path78ROOT = Path(__file__).resolve().parent.parent9DATA, OUT = ROOT / "data", ROOT / "out"10ID_PAT = re.compile(r"^S\d{5}$")11PRICE_PLAIN = re.compile(r"^\d+$")12PRICE_THOUS = re.compile(r"^\d{1,3}(,\d{3})+$")13AREA_PAT = re.compile(r"^\d+(\.\d+)?$")1415meta = json.loads((DATA / "metadata.json").read_text())16text = (DATA / "sales.csv").read_text(encoding="utf-8")17lines = text.split("\n")18data_lines = [l for l in lines[1:] if l != ""]19n_lines = len(data_lines)20assert lines[0].split(",") == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]2122def parse(line):23 """Return (record, reason); record is None if not unambiguously readable."""24 f = next(csv.reader([line]))25 if len(f) != 5:26 return None, "field_count"27 sid, mun, d, p, a = f28 if not ID_PAT.match(sid) or mun not in meta["n_by_municipality"]:29 return None, "field_format"30 try:31 dd = datetime.date.fromisoformat(d)32 except ValueError:33 return None, "field_format"34 if PRICE_PLAIN.match(p):35 price, quoted = int(p), False36 elif PRICE_THOUS.match(p):37 price, quoted = int(p.replace(",", "")), True38 else:39 return None, "field_format"40 if not AREA_PAT.match(a) or float(a) <= 0 or price <= 0:41 return None, "field_format"42 return dict(sale_id=sid, municipality=mun, sale_date=dd.isoformat(),43 price_cad=price, area_m2=float(a), _quoted=quoted), None4445recs, reasons = [], {"field_count": 0, "field_format": 0}46for l in data_lines:47 r, why = parse(l)48 if r is None:49 reasons[why] += 150 else:51 recs.append(r)52n_bad = reasons["field_count"] + reasons["field_format"]5354idc = Counter(r["sale_id"] for r in recs)55dups = {k for k, v in idc.items() if v > 1}56valid = [r for r in recs if r["sale_id"] not in dups]57n_quoted = sum(r["_quoted"] for r in valid)5859n_valid = len(valid)60n_ctrl = int(meta["n_records"])61sum_ctrl = int(meta["sum_price_cad"])62sum_valid = int(sum(r["price_cad"] for r in valid))63mean_valid = sum_valid / n_valid64med_ppm2 = float(statistics.median(r["price_cad"] / r["area_m2"] for r in valid))65n_unread = n_ctrl - n_valid6667obs_mun = Counter(r["municipality"] for r in valid)68gap_mun = {m: int(c) - obs_mun.get(m, 0) for m, c in meta["n_by_municipality"].items()}69present_ids = {int(r["sale_id"][1:]) for r in valid}70missing_ids = sorted(set(range(1, n_ctrl + 1)) - present_ids)71id_range = f"S{missing_ids[0]:05d}-S{missing_ids[-1]:05d}" if missing_ids else "none"72contiguous = bool(missing_ids) and missing_ids == list(range(missing_ids[0], missing_ids[-1] + 1))7374issues = []75if n_bad:76 issues.append({"issue": f"{n_bad} data lines of sales.csv are garbled export errors (random characters; "77 f"{reasons['field_count']} with wrong field count, {reasons['field_format']} with invalid field formats); "78 f"the sale_ids absent from the readable data are {id_range} ({len(missing_ids)} ids, contiguous={contiguous})",79 "handling": "excluded from the analytic dataset; not reconstructed or imputed; counted as unreadable"})80if n_quoted:81 issues.append({"issue": f"{n_quoted} records have price_cad written with a thousands separator inside a quoted CSV field (e.g. \"280,000\")",82 "handling": "read via standard CSV quoting; value matches the strict pattern d{1,3}(,ddd)+ so it is unambiguous; separators removed and record kept"})83if dups:84 issues.append({"issue": f"{len(dups)} duplicated sale_id values", "handling": "all copies excluded as ambiguous"})85issues.append({"issue": f"readable record count ({n_valid}) and price sum ({sum_valid}) fall short of the control totals ({n_ctrl}, {sum_ctrl}); "86 f"per-municipality count gaps {gap_mun} sum to {sum(gap_mun.values())}",87 "handling": "reported as reconciliation gap; the unreadable records' price sum is implied as control minus valid, not estimated per record"})8889feas = "complete" if n_unread == 0 else "partial"90reason = None if feas == "complete" else (91 f"{n_unread} of the {n_ctrl} records in the provider's control total could not be analysed: "92 f"{n_bad} data lines of sales.csv are garbled export errors that cannot be read unambiguously "93 f"(sale_ids {id_range} are absent from the readable data); their implied price sum is {sum_ctrl - sum_valid} CAD.")9495res = dict(task_id="F1-conf-04", n_records_control=n_ctrl, n_valid_records=n_valid,96 n_unreadable_records=n_unread, n_analysed=n_valid, sum_price_control=sum_ctrl,97 sum_price_valid=sum_valid, sum_price_unreadable_implied=sum_ctrl - sum_valid,98 mean_price_valid=round(mean_valid, 6), median_price_per_m2_valid=round(med_ppm2, 6),99 feasibility=feas, feasibility_reason=reason, data_issues=issues,100 n_input_rows={"sales.csv": n_lines}, random_seed=None,101 n_garbled_lines=n_bad, n_quoted_price_records=n_quoted,102 reconciliation_by_municipality_gap=gap_mun)103(OUT / "results.json").write_text(json.dumps(res, indent=2) + "\n")104105with open(OUT / "analytic.csv", "w", newline="") as fh:106 w = csv.writer(fh)107 w.writerow(["sale_id", "municipality", "sale_date", "price_cad", "area_m2"])108 for r in valid:109 w.writerow([r["sale_id"], r["municipality"], r["sale_date"], r["price_cad"], r["area_m2"]])110111n1 = n_lines - reasons["field_count"]112n2 = n1 - reasons["field_format"]113steps = [114 (1, "parse with CSV quoting; require exactly 5 fields", n_lines, n1, "lines with wrong field count are garbled and cannot be read unambiguously"),115 (2, "require valid formats: sale_id S+5 digits; known municipality; ISO date; positive integer price (plain or strict thousands-separated); positive numeric area", n1, n2, "fields not unambiguously readable"),116 (3, "exclude duplicated sale_id (all copies)", n2, n_valid, "sale_id must be unique"),117]118with open(OUT / "exclusions.csv", "w", newline="") as fh:119 w = csv.writer(fh)120 w.writerow(["step", "rule", "n_before", "n_after", "n_removed", "rationale"])121 for s, rule, b, a, why in steps:122 w.writerow([s, rule, b, a, b - a, why])123124print(json.dumps({k: res[k] for k in ["n_valid_records", "n_unreadable_records", "sum_price_valid",125 "sum_price_unreadable_implied", "mean_price_valid",126 "median_price_per_m2_valid", "feasibility"]}, indent=1))127