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, os5from datetime import date6import pandas as pd78DATA, OUT = "data", "out"9meta = json.load(open(os.path.join(DATA, "metadata.json")))10munis = set(meta["n_by_municipality"])1112with open(os.path.join(DATA, "sales.csv"), newline="") as f:13 lines = f.read().splitlines()14header, data_lines = lines[0], lines[1:]15assert header == "sale_id,municipality,sale_date,price_cad,area_m2"16n_lines = len(data_lines)17rows = list(csv.reader(data_lines)) # RFC-4180 parsing (handles quoted fields)1819ID_RE = re.compile(r"^S\d{5}$")20PRICE_PLAIN = re.compile(r"^\d+$")21PRICE_THOUS = re.compile(r"^\d{1,3}(,\d{3})+$")22AREA_RE = re.compile(r"^\d+(\.\d+)?$")2324def valid_date(s):25 try:26 return re.fullmatch(r"\d{4}-\d{2}-\d{2}", s) is not None and date.fromisoformat(s) is not None27 except ValueError:28 return False2930steps = []31def step(name, rule, keep, cur, rationale):32 new = [r for r in cur if keep(r)]33 steps.append(dict(step=name, rule=rule, n_before=len(cur), n_after=len(new),34 n_removed=len(cur) - len(new), rationale=rationale))35 return new3637cur = rows38cur = step("1_field_count", "row has exactly 5 CSV fields", lambda r: len(r) == 5, cur,39 "corrupted/garbled export lines cannot be mapped to columns; not reconstructed")40cur = step("2_sale_id", "sale_id matches ^S\\d{5}$", lambda r: bool(ID_RE.match(r[0])), cur,41 "identifier must be readable")42cur = step("3_municipality", "municipality is one of the provider's municipalities", lambda r: r[1] in munis, cur,43 "municipality must be readable")44cur = step("4_sale_date", "sale_date is a valid ISO date YYYY-MM-DD", lambda r: valid_date(r[2]), cur,45 "date must be readable")46cur = step("5_price", "price_cad is an integer, optionally with comma thousands separators inside a quoted field",47 lambda r: bool(PRICE_PLAIN.match(r[3]) or PRICE_THOUS.match(r[3])), cur,48 "price must be unambiguous; quoted '280,000' is unambiguous under CSV quoting")49cur = step("6_area", "area_m2 is a positive decimal number",50 lambda r: bool(AREA_RE.match(r[4])) and float(r[4]) > 0, cur, "area must be readable and positive")51seen = set()52def first(r):53 if r[0] in seen: return False54 seen.add(r[0]); return True55cur = step("7_duplicate_id", "keep first occurrence of each sale_id", first, cur, "sale_id must be unique")5657df = pd.DataFrame(cur, columns=["sale_id", "municipality", "sale_date", "price_cad", "area_m2"])58n_thous = int(df.price_cad.str.contains(",").sum())59df["price_cad"] = df.price_cad.str.replace(",", "", regex=False).astype("int64")60df["area_m2"] = df.area_m2.astype(float)61df.to_csv(os.path.join(OUT, "analytic.csv"), index=False)62pd.DataFrame(steps).to_csv(os.path.join(OUT, "exclusions.csv"), index=False)6364n_valid = len(df)65n_control = int(meta["n_records"])66sum_control = int(meta["sum_price_cad"])67sum_valid = int(df.price_cad.sum())68n_unread = n_control - n_valid69n_badfields = steps[0]["n_removed"]70other_removed = sum(s["n_removed"] for s in steps[1:])71by_muni_valid = df.municipality.value_counts().to_dict()72by_muni_gap = {m: int(meta["n_by_municipality"][m] - by_muni_valid.get(m, 0)) for m in sorted(munis)}7374issues = []75if n_badfields:76 bad_line_nos = [i + 2 for i, r in enumerate(rows) if len(r) != 5]77 issues.append({"issue": f"{n_badfields} data lines (file lines {min(bad_line_nos)}-{max(bad_line_nos)}) are garbled "78 "(random characters, wrong number of fields) and cannot be read",79 "handling": "excluded from the analytic dataset; not reconstructed or imputed; their count and implied "80 "price sum are reported via reconciliation with the control totals"})81if other_removed:82 issues.append({"issue": f"{other_removed} rows with 5 fields failed field-level validity checks",83 "handling": "excluded (see exclusions.csv)"})84if n_thous:85 issues.append({"issue": f"{n_thous} price_cad values are exported as quoted strings with comma thousands "86 "separators (e.g. \"280,000\")",87 "handling": "kept: the quoted field is unambiguous under CSV quoting; commas removed and parsed as integer CAD"})8889feas = "complete" if n_unread == 0 and n_valid == n_control else "partial"90reason = None if feas == "complete" else (91 f"{n_unread} of the {n_control} records in the provider's control count could not be analysed: "92 f"{n_badfields} export lines are garbled (corrupted characters / wrong field count) and cannot be read "93 f"unambiguously; they account for an implied {sum_control - sum_valid} CAD of the control price sum "94 f"(municipality gaps vs control: {by_muni_gap}).")9596res = {97 "task_id": "F1-conf-04",98 "n_records_control": n_control,99 "n_valid_records": n_valid,100 "n_unreadable_records": n_unread,101 "n_analysed": n_valid,102 "sum_price_control": sum_control,103 "sum_price_valid": sum_valid,104 "sum_price_unreadable_implied": sum_control - sum_valid,105 "mean_price_valid": round(float(df.price_cad.mean()), 4),106 "median_price_per_m2_valid": round(float((df.price_cad / df.area_m2).median()), 4),107 "feasibility": feas,108 "feasibility_reason": reason,109 "data_issues": issues,110 "n_input_rows": {"sales.csv": n_lines},111 "random_seed": None,112 "n_by_municipality_valid": {m: int(by_muni_valid.get(m, 0)) for m in sorted(munis)},113 "n_by_municipality_gap_vs_control": by_muni_gap,114 "mean_price_unreadable_implied": round((sum_control - sum_valid) / n_unread, 2) if n_unread else None,115 "sale_date_min_valid": df.sale_date.min(),116 "sale_date_max_valid": df.sale_date.max(),117}118json.dump(res, open(os.path.join(OUT, "results.json"), "w"), indent=2)119print(json.dumps({k: res[k] for k in ["n_valid_records", "n_unreadable_records", "sum_price_valid",120 "mean_price_valid", "median_price_per_m2_valid", "feasibility"]}))121