Prepare House Price Regression Data
Capital One● MediumFULLTIMEOAProblem statement
Prepare train_houses and test_houses for house-price regression. Return both transformed datasets in one table, with all training rows first and all test rows second. Add dataset as the first column, using "train" or "test".
- Compute the mean of the non-null training
agevalues, round it down to an integer, and fill missing ages in both datasets with that value. - Map
"Poor","Fair","Good", and"Excellent"to0,1,2, and3. - Using training rows only, standardize
bedrooms,bathrooms,sqft_living,sqft_lot,floors,age, andnum_viewswith the population standard deviation. Round standardized values to five decimal places; when a training standard deviation is0, return0for that column in both datasets.
Keep house_id, waterfront, and price unchanged. Preserve a null test price as null.
Table schema
Pandas
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
train_houses
Labeled training house features.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_idPK | Integer | No | — |
| bedrooms | Integer | No | — |
| bathrooms | Integer | No | — |
| sqft_living | Integer | No | — |
| sqft_lot | Integer | No | — |
| floors | Decimal | No | — |
| waterfront | Integer | No | — |
| age | Integer | Yes | — |
| num_views | Integer | No | — |
| condition_name | Text | No | — |
| price | Decimal | Yes | — |
test_houses
House features transformed with training-only statistics.
| Column | Type | Nullable | Description |
|---|---|---|---|
| house_idPK | Integer | No | — |
| bedrooms | Integer | No | — |
| bathrooms | Integer | No | — |
| sqft_living | Integer | No | — |
| sqft_lot | Integer | No | — |
| floors | Decimal | No | — |
| waterfront | Integer | No | — |
| age | Integer | Yes | — |
| num_views | Integer | No | — |
| condition_name | Text | No | — |
| price | Decimal | Yes | — |
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| dataset | Text | No | — |
| house_id | Integer | No | — |
| bedrooms | Decimal | No | — |
| bathrooms | Decimal | No | — |
| sqft_living | Decimal | No | — |
| sqft_lot | Decimal | No | — |
| floors | Decimal | No | — |
| waterfront | Integer | No | — |
| age | Decimal | No | — |
| num_views | Decimal | No | — |
| condition | Integer | No | — |
| price | Decimal | Yes | — |
Row order: must match exactly. Numeric tolerance: 0.00001.
Constraints
- Both input tables are non-empty.
- At least one training age is non-null.
- Every
condition_nameis"Poor","Fair","Good", or"Excellent". - Training prices are non-null; test prices may be null.
- Every numeric input is finite.
- Result row order is exact.
Source note: Original Capital One data-science assessment task (carousel slide 4).