1"""F1-conf-04: reconcile property-sales export to 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, re5from collections import Counter6from datetime import date7from pathlib import Path8import numpy as np910DATA, OUT = Path("data"), Path("out")11meta = json.loads((DATA / "metadata.json").read_text())1213with open(DATA / "sales.csv", newline="", encoding="utf-8") as f:14 rows = list(csv.reader(f))15header, lines = rows[0], rows[1:]16assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]17n_lines = len(lines)18munis = set(meta["n_by_municipality"])19ID = re.compile(r"S\d{5}")20PLAIN = re.compile(r"\d+")21THOUS = re.compile(r"\d{1,3}(,\d{3})+")22AREA = re.compile(r"\d+(\.\d+)?")23DATE = re.compile(r"\d{4}-\d{2}-\d{2}")2425def parse(r):26 """Return (record, used_thousands_sep) or None if not unambiguously readable."""27 sid, mun, d, p, a = r28 if not ID.fullmatch(sid) or mun not in munis or not AREA.fullmatch(a) or not DATE.fullmatch(d):29 return None30 try:31 dd = date.fromisoformat(d)32 except ValueError:33 return None34 if PLAIN.fullmatch(p):35 price, sep = int(p), False36 elif THOUS.fullmatch(p):37 price, sep = int(p.replace(",", "")), True38 else:39 return None40 area = float(a)41 if area <= 0 or price <= 0:42 return None43 return {"sale_id": sid, "municipality": mun, "sale_date": dd.isoformat(),44 "price_cad": price, "area_m2": area}, sep4546# Step 1: structurally malformed lines (field count != 5)47s1 = [r for r in lines if len(r) == 5]48# Step 2: rows with 5 fields but some field not unambiguously readable49s2 = [p for p in (parse(r) for r in s1) if p is not None]50n_sep = sum(p[1] for p in s2)51recs = [p[0] for p in s2]52# Step 3: duplicate sale_id -> ambiguous, drop all copies53cnt = Counter(r["sale_id"] for r in recs)54valid = [r for r in recs if cnt[r["sale_id"]] == 1]5556excl = [57 ("1", "line must split into exactly 5 CSV fields", n_lines, len(s1),58 "corrupted export lines (random characters, wrong field count); cannot be read, not reconstructed"),59 ("2", "all fields parse: sale_id S+5 digits, known municipality, ISO date, integer price (plain or comma-thousands), positive numeric area",60 len(s1), len(s2), "fields not unambiguously readable"),61 ("3", "sale_id unique", len(s2), len(valid), "duplicated ids would be ambiguous"),62]63with open(OUT / "exclusions.csv", "w", newline="") as f:64 w = csv.writer(f)65 w.writerow(["step", "rule", "n_before", "n_after", "n_removed", "rationale"])66 for s, rule, nb, na, rat in excl:67 w.writerow([s, rule, nb, na, nb - na, rat])6869with open(OUT / "analytic.csv", "w", newline="") as f:70 w = csv.DictWriter(f, fieldnames=["sale_id", "municipality", "sale_date", "price_cad", "area_m2"])71 w.writeheader()72 w.writerows(valid)7374price = np.array([r["price_cad"] for r in valid], dtype=np.int64)75area = np.array([r["area_m2"] for r in valid], dtype=float)76n_valid = len(valid)77n_ctrl = int(meta["n_records"])78sum_ctrl = int(meta["sum_price_cad"])79sum_valid = int(price.sum())80n_unread = n_ctrl - n_valid81present = {int(r["sale_id"][1:]) for r in valid}82missing_ids = sorted(set(range(1, n_ctrl + 1)) - present)83muni_valid = Counter(r["municipality"] for r in valid)84muni_short = {m: meta["n_by_municipality"][m] - muni_valid.get(m, 0) for m in meta["n_by_municipality"]}8586n_malformed = n_lines - len(s1)87issues = []88if n_malformed:89 issues.append({"issue": f"{n_malformed} data lines of sales.csv are corrupted (random characters, field counts other than 5); "90 f"sale_ids S{missing_ids[0]:05d}-S{missing_ids[-1]:05d} ({len(missing_ids)} ids) are absent from readable rows",91 "handling": "excluded from the analytic dataset; not reconstructed or imputed; reported as unreadable records"})92if len(s1) - len(s2):93 issues.append({"issue": f"{len(s1)-len(s2)} rows with 5 fields have unreadable field values", "handling": "excluded"})94if n_sep:95 issues.append({"issue": f"{n_sep} price_cad values are CSV-quoted with a comma thousands separator (e.g. \"280,000\")",96 "handling": "parsed as integers after removing commas (pattern \\d{1,3}(,\\d{3})+ is unambiguous for integer CAD prices); kept"})97if len(s2) - n_valid:98 issues.append({"issue": f"{len(s2)-n_valid} rows with duplicated sale_id", "handling": "excluded"})99issues.append({"issue": "record count and price sum of readable rows fall short of provider control totals; per-municipality shortfall "100 + ", ".join(f"{m} {v}" for m, v in muni_short.items()),101 "handling": "reported as partial feasibility; shortfall sum attributed to unreadable records as an implied total only"})102103feas = "complete" if n_unread == 0 else "partial"104reason = None if feas == "complete" else (105 f"{n_unread} of the {n_ctrl} records in the provider's control count could not be analysed: "106 f"{n_malformed} lines of sales.csv are corrupted export lines (random characters, wrong number of fields) "107 f"whose fields cannot be read unambiguously, so they were excluded and not reconstructed. "108 f"The implied price total of the missing records is {sum_ctrl - sum_valid} CAD.")109110results = {111 "task_id": "F1-conf-04",112 "n_records_control": n_ctrl,113 "n_valid_records": n_valid,114 "n_unreadable_records": n_unread,115 "n_analysed": n_valid,116 "sum_price_control": sum_ctrl,117 "sum_price_valid": sum_valid,118 "sum_price_unreadable_implied": sum_ctrl - sum_valid,119 "mean_price_valid": round(float(price.mean()), 4),120 "median_price_per_m2_valid": round(float(np.median(price / area)), 4),121 "feasibility": feas,122 "feasibility_reason": reason,123 "data_issues": issues,124 "n_input_rows": {"sales.csv": n_lines},125 "random_seed": None,126 "n_malformed_lines": n_malformed,127 "n_price_thousands_separator_parsed": n_sep,128 "n_valid_by_municipality": dict(sorted(muni_valid.items())),129 "n_shortfall_by_municipality": muni_short,130}131(OUT / "results.json").write_text(json.dumps(results, indent=2) + "\n")132print(json.dumps({k: results[k] for k in ["n_valid_records", "n_unreadable_records", "sum_price_valid",133 "sum_price_unreadable_implied", "mean_price_valid",134 "median_price_per_m2_valid", "feasibility"]}, indent=1))135