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`

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`

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`

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 Libraries

import pandas as pd
import numpy as np

Tasks

Split and tidy bank_marketing.csv, storing as three DataFrames called client, campaign, and economics, each containing the columns outlined in the notebook and formatted to the data types listed.
Save the three DataFrames to csv files, without an index, as client.csv, campaign.csv, and economics.csv respectively.

# read bank_marketing csv and show first rows
df = pd.read_csv("bank_marketing.csv")
df.head()

df.head()

df.dtypes

1. Create and clean clients_df

# keep only relevant columns for clients_df 
clients_df = df[["client_id", "age", "job", "marital", "education", "credit_default", "mortgage"]]

# create map for boolean cast
map = {'yes': 1, 'no': 0, 'unknown':0}

# cast 'credit_default' and 'mortgage' as bool with 1 for 'yes' and 0 for 'no'
clients_df['credit_default'] = clients_df['credit_default'].map(map).astype(bool)
clients_df['mortgage'] = clients_df['mortgage'].map(map).astype(bool)

# replace strings
clients_df['job'] = clients_df['job'].str.replace('.','_')
clients_df['education'] = clients_df['education'].str.replace('.','_').replace('unknown',np.nan)

# show data types
clients_df.dtypes

# show clients_df first rows
clients_df.head()

2. Create and clean campaign_df

# keep only relevant columns for campaign_df 
campaign_df = df[['client_id', 'number_contacts', 'contact_duration', 'previous_campaign_contacts', 'previous_outcome', 'campaign_outcome', 'day','month']]

# create new column year 2022
campaign_df['year'] = '2022'

# cast day as string
campaign_df['day'] = campaign_df['day'].astype(str)

# create column last_contact_date as concat day, month and year and cast as datetime
campaign_df['last_contact_date'] = pd.to_datetime(campaign_df['year'] + '-' + campaign_df['month'] + '-' + campaign_df['day'], format='%Y-%b-%d')

# drop columns day, month, year
campaign_df = campaign_df.drop(columns=['day', 'month', 'year'])

# map campaign_outcome and change type to bool
campaign_df['campaign_outcome'] = campaign_df['campaign_outcome'].map(map).astype(bool)

# map previous outcome and change type to bool
campaign_df['previous_outcome'] = campaign_df['previous_outcome'].apply(lambda x: 1 if x == 'success' else 0).astype(bool)

# show campaign_df data types
campaign_df.dtypes

# show campaign_df first rows
campaign_df.head()

3. Create and clean economics_df

# keep only relevant columns for economics_df 
economics_df = df[['client_id', 'cons_price_idx', 'euribor_three_months']]

# show economics_df data types
economics_df.dtypes

‌
‌
‌