spb/wp5_uqo Public

1# Author: Simon-Pierre Boucher — contact@spboucher.ai2"""303_clean_rent.py4----------------5Clean and standardize the Realtor.ca rental dataset (data/raw/rent.json).67Steps8  1.  Load rent.json.9  2.  Filter to Property.Type == "Single Family" and LeaseRent containing "Monthly".10  3.  Parse monthly rent (remove $, commas, split on /).11  4.  Extract city from the LAST pipe-segment of AddressText (before the comma).12  5.  Standardize city names to match Airbnb conventions (collapse Montreal13      boroughs, Québec arrondissements, Longueuil boroughs, etc.).14  6.  Parse lat/lon to float.15  7.  Parse bedrooms, bathrooms, SizeInterior to numeric.16  8.  Winsorize extreme rents at 1st/99th percentile.17  9.  Create log_rent = ln(monthly_rent).18  10. Save to data/processed/rent_clean.parquet.19"""2021import json22import re23import sys24from pathlib import Path2526sys.path.insert(0, str(Path(__file__).resolve().parent.parent))2728import numpy as np29import pandas as pd3031from src.config import RENT_RAW, RENT_CLEAN, require3233RAW_HINT = ("Place the original rent.json in data/raw/ to rebuild "34            "rent_clean.parquet; data/processed/ already contains the "35            "committed version.")363738def parse_monthly_rent(raw: str) -> float:39    """Extract numeric monthly rent from strings like '$1,950/Monthly'."""40    if not isinstance(raw, str) or "Monthly" not in raw:41        return np.nan42    # Remove $ and commas, take part before first /43    raw = raw.replace("$", "").replace(",", "")44    parts = raw.split("/")45    try:46        return float(parts[0])47    except (ValueError, IndexError):48        return np.nan495051def extract_city_raw(address_text: str) -> str:52    """53    AddressText format:54      "Street|[#Unit|]City (Borough), Province PostalCode"55    The city info is in the LAST pipe-segment, before the comma.56    """57    if not isinstance(address_text, str):58        return ""59    parts = address_text.split("|")60    last_segment = parts[-1].strip()61    # Split at comma to drop province + postal code62    if "," in last_segment:63        city_part = last_segment.split(",")[0].strip()64    else:65        city_part = last_segment.strip()66    return city_part676869def extract_borough(city_raw: str) -> str:70    """Return the borough from 'CityName (Borough)' or empty string."""71    m = re.search(r"\((.+)\)", city_raw)72    return m.group(1).strip() if m else ""737475def standardize_city(city_raw: str) -> str:76    """77    Map Realtor address-level city names to the canonical city names78    used in the Airbnb dataset.79    """80    cr = city_raw.strip()8182    # -- Montreal boroughs -> "Montreal"83    if cr.startswith("Montréal"):84        return "Montreal"85    if cr in ("Montréal", "Montreal"):86        return "Montreal"8788    # -- Québec arrondissements -> "Québec"89    if cr.startswith("Québec"):90        return "Québec"9192    # -- Longueuil boroughs -> "Longueuil"93    if cr.startswith("Longueuil"):94        return "Longueuil"9596    # -- Laval arrondissements -> "Laval"97    if cr.startswith("Laval"):98        return "Laval"99100    # -- Gatineau sectors -> "Gatineau"101    if cr.startswith("Gatineau"):102        return "Gatineau"103104    # -- Saguenay boroughs -> "Saguenay"105    if cr.startswith("Saguenay"):106        return "Saguenay"107108    # -- Sherbrooke boroughs -> "Sherbrooke"109    if cr.startswith("Sherbrooke"):110        return "Sherbrooke"111112    # -- Lévis boroughs -> "Lévis"113    if cr.startswith("Lévis"):114        return "Lévis"115116    # -- Terrebonne sectors -> "Terrebonne"117    if cr.startswith("Terrebonne"):118        return "Terrebonne"119120    # -- Repentigny sectors -> "Repentigny"121    if cr.startswith("Repentigny"):122        return "Repentigny"123124    # Explicit one-off mappings125    city_map = {126        "Trois-Rivières":    "Trois-Rivières",127        "Mont-Royal":        "Mont-Royal",128        "Westmount":         "Westmount",129        "Côte-Saint-Luc":    "Côte-Saint-Luc",130        "Montréal-Ouest":    "Montréal-Ouest",131        "Dollard-des-Ormeaux": "Dollard-Des Ormeaux",132    }133    if cr in city_map:134        return city_map[cr]135136    # Strip parenthetical borough for any remaining cities137    base = re.sub(r"\s*\(.+\)\s*", "", cr).strip()138    return base139140141def parse_size(raw: str) -> float:142    """Parse SizeInterior strings like '850 sqft' to float square feet."""143    if not isinstance(raw, str) or raw.strip() == "":144        return np.nan145    cleaned = raw.lower().replace("sqft", "").replace("sq ft", "").replace(",", "").strip()146    try:147        return float(cleaned)148    except ValueError:149        return np.nan150151152def main() -> None:153    require(RENT_RAW, RAW_HINT)154155    # 1. Load156    with open(RENT_RAW, "r", encoding="utf-8") as f:157        rent_raw = json.load(f)158159    print(f"[load] Total records in rent.json: {len(rent_raw)}")160161    # 2. Filter: Single Family + Monthly162    filtered = []163    for rec in rent_raw:164        prop = rec.get("Property", {})165        if prop.get("Type") != "Single Family":166            continue167        lease_rent = prop.get("LeaseRent", "")168        if "Monthly" not in lease_rent:169            continue170        filtered.append(rec)171172    print(f"[filter] After Single Family + Monthly: {len(filtered)}")173174    # 3. Flatten into a DataFrame175    rows = []176    for rec in filtered:177        prop = rec.get("Property", {})178        addr = prop.get("Address", {})179        bld = rec.get("Building", {})180        rows.append({181            "address_text":           addr.get("AddressText", ""),182            "lat_raw":                addr.get("Latitude", ""),183            "lon_raw":                addr.get("Longitude", ""),184            "lease_rent_raw":         prop.get("LeaseRent", ""),185            "lease_rent_unformatted": prop.get("LeaseRentUnformattedValue", ""),186            "building_type":          bld.get("Type", ""),187            "bedrooms_raw":           bld.get("Bedrooms", ""),188            "bathrooms_raw":          bld.get("BathroomTotal", ""),189            "size_interior_raw":      bld.get("SizeInterior", ""),190            "postal_code":            rec.get("PostalCode", ""),191            "province":               rec.get("ProvinceName", ""),192            "mls_number":             rec.get("MlsNumber", ""),193        })194195    df = pd.DataFrame(rows)196    print(f"[flatten] Shape: {df.shape}")197198    # 4. Parse monthly rent199    df["monthly_rent"] = df["lease_rent_raw"].apply(parse_monthly_rent)200201    n_parsed = df["monthly_rent"].notna().sum()202    n_failed = df["monthly_rent"].isna().sum()203    print(f"[rent] Parsed: {n_parsed}, failed: {n_failed}")204205    # Drop rows where rent could not be parsed or is non-positive206    df = df.dropna(subset=["monthly_rent"])207    df = df[df["monthly_rent"] > 0].copy()208    print(f"[rent] After dropping null/non-positive rents: {len(df)}")209210    # 5. Extract city and borough from AddressText211    df["city_raw"] = df["address_text"].apply(extract_city_raw)212    df["borough"] = df["city_raw"].apply(extract_borough)213214    # 6. Standardize city names to match Airbnb conventions215    df["city"] = df["city_raw"].apply(standardize_city)216217    print(f"[city] Unique cities after standardisation: {df['city'].nunique()}")218    print(f"[city] Top 10:\n{df['city'].value_counts().head(10).to_string()}\n")219220    # 7. Parse lat / lon to float221    df["lat"] = pd.to_numeric(df["lat_raw"], errors="coerce")222    df["lon"] = pd.to_numeric(df["lon_raw"], errors="coerce")223224    n_latlon_null = df[["lat", "lon"]].isnull().any(axis=1).sum()225    print(f"[latlon] Null lat or lon: {n_latlon_null}")226227    # 8. Parse bedrooms, bathrooms, SizeInterior228    df["bedrooms"] = pd.to_numeric(df["bedrooms_raw"], errors="coerce")229    df["bathrooms"] = pd.to_numeric(df["bathrooms_raw"], errors="coerce")230    df["size_interior_sqft"] = df["size_interior_raw"].apply(parse_size)231232    print(f"[bedrooms]  non-null: {df['bedrooms'].notna().sum()}, "233          f"null: {df['bedrooms'].isna().sum()}")234    print(f"[bathrooms] non-null: {df['bathrooms'].notna().sum()}, "235          f"null: {df['bathrooms'].isna().sum()}")236    print(f"[size]      non-null: {df['size_interior_sqft'].notna().sum()}, "237          f"null: {df['size_interior_sqft'].isna().sum()}")238239    # 9. Winsorize extreme rents at 1st / 99th percentile240    p01 = df["monthly_rent"].quantile(0.01)241    p99 = df["monthly_rent"].quantile(0.99)242    print(f"[winsorize] Rent bounds: p1={p01:.2f}, p99={p99:.2f}")243    df["monthly_rent"] = df["monthly_rent"].clip(lower=p01, upper=p99)244245    # 10. Create log_rent246    df["log_rent"] = np.log(df["monthly_rent"])247248    print(f"[rent] monthly_rent  mean={df['monthly_rent'].mean():.2f}  "249          f"median={df['monthly_rent'].median():.2f}")250251    # 11. Select final columns and save252    keep_cols = [253        "mls_number",254        "city",255        "borough",256        "building_type",257        "monthly_rent",258        "log_rent",259        "bedrooms",260        "bathrooms",261        "size_interior_sqft",262        "lat",263        "lon",264        "postal_code",265    ]266    df_out = df[keep_cols].copy()267268    df_out.to_parquet(RENT_CLEAN, index=False)269    print(f"\n>>> Saved cleaned rent data ({len(df_out)} rows) to {RENT_CLEAN}")270    print(f"    Columns: {list(df_out.columns)}")271272273if __name__ == "__main__":274    main()275