v1.1-DataPreProcessing.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CapitalOne Data Challenge - Data Processing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Firstly, I loaded required packages, then imported the dataset by using pandas package. Before importing, I changed Null values to NaN in '.txt' file since Python does not convert Null values as NaN.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "#load package\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "import warnings\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "def concat(x):\n",
    "    y=str('T') + str(x)\n",
    "    return y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path= './Desktop/DS-master/cardtransactions.txt' # path of the file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Data Pre-Processing**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create import function\n",
    "def import_data(path):\n",
    "    dfList = []\n",
    "    df=pd.DataFrame()\n",
    "    for iot_chunk in pd.read_json(file_path, lines=True,chunksize=100000) :\n",
    "        dfList.append(iot_chunk)\n",
    "    df = pd.concat(dfList,sort=False)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>accountNumber</th>\n",
       "      <th>customerId</th>\n",
       "      <th>creditLimit</th>\n",
       "      <th>availableMoney</th>\n",
       "      <th>transactionDateTime</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>acqCountry</th>\n",
       "      <th>merchantCountryCode</th>\n",
       "      <th>posEntryMode</th>\n",
       "      <th>posConditionCode</th>\n",
       "      <th>merchantCategoryCode</th>\n",
       "      <th>currentExpDate</th>\n",
       "      <th>accountOpenDate</th>\n",
       "      <th>dateOfLastAddressChange</th>\n",
       "      <th>cardCVV</th>\n",
       "      <th>enteredCVV</th>\n",
       "      <th>cardLast4Digits</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>echoBuffer</th>\n",
       "      <th>currentBalance</th>\n",
       "      <th>merchantCity</th>\n",
       "      <th>merchantState</th>\n",
       "      <th>merchantZip</th>\n",
       "      <th>cardPresent</th>\n",
       "      <th>posOnPremises</th>\n",
       "      <th>recurringAuthInd</th>\n",
       "      <th>expirationDateKeyInMatch</th>\n",
       "      <th>isFraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>737265056</td>\n",
       "      <td>737265056</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>2016-08-13T14:27:32</td>\n",
       "      <td>98.55</td>\n",
       "      <td>Uber</td>\n",
       "      <td>US</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>rideshare</td>\n",
       "      <td>06/2023</td>\n",
       "      <td>2015-03-14</td>\n",
       "      <td>2015-03-14</td>\n",
       "      <td>414</td>\n",
       "      <td>414</td>\n",
       "      <td>1803</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>737265056</td>\n",
       "      <td>737265056</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>2016-10-11T05:05:54</td>\n",
       "      <td>74.51</td>\n",
       "      <td>AMC #191138</td>\n",
       "      <td>US</td>\n",
       "      <td>US</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>entertainment</td>\n",
       "      <td>02/2024</td>\n",
       "      <td>2015-03-14</td>\n",
       "      <td>2015-03-14</td>\n",
       "      <td>486</td>\n",
       "      <td>486</td>\n",
       "      <td>767</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>737265056</td>\n",
       "      <td>737265056</td>\n",
       "      <td>5000</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>2016-11-08T09:18:39</td>\n",
       "      <td>7.47</td>\n",
       "      <td>Play Store</td>\n",
       "      <td>US</td>\n",
       "      <td>US</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>mobileapps</td>\n",
       "      <td>08/2025</td>\n",
       "      <td>2015-03-14</td>\n",
       "      <td>2015-03-14</td>\n",
       "      <td>486</td>\n",
       "      <td>486</td>\n",
       "      <td>767</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   accountNumber  customerId  creditLimit  availableMoney  \\\n",
       "0      737265056   737265056         5000          5000.0   \n",
       "1      737265056   737265056         5000          5000.0   \n",
       "2      737265056   737265056         5000          5000.0   \n",
       "\n",
       "   transactionDateTime  transactionAmount merchantName acqCountry  \\\n",
       "0  2016-08-13T14:27:32              98.55         Uber         US   \n",
       "1  2016-10-11T05:05:54              74.51  AMC #191138         US   \n",
       "2  2016-11-08T09:18:39               7.47   Play Store         US   \n",
       "\n",
       "  merchantCountryCode  posEntryMode  posConditionCode merchantCategoryCode  \\\n",
       "0                  US           2.0               1.0            rideshare   \n",
       "1                  US           9.0               1.0        entertainment   \n",
       "2                  US           9.0               1.0           mobileapps   \n",
       "\n",
       "  currentExpDate accountOpenDate dateOfLastAddressChange  cardCVV  enteredCVV  \\\n",
       "0        06/2023      2015-03-14              2015-03-14      414         414   \n",
       "1        02/2024      2015-03-14              2015-03-14      486         486   \n",
       "2        08/2025      2015-03-14              2015-03-14      486         486   \n",
       "\n",
       "   cardLast4Digits transactionType  echoBuffer  currentBalance  merchantCity  \\\n",
       "0             1803        PURCHASE         NaN             0.0           NaN   \n",
       "1              767        PURCHASE         NaN             0.0           NaN   \n",
       "2              767        PURCHASE         NaN             0.0           NaN   \n",
       "\n",
       "   merchantState  merchantZip  cardPresent  posOnPremises  recurringAuthInd  \\\n",
       "0            NaN          NaN        False            NaN               NaN   \n",
       "1            NaN          NaN         True            NaN               NaN   \n",
       "2            NaN          NaN        False            NaN               NaN   \n",
       "\n",
       "   expirationDateKeyInMatch  isFraud  \n",
       "0                     False    False  \n",
       "1                     False    False  \n",
       "2                     False    False  "
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', None) # pandas setting\n",
    "transactions=import_data(file_path) # execute the function\n",
    "transactions.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 2.,  9.,  5., 80., 90., nan])"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions.posEntryMode.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1.,  8., 99., nan])"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions.posConditionCode.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I removed several columns including all Null values in the dataset to reduce dimensionality.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "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>index</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>recurringAuthInd</td>\n",
       "      <td>786363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>posOnPremises</td>\n",
       "      <td>786363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>merchantZip</td>\n",
       "      <td>786363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>merchantState</td>\n",
       "      <td>786363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>merchantCity</td>\n",
       "      <td>786363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>echoBuffer</td>\n",
       "      <td>786363</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>acqCountry</td>\n",
       "      <td>4562</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>posEntryMode</td>\n",
       "      <td>4054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>merchantCountryCode</td>\n",
       "      <td>724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>transactionType</td>\n",
       "      <td>698</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>posConditionCode</td>\n",
       "      <td>409</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  index       0\n",
       "26     recurringAuthInd  786363\n",
       "25        posOnPremises  786363\n",
       "23          merchantZip  786363\n",
       "22        merchantState  786363\n",
       "21         merchantCity  786363\n",
       "19           echoBuffer  786363\n",
       "7            acqCountry    4562\n",
       "9          posEntryMode    4054\n",
       "8   merchantCountryCode     724\n",
       "18      transactionType     698\n",
       "10     posConditionCode     409"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(transactions.isnull().sum()).reset_index(drop=False).sort_values(by=0,ascending=False)[:11]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions.drop(['echoBuffer','merchantCity','merchantState','merchantZip','posOnPremises','recurringAuthInd'],\n",
    "                  axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I created 'Date' column by splitting original 'TransactionDate' column.The dataset obtains transactions in year 2016.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions['Date']=transactions['transactionDateTime'].apply(lambda x: x.split('T')[0]) # create 'Date' column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "transactions.reset_index(drop=False,inplace=True)\n",
    "transactions['transactionKey']=transactions['index'].apply(lambda x: concat(x))\n",
    "transactions.drop(['index'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**This table indicates how many transactions each account had for each merchant in year 2016. (use the table in the next steps)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st BBQ</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Pub</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Sandwitch Bar #758805</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100088067</td>\n",
       "      <td>34th BBQ #412895</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #191138</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId               merchantName  Count\n",
       "0   100088067                    1st BBQ      1\n",
       "1   100088067                    1st Pub      1\n",
       "2   100088067  1st Sandwitch Bar #758805      1\n",
       "3   100088067           34th BBQ #412895      1\n",
       "4   100088067                AMC #191138      1"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cust_length=transactions.groupby(['customerId','merchantName']).count()['Date']\n",
    "cust_length=pd.DataFrame(cust_length).reset_index()\n",
    "cust_length.columns=['customerId','merchantName','Count']\n",
    "cust_length.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Pre-Processing | Case Study 1: Detect Reversal transactions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Firstly, I created Timestamp feature by modifying 'transactionDateTime' column and replaced null values of transaction type with 'Purchase'. Then, I sorted datasets based on several columns such as customerID, merchantName and Timestamp in ascending order.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "card_data=transactions.copy() # make a copy\n",
    "card_data['transactionType']=card_data['transactionType'].fillna(value='PURCHASE')\n",
    "card_data['Date']=card_data['transactionDateTime'].apply(lambda x: x.split('T')[0]) # create 'Date' column\n",
    "card_data['Time']=card_data['transactionDateTime'].apply(lambda x: x.split('T')[1]) # create 'Date' column\n",
    "card_data.Date=pd.to_datetime(card_data.Date)\n",
    "card_data['Timestamp']=pd.to_datetime(card_data.Date.astype(str)+' '+card_data.Time.astype(str))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "card_data=card_data[['customerId','merchantName','transactionAmount','transactionType','transactionKey','Timestamp']]\n",
    "card_data=card_data.sort_values(by=['customerId','merchantName','Timestamp'],ascending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Assumption 1: Transaction amount of 'address_verification' is 0; there is no effect in modeling part. Hence, I removed the transactions whose type is 'address_verification'.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove the observations including 'ADDRESS_VERIIFICATION'\n",
    "card_data=card_data.reset_index(drop=True)\n",
    "card_data=card_data[-(card_data.transactionType=='ADDRESS_VERIFICATION')]\n",
    "card_data=card_data.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Assumption 2: I suppose that purchase and reversal transactions are successive and the same $ amount. Hence, I took indexes of reversal transactions and purchase transactions by using 'transactionType' feature.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "reversal_index=card_data[card_data['transactionType']=='REVERSAL'].index.values.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #79863</td>\n",
       "      <td>22.32</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T541964</td>\n",
       "      <td>2016-11-20 08:00:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>100328049</td>\n",
       "      <td>Lyft</td>\n",
       "      <td>43.74</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T87920</td>\n",
       "      <td>2016-01-15 20:36:18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     customerId merchantName  transactionAmount transactionType  \\\n",
       "13    100088067   AMC #79863              22.32        REVERSAL   \n",
       "124   100328049         Lyft              43.74        REVERSAL   \n",
       "\n",
       "    transactionKey           Timestamp  \n",
       "13         T541964 2016-11-20 08:00:04  \n",
       "124         T87920 2016-01-15 20:36:18  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "second_reversal=card_data.iloc[reversal_index]\n",
    "second_reversal.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "previous_index=reversal_index-1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #79863</td>\n",
       "      <td>22.32</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541963</td>\n",
       "      <td>2016-11-20 07:57:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>100328049</td>\n",
       "      <td>Lyft</td>\n",
       "      <td>43.74</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T87919</td>\n",
       "      <td>2016-01-15 20:34:35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     customerId merchantName  transactionAmount transactionType  \\\n",
       "12    100088067   AMC #79863              22.32        PURCHASE   \n",
       "123   100328049         Lyft              43.74        PURCHASE   \n",
       "\n",
       "    transactionKey           Timestamp  \n",
       "12         T541963 2016-11-20 07:57:05  \n",
       "123         T87919 2016-01-15 20:34:35  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_reversal=card_data.iloc[previous_index]\n",
    "first_reversal.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I created the dataframe which demonstrates indexes and transaction amounts of purchase and reversal transactions. Then, I realized that purchase transactions and its reversal transactions do not have to be consecutive.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "previndex=pd.DataFrame(previous_index)\n",
    "previndex.columns=['Purchase_Index'] # rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "revindex=pd.DataFrame(reversal_index)\n",
    "revindex.columns=['Reversal_Index'] # rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "firstreverse=pd.DataFrame(first_reversal['transactionAmount'].values)\n",
    "firstreverse.columns=['Purchase_Amount'] # rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "secondreverse=pd.DataFrame(second_reversal['transactionAmount'].values)\n",
    "secondreverse.columns=['Reversal_Amount'] # rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>Purchase_Index</th>\n",
       "      <th>Purchase_Amount</th>\n",
       "      <th>Reversal_Index</th>\n",
       "      <th>Reversal_Amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>22.32</td>\n",
       "      <td>13</td>\n",
       "      <td>22.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>123</td>\n",
       "      <td>43.74</td>\n",
       "      <td>124</td>\n",
       "      <td>43.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>139</td>\n",
       "      <td>284.97</td>\n",
       "      <td>140</td>\n",
       "      <td>284.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>262</td>\n",
       "      <td>93.67</td>\n",
       "      <td>263</td>\n",
       "      <td>93.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>277</td>\n",
       "      <td>501.29</td>\n",
       "      <td>278</td>\n",
       "      <td>501.29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Purchase_Index  Purchase_Amount  Reversal_Index  Reversal_Amount\n",
       "0              12            22.32              13            22.32\n",
       "1             123            43.74             124            43.74\n",
       "2             139           284.97             140           284.97\n",
       "3             262            93.67             263            93.67\n",
       "4             277           501.29             278           501.29"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "logic=pd.concat([previndex,firstreverse,revindex,secondreverse],axis=1) # merge all dataframes vertically\n",
    "logic.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "logic['NotEqual']=logic[['Purchase_Amount','Reversal_Amount']].apply(lambda x: True if (x[0]!=x[1]) else False,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "logic=logic[-(logic.Reversal_Amount==0)] # exlude the transactions whose amount is $0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    12665\n",
       "True      7066\n",
       "Name: NotEqual, dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "logic.NotEqual.value_counts() # check whether purchase and reversal transactions are successive or not"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I saved the transactions whose purchase and reversal types are consecutive (size:12665)**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "capture1=logic.loc[logic['NotEqual'] == False, 'Reversal_Index'].values #take reversal indexes\n",
    "capture2=logic.loc[logic['NotEqual'] == False, 'Purchase_Index'].values #take purchase indexes\n",
    "cpt=np.concatenate((capture1, capture2)) # combine two arrays\n",
    "cpt1=card_data.iloc[cpt] # take dataframe regarding these indexes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #79863</td>\n",
       "      <td>22.32</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541963</td>\n",
       "      <td>2016-11-20 07:57:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #79863</td>\n",
       "      <td>22.32</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T541964</td>\n",
       "      <td>2016-11-20 08:00:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>100328049</td>\n",
       "      <td>Lyft</td>\n",
       "      <td>43.74</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T87919</td>\n",
       "      <td>2016-01-15 20:34:35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     customerId merchantName  transactionAmount transactionType  \\\n",
       "12    100088067   AMC #79863              22.32        PURCHASE   \n",
       "13    100088067   AMC #79863              22.32        REVERSAL   \n",
       "123   100328049         Lyft              43.74        PURCHASE   \n",
       "\n",
       "    transactionKey           Timestamp  \n",
       "12         T541963 2016-11-20 07:57:05  \n",
       "13         T541964 2016-11-20 08:00:04  \n",
       "123         T87919 2016-01-15 20:34:35  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cpt1=cpt1.sort_values(by=['customerId','merchantName','Timestamp'],ascending=True)\n",
    "cpt1.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "REVERSAL    12665\n",
       "PURCHASE    12665\n",
       "Name: transactionType, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cpt1.transactionType.value_counts() # check the distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #79863</td>\n",
       "      <td>22.32</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541963</td>\n",
       "      <td>2016-11-20 07:57:05</td>\n",
       "      <td>100088067-AMC #79863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #79863</td>\n",
       "      <td>22.32</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T541964</td>\n",
       "      <td>2016-11-20 08:00:04</td>\n",
       "      <td>100088067-AMC #79863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>100328049</td>\n",
       "      <td>Lyft</td>\n",
       "      <td>43.74</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T87919</td>\n",
       "      <td>2016-01-15 20:34:35</td>\n",
       "      <td>100328049-Lyft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>100328049</td>\n",
       "      <td>Lyft</td>\n",
       "      <td>43.74</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T87920</td>\n",
       "      <td>2016-01-15 20:36:18</td>\n",
       "      <td>100328049-Lyft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>100328049</td>\n",
       "      <td>McDonalds #423357</td>\n",
       "      <td>284.97</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T87935</td>\n",
       "      <td>2016-03-24 22:57:15</td>\n",
       "      <td>100328049-McDonalds #423357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     customerId       merchantName  transactionAmount transactionType  \\\n",
       "12    100088067         AMC #79863              22.32        PURCHASE   \n",
       "13    100088067         AMC #79863              22.32        REVERSAL   \n",
       "123   100328049               Lyft              43.74        PURCHASE   \n",
       "124   100328049               Lyft              43.74        REVERSAL   \n",
       "139   100328049  McDonalds #423357             284.97        PURCHASE   \n",
       "\n",
       "    transactionKey           Timestamp                           ID  \n",
       "12         T541963 2016-11-20 07:57:05         100088067-AMC #79863  \n",
       "13         T541964 2016-11-20 08:00:04         100088067-AMC #79863  \n",
       "123         T87919 2016-01-15 20:34:35               100328049-Lyft  \n",
       "124         T87920 2016-01-15 20:36:18               100328049-Lyft  \n",
       "139         T87935 2016-03-24 22:57:15  100328049-McDonalds #423357  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "warnings.filterwarnings(\"ignore\")\n",
    "cpt1['ID'] = cpt1.customerId.map(str) + \"-\" + cpt1.merchantName\n",
    "cpt1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I will capture purchase transactions associated to 7066 reversal observations in the later step.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "rvs=logic.loc[logic['NotEqual'] == True, 'Reversal_Index'].values "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "nexts=card_data.iloc[rvs]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>669</th>\n",
       "      <td>101380713</td>\n",
       "      <td>sears.com</td>\n",
       "      <td>5.03</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T3010</td>\n",
       "      <td>2016-06-22 22:10:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>848</th>\n",
       "      <td>101661970</td>\n",
       "      <td>American Airlines</td>\n",
       "      <td>18.15</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T470094</td>\n",
       "      <td>2016-04-24 14:03:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1047</th>\n",
       "      <td>101738384</td>\n",
       "      <td>discount.com</td>\n",
       "      <td>174.92</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T736347</td>\n",
       "      <td>2016-03-17 05:37:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1161</th>\n",
       "      <td>101738384</td>\n",
       "      <td>staples.com</td>\n",
       "      <td>954.71</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T736421</td>\n",
       "      <td>2016-06-11 00:47:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1329</th>\n",
       "      <td>101876201</td>\n",
       "      <td>discount.com</td>\n",
       "      <td>163.75</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T542091</td>\n",
       "      <td>2016-06-28 10:10:22</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      customerId       merchantName  transactionAmount transactionType  \\\n",
       "669    101380713          sears.com               5.03        REVERSAL   \n",
       "848    101661970  American Airlines              18.15        REVERSAL   \n",
       "1047   101738384       discount.com             174.92        REVERSAL   \n",
       "1161   101738384        staples.com             954.71        REVERSAL   \n",
       "1329   101876201       discount.com             163.75        REVERSAL   \n",
       "\n",
       "     transactionKey           Timestamp  \n",
       "669           T3010 2016-06-22 22:10:22  \n",
       "848         T470094 2016-04-24 14:03:36  \n",
       "1047        T736347 2016-03-17 05:37:44  \n",
       "1161        T736421 2016-06-11 00:47:21  \n",
       "1329        T542091 2016-06-28 10:10:22  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nexts.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7066"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nexts.transactionKey.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "card_upd=card_data[-(card_data.transactionType=='REVERSAL')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.filterwarnings(\"ignore\")\n",
    "nexts['ID'] = nexts.customerId.map(str) + \"-\" + nexts.merchantName"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I wrote the \"for loop\" below which helps exploring purchase transactions associated to reversal ones when they are not consecutive.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfz={}\n",
    "for i in nexts.customerId.unique():\n",
    "    for j in nexts.merchantName.unique():\n",
    "        if str(i) + \"-\"+j in nexts.ID.unique():\n",
    "            inter=nexts[(nexts.customerId==i) & (nexts.merchantName==j)]\n",
    "            size=len(inter)\n",
    "            for k in range(size):\n",
    "                z=inter.iloc[k]\n",
    "                amnt=z.transactionAmount\n",
    "                df=card_upd[(card_upd.customerId==i) & (card_upd.merchantName==j)]\n",
    "                q=df[(df.transactionAmount==amnt)]\n",
    "            dfz[i,j,k]=q"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**After, I created the dataframe to combine all purchase transactions associated to reversal ones.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101738384</td>\n",
       "      <td>discount.com</td>\n",
       "      <td>174.92</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T736332</td>\n",
       "      <td>2016-03-03 19:51:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101738384</td>\n",
       "      <td>staples.com</td>\n",
       "      <td>954.71</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T736406</td>\n",
       "      <td>2016-05-26 07:14:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102755773</td>\n",
       "      <td>gap.com</td>\n",
       "      <td>81.69</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T690279</td>\n",
       "      <td>2016-10-08 21:12:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>102755773</td>\n",
       "      <td>target.com</td>\n",
       "      <td>53.78</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T690011</td>\n",
       "      <td>2016-01-29 19:10:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>102980467</td>\n",
       "      <td>Seattle News</td>\n",
       "      <td>303.61</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T32965</td>\n",
       "      <td>2016-06-04 22:48:56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId  merchantName  transactionAmount transactionType transactionKey  \\\n",
       "0   101738384  discount.com             174.92        PURCHASE        T736332   \n",
       "1   101738384   staples.com             954.71        PURCHASE        T736406   \n",
       "2   102755773       gap.com              81.69        PURCHASE        T690279   \n",
       "3   102755773    target.com              53.78        PURCHASE        T690011   \n",
       "4   102980467  Seattle News             303.61        PURCHASE         T32965   \n",
       "\n",
       "            Timestamp  \n",
       "0 2016-03-03 19:51:21  \n",
       "1 2016-05-26 07:14:32  \n",
       "2 2016-10-08 21:12:06  \n",
       "3 2016-01-29 19:10:40  \n",
       "4 2016-06-04 22:48:56  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nrow=len(dfz.keys())\n",
    "df_upd_data=pd.DataFrame()\n",
    "for i in range(nrow):\n",
    "    df_inter=dfz[[*dfz.keys()][i]]\n",
    "    df_upd_data=df_upd_data.append(df_inter)\n",
    "df_upd_data=df_upd_data.reset_index(drop=True)\n",
    "df_upd_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101738384</td>\n",
       "      <td>discount.com</td>\n",
       "      <td>174.92</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T736332</td>\n",
       "      <td>2016-03-03 19:51:21</td>\n",
       "      <td>101738384-discount.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101738384</td>\n",
       "      <td>staples.com</td>\n",
       "      <td>954.71</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T736406</td>\n",
       "      <td>2016-05-26 07:14:32</td>\n",
       "      <td>101738384-staples.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>102755773</td>\n",
       "      <td>gap.com</td>\n",
       "      <td>81.69</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T690279</td>\n",
       "      <td>2016-10-08 21:12:06</td>\n",
       "      <td>102755773-gap.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>102755773</td>\n",
       "      <td>target.com</td>\n",
       "      <td>53.78</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T690011</td>\n",
       "      <td>2016-01-29 19:10:40</td>\n",
       "      <td>102755773-target.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>102980467</td>\n",
       "      <td>Seattle News</td>\n",
       "      <td>303.61</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T32965</td>\n",
       "      <td>2016-06-04 22:48:56</td>\n",
       "      <td>102980467-Seattle News</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId  merchantName  transactionAmount transactionType transactionKey  \\\n",
       "0   101738384  discount.com             174.92        PURCHASE        T736332   \n",
       "1   101738384   staples.com             954.71        PURCHASE        T736406   \n",
       "2   102755773       gap.com              81.69        PURCHASE        T690279   \n",
       "3   102755773    target.com              53.78        PURCHASE        T690011   \n",
       "4   102980467  Seattle News             303.61        PURCHASE         T32965   \n",
       "\n",
       "            Timestamp                      ID  \n",
       "0 2016-03-03 19:51:21  101738384-discount.com  \n",
       "1 2016-05-26 07:14:32   101738384-staples.com  \n",
       "2 2016-10-08 21:12:06       102755773-gap.com  \n",
       "3 2016-01-29 19:10:40    102755773-target.com  \n",
       "4 2016-06-04 22:48:56  102980467-Seattle News  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "warnings.filterwarnings(\"ignore\")\n",
    "df_upd_data['ID'] = df_upd_data.customerId.map(str) + \"-\" + df_upd_data.merchantName\n",
    "df_upd_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101380713</td>\n",
       "      <td>sears.com</td>\n",
       "      <td>5.03</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T3010</td>\n",
       "      <td>2016-06-22 22:10:22</td>\n",
       "      <td>101380713-sears.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101661970</td>\n",
       "      <td>American Airlines</td>\n",
       "      <td>18.15</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T470094</td>\n",
       "      <td>2016-04-24 14:03:36</td>\n",
       "      <td>101661970-American Airlines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101738384</td>\n",
       "      <td>discount.com</td>\n",
       "      <td>174.92</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T736332</td>\n",
       "      <td>2016-03-03 19:51:21</td>\n",
       "      <td>101738384-discount.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101738384</td>\n",
       "      <td>discount.com</td>\n",
       "      <td>174.92</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T736347</td>\n",
       "      <td>2016-03-17 05:37:44</td>\n",
       "      <td>101738384-discount.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101738384</td>\n",
       "      <td>staples.com</td>\n",
       "      <td>954.71</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T736406</td>\n",
       "      <td>2016-05-26 07:14:32</td>\n",
       "      <td>101738384-staples.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId       merchantName  transactionAmount transactionType  \\\n",
       "0   101380713          sears.com               5.03        REVERSAL   \n",
       "1   101661970  American Airlines              18.15        REVERSAL   \n",
       "2   101738384       discount.com             174.92        PURCHASE   \n",
       "3   101738384       discount.com             174.92        REVERSAL   \n",
       "4   101738384        staples.com             954.71        PURCHASE   \n",
       "\n",
       "  transactionKey           Timestamp                           ID  \n",
       "0          T3010 2016-06-22 22:10:22          101380713-sears.com  \n",
       "1        T470094 2016-04-24 14:03:36  101661970-American Airlines  \n",
       "2        T736332 2016-03-03 19:51:21       101738384-discount.com  \n",
       "3        T736347 2016-03-17 05:37:44       101738384-discount.com  \n",
       "4        T736406 2016-05-26 07:14:32        101738384-staples.com  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cpt2=pd.concat([df_upd_data,nexts],axis=0)\n",
    "cpt2=cpt2.sort_values(by=['customerId','merchantName','Timestamp'],ascending=True)\n",
    "cpt2=cpt2.reset_index(drop=True)\n",
    "cpt2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "REVERSAL    7066\n",
       "PURCHASE    2798\n",
       "Name: transactionType, dtype: int64"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cpt2.transactionType.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Lessons Learned: I was not able to match 4268 reversal transactions. The possible reasons for this are 1. Data Quality 2. Transaction amount in purchase being different than the reversal amount.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #79863</td>\n",
       "      <td>22.32</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541963</td>\n",
       "      <td>2016-11-20 07:57:05</td>\n",
       "      <td>100088067-AMC #79863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #79863</td>\n",
       "      <td>22.32</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T541964</td>\n",
       "      <td>2016-11-20 08:00:04</td>\n",
       "      <td>100088067-AMC #79863</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100328049</td>\n",
       "      <td>Lyft</td>\n",
       "      <td>43.74</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T87919</td>\n",
       "      <td>2016-01-15 20:34:35</td>\n",
       "      <td>100328049-Lyft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100328049</td>\n",
       "      <td>Lyft</td>\n",
       "      <td>43.74</td>\n",
       "      <td>REVERSAL</td>\n",
       "      <td>T87920</td>\n",
       "      <td>2016-01-15 20:36:18</td>\n",
       "      <td>100328049-Lyft</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100328049</td>\n",
       "      <td>McDonalds #423357</td>\n",
       "      <td>284.97</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T87935</td>\n",
       "      <td>2016-03-24 22:57:15</td>\n",
       "      <td>100328049-McDonalds #423357</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId       merchantName  transactionAmount transactionType  \\\n",
       "0   100088067         AMC #79863              22.32        PURCHASE   \n",
       "1   100088067         AMC #79863              22.32        REVERSAL   \n",
       "2   100328049               Lyft              43.74        PURCHASE   \n",
       "3   100328049               Lyft              43.74        REVERSAL   \n",
       "4   100328049  McDonalds #423357             284.97        PURCHASE   \n",
       "\n",
       "  transactionKey           Timestamp                           ID  \n",
       "0        T541963 2016-11-20 07:57:05         100088067-AMC #79863  \n",
       "1        T541964 2016-11-20 08:00:04         100088067-AMC #79863  \n",
       "2         T87919 2016-01-15 20:34:35               100328049-Lyft  \n",
       "3         T87920 2016-01-15 20:36:18               100328049-Lyft  \n",
       "4         T87935 2016-03-24 22:57:15  100328049-McDonalds #423357  "
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_reversal=pd.concat([cpt1,cpt2],axis=0).reset_index(drop=True)\n",
    "all_reversal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "REVERSAL    19731\n",
       "PURCHASE    15463\n",
       "Name: transactionType, dtype: int64"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_reversal['transactionType'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "transactionType\n",
       "PURCHASE    2322179.48\n",
       "REVERSAL    2821792.50\n",
       "Name: transactionAmount, dtype: float64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_reversal.groupby(['transactionType']).sum()['transactionAmount']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "# validate my work by checking it via Excel\n",
    "#q=deneme[deneme.customerId==111113489]\n",
    "#q.to_csv('secondcust_valid.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question 3-part a: What total number of transactions and total dollar amount do you estimate for the reversed transactions?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Final-1: As a result of my analysis, I found 15463 reversal transactions and total dollar value of these are $2,322,179.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Final-2: I was not able to find purchase associated to reversal transactions (4268) that corresponds to $499,613.02.**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Remove Duplicates (Reversal Transactions)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit_card_no_duplicate=card_data[~(card_data.transactionKey.isin(all_reversal.transactionKey))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(731000, 6)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credit_card_no_duplicate.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st BBQ</td>\n",
       "      <td>200.12</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541917</td>\n",
       "      <td>2016-04-08 19:56:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Pub</td>\n",
       "      <td>95.11</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541962</td>\n",
       "      <td>2016-11-09 07:26:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Sandwitch Bar #758805</td>\n",
       "      <td>5.26</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541920</td>\n",
       "      <td>2016-05-06 02:54:01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId               merchantName  transactionAmount transactionType  \\\n",
       "0   100088067                    1st BBQ             200.12        PURCHASE   \n",
       "1   100088067                    1st Pub              95.11        PURCHASE   \n",
       "2   100088067  1st Sandwitch Bar #758805               5.26        PURCHASE   \n",
       "\n",
       "  transactionKey           Timestamp  \n",
       "0        T541917 2016-04-08 19:56:36  \n",
       "1        T541962 2016-11-09 07:26:06  \n",
       "2        T541920 2016-05-06 02:54:01  "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "credit_card_no_duplicate.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Pre-processing | Case Study 2: Multiple Swipes Detection**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question 3-part b: What total number of transactions and total dollar amount do you estimate for the multi-swipe transactions?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I assume that multi-swipe transactions are consecutive in the data. My approach is that I took sub dataframes for each customer and merchant combination. Then, I sorted their transactions based on Timestamp and referenced their first transaction as the origin point at customer/merchant level**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**After, I calculated how many seconds passed from previous transactions to specific transaction. When the elapsed time is less than 3 minutes and transaction amount is the same, I defined these transactions as multiple swipes.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st BBQ</td>\n",
       "      <td>200.12</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541917</td>\n",
       "      <td>2016-04-08 19:56:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Pub</td>\n",
       "      <td>95.11</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541962</td>\n",
       "      <td>2016-11-09 07:26:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Sandwitch Bar #758805</td>\n",
       "      <td>5.26</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541920</td>\n",
       "      <td>2016-05-06 02:54:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100088067</td>\n",
       "      <td>34th BBQ #412895</td>\n",
       "      <td>141.60</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541904</td>\n",
       "      <td>2016-02-03 04:48:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100088067</td>\n",
       "      <td>AMC #191138</td>\n",
       "      <td>304.15</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541925</td>\n",
       "      <td>2016-06-03 18:50:55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId               merchantName  transactionAmount transactionType  \\\n",
       "0   100088067                    1st BBQ             200.12        PURCHASE   \n",
       "1   100088067                    1st Pub              95.11        PURCHASE   \n",
       "2   100088067  1st Sandwitch Bar #758805               5.26        PURCHASE   \n",
       "3   100088067           34th BBQ #412895             141.60        PURCHASE   \n",
       "4   100088067                AMC #191138             304.15        PURCHASE   \n",
       "\n",
       "  transactionKey           Timestamp  \n",
       "0        T541917 2016-04-08 19:56:36  \n",
       "1        T541962 2016-11-09 07:26:06  \n",
       "2        T541920 2016-05-06 02:54:01  \n",
       "3        T541904 2016-02-03 04:48:54  \n",
       "4        T541925 2016-06-03 18:50:55  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions_mlt=credit_card_no_duplicate\n",
    "transactions_mlt.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "df_dataz={}\n",
    "CustomerList=transactions_mlt.customerId.unique()\n",
    "for i in CustomerList:\n",
    "    sample_mlt=transactions_mlt[transactions_mlt.customerId==i]\n",
    "    for j in sample_mlt.merchantName.unique():\n",
    "        if cust_length[(cust_length.customerId==i) & (cust_length.merchantName==j)]['Count'].values>1:\n",
    "            sample_merchant=sample_mlt[sample_mlt.merchantName==j]\n",
    "            sample_merchant=sample_merchant.reset_index(drop=True)\n",
    "            sample_merchant=sample_merchant.sort_values(by='Timestamp',ascending=True)\n",
    "    \n",
    "            TimeSeconds=[]\n",
    "            for i in range(1,len(sample_merchant)):\n",
    "                Seconds=(sample_merchant['Timestamp'][i]-sample_merchant['Timestamp'][0]).total_seconds()\n",
    "                TimeSeconds.append(Seconds)\n",
    "            TimeSeconds.insert(0,0)\n",
    "    \n",
    "            sample_merchant['Seconds']=TimeSeconds\n",
    "            sample_merchant['SecondsDiff']=sample_merchant['Seconds'].diff()\n",
    "            sample_merchant['SecondsDiff'].iloc[0]=sample_merchant['SecondsDiff'].max()\n",
    "    \n",
    "            sample_merchant['AmountDiff']=sample_merchant['transactionAmount'].diff()\n",
    "            sample_merchant['AmountDiff'].iloc[0]=sample_merchant['AmountDiff'].max()\n",
    "    \n",
    "            sample_merchant['MultiSwipes']=sample_merchant[['SecondsDiff','AmountDiff']].apply(lambda x: True if (x[0]<180 and x[1]==0) else False,axis=1)\n",
    "            df_dataz[i,j]=sample_merchant"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>Seconds</th>\n",
       "      <th>SecondsDiff</th>\n",
       "      <th>AmountDiff</th>\n",
       "      <th>MultiSwipes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>999086814</td>\n",
       "      <td>AMC #552863</td>\n",
       "      <td>672.69</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T388392</td>\n",
       "      <td>2016-01-16 07:46:07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>21069985.0</td>\n",
       "      <td>-569.44</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>999086814</td>\n",
       "      <td>AMC #552863</td>\n",
       "      <td>103.25</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T388833</td>\n",
       "      <td>2016-09-16 04:32:32</td>\n",
       "      <td>21069985.0</td>\n",
       "      <td>21069985.0</td>\n",
       "      <td>-569.44</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100108752</td>\n",
       "      <td>Shell Gas #494785</td>\n",
       "      <td>45.35</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T225227</td>\n",
       "      <td>2016-05-07 21:37:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1271994.0</td>\n",
       "      <td>12.96</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100108752</td>\n",
       "      <td>Shell Gas #494785</td>\n",
       "      <td>44.21</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T225228</td>\n",
       "      <td>2016-05-22 01:29:56</td>\n",
       "      <td>1223567.0</td>\n",
       "      <td>1223567.0</td>\n",
       "      <td>-1.14</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100108752</td>\n",
       "      <td>Shell Gas #494785</td>\n",
       "      <td>45.35</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T225229</td>\n",
       "      <td>2016-06-05 03:44:36</td>\n",
       "      <td>2441247.0</td>\n",
       "      <td>1217680.0</td>\n",
       "      <td>1.14</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId       merchantName  transactionAmount transactionType  \\\n",
       "0   999086814        AMC #552863             672.69        PURCHASE   \n",
       "1   999086814        AMC #552863             103.25        PURCHASE   \n",
       "2   100108752  Shell Gas #494785              45.35        PURCHASE   \n",
       "3   100108752  Shell Gas #494785              44.21        PURCHASE   \n",
       "4   100108752  Shell Gas #494785              45.35        PURCHASE   \n",
       "\n",
       "  transactionKey           Timestamp     Seconds  SecondsDiff  AmountDiff  \\\n",
       "0        T388392 2016-01-16 07:46:07         0.0   21069985.0     -569.44   \n",
       "1        T388833 2016-09-16 04:32:32  21069985.0   21069985.0     -569.44   \n",
       "2        T225227 2016-05-07 21:37:09         0.0    1271994.0       12.96   \n",
       "3        T225228 2016-05-22 01:29:56   1223567.0    1223567.0       -1.14   \n",
       "4        T225229 2016-06-05 03:44:36   2441247.0    1217680.0        1.14   \n",
       "\n",
       "   MultiSwipes  \n",
       "0        False  \n",
       "1        False  \n",
       "2        False  \n",
       "3        False  \n",
       "4        False  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nrow=len(df_dataz.keys())\n",
    "df_upd_data=pd.DataFrame()\n",
    "for i in range(nrow):\n",
    "    df_inter=df_dataz[[*df_dataz.keys()][i]]\n",
    "    df_upd_data=df_upd_data.append(df_inter)\n",
    "df_upd_data=df_upd_data.reset_index(drop=True)\n",
    "df_upd_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "swipes=df_upd_data[df_upd_data.MultiSwipes==True]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Final 1:Total number of multi-swipe transactions is 254.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "254"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "swipes.transactionAmount.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Final 2:Total dollar value of multi-swipe transactions is $38,261.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "38261.68"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "swipes.transactionAmount.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Remove Duplicates (Multiple Swipes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I captured 254 multi-swipes in the original data and removed it.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "processed_data=credit_card_no_duplicate[~(credit_card_no_duplicate.transactionKey.isin(swipes.transactionKey))]\n",
    "processed_data=processed_data[~(processed_data.transactionType=='REVERSAL')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(730746, 6)"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "processed_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st BBQ</td>\n",
       "      <td>200.12</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541917</td>\n",
       "      <td>2016-04-08 19:56:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Pub</td>\n",
       "      <td>95.11</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541962</td>\n",
       "      <td>2016-11-09 07:26:06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Sandwitch Bar #758805</td>\n",
       "      <td>5.26</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541920</td>\n",
       "      <td>2016-05-06 02:54:01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId               merchantName  transactionAmount transactionType  \\\n",
       "0   100088067                    1st BBQ             200.12        PURCHASE   \n",
       "1   100088067                    1st Pub              95.11        PURCHASE   \n",
       "2   100088067  1st Sandwitch Bar #758805               5.26        PURCHASE   \n",
       "\n",
       "  transactionKey           Timestamp  \n",
       "0        T541917 2016-04-08 19:56:36  \n",
       "1        T541962 2016-11-09 07:26:06  \n",
       "2        T541920 2016-05-06 02:54:01  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "processed_data.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "------"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Save the processed data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I finalized the data processing step and saved the clean data as '.csv' format to not run all processing code again.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "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>customerId_x</th>\n",
       "      <th>merchantName_x</th>\n",
       "      <th>transactionAmount_x</th>\n",
       "      <th>transactionType_x</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>accountNumber</th>\n",
       "      <th>customerId_y</th>\n",
       "      <th>creditLimit</th>\n",
       "      <th>availableMoney</th>\n",
       "      <th>transactionDateTime</th>\n",
       "      <th>transactionAmount_y</th>\n",
       "      <th>merchantName_y</th>\n",
       "      <th>acqCountry</th>\n",
       "      <th>merchantCountryCode</th>\n",
       "      <th>posEntryMode</th>\n",
       "      <th>posConditionCode</th>\n",
       "      <th>merchantCategoryCode</th>\n",
       "      <th>currentExpDate</th>\n",
       "      <th>accountOpenDate</th>\n",
       "      <th>dateOfLastAddressChange</th>\n",
       "      <th>cardCVV</th>\n",
       "      <th>enteredCVV</th>\n",
       "      <th>cardLast4Digits</th>\n",
       "      <th>transactionType_y</th>\n",
       "      <th>currentBalance</th>\n",
       "      <th>cardPresent</th>\n",
       "      <th>expirationDateKeyInMatch</th>\n",
       "      <th>isFraud</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st BBQ</td>\n",
       "      <td>200.12</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541917</td>\n",
       "      <td>2016-04-08 19:56:36</td>\n",
       "      <td>100088067</td>\n",
       "      <td>100088067</td>\n",
       "      <td>50000</td>\n",
       "      <td>49953.81</td>\n",
       "      <td>2016-04-08T19:56:36</td>\n",
       "      <td>200.12</td>\n",
       "      <td>1st BBQ</td>\n",
       "      <td>US</td>\n",
       "      <td>US</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>food</td>\n",
       "      <td>07/2029</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>546</td>\n",
       "      <td>546</td>\n",
       "      <td>4615</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>46.19</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-04-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Pub</td>\n",
       "      <td>95.11</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541962</td>\n",
       "      <td>2016-11-09 07:26:06</td>\n",
       "      <td>100088067</td>\n",
       "      <td>100088067</td>\n",
       "      <td>50000</td>\n",
       "      <td>49929.31</td>\n",
       "      <td>2016-11-09T07:26:06</td>\n",
       "      <td>95.11</td>\n",
       "      <td>1st Pub</td>\n",
       "      <td>US</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>food</td>\n",
       "      <td>04/2021</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>402</td>\n",
       "      <td>402</td>\n",
       "      <td>2077</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>70.69</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-11-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Sandwitch Bar #758805</td>\n",
       "      <td>5.26</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541920</td>\n",
       "      <td>2016-05-06 02:54:01</td>\n",
       "      <td>100088067</td>\n",
       "      <td>100088067</td>\n",
       "      <td>50000</td>\n",
       "      <td>49996.04</td>\n",
       "      <td>2016-05-06T02:54:01</td>\n",
       "      <td>5.26</td>\n",
       "      <td>1st Sandwitch Bar #758805</td>\n",
       "      <td>US</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>food</td>\n",
       "      <td>07/2033</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>2014-01-09</td>\n",
       "      <td>546</td>\n",
       "      <td>546</td>\n",
       "      <td>4615</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>3.96</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-05-06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId_x             merchantName_x  transactionAmount_x  \\\n",
       "0     100088067                    1st BBQ               200.12   \n",
       "1     100088067                    1st Pub                95.11   \n",
       "2     100088067  1st Sandwitch Bar #758805                 5.26   \n",
       "\n",
       "  transactionType_x transactionKey           Timestamp  accountNumber  \\\n",
       "0          PURCHASE        T541917 2016-04-08 19:56:36      100088067   \n",
       "1          PURCHASE        T541962 2016-11-09 07:26:06      100088067   \n",
       "2          PURCHASE        T541920 2016-05-06 02:54:01      100088067   \n",
       "\n",
       "   customerId_y  creditLimit  availableMoney  transactionDateTime  \\\n",
       "0     100088067        50000        49953.81  2016-04-08T19:56:36   \n",
       "1     100088067        50000        49929.31  2016-11-09T07:26:06   \n",
       "2     100088067        50000        49996.04  2016-05-06T02:54:01   \n",
       "\n",
       "   transactionAmount_y             merchantName_y acqCountry  \\\n",
       "0               200.12                    1st BBQ         US   \n",
       "1                95.11                    1st Pub         US   \n",
       "2                 5.26  1st Sandwitch Bar #758805         US   \n",
       "\n",
       "  merchantCountryCode  posEntryMode  posConditionCode merchantCategoryCode  \\\n",
       "0                  US           9.0               1.0                 food   \n",
       "1                  US           2.0               1.0                 food   \n",
       "2                  US           2.0               1.0                 food   \n",
       "\n",
       "  currentExpDate accountOpenDate dateOfLastAddressChange  cardCVV  enteredCVV  \\\n",
       "0        07/2029      2014-01-09              2014-01-09      546         546   \n",
       "1        04/2021      2014-01-09              2014-01-09      402         402   \n",
       "2        07/2033      2014-01-09              2014-01-09      546         546   \n",
       "\n",
       "   cardLast4Digits transactionType_y  currentBalance  cardPresent  \\\n",
       "0             4615          PURCHASE           46.19         True   \n",
       "1             2077          PURCHASE           70.69         True   \n",
       "2             4615          PURCHASE            3.96         True   \n",
       "\n",
       "   expirationDateKeyInMatch  isFraud        Date  \n",
       "0                     False    False  2016-04-08  \n",
       "1                     False    False  2016-11-09  \n",
       "2                     False    False  2016-05-06  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "processed=processed_data.merge(transactions,on='transactionKey',how='left')\n",
    "processed.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x1a77c3bd50>"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAA0sAAAFNCAYAAADRgvRCAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nOzdeVyU5f7/8fcA7rgHYkZaWdTRFBS3JHAJ0BQXTplLetQ6mgetrDRB0iwNM796NDWrU5ZrkqmIKW4ZhZimmeb5WfktcQ/HBRUVBOb+/eHXSXLEcRlultfz8fAh1zX3zP2em4GZD9d9XbfFMAxDAAAAAIB83MwOAAAAAABFEcUSAAAAADhAsQQAAAAADlAsAQAAAIADFEsAAAAA4ADFEgAAAAA4QLEEwCUOHTokPz8/+7+GDRuqQ4cO+vzzz+3bvPvuu/Lz89OePXsKfKy0tDQNGTJEP//88zW38fPz07/+9S9JUt++fRUYGHhTuefPn68xY8bkew4TJky4qce6GYZh6MUXX1RgYKAeffRRZWRk2G+7fLwc/Vu6dGmhZbwZhXlcly9fLj8/P7Vp00Z5eXku2cfNunjxombOnKlp06bd8H3PnDmjNm3aaOPGjdqyZYvD18HlY+wq69evd+nrzc/PTwEBATp06JC9r2vXrmrXrt1t39e1fp6+/PLL276vK02YMEF+fn46dOiQJk6caP+9BaBo8jA7AICSLSwsTD179lRmZqYWLFig2NhY1apVS8HBwerWrZuaNm2qu+++u8DHSExM1FdffaXnn3/+mtvMmTNHNWrUuOW8b775ptq3by9J8vLy0pw5c1S7du1bflxnpaena/Xq1WrRooWeeeYZVatWzX7b5eMlSVFRUfL19dWoUaMkSfXr1y+0jDejMI/rggULJElHjx7VunXr1KFDB5fs52YcO3ZM06dPV79+/W74vu+++67KlSunNm3aaOvWrZKkXr16KSwszL5NrVq1bltWs5w/f15jx47VRx99VCj7GzduXL7fQQ888ECh7FeS+vfvr7Zt22r9+vV67LHHCm2/AJzHyBIAl/Lx8VHr1q0VHh6uWbNmydPTU++++66kSyMAAwYM0IEDB3T27FkNGzZMzZo1U+PGjdWzZ0/t3btXW7Zs0YwZMyRdKha2bNmivn37KjQ0VP369VNAQID27dunAQMGaPr06fb92mw2xcXFqXnz5nr88ceVmpoqSVq6dKn8/Py0fv36q9p9+/aVJG3YsEF9+/aV1WrVgAEDtHDhQknS6dOnFR0drebNm6t58+Z6/vnndfTo0XyPM3v2bD322GMKDAzUa6+9JkfX/c7OzlZcXJxat26tJk2aaODAgdq7d68kKSQkRJK0ZcsW/ec//8l3P19fXz3yyCN65JFH5O7urqpVq9rb3t7eGjVqlAICAvTCCy8oICBAmzdv1ueff6527dqpYcOGCgoK0qxZs+yP7+fnpylTpigiIkIBAQEaNmyYsrKyJElTp05VUFCQfURwzZo1kqQLFy7olVdeUbNmzdSwYUN17dpVO3fulCSdPXtW0dHRatWqlZo2baoXX3xRZ8+eLbTjKkm7d+/Wrl279PTTT6t8+fKaN2+e/bbLI1pjx45Vz5491axZM40cOVKLFi1S27Zt1bx5c82dO9e+/bp169SlSxc1atRIHTt2zDcq2q5dO3Xt2tVh+/KIxSeffKJHH31ULVq0sL/mLxdJc+fO1ahRo3T06FENGDBATZo0UUBAgJ555hmlp6df9bzOnTunxYsXKyIiQhaLJd9romnTpvZ/9913n/15DhkyREFBQRowYIBOnDihQYMGqUmTJmrUqJF69eqltLQ0SVePxF7ZzsnJ0ZtvvqkWLVooPDxcmzZtcnjct2/fLj8/P/vrS5JCQ0MVERGhnJwcvfbaa2rZsqUaNWqkbt266fvvv3f4OJLk7u6ulJQULV++3OHt1/u+DBw4UC+//LL8/f3VqVMn7dix45r7kqS//e1v9uMXGBioO+64w/66e+WVV9S0aVPNmDFDP/74oyIjI/Xwww8rMDBQL730kv3n5cqR7b+209PT7ce+T58++UbNfHx8FBgYeNXPOoCig2IJQKHx9PRU/fr17YXBlZYtW6a1a9fq5Zdf1oQJE3Tx4kWtXLlSDz74oP1D6Lhx4/Tggw9Kkg4cOKAGDRrozTff1D333HPV4507d04XLlzQuHHjlJWVZf/gXpBXX31VkhQQEGD/+q+3JyYm6l//+pdiYmK0bds2DRo0SBcvXrRvs2LFCg0fPlwPPPCA4uPjHX5QmzRpkj755BM99dRTGj9+vA4fPqwBAwYoIyND77zzjqRLI3KOMlzP+fPnVbZsWb3++ut6+OGHtXDhQjVs2FDvvvuu6tatq+nTp+c7DkuXLtWzzz6r1q1ba+3atVq3bp1+/vlnzZ49W6Ghofr3v/+tu+++W1988YXy8vL0zTffaPv27YqKitKkSZP022+/2T/oTZgwQStWrNDQoUM1bNgwJSUladasWYV2XKVLo0oWi0X9+/dXeHi4tm3bdtVpnitXrtRTTz2lgIAAJSQkaOHChRo5cqQ8PT01adIk5ebmateuXXrhhRdUtWpVTZkyRY0aNVJsbKwSExOd/l6sWbNG0dHRqlGjhmbMmKH09HT78w8LC9Ozzz6rTz75RN9//70mTJigmJgYHTt2TOvWrbvqsTZv3qzs7Gw1adIkX/+kSZPUqFEj+7/s7Gz7bdu2bdOoUaMUFRWl1atX63//9381evRoRUdH64cfftBnn3123eewaNEizZ8/X6GhoXrppZfsf3T4q6ZNm+qhhx7S0qVLZRiGtm7dqgMHDqhXr1765ptvFB8fr6efflqTJk2Sp6dngafxBQUF6YEHHlBcXJxOnjyZ7zZnvi+bNm3SnXfeqVdeeUW//fabvVDNysrSuXPndO7cuXyvrSeffNJ+/J5++ul8+/v99981fvx4RURE2Iv7y39g+PLLL695PK705ptv6ttvv9Xzzz+vbt26afPmzflub9KkiX788UedOnXquo8FoPBxGh6AQudoHkmTJk1UoUIFvffee2revLn69Omjjh07qmLFivL19ZUkNW7cWFWrVpUkubm5afjw4SpbtqzDfZQpU0ZjxoyRh4eH/vjjD02cONHh3KgrRygaNmwoSapRo4YaNmyY7y/AZ86c0caNG9W5c2f1799fkmS1WjV58uR8jzto0CB16tRJmZmZ2r59+1Uf9iQpISFBAQEB+U4rHD58uFJTU+0fhn18fOx5blRUVJTq1asnSfrPf/6jr776Shs3btTRo0dlGIbOnDlj37Znz57q2rWratasqXXr1unkyZO66667dPfdd2vFihU6evSoWrZsqccff1zu7u4KDw9X7dq19d133ykpKUkWi8U+r+rrr79W06ZN1adPH0lSZGSkqlSpYt+Xq49rRkaGvvzyS/n7+6tSpUoKCwtTQkKC5s6dq7i4OPt2HTp0UPfu3ZWbm6vk5GQ988wz6tixo7766iutWLFCFy5cUGJiovLy8vTGG2/onnvuUZs2bbRu3TolJCQoIiLiqn07Gul66aWX1KxZM/388896//33derUKTVo0EDSpe9v/fr11aJFC82bN09Tp05V8+bNNWTIEIWGhl71WAcOHJB09Wl2//jHP/T444/b21f+PAQHB6tz586SpMDAQNWvX1/btm3T7t27ZbFYdPr06av289fnsnXrVpUpU0Zjx45VmTJldObMGcXGxjq8X58+fRQbG6utW7dq6dKlqlSpkrp06aIzZ86oRo0aWrBggZo3b66OHTvmy/xXHh4emjBhgp566qmr5rU5832566679PLLL0uSZs+ebX+t/POf/7Sfvjh06FD7Y7799tv2n5dKlSrl21/Pnj3VsWNHSZeKno0bN+qHH37Qrl27JMnhMfzra2Hr1q0KDAy0v763bt2qFStW2G+vVauWDMPQwYMHVb169WseFwDmYGQJQKG5ePGifv/9d91///1X3dawYUOtXLlSw4YNU7ly5TRp0iQ9+eST13ysChUqXLNQki6dhne5KLt82pKHh4f969zcXEmXTitzxpWnPl12+UPRlbd5enpKulSsXYubm2t/9V6e53T06FF16tRJGzZsUPv27RUeHi4p/4e5v+Y1DEOenp5atmyZxo8fr3r16ik+Pl4dOnTQoUOHtHDhQvXo0UOS9Oyzz8rb2zvfvq8shH///XcdPny4wKy387guWbJE2dnZ2rFjh1q1aqWoqChJ0pdffpmvuKpYsaKkS6d7XfnYl9uGYVzze3Rlpiuf6/nz56/a1tGx/at27dopMTFR/fv3V05OjmJiYhxO+L98X5vNlq//zjvvlL+/v/3flfmu/OA9efJkPffcc6pevbpefPFFubu758tzrefi5uYmm83m8HvyV507d1bVqlW1aNEirV27Vl26dJGnp6fuvPNOJSYmKjo6WjVr1tQHH3xgL3yvpVGjRurXr59WrlxpP13wch5HHL1WpEvF4+XsY8eO1eLFi7V48eJ8v1v8/Pzsx++vv5uunDPYt29fTZs2TQ0bNtSAAQMkyeEx/OtrwWKx5Du+fz2Gl9uu/r0A4ObwkwnApf744w+lpqZq48aNevnll3XmzBn7B40rzZgxQ4899piOHDmidu3aycfHR0ePHlVeXp79w+a3335rn89xvQ8WeXl5io2NVVJSkubPn6+aNWvKz8/PvgjEunXrtGXLlnzzHaRLH2wPHTp01akylStX1iOPPKI1a9bo008/VUJCgj799FP5+fnpoYceuqFjEhYWph07dmjGjBlavXq1pk2bJm9vb7Vu3fqGHudaLh+b//73vzp16pTKli2rM2fOaMOGDZKu/sD9V999951atGih1atXq3nz5mrQoIEuXLigkydPKiUlRdKl47Fp0yYdOXLE/kGwbdu2+uGHHzR37lwtWLBATz/9tGbOnCnJ9cfVZrPps88+U/Xq1TVnzhz7v0GDBik7O1vx8fFOP5Z0ab6NxWLR66+/rvXr1+u1117TuXPn1L17d0lSzZo1dfDgQSUnJ+vDDz/Mt2phQS6/ln/77Tft2LFDMTEx6tKli3JycvTYY4+patWqDgvMe++9V9KlBSKcdeWH8m+//VbSpeO9dOlS5ebm2r9vNWvW1Pnz57VixQp98cUX+uWXX+z3CwoKUl5ensaOHaukpCR9+OGH19xfhQoVFBkZqdWrV+v8+fPq1auXpEunej766KPavn27Hn30Ud1zzz06derUdf9Q8eKLL+quu+6yzwuSrv99KUj9+vXtRZGPj891t5f+/Fk6c+aMdu7caf+Dy+X5VFcew927d2vLli355k5Kl47hjz/+qP/85z+Kj4+3z/+7LD09Xe7u7vbRLQBFC8USAJdau3atBgwYoGHDhum3337TuHHjHJ7G9Oyzz6pHjx5atGiRXnzxRVksFk2fPl3u7u5q166d6tSpo48++ki//vqrU/t94IEH5OXlpejoaJUvX17Tp09XpUqV9Mgjj6hz5876+uuv9frrr9tPsbnsqaee0sGDBx1OuP6f//kfderUSTNnztSECRPUtGlTffDBBwWOdjgyevRo9e3bV4sWLdLo0aN111136ZNPPrGfYni7BAUFqX379kpOTtakSZPsxcf1jmHLli01YsQI/fe//9Xzzz+vHTt2aNSoUWrUqJGeffZZ1atXT3FxcVq3bp0efvhh/f7778rJyVF0dLS6deumWbNmaerUqXrssccUHR0tyfXH9ZtvvtHBgwfVvXt3+6IXjzzyiIYMGaIqVapo0aJFN7SMeGBgoKZMmaKTJ09q+PDh+vHHH/XWW2/ZTx974YUXVLt2bb344os6evSoAgICnHrcWrVqqX379tqxY4eWLl2qV155RaGhoZoxY4ZGjhyp2rVra9KkSVfdr0WLFqpYsaK2b9/u9HO40rBhw1StWjWNGTNGe/bs0T333GN/HQwaNEgPPfSQXnvtNaWkpKht27b2+z3xxBN65pln9NVXX2nq1Knq1KlTgfvp3bu3LBaLmjRpIj8/P0mXlv7+5z//aV/R8o8//tDbb78tLy+vAh+rQoUKGj9+fL6+631fXKVKlSqKiorSH3/8oTFjxqhatWoqU6aM/RiOHDlS5cqV09ChQ1W5cmXdeeed9vuOHj1abdu21axZs7R06dJ8C4NI0s6dOxUYGJhvRAxA0WExrrWkEAAAKDImTpyo1NTUfPNdULydPHlSwcHBmj59ukuuJQXg1jGyBABAMRAVFaUzZ85cdSojiq958+YpKCiIQgkowhhZAgAAAAAHGFkCAAAAAAcolgAAAADAAYolAAAAAHCAYgkAAAAAHPAwO4CrnTp1TjYba1gAAAAAyM/NzaLq1Std8/YSXyzZbAbFEgAAAIAbxml4AAAAAOAAxRIAAAAAOECxBAAAAAAOUCwBAAAAgAMUSwAAAADgAMUSUApkZJzSxIlv6PTpDLOjAAAAFBsUS0ApkJi4THv3/qIVK5aaHQUAAKDYcGmxNGPGDHXq1EmdOnXSpEmTJEmpqamKiIhQWFiYpk6dat92z549ioyMVHh4uEaPHq3c3FxJ0pEjR9SnTx916NBBQ4YM0blz51wZGShxMjJOKSUlWYZhKCXlG0aXAAAAnOSyYik1NVUpKSlatmyZli9frv/+979auXKlYmJiNGvWLK1atUq7d+9WcnKyJGnEiBEaM2aM1qxZI8MwFB8fL0kaN26cevfuraSkJDVs2FCzZs1yVWSgREpMXGa/MLPNZmN0CQAAwEkuK5a8vLw0atQolS1bVmXKlNF9992ntLQ01a1bV76+vvLw8FBERISSkpJ0+PBhZWVlyd/fX5IUGRmppKQk5eTk6Pvvv1d4eHi+fgDO27x5k/LyLo3U5uXlavPmTSYnAgAAKB48XPXA999/v/3rtLQ0rV69Wk8//bS8vLzs/d7e3kpPT9exY8fy9Xt5eSk9PV2nTp2Sp6enPDw88vXfiJo1PW/xmQDFW9u2bbRu3Trl5ubKw8ND7dq1lZdXZbNjAQAAFHkuK5Yu27t3rwYPHqyRI0fK3d1daWlp9tsMw5DFYpHNZpPFYrmq//L/V/pr+3pOnMi0n4IElEahoZ21fv16SZLF4qbQ0M6yWs+anAoAAMB8bm6WAgdXXLrAw/bt29W/f3+9/PLL6t69u3x8fGS1Wu23W61WeXt7X9V//PhxeXt7q0aNGjp79qzy8vLybQ/AedWqVVdQUIgsFouCgoJVtWo1syMBAAAUCy4rlo4ePaqoqChNnjxZnTp1kiQ1btxY+/bt0/79+5WXl6eVK1cqODhYderUUbly5bR9+3ZJUkJCgoKDg1WmTBkFBgZq1apVkqTly5crODjYVZGBEisiorvuv99PXbpEmh0FAACg2LAYhuGSc9TGjx+vL774Qnfffbe9r2fPnqpXr57i4uKUnZ2tkJAQRUdHy2Kx6Oeff1ZsbKwyMzPVoEEDxcXFqWzZsjp8+LBGjRqlEydOqHbt2poyZYqqVq3qdA5OwwMAAADgyPVOw3NZsVRUUCwBAAAAcMTUOUsAAAAAUFxRLAEAAACAAxRLQCmQkXFKEye+odOnM8yOAgAAUGxQLAGlQGLiMu3d+4tWrFhqdhQAAIBig2IJKOEyMk4pJSVZhmEoJeUbRpcAAACcRLEElHCJicvsK0LabDZGlwAAAJxEsQSUcJs3b1JeXq4kKS8vV5s3bzI5EQAAQPFAsQSUcK1atZbFYpEkWSwWtWrV2uREAAAAxQPFElDChYS00+VrTxuGoTZt2pucCAAAoHigWAJKuOTkryRZ/q9l0ddfbzAzDgAAQLFBsQSUcJfmKBn/1zKYswQAAOAkiiWghGvVqrXc3T0kSe7uHsxZAgAAcBLFElDCRUR0l5vbpdPw3Nzc1KVLpMmJAAAAigeKJaCEq1atuoKCQmSxWBQUFKyqVauZHQkAAKBY8DA7AADXi4jorsOHDzGqBAAAcAMsxuU1hUuoEycyZbOV6KcIAAAA4Ca4uVlUs6bntW8vxCwAAAAAUGxQLAGlQEbGKU2c+IZOn84wOwoAAECxQbEElAJLlnymX3/9WUuWfGZ2FAAAgGKDYgko4TIyTtkvRLt5cwqjSwAAAE6iWAJKuCVLPpNh2CRJNpuN0SUAAAAnUSwBJdyWLan52t99t8mkJAAAAMWLy6+zlJmZqZ49e2r27Nn67bffNGXKFPtt6enpaty4sd5//33NmDFDX3zxhapUqSJJ6tGjh/r06aM9e/Zo9OjROnfunAIDAzVu3Dh5eHB5KMBZeXl5BbYBAADgmEurjp07dyo2NlZpaWmSpJCQEIWEhEiSrFarevXqpejoaEnS7t27NWXKFAUEBOR7jBEjRmj8+PHy9/dXTEyM4uPj1bt3b1fGBkoUNzc32Wy2fG0AAABcn0s/NcXHx2vs2LHy9va+6rZJkyapZ8+eqlevnqRLxdL777+viIgIvfHGG8rOztbhw4eVlZUlf39/SVJkZKSSkpJcGRkocVq2bF1gGwAAAI65dGRpwoQJDvvT0tK0detW++3nzp3TQw89pBEjRqhu3boaNWqUZs2apTZt2sjLy8t+Py8vL6Wnp99QhoKuyAuUBoMHP6vNm1NkGIYsFouee+6fql69stmxAAAAijxTJv8sXrxYvXv3VtmyZSVJlSpV0ocffmi/feDAgYqJiVFwcLAsFou9//KHvRtx4kSmbDbj9gQHiqUyevhhf+3atUONGwcoN9dDVutZs0MBAACYzs3NUuDgiimTFzZs2KDHH3/c3j5y5IiWLFlibxuGIQ8PD/n4+Mhqtdr7jx8/7vCUPgAFO3r0sCTp8OFDJicBAAAoPgq9WDp58qSysrLk6+tr7ytfvrzeeecdHTx4UIZhaMGCBQoNDVWdOnVUrlw5bd++XZKUkJCg4ODgwo4MFGsHDqTJaj0mSbJaj+ngwf0mJwIAACgeCr1YOnTokHx8fPL11ahRQ2+88YaGDBmiDh06yDAMDRgwQJI0efJkxcXFqUOHDjp//rz69etX2JGBYu29997N1541a7pJSQAAAIoXi2EYJXpCD3OWUNoNHHj1Uvsff7zQhCQAAABFS5GcswQAAAAARR3FEgAAAAA4QLEElHC1atUusA0AAADHKJaAEm7IkGH52v/61/MmJQEAACheKJaAEu7uu+vZR5Nq1aotX9+6JicCAAAoHiiWgFJgyJBhqlChAqNKAAAAN4ClwwEAAACUSiwdDgAAgHwyMk5p4sQ3dPp0htlRgCKNYgkAAKCUSUxcpr17f9GKFUvNjgIUaRRLAAAApUhGximlpCTLMAylpHzD6BJQAIolAACAUiQxcZl9PrfNZmN0CSgAxRIAAEApsnnzJuXl5UqS8vJytXnzJpMTAUUXxRIAAEAp0qpVa7m7e0iS3N091KpVa5MTAUUXxRJQChw4kKaoqGd08OB+s6MAAEwWEdFdFsulry0Wi7p0iTQ3EFCEUSwBpcAHH8zUhQsX9P77M8yOAgAwWbVq1eXtXUuS5O3trapVq5mcCCi6KJaAEu7AgTQdOXJYknTkyGFGlwCglMvIOKVjx45JkqzWY6yGBxSAYgko4T74YGa+NqNLAFC6JSYuk2FcXg3PYDU8oAAUS0AJd3lU6VptAEDpwmp4gPMoloASzs3NvcA2AKB0YTU8wHkUS0AJZ7PlFdgGAJQuERHd5eZ2aTk8Nzc3VsMDCkCxBAAAUIpUq1ZdQUEhslgsCgoKZjU8oAAuL5YyMzPVuXNnHTp0SJIUHR2tsLAwde3aVV27dtW6deskSampqYqIiFBYWJimTp1qv/+ePXsUGRmp8PBwjR49Wrm5ua6ODAAAUKKFhLRT+fLl1aZNe7OjAEWaS4ulnTt3qlevXkpLS7P37d69W/Pnz1dCQoISEhIUGhqqrKwsxcTEaNasWVq1apV2796t5ORkSdKIESM0ZswYrVmzRoZhKD4+3pWRAQAASrzk5K+UlZWlr7/eYHYUoEhzabEUHx+vsWPHytvbW5J04cIFHTlyRDExMYqIiND06dNls9m0a9cu1a1bV76+vvLw8FBERISSkpJ0+PBhZWVlyd/fX5IUGRmppKQkV0YGAAAo0TIyTiklJVmGYSgl5RuuswQUwKXF0oQJExQYGGhvHz9+XC1bttRbb72l+Ph4bdu2TUuWLNGxY8fk5eVl387b21vp6elX9Xt5eSk9Pd2VkQEAAEq0xMRlstkuX2fJxnWWgAJ4FObOfH19NXPmnxfI7Nu3r5YvX67w8HBZLBZ7v2EYslgsstlsDvtvRM2anrceHChhvLwqmx0BAGCS777Lf52l777bpJdeesHkVEDRVKjF0i+//KK0tDSFh4dLulT8eHh4yMfHR1ar1b6d1WqVt7f3Vf3Hjx+3n9LnrBMnMu1/PQFwidV61uwIAACTtGzZWt9887Xy8nLl7u6hli1b876AUsvNzVLg4EqhLh1uGIbeeustnT59Wjk5OVq8eLFCQ0PVuHFj7du3T/v371deXp5Wrlyp4OBg1alTR+XKldP27dslSQkJCQoODi7MyAAAACVKRER3XT5Rx2IR11kCClCoI0sPPvigBg0apF69eik3N1dhYWHq3LmzJGnixIkaNmyYsrOzFRISog4dOkiSJk+erNjYWGVmZqpBgwbq169fYUYGAAAoUapVq66yZcspNzdXZcuW4zpLQAEshmGU6HPUOA0Ppd3Agb2v6vv444UmJAEAFAUHDqTp9ddj7O1x4+Lk61vXxESAeYrUaXgAAAAw1wcfzMzXfv/9GSYlAYo+p4ql7Ozsq/pYwhsAAKD4OXLkcIFtAH9yqlh68skntX//fnt7/fr1ioxkMiAAAEBxU6uWT4FtAH9yaoGHvn37qk+fPnr11Vf1448/KiUlRTNmMGQLAABQ3Pj61lV6+h/29t13M18JuBaniqUnn3xS3t7eeu6553THHXdoxYoVql69uquzAQAA4DbbvXtXvvZPP+26xpYAnDoNb9GiRRo1apRGjx6ttm3bqlevXvrpp59cnQ3AbVCmTJkC2wCA0qVVq9Zyd3eXJLm7u6tVq9YmJwKKLqdGlhYsWKBPP/1UDzzwgCRp7dq1eu6557Rp0+/unv0AACAASURBVCaXhgNw63JycgpsAwBKl4iI7vr222RJeXJzc+OitEABnBpZ+uKLL+yFkiSFhYXpiy++kHTpYrIAAAAoHqpVqy5vb29JkpeXNxelBQrgVLFUrly5q/p8fC6tnLJly5bbmwgAAAAuk5FxSseOXboEzLFjx3T6dIbJiYCi65YvSmsYxu3IAQAAgEKQmLhMlz++GYahFSuWmhsIKMJuuViyWCy3IwcAAAAKwebNm5SXlytJysvL1ebNzEEHruWWiyUAAAAUH5dWv7v8x24Lq+EBBaBYAgAAKEVCQtpJujyNwlCbNu3NjAMUacxZAgAAKEWSk7+yT6OwWCz6+usNJicCii6niqV///vfV/WNHz9ekhQdHX17EwEAAMBlNm/eZP9jt2EYzFkCClDgRWmnT5+uM2fOaNWqVcrMzLT35+TkKCUlRbGxsWrRooXLQwIAAOD2aNIkUKmp39rbTZs2MzENULQVWCw1btxYP/30k9zc3FSt2p8XLHN3d9fkyZNdHg4AAACuxZQK4NoKLJZCQkIUEhKi4OBgNWrUqLAyAQAAwEV++GFbgW0AfyqwWLrM09NT0dHRysjIyPfXh9mzZ7ssGAAAAG6/Vq1aKzl5o2y2PLm5ubN0OFAAp4qlUaNGqVGjRmrWrBkXoQUAACjGIiK6KyUlWTZbntzd3dWlS6TZkYAiy6li6cKFC4qNjXV1FgAAALhYtWrVFRQUoq+/3qCgoGBVrVrt+ncCSimnlg6vW7eujh075uosAAAAKAQhIe1Uvnx5LkgLXIdTI0s2m02dO3dWgwYNVK5cOXu/M3OWMjMz1bNnT82ePVt33XWXFi9erHnz5slisahhw4YaN26cypYtqxkzZuiLL75QlSpVJEk9evRQnz59tGfPHo0ePVrnzp1TYGCgxo0bJw8Pp2IDAADAgeTkr5SVlaWvv96gvn0Hmh0HKLKcqjpCQ0MVGhp6ww++c+dOxcbGKi0tTZK0b98+ffTRR1q6dKkqVaqkUaNGaeHCherfv792796tKVOmKCAgIN9jjBgxQuPHj5e/v79iYmIUHx+v3r1733AWAAAASBkZp/Ttt1/LMAx9+22yunSJ5FQ84BqcKpa6d+9+Uw8eHx+vsWPHauTIkZKksmXLauzYsfL09JQkPfDAAzpy5Igkaffu3Xr//fd1+PBhNWvWTK+++qqOHz+urKws+fv7S5IiIyM1ffp0iiUAAICblJi4TLm5uZKk3NwcrVixlNEl4BqcKpYCAgIcroL3ww8/FHi/CRMm5GvXqVNHderUkSSdPHlSCxYsUFxcnM6dO6eHHnpII0aMUN26dTVq1CjNmjVLbdq0kZeXl/3+Xl5eSk9PdyayXc2anje0PVAaeHlVNjsCAMAkmzenXNV+6aUXTEoDFG1OFUsrV660f33x4kV9+eWXqlChwk3vND09Xc8++6z+/ve/q0WLFpKkDz/80H77wIEDFRMTo+Dg4HxFmmEYN7x0+YkTmbLZuDI1cCWr9azZEQAAJqlatbqyso7ma/O+gNLKzc1S4OCKU6vhXR4RqlOnju655x4NHTpUSUlJNxXot99+U8+ePdW9e3dFRUVJko4cOaIlS5bYtzEMQx4eHvLx8ZHVarX3Hz9+XN7e3je1XwAAAEhW67EC2wD+5FSx9Fe//fabTpw4ccP3y8zM1DPPPKMXXnhBAwf+eW5s+fLl9c477+jgwYMyDEMLFixQaGio6tSpo3Llymn79u2SpISEBAUHB99MZAAAAEiy2fIKbAP40w3PWTIMQzk5ORoxYsQN72zJkiU6fvy45syZozlz5kiS2rVrpxdeeEFvvPGGhgwZopycHDVp0kQDBgyQJE2ePFmxsbHKzMxUgwYN1K9fvxveLwAAAC6xWCwyDCNfG4BjFuPKn5ZrOHz48J93sFhUpUoV+4p2RR1zllDaDRx49eqRH3+80IQkAICi4NVXX8x36p2XVy29/fZUExMB5rnenCWnRpbq1KmjVatW6dtvv1VOTo6CgoLUrVu32xYSAAAAhePEieN/aVuvsSUAp+YsffTRR3r//ffl5+enBg0aaM6cOZo1a5arswEAAOA2s9lsBbYB/MmpkaXly5dr0aJF9lPvnnjiCfXo0UP/+te/XBoOAAAAAMzi9Gp4V85Rqly5sjw8nKqzAAAAAKBYcvo6S59++qlycnKUk5OjTz75RHfeeaerswEAAOA2++vqd6yGB1ybU8XSuHHjtH79evn7+8vf319r167VmDFjXJ0NAAAAAEzj1Ll0tWrV0rx583ThwgXZbDZVqlTJ1bkAAADgAn+9aowTV5EBSi2niqW9e/dq3rx5On36dL7+adOmuSQUAAAAXMPNzS3fCnhubk5PYQdKHaeKpRdffFFBQUHy8/NzdR4AAAC4UM2ad+S7KG3NmneYmAYo2pwqlsqXL6/o6GhXZwEAAICLHT9uLbAN4E9Ojbs2b95cycnJysvLc3UeAAAAuBBzlgDnOTWydMcdd2jw4MH2pSUNw5DFYtGePXtcGg4AAAC3l8ViyVcgsXQ4cG1OFUvx8fGKj4+Xr6+vq/MAAADAhRhZApznVLFUo0YNNWrUyNVZAAAA4GKMLAHOc6pY8vf31/PPP6+wsDCVLVvW3h8WFuayYAAAAHAFiyTjL20AjjhVLO3evVuStHjxYnufxWKhWAIAAChmDMNWYBvAn5wqlubNm3dVX3Jy8m0PAwAAAABFhVPF0mVZWVlatmyZ5s6dq+PHj+v77793VS4AAAAAMJVTxVJ6errmz5+v+Ph4nTt3ToMGDVL//v1dHA0AAAAAzFPgRWl37dqll156SWFhYdq7d6/Gjh0rb29vPf/886pSpUphZQQAAACAQldgsdSjRw95eHho/fr1mj17th5//HGWlwQAAABQKhRYLI0ePVr/7//9P0VGRuqdd97R3r17CysXAAAAAJiqwGKpb9++WrlypSZPnqzDhw+re/fuslqtWr58uXJzc6/74JmZmercubMOHTokSUpNTVVERITCwsI0depU+3Z79uxRZGSkwsPDNXr0aPtjHzlyRH369FGHDh00ZMgQnTt37laeKwAAAAA4rcBi6bIWLVro3//+tzZu3KjBgwdr2rRpeuyxxwq8z86dO9WrVy+lpaVJurSSXkxMjGbNmqVVq1Zp9+7d9uXHR4wYoTFjxmjNmjUyDEPx8fGSpHHjxql3795KSkpSw4YNNWvWrFt4qgAAAADgPKeKpcu8vLwUFRWlDRs26LXXXitw2/j4ePuCENKlxSLq1q0rX19feXh4KCIiQklJSTp8+LCysrLk7+8vSYqMjFRSUpJycnL0/fffKzw8PF8/AAAAABQGp5YO//333/Xhhx8qIyNDhmHY+9u3b3/N+0yYMCFf+9ixY/Ly8rK3vb29lZ6eflW/l5eX0tPTderUKXl6esrDwyNf/42qWdPzhu8DlHReXpXNjgAAKEJ4XwAcc6pYGjVqlBo1aqRmzZrd9Gp4Npst330Nw5DFYrlm/+X/r3Qz+z5xIlM2m3H9DYFSxGo9a3YEAEARwvsCSis3N0uBgytOFUsXLlxQbGzsLQXx8fGR1Wq1t61Wq7y9va/qP378uLy9vVWjRg2dPXtWeXl5cnd3t28PAAAAAIXBqTlLdevW1bFjx25pR40bN9a+ffu0f/9+5eXlaeXKlQoODladOnVUrlw5bd++XZKUkJCg4OBglSlTRoGBgVq1apUkafny5QoODr6lDAAAAADgLKdGlmw2mzp37qwGDRqoXLly9v7Zs2c7vaNy5cpp4sSJGjZsmLKzsxUSEqIOHTpIkiZPnqzY2FhlZmaqQYMG6tevnyRp7NixGjVqlN577z3Vrl1bU6ZMuZHnBgAAAAA3zWJcuWLDNSxbtsxhf/fu3W97oNuNOUso7QYO7H1V38cfLzQhCQCgKOB9AfjTbZmz1L17dx0+fFhbt25Vbm6umjdvrrp16962kAAAAABQ1Dg1Z+nbb7/V3//+d61fv14bNmzQE088ofXr17s6GwAAAACYxqmRpWnTpmn+/PmqX7++JGnv3r0aMWKEHnvsMZeGAwAAAACzODWylJOTYy+UJOn+++9XXl6ey0IBAAAAgNmcKpbKly+vn376yd7+6aefVKFCBZeFAgAAAACzOXUa3ogRI/Tcc8/ZF3XYt2+fpk2b5tJgAAAAAGAmp4qlwMBAffnll9q5c6dsNpv8/f1VvXp1V2cDAAAAANMUWCwlJCSoa9eumjNnTr7+tLQ0SdKAAQNcFgwAAAAAzFRgsbR//35J0q+//looYQAAAACgqCiwWHr++eclSe3bt79qmfDly5e7LhUAAAAAmKzAYumrr75Sbm6uJk2aJMMwZBiGJCk3N1fvvvuuunXrVighAQAAAKCwFVgs7dmzR999951OnDihuXPn/nknDw/179/f1dkAAAAAwDQFFktRUVGKiorSggUL1KdPH0lSXl6eDMOQh4dTC+kBAAAAQLHk1EVp77vvPnXp0kWS9PvvvyskJEQ7duxwaTAAAAAAMJNTxdKkSZMUFxcnSbr//vv1wQcf2NsAAAAAUBI5VSzl5OSoQYMG9naDBg108eJFl4UCAAAAALM5VSxVqFBB33zzjb29efNmVaxY0WWhAAAAAMBsTq3SMHr0aEVFRcnDw0MWi0UWi0Xvvvuuq7MBAAAAgGmcKpYaN26sr7/+Wr/++qvc3d11zz33qGzZsq7OBgAAAACmcapYunjxopKTk3Xu3DlJ0u7du3XgwAENHz7cpeEAAAAAwCxOFUvDhw/XwYMHZbVa9be//U07d+5U8+bNXZ0NAAAAAEzj1AIPe/bs0dKlS9W+fXvFxMRo0aJFOn36tKuzAQAAAIBpnBpZ8vb2loeHh+rVq6dff/1VHTt21NmzZ29qh59//rnmz59vbx86dEhdu3bVhQsXtH37dlWoUEGSNHToUIWGhio1NVVxcXHKzs5Wx44dOfUPAAAAQKFwqliqWLGiEhMT9eCDDyo+Pl733nuvzp8/f1M7fPLJJ/Xkk09Kkvbu3auoqCgNHTpU//jHPzR//nx5e3vbt83KylJMTIzmzZun2rVra/DgwUpOTlZISMhN7RsAAAAAnOXUaXhjxozRnj171Lp1a7m5ualv37565plnbnnnr7/+uoYPH64KFSroyJEjiomJUUREhKZPny6bzaZdu3apbt268vX1lYeHhyIiIpSUlHTL+wUAAACA63FqZKlevXoaOXKkJGnSpEmyWq2qU6fOLe04NTVVWVlZ6tixow4ePKiWLVtq7Nixqly5sgYPHqwlS5aoYsWK8vLyst/H29tb6enpN7SfmjU9byknUBJ5eVU2OwIAoAjhfQFwzKliad26dfruu+80fPhwdenSRWfPnrWfOnezPvvsMw0YMECS5Ovrq5kzZ9pv69u3r5YvX67w8HBZLBZ7v2EY+drOOHEiUzabcdM5gZLIar25OYcAgJKJ9wWUVm5ulgIHV5w6De/9999Xjx49tHbtWvn7+2vjxo1KSEi46VAXL17U999/r3bt2kmSfvnlF61Zs8Z+u2EY8vDwkI+Pj6xWq73farXmm9MEAAAAAK7iVLFkGIb8/PyUmpqq4OBgeXp6yjBufrTml19+Ub169VSxYkX747/11ls6ffq0cnJytHjxYoWGhqpx48bat2+f9u/fr7y8PK1cuVLBwcE3vV8AAAAAcJZTp+G5ublp1apVSklJ0auvvqrk5OQbPh3uSgcPHpSPj4+9/eCDD2rQoEHq1auXcnNzFRYWps6dO0uSJk6cqGHDhik7O1shISHq0KHDTe8XAAAAAJxlMZwYItq2bZtmzJihjh076qmnnlLPnj01YsQINW3atDAy3hLmLKG0Gziw91V9H3+80IQkAICigPcF4E/Xm7Pk1MhSYGCgPvnkE3v7s88+u+VgAAAAAFCUOVUs7dixQ1OmTNHp06fzzVVKTEx0WTAAAAAAMJNTxdKYMWMUGRmpv/3tb7c0VwkAAAAAiguniiUPDw/7NZEAAAAAoDRwaunw+++/X7/88ourswAAAABAkeHUyNLBgwf197//XXfeeafKlStn72fOEgAAMNOmTd8oJSXZ7BjF3ttvv2l2hGIjKChErVtz3c/Swqliafjw4a7OAQAAAABFilPFUvPmzZWRkaELFy7IMAzl5eXpwIEDrs4GAABQoNatg/kr/w1ydJ2lV199zYQkQNHnVLE0bdo0ffDBB5Ikd3d35eTkqH79+pyGBwAAUMx4e9fWsWNH7e3ateuYmAYo2pxa4CEhIUEbN25UeHi41q5dq7i4ONWvX9/V2QAAAHCbTZz4P/naEya8Y1ISoOhzqliqUaOGvL29de+99+rnn39Wt27d9Ouvv7o6GwAAAFzA3d1dEqNKwPU4VSx5eHjowIEDuvfee7Vt2zbl5uYqOzvb1dkAAADgAvXrPyA/v4cYVQKuw6li6bnnntNrr72mNm3aaN26dWrTpo1atmzp6mwAAAAAYBqnFnjIzc3Vp59+Kklavny59u/fLz8/P5cGAwAAAAAzOTWyNHXqVPvXFSpU0IMPPiiLxeKyUAAAAABgNqdGlh544AG99957CgwMVMWKFe39DRo0cFkwAAAAADCTU8XSmjVrtHPnTn3++ef2vgsXLmjz5s0uCwYAAAAAZiqwWMrIyJAk1a9fX3PnzpVhGLJYLMrJydHTTz9dKAEBAAAAwAwFFksvv/yyNm3aJIvFolatWtn73d3dFR4e7vJwAAAAAGCWAouljz76SJIUHR2tuLi4QgkEAAAAAEWBU6vhUSgBAAAAKG2cWuDhduvbt69OnjwpD49Lu3/jjTd04MABvffee8rNzdU//vEP9enTR5KUmpqquLg4ZWdnq2PHjho+fLgZkQEAAACUMoVeLBmGobS0NG3cuNFeLKWnp2v48OFaunSpypYtq549e6pFixa66667FBMTo3nz5ql27doaPHiwkpOTFRISUtixAQAAAJQyhV4s/f7775KkgQMHKiMjQz169FClSpXUsmVLVatWTZIUHh6upKQkNW/eXHXr1pWvr68kKSIiQklJSRRLAAAAAFzOqTlLt9OZM2fUqlUrzZw5U5988ok+++wzHTlyRF5eXvZtvL29lZ6ermPHjjnsBwAAAABXK/SRpYCAAAUEBNjbTzzxhOLi4jRkyBB73+XrOdlsNlkslqv6b0TNmp63HhooYby8KpsdAQBgojJl3CXxfgBcT6EXS9u2bVNOTo79uk2GYahOnTqyWq32baxWq7y9veXj4+Ow/0acOJEpm824PeGBEsJqPWt2BACAiXJy8iTxfgC4uVkKHFwp9NPwzp49q0mTJik7O1uZmZlatmyZ3nnnHW3evFknT57UhQsXtHbtWgUHB6tx48bat2+f9u/fr7y8PK1cuVLBwcGFHRkAAABAKVToI0tt27bVzp071a1bN9lsNvXu3VtNmzbV8OHD1a9fP+Xk5OiJJ55Qo0aNJEkTJ07UsGHDlJ2drZCQEHXo0KGwIwMAAAAohSyGYZToc9Q4DQ+l3cCBva/q+/jjhSYkAQAUFW+//aYk6dVXXzM5CWCuIncaHgAAAAAUBxRLAAAAAOBAoc9ZAm7Wpk3fKCUl2ewYJcLl0y9wfUFBIWrdmoVlAAAojRhZAgAAAAAHGFlCsdG6dTB/4b8JjhZ4YEIvAADA9TGyBAAAAAAOUCwBJdxflwln2XAAAADnUCwBAAAAgAPMWQJKAT+/hyQxVwkAAOBGMLIEAAAAAA5QLAEAAACAAxRLAAAAAOAAxRIAAAAAOECxBAAAAAAOUCwBAAAAgAMUSwAAAADgANdZAgCgCFi4cK4OHtxvdgyUEgcOXHqtvf32myYnQWnh61tXvXv3MzvGDaNYAgCgCDh4cL/S/vdn+Xjy1gzXqyibJCnrj/81OQlKgz8yc82OcNP4jQwAQBHh4+mhAY1qmB0DAG6rObtOmh3hpjFnCQAAAAAcoFgCAAAAAAcolgAAAADAAVPmLM2YMUOrV6+WJIWEhGjkyJGKjo7W9u3bVaFCBUnS0KFDFRoaqtTUVMXFxSk7O1sdO3bU8OHDzYgMAAAAoJQp9GIpNTVVKSkpWrZsmSwWi5599lmtW7dOu3fv1vz58+Xt7W3fNisrSzExMZo3b55q166twYMHKzk5WSEhIYUdGwAAAEApU+in4Xl5eWnUqFEqW7asypQpo/vuu09HjhzRkSNHFBMTo4iICE2fPl02m027du1S3bp15evrKw8PD0VERCgpKamwIwMAAAAohQp9ZOn++++3f52WlqbVq1drwYIF2rp1q8aOHavKlStr8ODBWrJkiSpWrCgvLy/79t7e3kpPT7+h/dWs6XnbsgPFVZky7pIkL6/KJicBcC1lyrgry+wQAOAiZcq4F8vPIaZdZ2nv3r0aPHiwRo4cqXvvvVczZ86039a3b18tX75c4eHhslgs9n7DMPK1nXHiRKZsNuO25QaKo5ycPEmS1XrW5CQAruXyzykAlEQ5OXlF8nOIm5ulwMEVU1bD2759u/r376+XX35Z3bt31y+//KI1a9bYbzcMQx4eHvLx8ZHVarX3W63WfHOaAAAAAMBVCr1YOnr0qKKiojR58mR16tRJ0qXi6K233tLp06eVk5OjxYsXKzQ0VI0bN9a+ffu0f/9+5eXlaeXKlQoODi7syAAAAABKoUI/De+jjz5Sdna2Jk6caO/r2bOnBg0apF69eik3N1dhYWHq3LmzJGnixIkaNmyYsrOzFRISog4dOhR2ZAAAAAClUKEXS7GxsYqNjXV4W58+fa7qa9WqlVasWOHqWAAAAACQj2kLPJRmCxfO1cGD+82OgVLkwIFLr7e3337T5CQoLXx966p3735mxwAA4JZQLJng4MH9+mXv/8q9fDWzo6CUsOVdWjr8fw8eNzkJSoO8rAyzIwAAcFtQLJnEvXw1Vazb3uwYAHDbnd+/wewIxdLp0xk6lZmrObtOmh0FAG6rPzJzVf108fxDmilLhwMAAABAUcfIEgAARUDVqtVU7sJxDWhUw+woAHBbzdl1UuWrFs/pJ4wsAQAAAIADFEsAAAAA4ADFEgAAAAA4QLEEAAAAAA5QLAEAAACAAxRLAAAAAOAAxRIAAAAAOECxBAAAAAAOcFFaE5w+naG8rAyd37/B7CgAcNvlZWXo9GneXm7GH5m5mrPrpNkxUApkXrRJkjzL8ndzuN4fmbmqZ3aIm8S7GQAARYCvb12zI6AUOXZgvyTpDh9ed3C9eiq+v+MolkxQtWo1Wc/kqmLd9mZHAYDb7vz+DapatZrZMYqd3r37mR0Bpcjbb78pSXr11ddMTgIUbYy9AgAAAIADFEsAAAAA4ADFEgAAAAA4wJwlk7AaHgqTLTdLkuTmUd7kJCgN8rIyJN1hdgwAAG4ZxZIJiutqICi+Dvzfqkd3+/IBFoXhDn7PAQBKBIolE7DiEQobqx4BAADcuGIxZykxMVGPP/64wsLCtGDBArPjAAAAACgFivzIUnp6uqZOnaqlS5eqbNmy6tmzp1q0aKH69eubHQ0AAABACWYxDMMwO0RBli1bpu+//15vvfWWJGnmzJkyDENDhw516v4nTmTKZivSTxFO2rTpG6WkJJsdo1iyz1m6m3kkNyooKEStWwebHQPANfDecHN4X7h5vC+ULG5uFtWs6XnN24v8yNKxY8fk5eVlb3t7e2vXrl1O37+gJ4/ipUqVCipTxt3sGMVSzZo1JInjdxOqVKkgL6/KZscAcA28N9wc3hduHu8LpUuRL5ZsNpssFou9bRhGvvb1MLJUcjz8cDM9/HAzs2OgFLJaz5odAcA18N4AM/C+UHJcb2SpyC/w4OPjI6vVam9brVZ5e3ubmAgAAABAaVDki6VHHnlEmzdv1smTJ3XhwgWtXbtWwcGcJwoAAADAtYr8aXi1atXS8OHD1a9fP+Xk5OiJJ55Qo0aNzI4FAAAAoIQr8qvh3SrmLAEAAABwpNjPWQIAAAAAM1AsAQAAAIADFEsAAAAA4ADFEgAAAAA4QLEEAAAAAA4U+aXDb5Wbm8XsCAAAAACKoOvVCiV+6XAAAAAAuBmchgcAAAAADlAsAQAAAIADFEsAAAAA4ADFEgAAAAA4QLEEAAAAAA5QLAEAAACAAxRLAAAAAOAAxRIAAAAAOECxBAAAAAAOUCwBAAAAgAMeZgcAcOMOHTqkDh066L777svXP3v2bNWuXfuq7d99911J0rBhwwolHwDAHOPGjdMPP/ygnJwcHThwwP4+0a9fP/3/9u4lpMqtj+P4NzihiEgWNDk1KCxr0I0UuoBUBuVle9m7iwkGXSZJSYSKlBkYNYgK0ZHDiCyjxKKLSQqWBYrRoKASMcIgNAlqYJSZZyBJvuxT78t7PGp9P7BhP89az7PXGi1+z/o/7FAoNMGjk6Yew5I0Rc2ePZtr165N9DAkSZPIsWPHgJGHajt37nSdkP5PluFJv5DOzk7y8vIIhUKsX7+eixcvjmkfHBykqKiIrKwssrKyuHz5MgD9/f3k5+cTDAYJhUI8fPhwIoYvSRonVVVV7Nmzh9TUVGpqasjLy6OtrQ0YCVYbNmwAXA+k/+TOkjRF9fX1kZmZOXocCATo7e0lPz+f1atX09PTQ0ZGBjt27Bjt8/jxY96/f099fT29vb2cOXOGbdu2ceLECUKhEMnJyfT19ZGbm0t9fT3R0dETMTVJ0jj4/Pkzt27dAuD27dth+7geSGMZlqQpKlwZ3tDQEPfv36e6uprOzk4GBgbGtC9YsICXL1+yZ88ekpKSKC4uBuDhw4d0d3dTWVkJwJcvX+jp6WHx4sX/zmQkSeNu6dKlP+3jeiCNZViSfiEHDx4kJiaG9evXk5qayo0bN8a0x8bGcvPmTR48eEBLSwvZ2dncvHmTr1+/cu7cOWbMmAGM7FrNmjVrIqYgSRonkZGRY46Hh4eBkUD0jeuBNJbvLEm/kAcPxPFiEgAAA3FJREFUHlBQUMDGjRu5d+8eMLLb9E1TUxNFRUWsW7eO0tJSoqKiePPmDatWraKmpgaArq4uAoEAHz9+nJA5SJLGX2xsLF1dXQDcvXt39LzrgTSWO0vSL+TAgQPk5uYSERHBokWL+PPPP3n9+vVoe1JSEo2NjaSlpREREUFGRgbx8fGUlpZSVlZGIBAA4NSpU9anS9IvbO/evZSUlHD16lWSk5NHz7seSGNNG/62BytJkiRJGmUZniRJkiSFYViSJEmSpDAMS5IkSZIUhmFJkiRJksIwLEmSJElSGIYlSdKk9+TJEwoKCv62va6ujpUrV5KZmTnm09TUNC7jeffuHfHx8eNyb0nS5OH/LEmSJr0lS5ZQWVn5wz4JCQlUV1f/SyOSJP0O3FmSJE16bW1tpKen09HRwZYtWwgGgwSDQe7cufPTa+vq6sjNzSU7O5u8vDwGBgYoLi5m+/btbNq0iWAwSHd3NwB5eXk0NDSMXvv9cWNjIykpKQSDQSoqKsZnopKkScWdJUnSlFFVVcWuXbtIS0vj+fPn1NbWsmnTJgA6OjrIzMwc7bts2TLKy8sB6Orqorm5mejoaBoaGoiJiaG2thaAsrIyLly4wNGjR//2d/v7+zl8+DCXLl0iLi7OHSxJ+k0YliRJU0ZKSgrl5eU0NzezZs0aDh06NNr2ozK8+Ph4oqOjAdi8eTNz587l/PnzvHr1ivb2dlasWPHD33306BELFy4kLi4OgO3bt3P27Nl/aFaSpMnKMjxJ0pSRk5PD9evXWbt2La2trWRkZPDp06efXhcVFTX6vaamhiNHjhAZGUkgECA9PZ3h4eHR9u+/Dw4Ohj3/xx8+a5Sk34FhSZI0ZeTk5PDs2TOCwSDHjx/nw4cPvH379n+6R2trK9nZ2WzdupV58+bR3NzM0NAQADNnzuTp06fASOneixcvAEhMTKSrq4vnz58DI+9BSZJ+fT4akyRNGYWFhZw8eZKKigqmTZvG/v37mTNnDu3t7f/1PXbv3k1ZWRlXrlwBYPny5XR2dgKwb98+SkpKaGlpYf78+SQkJAAjIer06dMUFhYyffp0EhMT//nJSZImnWnD39cVSJIkSZIAy/AkSZIkKSzDkiRJkiSFYViSJEmSpDAMS5IkSZIUhmFJkiRJksIwLEmSJElSGIYlSZIkSQrjL4oCq1tREKRtAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 1008x360 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.figure(figsize=(14,5))\n",
    "plt.title('Distribution of Transaction Amounts (Fraud vs Non-Fraud)',weight='bold')\n",
    "sns.set()\n",
    "sns.boxplot(x='isFraud', y='transactionAmount_x', data=processed)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(730174, 30)"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "processed.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_data=processed[['customerId_x','merchantName_x','transactionAmount_x','transactionType_x','transactionKey','Timestamp','creditLimit','availableMoney','merchantCountryCode','posEntryMode','posConditionCode','merchantCategoryCode','currentBalance','cardPresent','expirationDateKeyInMatch','isFraud']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_data.columns=['customerId','merchantName','transactionAmount','transactionType','transactionKey','Timestamp','creditLimit','availableMoney','merchantCountryCode','posEntryMode','posConditionCode','merchantCategoryCode','currentBalance','cardPresent','expirationDateKeyInMatch','isFraud']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_data.to_csv('clean_data.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "#q=pd.read_csv('./clean_data.csv')\n",
    "#q.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>customerId</th>\n",
       "      <th>merchantName</th>\n",
       "      <th>transactionAmount</th>\n",
       "      <th>transactionType</th>\n",
       "      <th>transactionKey</th>\n",
       "      <th>Timestamp</th>\n",
       "      <th>creditLimit</th>\n",
       "      <th>availableMoney</th>\n",
       "      <th>merchantCountryCode</th>\n",
       "      <th>posEntryMode</th>\n",
       "      <th>posConditionCode</th>\n",
       "      <th>merchantCategoryCode</th>\n",
       "      <th>currentBalance</th>\n",
       "      <th>cardPresent</th>\n",
       "      <th>expirationDateKeyInMatch</th>\n",
       "      <th>isFraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st BBQ</td>\n",
       "      <td>200.12</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541917</td>\n",
       "      <td>2016-04-08 19:56:36</td>\n",
       "      <td>50000</td>\n",
       "      <td>49953.81</td>\n",
       "      <td>US</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>food</td>\n",
       "      <td>46.19</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Pub</td>\n",
       "      <td>95.11</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541962</td>\n",
       "      <td>2016-11-09 07:26:06</td>\n",
       "      <td>50000</td>\n",
       "      <td>49929.31</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>food</td>\n",
       "      <td>70.69</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100088067</td>\n",
       "      <td>1st Sandwitch Bar #758805</td>\n",
       "      <td>5.26</td>\n",
       "      <td>PURCHASE</td>\n",
       "      <td>T541920</td>\n",
       "      <td>2016-05-06 02:54:01</td>\n",
       "      <td>50000</td>\n",
       "      <td>49996.04</td>\n",
       "      <td>US</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>food</td>\n",
       "      <td>3.96</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   customerId               merchantName  transactionAmount transactionType  \\\n",
       "0   100088067                    1st BBQ             200.12        PURCHASE   \n",
       "1   100088067                    1st Pub              95.11        PURCHASE   \n",
       "2   100088067  1st Sandwitch Bar #758805               5.26        PURCHASE   \n",
       "\n",
       "  transactionKey           Timestamp  creditLimit  availableMoney  \\\n",
       "0        T541917 2016-04-08 19:56:36        50000        49953.81   \n",
       "1        T541962 2016-11-09 07:26:06        50000        49929.31   \n",
       "2        T541920 2016-05-06 02:54:01        50000        49996.04   \n",
       "\n",
       "  merchantCountryCode  posEntryMode  posConditionCode merchantCategoryCode  \\\n",
       "0                  US           9.0               1.0                 food   \n",
       "1                  US           2.0               1.0                 food   \n",
       "2                  US           2.0               1.0                 food   \n",
       "\n",
       "   currentBalance  cardPresent  expirationDateKeyInMatch  isFraud  \n",
       "0           46.19         True                     False    False  \n",
       "1           70.69         True                     False    False  \n",
       "2            3.96         True                     False    False  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**I found 56189 observations as duplicates (combination of both reversal and multi-swipes) that corresponds to 7.07% of original data.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "56189"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "786363 - 730174"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.07"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "round(56189/786363,2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-------"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}