1"""F1-conf-04: reconcile property-sales export to provider control totals.2Run from workspace root: ~/wp29/venv/bin/python out/analysis.py"""3import csv, json, re, datetime, statistics4from collections import Counter5import pandas as pd67meta = json.load(open("data/metadata.json"))8with open("data/sales.csv", newline="") as f:9 rows = list(csv.reader(f))10header, lines = rows[0], rows[1:]11assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]12n_lines = len(lines)1314ID = re.compile(r"S\d{5}")15PRICE_PLAIN = re.compile(r"\d+")16PRICE_THOUS = re.compile(r"\d{1,3}(,\d{3})+")17AREA = re.compile(r"\d+(\.\d+)?")18MUNIS = set(meta["n_by_municipality"])1920def date_ok(s):21 try:22 datetime.date.fromisoformat(s); return re.fullmatch(r"\d{4}-\d{2}-\d{2}", s) is not None23 except ValueError:24 return False2526steps = []27# Step 1: structural parse (exactly 5 fields, valid sale_id)28s1 = [r for r in lines if len(r) == 5 and ID.fullmatch(r[0])]29garbled_idx = [i + 2 for i, r in enumerate(lines) if not (len(r) == 5 and ID.fullmatch(r[0]))]30steps.append(("1", "line parses into exactly 5 CSV fields with sale_id matching S+5 digits",31 n_lines, len(s1), "garbled/corrupted export lines cannot be read unambiguously; not reconstructed"))32# Step 2: field validity33def valid(r):34 return (r[1] in MUNIS and date_ok(r[2])35 and (PRICE_PLAIN.fullmatch(r[3]) or PRICE_THOUS.fullmatch(r[3]))36 and AREA.fullmatch(r[4]) and float(r[4]) > 0)37s2 = [r for r in s1 if valid(r)]38steps.append(("2", "municipality in control list, valid ISO date, numeric price (plain or quoted thousands-separated), positive numeric area",39 len(s1), len(s2), "fields must be readable unambiguously"))40# Step 3: duplicate sale_id41seen, s3 = set(), []42for r in s2:43 if r[0] not in seen:44 seen.add(r[0]); s3.append(r)45steps.append(("3", "unique sale_id (drop exact duplicate ids)", len(s2), len(s3), "sale_id is documented as unique"))4647n_thous = sum(1 for r in s3 if PRICE_THOUS.fullmatch(r[3]))48df = pd.DataFrame(s3, columns=header)49df["price_cad"] = df["price_cad"].str.replace(",", "", regex=False).astype("int64")50df["area_m2"] = df["area_m2"].astype(float)51df.to_csv("out/analytic.csv", index=False)5253ex = pd.DataFrame([dict(step=s, rule=ru, n_before=b, n_after=a, n_removed=b - a, rationale=ra)54 for s, ru, b, a, ra in steps])55ex.to_csv("out/exclusions.csv", index=False)5657n_valid = len(df)58n_ctrl = meta["n_records"]59sum_valid = int(df["price_cad"].sum())60sum_ctrl = int(meta["sum_price_cad"])61ids = set(df["sale_id"])62missing = [f"S{i:05d}" for i in range(1, n_ctrl + 1) if f"S{i:05d}" not in ids]63by_muni = df["municipality"].value_counts().to_dict()64muni_gap = {m: meta["n_by_municipality"][m] - int(by_muni.get(m, 0)) for m in meta["n_by_municipality"]}65n_unread = n_ctrl - n_valid6667issues = []68if garbled_idx:69 issues.append({"issue": f"{len(garbled_idx)} data lines (file lines {garbled_idx[0]}-{garbled_idx[-1]}) are garbled/corrupted (random characters, wrong field count, invalid sale_id); sale_ids {missing[0]}-{missing[-1]} absent from readable rows" if missing else f"{len(garbled_idx)} garbled lines",70 "handling": "excluded as unreadable; not reconstructed or imputed; their implied price sum is derived only as control minus valid sum"})71if n_thous:72 issues.append({"issue": f"{n_thous} records have price_cad exported as a quoted string with comma thousands separators (e.g. \"280,000\")",73 "handling": "parsed unambiguously by removing the thousands separators (pattern d{1,3}(,ddd)+ enforced); records retained"})74if n_lines - len(s1) != n_unread:75 issues.append({"issue": "unreadable line count differs from control-count gap", "handling": "reported"})76issues.append({"issue": f"per-municipality shortfall versus control counts: {muni_gap}",77 "handling": "reported; shortfalls sum to the number of unreadable records"})7879import numpy as np80SEED = 2026103081rng = np.random.default_rng(SEED)82pr = df["price_cad"].to_numpy(float); ppm = (df["price_cad"] / df["area_m2"]).to_numpy()83B = 200084bm, bmed = [], []85for _ in range(B):86 i = rng.integers(0, n_valid, n_valid)87 bm.append(pr[i].mean()); bmed.append(np.median(ppm[i]))88boot = {"B": B, "mean_price_ci95": [round(float(x), 2) for x in np.percentile(bm, [2.5, 97.5])],89 "median_price_per_m2_ci95": [round(float(x), 2) for x in np.percentile(bmed, [2.5, 97.5])]}9091res = {92 "task_id": "F1-conf-04",93 "n_records_control": n_ctrl,94 "n_valid_records": n_valid,95 "n_unreadable_records": n_unread,96 "n_analysed": n_valid,97 "sum_price_control": sum_ctrl,98 "sum_price_valid": sum_valid,99 "sum_price_unreadable_implied": sum_ctrl - sum_valid,100 "mean_price_valid": round(float(df["price_cad"].mean()), 4),101 "median_price_per_m2_valid": round(float((df["price_cad"] / df["area_m2"]).median()), 4),102 "feasibility": "complete" if n_unread == 0 else "partial",103 "feasibility_reason": None if n_unread == 0 else104 f"{n_unread} of the {n_ctrl} control records could not be analysed: their export lines "105 f"(file lines {garbled_idx[0]}-{garbled_idx[-1]}, sale_ids {missing[0]}-{missing[-1]} by gap) are corrupted with random "106 f"characters and cannot be read unambiguously; they were excluded, not reconstructed. Their implied price sum is "107 f"{sum_ctrl - sum_valid} CAD (control minus valid).",108 "data_issues": issues,109 "n_input_rows": {"sales.csv": n_lines},110 "random_seed": SEED,111 "_diagnostics": {"bootstrap": boot, "mean_price_all_control_records": round(sum_ctrl / n_ctrl, 2), "municipality_shortfall": muni_gap, "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 2) if n_unread else None},112}113json.dump(res, open("out/results.json", "w"), indent=2)114print(json.dumps(res, indent=2))115