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.4No randomness is used (random_seed = null)."""5import csv, json, re, datetime, os6from collections import Counter7import numpy as np89DATA = "data"10OUT = "out"11os.makedirs(OUT, exist_ok=True)1213meta = json.load(open(os.path.join(DATA, "metadata.json")))14with open(os.path.join(DATA, "sales.csv"), newline="", encoding="utf-8") as f:15 rows = list(csv.reader(f))16header, lines = rows[0], rows[1:]17assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"], header18n_lines = len(lines)1920ID_RE = re.compile(r"S\d{5}")21PRICE_PLAIN = re.compile(r"\d+")22PRICE_THOUS = re.compile(r"\d{1,3}(,\d{3})+") # e.g. "280,000" (quoted field)23KNOWN_MUNI = set(meta["n_by_municipality"])242526def parse(r):27 """Return (parsed record, None) or (None, reason)."""28 if len(r) != 5:29 return None, f"wrong field count ({len(r)})"30 sid, muni, d, p, a = (x.strip() for x in r)31 if not ID_RE.fullmatch(sid):32 return None, "malformed sale_id"33 if muni not in KNOWN_MUNI:34 return None, "unknown municipality"35 if not re.fullmatch(r"\d{4}-\d{2}-\d{2}", d):36 return None, "invalid date"37 try:38 datetime.date.fromisoformat(d)39 except ValueError:40 return None, "invalid date"41 if PRICE_PLAIN.fullmatch(p):42 price, fmt = int(p), "plain"43 elif PRICE_THOUS.fullmatch(p):44 price, fmt = int(p.replace(",", "")), "thousands"45 else:46 return None, "invalid price"47 try:48 area = float(a)49 except ValueError:50 return None, "invalid area"51 if not (price > 0 and np.isfinite(area) and area > 0):52 return None, "non-positive price/area"53 return dict(sale_id=sid, municipality=muni, sale_date=d, price_cad=price,54 area_m2=area, _fmt=fmt), None555657parsed, failed = [], []58for lineno, r in enumerate(lines, start=2):59 rec, why = parse(r)60 if rec:61 parsed.append(rec)62 else:63 failed.append((lineno, why))6465# Step 2: duplicate sale_id among parsed records (ambiguous -> drop all copies)66idc = Counter(x["sale_id"] for x in parsed)67dups = {k for k, v in idc.items() if v > 1}68valid = [x for x in parsed if x["sale_id"] not in dups]69n_thous = sum(x["_fmt"] == "thousands" for x in valid)7071excl = [72 dict(step=1, rule="line parses into 5 well-formed fields (S+5-digit id; known municipality; ISO date; integer price, plain or quoted with comma thousands separators; positive numeric area)",73 n_before=n_lines, n_after=len(parsed), n_removed=n_lines - len(parsed),74 rationale="corrupted/garbled lines cannot be read unambiguously; not reconstructed (TASK rule)"),75 dict(step=2, rule="sale_id unique among parsed records",76 n_before=len(parsed), n_after=len(valid), n_removed=len(parsed) - len(valid),77 rationale="duplicate ids would make record identity ambiguous"),78]79with open(os.path.join(OUT, "exclusions.csv"), "w", newline="") as f:80 w = csv.DictWriter(f, fieldnames=list(excl[0]))81 w.writeheader()82 w.writerows(excl)8384cols = ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"]85with open(os.path.join(OUT, "analytic.csv"), "w", newline="") as f:86 w = csv.writer(f)87 w.writerow(cols)88 for x in valid:89 w.writerow([x[c] for c in cols])9091price = np.array([x["price_cad"] for x in valid], dtype=np.int64)92area = np.array([x["area_m2"] for x in valid], dtype=float)93n_valid = int(len(valid))94n_ctrl = int(meta["n_records"])95sum_ctrl = int(meta["sum_price_cad"])96sum_valid = int(price.sum())97n_unread = n_ctrl - n_valid9899muni_valid = Counter(x["municipality"] for x in valid)100muni_gap = {m: int(meta["n_by_municipality"][m] - muni_valid.get(m, 0)) for m in meta["n_by_municipality"]}101102reasons = Counter(why for _, why in failed)103bad_lines = [l for l, _ in failed]104missing_ids = sorted(set(f"S{i:05d}" for i in range(1, n_ctrl + 1)) - {x["sale_id"] for x in valid})105106issues = []107if failed:108 issues.append({109 "issue": f"{len(failed)} data lines of sales.csv (file lines {min(bad_lines)}-{max(bad_lines)}) are garbled/corrupted and cannot be parsed ({dict(sorted(reasons.items()))}); "110 f"{len(missing_ids)} sale_ids ({missing_ids[0]}-{missing_ids[-1]}) are absent from the readable records",111 "handling": "excluded from the analytic dataset (exclusion step 1); not reconstructed or imputed; their count and implied price sum are reported via n_unreadable_records and sum_price_unreadable_implied"})112if n_thous:113 issues.append({114 "issue": f"{n_thous} records have price_cad written as a quoted string with comma thousands separators (e.g. \"280,000\") instead of a plain integer",115 "handling": "the quoted field is unambiguous under CSV quoting; parsed as integer after removing the separators and kept in the analytic dataset"})116if dups:117 issues.append({"issue": f"{len(dups)} duplicated sale_id values", "handling": "all copies excluded (step 2)"})118if n_unread or sum_ctrl != sum_valid:119 issues.append({120 "issue": f"record count and price sum do not reconcile with the control totals: {n_unread} records and {sum_ctrl - sum_valid} CAD missing; per-municipality count gaps {muni_gap}",121 "handling": f"gap reported, not imputed; the per-municipality gaps sum to {sum(muni_gap.values())}, equal to the number of corrupted lines ({len(failed)})"})122123feas = "complete" if n_unread == 0 else ("partial" if n_valid > 0 else "infeasible")124reason = None if feas == "complete" else (125 f"{n_unread} of the {n_ctrl} records in the provider's control count could not be analysed: "126 f"{len(failed)} data lines of sales.csv are corrupted (garbled characters, wrong field counts) and cannot be read unambiguously, "127 f"so they were excluded and not reconstructed; the implied price sum of these records is {sum_ctrl - sum_valid} CAD.")128129results = {130 "task_id": "F1-conf-04",131 "n_records_control": n_ctrl,132 "n_valid_records": n_valid,133 "n_unreadable_records": int(n_unread),134 "n_analysed": n_valid,135 "sum_price_control": sum_ctrl,136 "sum_price_valid": sum_valid,137 "sum_price_unreadable_implied": int(sum_ctrl - sum_valid),138 "mean_price_valid": round(float(price.mean()), 6),139 "median_price_per_m2_valid": round(float(np.median(price / area)), 6),140 "feasibility": feas,141 "feasibility_reason": reason,142 "data_issues": issues,143 "n_input_rows": {"sales.csv": n_lines},144 "random_seed": None,145 # supplementary keys (not required by TASK.md)146 "n_parse_failures": len(failed),147 "n_price_thousands_separator": int(n_thous),148 "n_duplicate_ids": len(dups),149 "n_by_municipality_valid": dict(sorted(muni_valid.items())),150 "n_by_municipality_gap": muni_gap,151 "missing_sale_ids_range": [missing_ids[0], missing_ids[-1]] if missing_ids else None,152 "n_missing_sale_ids": len(missing_ids),153 "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 6) if n_unread else None,154 "price_min_valid": int(price.min()),155 "price_max_valid": int(price.max()),156 "date_min_valid": min(x["sale_date"] for x in valid),157 "date_max_valid": max(x["sale_date"] for x in valid),158}159with open(os.path.join(OUT, "results.json"), "w") as f:160 json.dump(results, f, indent=2)161print(json.dumps({k: results[k] for k in ["n_input_rows", "n_valid_records", "n_unreadable_records",162 "sum_price_valid", "sum_price_unreadable_implied", "mean_price_valid",163 "median_price_per_m2_valid", "feasibility"]}, indent=1))164