Skip to content

Personal loans are a lucrative revenue stream for banks. The typical interest rate of a two-year loan in the United Kingdom is around 10%. This might not sound like a lot, but in September 2022 alone UK consumers borrowed around £1.5 billion, which would mean approximately £300 million in interest generated by banks over two years!

You have been asked to work with a bank to clean the data they collected as part of a recent marketing campaign, which aimed to get customers to take out a personal loan. They plan to conduct more marketing campaigns going forward so would like you to ensure it conforms to the specific structure and data types that they specify so that they can then use the cleaned data you provide to set up a PostgreSQL database, which will store this campaign's data and allow data from future campaigns to be easily imported.

They have supplied you with a csv file called "bank_marketing.csv", which you will need to clean, reformat, and split the data, saving three final csv files. Specifically, the three files should have the names and contents as outlined below:

client.csv

columndata typedescriptioncleaning requirements
client_idintegerClient IDN/A
ageintegerClient's age in yearsN/A
jobobjectClient's type of jobChange "." to "_"
maritalobjectClient's marital statusN/A
educationobjectClient's level of educationChange "." to "_" and "unknown" to np.NaN
credit_defaultboolWhether the client's credit is in defaultConvert to boolean data type:
1 if "yes", otherwise 0
mortgageboolWhether the client has an existing mortgage (housing loan)Convert to boolean data type:
1 if "yes", otherwise 0

campaign.csv

columndata typedescriptioncleaning requirements
client_idintegerClient IDN/A
number_contactsintegerNumber of contact attempts to the client in the current campaignN/A
contact_durationintegerLast contact duration in secondsN/A
previous_campaign_contactsintegerNumber of contact attempts to the client in the previous campaignN/A
previous_outcomeboolOutcome of the previous campaignConvert to boolean data type:
1 if "success", otherwise 0.
campaign_outcomeboolOutcome of the current campaignConvert to boolean data type:
1 if "yes", otherwise 0.
last_contact_datedatetimeLast date the client was contactedCreate from a combination of day, month, and a newly created year column (which should have a value of 2022);
Format = "YYYY-MM-DD"

economics.csv

columndata typedescriptioncleaning requirements
client_idintegerClient IDN/A
cons_price_idxfloatConsumer price index (monthly indicator)N/A
euribor_three_monthsfloatEuro Interbank Offered Rate (euribor) three-month rate (daily indicator)N/A
import pandas as pd
import numpy as np
df = pd.read_csv("bank_marketing.csv")

for col in ["credit_default", "mortgage", "previous_outcome", "campaign_outcome"]:
    print(col)
    print("--------------")
    print(df[col].value_counts())
    
# Display all columns of the DataFrame to see what the data looks like
pd.set_option('display.max_columns', None) 
print(df.head())
import pandas as pd
import numpy as np

# Load the dataset
df = pd.read_csv("bank_marketing.csv")

# Select specific columns for client.csv
selected_columns1 = df[['client_id','age', 'job', 'marital', 'education', 'credit_default', 'mortgage']]

# Clean up the data for client.csv
# Clean education column
selected_columns1["education"] = selected_columns1["education"].str.replace(".", "_")
selected_columns1["education"] = selected_columns1["education"].replace("unknown", np.NaN)

# Clean job column
selected_columns1["job"] = selected_columns1["job"].str.replace(".", "_")

# Convert credit_default and mortgage columns to bool data type
for col in ["credit_default", "mortgage"]:
    selected_columns1[col] = selected_columns1[col].map({"yes": True, "no": False, "unknown": False})
    selected_columns1[col] = selected_columns1[col].astype(bool)

# Save the modified DataFrame to a new CSV file
selected_columns1.to_csv("client.csv", index=False)

# Display the DataFrame to check if it is correct
print(selected_columns1.head())

import pandas as pd
import numpy as np

# Load the dataset
df = pd.read_csv("bank_marketing.csv")

# Select specific columns for campaign.csv
selected_columns2 = df[['client_id', 'number_contacts', 'contact_duration', 'previous_campaign_contacts', 'previous_outcome', 'campaign_outcome', 'month', 'day']]

# Clean up the data for campaign.csv
# Convert previous_outcome and campaign_outcome columns to boolean. 1 if success, else 0
for col in ["previous_outcome"]:
    selected_columns2[col] = selected_columns2[col].map({"success": 1, "failure": 0, "nonexistent": 0})
    selected_columns2[col] = selected_columns2[col].astype(bool)
    
for col in ["campaign_outcome"]:
    selected_columns2[col] = selected_columns2[col].map({"yes": 1, "no": 0, "nonexistent": 0})
    selected_columns2[col] = selected_columns2[col].astype(bool)

# Find unique values in 'month' and 'day' columns and display them
unique_months = selected_columns2['month'].unique()
unique_days = selected_columns2['day'].unique()

print("Unique months:", unique_months)
print("Unique days:", unique_days)

# Convert 'month' column from words to numbers
month_to_num = {
    'jan': '01', 'feb': '02', 'mar': '03', 'apr': '04', 'may': '05', 'jun': '06',
    'jul': '07', 'aug': '08', 'sep': '09', 'oct': '10', 'nov': '11', 'dec': '12'
}
selected_columns2['month'] = selected_columns2['month'].map(month_to_num)

# Convert 'day' column to ensure it has two digits
selected_columns2['day'] = selected_columns2['day'].astype(str).str.zfill(2)

# Combine 2022, converted month, and converted day column to create a new column 'last_contact_date_str'
selected_columns2['last_contact_date_str'] = '2022-' + selected_columns2['month'] + '-' + selected_columns2['day']

# Convert 'last_contact_date_str' to datetime format
selected_columns2['last_contact_date'] = pd.to_datetime(selected_columns2['last_contact_date_str'], errors='coerce')

# Drop the intermediate 'last_contact_date_str' column
selected_columns2.drop('last_contact_date_str', axis=1, inplace=True)

# Drop the 'month' and 'day' columns
selected_columns2.drop(['month', 'day'], axis=1, inplace=True)

# Save the DataFrame to campaign.csv file
selected_columns2.to_csv("campaign.csv", index=False)

print(selected_columns2.head())
import pandas as pd
import numpy as np

# Load the dataset
df = pd.read_csv("bank_marketing.csv")

# Select specific columns for economics.csv
selected_columns3 = df[['client_id', 'cons_price_idx', 'euribor_three_months']]

# Save the DataFrame to economics.csv file
selected_columns3.to_csv("economics.csv", index=False)

# Display the first few rows of the dataframe
print(selected_columns3.head())