Skip to content
Project: Cleaning Bank Marketing Campaign Data
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 |
mortgage | bool | Whether the client has an existing mortgage (housing loan) | Convert to boolean data type |
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 |
campaign_outcome | bool | Outcome of the current campaign | Convert to boolean data type |
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 |
import pandas as pd
import numpy as np# Load csv file into pandas dataframe
bank_marketing_df = pd.read_csv('bank_marketing.csv')bank_marketing_df.head()# Replace '.' with '_' in the 'education' column
bank_marketing_df['education'] = bank_marketing_df['education'].str.replace('.', '_', regex=False)
# Replace 'unknown' with NaN in the 'education' column
bank_marketing_df['education'] = bank_marketing_df['education'].replace('unknown', np.NaN)
# Remove periods from the 'job' column
bank_marketing_df['job'] = bank_marketing_df['job'].str.replace('.', '', regex=False)
# # Replace 'unknown' with NaN in the 'credit_default' column
# bank_marketing_df['credit_default'] = bank_marketing_df['credit_default'].replace('unknown', np.NaN)
# Convert 'credit_default' to binary values
bank_marketing_df['credit_default'] = bank_marketing_df['credit_default'].map({'yes': 1, 'no': 0, 'unknown': 0})
# Convert 'mortgage' to binary values, treating 'yes' as 1 and both 'no' and 'unknown' as 0
bank_marketing_df['mortgage'] = bank_marketing_df['mortgage'].map({'yes': 1, 'no': 0, 'unknown': 0})
# Convert 'campaign_outcome' to binary values
bank_marketing_df['campaign_outcome'] = bank_marketing_df['campaign_outcome'].map({'yes': 1, 'no': 0})
# Convert 'previous_outcome' to binary values
bank_marketing_df['previous_outcome'] = bank_marketing_df['previous_outcome'].map({'success': 1, 'failure': 0, 'nonexistent': 0})
# Display the head of the dataframe to verify changes
bank_marketing_df.head()# Basic statistical analysis
bank_marketing_df.describe()# Missing values
bank_marketing_df.isnull().sum()# New column 'last_contact_date'
# Create new column 'year'
bank_marketing_df['year'] = '2022'
# Capitalize the values of 'month'
bank_marketing_df['month'] = bank_marketing_df['month'].str.capitalize()
# Convert the 'day' column to a string
bank_marketing_df['day'] = bank_marketing_df['day'].astype('string')
# Create column 'last_contact_date' and convert it to a datetime
bank_marketing_df['last_contact_date'] = bank_marketing_df['year'] + '-' + bank_marketing_df['month'] + '-' + bank_marketing_df['day']
bank_marketing_df['last_contact_date'] = pd.to_datetime(bank_marketing_df['last_contact_date'])
# Deleting columns no longer needed
bank_marketing_df.drop(columns = ['year', 'month', 'day'], inplace = True)# Splitting the dataframe into three separate dataframes as per the outlined columns
# DataFrame for clients information
client_columns = ['client_id', 'age', 'job', 'marital', 'education', 'credit_default', 'mortgage']
client = bank_marketing_df[client_columns]
# DataFrame for campaigns
campaign_columns = ['client_id', 'number_contacts', 'contact_duration', 'previous_campaign_contacts', 'previous_outcome', 'campaign_outcome', 'last_contact_date']
campaign = bank_marketing_df[campaign_columns]
# DataFrame for economics
economics_columns = ['client_id', 'cons_price_idx', 'euribor_three_months']
economics = bank_marketing_df[economics_columns]# Convert data types as per the requirement
client = client.astype({'client_id': 'int64', 'age': 'int64', 'job': 'object', 'marital': 'object', 'education': 'object', 'credit_default': 'bool', 'mortgage': 'bool'})
campaign = campaign.astype({'client_id': 'int64', 'last_contact_date': 'datetime64[ns]', 'contact_duration': 'int64', 'number_contacts': 'int64', 'previous_campaign_contacts': 'int64', 'previous_outcome': 'bool', 'campaign_outcome': 'bool'})
economics = economics.astype({'client_id': 'int64', 'cons_price_idx': 'float64', 'euribor_three_months': 'float64'})client.info()campaign.info()economics.info()# Saving the data to csv files
client.to_csv('client.csv', index=False)
campaign.to_csv('campaign.csv', index=False)
economics.to_csv('economics.csv', index=False)