1"""F1-conf-04: summarise sales export and reconcile 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, statistics6from pathlib import Path78DATA = Path("data"); OUT = Path("out"); OUT.mkdir(exist_ok=True)9meta = json.loads((DATA / "metadata.json").read_text())10KNOWN_MUNI = set(meta["n_by_municipality"])1112with open(DATA / "sales.csv", newline="", encoding="utf-8") as f:13 lines = list(csv.reader(f))14header, body = lines[0], lines[1:]15assert header == ["sale_id", "municipality", "sale_date", "price_cad", "area_m2"], header16n_lines = len(body)1718PRICE_PLAIN = re.compile(r"\d+")19PRICE_THOUS = re.compile(r"\d{1,3}(,\d{3})+") # e.g. "280,000" inside CSV quotes20AREA = re.compile(r"\d+(\.\d+)?")2122def parse_price(s):23 if PRICE_PLAIN.fullmatch(s) or PRICE_THOUS.fullmatch(s):24 return int(s.replace(",", ""))25 return None2627ledger = []28def step(name, rule, before, after, why):29 ledger.append(dict(step=name, rule=rule, n_before=len(before), n_after=len(after),30 n_removed=len(before) - len(after), rationale=why))3132# Step 1: structural readability (exactly 5 fields and a well-formed sale_id)33s1 = [r for r in body if len(r) == 5 and re.fullmatch(r"S\d{5}", r[0])]34step("1_structure", "line has exactly 5 CSV fields and sale_id matches S+5 digits",35 body, s1, "corrupted/garbled export lines cannot be read unambiguously; not reconstructed")3637# Step 2: field validity38def valid(r):39 _, muni, d, p, a = r40 if muni not in KNOWN_MUNI: return False41 try: datetime.date.fromisoformat(d)42 except ValueError: return False43 if parse_price(p) is None or parse_price(p) <= 0: return False44 if not AREA.fullmatch(a) or float(a) <= 0: return False45 return True46s2 = [r for r in s1 if valid(r)]47step("2_fields", "municipality in control list, ISO date, positive integer price (plain or quoted thousands-separated), positive numeric area",48 s1, s2, "fields must be readable unambiguously")4950# Step 3: duplicate sale_id51seen, s3 = set(), []52for r in s2:53 if r[0] not in seen:54 seen.add(r[0]); s3.append(r)55step("3_dedup", "drop repeated sale_id (keep first)", s2, s3, "sale_id must be unique")5657n_thous = sum(1 for r in s3 if PRICE_THOUS.fullmatch(r[3]))58recs = [dict(sale_id=r[0], municipality=r[1], sale_date=r[2],59 price_cad=parse_price(r[3]), area_m2=float(r[4])) for r in s3]6061with open(OUT / "analytic.csv", "w", newline="") as f:62 w = csv.DictWriter(f, fieldnames=["sale_id", "municipality", "sale_date", "price_cad", "area_m2"])63 w.writeheader(); w.writerows(recs)64with open(OUT / "exclusions.csv", "w", newline="") as f:65 w = csv.DictWriter(f, fieldnames=["step", "rule", "n_before", "n_after", "n_removed", "rationale"])66 w.writeheader(); w.writerows(ledger)6768n_valid = len(recs)69n_ctrl = int(meta["n_records"]); sum_ctrl = int(meta["sum_price_cad"])70sum_valid = sum(r["price_cad"] for r in recs)71n_unread = n_ctrl - n_valid72mean_p = sum_valid / n_valid73med_ppm2 = statistics.median(r["price_cad"] / r["area_m2"] for r in recs)7475ids = {int(r["sale_id"][1:]) for r in recs}76missing = sorted(set(range(1, n_ctrl + 1)) - ids)77miss_rng = f"S{missing[0]:05d}-S{missing[-1]:05d}" if missing else "none"78contig = bool(missing) and missing == list(range(missing[0], missing[-1] + 1))79n_by_muni_valid = {m: sum(1 for r in recs if r["municipality"] == m) for m in meta["n_by_municipality"]}80gap_by_muni = {m: meta["n_by_municipality"][m] - n_by_muni_valid[m] for m in n_by_muni_valid}8182n_struct = ledger[0]["n_removed"]83issues = []84if n_struct:85 issues.append({"issue": f"{n_struct} data lines (sale_id numbers {miss_rng}{', a contiguous block' if contig else ''}) are garbled: wrong number of fields and/or non-conforming sale_id with random characters",86 "handling": "excluded as unreadable; not reconstructed or imputed; their implied price total is reported as sum_price_unreadable_implied"})87if n_thous:88 issues.append({"issue": f"{n_thous} records store price_cad as a quoted string with thousands separators (e.g. \"280,000\")",89 "handling": "read unambiguously by removing the comma separators (standard CSV quoting); records retained"})90if ledger[1]["n_removed"]:91 issues.append({"issue": f"{ledger[1]['n_removed']} structurally readable lines have invalid field values", "handling": "excluded"})92if ledger[2]["n_removed"]:93 issues.append({"issue": f"{ledger[2]['n_removed']} duplicate sale_id", "handling": "duplicates dropped, first kept"})9495feas = "complete" if n_unread == 0 and sum_valid == sum_ctrl else "partial"96reason = None if feas == "complete" else (97 f"{n_unread} of the {n_ctrl} records in the provider's control totals could not be analysed: "98 f"{n_struct} export lines (sale_id {miss_rng}) are corrupted with garbled characters and wrong field counts, "99 f"so their fields cannot be read unambiguously; they account for an implied {sum_ctrl - sum_valid} CAD of the control price sum. "100 f"Per-municipality shortfall vs control: {gap_by_muni}.")101102res = {103 "task_id": "F1-conf-04",104 "n_records_control": n_ctrl,105 "n_valid_records": n_valid,106 "n_unreadable_records": n_unread,107 "n_analysed": n_valid,108 "sum_price_control": sum_ctrl,109 "sum_price_valid": int(sum_valid),110 "sum_price_unreadable_implied": int(sum_ctrl - sum_valid),111 "mean_price_valid": round(mean_p, 6),112 "median_price_per_m2_valid": round(med_ppm2, 6),113 "feasibility": feas,114 "feasibility_reason": reason,115 "data_issues": issues,116 "n_input_rows": {"sales.csv": n_lines},117 "random_seed": None,118 # supplementary keys119 "baseline_n_lines_minus_control": n_lines - n_ctrl,120 "n_price_thousands_separator": n_thous,121 "n_by_municipality_valid": n_by_muni_valid,122 "n_by_municipality_shortfall": gap_by_muni,123 "mean_price_unreadable_implied": round((sum_ctrl - sum_valid) / n_unread, 6) if n_unread else None,124 "sale_date_min_valid": min(r["sale_date"] for r in recs),125 "sale_date_max_valid": max(r["sale_date"] for r in recs),126}127(OUT / "results.json").write_text(json.dumps(res, indent=2, ensure_ascii=False) + "\n")128print(json.dumps({k: res[k] for k in ["n_valid_records", "n_unreadable_records", "sum_price_valid",129 "sum_price_unreadable_implied", "mean_price_valid", "median_price_per_m2_valid", "feasibility"]}, indent=1))130