Task 2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Imports and loading dataset "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from sklearn.linear_model import LinearRegression\n",
    "from sklearn.tree import DecisionTreeClassifier, DecisionTreeRegressor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"ANZ synthesised transaction dataset.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modifying data to obtain salaries for each customer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "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>card_present_flag</th>\n",
       "      <th>merchant_code</th>\n",
       "      <th>balance</th>\n",
       "      <th>age</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CUS-1005756958</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4718.665385</td>\n",
       "      <td>53</td>\n",
       "      <td>970.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUS-1117979751</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>11957.202857</td>\n",
       "      <td>21</td>\n",
       "      <td>3578.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUS-1140341822</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5841.720000</td>\n",
       "      <td>28</td>\n",
       "      <td>1916.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUS-1147642491</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8813.467692</td>\n",
       "      <td>34</td>\n",
       "      <td>1711.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUS-1196156254</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>23845.717143</td>\n",
       "      <td>34</td>\n",
       "      <td>3903.73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                card_present_flag  merchant_code       balance  age   amount\n",
       "customer_id                                                                 \n",
       "CUS-1005756958                NaN            0.0   4718.665385   53   970.47\n",
       "CUS-1117979751                NaN            0.0  11957.202857   21  3578.65\n",
       "CUS-1140341822                NaN            0.0   5841.720000   28  1916.51\n",
       "CUS-1147642491                NaN            0.0   8813.467692   34  1711.39\n",
       "CUS-1196156254                NaN            0.0  23845.717143   34  3903.73"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_salaries = df[df[\"txn_description\"]==\"PAY/SALARY\"].groupby(\"customer_id\").mean()\n",
    "df_salaries.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "salaries = []\n",
    "\n",
    "for customer_id in df[\"customer_id\"]:\n",
    "    salaries.append(int(df_salaries.loc[customer_id][\"amount\"]))\n",
    "    \n",
    "df[\"annual_salary\"] = salaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>card_present_flag</th>\n",
       "      <th>merchant_code</th>\n",
       "      <th>balance</th>\n",
       "      <th>age</th>\n",
       "      <th>amount</th>\n",
       "      <th>annual_salary</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>customer_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CUS-1005756958</th>\n",
       "      <td>0.812500</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2275.852055</td>\n",
       "      <td>53</td>\n",
       "      <td>222.862603</td>\n",
       "      <td>970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUS-1117979751</th>\n",
       "      <td>0.826923</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9829.929000</td>\n",
       "      <td>21</td>\n",
       "      <td>339.843700</td>\n",
       "      <td>3578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUS-1140341822</th>\n",
       "      <td>0.815385</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5699.212250</td>\n",
       "      <td>28</td>\n",
       "      <td>212.632500</td>\n",
       "      <td>1916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUS-1147642491</th>\n",
       "      <td>0.750000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9032.841186</td>\n",
       "      <td>34</td>\n",
       "      <td>245.600169</td>\n",
       "      <td>1711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CUS-1196156254</th>\n",
       "      <td>0.785276</td>\n",
       "      <td>0.0</td>\n",
       "      <td>22272.433755</td>\n",
       "      <td>34</td>\n",
       "      <td>147.145796</td>\n",
       "      <td>3903</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                card_present_flag  merchant_code       balance  age  \\\n",
       "customer_id                                                           \n",
       "CUS-1005756958           0.812500            0.0   2275.852055   53   \n",
       "CUS-1117979751           0.826923            0.0   9829.929000   21   \n",
       "CUS-1140341822           0.815385            0.0   5699.212250   28   \n",
       "CUS-1147642491           0.750000            0.0   9032.841186   34   \n",
       "CUS-1196156254           0.785276            0.0  22272.433755   34   \n",
       "\n",
       "                    amount  annual_salary  \n",
       "customer_id                                \n",
       "CUS-1005756958  222.862603            970  \n",
       "CUS-1117979751  339.843700           3578  \n",
       "CUS-1140341822  212.632500           1916  \n",
       "CUS-1147642491  245.600169           1711  \n",
       "CUS-1196156254  147.145796           3903  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cus = df.groupby(\"customer_id\").mean()\n",
    "df_cus.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Predictive Analytics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Linear Regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "N_train = int(len(df_cus)*0.8)\n",
    "X_train = df_cus.drop(\"annual_salary\", axis=1).iloc[:N_train]\n",
    "Y_train = df_cus[\"annual_salary\"].iloc[:N_train]\n",
    "X_test = df_cus.drop(\"annual_salary\", axis=1).iloc[N_train:]\n",
    "Y_test = df_cus[\"annual_salary\"].iloc[N_train:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "linear_reg = LinearRegression()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.23295376366257825"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "linear_reg.fit(X_train, Y_train)\n",
    "linear_reg.score(X_train, Y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1993.98473311, 2867.39066481, 1944.95959591, 1806.85984885,\n",
       "       2226.35045442, 2075.34697175, 1813.02987337, 5388.67435983,\n",
       "       1902.35351608, 2191.90445145, 1713.48134178, 2854.40519949,\n",
       "       2094.77781158, 3815.34342881, 2249.92922822, 1768.80816189,\n",
       "       2095.02988288, 1515.18425875, 1782.72752537, 2481.2898546 ])"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "linear_reg.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.3169423498074737"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "linear_reg.score(X_test, Y_test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Decision Tree - Classification and Regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cat = df[[\"txn_description\", \"gender\", \"age\", \"merchant_state\", \"movement\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "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>age</th>\n",
       "      <th>txn_description_INTER BANK</th>\n",
       "      <th>txn_description_PAY/SALARY</th>\n",
       "      <th>txn_description_PAYMENT</th>\n",
       "      <th>txn_description_PHONE BANK</th>\n",
       "      <th>txn_description_POS</th>\n",
       "      <th>txn_description_SALES-POS</th>\n",
       "      <th>gender_F</th>\n",
       "      <th>gender_M</th>\n",
       "      <th>merchant_state_ACT</th>\n",
       "      <th>merchant_state_NSW</th>\n",
       "      <th>merchant_state_NT</th>\n",
       "      <th>merchant_state_QLD</th>\n",
       "      <th>merchant_state_SA</th>\n",
       "      <th>merchant_state_TAS</th>\n",
       "      <th>merchant_state_VIC</th>\n",
       "      <th>merchant_state_WA</th>\n",
       "      <th>movement_credit</th>\n",
       "      <th>movement_debit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age  txn_description_INTER BANK  txn_description_PAY/SALARY  \\\n",
       "0   26                           0                           0   \n",
       "1   26                           0                           0   \n",
       "2   38                           0                           0   \n",
       "3   40                           0                           0   \n",
       "4   26                           0                           0   \n",
       "\n",
       "   txn_description_PAYMENT  txn_description_PHONE BANK  txn_description_POS  \\\n",
       "0                        0                           0                    1   \n",
       "1                        0                           0                    0   \n",
       "2                        0                           0                    1   \n",
       "3                        0                           0                    0   \n",
       "4                        0                           0                    0   \n",
       "\n",
       "   txn_description_SALES-POS  gender_F  gender_M  merchant_state_ACT  \\\n",
       "0                          0         1         0                   0   \n",
       "1                          1         1         0                   0   \n",
       "2                          0         0         1                   0   \n",
       "3                          1         1         0                   0   \n",
       "4                          1         1         0                   0   \n",
       "\n",
       "   merchant_state_NSW  merchant_state_NT  merchant_state_QLD  \\\n",
       "0                   0                  0                   1   \n",
       "1                   1                  0                   0   \n",
       "2                   1                  0                   0   \n",
       "3                   0                  0                   1   \n",
       "4                   0                  0                   1   \n",
       "\n",
       "   merchant_state_SA  merchant_state_TAS  merchant_state_VIC  \\\n",
       "0                  0                   0                   0   \n",
       "1                  0                   0                   0   \n",
       "2                  0                   0                   0   \n",
       "3                  0                   0                   0   \n",
       "4                  0                   0                   0   \n",
       "\n",
       "   merchant_state_WA  movement_credit  movement_debit  \n",
       "0                  0                0               1  \n",
       "1                  0                0               1  \n",
       "2                  0                0               1  \n",
       "3                  0                0               1  \n",
       "4                  0                0               1  "
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.get_dummies(df_cat).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "N_train = int(len(df)*0.8)\n",
    "X_train = pd.get_dummies(df_cat).iloc[:N_train]\n",
    "Y_train = df[\"annual_salary\"].iloc[:N_train]\n",
    "X_test = pd.get_dummies(df_cat).iloc[N_train:]\n",
    "Y_test = df[\"annual_salary\"].iloc[N_train:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Classification"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "metadata": {},
   "outputs": [],
   "source": [
    "decision_tree_class = DecisionTreeClassifier()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7882499481004774"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decision_tree_class.fit(X_train, Y_train)\n",
    "decision_tree_class.score(X_train, Y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1013, 1043, 4132, ..., 4054, 1043,  996], dtype=int64)"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decision_tree_class.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.755500207555002"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decision_tree_class.score(X_test, Y_test)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Regression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "decision_tree_reg = DecisionTreeRegressor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7468978726536879"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decision_tree_reg.fit(X_train, Y_train)\n",
    "decision_tree_reg.score(X_train, Y_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([1226.42857143, 1043.        , 4132.        , ..., 3345.04761905,\n",
       "       1043.        , 1626.        ])"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decision_tree_reg.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.6730992465419754"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "decision_tree_reg.score(X_test, Y_test)"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}