House sales

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.

# Use this cell to write your code for Task 1
import pandas as pd

# Load the data from the CSV file
data = pd.read_csv("house_sales.csv")

# Count the number of missing values in the "city" column
missing_city = data["city"].isin(['--']).sum()

# Print the result
print(missing_city)

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.

# Use this cell to write your code for Task 2
import pandas as pd
from pandas.api.types import CategoricalDtype
import re

# Load the data from the CSV file
data = pd.read_csv("house_sales.csv", decimal='.')

# Replace missing values in the 'city' column with "Unknown"
data['city'].fillna("Unknown", inplace=True)

# Replace '--' values in the 'city' column with 'Unknown'
data['city'].replace('--', 'Unknown', inplace=True)

# Remove rows with missing values in the 'sale_price' column
data.dropna(subset=['sale_price'], inplace=True)

# Replace missing values in the 'sale_date' column with '2023-01-01'
data['sale_date'].fillna('2023-01-01', inplace=True)

# Replace missing values in the 'months_listed' column with the mean number of months listed
mean_months_listed = round(data['months_listed'].mean(),1)
data['months_listed'].fillna(mean_months_listed, inplace=True)

# Replace missing values in the 'bedrooms' column with the mean number of bedrooms rounded to the nearest integer
mean_bedrooms = round(data['bedrooms'].mean())
data['bedrooms'].fillna(mean_bedrooms, inplace=True)

# Replace missing values in the 'house_type' column with the most common house type
most_common_house_type = data['house_type'].mode().iloc[0]
data['house_type'].fillna(most_common_house_type, inplace=True)

# Replace 'nan' values in the 'house_type' column with 'most common value'
data['house_type'].replace({'Det.': 'Detached', 'Terr.': 'Terraced', 'Semi': 'Semi-detached'} , inplace=True)

# Extract numerical values from the 'area' column
data['area'] = data['area'].apply(lambda x: re.findall(r'\d+\.\d+|\d+', str(x)))
data['area'] = data['area'].apply(lambda x: float(x[0]) if len(x) > 0 else None)

# Replace missing values in the 'area' column with the mean area rounded to one decimal place
mean_area = round(data['area'].mean(), 1)
data['area'].fillna(mean_area, inplace=True)

# Convert house_type column into ordinal type
cat_type= CategoricalDtype(categories=[ "Terraced", "Semi-detached", "Detached" ],ordered=True)
data['house_type'] = data['house_type'].astype(cat_type)

# Convert columns to their appropriate data types
data['house_id'] = data['house_id'].astype(str)
data['city'] = data['city'].astype(str)
data['sale_price'] = data['sale_price'].astype(int)
data['sale_date'] = pd.to_datetime(data['sale_date'])
data['months_listed'] = data['months_listed'].astype(float)
data['bedrooms'] = data['bedrooms'].astype(int)
data['house_type'] = data['house_type'].astype(str)
data['area'] = data['area'].astype(float)

# Create the cleaned dataframe named 'clean_data'
clean_data = data.copy()

# Print the cleaned dataframe
print(clean_data)

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.

# Use this cell to write your code for Task 2
import pandas as pd
from pandas.api.types import CategoricalDtype
import re

# Load the data from the CSV file
data = pd.read_csv("house_sales.csv", decimal='.')

# Replace missing values in the 'city' column with "Unknown"
data['city'].fillna("Unknown", inplace=True)

# Replace '--' values in the 'city' column with 'Unknown'
data['city'].replace('--', 'Unknown', inplace=True)

# Remove rows with missing values in the 'sale_price' column
data.dropna(subset=['sale_price'], inplace=True)

# Replace missing values in the 'sale_date' column with '2023-01-01'
data['sale_date'].fillna('2023-01-01', inplace=True)

# Replace missing values in the 'months_listed' column with the mean number of months listed
mean_months_listed = round(data['months_listed'].mean(),1)
data['months_listed'].fillna(mean_months_listed, inplace=True)

# Replace missing values in the 'bedrooms' column with the mean number of bedrooms rounded to the nearest integer
mean_bedrooms = round(data['bedrooms'].mean())
data['bedrooms'].fillna(mean_bedrooms, inplace=True)

# Replace missing values in the 'house_type' column with the most common house type
most_common_house_type = data['house_type'].mode().iloc[0]
data['house_type'].fillna(most_common_house_type, inplace=True)

# Replace 'nan' values in the 'house_type' column with 'most common value'
data['house_type'].replace({'Det.': 'Detached', 'Terr.': 'Terraced', 'Semi': 'Semi-detached'} , inplace=True)

# Extract numerical values from the 'area' column
data['area'] = data['area'].apply(lambda x: re.findall(r'\d+\.\d+|\d+', str(x)))
data['area'] = data['area'].apply(lambda x: float(x[0]) if len(x) > 0 else None)

# Replace missing values in the 'area' column with the mean area rounded to one decimal place
mean_area = round(data['area'].mean(), 1)
data['area'].fillna(mean_area, inplace=True)

# Convert house_type column into ordinal type
cat_type= CategoricalDtype(categories=[ "Terraced", "Semi-detached", "Detached" ],ordered=True)
data['house_type'] = data['house_type'].astype(cat_type)

# Convert columns to their appropriate data types
data['house_id'] = data['house_id'].astype(str)
data['city'] = data['city'].astype(str)
data['sale_price'] = data['sale_price'].astype(int)
data['sale_date'] = pd.to_datetime(data['sale_date'])
data['months_listed'] = data['months_listed'].astype(float)
data['bedrooms'] = data['bedrooms'].astype(int)
data['house_type'] = data['house_type'].astype(str)
data['area'] = data['area'].astype(float)

# Group by number of bedrooms and calculate average sale price and variance
price_by_rooms = data.groupby('bedrooms').agg({'sale_price': ['mean', 'var']})

# Rename columns
price_by_rooms.columns = ['avg_price', 'var_price']

# Round values to one decimal place
price_by_rooms = price_by_rooms.round({'avg_price': 1, 'var_price': 1})

# Reset index
price_by_rooms.reset_index(inplace=True)

# Print the price_by_rooms data frame
print(price_by_rooms)

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 includes house_id and price. The price column must be your predicted values.

# Use this cell to write your code for Task 4
import pandas as pd
from sklearn.linear_model import LinearRegression

# Load the training and validation datasets
train_data = pd.read_csv('train.csv')
val_data = pd.read_csv('validation.csv')

# Transformation functions
def transform_city(data):
    data['city'].fillna('Unknown', inplace=True)

def transform_sale_date(data):
    data['sale_date'].fillna('2023-01-01', inplace=True)

def transform_months_listed(data):
    mean_months_listed = data['months_listed'].mean()
    data['months_listed'].fillna(round(mean_months_listed, 1), inplace=True)

def transform_bedrooms(data):
    mean_bedrooms = data['bedrooms'].mean()
    data['bedrooms'].fillna(round(mean_bedrooms), inplace=True)

def transform_house_type(data):
    most_common_house_type = data['house_type'].mode()[0]
    data['house_type'].fillna(most_common_house_type, inplace=True)

def transform_area(data):
    mean_area = data['area'].mean()
    data['area'].fillna(round(mean_area, 1), inplace=True)

# Apply transformations to the training data
transform_city(train_data)
transform_sale_date(train_data)
transform_months_listed(train_data)
transform_bedrooms(train_data)
transform_house_type(train_data)
transform_area(train_data)

# Apply transformations to the validation data
transform_city(val_data)
transform_sale_date(val_data)
transform_months_listed(val_data)
transform_bedrooms(val_data)
transform_house_type(val_data)
transform_area(val_data)

# Fit a Linear Regression model
model = LinearRegression()

# Encode 'house_type' and 'city' columns
train_data = pd.get_dummies(train_data, columns=['house_type', 'city'])
val_data = pd.get_dummies(val_data, columns=['house_type', 'city'])


# Convert to datetime to get year, month, day
train_data['sale_date'] = pd.to_datetime(train_data['sale_date'])
val_data['sale_date'] = pd.to_datetime(val_data['sale_date'])


# Extract useful date-related features in train_data
train_data['sale_year'] = train_data['sale_date'].dt.year
train_data['sale_month'] = train_data['sale_date'].dt.month
train_data['sale_day'] = train_data['sale_date'].dt.day

# Repeat the same process for val_data
val_data['sale_date'] = pd.to_datetime(val_data['sale_date'])
val_data['sale_year'] = val_data['sale_date'].dt.year
val_data['sale_month'] = val_data['sale_date'].dt.month
val_data['sale_day'] = val_data['sale_date'].dt.day

# Drop the original 'sale_date' columns
train_data.drop('sale_date', axis=1, inplace=True)
val_data.drop('sale_date', axis=1, inplace=True)

# Fit model, seperating features and targets
model.fit(train_data.drop('sale_price', axis=1), train_data['sale_price'])

# Make predictions on the validation data
predicted_prices = model.predict(val_data)

# Create the base_result dataframe with house_id and predicted price
base_result = pd.DataFrame({'house_id': val_data['house_id'], 'price': predicted_prices})

# Display the resulting dataframe
print(base_result)

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 includes house_id and price. The price column must be your predicted values.

# Use this cell to write your code for Task 5
import pandas as pd
from sklearn.ensemble import RandomForestRegressor

# Load the training and validation datasets
train_data = pd.read_csv('train.csv')
val_data = pd.read_csv('validation.csv')

# Transformation functions
def transform_city(data):
    data['city'].fillna('Unknown', inplace=True)

def transform_sale_date(data):
    data['sale_date'].fillna('2023-01-01', inplace=True)

def transform_months_listed(data):
    mean_months_listed = data['months_listed'].mean()
    data['months_listed'].fillna(round(mean_months_listed, 1), inplace=True)

def transform_bedrooms(data):
    mean_bedrooms = data['bedrooms'].mean()
    data['bedrooms'].fillna(round(mean_bedrooms), inplace=True)

def transform_house_type(data):
    most_common_house_type = data['house_type'].mode()[0]
    data['house_type'].fillna(most_common_house_type, inplace=True)

def transform_area(data):
    mean_area = data['area'].mean()
    data['area'].fillna(round(mean_area, 1), inplace=True)

# Apply transformations to the training data
transform_city(train_data)
transform_sale_date(train_data)
transform_months_listed(train_data)
transform_bedrooms(train_data)
transform_house_type(train_data)
transform_area(train_data)

# Apply transformations to the validation data
transform_city(val_data)
transform_sale_date(val_data)
transform_months_listed(val_data)
transform_bedrooms(val_data)
transform_house_type(val_data)
transform_area(val_data)

# Fit a Linear Regression model
model = RandomForestRegressor()

# Encode 'house_type' and 'city' columns
train_data = pd.get_dummies(train_data, columns=['house_type', 'city'])
val_data = pd.get_dummies(val_data, columns=['house_type', 'city'])


# Convert to datetime to get year, month, day
train_data['sale_date'] = pd.to_datetime(train_data['sale_date'])
val_data['sale_date'] = pd.to_datetime(val_data['sale_date'])


# Extract useful date-related features in train_data
train_data['sale_year'] = train_data['sale_date'].dt.year
train_data['sale_month'] = train_data['sale_date'].dt.month
train_data['sale_day'] = train_data['sale_date'].dt.day

# Repeat the same process for val_data
val_data['sale_date'] = pd.to_datetime(val_data['sale_date'])
val_data['sale_year'] = val_data['sale_date'].dt.year
val_data['sale_month'] = val_data['sale_date'].dt.month
val_data['sale_day'] = val_data['sale_date'].dt.day

# Drop the original 'sale_date' columns
train_data.drop('sale_date', axis=1, inplace=True)
val_data.drop('sale_date', axis=1, inplace=True)

# Fit model, seperating features and targets
model.fit(train_data.drop('sale_price', axis=1), train_data['sale_price'])

# Make predictions on the validation data
predicted_prices = model.predict(val_data)

# Create the base_result dataframe with house_id and predicted price
compare_result = pd.DataFrame({'house_id': val_data['house_id'], 'price': predicted_prices})

# Display the resulting dataframe
print(compare_result)