Project

import pandas as pd
import numpy as np

# Start coding here...
# Load the data from the CSV file
data = pd.read_csv('bank_marketing.csv')
data.head()

# Create the "client" DataFrame
client = data[["client_id", "age", "job", "marital", "education", "credit_default", "housing", "loan"]]

# Create the "campaign" DataFrame
campaign = data[["client_id", "campaign", "month", "day", "duration", "pdays", "previous", "poutcome", "y"]]
# Create the "economics" DataFrame
economics = data[["client_id", "emp_var_rate", "cons_price_idx", "euribor3m", "nr_employed"]]

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

# Rename columns in the campaign DataFrame
campaign = campaign.rename(columns={"duration": "contact_duration",
                                    "previous": "previous_campaign_contacts",
                                    "y": "campaign_outcome",
                                    "campaign": "number_contacts",
                                    "poutcome": "previous_outcome"})
# Rename columns in the economics DataFrame
economics = economics.rename(columns={"euribor3m": "euribor_three_months",
                                      "nr_employed": "number_employed"})

# Replace "." with "_" in the "education" column
client["education"] = client["education"].str.replace(".", "_")

# Change "unknown" to null values in the "education" column
client["education"] = client["education"].replace("unknown", np.NaN)

# Convert "campaign_outcome" to binary values (1 or 0)
campaign["campaign_outcome"] = campaign["campaign_outcome"].replace({"yes": 1, "no": 0})

# Change "nonexistent" to null values in the "previous_outcome" column
campaign["previous_outcome"] = campaign["previous_outcome"].replace("nonexistent", np.NaN)

# Capitalize the values in the "month" column
campaign["month"] = campaign["month"].str.capitalize()

# Add a new column "year" with all values set as "2022" (as a string)
campaign["year"] = "2022"

# Convert the "day" column to string
campaign["day"] = campaign["day"].astype(str)

# Create the "last_contact_date" column by concatenating "year", "month", and "day"
campaign["last_contact_date"] = campaign["year"] + "-" + campaign["month"] + "-" + campaign["day"]

# Convert "last_contact_date" to datetime format
campaign["last_contact_date"] = pd.to_datetime(campaign["last_contact_date"], format="%Y-%b-%d")

# Remove the unnecessary columns
campaign.drop(["month", "day", "year"], axis=1, inplace=True)

# Save the client DataFrame
client.to_csv("client.csv", index=False)

# Save the campaign DataFrame
campaign.to_csv("campaign.csv", index=False)

# Save the economics DataFrame
economics.to_csv("economics.csv", index=False)

# SQL code for creating the client table and importing data
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;
"""
# SQL code for creating the campaign table and importing data
campaign_table = """
CREATE TABLE campaign (
    client_id SERIAL REFERENCES client (id),
    campaign_id SERIAL PRIMARY KEY,
    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;
"""

# SQL code for creating the economics table and importing data
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;
"""