spb/wp5_uqo Public
UQO Working Paper No. 5 — Airbnb, residential rents and housing market pressure.
TeX 53.4%
Python 46.5%
1# Author: Simon-Pierre Boucher — contact@spboucher.ai2"""304_merge_data.py4================5Merge Airbnb and rental listing data using three linkage strategies:6 1. Spatial buffer merge (Haversine distance at 250m, 500m, 1km, 2km)7 2. City/borough-level aggregation8 3. Combined analysis file910Inputs:11 data/processed/airbnb_clean.parquet12 data/processed/rent_clean.parquet1314Outputs:15 data/processed/merged_spatial.parquet16 data/processed/merged_neighborhood.parquet17 data/processed/merged_analysis.parquet18"""1920import sys21from pathlib import Path2223sys.path.insert(0, str(Path(__file__).resolve().parent.parent))2425import numpy as np26import pandas as pd2728from src.config import (29 AIRBNB_CLEAN,30 RENT_CLEAN,31 MERGED_SPATIAL,32 MERGED_NEIGHBORHOOD,33 MERGED_ANALYSIS,34 BUFFER_KM,35 CHUNK_SIZE,36 require,37)38from src.geo import haversine_matrix3940PROCESSED_HINT = ("Run scripts/02_clean_airbnb.py and scripts/03_clean_rent.py "41 "first (requires the raw data), or restore the committed "42 "parquet files in data/processed/.")434445# ===================================================================46# Strategy 1 — spatial buffer merge47# ===================================================================4849def spatial_buffer_merge(rent: pd.DataFrame, airbnb: pd.DataFrame) -> pd.DataFrame:50 """51 For each rental listing, compute Airbnb exposure metrics within52 multiple distance buffers.53 """54 n_rent = len(rent)55 n_airbnb = len(airbnb)5657 # Pre-extract numpy arrays for speed58 rent_lat = rent["lat"].values.astype(np.float64)59 rent_lon = rent["lon"].values.astype(np.float64)60 airbnb_lat = airbnb["lat"].values.astype(np.float64)61 airbnb_lon = airbnb["lon"].values.astype(np.float64)6263 airbnb_price = airbnb["price_numeric"].values.astype(np.float64)64 airbnb_entire = airbnb["is_entire_home"].values.astype(np.float64)65 airbnb_rating = airbnb["rating"].values.astype(np.float64)66 airbnb_superhost = airbnb["is_superhost"].values.astype(np.float64)6768 # Initialise result columns69 result_cols = {}70 for buf in BUFFER_KM:71 tag = f"{int(buf * 1000)}m"72 result_cols[f"airbnb_count_{tag}"] = np.zeros(n_rent, dtype=np.int32)73 result_cols[f"airbnb_density_{tag}"] = np.zeros(n_rent, dtype=np.float64)74 result_cols[f"mean_airbnb_price_{tag}"] = np.full(n_rent, np.nan, dtype=np.float64)75 result_cols[f"share_entire_home_{tag}"] = np.full(n_rent, np.nan, dtype=np.float64)76 result_cols[f"mean_rating_{tag}"] = np.full(n_rent, np.nan, dtype=np.float64)77 result_cols[f"superhost_share_{tag}"] = np.full(n_rent, np.nan, dtype=np.float64)7879 n_chunks = int(np.ceil(n_rent / CHUNK_SIZE))80 print(f" Processing {n_rent} rental listings in {n_chunks} chunks "81 f"against {n_airbnb} Airbnb listings ...")8283 for c in range(n_chunks):84 start = c * CHUNK_SIZE85 end = min(start + CHUNK_SIZE, n_rent)86 idx = slice(start, end)8788 # Distance matrix: (chunk_size, n_airbnb)89 dist = haversine_matrix(rent_lat[idx], rent_lon[idx],90 airbnb_lat, airbnb_lon)9192 for buf in BUFFER_KM:93 tag = f"{int(buf * 1000)}m"94 within = dist <= buf # boolean mask (chunk, n_airbnb)9596 counts = within.sum(axis=1)97 result_cols[f"airbnb_count_{tag}"][idx] = counts98 area = np.pi * buf ** 299 result_cols[f"airbnb_density_{tag}"][idx] = counts / area100101 # For each rental in the chunk, compute means over nearby Airbnb102 for i_local in range(end - start):103 mask = within[i_local]104 if mask.sum() == 0:105 continue106 i_global = start + i_local107108 prices = airbnb_price[mask]109 valid_prices = prices[~np.isnan(prices)]110 if len(valid_prices) > 0:111 result_cols[f"mean_airbnb_price_{tag}"][i_global] = valid_prices.mean()112113 result_cols[f"share_entire_home_{tag}"][i_global] = airbnb_entire[mask].mean()114115 ratings = airbnb_rating[mask]116 valid_ratings = ratings[~np.isnan(ratings)]117 if len(valid_ratings) > 0:118 result_cols[f"mean_rating_{tag}"][i_global] = valid_ratings.mean()119120 result_cols[f"superhost_share_{tag}"][i_global] = airbnb_superhost[mask].mean()121122 if (c + 1) % 20 == 0 or (c + 1) == n_chunks:123 print(f" Chunk {c + 1}/{n_chunks} done.")124125 # Attach to rent DataFrame126 spatial = rent.copy()127 for col, arr in result_cols.items():128 spatial[col] = arr129130 return spatial131132133# ===================================================================134# Strategy 2 — city / borough aggregation135# ===================================================================136137def city_borough_aggregation(rent: pd.DataFrame, airbnb: pd.DataFrame) -> pd.DataFrame:138 """139 Aggregate Airbnb metrics at the city level (and borough level for140 Montreal) and merge onto rental data.141 """142 # --- City-level aggregation ---143 city_key = "city_clean" if "city_clean" in airbnb.columns else "city"144145 city_agg = airbnb.groupby(city_key).agg(146 airbnb_count_city=("price_numeric", "size"),147 mean_airbnb_price_city=("price_numeric", "mean"),148 median_airbnb_price_city=("price_numeric", "median"),149 share_entire_home_city=("is_entire_home", "mean"),150 mean_reviews_city=("num_reviews", "mean"),151 mean_rating_city=("rating", "mean"),152 ).reset_index()153154 # Rename the grouping column for merging155 city_agg = city_agg.rename(columns={city_key: "city"})156157 # Count rentals per city to compute density158 rent_city_counts = rent.groupby("city").size().reset_index(name="_n_rent_city")159 city_agg = city_agg.merge(rent_city_counts, on="city", how="left")160 city_agg["airbnb_density_per_1000_rentals_city"] = np.where(161 city_agg["_n_rent_city"] > 0,162 city_agg["airbnb_count_city"] / city_agg["_n_rent_city"] * 1000,163 np.nan,164 )165 city_agg = city_agg.drop(columns=["_n_rent_city"])166167 # Merge onto rent (the rent file's 'city' column aligns with the168 # standardized Airbnb city names)169 neighborhood = rent.copy()170 neighborhood = neighborhood.merge(city_agg, on="city", how="left")171172 # --- Borough-level aggregation (Montreal only) ---173 if "borough" in rent.columns:174 # Identify Montreal Airbnb listings based on city name175 mtl_variants = ["montreal", "montréal", "mtl"]176177 if city_key in airbnb.columns:178 airbnb_city_lower = airbnb[city_key].str.lower().str.strip()179 else:180 airbnb_city_lower = airbnb["city"].str.lower().str.strip()181182 airbnb_mtl = airbnb[airbnb_city_lower.isin(mtl_variants)].copy()183184 if len(airbnb_mtl) > 0 and "borough" not in airbnb_mtl.columns:185 # Airbnb data might not have borough info; skip borough merge186 print(" Note: Airbnb data does not have 'borough' column; "187 "borough-level aggregation skipped.")188 elif len(airbnb_mtl) > 0 and "borough" in airbnb_mtl.columns:189 borough_agg = airbnb_mtl.groupby("borough").agg(190 airbnb_count_borough=("price_numeric", "size"),191 mean_airbnb_price_borough=("price_numeric", "mean"),192 median_airbnb_price_borough=("price_numeric", "median"),193 share_entire_home_borough=("is_entire_home", "mean"),194 mean_reviews_borough=("num_reviews", "mean"),195 mean_rating_borough=("rating", "mean"),196 ).reset_index()197198 rent_borough_counts = (199 rent[rent["borough"].notna()]200 .groupby("borough")201 .size()202 .reset_index(name="_n_rent_borough")203 )204 borough_agg = borough_agg.merge(rent_borough_counts, on="borough", how="left")205 borough_agg["airbnb_density_per_1000_rentals_borough"] = np.where(206 borough_agg["_n_rent_borough"] > 0,207 borough_agg["airbnb_count_borough"] / borough_agg["_n_rent_borough"] * 1000,208 np.nan,209 )210 borough_agg = borough_agg.drop(columns=["_n_rent_borough"])211212 neighborhood = neighborhood.merge(borough_agg, on="borough", how="left")213214 return neighborhood215216217# ===================================================================218# Main219# ===================================================================220221def main():222 print("=" * 70)223 print("04 MERGE DATA")224 print("=" * 70)225226 # ------------------------------------------------------------------227 # Load data228 # ------------------------------------------------------------------229 print("\n[1] Loading cleaned data ...")230 airbnb = pd.read_parquet(require(AIRBNB_CLEAN, PROCESSED_HINT))231 rent = pd.read_parquet(require(RENT_CLEAN, PROCESSED_HINT))232233 # Ensure coordinate column names are consistent234 if "long" in airbnb.columns and "lon" not in airbnb.columns:235 airbnb = airbnb.rename(columns={"long": "lon"})236237 print(f" Airbnb : {airbnb.shape[0]:,} rows x {airbnb.shape[1]} cols")238 print(f" Rent : {rent.shape[0]:,} rows x {rent.shape[1]} cols")239240 # Drop rows with missing coordinates241 airbnb_valid = airbnb.dropna(subset=["lat", "lon"])242 rent_valid = rent.dropna(subset=["lat", "lon"])243 print(f" Airbnb with valid coords: {len(airbnb_valid):,}")244 print(f" Rent with valid coords: {len(rent_valid):,}")245246 # ------------------------------------------------------------------247 # Strategy 1: Spatial buffer merge248 # ------------------------------------------------------------------249 print("\n[2] Strategy 1 — Spatial buffer merge ...")250 spatial = spatial_buffer_merge(rent_valid, airbnb_valid)251252 for buf in BUFFER_KM:253 tag = f"{int(buf * 1000)}m"254 col = f"airbnb_count_{tag}"255 print(f" Buffer {tag}: "256 f"mean count = {spatial[col].mean():.2f}, "257 f"median = {spatial[col].median():.0f}, "258 f"max = {spatial[col].max()}")259260 spatial.to_parquet(MERGED_SPATIAL, index=False)261 print(f" Saved: {MERGED_SPATIAL}")262263 # ------------------------------------------------------------------264 # Strategy 2: City/borough aggregation265 # ------------------------------------------------------------------266 print("\n[3] Strategy 2 — City/borough-level aggregation ...")267 neighborhood = city_borough_aggregation(rent_valid, airbnb_valid)268269 city_cols = [c for c in neighborhood.columns if c.endswith("_city")]270 if city_cols:271 print(f" City-level columns added: {city_cols}")272 print(f" Rentals with city match: "273 f"{neighborhood['airbnb_count_city'].notna().sum():,} / {len(neighborhood):,}")274275 borough_cols = [c for c in neighborhood.columns if c.endswith("_borough")]276 if borough_cols:277 print(f" Borough-level columns added: {borough_cols}")278 print(f" Rentals with borough match: "279 f"{neighborhood['airbnb_count_borough'].notna().sum():,} / {len(neighborhood):,}")280281 neighborhood.to_parquet(MERGED_NEIGHBORHOOD, index=False)282 print(f" Saved: {MERGED_NEIGHBORHOOD}")283284 # ------------------------------------------------------------------285 # Combined analysis file286 # ------------------------------------------------------------------287 print("\n[4] Creating combined analysis file ...")288289 # Start from spatial (which already has rent + buffer vars) and add290 # neighborhood-level columns that are not already present. Both frames291 # are row-aligned because they started from the same rent_valid.292 spatial_cols = set(spatial.columns)293 extra_cols = [c for c in neighborhood.columns if c not in spatial_cols]294295 analysis = spatial.copy()296 for col in extra_cols:297 analysis[col] = neighborhood[col].values298299 analysis.to_parquet(MERGED_ANALYSIS, index=False)300 print(f" Saved: {MERGED_ANALYSIS}")301 print(f" Final shape: {analysis.shape[0]:,} rows x {analysis.shape[1]} cols")302303 # ------------------------------------------------------------------304 # Summary diagnostics305 # ------------------------------------------------------------------306 print("\n" + "=" * 70)307 print("MERGE DIAGNOSTICS")308 print("=" * 70)309 print(f" Total rental listings: {len(rent):>8,}")310 print(f" With valid coordinates: {len(rent_valid):>8,}")311 print(f" Total Airbnb listings: {len(airbnb):>8,}")312 print(f" With valid coordinates: {len(airbnb_valid):>8,}")313 print()314315 for buf in BUFFER_KM:316 tag = f"{int(buf * 1000)}m"317 col = f"airbnb_count_{tag}"318 n_zero = (analysis[col] == 0).sum()319 n_nonzero = (analysis[col] > 0).sum()320 print(f" Buffer {tag:>5s}: {n_nonzero:,} rentals have >= 1 Airbnb nearby, "321 f"{n_zero:,} have 0")322323 print()324 print(" Columns in merged_analysis.parquet:")325 for i, col in enumerate(analysis.columns):326 print(f" {i + 1:3d}. {col}")327328 print("\nDone.\n")329330331if __name__ == "__main__":332 main()333