Practical Exam: House sales¶
RealAgents is a real estate company that focuses on selling houses.
RealAgents sells a variety of types of house in one metropolitan area.
Some houses sell slowly and sometimes require lowering the price in order to find a buyer.
In order to stay competitive, RealAgents would like to optimize the listing prices of the houses it is trying to sell.
They want to do this by predicting the sale price of a house given its characteristics.
If they can predict the sale price in advance, they can decrease the time to sale.
Data¶
The dataset contains records of previous houses sold in the area.
| Column Name | Criteria |
|---|---|
| house_id | Nominal. Unique identifier for houses. Missing values not possible. |
| city | Nominal. The city in which the house is located. One of ‘Silvertown’, ‘Riverford’, ‘Teasdale’ and ‘Poppleton’. Replace missing values with “Unknown”. |
| sale_price | Discrete. The sale price of the house in whole dollars. Values can be any positive number greater than or equal to zero.Remove missing entries. |
| sale_date | Discrete. The date of the last sale of the house. Replace missing values with 2023-01-01. |
| months_listed | Continuous. The number of months the house was listed on the market prior to its last sale, rounded to one decimal place. Replace missing values with mean number of months listed, to one decimal place. |
| bedrooms | Discrete. The number of bedrooms in the house. Any positive values greater than or equal to zero. Replace missing values with the mean number of bedrooms, rounded to the nearest integer. |
| house_type | Ordinal. One of “Terraced” (two shared walls), “Semi-detached” (one shared wall), or “Detached” (no shared walls). Replace missing values with the most common house type. |
| area | Continuous. The area of the house in square meters, rounded to one decimal place. Replace missing values with the mean, to one decimal place. |
Task 1¶
The team at RealAgents knows that the city that a property is located in makes a difference to the sale price.
Unfortuntately they believe that this isn’t always recorded in the data.
Calculate the number of missing values of the city.
-
You should use the data in the file “house_sales.csv”.
-
Your output should be an object
missing_city, that contains the number of missing values in this column.
In [ ]:
# Use this cell to write your code for Task 1
import pandas as pd
# Load the house_sales.csv dataset
house_sales = pd.read_csv('house_sales.csv',na_values=['--', 'missing', 'N/A', 'na', 'NaN', 'nan', 'None'])
# Calculate the number of missing values in the 'city' column
missing_city = house_sales['city'].isnull().sum()
# Output the result
missing_city
Out[ ]:
73
Task 2¶
Before you fit any models, you will need to make sure the data is clean.
The table below shows what the data should look like.
Create a cleaned version of the dataframe.
-
You should start with the data in the file “house_sales.csv”.
-
Your output should be a dataframe named
clean_data. -
All column names and values should match the table below.
| Column Name | Criteria |
|---|---|
| house_id | Nominal. Unique identifier for houses. Missing values not possible. |
| city | Nominal. The city in which the house is located. One of ‘Silvertown’, ‘Riverford’, ‘Teasdale’ and ‘Poppleton’ Replace missing values with “Unknown”. |
| sale_price | Discrete. The sale price of the house in whole dollars. Values can be any positive number greater than or equal to zero.Remove missing entries. |
| sale_date | Discrete. The date of the last sale of the house. Replace missing values with 2023-01-01. |
| months_listed | Continuous. The number of months the house was listed on the market prior to its last sale, rounded to one decimal place. Replace missing values with mean number of months listed, to one decimal place. |
| bedrooms | Discrete. The number of bedrooms in the house. Any positive values greater than or equal to zero. Replace missing values with the mean number of bedrooms, rounded to the nearest integer. |
| house_type | Ordinal. One of “Terraced”, “Semi-detached”, or “Detached”. Replace missing values with the most common house type. |
| area | Continuous. The area of the house in square meters, rounded to one decimal place. Replace missing values with the mean, to one decimal place. |
In [ ]:
# Use this cell to write your code for Task 2
import pandas as pd
# Load the house_sales.csv dataset
house_sales = pd.read_csv('house_sales.csv',na_values=['--', 'missing', 'N/A', 'na', 'NaN', 'nan', 'None'])
# 1. Replace missing values in 'city' with "Unknown"
house_sales['city'].fillna("Unknown", inplace=True)
# 2. Remove entries with missing 'sale_price'
house_sales.dropna(subset=['sale_price'], inplace=True)
# Validate 'sale_price' to ensure all values are positive numbers greater than or equal to zero
house_sales = house_sales[house_sales['sale_price'] >= 0]
# 3. Replace missing 'sale_date' with "2023-01-01"
house_sales['sale_date'].fillna("2023-01-01", inplace=True)
# 4. Replace missing 'months_listed' with the mean number of months listed, rounded to one decimal place
mean_months_listed = round(house_sales['months_listed'].mean(), 1)
house_sales['months_listed'].fillna(mean_months_listed, inplace=True)
# 5. Replace missing 'bedrooms' with the mean number of bedrooms, rounded to the nearest integer
mean_bedrooms = round(house_sales['bedrooms'].mean())
house_sales['bedrooms'].fillna(mean_bedrooms, inplace=True)
# 6. Standardize 'house_type' values to "Terraced", "Semi-detached", "Detached"
type_mapping = {
'Semi-detached': ['Semi-detached', 'Semi'],
'Detached': ['Detached', 'Det.'],
'Terraced': ['Terraced', 'Terr.']
}
def map_house_type(house_type):
for standard_type, variants in type_mapping.items():
if house_type in variants:
return standard_type
return house_type
house_sales['house_type'] = house_sales['house_type'].apply(map_house_type)
# Replace missing 'house_type' with the most common house type
most_common_house_type = house_sales['house_type'].mode()[0]
house_sales['house_type'].fillna(most_common_house_type, inplace=True)
# 7. Replace missing 'area' with the mean area, rounded to one decimal place
# Remove 'sq.m.' suffix and convert to float
house_sales['area'] = house_sales['area'].str.replace(' sq.m.', '').astype(float)
mean_area = round(house_sales['area'].mean(), 1)
house_sales['area'].fillna(mean_area, inplace=True)
# The cleaned dataframe
clean_data = house_sales
# Display the cleaned dataframe
clean_data.head()
Out[ ]:
| house_id | city | sale_price | sale_date | months_listed | bedrooms | house_type | area | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1217792 | Silvertown | 55943 | 2021-09-12 | 5.4 | 2 | Semi-detached | 107.8 |
| 1 | 1900913 | Silvertown | 384677 | 2021-01-17 | 6.3 | 5 | Detached | 498.8 |
| 2 | 1174927 | Riverford | 281707 | 2021-11-10 | 6.9 | 6 | Detached | 542.5 |
| 3 | 1773666 | Silvertown | 373251 | 2020-04-13 | 6.1 | 6 | Detached | 528.4 |
| 4 | 1258487 | Silvertown | 328885 | 2020-09-24 | 8.7 | 5 | Detached | 477.1 |
Task 3¶
The team at RealAgents have told you that they have always believed that the number of bedrooms is the biggest driver of house price.
Producing a table showing the difference in the average sale price by number of bedrooms along with the variance to investigate this question for the team.
-
You should start with the data in the file ‘house_sales.csv’.
-
Your output should be a data frame named
price_by_rooms. -
It should include the three columns
bedrooms,avg_price,var_price. -
Your answers should be rounded to 1 decimal place.
In [ ]:
# Use this cell to write your code for Task 3
# Group by 'bedrooms' and calculate mean and variance of 'sale_price'
price_by_rooms = house_sales.groupby('bedrooms')['sale_price'].agg(['mean', 'var']).reset_index()
# Rename columns and round values to one decimal place
price_by_rooms.columns = ['bedrooms', 'avg_price', 'var_price']
price_by_rooms = price_by_rooms.round(1)
# Display the resulting dataframe
price_by_rooms
Out[ ]:
| bedrooms | avg_price | var_price | |
|---|---|---|---|
| 0 | 2 | 67076.4 | 5.652896e+08 |
| 1 | 3 | 154665.1 | 2.378289e+09 |
| 2 | 4 | 234704.6 | 1.725211e+09 |
| 3 | 5 | 301515.9 | 2.484328e+09 |
| 4 | 6 | 375741.3 | 3.924432e+09 |
Task 4¶
Fit a baseline model to predict the sale price of a house.
-
Fit your model using the data contained in “train.csv”
-
Use “validation.csv” to predict new values based on your model. You must return a dataframe named
base_result, that includeshouse_idandprice. The price column must be your predicted values.
In [ ]:
# Use this cell to write your code for Task 4import pandas as pd
from sklearn.linear_model import LinearRegression
# Load train and validation datasets
train = pd.read_csv('train.csv')
validation = pd.read_csv('validation.csv')
# Preprocessing function to clean the dataset
def preprocess_data(df, is_train=True):
df['city'].fillna("Unknown", inplace=True)
if is_train:
df.dropna(subset=['sale_price'], inplace=True)
df = df[df['sale_price'] >= 0]
df['sale_date'].fillna("2023-01-01", inplace=True)
mean_months_listed = round(df['months_listed'].mean(), 1)
df['months_listed'].fillna(mean_months_listed, inplace=True)
mean_bedrooms = round(df['bedrooms'].mean())
df['bedrooms'].fillna(mean_bedrooms, inplace=True)
type_mapping = {
'Semi-detached': ['Semi-detached', 'Semi'],
'Detached': ['Detached', 'Det.'],
'Terraced': ['Terraced', 'Terr.']
}
def map_house_type(house_type):
for standard_type, variants in type_mapping.items():
if house_type in variants:
return standard_type
return house_type
df['house_type'] = df['house_type'].apply(map_house_type)
most_common_house_type = df['house_type'].mode()[0]
df['house_type'].fillna(most_common_house_type, inplace=True)
if df['area'].dtype == 'object':
df['area'] = df['area'].str.replace(' sq.m.', '').astype(float)
mean_area = round(df['area'].mean(), 1)
df['area'].fillna(mean_area, inplace=True)
return df
# Preprocess the train and validation data
train = preprocess_data(train)
validation = preprocess_data(validation, is_train=False)
# One-hot encode the 'city' and 'house_type' columns
train_encoded = pd.get_dummies(train, columns=['city', 'house_type'], drop_first=True)
validation_encoded = pd.get_dummies(validation, columns=['city', 'house_type'], drop_first=True)
# Define features and target variable for training
X_train = train_encoded.drop(columns=['house_id', 'sale_price', 'sale_date'])
y_train = train_encoded['sale_price']
# Train a Linear Regression model
model = LinearRegression()
model.fit(X_train, y_train)
# Predict on the validation set
X_validation = validation_encoded.drop(columns=['house_id', 'sale_date'])
predictions = model.predict(X_validation)
# Create the base_result dataframe
base_result = pd.DataFrame({
'house_id': validation['house_id'],
'price': predictions
})
# Display the base_result dataframe
base_result
Out[ ]:
| house_id | price | |
|---|---|---|
| 0 | 1331375 | 121527.827316 |
| 1 | 1630115 | 304386.625267 |
| 2 | 1645745 | 384760.100656 |
| 3 | 1336775 | 123976.268985 |
| 4 | 1888274 | 271186.199353 |
| … | … | … |
| 295 | 1986255 | 351916.468218 |
| 296 | 1896276 | 370138.966354 |
| 297 | 1758223 | 259024.117384 |
| 298 | 1752010 | 169120.160936 |
| 299 | 1651404 | 391289.206911 |
300 rows × 2 columns
Task 5¶
Fit a comparison model to predict the sale price of a house.
-
Fit your model using the data contained in “train.csv”
-
Use “validation.csv” to predict new values based on your model. You must return a dataframe named
compare_result, that includeshouse_idandprice. The price column must be your predicted values.
In [ ]:
# Use this cell to write your code for Task 5
import pandas as pd
from sklearn.ensemble import RandomForestRegressor
# Load train and validation datasets
train = pd.read_csv('train.csv')
validation = pd.read_csv('validation.csv')
# Preprocessing function to clean the dataset
def preprocess_data(df, is_train=True):
df['city'].fillna("Unknown", inplace=True)
if is_train:
df.dropna(subset=['sale_price'], inplace=True)
df = df[df['sale_price'] >= 0]
df['sale_date'].fillna("2023-01-01", inplace=True)
mean_months_listed = round(df['months_listed'].mean(), 1)
df['months_listed'].fillna(mean_months_listed, inplace=True)
mean_bedrooms = round(df['bedrooms'].mean())
df['bedrooms'].fillna(mean_bedrooms, inplace=True)
type_mapping = {
'Semi-detached': ['Semi-detached', 'Semi'],
'Detached': ['Detached', 'Det.'],
'Terraced': ['Terraced', 'Terr.']
}
def map_house_type(house_type):
for standard_type, variants in type_mapping.items():
if house_type in variants:
return standard_type
return house_type
df['house_type'] = df['house_type'].apply(map_house_type)
most_common_house_type = df['house_type'].mode()[0]
df['house_type'].fillna(most_common_house_type, inplace=True)
if df['area'].dtype == 'object':
df['area'] = df['area'].str.replace(' sq.m.', '').astype(float)
mean_area = round(df['area'].mean(), 1)
df['area'].fillna(mean_area, inplace=True)
return df
# Preprocess the train and validation data
train = preprocess_data(train)
validation = preprocess_data(validation, is_train=False)
# One-hot encode the 'city' and 'house_type' columns
train_encoded = pd.get_dummies(train, columns=['city', 'house_type'], drop_first=True)
validation_encoded = pd.get_dummies(validation, columns=['city', 'house_type'], drop_first=True)
# Define features and target variable for training
X_train = train_encoded.drop(columns=['house_id', 'sale_price', 'sale_date'])
y_train = train_encoded['sale_price']
# Train a Random Forest Regressor model
model = RandomForestRegressor(random_state=42)
model.fit(X_train, y_train)
# Predict on the validation set
X_validation = validation_encoded.drop(columns=['house_id', 'sale_date'])
predictions = model.predict(X_validation)
# Create the compare_result dataframe
compare_result = pd.DataFrame({
'house_id': validation['house_id'],
'price': predictions
})
# Display the compare_result dataframe
compare_result
Out[ ]:
| house_id | price | |
|---|---|---|
| 0 | 1331375 | 82153.81 |
| 1 | 1630115 | 302692.88 |
| 2 | 1645745 | 404702.98 |
| 3 | 1336775 | 106794.10 |
| 4 | 1888274 | 267211.50 |
| … | … | … |
| 295 | 1986255 | 364083.39 |
| 296 | 1896276 | 392105.41 |
| 297 | 1758223 | 264528.57 |
| 298 | 1752010 | 178815.78 |
| 299 | 1651404 | 421717.20 |
300 rows × 2 columns