challenge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Challenge\n",
    "\n",
    "Another approach to identifying fraudulent transactions is to look for outliers in the data. Standard deviation or quartiles are often used to detect outliers. Using this starter notebook, code two Python functions:\n",
    "\n",
    "* One that uses standard deviation to identify anomalies for any cardholder.\n",
    "\n",
    "* Another that uses interquartile range to identify anomalies for any cardholder.\n",
    "\n",
    "## Identifying Outliers using Standard Deviation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Initial imports\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import random\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a connection to the database\n",
    "engine = create_engine(\"postgresql://postgres:panda@localhost:5432/fraud_detection\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the query\n",
    "query = \"\"\"\n",
    "SELECT *\n",
    "FROM \tv_data_joined\n",
    "        \"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>txn_id</th>\n",
       "      <th>txn_day</th>\n",
       "      <th>txn_month</th>\n",
       "      <th>txn_hour</th>\n",
       "      <th>txn_min</th>\n",
       "      <th>cardholder_id</th>\n",
       "      <th>amount</th>\n",
       "      <th>merchant_name</th>\n",
       "      <th>merchant_category</th>\n",
       "      <th>twoless_ind</th>\n",
       "      <th>large_ind</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></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>2018-01-01 21:35:10</th>\n",
       "      <td>222</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>13</td>\n",
       "      <td>6.22</td>\n",
       "      <td>Dominguez PLC</td>\n",
       "      <td>food truck</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 21:43:12</th>\n",
       "      <td>2045</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>13</td>\n",
       "      <td>3.83</td>\n",
       "      <td>Patton-Rivera</td>\n",
       "      <td>bar</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 22:41:21</th>\n",
       "      <td>395</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>10</td>\n",
       "      <td>9.61</td>\n",
       "      <td>Day-Murray</td>\n",
       "      <td>food truck</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 23:13:30</th>\n",
       "      <td>3309</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4</td>\n",
       "      <td>19.03</td>\n",
       "      <td>Miller-Blevins</td>\n",
       "      <td>pub</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-01 23:15:10</th>\n",
       "      <td>567</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>18</td>\n",
       "      <td>2.95</td>\n",
       "      <td>Cline, Myers and Strong</td>\n",
       "      <td>restaurant</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     txn_id  txn_day  txn_month  txn_hour  txn_min  \\\n",
       "date                                                                 \n",
       "2018-01-01 21:35:10     222      1.0        1.0      21.0     35.0   \n",
       "2018-01-01 21:43:12    2045      1.0        1.0      21.0     43.0   \n",
       "2018-01-01 22:41:21     395      1.0        1.0      22.0     41.0   \n",
       "2018-01-01 23:13:30    3309      1.0        1.0      23.0     13.0   \n",
       "2018-01-01 23:15:10     567      1.0        1.0      23.0     15.0   \n",
       "\n",
       "                     cardholder_id  amount            merchant_name  \\\n",
       "date                                                                  \n",
       "2018-01-01 21:35:10             13    6.22            Dominguez PLC   \n",
       "2018-01-01 21:43:12             13    3.83            Patton-Rivera   \n",
       "2018-01-01 22:41:21             10    9.61               Day-Murray   \n",
       "2018-01-01 23:13:30              4   19.03           Miller-Blevins   \n",
       "2018-01-01 23:15:10             18    2.95  Cline, Myers and Strong   \n",
       "\n",
       "                    merchant_category  twoless_ind  large_ind  \n",
       "date                                                           \n",
       "2018-01-01 21:35:10        food truck            0          0  \n",
       "2018-01-01 21:43:12               bar            0          0  \n",
       "2018-01-01 22:41:21        food truck            0          0  \n",
       "2018-01-01 23:13:30               pub            0          0  \n",
       "2018-01-01 23:15:10        restaurant            0          0  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# loading all data from the database\n",
    "df_transaction = pd.read_sql(query,\n",
    "                             engine,\n",
    "                             index_col = \"date\",\n",
    "                             parse_dates = True)\n",
    "\n",
    "#review sample datas from dataframe\n",
    "df_transaction.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>cardholder_id</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-04-30 18:50:48</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-05 17:49:05</th>\n",
       "      <td>1</td>\n",
       "      <td>67</td>\n",
       "      <td>8.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-09 10:30:47</th>\n",
       "      <td>1</td>\n",
       "      <td>81</td>\n",
       "      <td>8.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-05-21 09:19:58</th>\n",
       "      <td>1</td>\n",
       "      <td>153</td>\n",
       "      <td>4.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-21 16:23:34</th>\n",
       "      <td>1</td>\n",
       "      <td>163</td>\n",
       "      <td>1.10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     cardholder_id  txn_id  amount\n",
       "date                                              \n",
       "2018-04-30 18:50:48              1       1    5.62\n",
       "2018-05-05 17:49:05              1      67    8.42\n",
       "2018-09-09 10:30:47              1      81    8.31\n",
       "2018-05-21 09:19:58              1     153    4.57\n",
       "2018-08-21 16:23:34              1     163    1.10"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#remove unwanted columns\n",
    "df_filtered = df_transaction.filter([\"cardholder_id\",\"txn_id\",\"amount\"]).sort_values(by=[\"cardholder_id\",\"txn_id\"])\n",
    "df_filtered.head()"
   ]
  },
  {
   "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>cardholder_id</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>67</td>\n",
       "      <td>8.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>81</td>\n",
       "      <td>8.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>153</td>\n",
       "      <td>4.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>163</td>\n",
       "      <td>1.10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cardholder_id  txn_id  amount\n",
       "0              1       1    5.62\n",
       "1              1      67    8.42\n",
       "2              1      81    8.31\n",
       "3              1     153    4.57\n",
       "4              1     163    1.10"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#reset index and remove dates column\n",
    "df_filtered = df_filtered.reset_index()\n",
    "df_filtered.drop(columns = \"date\", inplace= True)\n",
    "df_filtered.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cardholder_id</th>\n",
       "      <th></th>\n",
       "      <th></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>1</th>\n",
       "      <td>133.0</td>\n",
       "      <td>110.673609</td>\n",
       "      <td>361.114725</td>\n",
       "      <td>0.69</td>\n",
       "      <td>5.3200</td>\n",
       "      <td>10.580</td>\n",
       "      <td>15.7800</td>\n",
       "      <td>1894.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>99.0</td>\n",
       "      <td>9.591919</td>\n",
       "      <td>5.541201</td>\n",
       "      <td>0.70</td>\n",
       "      <td>4.1400</td>\n",
       "      <td>10.260</td>\n",
       "      <td>13.2950</td>\n",
       "      <td>19.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>58.0</td>\n",
       "      <td>139.172241</td>\n",
       "      <td>329.356054</td>\n",
       "      <td>1.36</td>\n",
       "      <td>5.4450</td>\n",
       "      <td>10.630</td>\n",
       "      <td>15.9400</td>\n",
       "      <td>1160.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>148.0</td>\n",
       "      <td>9.261824</td>\n",
       "      <td>5.751302</td>\n",
       "      <td>0.70</td>\n",
       "      <td>3.6400</td>\n",
       "      <td>10.205</td>\n",
       "      <td>12.1725</td>\n",
       "      <td>21.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>140.0</td>\n",
       "      <td>8.856571</td>\n",
       "      <td>5.888215</td>\n",
       "      <td>0.78</td>\n",
       "      <td>3.4625</td>\n",
       "      <td>7.620</td>\n",
       "      <td>14.2325</td>\n",
       "      <td>20.53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              amount                                                         \\\n",
       "               count        mean         std   min     25%     50%      75%   \n",
       "cardholder_id                                                                 \n",
       "1              133.0  110.673609  361.114725  0.69  5.3200  10.580  15.7800   \n",
       "2               99.0    9.591919    5.541201  0.70  4.1400  10.260  13.2950   \n",
       "3               58.0  139.172241  329.356054  1.36  5.4450  10.630  15.9400   \n",
       "4              148.0    9.261824    5.751302  0.70  3.6400  10.205  12.1725   \n",
       "5              140.0    8.856571    5.888215  0.78  3.4625   7.620  14.2325   \n",
       "\n",
       "                        \n",
       "                   max  \n",
       "cardholder_id           \n",
       "1              1894.00  \n",
       "2                19.51  \n",
       "3              1160.00  \n",
       "4                21.50  \n",
       "5                20.53  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#create a dataframe to hold the key statistics of transaction amount for each cardholder id\n",
    "df_stats = df_filtered[[\"cardholder_id\",\"amount\"]].groupby(\"cardholder_id\").describe()\n",
    "df_stats.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write function that locates outliers using standard deviation\n",
    "def locate_outlier_use_std (selected_cardholder_id):\n",
    "    \n",
    "        #identify mean and std dev\n",
    "        amt_mean = df_stats.iloc[selected_cardholder_id-1 ,1]\n",
    "        amt_std = df_stats.iloc[selected_cardholder_id-1,2]\n",
    "        \n",
    "        #calculate the cut off and define lower and upper limit\n",
    "        cut_off = amt_std * 3\n",
    "        lower_limit = amt_mean - cut_off\n",
    "        upper_limit = amt_mean + cut_off\n",
    "        \n",
    "        #dataframe that only contains the transaction for the selected cardholder id\n",
    "        df_cardholder = df_filtered.loc[df_filtered[\"cardholder_id\"] == selected_cardholder_id]\n",
    "    \n",
    "        max_loop = len(df_cardholder)-1\n",
    "        \n",
    "        #initialise empty list\n",
    "        list_cardholder_id = []\n",
    "        list_txn_id = []\n",
    "        list_txn_amount = []\n",
    "        \n",
    "        for txn_n in range(0,max_loop):\n",
    "            \n",
    "            id_n = df_cardholder.iloc[txn_n, 1]\n",
    "            amt_n = df_cardholder.iloc[txn_n, 2]\n",
    "            \n",
    "            if amt_n <= lower_limit:\n",
    "                list_cardholder_id.append(selected_cardholder_id)\n",
    "                list_txn_id.append(id_n)\n",
    "                list_txn_amount.append(amt_n)\n",
    "                \n",
    "            elif amt_n >= upper_limit:\n",
    "                list_cardholder_id.append(selected_cardholder_id)\n",
    "                list_txn_id.append(id_n)\n",
    "                list_txn_amount.append(amt_n)\n",
    "        \n",
    "        df_outliers = pd.DataFrame({\"cardholder_id\": list_cardholder_id,\n",
    "                                    \"txn_id\":list_txn_id,\n",
    "                                    \"amount\":list_txn_amount})\n",
    "                \n",
    "        display(df_outliers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>cardholder_id</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2497</td>\n",
       "      <td>1790.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2672</td>\n",
       "      <td>1660.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2752</td>\n",
       "      <td>1302.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2913</td>\n",
       "      <td>1691.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>3163</td>\n",
       "      <td>1894.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cardholder_id  txn_id  amount\n",
       "0              1    2497  1790.0\n",
       "1              1    2672  1660.0\n",
       "2              1    2752  1302.0\n",
       "3              1    2913  1691.0\n",
       "4              1    3163  1894.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Find anomalous transactions for 3 random card holders\n",
    "# 1 of 3 card holders\n",
    "locate_outlier_use_std(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>cardholder_id</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>1459</td>\n",
       "      <td>1379.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>2710</td>\n",
       "      <td>2108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>2984</td>\n",
       "      <td>1856.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>3125</td>\n",
       "      <td>2001.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>3225</td>\n",
       "      <td>1398.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cardholder_id  txn_id  amount\n",
       "0              6    1459  1379.0\n",
       "1              6    2710  2108.0\n",
       "2              6    2984  1856.0\n",
       "3              6    3125  2001.0\n",
       "4              6    3225  1398.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Find anomalous transactions for 3 random card holders\n",
    "# 2 of 3 card holders\n",
    "locate_outlier_use_std(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>cardholder_id</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>2696</td>\n",
       "      <td>1724.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>2789</td>\n",
       "      <td>1534.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>3143</td>\n",
       "      <td>1795.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cardholder_id  txn_id  amount\n",
       "0              9    2696  1724.0\n",
       "1              9    2789  1534.0\n",
       "2              9    3143  1795.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Find anomalous transactions for 3 random card holders\n",
    "# 3 of 3 card holders\n",
    "locate_outlier_use_std(9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identifying Outliers Using Interquartile Range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write a function that locates outliers using interquartile range\n",
    "def locate_outlier_use_interquartile (selected_cardholder_id):\n",
    "        #identify the first and third quartile\n",
    "        amt_q1 = df_stats.iloc[selected_cardholder_id-1,4]\n",
    "        amt_q3 = df_stats.iloc[selected_cardholder_id-1,6]\n",
    "        \n",
    "        #calculate the interquartile range\n",
    "        interquartile_range = amt_q3 - amt_q1\n",
    "        \n",
    "        #calculate the cut off and define lower and upper limit\n",
    "        cut_off = interquartile_range * 1.5\n",
    "        lower_limit = amt_q1 - cut_off\n",
    "        upper_limit = amt_q3 + cut_off\n",
    "        \n",
    "        #dataframe that only contains the transaction for the selected cardholder id\n",
    "        df_cardholder = df_filtered.loc[df_filtered[\"cardholder_id\"] == selected_cardholder_id]\n",
    "    \n",
    "        max_loop = len(df_cardholder)-1\n",
    "        \n",
    "        #initialise empty list\n",
    "        list_cardholder_id = []\n",
    "        list_txn_id = []\n",
    "        list_txn_amount = []\n",
    "        \n",
    "        for txn_n in range(0,max_loop):\n",
    "            \n",
    "            id_n = df_cardholder.iloc[txn_n, 1]\n",
    "            amt_n = df_cardholder.iloc[txn_n, 2]\n",
    "            \n",
    "            if amt_n <= lower_limit:\n",
    "                list_cardholder_id.append(selected_cardholder_id)\n",
    "                list_txn_id.append(id_n)\n",
    "                list_txn_amount.append(amt_n)\n",
    "                \n",
    "            elif amt_n >= upper_limit:\n",
    "                list_cardholder_id.append(selected_cardholder_id)\n",
    "                list_txn_id.append(id_n)\n",
    "                list_txn_amount.append(amt_n)\n",
    "        \n",
    "        df_outliers = pd.DataFrame({\"cardholder_id\": list_cardholder_id,\n",
    "                                    \"txn_id\":list_txn_id,\n",
    "                                    \"amount\":list_txn_amount})\n",
    "                \n",
    "        display(df_outliers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>cardholder_id</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>99</td>\n",
       "      <td>1031.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>208</td>\n",
       "      <td>748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12</td>\n",
       "      <td>236</td>\n",
       "      <td>852.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12</td>\n",
       "      <td>1129</td>\n",
       "      <td>1108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12</td>\n",
       "      <td>1204</td>\n",
       "      <td>1123.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12</td>\n",
       "      <td>1622</td>\n",
       "      <td>1102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12</td>\n",
       "      <td>1856</td>\n",
       "      <td>1075.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>12</td>\n",
       "      <td>2240</td>\n",
       "      <td>233.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>12</td>\n",
       "      <td>2610</td>\n",
       "      <td>1530.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>12</td>\n",
       "      <td>2667</td>\n",
       "      <td>1678.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>12</td>\n",
       "      <td>2760</td>\n",
       "      <td>1802.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>3318</td>\n",
       "      <td>1592.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cardholder_id  txn_id  amount\n",
       "0              12      99  1031.0\n",
       "1              12     208   748.0\n",
       "2              12     236   852.0\n",
       "3              12    1129  1108.0\n",
       "4              12    1204  1123.0\n",
       "5              12    1622  1102.0\n",
       "6              12    1856  1075.0\n",
       "7              12    2240   233.0\n",
       "8              12    2610  1530.0\n",
       "9              12    2667  1678.0\n",
       "10             12    2760  1802.0\n",
       "11             12    3318  1592.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Find anomalous transactions for 3 random card holders\n",
    "#1 of 3 - cardholder 12\n",
    "locate_outlier_use_interquartile(12)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>cardholder_id</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "      <td>466</td>\n",
       "      <td>393.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>16</td>\n",
       "      <td>606</td>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "      <td>1191</td>\n",
       "      <td>1103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>1442</td>\n",
       "      <td>1131.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16</td>\n",
       "      <td>1757</td>\n",
       "      <td>1203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>16</td>\n",
       "      <td>1800</td>\n",
       "      <td>1014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>16</td>\n",
       "      <td>2188</td>\n",
       "      <td>229.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>16</td>\n",
       "      <td>2451</td>\n",
       "      <td>1617.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>16</td>\n",
       "      <td>2508</td>\n",
       "      <td>1803.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>16</td>\n",
       "      <td>2520</td>\n",
       "      <td>1634.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>16</td>\n",
       "      <td>2597</td>\n",
       "      <td>1911.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>16</td>\n",
       "      <td>2699</td>\n",
       "      <td>1430.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>16</td>\n",
       "      <td>3338</td>\n",
       "      <td>178.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cardholder_id  txn_id  amount\n",
       "0              16     466   393.0\n",
       "1              16     606    89.0\n",
       "2              16    1191  1103.0\n",
       "3              16    1442  1131.0\n",
       "4              16    1757  1203.0\n",
       "5              16    1800  1014.0\n",
       "6              16    2188   229.0\n",
       "7              16    2451  1617.0\n",
       "8              16    2508  1803.0\n",
       "9              16    2520  1634.0\n",
       "10             16    2597  1911.0\n",
       "11             16    2699  1430.0\n",
       "12             16    3338   178.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Find anomalous transactions for 3 random card holders\n",
    "#2 of 3 cardholder 16\n",
    "locate_outlier_use_interquartile(16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>cardholder_id</th>\n",
       "      <th>txn_id</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24</td>\n",
       "      <td>528</td>\n",
       "      <td>258.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "      <td>682</td>\n",
       "      <td>291.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24</td>\n",
       "      <td>1119</td>\n",
       "      <td>1035.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>24</td>\n",
       "      <td>1821</td>\n",
       "      <td>1011.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24</td>\n",
       "      <td>2192</td>\n",
       "      <td>466.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>24</td>\n",
       "      <td>2241</td>\n",
       "      <td>525.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>24</td>\n",
       "      <td>2461</td>\n",
       "      <td>1301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>24</td>\n",
       "      <td>3064</td>\n",
       "      <td>1901.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cardholder_id  txn_id  amount\n",
       "0             24     528   258.0\n",
       "1             24     682   291.0\n",
       "2             24    1119  1035.0\n",
       "3             24    1821  1011.0\n",
       "4             24    2192   466.0\n",
       "5             24    2241   525.0\n",
       "6             24    2461  1301.0\n",
       "7             24    3064  1901.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Find anomalous transactions for 3 random card holders\n",
    "#3 of 3 cardholder 24\n",
    "locate_outlier_use_interquartile(24)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "file_extension": ".py",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  },
  "mimetype": "text/x-python",
  "name": "python",
  "npconvert_exporter": "python",
  "pygments_lexer": "ipython3",
  "version": 3
 },
 "nbformat": 4,
 "nbformat_minor": 4
}