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, datetime5from pathlib import Path6import numpy as np78DATA = Path("data"); OUT = Path("out"); OUT.mkdir(exist_ok=True)9COLS = ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]10meta = json.loads((DATA / "metadata.json").read_text())1112with open(DATA / "sales.csv", newline="", encoding="utf-8") as f:13 raw_lines = f.read().splitlines()14header, data_lines = raw_lines[0], [l for l in raw_lines[1:]]15assert header.split(",") == COLS, header16n_lines = len(data_lines)17parsed = list(csv.reader(data_lines))1819ID_RE = re.compile(r"S\d{5}")20PLAIN_INT = re.compile(r"\d+")21THOUSANDS = re.compile(r"\d{1,3}(,\d{3})+") # e.g. "280,000" (quoted in file)22AREA_RE = re.compile(r"\d+(\.\d+)?")2324def valid_date(s):25 try:26 datetime.date.fromisoformat(s); return bool(re.fullmatch(r"\d{4}-\d{2}-\d{2}", s))27 except ValueError:28 return False2930# Step 1: structural parse (exactly 5 fields)31s1 = [r for r in parsed if len(r) == 5]32# Step 2: every field matches its declared format (price plain integer OR33# integer with comma thousands separators), date valid, area positive number,34# municipality non-empty alphabetic35s2, n_thousands = [], 036for r in s1:37 sid, mun, d, p, a = r38 ok = (ID_RE.fullmatch(sid) and re.fullmatch(r"[A-Za-z][A-Za-z \-']*", mun)39 and valid_date(d) and (PLAIN_INT.fullmatch(p) or THOUSANDS.fullmatch(p))40 and AREA_RE.fullmatch(a) and float(a) > 0)41 if ok:42 if THOUSANDS.fullmatch(p): n_thousands += 143 s2.append([sid, mun, d, int(p.replace(",", "")), float(a), bool(THOUSANDS.fullmatch(p))])44# Step 3: duplicate sale_id removal (keep none ambiguous: drop all copies)45from collections import Counter46cnt = Counter(r[0] for r in s2)47s3 = [r for r in s2 if cnt[r[0]] == 1]4849ledger = [50 ("1", "row has exactly 5 CSV fields", n_lines, len(s1),51 "lines with wrong field count are corrupted export garbage; content cannot be read unambiguously"),52 ("2", "all fields match declared formats (S+5 digits id, municipality text, valid ISO date, integer price incl. quoted comma-thousands form, positive numeric area)", len(s1), len(s2),53 "fields that do not match their format cannot be read unambiguously"),54 ("3", "sale_id unique", len(s2), len(s3), "duplicate ids would be ambiguous"),55]56with open(OUT / "exclusions.csv", "w", newline="") as f:57 w = csv.writer(f); w.writerow(["step", "rule", "n_before", "n_after", "n_removed", "rationale"])58 for st, rule, b, a, rat in ledger:59 w.writerow([st, rule, b, a, b - a, rat])6061with open(OUT / "analytic.csv", "w", newline="") as f:62 w = csv.writer(f); w.writerow(COLS)63 for r in s3:64 w.writerow(r[:5])6566n_valid = len(s3)67price = np.array([r[3] for r in s3], dtype=np.int64)68area = np.array([r[4] for r in s3], dtype=float)69thou = np.array([r[5] for r in s3], dtype=bool)70n_ctrl = int(meta["n_records"]); sum_ctrl = int(meta["sum_price_cad"])71sum_valid = int(price.sum())72n_unread = n_ctrl - n_valid73n_bad_struct = n_lines - len(s1)74n_bad_fmt = len(s1) - len(s2)75n_dup = len(s2) - len(s3)7677# municipality reconciliation (diagnostic only)78mun_cnt = Counter(r[1] for r in s3)79mun_gap = {m: int(meta["n_by_municipality"].get(m, 0)) - mun_cnt.get(m, 0)80 for m in sorted(set(meta["n_by_municipality"]) | set(mun_cnt))}81ids = sorted(int(r[0][1:]) for r in s3)82missing_ids = sorted(set(range(1, n_ctrl + 1)) - set(ids))8384issues = []85if n_bad_struct:86 issues.append({"issue": f"{n_bad_struct} data lines (file lines {min(i+2 for i,r in enumerate(parsed) if len(r)!=5)}-{max(i+2 for i,r in enumerate(parsed) if len(r)!=5)}) are corrupted: garbage characters and wrong number of fields; sale_id sequence gap S{missing_ids[0]:05d}-S{missing_ids[-1]:05d} ({len(missing_ids)} ids) coincides",87 "handling": "excluded; not reconstructed or imputed; their count and implied price sum reported via n_unreadable_records and sum_price_unreadable_implied"})88if n_bad_fmt:89 issues.append({"issue": f"{n_bad_fmt} rows with 5 fields but a field not matching its format", "handling": "excluded"})90if n_thousands:91 issues.append({"issue": f"{n_thousands} rows store price_cad as a quoted string with comma thousands separators (e.g. \"280,000\")",92 "handling": "kept: quoting makes the field unambiguous under CSV rules and the pattern d{1,3}(,ddd)+ has a single integer reading; commas removed and converted to integer"})93if n_dup:94 issues.append({"issue": f"{n_dup} rows with duplicated sale_id", "handling": "excluded"})9596feas = "complete" if n_unread == 0 else "partial"97reason = None if feas == "complete" else (98 f"{n_unread} of the {n_ctrl} records in the provider's control count could not be analysed: "99 f"{n_bad_struct} data lines of sales.csv are corrupted (unreadable garbage, wrong field count), "100 f"{n_bad_fmt} rows had malformed fields and {n_dup} duplicated ids; these records were not reconstructed. "101 f"Their implied price sum is {sum_ctrl - sum_valid} CAD.")102103results = {104 "task_id": "F1-conf-04",105 "n_records_control": n_ctrl,106 "n_valid_records": n_valid,107 "n_unreadable_records": n_unread,108 "n_analysed": n_valid,109 "sum_price_control": sum_ctrl,110 "sum_price_valid": sum_valid,111 "sum_price_unreadable_implied": sum_ctrl - sum_valid,112 "mean_price_valid": round(float(price.mean()), 6),113 "median_price_per_m2_valid": round(float(np.median(price / area)), 6),114 "feasibility": feas,115 "feasibility_reason": reason,116 "data_issues": issues,117 "n_input_rows": {"sales.csv": n_lines},118 "random_seed": None,119 "diagnostics": {120 "n_lines_wrong_field_count": n_bad_struct,121 "n_rows_bad_format": n_bad_fmt,122 "n_rows_duplicate_id": n_dup,123 "n_price_thousands_separator_parsed": n_thousands,124 "municipality_count_gap_control_minus_valid": mun_gap,125 "n_missing_ids_in_sequence": len(missing_ids),126 "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 6) if n_unread else None,127 "sensitivity_excluding_thousands_separator_rows": {128 "n": int((~thou).sum()), "sum_price": int(price[~thou].sum()),129 "mean_price": round(float(price[~thou].mean()), 6),130 "median_price_per_m2": round(float(np.median(price[~thou] / area[~thou])), 6)},131 "sale_date_min": min(r[2] for r in s3), "sale_date_max": max(r[2] for r in s3),132 },133}134(OUT / "results.json").write_text(json.dumps(results, indent=2, ensure_ascii=False) + "\n")135print(f"n_lines={n_lines} n_valid={n_valid} n_unreadable={n_unread} sum_valid={sum_valid} "136 f"mean={results['mean_price_valid']} median_ppm2={results['median_price_per_m2_valid']} feas={feas}")137