data_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3117ebaf",
   "metadata": {
    "papermill": {
     "duration": 0.009065,
     "end_time": "2024-03-08T10:23:47.298153",
     "exception": false,
     "start_time": "2024-03-08T10:23:47.289088",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### Data Cleaining and Preprocessing\n",
    "\n",
    "- Load data into a dataframe\n",
    "- Look into each of the columns\n",
    "- over_sampling/under_sampling for imbalance of class (is_fraud) (To-do)\n",
    "- feature selection (to-do)\n",
    "- split data into train and test\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "99927b00",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:23:43.838176Z",
     "iopub.status.busy": "2024-03-08T10:23:43.837156Z",
     "iopub.status.idle": "2024-03-08T10:23:47.275513Z",
     "shell.execute_reply": "2024-03-08T10:23:47.274137Z"
    },
    "papermill": {
     "duration": 3.453663,
     "end_time": "2024-03-08T10:23:47.279064",
     "exception": false,
     "start_time": "2024-03-08T10:23:43.825401",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from imblearn.over_sampling import SMOTE\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.preprocessing import LabelEncoder, StandardScaler\n",
    "from sklearn.linear_model import LogisticRegression\n",
    "from sklearn.tree import DecisionTreeClassifier\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "import xgboost as xgb\n",
    "from sklearn.metrics import accuracy_score, classification_report\n",
    "\n",
    "import pickle\n",
    "\n",
    "import warnings \n",
    "warnings.filterwarnings('ignore')\n",
    "pd.set_option('display.max_rows', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "851acf00",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pip install pandas scikit-learn xgboost pyspark numpy matplotlib imblearn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "88fd75e2",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:23:47.318344Z",
     "iopub.status.busy": "2024-03-08T10:23:47.317738Z",
     "iopub.status.idle": "2024-03-08T10:24:09.099890Z",
     "shell.execute_reply": "2024-03-08T10:24:09.097953Z"
    },
    "papermill": {
     "duration": 21.796174,
     "end_time": "2024-03-08T10:24:09.103345",
     "exception": false,
     "start_time": "2024-03-08T10:23:47.307171",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# df_train = pd.read_csv('./data/fraudTrain.csv', low_memory=False, index_col=0)\n",
    "# df_test = pd.read_csv('./data/fraudTest.csv', low_memory=False, index_col=0)\n",
    "\n",
    "# df = pd.concat([df_train, df_test],ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64a33fb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('./data/sample.csv', low_memory=False, index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f2b08f26",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:24:09.451220Z",
     "iopub.status.busy": "2024-03-08T10:24:09.450765Z",
     "iopub.status.idle": "2024-03-08T10:24:09.458587Z",
     "shell.execute_reply": "2024-03-08T10:24:09.457328Z"
    },
    "papermill": {
     "duration": 0.021347,
     "end_time": "2024-03-08T10:24:09.461373",
     "exception": false,
     "start_time": "2024-03-08T10:24:09.440026",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1852394, 22)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "43ebbe86",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:24:09.482261Z",
     "iopub.status.busy": "2024-03-08T10:24:09.481812Z",
     "iopub.status.idle": "2024-03-08T10:24:09.515749Z",
     "shell.execute_reply": "2024-03-08T10:24:09.514592Z"
    },
    "papermill": {
     "duration": 0.047475,
     "end_time": "2024-03-08T10:24:09.518332",
     "exception": false,
     "start_time": "2024-03-08T10:24:09.470857",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "is_fraud\n",
       "0    1842743\n",
       "1       9651\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.is_fraud.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "96fd64ba",
   "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>trans_date_trans_time</th>\n",
       "      <th>cc_num</th>\n",
       "      <th>merchant</th>\n",
       "      <th>category</th>\n",
       "      <th>amt</th>\n",
       "      <th>first</th>\n",
       "      <th>last</th>\n",
       "      <th>gender</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>...</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city_pop</th>\n",
       "      <th>job</th>\n",
       "      <th>dob</th>\n",
       "      <th>trans_num</th>\n",
       "      <th>unix_time</th>\n",
       "      <th>merch_lat</th>\n",
       "      <th>merch_long</th>\n",
       "      <th>is_fraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01 00:00:18</td>\n",
       "      <td>2703186189652095</td>\n",
       "      <td>fraud_Rippin, Kub and Mann</td>\n",
       "      <td>misc_net</td>\n",
       "      <td>4.97</td>\n",
       "      <td>Jennifer</td>\n",
       "      <td>Banks</td>\n",
       "      <td>F</td>\n",
       "      <td>561 Perry Cove</td>\n",
       "      <td>Moravian Falls</td>\n",
       "      <td>...</td>\n",
       "      <td>36.0788</td>\n",
       "      <td>-81.1781</td>\n",
       "      <td>3495</td>\n",
       "      <td>Psychologist, counselling</td>\n",
       "      <td>1988-03-09</td>\n",
       "      <td>0b242abb623afc578575680df30655b9</td>\n",
       "      <td>1325376018</td>\n",
       "      <td>36.011293</td>\n",
       "      <td>-82.048315</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-01-01 00:00:44</td>\n",
       "      <td>630423337322</td>\n",
       "      <td>fraud_Heller, Gutmann and Zieme</td>\n",
       "      <td>grocery_pos</td>\n",
       "      <td>107.23</td>\n",
       "      <td>Stephanie</td>\n",
       "      <td>Gill</td>\n",
       "      <td>F</td>\n",
       "      <td>43039 Riley Greens Suite 393</td>\n",
       "      <td>Orient</td>\n",
       "      <td>...</td>\n",
       "      <td>48.8878</td>\n",
       "      <td>-118.2105</td>\n",
       "      <td>149</td>\n",
       "      <td>Special educational needs teacher</td>\n",
       "      <td>1978-06-21</td>\n",
       "      <td>1f76529f8574734946361c461b024d99</td>\n",
       "      <td>1325376044</td>\n",
       "      <td>49.159047</td>\n",
       "      <td>-118.186462</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-01 00:00:51</td>\n",
       "      <td>38859492057661</td>\n",
       "      <td>fraud_Lind-Buckridge</td>\n",
       "      <td>entertainment</td>\n",
       "      <td>220.11</td>\n",
       "      <td>Edward</td>\n",
       "      <td>Sanchez</td>\n",
       "      <td>M</td>\n",
       "      <td>594 White Dale Suite 530</td>\n",
       "      <td>Malad City</td>\n",
       "      <td>...</td>\n",
       "      <td>42.1808</td>\n",
       "      <td>-112.2620</td>\n",
       "      <td>4154</td>\n",
       "      <td>Nature conservation officer</td>\n",
       "      <td>1962-01-19</td>\n",
       "      <td>a1a22d70485983eac12b5b88dad1cf95</td>\n",
       "      <td>1325376051</td>\n",
       "      <td>43.150704</td>\n",
       "      <td>-112.154481</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01 00:01:16</td>\n",
       "      <td>3534093764340240</td>\n",
       "      <td>fraud_Kutch, Hermiston and Farrell</td>\n",
       "      <td>gas_transport</td>\n",
       "      <td>45.00</td>\n",
       "      <td>Jeremy</td>\n",
       "      <td>White</td>\n",
       "      <td>M</td>\n",
       "      <td>9443 Cynthia Court Apt. 038</td>\n",
       "      <td>Boulder</td>\n",
       "      <td>...</td>\n",
       "      <td>46.2306</td>\n",
       "      <td>-112.1138</td>\n",
       "      <td>1939</td>\n",
       "      <td>Patent attorney</td>\n",
       "      <td>1967-01-12</td>\n",
       "      <td>6b849c168bdad6f867558c3793159a81</td>\n",
       "      <td>1325376076</td>\n",
       "      <td>47.034331</td>\n",
       "      <td>-112.561071</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-01 00:03:06</td>\n",
       "      <td>375534208663984</td>\n",
       "      <td>fraud_Keeling-Crist</td>\n",
       "      <td>misc_pos</td>\n",
       "      <td>41.96</td>\n",
       "      <td>Tyler</td>\n",
       "      <td>Garcia</td>\n",
       "      <td>M</td>\n",
       "      <td>408 Bradley Rest</td>\n",
       "      <td>Doe Hill</td>\n",
       "      <td>...</td>\n",
       "      <td>38.4207</td>\n",
       "      <td>-79.4629</td>\n",
       "      <td>99</td>\n",
       "      <td>Dance movement psychotherapist</td>\n",
       "      <td>1986-03-28</td>\n",
       "      <td>a41d7549acf90789359a9aa5346dcb46</td>\n",
       "      <td>1325376186</td>\n",
       "      <td>38.674999</td>\n",
       "      <td>-78.632459</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  trans_date_trans_time            cc_num                            merchant  \\\n",
       "0   2019-01-01 00:00:18  2703186189652095          fraud_Rippin, Kub and Mann   \n",
       "1   2019-01-01 00:00:44      630423337322     fraud_Heller, Gutmann and Zieme   \n",
       "2   2019-01-01 00:00:51    38859492057661                fraud_Lind-Buckridge   \n",
       "3   2019-01-01 00:01:16  3534093764340240  fraud_Kutch, Hermiston and Farrell   \n",
       "4   2019-01-01 00:03:06   375534208663984                 fraud_Keeling-Crist   \n",
       "\n",
       "        category     amt      first     last gender  \\\n",
       "0       misc_net    4.97   Jennifer    Banks      F   \n",
       "1    grocery_pos  107.23  Stephanie     Gill      F   \n",
       "2  entertainment  220.11     Edward  Sanchez      M   \n",
       "3  gas_transport   45.00     Jeremy    White      M   \n",
       "4       misc_pos   41.96      Tyler   Garcia      M   \n",
       "\n",
       "                         street            city  ...      lat      long  \\\n",
       "0                561 Perry Cove  Moravian Falls  ...  36.0788  -81.1781   \n",
       "1  43039 Riley Greens Suite 393          Orient  ...  48.8878 -118.2105   \n",
       "2      594 White Dale Suite 530      Malad City  ...  42.1808 -112.2620   \n",
       "3   9443 Cynthia Court Apt. 038         Boulder  ...  46.2306 -112.1138   \n",
       "4              408 Bradley Rest        Doe Hill  ...  38.4207  -79.4629   \n",
       "\n",
       "   city_pop                                job         dob  \\\n",
       "0      3495          Psychologist, counselling  1988-03-09   \n",
       "1       149  Special educational needs teacher  1978-06-21   \n",
       "2      4154        Nature conservation officer  1962-01-19   \n",
       "3      1939                    Patent attorney  1967-01-12   \n",
       "4        99     Dance movement psychotherapist  1986-03-28   \n",
       "\n",
       "                          trans_num   unix_time  merch_lat  merch_long  \\\n",
       "0  0b242abb623afc578575680df30655b9  1325376018  36.011293  -82.048315   \n",
       "1  1f76529f8574734946361c461b024d99  1325376044  49.159047 -118.186462   \n",
       "2  a1a22d70485983eac12b5b88dad1cf95  1325376051  43.150704 -112.154481   \n",
       "3  6b849c168bdad6f867558c3793159a81  1325376076  47.034331 -112.561071   \n",
       "4  a41d7549acf90789359a9aa5346dcb46  1325376186  38.674999  -78.632459   \n",
       "\n",
       "   is_fraud  \n",
       "0         0  \n",
       "1         0  \n",
       "2         0  \n",
       "3         0  \n",
       "4         0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "660af217",
   "metadata": {
    "papermill": {
     "duration": 0.00924,
     "end_time": "2024-03-08T10:24:09.539786",
     "exception": false,
     "start_time": "2024-03-08T10:24:09.530546",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### Looking into columns and cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "38619c3b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:24:09.561954Z",
     "iopub.status.busy": "2024-03-08T10:24:09.561515Z",
     "iopub.status.idle": "2024-03-08T10:24:09.858046Z",
     "shell.execute_reply": "2024-03-08T10:24:09.856631Z"
    },
    "papermill": {
     "duration": 0.311092,
     "end_time": "2024-03-08T10:24:09.860954",
     "exception": false,
     "start_time": "2024-03-08T10:24:09.549862",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_removed = df[['cc_num','first', 'last', 'street', 'city', 'state', 'zip', 'dob', 'trans_num','trans_date_trans_time']]\n",
    "df = df.drop(['cc_num','first', 'last', 'street', 'city', 'state', 'zip', 'dob', 'trans_num','trans_date_trans_time'],axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5a44a0eb",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:24:09.881835Z",
     "iopub.status.busy": "2024-03-08T10:24:09.881394Z",
     "iopub.status.idle": "2024-03-08T10:24:09.906437Z",
     "shell.execute_reply": "2024-03-08T10:24:09.904774Z"
    },
    "papermill": {
     "duration": 0.039285,
     "end_time": "2024-03-08T10:24:09.909651",
     "exception": false,
     "start_time": "2024-03-08T10:24:09.870366",
     "status": "completed"
    },
    "tags": []
   },
   "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>merchant</th>\n",
       "      <th>category</th>\n",
       "      <th>amt</th>\n",
       "      <th>gender</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city_pop</th>\n",
       "      <th>job</th>\n",
       "      <th>unix_time</th>\n",
       "      <th>merch_lat</th>\n",
       "      <th>merch_long</th>\n",
       "      <th>is_fraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>fraud_Rippin, Kub and Mann</td>\n",
       "      <td>misc_net</td>\n",
       "      <td>4.97</td>\n",
       "      <td>F</td>\n",
       "      <td>36.0788</td>\n",
       "      <td>-81.1781</td>\n",
       "      <td>3495</td>\n",
       "      <td>Psychologist, counselling</td>\n",
       "      <td>1325376018</td>\n",
       "      <td>36.011293</td>\n",
       "      <td>-82.048315</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>fraud_Heller, Gutmann and Zieme</td>\n",
       "      <td>grocery_pos</td>\n",
       "      <td>107.23</td>\n",
       "      <td>F</td>\n",
       "      <td>48.8878</td>\n",
       "      <td>-118.2105</td>\n",
       "      <td>149</td>\n",
       "      <td>Special educational needs teacher</td>\n",
       "      <td>1325376044</td>\n",
       "      <td>49.159047</td>\n",
       "      <td>-118.186462</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          merchant     category     amt gender      lat  \\\n",
       "0       fraud_Rippin, Kub and Mann     misc_net    4.97      F  36.0788   \n",
       "1  fraud_Heller, Gutmann and Zieme  grocery_pos  107.23      F  48.8878   \n",
       "\n",
       "       long  city_pop                                job   unix_time  \\\n",
       "0  -81.1781      3495          Psychologist, counselling  1325376018   \n",
       "1 -118.2105       149  Special educational needs teacher  1325376044   \n",
       "\n",
       "   merch_lat  merch_long  is_fraud  \n",
       "0  36.011293  -82.048315         0  \n",
       "1  49.159047 -118.186462         0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e244eb19",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:24:09.931268Z",
     "iopub.status.busy": "2024-03-08T10:24:09.930868Z",
     "iopub.status.idle": "2024-03-08T10:24:09.950312Z",
     "shell.execute_reply": "2024-03-08T10:24:09.948659Z"
    },
    "papermill": {
     "duration": 0.033503,
     "end_time": "2024-03-08T10:24:09.953104",
     "exception": false,
     "start_time": "2024-03-08T10:24:09.919601",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1852394 entries, 0 to 1852393\n",
      "Data columns (total 12 columns):\n",
      " #   Column      Dtype  \n",
      "---  ------      -----  \n",
      " 0   merchant    object \n",
      " 1   category    object \n",
      " 2   amt         float64\n",
      " 3   gender      object \n",
      " 4   lat         float64\n",
      " 5   long        float64\n",
      " 6   city_pop    int64  \n",
      " 7   job         object \n",
      " 8   unix_time   int64  \n",
      " 9   merch_lat   float64\n",
      " 10  merch_long  float64\n",
      " 11  is_fraud    int64  \n",
      "dtypes: float64(5), int64(3), object(4)\n",
      "memory usage: 169.6+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0cd9dfe4",
   "metadata": {
    "papermill": {
     "duration": 0.009521,
     "end_time": "2024-03-08T10:24:09.972744",
     "exception": false,
     "start_time": "2024-03-08T10:24:09.963223",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### Sampling and handling the minority class using SMOTE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f12e1b71",
   "metadata": {
    "papermill": {
     "duration": 0.010099,
     "end_time": "2024-03-08T10:24:09.992733",
     "exception": false,
     "start_time": "2024-03-08T10:24:09.982634",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ec624ce0",
   "metadata": {},
   "source": [
    "### Encoding object columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "806d3d1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['merchant', 'category', 'gender', 'job']\n",
    "def encode(df):\n",
    "    df_obj = df.select_dtypes(include=['object'])\n",
    "    encoders = {}\n",
    "    for col in cols:\n",
    "        encoder = LabelEncoder()\n",
    "        df[col+'_label'] = encoder.fit_transform(df[col])\n",
    "        encoders[col] = encoder\n",
    "    with open('./encoders/LE_model_v1.pkl', 'wb') as f:\n",
    "        pickle.dump(encoders, f)\n",
    "    return df\n",
    "\n",
    "df = encode(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "bcd4053a",
   "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>trans_date_trans_time</th>\n",
       "      <th>cc_num</th>\n",
       "      <th>merchant</th>\n",
       "      <th>category</th>\n",
       "      <th>amt</th>\n",
       "      <th>first</th>\n",
       "      <th>last</th>\n",
       "      <th>gender</th>\n",
       "      <th>street</th>\n",
       "      <th>city</th>\n",
       "      <th>...</th>\n",
       "      <th>dob</th>\n",
       "      <th>trans_num</th>\n",
       "      <th>unix_time</th>\n",
       "      <th>merch_lat</th>\n",
       "      <th>merch_long</th>\n",
       "      <th>is_fraud</th>\n",
       "      <th>merchant_label</th>\n",
       "      <th>category_label</th>\n",
       "      <th>gender_label</th>\n",
       "      <th>job_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-01-01 00:00:18</td>\n",
       "      <td>2703186189652095</td>\n",
       "      <td>fraud_Rippin, Kub and Mann</td>\n",
       "      <td>misc_net</td>\n",
       "      <td>4.97</td>\n",
       "      <td>Jennifer</td>\n",
       "      <td>Banks</td>\n",
       "      <td>F</td>\n",
       "      <td>561 Perry Cove</td>\n",
       "      <td>Moravian Falls</td>\n",
       "      <td>...</td>\n",
       "      <td>1988-03-09</td>\n",
       "      <td>0b242abb623afc578575680df30655b9</td>\n",
       "      <td>1325376018</td>\n",
       "      <td>36.011293</td>\n",
       "      <td>-82.048315</td>\n",
       "      <td>0</td>\n",
       "      <td>514</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-01-01 00:00:44</td>\n",
       "      <td>630423337322</td>\n",
       "      <td>fraud_Heller, Gutmann and Zieme</td>\n",
       "      <td>grocery_pos</td>\n",
       "      <td>107.23</td>\n",
       "      <td>Stephanie</td>\n",
       "      <td>Gill</td>\n",
       "      <td>F</td>\n",
       "      <td>43039 Riley Greens Suite 393</td>\n",
       "      <td>Orient</td>\n",
       "      <td>...</td>\n",
       "      <td>1978-06-21</td>\n",
       "      <td>1f76529f8574734946361c461b024d99</td>\n",
       "      <td>1325376044</td>\n",
       "      <td>49.159047</td>\n",
       "      <td>-118.186462</td>\n",
       "      <td>0</td>\n",
       "      <td>241</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-01-01 00:00:51</td>\n",
       "      <td>38859492057661</td>\n",
       "      <td>fraud_Lind-Buckridge</td>\n",
       "      <td>entertainment</td>\n",
       "      <td>220.11</td>\n",
       "      <td>Edward</td>\n",
       "      <td>Sanchez</td>\n",
       "      <td>M</td>\n",
       "      <td>594 White Dale Suite 530</td>\n",
       "      <td>Malad City</td>\n",
       "      <td>...</td>\n",
       "      <td>1962-01-19</td>\n",
       "      <td>a1a22d70485983eac12b5b88dad1cf95</td>\n",
       "      <td>1325376051</td>\n",
       "      <td>43.150704</td>\n",
       "      <td>-112.154481</td>\n",
       "      <td>0</td>\n",
       "      <td>390</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-01-01 00:01:16</td>\n",
       "      <td>3534093764340240</td>\n",
       "      <td>fraud_Kutch, Hermiston and Farrell</td>\n",
       "      <td>gas_transport</td>\n",
       "      <td>45.00</td>\n",
       "      <td>Jeremy</td>\n",
       "      <td>White</td>\n",
       "      <td>M</td>\n",
       "      <td>9443 Cynthia Court Apt. 038</td>\n",
       "      <td>Boulder</td>\n",
       "      <td>...</td>\n",
       "      <td>1967-01-12</td>\n",
       "      <td>6b849c168bdad6f867558c3793159a81</td>\n",
       "      <td>1325376076</td>\n",
       "      <td>47.034331</td>\n",
       "      <td>-112.561071</td>\n",
       "      <td>0</td>\n",
       "      <td>360</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-01-01 00:03:06</td>\n",
       "      <td>375534208663984</td>\n",
       "      <td>fraud_Keeling-Crist</td>\n",
       "      <td>misc_pos</td>\n",
       "      <td>41.96</td>\n",
       "      <td>Tyler</td>\n",
       "      <td>Garcia</td>\n",
       "      <td>M</td>\n",
       "      <td>408 Bradley Rest</td>\n",
       "      <td>Doe Hill</td>\n",
       "      <td>...</td>\n",
       "      <td>1986-03-28</td>\n",
       "      <td>a41d7549acf90789359a9aa5346dcb46</td>\n",
       "      <td>1325376186</td>\n",
       "      <td>38.674999</td>\n",
       "      <td>-78.632459</td>\n",
       "      <td>0</td>\n",
       "      <td>297</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>116</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  trans_date_trans_time            cc_num                            merchant  \\\n",
       "0   2019-01-01 00:00:18  2703186189652095          fraud_Rippin, Kub and Mann   \n",
       "1   2019-01-01 00:00:44      630423337322     fraud_Heller, Gutmann and Zieme   \n",
       "2   2019-01-01 00:00:51    38859492057661                fraud_Lind-Buckridge   \n",
       "3   2019-01-01 00:01:16  3534093764340240  fraud_Kutch, Hermiston and Farrell   \n",
       "4   2019-01-01 00:03:06   375534208663984                 fraud_Keeling-Crist   \n",
       "\n",
       "        category     amt      first     last gender  \\\n",
       "0       misc_net    4.97   Jennifer    Banks      F   \n",
       "1    grocery_pos  107.23  Stephanie     Gill      F   \n",
       "2  entertainment  220.11     Edward  Sanchez      M   \n",
       "3  gas_transport   45.00     Jeremy    White      M   \n",
       "4       misc_pos   41.96      Tyler   Garcia      M   \n",
       "\n",
       "                         street            city  ...         dob  \\\n",
       "0                561 Perry Cove  Moravian Falls  ...  1988-03-09   \n",
       "1  43039 Riley Greens Suite 393          Orient  ...  1978-06-21   \n",
       "2      594 White Dale Suite 530      Malad City  ...  1962-01-19   \n",
       "3   9443 Cynthia Court Apt. 038         Boulder  ...  1967-01-12   \n",
       "4              408 Bradley Rest        Doe Hill  ...  1986-03-28   \n",
       "\n",
       "                          trans_num   unix_time  merch_lat  merch_long  \\\n",
       "0  0b242abb623afc578575680df30655b9  1325376018  36.011293  -82.048315   \n",
       "1  1f76529f8574734946361c461b024d99  1325376044  49.159047 -118.186462   \n",
       "2  a1a22d70485983eac12b5b88dad1cf95  1325376051  43.150704 -112.154481   \n",
       "3  6b849c168bdad6f867558c3793159a81  1325376076  47.034331 -112.561071   \n",
       "4  a41d7549acf90789359a9aa5346dcb46  1325376186  38.674999  -78.632459   \n",
       "\n",
       "  is_fraud merchant_label category_label  gender_label  job_label  \n",
       "0        0            514              8             0        372  \n",
       "1        0            241              4             0        431  \n",
       "2        0            390              0             1        308  \n",
       "3        0            360              2             1        330  \n",
       "4        0            297              9             1        116  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e961fcd3",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "08a199a4",
   "metadata": {
    "papermill": {
     "duration": 0.009439,
     "end_time": "2024-03-08T10:24:10.012048",
     "exception": false,
     "start_time": "2024-03-08T10:24:10.002609",
     "status": "completed"
    },
    "tags": []
   },
   "source": [
    "### Splitting data into train and test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "dd82a393",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:24:10.033665Z",
     "iopub.status.busy": "2024-03-08T10:24:10.033216Z",
     "iopub.status.idle": "2024-03-08T10:24:11.916577Z",
     "shell.execute_reply": "2024-03-08T10:24:11.914803Z"
    },
    "papermill": {
     "duration": 1.898483,
     "end_time": "2024-03-08T10:24:11.920331",
     "exception": false,
     "start_time": "2024-03-08T10:24:10.021848",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((1667154, 26), (185240, 26))"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train, test = train_test_split(df, test_size=0.1, shuffle=True, random_state=42)\n",
    "train = train.reset_index(drop=True)\n",
    "test = test.reset_index(drop=True)\n",
    "train.shape, test.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "d3f6bfee",
   "metadata": {},
   "outputs": [],
   "source": [
    "train = train.drop(['merchant','gender','job','category','cc_num','first', 'last', 'street', 'city', 'state', 'zip', 'dob','trans_date_trans_time'],axis=1)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "ca3b98c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "train.to_csv('./data/clean_train.csv')\n",
    "test.to_csv('./data/clean_test.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "679bbe0f",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:24:11.964959Z",
     "iopub.status.busy": "2024-03-08T10:24:11.964487Z",
     "iopub.status.idle": "2024-03-08T10:24:13.799855Z",
     "shell.execute_reply": "2024-03-08T10:24:13.797965Z"
    },
    "papermill": {
     "duration": 1.849964,
     "end_time": "2024-03-08T10:24:13.802822",
     "exception": false,
     "start_time": "2024-03-08T10:24:11.952858",
     "status": "completed"
    },
    "tags": []
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b8991e7a",
   "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>merchant</th>\n",
       "      <th>category</th>\n",
       "      <th>amt</th>\n",
       "      <th>gender</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city_pop</th>\n",
       "      <th>job</th>\n",
       "      <th>unix_time</th>\n",
       "      <th>merch_lat</th>\n",
       "      <th>merch_long</th>\n",
       "      <th>is_fraud</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>514</td>\n",
       "      <td>8</td>\n",
       "      <td>4.97</td>\n",
       "      <td>0</td>\n",
       "      <td>36.0788</td>\n",
       "      <td>-81.1781</td>\n",
       "      <td>3495</td>\n",
       "      <td>372</td>\n",
       "      <td>1325376018</td>\n",
       "      <td>36.011293</td>\n",
       "      <td>-82.048315</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>241</td>\n",
       "      <td>4</td>\n",
       "      <td>107.23</td>\n",
       "      <td>0</td>\n",
       "      <td>48.8878</td>\n",
       "      <td>-118.2105</td>\n",
       "      <td>149</td>\n",
       "      <td>431</td>\n",
       "      <td>1325376044</td>\n",
       "      <td>49.159047</td>\n",
       "      <td>-118.186462</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>390</td>\n",
       "      <td>0</td>\n",
       "      <td>220.11</td>\n",
       "      <td>1</td>\n",
       "      <td>42.1808</td>\n",
       "      <td>-112.2620</td>\n",
       "      <td>4154</td>\n",
       "      <td>308</td>\n",
       "      <td>1325376051</td>\n",
       "      <td>43.150704</td>\n",
       "      <td>-112.154481</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>360</td>\n",
       "      <td>2</td>\n",
       "      <td>45.00</td>\n",
       "      <td>1</td>\n",
       "      <td>46.2306</td>\n",
       "      <td>-112.1138</td>\n",
       "      <td>1939</td>\n",
       "      <td>330</td>\n",
       "      <td>1325376076</td>\n",
       "      <td>47.034331</td>\n",
       "      <td>-112.561071</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>297</td>\n",
       "      <td>9</td>\n",
       "      <td>41.96</td>\n",
       "      <td>1</td>\n",
       "      <td>38.4207</td>\n",
       "      <td>-79.4629</td>\n",
       "      <td>99</td>\n",
       "      <td>116</td>\n",
       "      <td>1325376186</td>\n",
       "      <td>38.674999</td>\n",
       "      <td>-78.632459</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   merchant  category     amt  gender      lat      long  city_pop  job  \\\n",
       "0       514         8    4.97       0  36.0788  -81.1781      3495  372   \n",
       "1       241         4  107.23       0  48.8878 -118.2105       149  431   \n",
       "2       390         0  220.11       1  42.1808 -112.2620      4154  308   \n",
       "3       360         2   45.00       1  46.2306 -112.1138      1939  330   \n",
       "4       297         9   41.96       1  38.4207  -79.4629        99  116   \n",
       "\n",
       "    unix_time  merch_lat  merch_long  is_fraud  \n",
       "0  1325376018  36.011293  -82.048315         0  \n",
       "1  1325376044  49.159047 -118.186462         0  \n",
       "2  1325376051  43.150704 -112.154481         0  \n",
       "3  1325376076  47.034331 -112.561071         0  \n",
       "4  1325376186  38.674999  -78.632459         0  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "5c485c70",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2024-03-08T10:24:13.826341Z",
     "iopub.status.busy": "2024-03-08T10:24:13.825934Z",
     "iopub.status.idle": "2024-03-08T10:24:13.843223Z",
     "shell.execute_reply": "2024-03-08T10:24:13.841762Z"
    },
    "papermill": {
     "duration": 0.032211,
     "end_time": "2024-03-08T10:24:13.846034",
     "exception": false,
     "start_time": "2024-03-08T10:24:13.813823",
     "status": "completed"
    },
    "tags": []
   },
   "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>amt</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "      <th>city_pop</th>\n",
       "      <th>trans_num</th>\n",
       "      <th>unix_time</th>\n",
       "      <th>merch_lat</th>\n",
       "      <th>merch_long</th>\n",
       "      <th>is_fraud</th>\n",
       "      <th>merchant_label</th>\n",
       "      <th>category_label</th>\n",
       "      <th>gender_label</th>\n",
       "      <th>job_label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>85.75</td>\n",
       "      <td>36.4899</td>\n",
       "      <td>-79.4736</td>\n",
       "      <td>3402</td>\n",
       "      <td>db9872c1e5c88708e700a23a04435ef3</td>\n",
       "      <td>1341848490</td>\n",
       "      <td>36.974911</td>\n",
       "      <td>-80.462600</td>\n",
       "      <td>0</td>\n",
       "      <td>260</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8.45</td>\n",
       "      <td>40.8170</td>\n",
       "      <td>-74.0000</td>\n",
       "      <td>13835</td>\n",
       "      <td>61cb27a0aa621405f8adde59ea363003</td>\n",
       "      <td>1344061258</td>\n",
       "      <td>41.076082</td>\n",
       "      <td>-74.337634</td>\n",
       "      <td>0</td>\n",
       "      <td>421</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "      <td>368</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     amt      lat     long  city_pop                         trans_num  \\\n",
       "0  85.75  36.4899 -79.4736      3402  db9872c1e5c88708e700a23a04435ef3   \n",
       "1   8.45  40.8170 -74.0000     13835  61cb27a0aa621405f8adde59ea363003   \n",
       "\n",
       "    unix_time  merch_lat  merch_long  is_fraud  merchant_label  \\\n",
       "0  1341848490  36.974911  -80.462600         0             260   \n",
       "1  1344061258  41.076082  -74.337634         0             421   \n",
       "\n",
       "   category_label  gender_label  job_label  \n",
       "0               5             1        251  \n",
       "1              11             1        368  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1d70d8b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kaggle": {
   "accelerator": "none",
   "dataSources": [
    {
     "datasetId": 817870,
     "sourceId": 1399887,
     "sourceType": "datasetVersion"
    }
   ],
   "dockerImageVersionId": 30664,
   "isGpuEnabled": false,
   "isInternetEnabled": true,
   "language": "python",
   "sourceType": "notebook"
  },
  "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.11.4"
  },
  "papermill": {
   "default_parameters": {},
   "duration": 960.673208,
   "end_time": "2024-03-08T10:39:40.966232",
   "environment_variables": {},
   "exception": null,
   "input_path": "__notebook__.ipynb",
   "output_path": "__notebook__.ipynb",
   "parameters": {},
   "start_time": "2024-03-08T10:23:40.293024",
   "version": "2.5.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}