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, datetime4import pandas as pd56DATA, OUT = "data", "out"7meta = json.load(open(f"{DATA}/metadata.json"))8with open(f"{DATA}/sales.csv", newline="", encoding="utf-8") 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 = re.compile(r"S\d{5}")15MUNIS = set(meta["n_by_municipality"])1617def valid_date(s):18 try:19 datetime.date.fromisoformat(s); return re.fullmatch(r"\d{4}-\d{2}-\d{2}", s) is not None20 except ValueError:21 return False2223# Step 1: structurally readable (5 fields, valid sale_id)24s1 = [r for r in lines if len(r) == 5 and ID_RE.fullmatch(r[0])]25# Step 2: all fields parse unambiguously26PRICE_RE = re.compile(r"\d{1,3}(,\d{3})+|\d+")27def fields_ok(r):28 return (r[1] in MUNIS and valid_date(r[2]) and PRICE_RE.fullmatch(r[3]) is not None29 and re.fullmatch(r"\d+(\.\d+)?", r[4]) is not None and float(r[4]) > 0)30s2 = [r for r in s1 if fields_ok(r)]31# Step 3: deduplicate sale_id (keep none if conflicting duplicates)32ids = pd.Series([r[0] for r in s2])33dup = set(ids[ids.duplicated(keep=False)])34s3 = [r for r in s2 if r[0] not in dup]3536n_thousands_sep = sum(1 for r in s3 if "," in r[3])37df = pd.DataFrame(s3, columns=header)38df["price_cad"] = df["price_cad"].str.replace(",", "", regex=False).astype("int64")39df["area_m2"] = df["area_m2"].astype(float)40df = df.sort_values("sale_id").reset_index(drop=True)41df.to_csv(f"{OUT}/analytic.csv", index=False)4243steps = [44 ("1", "keep lines with exactly 5 CSV fields and sale_id matching S+5 digits", n_lines, len(s1),45 "garbled/corrupted lines cannot be read unambiguously; not reconstructed"),46 ("2", "keep rows whose municipality, ISO date, price and area all parse", len(s1), len(s2),47 "fields must be readable unambiguously"),48 ("3", "drop rows with duplicated sale_id", len(s2), len(s3), "sale_id must be unique"),49]50pd.DataFrame([dict(step=a, rule=b, n_before=c, n_after=d, n_removed=c - d, rationale=e)51 for a, b, c, d, e in steps]).to_csv(f"{OUT}/exclusions.csv", index=False)5253n_ctrl, sum_ctrl = int(meta["n_records"]), int(meta["sum_price_cad"])54n_valid = len(df); sum_valid = int(df["price_cad"].sum())55n_unread = n_ctrl - n_valid56by_muni = df["municipality"].value_counts().to_dict()57muni_gap = {m: int(meta["n_by_municipality"][m] - by_muni.get(m, 0)) for m in meta["n_by_municipality"]}5859present = set(df["sale_id"])60missing = [f"S{i:05d}" for i in range(1, n_ctrl + 1) if f"S{i:05d}" not in present]61issues = []62if n_lines - len(s1):63 issues.append({"issue": f"{n_lines - len(s1)} data lines in sales.csv are garbled (random characters, wrong field count, malformed sale_id); "64 f"unreadable records per municipality implied by control totals: {muni_gap}",65 "handling": "excluded; not reconstructed or imputed; their implied price sum reported as sum_price_unreadable_implied"})66if n_thousands_sep:67 issues.append({"issue": f"{n_thousands_sep} readable records have price_cad written as a quoted string with comma thousands separators (e.g. \"280,000\")",68 "handling": "parsed unambiguously by removing the thousands separator (CSV quoting makes the field boundary unambiguous); retained"})69if len(s1) - len(s2):70 issues.append({"issue": f"{len(s1)-len(s2)} structurally readable rows had unparseable fields", "handling": "excluded"})71if len(s2) - len(s3):72 issues.append({"issue": f"{len(s2)-len(s3)} rows with duplicated sale_id", "handling": "excluded"})7374res = {75 "task_id": "F1-conf-04",76 "n_records_control": n_ctrl,77 "n_valid_records": n_valid,78 "n_unreadable_records": n_unread,79 "n_analysed": n_valid,80 "sum_price_control": sum_ctrl,81 "sum_price_valid": sum_valid,82 "sum_price_unreadable_implied": sum_ctrl - sum_valid,83 "mean_price_valid": round(float(df["price_cad"].mean()), 2),84 "median_price_per_m2_valid": round(float((df["price_cad"] / df["area_m2"]).median()), 4),85 "feasibility": "complete" if n_unread == 0 else "partial",86 "feasibility_reason": None if n_unread == 0 else87 (f"{n_unread} of the {n_ctrl} records in the provider's control count could not be analysed: "88 f"their lines in sales.csv are garbled (corrupted characters / wrong field count / malformed sale_id) and cannot be read "89 f"unambiguously; they were not reconstructed. Their implied price sum is {sum_ctrl - sum_valid} CAD."),90 "data_issues": issues,91 "n_input_rows": {"sales.csv": n_lines},92 "random_seed": None,93 "n_by_municipality_valid": {k: int(v) for k, v in sorted(by_muni.items())},94 "n_by_municipality_unreadable_implied": muni_gap,95 "missing_sale_ids_range": [missing[0], missing[-1]] if missing else None,96 "n_missing_sale_ids": len(missing),97 "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 2) if n_unread else None,98 "mean_price_control_implied": round(sum_ctrl / n_ctrl, 2),99}100json.dump(res, open(f"{OUT}/results.json", "w"), indent=2)101print(json.dumps(res, indent=2))102