churn-prediction/notebooks/feature_engineering.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Feature Engineering\n",
    "\n",
    "**Consideration for Feature Engineering:**\n",
    "\n",
    "1. **One-Hot Encoding**: Representing categorical variables as binary features. Can help capture any non-linear relationships between the categories and the target variable.\n",
    "\n",
    "2. **Binning or Discretization**: COnverting continuous variables into categorical variables by dividing them into bins or discrete intervals. \n",
    "\n",
    "3. **Feature Scaling**: Scaling numerical features to ensure that they are on a similar scale and have a similar range. *Standardization* (scaling to zero mean and unit variance) or *normalization* (scaling to a specified range, e.g., [0, 1]) can be applied to numerical features. This helps prevent features with larger values from dominating the model and ensures fair comparisons.\n",
    "\n",
    "4. **Feature Creation**: Combine domain knowledge and insights from the EDA to identify and create potential features that may be relevant for churn prediction. \n",
    "\n",
    "Feature engineering is an **iterative process**, and it often requires experimentation and evaluation of different approaches. Thus, this notebook will explore 2 loops of feature engineering. Only in the second loop it will be created new features from the combination of existing ones.\n",
    "**It will be evaluated the results in model performance to decide the final preprocessing code for model input.**\n",
    "\n",
    "`Obs. Scaling will be performed to all data before model input using StandartScaler. `\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## First Loop of Feature Engineering\n",
    "\n",
    "### Actions to be performed:\n",
    "\n",
    "**_Drop:_**\n",
    "> Reason: no effect on \"Exited\".\n",
    "> - `CustomerId`: random values of customer ID.\n",
    "> - `Surname`: customer surname.\n",
    "\n",
    "\n",
    "**_One-hot encoding:_**\n",
    "> Reason: categorical variables\n",
    "> - `Geography`: geography of the customer. \n",
    "> - `Gender`: customer gender.\n",
    "> - `NumOfProducts`: number of products that a customer has purchased through the bank\n",
    "\n",
    "\n",
    "**_Binning into some intervals:_**\n",
    "> Reason: can help capture non-linear relationships and patterns that might not be apparent when treating the variable as continuous\n",
    "> - `CreditScore`: credit score of customer.\n",
    "\n",
    "**_No transformation for now:_**\n",
    "> - `IsActiveMember`: active customers.\n",
    "> - `Tenure`: years the customer is a client of the bank.\n",
    "> - `HasCrCard`: denotes whether or not a customer has a credit card. \n",
    "> - `Age`: customer age. *[scaled later]*\n",
    "> - `EstimatedSalary`: customer estimated salary. *[scaled later]*\n",
    "> - `Balance`: balance of customer in the bank account. *[scaled later]*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CreditScore</th>\n",
       "      <th>Age</th>\n",
       "      <th>Tenure</th>\n",
       "      <th>Balance</th>\n",
       "      <th>HasCrCard</th>\n",
       "      <th>IsActiveMember</th>\n",
       "      <th>EstimatedSalary</th>\n",
       "      <th>Exited</th>\n",
       "      <th>Geography_Germany</th>\n",
       "      <th>Geography_Spain</th>\n",
       "      <th>Gender_Male</th>\n",
       "      <th>CreditScoreBins_Medium</th>\n",
       "      <th>CreditScoreBins_High</th>\n",
       "      <th>CreditScoreBins_Very High</th>\n",
       "      <th>NumOfProducts_2</th>\n",
       "      <th>NumOfProducts_3</th>\n",
       "      <th>NumOfProducts_4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>619</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>101348.88</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>608</td>\n",
       "      <td>41</td>\n",
       "      <td>1</td>\n",
       "      <td>83807.86</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>112542.58</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>502</td>\n",
       "      <td>42</td>\n",
       "      <td>8</td>\n",
       "      <td>159660.80</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>113931.57</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>699</td>\n",
       "      <td>39</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>93826.63</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>850</td>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>125510.82</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>79084.10</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CreditScore  Age  Tenure    Balance  HasCrCard  IsActiveMember  \\\n",
       "0          619   42       2       0.00          1               1   \n",
       "1          608   41       1   83807.86          0               1   \n",
       "2          502   42       8  159660.80          1               0   \n",
       "3          699   39       1       0.00          0               0   \n",
       "4          850   43       2  125510.82          1               1   \n",
       "\n",
       "   EstimatedSalary  Exited  Geography_Germany  Geography_Spain  Gender_Male  \\\n",
       "0        101348.88       1              False            False        False   \n",
       "1        112542.58       0              False             True        False   \n",
       "2        113931.57       1              False            False        False   \n",
       "3         93826.63       0              False            False        False   \n",
       "4         79084.10       0              False             True        False   \n",
       "\n",
       "   CreditScoreBins_Medium  CreditScoreBins_High  CreditScoreBins_Very High  \\\n",
       "0                    True                 False                      False   \n",
       "1                    True                 False                      False   \n",
       "2                    True                 False                      False   \n",
       "3                   False                  True                      False   \n",
       "4                   False                 False                       True   \n",
       "\n",
       "   NumOfProducts_2  NumOfProducts_3  NumOfProducts_4  \n",
       "0            False            False            False  \n",
       "1            False            False            False  \n",
       "2            False             True            False  \n",
       "3             True            False            False  \n",
       "4            False            False            False  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load data\n",
    "df = pd.read_csv('../data/01_raw/abandono_clientes.csv')\n",
    "\n",
    "# Drop irrelevant variables\n",
    "df = df.drop(['RowNumber', 'CustomerId', 'Surname'], axis=1)\n",
    "\n",
    "# Binning \n",
    "#-- CreditScore column\n",
    "bins = [350, 500, 650, 800, 850]  # Define the bin edges\n",
    "labels = ['Low', 'Medium', 'High', 'Very High']  # Define the bin labels\n",
    "\n",
    "df['CreditScoreBins'] = pd.cut(df['CreditScore'], bins=bins, labels=labels)\n",
    "\n",
    "# One hot encoding for categorical variables\n",
    "df = pd.get_dummies(df, columns =['Geography', 'Gender', 'CreditScoreBins', 'NumOfProducts'], drop_first = True)\n",
    "\n",
    "# Save to csv\n",
    "df.to_csv('../data/04_feature/features_first_loop.csv', index=False)\n",
    "\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Tenure</th>\n",
       "      <th>Balance</th>\n",
       "      <th>HasCrCard</th>\n",
       "      <th>IsActiveMember</th>\n",
       "      <th>Exited</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.00000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>5.012800</td>\n",
       "      <td>76485.889288</td>\n",
       "      <td>0.70550</td>\n",
       "      <td>0.515100</td>\n",
       "      <td>0.203700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>2.892174</td>\n",
       "      <td>62397.405202</td>\n",
       "      <td>0.45584</td>\n",
       "      <td>0.499797</td>\n",
       "      <td>0.402769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>5.000000</td>\n",
       "      <td>97198.540000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>7.000000</td>\n",
       "      <td>127644.240000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>10.000000</td>\n",
       "      <td>250898.090000</td>\n",
       "      <td>1.00000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Tenure        Balance    HasCrCard  IsActiveMember        Exited\n",
       "count  10000.000000   10000.000000  10000.00000    10000.000000  10000.000000\n",
       "mean       5.012800   76485.889288      0.70550        0.515100      0.203700\n",
       "std        2.892174   62397.405202      0.45584        0.499797      0.402769\n",
       "min        0.000000       0.000000      0.00000        0.000000      0.000000\n",
       "25%        3.000000       0.000000      0.00000        0.000000      0.000000\n",
       "50%        5.000000   97198.540000      1.00000        1.000000      0.000000\n",
       "75%        7.000000  127644.240000      1.00000        1.000000      0.000000\n",
       "max       10.000000  250898.090000      1.00000        1.000000      1.000000"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10000 entries, 0 to 9999\n",
      "Data columns (total 14 columns):\n",
      " #   Column                     Non-Null Count  Dtype  \n",
      "---  ------                     --------------  -----  \n",
      " 0   Tenure                     10000 non-null  int64  \n",
      " 1   Balance                    10000 non-null  float64\n",
      " 2   HasCrCard                  10000 non-null  int64  \n",
      " 3   IsActiveMember             10000 non-null  int64  \n",
      " 4   Exited                     10000 non-null  int64  \n",
      " 5   Geography_Germany          10000 non-null  bool   \n",
      " 6   Geography_Spain            10000 non-null  bool   \n",
      " 7   Gender_Male                10000 non-null  bool   \n",
      " 8   CreditScoreBins_Medium     10000 non-null  bool   \n",
      " 9   CreditScoreBins_High       10000 non-null  bool   \n",
      " 10  CreditScoreBins_Very High  10000 non-null  bool   \n",
      " 11  NumOfProducts_2            10000 non-null  bool   \n",
      " 12  NumOfProducts_3            10000 non-null  bool   \n",
      " 13  NumOfProducts_4            10000 non-null  bool   \n",
      "dtypes: bool(9), float64(1), int64(4)\n",
      "memory usage: 478.6 KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Second Loop:"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From EDA we were able to extranct some insights to formulate new variables:\n",
    "\n",
    "- \"Balance\" varies significantly by \"Geography\". From EDA avg for Germany is around 130k, while for Spain and France is around 60k. Lets create a variable \"Balance_GreographyMean_Ratio\".\n",
    "- Estimated Salary and Credit Score could be related\n",
    "- Age and Tenure can have a relationship.\n",
    "\n",
    "\n",
    "**_Features to be created:_**\n",
    "> - `EstimatedSalary_CreditScore_Ratio`\n",
    "> - `CreditScore_EstimatedSalary_Ratio`\n",
    "> - `Balance_EstimatedSalary_Ratio`\n",
    "> - `Balance_IsZero`\n",
    "> - `Balance_GeographyMean_Ratio`\n",
    "> - `Tenure_Age_Ratio`\n",
    "> - `NumOfProducts_Tenure_Ratio`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CreditScore</th>\n",
       "      <th>Age</th>\n",
       "      <th>Tenure</th>\n",
       "      <th>Balance</th>\n",
       "      <th>HasCrCard</th>\n",
       "      <th>IsActiveMember</th>\n",
       "      <th>EstimatedSalary</th>\n",
       "      <th>Exited</th>\n",
       "      <th>EstimatedSalary_CreditScore_Ratio</th>\n",
       "      <th>CreditScore_EstimatedSalary_Ratio</th>\n",
       "      <th>...</th>\n",
       "      <th>NumOfProducts_Tenure_Ratio</th>\n",
       "      <th>Geography_Germany</th>\n",
       "      <th>Geography_Spain</th>\n",
       "      <th>Gender_Male</th>\n",
       "      <th>CreditScoreBins_Medium</th>\n",
       "      <th>CreditScoreBins_High</th>\n",
       "      <th>CreditScoreBins_Very High</th>\n",
       "      <th>NumOfProducts_2</th>\n",
       "      <th>NumOfProducts_3</th>\n",
       "      <th>NumOfProducts_4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>619</td>\n",
       "      <td>42</td>\n",
       "      <td>2</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>101348.88</td>\n",
       "      <td>1</td>\n",
       "      <td>163.730016</td>\n",
       "      <td>0.006108</td>\n",
       "      <td>...</td>\n",
       "      <td>0.500</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>608</td>\n",
       "      <td>41</td>\n",
       "      <td>1</td>\n",
       "      <td>83807.86</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>112542.58</td>\n",
       "      <td>0</td>\n",
       "      <td>185.102928</td>\n",
       "      <td>0.005402</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>502</td>\n",
       "      <td>42</td>\n",
       "      <td>8</td>\n",
       "      <td>159660.80</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>113931.57</td>\n",
       "      <td>1</td>\n",
       "      <td>226.955319</td>\n",
       "      <td>0.004406</td>\n",
       "      <td>...</td>\n",
       "      <td>0.375</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>699</td>\n",
       "      <td>39</td>\n",
       "      <td>1</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>93826.63</td>\n",
       "      <td>0</td>\n",
       "      <td>134.229800</td>\n",
       "      <td>0.007450</td>\n",
       "      <td>...</td>\n",
       "      <td>2.000</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>850</td>\n",
       "      <td>43</td>\n",
       "      <td>2</td>\n",
       "      <td>125510.82</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>79084.10</td>\n",
       "      <td>0</td>\n",
       "      <td>93.040118</td>\n",
       "      <td>0.010748</td>\n",
       "      <td>...</td>\n",
       "      <td>0.500</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   CreditScore  Age  Tenure    Balance  HasCrCard  IsActiveMember  \\\n",
       "0          619   42       2       0.00          1               1   \n",
       "1          608   41       1   83807.86          0               1   \n",
       "2          502   42       8  159660.80          1               0   \n",
       "3          699   39       1       0.00          0               0   \n",
       "4          850   43       2  125510.82          1               1   \n",
       "\n",
       "   EstimatedSalary  Exited  EstimatedSalary_CreditScore_Ratio  \\\n",
       "0        101348.88       1                         163.730016   \n",
       "1        112542.58       0                         185.102928   \n",
       "2        113931.57       1                         226.955319   \n",
       "3         93826.63       0                         134.229800   \n",
       "4         79084.10       0                          93.040118   \n",
       "\n",
       "   CreditScore_EstimatedSalary_Ratio  ...  NumOfProducts_Tenure_Ratio  \\\n",
       "0                           0.006108  ...                       0.500   \n",
       "1                           0.005402  ...                       1.000   \n",
       "2                           0.004406  ...                       0.375   \n",
       "3                           0.007450  ...                       2.000   \n",
       "4                           0.010748  ...                       0.500   \n",
       "\n",
       "   Geography_Germany  Geography_Spain  Gender_Male  CreditScoreBins_Medium  \\\n",
       "0              False            False        False                    True   \n",
       "1              False             True        False                    True   \n",
       "2              False            False        False                    True   \n",
       "3              False            False        False                   False   \n",
       "4              False             True        False                   False   \n",
       "\n",
       "   CreditScoreBins_High  CreditScoreBins_Very High  NumOfProducts_2  \\\n",
       "0                 False                      False            False   \n",
       "1                 False                      False            False   \n",
       "2                 False                      False            False   \n",
       "3                  True                      False             True   \n",
       "4                 False                       True            False   \n",
       "\n",
       "   NumOfProducts_3  NumOfProducts_4  \n",
       "0            False            False  \n",
       "1            False            False  \n",
       "2             True            False  \n",
       "3            False            False  \n",
       "4            False            False  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load data\n",
    "df = pd.read_csv('../data/01_raw/abandono_clientes.csv')\n",
    "\n",
    "# When the division is 0, resulting variable will be zero:\n",
    "# EstimatedSalary / CreditScore\n",
    "df['EstimatedSalary_CreditScore_Ratio'] = np.where(df['CreditScore'] == 0, 0, df['EstimatedSalary'] / df['CreditScore'])\n",
    "\n",
    "# CreditScore / EstimatedSalary\n",
    "df['CreditScore_EstimatedSalary_Ratio'] = np.where(df['EstimatedSalary'] == 0, 0, df['CreditScore'] / df['EstimatedSalary'])\n",
    "\n",
    "# Balance / EstimatedSalary\n",
    "df['Balance_EstimatedSalary_Ratio'] = np.where(df['EstimatedSalary'] == 0, 0, df['Balance'] / df['EstimatedSalary'])\n",
    "\n",
    "# Balance is zero\n",
    "df['Balance_IsZero'] = df['Balance'] == 0\n",
    "\n",
    "# Balance / Geography Mean Balance\n",
    "mean_balance_germany = 120000 # from EDA\n",
    "mean_balance_spain_france = 60000 \n",
    "\n",
    "df['Balance_GeographyMean_Ratio'] = df['Balance'] / np.where(df['Geography'] == 'Germany', mean_balance_germany, mean_balance_spain_france)\n",
    "\n",
    "# Tenure / Age\n",
    "df['Tenure_Age_Ratio'] = np.where(df['Age'] == 0, 0, df['Tenure'] / df['Age'])\n",
    "\n",
    "# NumOf / Age\n",
    "df['NumOfProducts_Tenure_Ratio'] = np.where(df['Tenure'] == 0, 0, df['NumOfProducts'] / df['Tenure'])\n",
    "\n",
    "\n",
    "#########\n",
    "# From the first feature engineering loop:\n",
    "#########\n",
    "\n",
    "# Drop irrelevant variables\n",
    "df = df.drop(['RowNumber', 'CustomerId', 'Surname'], axis=1)\n",
    "\n",
    "# Binning \n",
    "#-- CreditScore column\n",
    "bins = [350, 500, 650, 800, 850]  # Define the bin edges\n",
    "labels = ['Low', 'Medium', 'High', 'Very High']  # Define the bin labels\n",
    "\n",
    "df['CreditScoreBins'] = pd.cut(df['CreditScore'], bins=bins, labels=labels)\n",
    "\n",
    "# One hot encoding for categorical variables\n",
    "df = pd.get_dummies(df, columns =['Geography', 'Gender', 'CreditScoreBins', 'NumOfProducts'], drop_first = True)\n",
    "#########\n",
    "\n",
    "# Save to csv\n",
    "df.to_csv('../data/04_feature/features_second_loop.csv', index=False)\n",
    "\n",
    "df.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10000 entries, 0 to 9999\n",
      "Data columns (total 23 columns):\n",
      " #   Column                             Non-Null Count  Dtype  \n",
      "---  ------                             --------------  -----  \n",
      " 0   CreditScore                        10000 non-null  int64  \n",
      " 1   Age                                10000 non-null  int64  \n",
      " 2   Tenure                             10000 non-null  int64  \n",
      " 3   Balance                            10000 non-null  float64\n",
      " 4   HasCrCard                          10000 non-null  int64  \n",
      " 5   IsActiveMember                     10000 non-null  int64  \n",
      " 6   EstimatedSalary                    10000 non-null  float64\n",
      " 7   Exited                             10000 non-null  int64  \n",
      " 8   EstimatedSalary_CreditScore_Ratio  10000 non-null  float64\n",
      " 9   CreditScore_EstimatedSalary_Ratio  10000 non-null  float64\n",
      " 10  Balance_EstimatedSalary_Ratio      10000 non-null  float64\n",
      " 11  Balance_IsZero                     10000 non-null  bool   \n",
      " 12  Balance_GeographyMean_Ratio        10000 non-null  float64\n",
      " 13  Tenure_Age_Ratio                   10000 non-null  float64\n",
      " 14  Geography_Germany                  10000 non-null  bool   \n",
      " 15  Geography_Spain                    10000 non-null  bool   \n",
      " 16  Gender_Male                        10000 non-null  bool   \n",
      " 17  CreditScoreBins_Medium             10000 non-null  bool   \n",
      " 18  CreditScoreBins_High               10000 non-null  bool   \n",
      " 19  CreditScoreBins_Very High          10000 non-null  bool   \n",
      " 20  NumOfProducts_2                    10000 non-null  bool   \n",
      " 21  NumOfProducts_3                    10000 non-null  bool   \n",
      " 22  NumOfProducts_4                    10000 non-null  bool   \n",
      "dtypes: bool(10), float64(7), int64(6)\n",
      "memory usage: 1.1 MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<p style=\"text-align: right;\"><em>Author: Laiza Milena Scheid Parizotto</em></p>\n",
    "<p style=\"text-align: right;\"><em>Date: June 20th, 2023</em></p>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}