Workspace
Mohammad Syed/

Project: Cleaning Bank Marketing Campaign Data

0
Beta
Spinner

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
mortgageboolWhether the client has an existing mortgage (housing loan)Convert to boolean data type

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
campaign_outcomeboolOutcome of the current campaignConvert to boolean data type
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
import matplotlib as plt
import matplotlib.pyplot as pyp
import missingno as msno

# Import CSVs headers

data_import = pd.read_csv('bank_marketing.csv')
data_import.head()
msno.matrix(data_import)
# Create a histogram for the 'age' column
pyp.hist(data_import['age'], bins=10)

# Set plot title and labels
pyp.title('Age Distribution')
pyp.xlabel('Age')
pyp.ylabel('Frequency')

# Show the plot
pyp.show()

The data set contains a combined list of attributes across all types of information. Exploring the information below, we can see that:

  • The dataset is a complete dataset without missing values
  • The dataset will need to be split to get the columns in each of the three files
  • Each column needs to be formatted into the appropriate data type in order to use it
  • There are some categorical data types (e.g. previous outcome) that need definition
#Analyse the data types

data_import.info()
#Check for unique values in boolean columns

print(
    data_import['credit_default'].unique(),
    data_import['mortgage'].unique(),
    data_import['previous_outcome'].unique(),
    data_import['campaign_outcome'].unique()

)

As we need to have boolean types, we need to replace the values that don't fall into either yes/no with a no. This is done because:

  • Business logic requires us to state that an unknown outcome is a no by default as we cannot confirm a yes without clear proof
#Clean values with null/ unknown/ error, before then replacing with a Boolean value. Done seperately for clarity of steps.

cr_rep = data_import['credit_default'] == 'unknown'
data_import.loc[cr_rep, 'credit_default'] = 'no'

mrt_rep = data_import['mortgage'] == 'unknown'
data_import.loc[mrt_rep, 'mortgage'] = 'no'

pr_rep = data_import['previous_outcome'] == 'nonexistent'
data_import.loc[pr_rep, 'previous_outcome'] = 'failure'

co_rep = data_import['campaign_outcome'] == ''
data_import.loc[co_rep, 'campaign_outcome'] = 'no'

print(
    data_import['credit_default'].unique(),
    data_import['mortgage'].unique(),
    data_import['previous_outcome'].unique(),
    data_import['campaign_outcome'].unique()

)
# Replace text boolean with numeric value to enable datatype as Boolean. E.g. 'no' = 0, 'yes' = 1, etc.

bool_values = {'no': 0,
              'yes': 1,
              'failure': 0,
              'success': 1
             }

data_import['credit_default'] = data_import['credit_default'].replace(bool_values)
data_import['mortgage'] = data_import['mortgage'].replace(bool_values)
data_import['previous_outcome'] = data_import['previous_outcome'].replace(bool_values)
data_import['campaign_outcome'] = data_import['campaign_outcome'].replace(bool_values)

print(
    data_import['credit_default'].unique(),
    data_import['mortgage'].unique(),
    data_import['previous_outcome'].unique(),
    data_import['campaign_outcome'].unique()
)
#Change basic data types using a dictionary

data_types = {'client_id':'int',
              'age':'int',
              'job': 'object',
              'marital': 'object',
              'education': 'object',
              'credit_default': 'bool',
              'mortgage': 'bool',
              'month': 'object',
              'day': 'int',
              'contact_duration': 'int',
              'number_contacts': 'int',
              'previous_campaign_contacts': 'int',
              'previous_outcome': 'bool',
              'cons_price_idx': 'float',
              'euribor_three_months': 'float',
              'campaign_outcome': 'bool',
             }

data_formatted = data_import.astype(data_types)

#Validate changes to data types have been executed
data_formatted.info()
#Ensure that Boolean data type has not replaced values with single value
print(
    data_formatted['credit_default'].unique(),
    data_formatted['mortgage'].unique(),
    data_formatted['previous_outcome'].unique(),
    data_formatted['campaign_outcome'].unique()

)
#Ensure that the distribution of values before & after the boolean data type change has not corrupted the dataset values.

print(
    data_formatted.groupby(by='credit_default').max(),
    data_import.groupby(by='credit_default').mean(),
    data_formatted.groupby(by='mortgage').mean(),
    data_import.groupby(by='mortgage').mean(),
    data_formatted.groupby(by='previous_outcome').mean(),
    data_import.groupby(by='previous_outcome').mean(),
    data_formatted.groupby(by='campaign_outcome').mean(),
    data_import.groupby(by='campaign_outcome').mean()
)
Hidden output
data_formatted.describe()

Now it is time to split the data into the three files by creating x3 Dataframes.




  • AI Chat
  • Code