Bank Marketing ETL Project
DataCamp
Completed
Note
This is a DataCamp project. I’m not responsible for any part of the problem scope, resources, documents, files, etc. That being said, I converted the Jupyter notebook provided into a Quarto Markdown file to add to my portfolio.
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 pandas as pd
import numpy as np
# Start coding here...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())credit_default
--------------
no 32588
unknown 8597
yes 3
Name: credit_default, dtype: int64
mortgage
--------------
yes 21576
no 18622
unknown 990
Name: mortgage, dtype: int64
previous_outcome
--------------
nonexistent 35563
failure 4252
success 1373
Name: previous_outcome, dtype: int64
campaign_outcome
--------------
no 36548
yes 4640
Name: campaign_outcome, dtype: int64client = df[['client_id', 'age', 'job', 'marital', 'education', 'credit_default', 'mortgage']]
campaign = df[['client_id', 'number_contacts', 'contact_duration', 'previous_campaign_contacts', 'previous_outcome', 'campaign_outcome', 'day', 'month']]
economics = df[['client_id', 'cons_price_idx', 'euribor_three_months']]
print(client.head())
print(campaign.head())
print(economics.head()) client_id age job marital education credit_default mortgage
0 0 56 housemaid married basic.4y no no
1 1 57 services married high.school unknown no
2 2 37 services married high.school no yes
3 3 40 admin. married basic.6y no no
4 4 56 services married high.school no no
client_id number_contacts contact_duration ... campaign_outcome day month
0 0 1 261 ... no 13 may
1 1 1 149 ... no 19 may
2 2 1 226 ... no 23 may
3 3 1 151 ... no 27 may
4 4 1 307 ... no 3 may
[5 rows x 8 columns]
client_id cons_price_idx euribor_three_months
0 0 93.994 4.857
1 1 93.994 4.857
2 2 93.994 4.857
3 3 93.994 4.857
4 4 93.994 4.857import numpy as np
client_c = client.copy()
client_c['job'] = client_c['job'].replace('.', '_')
client_c['education'] = client_c['education'].str.replace('.', '_')
client_c['education'].replace('unknown', np.NaN, inplace=True)
client_c['credit_default'] = client_c['credit_default'].apply(lambda x: 1 if x == 'yes' else 0)
client_c['credit_default'] = client_c['credit_default'].astype('bool')
client_c['mortgage'] = client_c['mortgage'].apply(lambda x: 1 if x == 'yes' else 0)
client_c['mortgage'] = client_c['mortgage'].astype('bool')
print(client_c.head()) client_id age job marital education credit_default mortgage
0 0 56 housemaid married basic_4y False False
1 1 57 services married high_school False False
2 2 37 services married high_school False True
3 3 40 admin. married basic_6y False False
4 4 56 services married high_school False Falsecampaign_c = campaign.copy()
campaign_c['previous_outcome'] = campaign_c['previous_outcome'].apply(lambda x: 1 if x == 'success' else 0).astype('bool')
campaign_c['campaign_outcome'] = campaign_c['campaign_outcome'].apply(lambda x: 1 if x == 'yes' else 0).astype('bool')
campaign_c['year'] = 2022
campaign_c['last_contact_date'] = pd.to_datetime(campaign_c['day'].astype(str) + campaign_c['month'] + campaign_c['year'].astype(str), format='%d%b%Y')
campaign_c.head()| client_id | number_contacts | contact_duration | previous_campaign_contacts | previous_outcome | campaign_outcome | day | month | year | last_contact_date | |
|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 0 | 1 | 261 | 0 | False | False | 13 | may | 2022 | 2022-05-13 |
| 1 | 1 | 1 | 149 | 0 | False | False | 19 | may | 2022 | 2022-05-19 |
| 2 | 2 | 1 | 226 | 0 | False | False | 23 | may | 2022 | 2022-05-23 |
| 3 | 3 | 1 | 151 | 0 | False | False | 27 | may | 2022 | 2022-05-27 |
| 4 | 4 | 1 | 307 | 0 | False | False | 3 | may | 2022 | 2022-05-03 |
campaign_c['previous_outcome'].value_counts()False 39815
True 1373
Name: previous_outcome, dtype: int64client = client_c
campaign = campaign_c.drop(['month', 'day', 'year'], axis=1)
print(client.head())
print(campaign.head())
print(economics.head()) client_id age job marital education credit_default mortgage
0 0 56 housemaid married basic_4y False False
1 1 57 services married high_school False False
2 2 37 services married high_school False True
3 3 40 admin. married basic_6y False False
4 4 56 services married high_school False False
client_id number_contacts ... campaign_outcome last_contact_date
0 0 1 ... False 2022-05-13
1 1 1 ... False 2022-05-19
2 2 1 ... False 2022-05-23
3 3 1 ... False 2022-05-27
4 4 1 ... False 2022-05-03
[5 rows x 7 columns]
client_id cons_price_idx euribor_three_months
0 0 93.994 4.857
1 1 93.994 4.857
2 2 93.994 4.857
3 3 93.994 4.857
4 4 93.994 4.857client.to_csv('client.csv', index=False)
campaign.to_csv('campaign.csv', index=False)
economics.to_csv('economics.csv', index=False)