spb/wp9_uqo Public

1# Author: Simon-Pierre Boucher — contact@spboucher.ai2"""Construction of the estimation sample from the raw DuckDB snapshot.34Steps (documented in the paper's data section):561. read the raw ``listings`` table (172,019 unique for-sale listings);72. parse the semi-structured fields (bedrooms, areas, lot, counts);83. consolidate dwelling type and ownership form; derive the FSA from the9   postal code;104. keep residential listings with a strictly positive price and non-missing11   core structural fields (living area, bedrooms, bathrooms) and a valid FSA;125. trim the extreme 1% tails of price and living area;136. pool FSAs with fewer than 25 listings into a province-level residual14   category.15"""16import duckdb17import numpy as np18import pandas as pd1920from . import parsing21from .config import (ANALYSIS_PARQUET, FSA_MIN_LISTINGS, LOT_TAIL_Q, RAW_DB,22                     TRIM_HI, TRIM_LO, ensure_dirs)2324_RAW_QUERY = """25select "_category"                        as cat,26       "_province"                        as prov,27       "building.type"                    as btype_raw,28       "ownership.type"                   as own_raw,29       "building.bedrooms"                as bed_raw,30       "building.bathroom_total"          as bath_raw,31       "building.half_bath_total"         as half_raw,32       "building.size_interior"           as size_interior,33       "building.floor_area_measurements" as floor_area,34       "building.stories_total"           as stories_raw,35       "parking.spaces_total"             as parking_raw,36       "land.size_total"                  as lot_raw,37       "location.postal_code"             as postal_code,38       price_cad, lat, lon39from listings40"""4142_FSA_PATTERN = r"^[A-Z]\d[A-Z]$"434445def load_raw() -> pd.DataFrame:46    """Read the raw listings table from the DuckDB snapshot."""47    with duckdb.connect(str(RAW_DB), read_only=True) as con:48        return con.execute(_RAW_QUERY).df()495051def parse_variables(raw: pd.DataFrame) -> pd.DataFrame:52    """Parse raw text fields into numeric/categorical analysis variables."""53    d = raw.copy()54    d["bedrooms"] = d["bed_raw"].map(parsing.parse_bedrooms)55    d["bathrooms"] = d["bath_raw"].map(parsing.parse_count)56    d["half_baths"] = d["half_raw"].map(parsing.parse_count).fillna(0.0)57    d["parking_n"] = d["parking_raw"].map(parsing.parse_count).fillna(0.0)58    d["stories_n"] = d["stories_raw"].map(parsing.parse_count).fillna(0.0)59    d["living_m2"] = (d["floor_area"].map(parsing.parse_floor_area)60                      .fillna(d["size_interior"].map(parsing.parse_size_interior)))6162    lot = d["lot_raw"].map(parsing.parse_lot)63    # Very large parsed lots (top 1% of positive values, mostly multi-acre64    # rural acreage strings) are treated as "no usable lot information".65    cap = lot[lot > 0].quantile(LOT_TAIL_Q)66    lot = lot.where(lot <= cap, np.nan)67    d["lot_m2_f"] = lot.fillna(0.0)68    d["has_lot"] = (d["lot_m2_f"] > 0).astype(float)6970    d["btype_c"] = d["btype_raw"].map(parsing.consolidate_building_type)71    d["own_c"] = d["own_raw"].map(parsing.consolidate_ownership)7273    fsa = d["postal_code"].astype("string").str.upper().str[:3]74    d["fsa"] = fsa.where(fsa.str.match(_FSA_PATTERN, na=False))75    return d767778def build_sample(d: pd.DataFrame) -> pd.DataFrame:79    """Apply the sample restrictions and derive the model variables."""80    core = ((d["price_cad"] > 0) & d["living_m2"].notna()81            & d["bedrooms"].notna() & d["bathrooms"].notna() & d["fsa"].notna())82    s = d[core].copy()8384    lo_p, hi_p = s["price_cad"].quantile([TRIM_LO, TRIM_HI])85    lo_a, hi_a = s["living_m2"].quantile([TRIM_LO, TRIM_HI])86    s = s[(s["price_cad"].between(lo_p, hi_p)) & (s["living_m2"].between(lo_a, hi_a))].copy()8788    s["ln_price"] = np.log(s["price_cad"])89    s["ln_living"] = np.log(s["living_m2"])90    s["ln_lot"] = np.log1p(s["lot_m2_f"])91    s["ppm2"] = s["price_cad"] / s["living_m2"]9293    counts = s.groupby("fsa")["fsa"].transform("size")94    s["fsa_c"] = np.where(counts >= FSA_MIN_LISTINGS, s["fsa"], s["prov"] + "_other")9596    keep = ["cat", "prov", "btype_c", "own_c", "fsa", "fsa_c",97            "price_cad", "ln_price", "ppm2",98            "living_m2", "ln_living", "bedrooms", "bathrooms", "half_baths",99            "parking_n", "stories_n", "lot_m2_f", "has_lot", "ln_lot",100            "lat", "lon"]101    return s[keep].reset_index(drop=True)102103104def build_and_save() -> pd.DataFrame:105    """Full raw-to-parquet pipeline; returns the estimation sample."""106    ensure_dirs()107    sample = build_sample(parse_variables(load_raw()))108    sample.to_parquet(ANALYSIS_PARQUET, index=False)109    return sample110111112def load_sample() -> pd.DataFrame:113    """Load the processed estimation sample (build it first with script 01)."""114    return pd.read_parquet(ANALYSIS_PARQUET)115