Project: Designing a Bank Marketing Database

import pandas as pd
import numpy as np

# Start coding here...
csv = 'bank_marketing.csv'
data = pd.read_csv(csv)
print(data.info())

# Split the data into three DataFrames using information provided about the desired tables as your guide: one with information about the client, another containing campaign data, and a third to store information about economics at the time of the campaign
client = pd.read_csv(csv, usecols=['client_id','age','job','marital','education','credit_default','housing','loan'])
campaign = pd.read_csv(csv, usecols=['client_id','campaign','duration','pdays','previous','poutcome','y','day','month'])
economics = pd.read_csv(csv, usecols=['client_id','emp_var_rate','cons_price_idx','euribor3m','nr_employed'])

#Rename the column "client_id" to "id" in client
client = client.rename(columns={'client_id': 'id'})

#Rename the columns "duration" to "contact_duration", "previous" to "previous_campaign_contacts", "y" to "campaign_outcome", "poutcome" to "previous_outcome", and "campaign" to "number_contacts" in campaign
campaign = campaign.rename(columns={'duration': 'contact_duration','previous':'previous_campaign_contacts','y':'campaign_outcome','poutcome':'previous_outcome','campaign':'number_contacts'})

#Rename the columns "euribor3m" to "euribor_three_months" and "nr_employed" to "number_employed" in economics
economics = economics.rename(columns={'euribor3m':'euribor_three_months','nr_employed':'number_employed'})

#Clean the "education" column, changing "." to "_" and "unknown" to NumPy's null values
client['education'] = client['education'].str.replace('.',"_")
client['education'] = client['education'].replace('unknown',np.nan)

#Remove periods from the "job" column
client['job'] = client['job'].str.replace('.',"")

#Convert "success" and "failure" in the "previous_outcome" and "campaign_outcome" columns to binary (1 or 0), along with the changing "nonexistent" to NumPy's null values in "previous_outcome"
campaign['previous_outcome'] = campaign['previous_outcome'].map({'success':True,'failure':False})
campaign['campaign_outcome'] = campaign['campaign_outcome'].map({'success':True,'failure':False})
campaign['previous_outcome'] = campaign['previous_outcome'].replace('nonexistent',np.nan)

#Add a column called campaign_id in campaign, where all rows have a value of 1
campaign['campaign_id'] = 1

#Create a datetime column called last_contact_date, in the format of "year-month-day", where the year is 2022, and the month and day values are taken from the "month" and "day" columns
campaign['year'] = '2022'
campaign["month"] = campaign["month"].str.capitalize()
campaign['day'] = campaign['day'].astype(str)
campaign["last_contact_date"] = campaign["year"] + "-" + campaign["month"] + "-" + campaign["day"]
campaign["last_contact_date"] = pd.to_datetime(campaign["last_contact_date"], 
                                               format="%Y-%b-%d")

#Remove any redundant data that might have been used to create new columns, ensuring the columns in each subset of the data match the table displayed in the notebook.
campaign.drop(columns=['month','day','year'], inplace=True)

#Save the three DataFrames to csv files without an index as client.csv, campaign.csv, and economics.csv respectively.
client.to_csv('client.csv',index=False)
campaign.to_csv('campaign.csv',index=False)
economics.to_csv('economics.csv',index=False)

#Create a Python variable called client_table, containing SQL code as a multi-line string to create a table called client using values from client.csv
client_table = """CREATE TABLE client
(
    id SERIAL PRIMARY KEY,
    age INTEGER,
    job TEXT,
    marital TEXT,
    education TEXT,
    credit_default BOOLEAN,
    housing BOOLEAN,
    loan BOOLEAN
);
\copy client from 'client.csv' DELIMITER ',' CSV HEADER
"""

#Create a Python variable called campaign_table, containing SQL code as a multi-line string to create a table called campaign using values from campaign.csv
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 BOOLEAN,
    campaign_outcome BOOLEAN,
    last_contact_date DATE    
);
\copy campaign from 'campaign.csv' DELIMITER ',' CSV HEADER
"""

#Create a Python variable called economics_table, containing SQL code as a multi-line string to create a table called economics using values from economics.csv
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
);
\copy economics from 'economics.csv' DELIMITER ',' CSV HEADER
"""