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 Counter56DATA = "data/sales.csv"7meta = json.load(open("data/metadata.json"))8with open(DATA, newline="", encoding="utf-8") as f:9 raw_lines = f.read().splitlines()10n_lines = len(raw_lines) - 1 # data lines excluding header11with open(DATA, newline="", encoding="utf-8") as f:12 rdr = csv.reader(f)13 header = next(rdr)14 rows = [(rdr.line_num, r) for r in rdr]15assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]16assert len(rows) == n_lines # no multi-line quoted fields1718MUNIS = set(meta["n_by_municipality"])19ID_RE = re.compile(r"S\d{5}")20PRICE_RE = re.compile(r"\d+|\d{1,3}(,\d{3})+")21NUM_RE = re.compile(r"\d+(\.\d+)?")2223def check_date(s):24 try:25 datetime.date.fromisoformat(s); return re.fullmatch(r"\d{4}-\d{2}-\d{2}", s) is not None26 except ValueError:27 return False2829steps = []30cur = rows31def step(name, rule, keep_fn, rationale):32 global cur33 before = len(cur)34 cur = [x for x in cur if keep_fn(x[1])]35 steps.append(dict(step=name, rule=rule, n_before=before, n_after=len(cur),36 n_removed=before - len(cur), rationale=rationale))3738bad_lines = [ln for ln, r in rows if not (len(r) == 5 and ID_RE.fullmatch(r[0]))]39step("1", "row parses into exactly 5 CSV fields", lambda r: len(r) == 5,40 "rows with wrong field count are corrupted (garbled bytes); content cannot be read unambiguously")41step("2", "sale_id matches S + 5 digits", lambda r: ID_RE.fullmatch(r[0]) is not None,42 "malformed identifier indicates a corrupted record")43step("3", "municipality in control-total list", lambda r: r[1] in MUNIS,44 "unknown municipality indicates a corrupted record")45step("4", "sale_date is a valid ISO date", lambda r: check_date(r[2]), "unreadable date")46step("5", "price_cad is a positive integer (optionally with thousands separators inside quotes)",47 lambda r: PRICE_RE.fullmatch(r[3]) is not None and int(r[3].replace(",", "")) > 0, "unreadable price")48step("6", "area_m2 is a positive number", lambda r: NUM_RE.fullmatch(r[4]) is not None and float(r[4]) > 0,49 "unreadable area")50ids = [r[0] for _, r in cur]51step("7", "sale_id unique", lambda r, c=Counter(ids): c[r[0]] == 1, "duplicate records would double count")5253n_sep = sum(1 for _, r in cur if "," in r[3])54recs = [dict(sale_id=r[0], municipality=r[1], sale_date=r[2],55 price_cad=int(r[3].replace(",", "")), area_m2=float(r[4])) for _, r in cur]56n_valid = len(recs)57sum_valid = sum(x["price_cad"] for x in recs)58mean_valid = sum_valid / n_valid59med_ppm2 = statistics.median(x["price_cad"] / x["area_m2"] for x in recs)60n_ctrl = meta["n_records"]; sum_ctrl = meta["sum_price_cad"]61n_unread = n_ctrl - n_valid62by_muni = Counter(x["municipality"] for x in recs)63muni_gap = {m: meta["n_by_municipality"][m] - by_muni.get(m, 0) for m in meta["n_by_municipality"]}64present = sorted(int(x["sale_id"][1:]) for x in recs)65missing_ids = [i for i in range(1, n_ctrl + 1) if i not in set(present)]66id_rng = f"S{missing_ids[0]:05d}-S{missing_ids[-1]:05d}" if missing_ids else "none"67contiguous = bool(missing_ids) and missing_ids == list(range(missing_ids[0], missing_ids[-1] + 1))6869issues = []70if bad_lines:71 issues.append(dict(issue=f"{len(bad_lines)} data lines (file lines {bad_lines[0]}-{bad_lines[-1]}) are corrupted/garbled "72 f"(wrong field count, malformed sale_id, non-numeric content)",73 handling="excluded as unreadable; not reconstructed or imputed; their price total is only known "74 "implicitly via the control sum"))75if n_sep:76 issues.append(dict(issue=f"{n_sep} records have price_cad written as a quoted string with a thousands separator (e.g. \"280,000\")",77 handling="read unambiguously as integers after removing the comma separator (standard CSV quoting); retained"))78if missing_ids:79 issues.append(dict(issue=f"sale_id values {id_rng} ({len(missing_ids)} ids, contiguous={contiguous}) are absent from the readable records",80 handling="consistent with the corrupted block; reported only, ids not assigned to the garbled lines"))81issues.append(dict(issue=f"readable record count by municipality falls short of provider control: {muni_gap}",82 handling="shortfall sums to the number of unreadable records; reported, no adjustment"))8384feas = "complete" if n_unread == 0 else "partial"85reason = None if feas == "complete" else (86 f"{n_unread} of {n_ctrl} control records could not be analysed: {len(bad_lines)} lines of sales.csv "87 f"(file lines {bad_lines[0]}-{bad_lines[-1]}) are corrupted export rows whose fields cannot be read unambiguously; "88 f"they were excluded, not reconstructed. Implied unreadable price sum = {sum_ctrl - sum_valid} CAD.")8990res = dict(task_id="F1-conf-04", n_records_control=n_ctrl, n_valid_records=n_valid,91 n_unreadable_records=n_unread, n_analysed=n_valid, sum_price_control=sum_ctrl,92 sum_price_valid=sum_valid, sum_price_unreadable_implied=sum_ctrl - sum_valid,93 mean_price_valid=round(mean_valid, 4), median_price_per_m2_valid=round(med_ppm2, 4),94 feasibility=feas, feasibility_reason=reason, data_issues=issues,95 n_input_rows={"sales.csv": n_lines}, random_seed=None,96 n_by_municipality_valid=dict(by_muni), n_by_municipality_shortfall=muni_gap)97json.dump(res, open("out/results.json", "w"), indent=2)98with open("out/analytic.csv", "w", newline="") as f:99 w = csv.DictWriter(f, fieldnames=["sale_id", "municipality", "sale_date", "price_cad", "area_m2"])100 w.writeheader(); w.writerows(recs)101with open("out/exclusions.csv", "w", newline="") as f:102 w = csv.DictWriter(f, fieldnames=["step", "rule", "n_before", "n_after", "n_removed", "rationale"])103 w.writeheader(); w.writerows(steps)104print(json.dumps(res, indent=2)); print(steps)105