prediction.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "61355aac",
   "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>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>State</th>\n",
       "      <th>Age</th>\n",
       "      <th>Important_Customer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>93</td>\n",
       "      <td>Executive Secretary</td>\n",
       "      <td>Health</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>81</td>\n",
       "      <td>Administrative Officer</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Male</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IT</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>No</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Female</td>\n",
       "      <td>56</td>\n",
       "      <td>Senior Editor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Male</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Retail</td>\n",
       "      <td>High Net Worth</td>\n",
       "      <td>Yes</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender  past_3_years_bike_related_purchases               job_title  \\\n",
       "0  Female                                   93     Executive Secretary   \n",
       "1    Male                                   81  Administrative Officer   \n",
       "2    Male                                   33                     NaN   \n",
       "3  Female                                   56           Senior Editor   \n",
       "4    Male                                   35                     NaN   \n",
       "\n",
       "  job_industry_category     wealth_segment owns_car  tenure  \\\n",
       "0                Health      Mass Customer      Yes    11.0   \n",
       "1    Financial Services      Mass Customer      Yes    16.0   \n",
       "2                    IT      Mass Customer       No     7.0   \n",
       "3                   NaN  Affluent Customer      Yes     8.0   \n",
       "4                Retail     High Net Worth      Yes    13.0   \n",
       "\n",
       "   property_valuation State             Age  Important_Customer  \n",
       "0                  10   NSW  Senior_Citizen                   1  \n",
       "1                  10   NSW     Middle_Aged                   0  \n",
       "2                   9   QLD  Senior_Citizen                   0  \n",
       "3                   4   NSW     Middle_Aged                   0  \n",
       "4                   9   VIC     Middle_Aged                   0  "
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "df=pd.read_csv('Cleaned_Data')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "dddbf8ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def past_bin(r):\n",
    "    if r<10:\n",
    "        return 0\n",
    "    elif r<20:\n",
    "        return 1\n",
    "    elif r<30:\n",
    "        return 2\n",
    "    elif r<40:\n",
    "        return 3\n",
    "    elif r<50:\n",
    "        return 4\n",
    "    elif r<60:\n",
    "        return 5\n",
    "    elif r<70:\n",
    "        return 6\n",
    "    elif r<80:\n",
    "        return 7\n",
    "    elif r<90:\n",
    "        return 8\n",
    "    else:\n",
    "        return 9\n",
    "\n",
    "df['past_3_years_purchase']=df.past_3_years_bike_related_purchases.apply(past_bin)\n",
    "\n",
    "df=df.drop('past_3_years_bike_related_purchases',1)\n",
    "\n",
    "\n",
    "def ten_bin(r):\n",
    "    if r<5:\n",
    "        return 0\n",
    "    elif r<10:\n",
    "        return 1\n",
    "    elif r<15:\n",
    "        return 2\n",
    "    else:\n",
    "        return 3\n",
    "\n",
    "df['Tenure']=df.tenure.apply(ten_bin)\n",
    "\n",
    "df=df.drop('tenure',1)\n",
    "\n",
    "\n",
    "X=df.drop('Important_Customer',1)\n",
    "y=df['Important_Customer']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "8a357988",
   "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>gender</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>State</th>\n",
       "      <th>Age</th>\n",
       "      <th>past_3_years_purchase</th>\n",
       "      <th>Tenure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>Executive Secretary</td>\n",
       "      <td>Health</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "      <td>9</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>Administrative Officer</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>10</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>8</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IT</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>No</td>\n",
       "      <td>9</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Female</td>\n",
       "      <td>Senior Editor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>4</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Retail</td>\n",
       "      <td>High Net Worth</td>\n",
       "      <td>Yes</td>\n",
       "      <td>9</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender               job_title job_industry_category     wealth_segment  \\\n",
       "0  Female     Executive Secretary                Health      Mass Customer   \n",
       "1    Male  Administrative Officer    Financial Services      Mass Customer   \n",
       "2    Male                     NaN                    IT      Mass Customer   \n",
       "3  Female           Senior Editor                   NaN  Affluent Customer   \n",
       "4    Male                     NaN                Retail     High Net Worth   \n",
       "\n",
       "  owns_car  property_valuation State             Age  past_3_years_purchase  \\\n",
       "0      Yes                  10   NSW  Senior_Citizen                      9   \n",
       "1      Yes                  10   NSW     Middle_Aged                      8   \n",
       "2       No                   9   QLD  Senior_Citizen                      3   \n",
       "3      Yes                   4   NSW     Middle_Aged                      5   \n",
       "4      Yes                   9   VIC     Middle_Aged                      3   \n",
       "\n",
       "   Tenure  \n",
       "0       2  \n",
       "1       3  \n",
       "2       1  \n",
       "3       1  \n",
       "4       2  "
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "7cc251d6",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Shakti\\anaconda3\\lib\\site-packages\\seaborn\\_decorators.py:36: FutureWarning: Pass the following variable as a keyword arg: x. From version 0.12, the only valid positional argument will be `data`, and passing other arguments without an explicit keyword will result in an error or misinterpretation.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<AxesSubplot:xlabel='State', ylabel='count'>"
      ]
     },
     "execution_count": 141,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sns.countplot(df.State,hue=df.Important_Customer)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "id": "ae5da6ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "from xverse.transformer import WOE\n",
    "clf=WOE()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "1be58c8a",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Shakti\\anaconda3\\lib\\site-packages\\pandas\\core\\arraylike.py:358: RuntimeWarning: divide by zero encountered in log\n",
      "  result = getattr(ufunc, method)(*inputs, **kwargs)\n"
     ]
    }
   ],
   "source": [
    "clf.fit(X,y)\n",
    "X_woe=clf.transform(X)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "63752a2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.neighbors import KNeighborsClassifier\n",
    "from xgboost import XGBClassifier\n",
    "from sklearn.ensemble import StackingClassifier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "23134892",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[12:12:47] WARNING: C:/Users/Administrator/workspace/xgboost-win64_release_1.5.1/src/learner.cc:1115: Starting in XGBoost 1.3.0, the default evaluation metric used with the objective 'binary:logistic' was changed from 'error' to 'logloss'. Explicitly set eval_metric if you'd like to restore the old behavior.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Shakti\\anaconda3\\lib\\site-packages\\xgboost\\sklearn.py:1224: UserWarning: The use of label encoder in XGBClassifier is deprecated and will be removed in a future release. To remove this warning, do the following: 1) Pass option use_label_encoder=False when constructing XGBClassifier object; and 2) Encode your labels (y) as integers starting with 0, i.e. 0, 1, 2, ..., [num_class - 1].\n",
      "  warnings.warn(label_encoder_deprecation_msg, UserWarning)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "StackingClassifier(estimators=[('KNN_model_1',\n",
       "                                KNeighborsClassifier(algorithm='ball_tree',\n",
       "                                                     n_neighbors=18, p=1.3)),\n",
       "                               ('KNN_model_2',\n",
       "                                KNeighborsClassifier(n_neighbors=18))],\n",
       "                   final_estimator=XGBClassifier(base_score=None, booster=None,\n",
       "                                                 colsample_bylevel=None,\n",
       "                                                 colsample_bynode=None,\n",
       "                                                 colsample_bytree=None,\n",
       "                                                 enable_categorical=False,\n",
       "                                                 gamma=None, gpu_id=None,\n",
       "                                                 importance...\n",
       "                                                 interaction_constraints=None,\n",
       "                                                 learning_rate=None,\n",
       "                                                 max_delta_step=None,\n",
       "                                                 max_depth=None,\n",
       "                                                 min_child_weight=None,\n",
       "                                                 missing=nan,\n",
       "                                                 monotone_constraints=None,\n",
       "                                                 n_estimators=100, n_jobs=None,\n",
       "                                                 num_parallel_tree=None,\n",
       "                                                 predictor=None,\n",
       "                                                 random_state=None,\n",
       "                                                 reg_alpha=None,\n",
       "                                                 reg_lambda=None,\n",
       "                                                 scale_pos_weight=None,\n",
       "                                                 subsample=None,\n",
       "                                                 tree_method=None,\n",
       "                                                 validate_parameters=None,\n",
       "                                                 verbosity=None))"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_learners = [\n",
    "                 ('KNN_model_1', KNeighborsClassifier(n_neighbors=18,algorithm='ball_tree',p=1.3)),\n",
    "                 ('KNN_model_2', KNeighborsClassifier(n_neighbors=18))]\n",
    "\n",
    "\n",
    "stack_model = StackingClassifier(estimators = base_learners, final_estimator = XGBClassifier())\n",
    "stack_model.fit(X_woe,y)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "d15e8aff",
   "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>DOB</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>deceased_indicator</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>property_valuation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chickie</td>\n",
       "      <td>Brister</td>\n",
       "      <td>Male</td>\n",
       "      <td>86</td>\n",
       "      <td>1957-07-12</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>45 Shopko Center</td>\n",
       "      <td>4500</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Morly</td>\n",
       "      <td>Genery</td>\n",
       "      <td>Male</td>\n",
       "      <td>69</td>\n",
       "      <td>1970-03-22</td>\n",
       "      <td>Structural Engineer</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>16</td>\n",
       "      <td>14 Mccormick Park</td>\n",
       "      <td>2113</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ardelis</td>\n",
       "      <td>Forrester</td>\n",
       "      <td>Female</td>\n",
       "      <td>10</td>\n",
       "      <td>1974-08-28 00:00:00</td>\n",
       "      <td>Senior Cost Accountant</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>10</td>\n",
       "      <td>5 Colorado Crossing</td>\n",
       "      <td>3505</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lucine</td>\n",
       "      <td>Stutt</td>\n",
       "      <td>Female</td>\n",
       "      <td>64</td>\n",
       "      <td>1979-01-28</td>\n",
       "      <td>Account Representative III</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>5</td>\n",
       "      <td>207 Annamark Plaza</td>\n",
       "      <td>4814</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Melinda</td>\n",
       "      <td>Hadlee</td>\n",
       "      <td>Female</td>\n",
       "      <td>34</td>\n",
       "      <td>1965-09-21</td>\n",
       "      <td>Financial Analyst</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>19</td>\n",
       "      <td>115 Montana Place</td>\n",
       "      <td>2093</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0 first_name  last_name  gender past_3_years_bike_related_purchases  \\\n",
       "0    Chickie    Brister    Male                                  86   \n",
       "1      Morly     Genery    Male                                  69   \n",
       "2    Ardelis  Forrester  Female                                  10   \n",
       "3     Lucine      Stutt  Female                                  64   \n",
       "4    Melinda     Hadlee  Female                                  34   \n",
       "\n",
       "0                  DOB                   job_title job_industry_category  \\\n",
       "0           1957-07-12             General Manager         Manufacturing   \n",
       "1           1970-03-22         Structural Engineer              Property   \n",
       "2  1974-08-28 00:00:00      Senior Cost Accountant    Financial Services   \n",
       "3           1979-01-28  Account Representative III         Manufacturing   \n",
       "4           1965-09-21           Financial Analyst    Financial Services   \n",
       "\n",
       "0     wealth_segment deceased_indicator owns_car tenure              address  \\\n",
       "0      Mass Customer                  N      Yes     14     45 Shopko Center   \n",
       "1      Mass Customer                  N       No     16    14 Mccormick Park   \n",
       "2  Affluent Customer                  N       No     10  5 Colorado Crossing   \n",
       "3  Affluent Customer                  N      Yes      5   207 Annamark Plaza   \n",
       "4  Affluent Customer                  N       No     19    115 Montana Place   \n",
       "\n",
       "0 postcode state    country property_valuation  \n",
       "0     4500   QLD  Australia                  6  \n",
       "1     2113   NSW  Australia                 11  \n",
       "2     3505   VIC  Australia                  5  \n",
       "3     4814   QLD  Australia                  1  \n",
       "4     2093   NSW  Australia                  9  "
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df=pd.read_excel('KPMG_VI_New_raw_data_update_final.xlsx',sheet_name='NewCustomerList')\n",
    "new_df.head()\n",
    "\n",
    "new_df.columns=new_df.iloc[0,:]  #First row is made as dataframe columns\n",
    "new_df.drop(0,axis=0,inplace=True)   #First row is dropped from the axis\n",
    "new_df.reset_index(drop=True,inplace=True)   #Now indices are reset original value.\n",
    "                            \n",
    "\n",
    "new_df=new_df.iloc[:,:16]\n",
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "aba28140",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=new_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "ae63a9d5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "N    1000\n",
       "Name: deceased_indicator, dtype: int64"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.deceased_indicator.value_counts() #only one 'Y' value lets drop it\n",
    "\n",
    "#df=df[df.deceased_indicator=='N']\n",
    "#df.shape\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "303a5a18",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 12 columns):\n",
      " #   Column                               Non-Null Count  Dtype \n",
      "---  ------                               --------------  ----- \n",
      " 0   gender                               1000 non-null   object\n",
      " 1   past_3_years_bike_related_purchases  1000 non-null   object\n",
      " 2   DOB                                  983 non-null    object\n",
      " 3   job_title                            894 non-null    object\n",
      " 4   job_industry_category                835 non-null    object\n",
      " 5   wealth_segment                       1000 non-null   object\n",
      " 6   deceased_indicator                   1000 non-null   object\n",
      " 7   owns_car                             1000 non-null   object\n",
      " 8   tenure                               1000 non-null   object\n",
      " 9   postcode                             1000 non-null   object\n",
      " 10  state                                1000 non-null   object\n",
      " 11  property_valuation                   1000 non-null   object\n",
      "dtypes: object(12)\n",
      "memory usage: 93.9+ KB\n"
     ]
    }
   ],
   "source": [
    "df=df.drop(['first_name','last_name','address','country'],1)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "f1c82049",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop('deceased_indicator',1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "68922554",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0\n",
       "gender                                  0.0\n",
       "past_3_years_bike_related_purchases     0.0\n",
       "DOB                                     1.7\n",
       "job_title                              10.6\n",
       "job_industry_category                  16.5\n",
       "wealth_segment                          0.0\n",
       "owns_car                                0.0\n",
       "tenure                                  0.0\n",
       "postcode                                0.0\n",
       "state                                   0.0\n",
       "property_valuation                      0.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(df.isnull().sum()/len(df))*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "7fe98030",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2017-12-30 00:00:00\n"
     ]
    }
   ],
   "source": [
    "import datetime\n",
    "now=datetime.date(2017,12,30)\n",
    "now=pd.to_datetime(now)\n",
    "print(now)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "87d61931",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.DOB.replace(to_replace=np.nan,value=now,inplace=True)\n",
    "df.DOB.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "05ac6bd3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.DOB=pd.to_datetime(df.DOB)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "6815de47",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    60\n",
       "1    47\n",
       "2    43\n",
       "3    38\n",
       "4    52\n",
       "Name: Age, dtype: int64"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Age']=df.DOB.apply(lambda r: ((now-r)/365.25).days)\n",
    "df.Age.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "bb0d417b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Age.replace({0:np.nan},inplace=True)\n",
    "df.Age[df.Age==0]\n",
    "\n",
    "df.Age.isnull().sum()  #Same as DOB column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "fd19a73f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop('DOB',1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "d2a186f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Female    513\n",
       "Male      470\n",
       "Name: gender, dtype: int64"
      ]
     },
     "execution_count": 158,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gen_d={'M':\"Male\",\"F\":'Female',\"Femal\":'Female','U':np.nan}\n",
    "\n",
    "df.gender.replace(gen_d,inplace=True)\n",
    "\n",
    "df.gender.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "523bcc88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NSW    506\n",
       "VIC    266\n",
       "QLD    228\n",
       "Name: state, dtype: int64"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.state.replace({'Victoria':'VIC','New South Wales':'NSW'},inplace=True)  #Repeatation of state names.\n",
    "df.state.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "4c2c33f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.reset_index(drop=True,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "0fc1c165",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[2600,\n",
       " 2601,\n",
       " 2602,\n",
       " 2603,\n",
       " 2604,\n",
       " 2605,\n",
       " 2606,\n",
       " 2607,\n",
       " 2608,\n",
       " 2609,\n",
       " 2610,\n",
       " 2611,\n",
       " 2612,\n",
       " 2613,\n",
       " 2614,\n",
       " 2615,\n",
       " 2616,\n",
       " 2617,\n",
       " 2618,\n",
       " 2619,\n",
       " 2540]"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "act=list(range(2600,2620))  # This the only range for ACT and other are as stated by state of the customer.\n",
    "act.append(2540)\n",
    "act                                                        #This helps in filtering out australian capital territory from NSW"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "1e36f4ae",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'VIC',\n",
       " 'QLD',\n",
       " 'NSW',\n",
       " 'VIC',\n",
       " 'NSW',\n",
       " 'NSW',\n",
       " 'QLD',\n",
       " 'QLD',\n",
       " 'NSW']"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "el=list()\n",
    "for i in range(df.shape[0]):\n",
    "    if df.state[i]=='NSW':\n",
    "        if df.postcode[i] in act:\n",
    "            el.append('ACT')\n",
    "        else:\n",
    "            el.append('NSW')\n",
    "    else:\n",
    "        el.append(df.state[i])\n",
    "            \n",
    "el   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "92796547",
   "metadata": {},
   "outputs": [],
   "source": [
    "el=np.array(el)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6e6379d4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "f857496b",
   "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>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>Age</th>\n",
       "      <th>State_New</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>86</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>4500</td>\n",
       "      <td>QLD</td>\n",
       "      <td>6</td>\n",
       "      <td>60.0</td>\n",
       "      <td>QLD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>69</td>\n",
       "      <td>Structural Engineer</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>No</td>\n",
       "      <td>16</td>\n",
       "      <td>2113</td>\n",
       "      <td>NSW</td>\n",
       "      <td>11</td>\n",
       "      <td>47.0</td>\n",
       "      <td>NSW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Female</td>\n",
       "      <td>10</td>\n",
       "      <td>Senior Cost Accountant</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>No</td>\n",
       "      <td>10</td>\n",
       "      <td>3505</td>\n",
       "      <td>VIC</td>\n",
       "      <td>5</td>\n",
       "      <td>43.0</td>\n",
       "      <td>VIC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Female</td>\n",
       "      <td>64</td>\n",
       "      <td>Account Representative III</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>5</td>\n",
       "      <td>4814</td>\n",
       "      <td>QLD</td>\n",
       "      <td>1</td>\n",
       "      <td>38.0</td>\n",
       "      <td>QLD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Female</td>\n",
       "      <td>34</td>\n",
       "      <td>Financial Analyst</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>No</td>\n",
       "      <td>19</td>\n",
       "      <td>2093</td>\n",
       "      <td>NSW</td>\n",
       "      <td>9</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NSW</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0  gender past_3_years_bike_related_purchases                   job_title  \\\n",
       "0    Male                                  86             General Manager   \n",
       "1    Male                                  69         Structural Engineer   \n",
       "2  Female                                  10      Senior Cost Accountant   \n",
       "3  Female                                  64  Account Representative III   \n",
       "4  Female                                  34           Financial Analyst   \n",
       "\n",
       "0 job_industry_category     wealth_segment owns_car tenure postcode state  \\\n",
       "0         Manufacturing      Mass Customer      Yes     14     4500   QLD   \n",
       "1              Property      Mass Customer       No     16     2113   NSW   \n",
       "2    Financial Services  Affluent Customer       No     10     3505   VIC   \n",
       "3         Manufacturing  Affluent Customer      Yes      5     4814   QLD   \n",
       "4    Financial Services  Affluent Customer       No     19     2093   NSW   \n",
       "\n",
       "0 property_valuation   Age State_New  \n",
       "0                  6  60.0       QLD  \n",
       "1                 11  47.0       NSW  \n",
       "2                  5  43.0       VIC  \n",
       "3                  1  38.0       QLD  \n",
       "4                  9  52.0       NSW  "
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['State_New']=el\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "1dd87ba7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop('state',1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "fe13c64f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.drop('postcode',1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "65b1b9b8",
   "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>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>State_New</th>\n",
       "      <th>Age_N</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>86</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>69</td>\n",
       "      <td>Structural Engineer</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>No</td>\n",
       "      <td>16</td>\n",
       "      <td>11</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Middle_Aged</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0 gender past_3_years_bike_related_purchases            job_title  \\\n",
       "0   Male                                  86      General Manager   \n",
       "1   Male                                  69  Structural Engineer   \n",
       "\n",
       "0 job_industry_category wealth_segment owns_car tenure property_valuation  \\\n",
       "0         Manufacturing  Mass Customer      Yes     14                  6   \n",
       "1              Property  Mass Customer       No     16                 11   \n",
       "\n",
       "0 State_New           Age_N  \n",
       "0       QLD  Senior_Citizen  \n",
       "1       NSW     Middle_Aged  "
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def age_br(r):\n",
    "    if r<30:\n",
    "        return 'Young'\n",
    "    elif r <55:\n",
    "        return 'Middle_Aged'\n",
    "    else:\n",
    "        return 'Senior_Citizen'\n",
    "\n",
    "df['Age_N']=df.Age.apply(age_br)\n",
    "df.Age_N.head()\n",
    "\n",
    "df=df.drop('Age',1)\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "e81ae4ba",
   "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>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>State</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>86</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>6</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0 gender past_3_years_bike_related_purchases        job_title  \\\n",
       "0   Male                                  86  General Manager   \n",
       "\n",
       "0 job_industry_category wealth_segment owns_car tenure property_valuation  \\\n",
       "0         Manufacturing  Mass Customer      Yes     14                  6   \n",
       "\n",
       "0 State             Age  \n",
       "0   QLD  Senior_Citizen  "
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "coln={'Age_N':'Age','State_New':'State'}\n",
    "df.rename(columns=coln,inplace=True)\n",
    "df.head(1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1631d2b6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "4650e1e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.iloc[:,1]=pd.to_numeric(df.iloc[:,1])\n",
    "df.iloc[:,6]=pd.to_numeric(df.iloc[:,6])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e297c1ca",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "d0c05f54",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "def past_bin(r):\n",
    "    if r<10:\n",
    "        return 0\n",
    "    elif r<20:\n",
    "        return 1\n",
    "    elif r<30:\n",
    "        return 2\n",
    "    elif r<40:\n",
    "        return 3\n",
    "    elif r<50:\n",
    "        return 4\n",
    "    elif r<60:\n",
    "        return 5\n",
    "    elif r<70:\n",
    "        return 6\n",
    "    elif r<80:\n",
    "        return 7\n",
    "    elif r<90:\n",
    "        return 8\n",
    "    else:\n",
    "        return 9\n",
    "\n",
    "df['past_3_years_purchase']=df.past_3_years_bike_related_purchases.apply(past_bin)\n",
    "\n",
    "df=df.drop('past_3_years_bike_related_purchases',1)\n",
    "\n",
    "\n",
    "def ten_bin(r):\n",
    "    if r<5:\n",
    "        return 0\n",
    "    elif r<10:\n",
    "        return 1\n",
    "    elif r<15:\n",
    "        return 2\n",
    "    else:\n",
    "        return 3\n",
    "\n",
    "df['Tenure']=df.tenure.apply(ten_bin)\n",
    "\n",
    "df=df.drop('tenure',1)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "5704004c",
   "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>gender</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>State</th>\n",
       "      <th>Age</th>\n",
       "      <th>past_3_years_purchase</th>\n",
       "      <th>Tenure</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>6</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0 gender        job_title job_industry_category wealth_segment owns_car  \\\n",
       "0   Male  General Manager         Manufacturing  Mass Customer      Yes   \n",
       "\n",
       "0 property_valuation State             Age  past_3_years_purchase  Tenure  \n",
       "0                  6   QLD  Senior_Citizen                      8       2  "
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "fbfdf6c4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['gender', 'job_title', 'job_industry_category', 'wealth_segment',\n",
       "       'owns_car', 'property_valuation', 'State', 'Age',\n",
       "       'past_3_years_purchase', 'Tenure'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "633f9ff4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns=X.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "0054a7eb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 3487 entries, 0 to 3486\n",
      "Data columns (total 10 columns):\n",
      " #   Column                 Non-Null Count  Dtype \n",
      "---  ------                 --------------  ----- \n",
      " 0   gender                 3410 non-null   object\n",
      " 1   job_title              3066 non-null   object\n",
      " 2   job_industry_category  2927 non-null   object\n",
      " 3   wealth_segment         3487 non-null   object\n",
      " 4   owns_car               3487 non-null   object\n",
      " 5   property_valuation     3487 non-null   int64 \n",
      " 6   State                  3487 non-null   object\n",
      " 7   Age                    3487 non-null   object\n",
      " 8   past_3_years_purchase  3487 non-null   int64 \n",
      " 9   Tenure                 3487 non-null   int64 \n",
      "dtypes: int64(3), object(7)\n",
      "memory usage: 272.5+ KB\n"
     ]
    }
   ],
   "source": [
    "X.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "ef530303",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 10 columns):\n",
      " #   Column                 Non-Null Count  Dtype \n",
      "---  ------                 --------------  ----- \n",
      " 0   gender                 983 non-null    object\n",
      " 1   job_title              894 non-null    object\n",
      " 2   job_industry_category  835 non-null    object\n",
      " 3   wealth_segment         1000 non-null   object\n",
      " 4   owns_car               1000 non-null   object\n",
      " 5   property_valuation     1000 non-null   int64 \n",
      " 6   State                  1000 non-null   object\n",
      " 7   Age                    1000 non-null   object\n",
      " 8   past_3_years_purchase  1000 non-null   int64 \n",
      " 9   Tenure                 1000 non-null   int64 \n",
      "dtypes: int64(3), object(7)\n",
      "memory usage: 78.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df.property_valuation=df.property_valuation.astype('int64')\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "id": "79ace9b9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Shakti\\anaconda3\\lib\\site-packages\\pandas\\core\\arraylike.py:358: RuntimeWarning: divide by zero encountered in log\n",
      "  result = getattr(ufunc, method)(*inputs, **kwargs)\n"
     ]
    }
   ],
   "source": [
    "clf=WOE()\n",
    "clf.fit(X,y)\n",
    "df_woe=clf.transform(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "9908f06e",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred=stack_model.predict(df_woe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "346ace52",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred_prob=stack_model.predict_proba(df_woe)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "50dac859",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "6be0360f",
   "metadata": {},
   "outputs": [],
   "source": [
    "y_pred_prob0=y_pred_prob[0]\n",
    "y_pred_prob1=y_pred_prob[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "fe4db82a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.4153819, 0.5846181)"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y_pred_prob0.min(),y_pred_prob0.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "8a09ffee",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0.4496992, 0.55030084)"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y_pred_prob1.min(),y_pred_prob1.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "ce022e1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Important_Customer']=y_pred"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "3b277fc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df.to_csv('Model_File_for_Prediction.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "ed312e9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NSW    506\n",
       "VIC    266\n",
       "QLD    228\n",
       "Name: State, dtype: int64"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.State.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "1cb4d9e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1000, 16), (1000, 10))"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.shape,df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a40eca6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "d6916f1a",
   "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>DOB</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>deceased_indicator</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>Important_Customer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chickie</td>\n",
       "      <td>Brister</td>\n",
       "      <td>Male</td>\n",
       "      <td>86</td>\n",
       "      <td>1957-07-12</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>45 Shopko Center</td>\n",
       "      <td>4500</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Morly</td>\n",
       "      <td>Genery</td>\n",
       "      <td>Male</td>\n",
       "      <td>69</td>\n",
       "      <td>1970-03-22</td>\n",
       "      <td>Structural Engineer</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>16</td>\n",
       "      <td>14 Mccormick Park</td>\n",
       "      <td>2113</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ardelis</td>\n",
       "      <td>Forrester</td>\n",
       "      <td>Female</td>\n",
       "      <td>10</td>\n",
       "      <td>1974-08-28 00:00:00</td>\n",
       "      <td>Senior Cost Accountant</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>10</td>\n",
       "      <td>5 Colorado Crossing</td>\n",
       "      <td>3505</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lucine</td>\n",
       "      <td>Stutt</td>\n",
       "      <td>Female</td>\n",
       "      <td>64</td>\n",
       "      <td>1979-01-28</td>\n",
       "      <td>Account Representative III</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>5</td>\n",
       "      <td>207 Annamark Plaza</td>\n",
       "      <td>4814</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Melinda</td>\n",
       "      <td>Hadlee</td>\n",
       "      <td>Female</td>\n",
       "      <td>34</td>\n",
       "      <td>1965-09-21</td>\n",
       "      <td>Financial Analyst</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>19</td>\n",
       "      <td>115 Montana Place</td>\n",
       "      <td>2093</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "0 first_name  last_name  gender past_3_years_bike_related_purchases  \\\n",
       "0    Chickie    Brister    Male                                  86   \n",
       "1      Morly     Genery    Male                                  69   \n",
       "2    Ardelis  Forrester  Female                                  10   \n",
       "3     Lucine      Stutt  Female                                  64   \n",
       "4    Melinda     Hadlee  Female                                  34   \n",
       "\n",
       "0                  DOB                   job_title job_industry_category  \\\n",
       "0           1957-07-12             General Manager         Manufacturing   \n",
       "1           1970-03-22         Structural Engineer              Property   \n",
       "2  1974-08-28 00:00:00      Senior Cost Accountant    Financial Services   \n",
       "3           1979-01-28  Account Representative III         Manufacturing   \n",
       "4           1965-09-21           Financial Analyst    Financial Services   \n",
       "\n",
       "0     wealth_segment deceased_indicator owns_car tenure              address  \\\n",
       "0      Mass Customer                  N      Yes     14     45 Shopko Center   \n",
       "1      Mass Customer                  N       No     16    14 Mccormick Park   \n",
       "2  Affluent Customer                  N       No     10  5 Colorado Crossing   \n",
       "3  Affluent Customer                  N      Yes      5   207 Annamark Plaza   \n",
       "4  Affluent Customer                  N       No     19    115 Montana Place   \n",
       "\n",
       "0 postcode state    country property_valuation  Important_Customer  \n",
       "0     4500   QLD  Australia                  6                   1  \n",
       "1     2113   NSW  Australia                 11                   0  \n",
       "2     3505   VIC  Australia                  5                   0  \n",
       "3     4814   QLD  Australia                  1                   0  \n",
       "4     2093   NSW  Australia                  9                   0  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df['Important_Customer']=y_pred\n",
    "\n",
    "new_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "480631c0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    512\n",
       "1    488\n",
       "Name: Important_Customer, dtype: int64"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_df.Important_Customer.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "320a0d2d",
   "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>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>State</th>\n",
       "      <th>Age</th>\n",
       "      <th>Important_Customer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>93</td>\n",
       "      <td>Executive Secretary</td>\n",
       "      <td>Health</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>11.0</td>\n",
       "      <td>10</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>81</td>\n",
       "      <td>Administrative Officer</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>16.0</td>\n",
       "      <td>10</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Male</td>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>IT</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>No</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Senior_Citizen</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Female</td>\n",
       "      <td>56</td>\n",
       "      <td>Senior Editor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>Yes</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Male</td>\n",
       "      <td>35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Retail</td>\n",
       "      <td>High Net Worth</td>\n",
       "      <td>Yes</td>\n",
       "      <td>13.0</td>\n",
       "      <td>9</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Middle_Aged</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender  past_3_years_bike_related_purchases               job_title  \\\n",
       "0  Female                                   93     Executive Secretary   \n",
       "1    Male                                   81  Administrative Officer   \n",
       "2    Male                                   33                     NaN   \n",
       "3  Female                                   56           Senior Editor   \n",
       "4    Male                                   35                     NaN   \n",
       "\n",
       "  job_industry_category     wealth_segment owns_car  tenure  \\\n",
       "0                Health      Mass Customer      Yes    11.0   \n",
       "1    Financial Services      Mass Customer      Yes    16.0   \n",
       "2                    IT      Mass Customer       No     7.0   \n",
       "3                   NaN  Affluent Customer      Yes     8.0   \n",
       "4                Retail     High Net Worth      Yes    13.0   \n",
       "\n",
       "   property_valuation State             Age  Important_Customer  \n",
       "0                  10   NSW  Senior_Citizen                   1  \n",
       "1                  10   NSW     Middle_Aged                   0  \n",
       "2                   9   QLD  Senior_Citizen                   0  \n",
       "3                   4   NSW     Middle_Aged                   0  \n",
       "4                   9   VIC     Middle_Aged                   0  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train=pd.read_csv('Cleaned_Data')\n",
    "df_train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "505fc9ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_test=new_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "f45423d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1746\n",
       "1    1741\n",
       "Name: Important_Customer, dtype: int64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_train.Important_Customer.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79498e93",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "d120586d",
   "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>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>gender</th>\n",
       "      <th>past_3_years_bike_related_purchases</th>\n",
       "      <th>DOB</th>\n",
       "      <th>job_title</th>\n",
       "      <th>job_industry_category</th>\n",
       "      <th>wealth_segment</th>\n",
       "      <th>deceased_indicator</th>\n",
       "      <th>owns_car</th>\n",
       "      <th>tenure</th>\n",
       "      <th>address</th>\n",
       "      <th>postcode</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>property_valuation</th>\n",
       "      <th>Important_Customer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Chickie</td>\n",
       "      <td>Brister</td>\n",
       "      <td>Male</td>\n",
       "      <td>86</td>\n",
       "      <td>1957-07-12</td>\n",
       "      <td>General Manager</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>45 Shopko Center</td>\n",
       "      <td>4500</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Morly</td>\n",
       "      <td>Genery</td>\n",
       "      <td>Male</td>\n",
       "      <td>69</td>\n",
       "      <td>1970-03-22</td>\n",
       "      <td>Structural Engineer</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>16</td>\n",
       "      <td>14 Mccormick Park</td>\n",
       "      <td>2113</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ardelis</td>\n",
       "      <td>Forrester</td>\n",
       "      <td>Female</td>\n",
       "      <td>10</td>\n",
       "      <td>1974-08-28 00:00:00</td>\n",
       "      <td>Senior Cost Accountant</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>10</td>\n",
       "      <td>5 Colorado Crossing</td>\n",
       "      <td>3505</td>\n",
       "      <td>VIC</td>\n",
       "      <td>Australia</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lucine</td>\n",
       "      <td>Stutt</td>\n",
       "      <td>Female</td>\n",
       "      <td>64</td>\n",
       "      <td>1979-01-28</td>\n",
       "      <td>Account Representative III</td>\n",
       "      <td>Manufacturing</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>5</td>\n",
       "      <td>207 Annamark Plaza</td>\n",
       "      <td>4814</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Melinda</td>\n",
       "      <td>Hadlee</td>\n",
       "      <td>Female</td>\n",
       "      <td>34</td>\n",
       "      <td>1965-09-21</td>\n",
       "      <td>Financial Analyst</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>19</td>\n",
       "      <td>115 Montana Place</td>\n",
       "      <td>2093</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>Ferdinand</td>\n",
       "      <td>Romanetti</td>\n",
       "      <td>Male</td>\n",
       "      <td>60</td>\n",
       "      <td>1959-10-07</td>\n",
       "      <td>Paralegal</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>9</td>\n",
       "      <td>2 Sloan Way</td>\n",
       "      <td>2200</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>Burk</td>\n",
       "      <td>Wortley</td>\n",
       "      <td>Male</td>\n",
       "      <td>22</td>\n",
       "      <td>2001-10-17</td>\n",
       "      <td>Senior Sales Associate</td>\n",
       "      <td>Health</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>No</td>\n",
       "      <td>6</td>\n",
       "      <td>04 Union Crossing</td>\n",
       "      <td>2196</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>Melloney</td>\n",
       "      <td>Temby</td>\n",
       "      <td>Female</td>\n",
       "      <td>17</td>\n",
       "      <td>1954-10-05</td>\n",
       "      <td>Budget/Accounting Analyst IV</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Affluent Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>15</td>\n",
       "      <td>33475 Fair Oaks Junction</td>\n",
       "      <td>4702</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>Dickie</td>\n",
       "      <td>Cubbini</td>\n",
       "      <td>Male</td>\n",
       "      <td>30</td>\n",
       "      <td>1952-12-17</td>\n",
       "      <td>Financial Advisor</td>\n",
       "      <td>Financial Services</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>19</td>\n",
       "      <td>57666 Victoria Way</td>\n",
       "      <td>4215</td>\n",
       "      <td>QLD</td>\n",
       "      <td>Australia</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>Sylas</td>\n",
       "      <td>Duffill</td>\n",
       "      <td>Male</td>\n",
       "      <td>56</td>\n",
       "      <td>1955-10-02</td>\n",
       "      <td>Staff Accountant IV</td>\n",
       "      <td>Property</td>\n",
       "      <td>Mass Customer</td>\n",
       "      <td>N</td>\n",
       "      <td>Yes</td>\n",
       "      <td>14</td>\n",
       "      <td>21875 Grover Drive</td>\n",
       "      <td>2010</td>\n",
       "      <td>NSW</td>\n",
       "      <td>Australia</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "0   first_name  last_name  gender past_3_years_bike_related_purchases  \\\n",
       "0      Chickie    Brister    Male                                  86   \n",
       "1        Morly     Genery    Male                                  69   \n",
       "2      Ardelis  Forrester  Female                                  10   \n",
       "3       Lucine      Stutt  Female                                  64   \n",
       "4      Melinda     Hadlee  Female                                  34   \n",
       "..         ...        ...     ...                                 ...   \n",
       "995  Ferdinand  Romanetti    Male                                  60   \n",
       "996       Burk    Wortley    Male                                  22   \n",
       "997   Melloney      Temby  Female                                  17   \n",
       "998     Dickie    Cubbini    Male                                  30   \n",
       "999      Sylas    Duffill    Male                                  56   \n",
       "\n",
       "0                    DOB                     job_title job_industry_category  \\\n",
       "0             1957-07-12               General Manager         Manufacturing   \n",
       "1             1970-03-22           Structural Engineer              Property   \n",
       "2    1974-08-28 00:00:00        Senior Cost Accountant    Financial Services   \n",
       "3             1979-01-28    Account Representative III         Manufacturing   \n",
       "4             1965-09-21             Financial Analyst    Financial Services   \n",
       "..                   ...                           ...                   ...   \n",
       "995           1959-10-07                     Paralegal    Financial Services   \n",
       "996           2001-10-17        Senior Sales Associate                Health   \n",
       "997           1954-10-05  Budget/Accounting Analyst IV    Financial Services   \n",
       "998           1952-12-17             Financial Advisor    Financial Services   \n",
       "999           1955-10-02           Staff Accountant IV              Property   \n",
       "\n",
       "0       wealth_segment deceased_indicator owns_car tenure  \\\n",
       "0        Mass Customer                  N      Yes     14   \n",
       "1        Mass Customer                  N       No     16   \n",
       "2    Affluent Customer                  N       No     10   \n",
       "3    Affluent Customer                  N      Yes      5   \n",
       "4    Affluent Customer                  N       No     19   \n",
       "..                 ...                ...      ...    ...   \n",
       "995  Affluent Customer                  N       No      9   \n",
       "996      Mass Customer                  N       No      6   \n",
       "997  Affluent Customer                  N      Yes     15   \n",
       "998      Mass Customer                  N      Yes     19   \n",
       "999      Mass Customer                  N      Yes     14   \n",
       "\n",
       "0                     address postcode state    country property_valuation  \\\n",
       "0            45 Shopko Center     4500   QLD  Australia                  6   \n",
       "1           14 Mccormick Park     2113   NSW  Australia                 11   \n",
       "2         5 Colorado Crossing     3505   VIC  Australia                  5   \n",
       "3          207 Annamark Plaza     4814   QLD  Australia                  1   \n",
       "4           115 Montana Place     2093   NSW  Australia                  9   \n",
       "..                        ...      ...   ...        ...                ...   \n",
       "995               2 Sloan Way     2200   NSW  Australia                  7   \n",
       "996         04 Union Crossing     2196   NSW  Australia                 10   \n",
       "997  33475 Fair Oaks Junction     4702   QLD  Australia                  2   \n",
       "998        57666 Victoria Way     4215   QLD  Australia                  2   \n",
       "999        21875 Grover Drive     2010   NSW  Australia                  9   \n",
       "\n",
       "0    Important_Customer  \n",
       "0                     1  \n",
       "1                     0  \n",
       "2                     0  \n",
       "3                     0  \n",
       "4                     0  \n",
       "..                  ...  \n",
       "995                   1  \n",
       "996                   0  \n",
       "997                   0  \n",
       "998                   1  \n",
       "999                   0  \n",
       "\n",
       "[1000 rows x 17 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "ce51f0e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "jit=df_test.groupby(['job_industry_category'])['Important_Customer'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "971dad54",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_test.to_csv('Predicted_File.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea1812b9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Its a rough file. For any concern contact us."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}