spb/wp2_uqo Public

1# Author: Simon-Pierre Boucher — contact@spboucher.ai2#3"""Extraction of the house sample from the raw SQLite database.45The extraction logic replicates the original ``hedonic_maison.py`` exactly:6single-family listings (``category = 'house'``) with a positive price and a7description of at least 20 characters, keeping the same field parsing and the8same defaults so the resulting sample is byte-for-byte identical9(n = 17,087).10"""1112import json13import sqlite31415import numpy as np16import pandas as pd171819def _parse_land_size(size_str):20    """Parse the leading numeric token of Land.SizeTotal (e.g. '5000 sqft')."""21    return float("".join(c for c in size_str.split()[0].replace(",", "") if c.isdigit() or c == "."))222324def load_houses(db_path):25    """Return the analysis sample of houses as a DataFrame.2627    Columns: id, price, log_price, bedrooms, bathrooms, half_baths, parking,28    stories, land_size, latitude, longitude, prop_type, remarks,29    remarks_length.30    """31    conn = sqlite3.connect(db_path)32    rows = conn.execute(33        "SELECT id, category, price_value, bedrooms, bathrooms, data "34        "FROM properties WHERE category = 'house'"35    ).fetchall()36    conn.close()3738    records = []39    for pid, _category, price, beds, baths, data_json in rows:40        try:41            data = json.loads(data_json)42        except (json.JSONDecodeError, TypeError):43            continue4445        remarks = data.get("PublicRemarks", "")46        if not remarks or not isinstance(remarks, str) or len(remarks.strip()) < 20:47            continue48        if not price or price <= 0:49            continue5051        parking = 052        try:53            parking = int(data["Property"].get("ParkingSpaceTotal", 0))54        except (KeyError, TypeError, ValueError):55            pass5657        stories = 058        try:59            stories = int(data["Building"].get("StoriesTotal", 0))60        except (KeyError, TypeError, ValueError):61            pass6263        half_bath = 064        try:65            half_bath = int(data["Building"].get("HalfBathTotal", 0))66        except (KeyError, TypeError, ValueError):67            pass6869        land_size = 070        try:71            land_size = _parse_land_size(data.get("Land", {}).get("SizeTotal", "0"))72        except (IndexError, ValueError):73            pass7475        prop_type = data.get("Property", {}).get("Type", "")7677        lat = lon = None78        try:79            lat = float(data["Property"]["Address"]["Latitude"])80            lon = float(data["Property"]["Address"]["Longitude"])81        except (KeyError, TypeError, ValueError):82            pass8384        records.append({85            "id": pid,86            "price": price,87            "log_price": np.log(price),88            "bedrooms": beds or 0,89            "bathrooms": baths or 0,90            "half_baths": half_bath,91            "parking": parking,92            "stories": stories,93            "land_size": land_size,94            "latitude": lat,95            "longitude": lon,96            "prop_type": prop_type,97            "remarks": remarks,98            "remarks_length": len(remarks),99        })100101    return pd.DataFrame(records).reset_index(drop=True)102