Cleaning and shaping the Airbnb listings

Sign in<br>Subscribe

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.<br>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.<br>The raw material<br>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.<br>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.<br>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.<br>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.<br>Tables<br>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.<br># Join listing facts to neighborhood aggregates<br>listings = df[['id', 'neighbourhood_cleansed', 'room_type', 'price_num']].copy()<br>neighbourhood_stats = df.groupby('neighbourhood_cleansed')['price_num'].agg(['mean', 'count']).reset_index()<br>inner_join = listings.merge(neighbourhood_stats, on='neighbourhood_cleansed', how='inner')<br>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.<br>Missing data<br>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.<br>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.<br># MICE: chained equations, 5 iterations<br>mice_imputer =...