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
client.csv| column | data type | description | cleaning requirements |
|---|---|---|---|
client_id | integer | Client ID | N/A |
age | integer | Client's age in years | N/A |
job | object | Client's type of job | Change "." to "_" |
marital | object | Client's marital status | N/A |
education | object | Client's level of education | Change "." to "_" and "unknown" to np.NaN |
credit_default | bool | Whether the client's credit is in default | Convert to boolean data type:1 if "yes", otherwise 0 |
mortgage | bool | Whether the client has an existing mortgage (housing loan) | Convert to boolean data type:1 if "yes", otherwise 0 |
campaign.csv
campaign.csv| column | data type | description | cleaning requirements |
|---|---|---|---|
client_id | integer | Client ID | N/A |
number_contacts | integer | Number of contact attempts to the client in the current campaign | N/A |
contact_duration | integer | Last contact duration in seconds | N/A |
previous_campaign_contacts | integer | Number of contact attempts to the client in the previous campaign | N/A |
previous_outcome | bool | Outcome of the previous campaign | Convert to boolean data type:1 if "success", otherwise 0. |
campaign_outcome | bool | Outcome of the current campaign | Convert to boolean data type:1 if "yes", otherwise 0. |
last_contact_date | datetime | Last date the client was contacted | Create 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
economics.csv| column | data type | description | cleaning requirements |
|---|---|---|---|
client_id | integer | Client ID | N/A |
cons_price_idx | float | Consumer price index (monthly indicator) | N/A |
euribor_three_months | float | Euro Interbank Offered Rate (euribor) three-month rate (daily indicator) | N/A |
Project Summary
This project involves analyzing a dataset related to bank marketing campaigns. The data is organized into three main dataframes: client, campaign, and economics, which are combined into a comprehensive dataframe called bank_marketing_data. Each dataframe contains specific information about the clients, the marketing campaigns, and economic indicators, respectively.
The primary objective of this project is to analyze the effectiveness of the bank's marketing campaigns by examining various factors such as client demographics, contact details, and economic indicators. This analysis will help in understanding the key drivers of successful campaign outcomes and provide insights for future marketing strategies.
#Import necessary packages
import pandas as pd
import numpy as np
# Start coding here...# Load the bank marketing data from a CSV file into a DataFrame
bank_marketing_data = pd.read_csv("bank_marketing.csv")
# Iterate over the specified columns to print their value counts
for col in ["credit_default", "mortgage", "previous_outcome", "campaign_outcome"]:
# Print the column name
print(col)
# Print a separator line
print("--------------")
# Print the value counts of the current column
print(bank_marketing_data[col].value_counts())# Display the DataFrame `bank_marketing_data`
bank_marketing_data# Replace periods in the 'job' column with underscores
bank_marketing_data['job'] = bank_marketing_data['job'].str.replace(".", "_")# Define a dictionary for replacing values in the 'education' column
education_dict = {
'.' : '_',
'unknown' : 'np.NaN'
}
# Replace periods in the 'education' column with underscores
bank_marketing_data['education'] = bank_marketing_data['education'].str.replace('.', '_')
# Replace 'unknown' values in the 'education' column with NaN
bank_marketing_data['education'] = bank_marketing_data['education'].replace('unknown', np.NaN)# Define a dictionary for conversion of 'credit_default' values
credef_dict = {
"yes": 1, # Map 'yes' to True for boolean
"no": 0, # Map 'no' to False for boolean
"unknown": 0 # Map 'unknown' to False (or adjust as needed)
}
# Iterate over the specified columns to clean and convert to boolean
for col in ["credit_default"]:
# Replace values in the column based on the dictionary
bank_marketing_data[col] = bank_marketing_data[col].replace(credef_dict)
# Convert the column to boolean data type
bank_marketing_data[col] = bank_marketing_data[col].astype(bool)# Define a dictionary for conversion of 'mortgage' values
mort_dict = {
"yes": 1, # Map 'yes' to True for boolean
"no": 0, # Map 'no' to False for boolean
"unknown": 0 # Map 'unknown' to False (or adjust as needed)
}
# Iterate over the specified columns to clean and convert to boolean
for col in ["mortgage"]:
# Replace values in the column based on the dictionary
bank_marketing_data[col] = bank_marketing_data[col].replace(mort_dict)
# Convert the column to boolean data type
bank_marketing_data[col] = bank_marketing_data[col].astype(bool)# Map 'previous_outcome' values to integers (1 for 'success', 0 for 'failure' and 'nonexistent')
bank_marketing_data["previous_outcome"] = bank_marketing_data["previous_outcome"].map({
"success": 1,
"failure": 0,
"nonexistent": 0})
# Convert the 'previous_outcome' column to boolean data type
bank_marketing_data['previous_outcome'] = bank_marketing_data['previous_outcome'].astype(bool)# Map 'campaign_outcome' values to integers (1 for 'yes', 0 for 'no')
bank_marketing_data["campaign_outcome"] = bank_marketing_data["campaign_outcome"].map({
"yes": 1,
"no": 0})
# Convert the 'campaign_outcome' column to boolean data type
bank_marketing_data['campaign_outcome'] = bank_marketing_data['campaign_outcome'].astype(bool)# Combine 'month' and 'day' columns with a fixed year to create a date string
bank_marketing_data['last_contact_date'] = pd.to_datetime(
# Convert the combined string to datetime format
"2022-" + bank_marketing_data['month'].astype(str) + "-" + bank_marketing_data['day'].astype(str), format="%Y-%b-%d")# Select desired columns for client dataframe
client_selected_columns = ['client_id', 'age', 'job', 'marital', 'education', 'credit_default', 'mortgage']
# Create client dataframe from bank_marketing_data
client = bank_marketing_data[client_selected_columns]
# Select desired columns for campaign dataframe
campaign_selected_columns = ['client_id', 'number_contacts', 'contact_duration', 'previous_campaign_contacts', 'previous_outcome', 'campaign_outcome', 'last_contact_date']
# Create campaign dataframe from bank_marketing_data
campaign = bank_marketing_data[campaign_selected_columns]
# Select desired columns for economics dataframe
economics_selected_columns = ['client_id', 'cons_price_idx', 'euribor_three_months']
# Create economics dataframe from bank_marketing_data
economics = bank_marketing_data[economics_selected_columns]# Define filename for client dataframe
client_filename = "client.csv"
# Define filename for campaign dataframe
campaign_filename = "campaign.csv"
# Define filename for economics dataframe
economics_filename = "economics.csv"
# Save client DataFrame to CSV file (without index)
client.to_csv(client_filename, index=False)
# Save campaign DataFrame to CSV file (without index)
campaign.to_csv(campaign_filename, index=False)
# Save economics DataFrame to CSV file (without index)
economics.to_csv(economics_filename, index=False)