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, re4from datetime import date5import numpy as np, pandas as pd67DATA, OUT = "data/sales.csv", "out"8meta = json.load(open("data/metadata.json"))9COLS = ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]10ID_RE = re.compile(r"^S\d{5}$")11NUM_RE = re.compile(r"^\d+(\.\d+)?$")12THOUS_RE = re.compile(r"^\d{1,3}(,\d{3})+$")13MUNIS = set(meta["n_by_municipality"])1415with open(DATA, newline="", encoding="utf-8") as f:16 lines = f.read().splitlines()17header, body = lines[0], lines[1:]18assert next(csv.reader([header])) == COLS19n_lines = len(body)2021rows, bad, n_thousands = [], [], 022for ln, raw in enumerate(body, start=2):23 try:24 fields = next(csv.reader([raw]))25 except Exception:26 fields = None27 reason = None28 if fields is None or len(fields) != 5:29 reason = "wrong field count"30 else:31 sid, muni, sdate, price, area = [x.strip() for x in fields]32 if not ID_RE.match(sid): reason = "bad sale_id"33 elif muni not in MUNIS: reason = "unknown municipality"34 else:35 try: date.fromisoformat(sdate)36 except ValueError: reason = "bad date"37 if reason is None:38 if THOUS_RE.match(price):39 n_thousands += 1; price = price.replace(",", "")40 if not NUM_RE.match(price) or not NUM_RE.match(area): reason = "non-numeric price/area"41 elif float(area) <= 0: reason = "non-positive area"42 if reason:43 bad.append((ln, raw, reason)); continue44 rows.append(dict(sale_id=sid, municipality=muni, sale_date=sdate,45 price_cad=int(round(float(price))), area_m2=float(area)))4647df = pd.DataFrame(rows, columns=COLS)48n_parsed = len(df)49dup = df.sale_id.duplicated(keep=False)50n_dup = int(dup.sum())51df = df[~dup].reset_index(drop=True)52n_valid = len(df)5354ledger = [55 dict(step=1, rule="data lines read (excluding header)", n_before=n_lines, n_after=n_lines, n_removed=0,56 rationale="all non-header lines of sales.csv"),57 dict(step=2, rule="drop lines that cannot be parsed unambiguously (CSV field count != 5, sale_id not S+5 digits, unknown municipality, invalid ISO date, non-numeric price/area)",58 n_before=n_lines, n_after=n_parsed, n_removed=n_lines - n_parsed,59 rationale="corrupted/garbled export lines; content not reconstructed per task rules"),60 dict(step=3, rule="drop records whose sale_id is duplicated", n_before=n_parsed, n_after=n_valid, n_removed=n_dup,61 rationale="sale_id must be unique; duplicates would be ambiguous"),62]63pd.DataFrame(ledger).to_csv(f"{OUT}/exclusions.csv", index=False)64df.to_csv(f"{OUT}/analytic.csv", index=False)6566n_ctrl, s_ctrl = int(meta["n_records"]), int(meta["sum_price_cad"])67s_valid = int(df.price_cad.sum())68n_unread = n_ctrl - n_valid69bad_ids = [l for l, _, _ in bad]70missing_ids = sorted(set(f"S{i:05d}" for i in range(1, n_ctrl + 1)) - set(df.sale_id))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 meta["n_by_municipality"]}7374issues = []75if bad:76 issues.append(dict(issue=f"{len(bad)} data lines (file lines {min(bad_ids)}-{max(bad_ids)}) are garbled/corrupted and cannot be parsed into the 5 intended fields; "77 f"sale_ids absent from the analytic data: {missing_ids[0]}..{missing_ids[-1]} ({len(missing_ids)} ids)" if missing_ids else "",78 handling="excluded; not reconstructed or imputed; their implied count and price sum are derived from the control totals"))79if n_thousands:80 issues.append(dict(issue=f"{n_thousands} records have price_cad written with a thousands separator inside CSV quotes (e.g. \"280,000\")",81 handling="quoted field is unambiguous under standard CSV quoting; comma removed and value parsed as integer CAD; records kept"))82issues.append(dict(issue=f"record count reconciliation: control {n_ctrl} vs {n_valid} analysable (gap {n_unread}); municipality gaps {by_muni_gap}",83 handling="reported as partial feasibility; gap equals number of unreadable lines" if n_unread == len(bad) + n_dup else "reported; gap not fully explained by unreadable lines"))84issues.append(dict(issue=f"price sum reconciliation: control {s_ctrl} vs analysable {s_valid}; implied unreadable sum {s_ctrl - s_valid}",85 handling="reported; cannot be verified record-by-record because the corresponding lines are unreadable"))86if n_dup:87 issues.append(dict(issue=f"{n_dup} records with duplicated sale_id", handling="all copies excluded"))8889feas = "complete" if n_unread == 0 else "partial"90reason = None if feas == "complete" else (91 f"{n_unread} of the {n_ctrl} records in the provider's control count could not be analysed: "92 f"{len(bad)} export lines (file lines {min(bad_ids)}-{max(bad_ids)}, corresponding to sale_ids {missing_ids[0]}-{missing_ids[-1]}) are corrupted/garbled "93 f"and cannot be read unambiguously" + (f"; {n_dup} duplicated-id records excluded" if n_dup else "") +94 f". Their implied price sum is {s_ctrl - s_valid} CAD (control minus analysable).")9596SEED = 2026103097rng = np.random.default_rng(SEED)98ppm = (df.price_cad / df.area_m2).to_numpy()99pr = df.price_cad.to_numpy(dtype=float)100se_mean = float(pr.std(ddof=1) / np.sqrt(len(pr)))101boot_med = np.array([np.median(rng.choice(ppm, len(ppm))) for _ in range(2000)])102med_ci = [float(x) for x in np.percentile(boot_med, [2.5, 97.5])]103res = dict(task_id="F1-conf-04", n_records_control=n_ctrl, n_valid_records=n_valid, n_unreadable_records=n_unread,104 n_analysed=n_valid, sum_price_control=s_ctrl, sum_price_valid=s_valid,105 sum_price_unreadable_implied=s_ctrl - s_valid,106 mean_price_valid=float(df.price_cad.mean()),107 median_price_per_m2_valid=float(np.median(df.price_cad / df.area_m2)),108 feasibility=feas, feasibility_reason=reason, data_issues=issues,109 n_input_rows={"sales.csv": n_lines}, random_seed=SEED, se_mean_price_valid=se_mean,110 ci95_mean_price_valid=[float(pr.mean() - 1.96 * se_mean), float(pr.mean() + 1.96 * se_mean)],111 ci95_median_price_per_m2_valid_bootstrap=med_ci,112 implied_mean_price_unreadable=float((s_ctrl - s_valid) / n_unread) if n_unread else None,113 n_by_municipality_valid={k: int(v) for k, v in by_muni_valid.items()},114 n_by_municipality_gap=by_muni_gap)115json.dump(res, open(f"{OUT}/results.json", "w"), indent=2)116print(json.dumps(res, indent=2))117