# Author: Simon-Pierre Boucher — contact@spboucher.ai """ 03_clean_rent.py ---------------- Clean and standardize the Realtor.ca rental dataset (data/raw/rent.json). Steps 1. Load rent.json. 2. Filter to Property.Type == "Single Family" and LeaseRent containing "Monthly". 3. Parse monthly rent (remove $, commas, split on /). 4. Extract city from the LAST pipe-segment of AddressText (before the comma). 5. Standardize city names to match Airbnb conventions (collapse Montreal boroughs, Québec arrondissements, Longueuil boroughs, etc.). 6. Parse lat/lon to float. 7. Parse bedrooms, bathrooms, SizeInterior to numeric. 8. Winsorize extreme rents at 1st/99th percentile. 9. Create log_rent = ln(monthly_rent). 10. Save to data/processed/rent_clean.parquet. """ import json import re import sys from pathlib import Path sys.path.insert(0, str(Path(__file__).resolve().parent.parent)) import numpy as np import pandas as pd from src.config import RENT_RAW, RENT_CLEAN, require RAW_HINT = ("Place the original rent.json in data/raw/ to rebuild " "rent_clean.parquet; data/processed/ already contains the " "committed version.") def parse_monthly_rent(raw: str) -> float: """Extract numeric monthly rent from strings like '$1,950/Monthly'.""" if not isinstance(raw, str) or "Monthly" not in raw: return np.nan # Remove $ and commas, take part before first / raw = raw.replace("$", "").replace(",", "") parts = raw.split("/") try: return float(parts[0]) except (ValueError, IndexError): return np.nan def extract_city_raw(address_text: str) -> str: """ AddressText format: "Street|[#Unit|]City (Borough), Province PostalCode" The city info is in the LAST pipe-segment, before the comma. """ if not isinstance(address_text, str): return "" parts = address_text.split("|") last_segment = parts[-1].strip() # Split at comma to drop province + postal code if "," in last_segment: city_part = last_segment.split(",")[0].strip() else: city_part = last_segment.strip() return city_part def extract_borough(city_raw: str) -> str: """Return the borough from 'CityName (Borough)' or empty string.""" m = re.search(r"\((.+)\)", city_raw) return m.group(1).strip() if m else "" def standardize_city(city_raw: str) -> str: """ Map Realtor address-level city names to the canonical city names used in the Airbnb dataset. """ cr = city_raw.strip() # -- Montreal boroughs -> "Montreal" if cr.startswith("Montréal"): return "Montreal" if cr in ("Montréal", "Montreal"): return "Montreal" # -- Québec arrondissements -> "Québec" if cr.startswith("Québec"): return "Québec" # -- Longueuil boroughs -> "Longueuil" if cr.startswith("Longueuil"): return "Longueuil" # -- Laval arrondissements -> "Laval" if cr.startswith("Laval"): return "Laval" # -- Gatineau sectors -> "Gatineau" if cr.startswith("Gatineau"): return "Gatineau" # -- Saguenay boroughs -> "Saguenay" if cr.startswith("Saguenay"): return "Saguenay" # -- Sherbrooke boroughs -> "Sherbrooke" if cr.startswith("Sherbrooke"): return "Sherbrooke" # -- Lévis boroughs -> "Lévis" if cr.startswith("Lévis"): return "Lévis" # -- Terrebonne sectors -> "Terrebonne" if cr.startswith("Terrebonne"): return "Terrebonne" # -- Repentigny sectors -> "Repentigny" if cr.startswith("Repentigny"): return "Repentigny" # Explicit one-off mappings city_map = { "Trois-Rivières": "Trois-Rivières", "Mont-Royal": "Mont-Royal", "Westmount": "Westmount", "Côte-Saint-Luc": "Côte-Saint-Luc", "Montréal-Ouest": "Montréal-Ouest", "Dollard-des-Ormeaux": "Dollard-Des Ormeaux", } if cr in city_map: return city_map[cr] # Strip parenthetical borough for any remaining cities base = re.sub(r"\s*\(.+\)\s*", "", cr).strip() return base def parse_size(raw: str) -> float: """Parse SizeInterior strings like '850 sqft' to float square feet.""" if not isinstance(raw, str) or raw.strip() == "": return np.nan cleaned = raw.lower().replace("sqft", "").replace("sq ft", "").replace(",", "").strip() try: return float(cleaned) except ValueError: return np.nan def main() -> None: require(RENT_RAW, RAW_HINT) # 1. Load with open(RENT_RAW, "r", encoding="utf-8") as f: rent_raw = json.load(f) print(f"[load] Total records in rent.json: {len(rent_raw)}") # 2. Filter: Single Family + Monthly filtered = [] for rec in rent_raw: prop = rec.get("Property", {}) if prop.get("Type") != "Single Family": continue lease_rent = prop.get("LeaseRent", "") if "Monthly" not in lease_rent: continue filtered.append(rec) print(f"[filter] After Single Family + Monthly: {len(filtered)}") # 3. Flatten into a DataFrame rows = [] for rec in filtered: prop = rec.get("Property", {}) addr = prop.get("Address", {}) bld = rec.get("Building", {}) rows.append({ "address_text": addr.get("AddressText", ""), "lat_raw": addr.get("Latitude", ""), "lon_raw": addr.get("Longitude", ""), "lease_rent_raw": prop.get("LeaseRent", ""), "lease_rent_unformatted": prop.get("LeaseRentUnformattedValue", ""), "building_type": bld.get("Type", ""), "bedrooms_raw": bld.get("Bedrooms", ""), "bathrooms_raw": bld.get("BathroomTotal", ""), "size_interior_raw": bld.get("SizeInterior", ""), "postal_code": rec.get("PostalCode", ""), "province": rec.get("ProvinceName", ""), "mls_number": rec.get("MlsNumber", ""), }) df = pd.DataFrame(rows) print(f"[flatten] Shape: {df.shape}") # 4. Parse monthly rent df["monthly_rent"] = df["lease_rent_raw"].apply(parse_monthly_rent) n_parsed = df["monthly_rent"].notna().sum() n_failed = df["monthly_rent"].isna().sum() print(f"[rent] Parsed: {n_parsed}, failed: {n_failed}") # Drop rows where rent could not be parsed or is non-positive df = df.dropna(subset=["monthly_rent"]) df = df[df["monthly_rent"] > 0].copy() print(f"[rent] After dropping null/non-positive rents: {len(df)}") # 5. Extract city and borough from AddressText df["city_raw"] = df["address_text"].apply(extract_city_raw) df["borough"] = df["city_raw"].apply(extract_borough) # 6. Standardize city names to match Airbnb conventions df["city"] = df["city_raw"].apply(standardize_city) print(f"[city] Unique cities after standardisation: {df['city'].nunique()}") print(f"[city] Top 10:\n{df['city'].value_counts().head(10).to_string()}\n") # 7. Parse lat / lon to float df["lat"] = pd.to_numeric(df["lat_raw"], errors="coerce") df["lon"] = pd.to_numeric(df["lon_raw"], errors="coerce") n_latlon_null = df[["lat", "lon"]].isnull().any(axis=1).sum() print(f"[latlon] Null lat or lon: {n_latlon_null}") # 8. Parse bedrooms, bathrooms, SizeInterior df["bedrooms"] = pd.to_numeric(df["bedrooms_raw"], errors="coerce") df["bathrooms"] = pd.to_numeric(df["bathrooms_raw"], errors="coerce") df["size_interior_sqft"] = df["size_interior_raw"].apply(parse_size) print(f"[bedrooms] non-null: {df['bedrooms'].notna().sum()}, " f"null: {df['bedrooms'].isna().sum()}") print(f"[bathrooms] non-null: {df['bathrooms'].notna().sum()}, " f"null: {df['bathrooms'].isna().sum()}") print(f"[size] non-null: {df['size_interior_sqft'].notna().sum()}, " f"null: {df['size_interior_sqft'].isna().sum()}") # 9. Winsorize extreme rents at 1st / 99th percentile p01 = df["monthly_rent"].quantile(0.01) p99 = df["monthly_rent"].quantile(0.99) print(f"[winsorize] Rent bounds: p1={p01:.2f}, p99={p99:.2f}") df["monthly_rent"] = df["monthly_rent"].clip(lower=p01, upper=p99) # 10. Create log_rent df["log_rent"] = np.log(df["monthly_rent"]) print(f"[rent] monthly_rent mean={df['monthly_rent'].mean():.2f} " f"median={df['monthly_rent'].median():.2f}") # 11. Select final columns and save keep_cols = [ "mls_number", "city", "borough", "building_type", "monthly_rent", "log_rent", "bedrooms", "bathrooms", "size_interior_sqft", "lat", "lon", "postal_code", ] df_out = df[keep_cols].copy() df_out.to_parquet(RENT_CLEAN, index=False) print(f"\n>>> Saved cleaned rent data ({len(df_out)} rows) to {RENT_CLEAN}") print(f" Columns: {list(df_out.columns)}") if __name__ == "__main__": main()