翻訳待ち：Cleaning and Shaping the Airbnb Listings

AI サービスが一時的に利用できないため、復旧後に翻訳を補完します。

In the last few posts we built regression models from raw features and learned how to inspect what drives them, but every one of those models assumed a table that was already tidy. This post takes the opposite starting point: we begin with the raw New York City Airbnb listings, roughly 20,000 rows of mixed types, missing values, and skewed prices, and we turn that mess into a model-ready feature matrix. By the time we are done, a random forest trained on just eight features reaches an R² of 0.856 on log price, which is the concrete payoff for every cleaning decision we make along the way. The through-line for this whole post is the idea of a table as a raw material: we mine it, refine it, and shape it until the structure of the data itself tells us what matters. The dataset comes from Inside Airbnb, a public snapshot of New York City listings with about 40,000 rows and 50 MB of raw CSV data. Because the live download returned a 403 error, this post uses a synthetic fallback; the real listings will be messier. We work with a deterministic sample of 20,000 rows so the notebook runs in under thirty minutes on a laptop. The table has 29 columns covering price, room type, neighborhood, host attributes, review scores, and amenities, which gives us every problem class we want to practice: missing blocks, long tails, categories with many distinct values (high-cardinality categories), and a target that needs transforming. The raw material Before any modeling, we inspect the table the way a miner inspects a vein. Price is right-skewed: the mean sits at $227.46 while the median is $207.00, so a handful of expensive listings pull the average upward. The histogram makes the shape obvious, with a long tail stretching past $700. Missing values arrive in blocks. Exactly 5,061 rows are missing every review score field, reviews_per_month, first_review, and last_review, all at once. These are listings that have never received a review, and the fact that they are missing is itself informative. Numeric fields have long tails too: the IQR fence (a boundary 1.5 interquartile ranges beyond the quartiles) flags 129 price outliers and 59 review-score outliers, while most other fields stay clean. The correlation heatmap confirms that accommodates, bedrooms, and beds move together, which matters later when we build interaction features. And the categorical bar charts show that room type and neighborhood group carry most of the signal, with Manhattan dominating the listings. These findings drive everything that follows. We will log-transform price, impute the review block with a robust median plus an explicit indicator, cap the outliers, and encode the categorical fields with care. Tables Before we clean individual columns, we need to keep related tables straight. That first module uses SQL joins to combine listing facts with neighborhood summaries, window functions to rank rows inside groups, and star schemas to keep a central fact table surrounded by small dimension tables. We start by treating the listings as fact-like rows and the neighborhood statistics as a dimension. # Join listing facts to neighborhood aggregates listings = df[['id', 'neighbourhood_cleansed', 'room_type', 'price_num']].copy() neighbourhood_stats = df.groupby('neighbourhood_cleansed')['price_num'].agg(['mean', 'count']).reset_index() inner_join = listings.merge(neighbourhood_stats, on='neighbourhood_cleansed', how='inner') Both the inner and left joins preserve all 20,000 rows, which tells us every listing has a valid neighborhood. The window function then ranks each price within its own neighborhood on a 0 to 1 scale, so we can compare a $300 listing in Manhattan against a $300 listing in Queens fairly. The star schema keeps the fact table narrow and moves descriptive attributes into dimension tables, which is the same discipline we apply when we build features later. Missing data Missing values do not occur by accident. Missing completely at random (MCAR) means the gap is independent of all values, missing at random (MAR) means the gap depends on observed columns, and missing not at random (MNAR) means the gap depends on the missing value itself. Our review scores are almost certainly MNAR: a listing without reviews has no score, and the absence of reviews is the signal. We test this by comparing mean price across the missing indicator. Listings without review scores average $227.70, while listings with scores average $226.17. The difference is small but real, and it tells us the missingness is not MCAR. We then compare three imputation strategies on a 4,000-row sample. Mean imputation pulls every gap to the observed mean of 4.566, which is simple but destroys variance. kNN imputation finds similar rows and fills from them, landing at 4.564. MICE models each column as a function of the others through chained equations, also landing at 4.564. # MICE: chained equations, 5 iterations mice_imputer = IterativeImputer(max_iter=5, random_state=42, sample_posterior=False) mice_values = mice_imputer.fit_transform(knn_sample) The three methods agree closely on the mean, but the real lesson is the indicator. We fill the review block with the median and add a has_reviews column that records whether the listing ever received a review. That binary flag preserves the MNAR signal that imputation would otherwise erase. Outliers Long tails distort linear models and distance-based imputers. Z-score outlier detection flags values more than three standard deviations from the mean, while IQR fences use quartiles and tolerate extreme values better. Before capping, the IQR fence flags 129 price outliers; the z-score rule flags 75. After capping, 98 IQR outliers remain in the review-score field. We cap the numeric fields with the IQR fence, clipping values to the whisker boundaries. Then we compare scaling methods. Standardization centers each column to mean 0 and standard deviation 1, which makes Lasso coefficients comparable later. Min-max scaling maps to the unit interval, which is useful for distance-based methods. Standardization and min-max scaling both preserve row order; neither changes the skew, so the next question is the transform. The raw price has a skew of 0.73. After a log transform, the skew drops to -0.206, and the histogram becomes roughly symmetric. Box-Cox transforms estimate the optimal power parameter, and the fitted lambda of 0.179 confirms that the log is a good reference point, since a lambda near zero corresponds to the log transform. The lesson is that capping handles the extreme tail while the log transform handles the shape. We keep the capped numeric fields in their original units for tree models, and we use log price as the modeling target. Encodings Models consume numbers, not categories. One-hot, ordinal, and target encoding handle categorical columns. Feature hashing compresses high-cardinality strings into four columns. Binning, interaction features, and cyclical encoding reshape numeric or circular columns. We split the data before any target encoding so the test set stays honest. All 20,000 rows remain after imputation, and we reserve a 5,000-row validation set for later tuning; the training set has 12,000 rows and the test set has 3,000. The target map for neighborhoods uses a smoothing factor of 20, blending each neighborhood's mean with the global prior, and it produces 145 encoded neighborhoods. # Smoothed target map for neighbourhood, computed only on train prior = train['log_price'].mean() smooth = 20 neigh_stats = train.groupby('neighbourhood_cleansed')['log_price'].agg(['mean', 'count']) neigh_map = (neigh_stats['count'] * neigh_stats['mean'] + smooth * prior) / (neigh_stats['count'] + smooth) The feature builder produces 29 columns: room type gets both ordinal and one-hot columns, neighborhood group gets one-hot columns, amenities are hashed into four numeric columns, and we add a review-count bin, an accommodates-by-bedrooms interaction, and a cyclical month feature from the first review date. The resulting matrix has no missing values and the same 29 columns in train and test. Selection More features mean more variance and longer runs, so we rank them four ways. Filter methods for feature ranking score each column without a model, and mutual information for feature selection is a filter that captures non-linear dependence. Wrapper methods fit a model on feature subsets, with recursive feature elimination as the popular choice. Embedded methods learn feature weights inside a regularized model. Permutation importance, which can be applied after any model fit, measures the drop in score when one column is shuffled. Mutual information puts room_type_ordinal at the top with 0.4908 nats (natural-log units), followed by the one-hot room type columns and neighbourhood_target at 0.1737. RFE with a random forest selects eight features: room_type_ordinal, accommodates_bedrooms, neighbourhood_group_cleansed_Manhattan, neighbourhood_target, reviews_per_month, room_type_Entire home/apt, beds, and availability_365. Lasso on standardized features agrees, giving room_type_ordinal the largest absolute coefficient at 0.3866 and neighbourhood_target second at 0.1147. Permutation importance also puts room_type_ordinal first at 1.0678, followed by the Manhattan indicator and the target-encoded neighborhood. The cross-method agreement is the real result. Accommodates, room type, and neighborhood appear near the top of every ranking, which is stronger evidence than any single score. We take the top eight mutual-information features and train a random forest with 80 trees. The baseline dummy regressor scores an R² of -0.0004, essentially predicting the mean for everyone. The final model reaches an R² of 0.856 with a root mean squared error (RMSE) of 0.189 on log price. Recap We started with a raw table and found a skewed target, a missing review block, long-tailed numerics, and high-cardinality neighborhoods. Every cleaning decision traced back to one of those findings: log price for the skew, median imputation plus a has_reviews indicator for the missing block, IQR capping for the tails, and target encoding for the neighborhoods. The result is a feature matrix where eight columns explain 85.6 percent of the variance in log price, which is a strong baseline for any further modeling. The notebook simplifies a few things. The synthetic fallback data has cleaner structure than the real listings, so the exact numbers here are optimistic. A production version would also handle time-based validation, since listings change over time, and would tune the smoothing factor and the number of hashed amenity columns. The core pipeline, however, transfers directly. The question this post leaves open is whether the eight selected features are enough, or whether a deeper model with all 29 columns would justify the extra complexity. The next post takes that question head on: we compare the compact model against a full-feature model and measure the cost of simplicity. Try the exercises in the notebook to extend the work: rerun the imputation comparison with different k values, build a target encoding for room type instead of neighborhood, or swap the random forest for a gradient-boosted model and watch the ranking change. Further reading Python for Data Analysis by Wes McKinney. Covers the pandas operations we used, including the joins, window functions, and reshaping throughout this post. Feature Engineering for Machine Learning by Alice Zheng and Amanda Casari. A practical guide to encoding, binning, and interaction features that goes deeper into the trade-offs we touched here. The Elements of Statistical Learning by Trevor Hastie, Robert Tibshirani, and Jerome Friedman. Provides the math behind imputation, regularization, and feature selection, including Lasso and mutual information. Download the full notebook to reproduce every step, from the raw CSV to the ranked fea [truncated for AI cost control]