Project

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 and store 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 set up a PostgreSQL database to store this campaign's data, designing the schema in a way that would 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, in order to save separate files based on the tables you will create. It is recommended to use pandas for these tasks.

Lastly, you will write the SQL code that the bank can execute to create the tables and populate with the data from the csv files. As the bank are quite strict about their security, you'll provide the database design script as a .sql file that they can then run.

You have been asked to design a database that will have three tables:

client

column	data type	description
`id`	`serial`	Client ID - primary key
`age`	`integer`	Client's age in years
`job`	`text`	Client's type of job
`marital`	`text`	Client's marital status
`education`	`text`	Client's level of education
`credit_default`	`boolean`	Whether the client's credit is in default
`housing`	`boolean`	Whether the client has an existing housing loan (mortgage)
`loan`	`boolean`	Whether the client has an existing personal loan

campaign

column	data type	description
`campaign_id`	`serial`	Campaign ID - primary key
`client_id`	`serial`	Client ID - references `id` in the `client` table
`number_contacts`	`integer`	Number of contact attempts to the client in the current campaign
`contact_duration`	`integer`	Last contact duration in seconds
`pdays`	`integer`	Number of days since contact in previous campaign (`999` = not previously contacted)
`previous_campaign_contacts`	`integer`	Number of contact attempts to the client in the previous campaign
`previous_outcome`	`boolean`	Outcome of the previous campaign
`campaign_outcome`	`boolean`	Outcome of the current campaign
`last_contact_date`	`date`	Last date the client was contacted

economics

column	data type	description
`client_id`	`serial`	Client ID - references `id` in the `client` table
`emp_var_rate`	`float`	Employment variation rate (quarterly indicator)
`cons_price_idx`	`float`	Consumer price index (monthly indicator)
`euribor_three_months`	`float`	Euro Interbank Offered Rate (euribor) three month rate (daily indicator)
`number_employed`	`float`	Number of employees (quarterly indicator)

import pandas as pd
import numpy as np
from datetime import datetime, date

## import data from csv
raw_data = pd.read_csv("bank_marketing.csv")

## split data into three data frames client, campaign, economics
raw_client_headers = ['client_id', 'age', 'job', 'marital', 'education', 'credit_default', 'housing', 'loan']
raw_campaign_headers = ['client_id', 'campaign','duration', 'pdays', 'previous', 'y', 'poutcome', 'month']
raw_economics_headers = ['client_id', 'emp_var_rate', 'cons_price_idx', 'euribor3m', 'nr_employed']

client_df = raw_data[raw_client_headers]
campaign_df = raw_data[raw_campaign_headers]
economics_df = raw_data[raw_economics_headers]

## rename columns
# client_df
# client_id -> id 
client_rename = {'client_id': 'id'}

# campaign
# campaign -> number_contacts in campaign
# duration -> contact_duration
# previous -> previous_campaign_contacts
# y -> campaign_outcome
# poutcome -> previous_outcome
campaign_rename ={
    'campaign': 'number_contacts',
    'duration': 'contact_duration',
    'previous': 'previous_campaign_contacts',
    'y': 'campaign_outcome',
    'poutcome': 'previous_outcome'
} 

# economics
# euribor3m -> euribor_three_months
# nr_employed -> number_employed in economics
economics_rename = {
    'euribor3m': 'euribor_three_months',
    'nr_employed': 'previous_outcome'
}

client_df.rename(columns=client_rename, inplace=True)
campaign_df.rename(columns=campaign_rename, inplace=True)
economics_df.rename(columns=economics_rename, inplace=True)

## clean education column: "." to "_" and "unknown" to Numpy null"
client_df['education'] = client_df['education'].str.replace('.', '_')
client_df['education'] = client_df['education'].replace('unknown', np.nan)

## remove periods from the job column
client_df['job'] = client_df['job'].str.replace('.', '')

## convert success and failure in previous_outcome and campaign_outcome to bool 0;1
campaign_df['previous_outcome'] = campaign_df['previous_outcome'].apply(lambda x: 1 if x == 'success' else (0 if x == 'failure' else x))
campaign_df['campaign_outcome'] = campaign_df['campaign_outcome'].apply(lambda x: 1 if x == 'success' else (0 if x == 'failure' else x))
## convert nonexistent to np.nan in previous_outcome
campaign_df['previous_outcome'] = campaign_df['previous_outcome'].replace('nonexistent', np.nan)
campaign_df['campaign_outcome'] = campaign_df['campaign_outcome'].replace('nonexistent', np.nan)

## Add campaign_id to campaign where all values are 1
campaign_df['campaign_id'] = 1

## Add Datetime column to campaign with name "last_contact_date" %Y-%m-%d with "2022" and "month"-"day" columns of df.
# ATTENTION DAY COLUMN IS NOT AVAILABLE -> BUG REPORTED TO datacamp
campaign_df['last_contact_date__helper'] = campaign_df.apply(lambda row: f'2022-{row["month"]}-01', axis=1)
campaign_df['last_contact_date'] = pd.to_datetime(campaign_df['last_contact_date__helper'])

## remove columns
# campaign: month, last_contact_date__helper
campaign_df.drop(['last_contact_date__helper', 'month'], axis=1, inplace=True)

# change order of campaign_df
campaign_df = campaign_df[
    ['campaign_id',
    'client_id',
    'number_contacts',
    'contact_duration',
    'pdays',
    'previous_campaign_contacts',
    'previous_outcome',
    'campaign_outcome',
    'last_contact_date']
]

## save the DataFrame to CSV files without index
client_df.to_csv('client.csv', index=False)
campaign_df.to_csv('campaign.csv', index=False)
economics_df.to_csv('economics.csv', index=False)

## Create tables
client_table = """
CREATE TABLE client (
    id serial primary key, 
    age integer, 
    job text, 
    marital text, 
    education text, 
    credit_default bool, 
    housing bool, 
    loan bool
    );"""

campaign_table = """
CREATE TABLE campaign (
    campaign_id serial PRIMARY KEY, 
    client_id serial references client (id), 
    number_contacts integer, 
    contact_duration integer, 
    pdays integer, 
    previous_campaign_contacts integer, 
    previous_outcome bool, 
    campaign_outcome bool, 
    last_contact_date date);"""

economics_table = """
CREATE TABLE economics (
    client_id serial references client (id), 
    emp_var_rate float, 
    cons_price_idx float, 
    euribor_three_months float, 
    number_employed float);"""

## add final line to copy data to database
client_table = client_table + "\copy client from 'client.csv' DELIMITER ',' CSV HEADER;"
campaign_table = campaign_table + "\copy campaign from 'campaign.csv' DELIMITER ',' CSV HEADER;"
economics_table = economics_table + "\copy economics from 'economics.csv' DELIMITER ',' CSV HEADER;"

.mfe-app-workspace-kj242g{position:absolute;top:-8px;}.mfe-app-workspace-11ezf91{display:inline-block;}.mfe-app-workspace-11ezf91:hover .Anchor__copyLink{visibility:visible;}client

campaign

economics

client