save_splitted_table.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2963c2ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "326aeb3f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Not labeled!\n"
     ]
    }
   ],
   "source": [
    "df_fraud_all = pd.read_csv(\"data/Fraud_Detection_sql.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "275147ae",
   "metadata": {},
   "source": [
    "fraud_all 제대로 불러오기 했는지 검사"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "36a31efc",
   "metadata": {
    "scrolled": false
   },
   "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_YEAR</th>\n",
       "      <th>IS_COVID_YEAR</th>\n",
       "      <th>TRANS_MONTH</th>\n",
       "      <th>TRANS_DAY</th>\n",
       "      <th>TRANS_DAY_SIMPLIFIED</th>\n",
       "      <th>TRANS_HOUR</th>\n",
       "      <th>TRANS_HOUR_SIMPLIFIED</th>\n",
       "      <th>CATEGORY</th>\n",
       "      <th>AMT</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>CITY</th>\n",
       "      <th>CITY_POP</th>\n",
       "      <th>STATE</th>\n",
       "      <th>JOB</th>\n",
       "      <th>AGE</th>\n",
       "      <th>IS_FRAUD</th>\n",
       "      <th>CC_NUM</th>\n",
       "      <th>log_AMT</th>\n",
       "      <th>BC_CITY_POP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4.97</td>\n",
       "      <td>False</td>\n",
       "      <td>Moravian Falls</td>\n",
       "      <td>3495</td>\n",
       "      <td>0</td>\n",
       "      <td>Psychologist, counselling</td>\n",
       "      <td>35</td>\n",
       "      <td>False</td>\n",
       "      <td>2703186189652095</td>\n",
       "      <td>1.603420</td>\n",
       "      <td>5.391860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>107.23</td>\n",
       "      <td>False</td>\n",
       "      <td>Orient</td>\n",
       "      <td>149</td>\n",
       "      <td>1</td>\n",
       "      <td>Special educational needs teacher</td>\n",
       "      <td>45</td>\n",
       "      <td>False</td>\n",
       "      <td>630423337322</td>\n",
       "      <td>4.674976</td>\n",
       "      <td>3.851125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TRANS_YEAR  IS_COVID_YEAR  TRANS_MONTH  TRANS_DAY  TRANS_DAY_SIMPLIFIED  \\\n",
       "0        2019          False            1          1                     0   \n",
       "1        2019          False            1          1                     0   \n",
       "\n",
       "   TRANS_HOUR  TRANS_HOUR_SIMPLIFIED  CATEGORY     AMT  GENDER  \\\n",
       "0           0                      0         0    4.97   False   \n",
       "1           0                      0         1  107.23   False   \n",
       "\n",
       "             CITY  CITY_POP  STATE                                JOB  AGE  \\\n",
       "0  Moravian Falls      3495      0          Psychologist, counselling   35   \n",
       "1          Orient       149      1  Special educational needs teacher   45   \n",
       "\n",
       "   IS_FRAUD            CC_NUM   log_AMT  BC_CITY_POP  \n",
       "0     False  2703186189652095  1.603420     5.391860  \n",
       "1     False      630423337322  4.674976     3.851125  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fraud_all.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "29d665f3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    1842743\n",
       "True        9651\n",
       "Name: IS_FRAUD, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fraud_all[\"IS_FRAUD\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "60763461",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fraud의 비율은 0.005210014716091717(0.521001%)입니다.\n"
     ]
    }
   ],
   "source": [
    "not_fraud_count = df_fraud_all[\"IS_FRAUD\"].value_counts()[0]\n",
    "fraud_count = df_fraud_all[\"IS_FRAUD\"].value_counts()[1]\n",
    "fraud_ratio = fraud_count / (not_fraud_count + fraud_count)\n",
    "print(\"fraud의 비율은 \"+str(fraud_ratio)+\"(%f%%)입니다.\" %(fraud_ratio*100))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64d2681b",
   "metadata": {},
   "source": [
    "0.5%이니 995:5 비율로 데이터가 존재한다.\n",
    "비율을 0.2로 맞추고 싶으면 20:5로 줄인다.\n",
    "따라서 not_fraud데이터 갯수에서 20/995가 내가 실제로 3개의 데이터 셋으로 나눌 대상 크기가 된다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "697f8af0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    37040\n",
       "True      9651\n",
       "Name: IS_FRAUD, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_not_fraud=df_fraud_all[df_fraud_all[\"IS_FRAUD\"] == 0]\n",
    "\n",
    "df_not_fraud_reduced = df_not_fraud.sample(frac = 20/995)\n",
    "\n",
    "df_fraud=df_fraud_all[df_fraud_all[\"IS_FRAUD\"] == 1]\n",
    "\n",
    "df_fraud_all_reduced = pd.concat([df_not_fraud_reduced, df_fraud])\n",
    "\n",
    "#비율을 맞춘 fraud 데이터프레임의 갯수 확인\n",
    "df_fraud_all_reduced[\"IS_FRAUD\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "79ae3284",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "fraud의 비율은 0.20669936390310767(20.669936%)입니다.\n"
     ]
    }
   ],
   "source": [
    "not_fraud_count = df_fraud_all_reduced[\"IS_FRAUD\"].value_counts()[0]\n",
    "fraud_count = df_fraud_all_reduced[\"IS_FRAUD\"].value_counts()[1]\n",
    "fraud_ratio = fraud_count / (not_fraud_count + fraud_count)\n",
    "print(\"fraud의 비율은 \"+str(fraud_ratio)+\"(%f%%)입니다.\" %(fraud_ratio*100))\n",
    "\n",
    "#df_fraud_all_reduced 데이터프레임을 사용하도록 한다."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7bb64674",
   "metadata": {},
   "source": [
    "이제 DF를 train, validation, test 로 나눈다. 보통 6:2:2로 나눈다."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "701c49d2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(46691, 18)\n",
      "(46691,)\n",
      "(28014, 18)\n",
      "(28014,)\n",
      "(9338, 18)\n",
      "(9338,)\n",
      "(9339, 18)\n",
      "(9339,)\n"
     ]
    }
   ],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "df_fraud_all_reduced_y = df_fraud_all_reduced[\"IS_FRAUD\"]\n",
    "df_fraud_all_reduced_x = df_fraud_all_reduced.drop(columns=[\"IS_FRAUD\"], inplace=False)\n",
    "\n",
    "print(df_fraud_all_reduced_x.shape)\n",
    "print(df_fraud_all_reduced_y.shape)\n",
    "\n",
    "df_train2_x, df_test_x, df_train2_y, df_test_y = train_test_split(df_fraud_all_reduced_x, df_fraud_all_reduced_y, train_size = 0.8, random_state = 42, shuffle = True, stratify=df_fraud_all_reduced_y)\n",
    "df_train_x, df_val_x, df_train_y, df_val_y = train_test_split(df_train2_x, df_train2_y, train_size = 0.75, random_state = 42, shuffle = True, stratify=df_train2_y)\n",
    "\n",
    "print(df_train_x.shape)\n",
    "print(df_train_y.shape)\n",
    "print(df_val_x.shape)\n",
    "print(df_val_y.shape)\n",
    "print(df_test_x.shape)\n",
    "print(df_test_y.shape)\n",
    "\n",
    "os.mkdir(\"data/splitted\")\n",
    "df_train_x.to_csv(\"data/splitted/Fraud_Detection_train_features.csv\", index=False)\n",
    "df_train_y.to_csv(\"data/splitted/Fraud_Detection_train_target.csv\", index=False)\n",
    "df_val_x.to_csv(\"data/splitted/Fraud_Detection_validation_features.csv\", index=False)\n",
    "df_val_y.to_csv(\"data/splitted/Fraud_Detection_validation_target.csv\", index=False)\n",
    "df_test_x.to_csv(\"data/splitted/Fraud_Detection_test_features.csv\", index=False)\n",
    "df_test_y.to_csv(\"data/splitted/Fraud_Detection_test_target.csv\", index=False)\n",
    "\n",
    "#df_train_x -> 훈련 데이터의 feature테이블\n",
    "#df_train_y -> 훈련 데이터의 target배열\n",
    "#df_val_x -> 검증 데이터의 feature테이블\n",
    "#df_val_y -> 검증 데이터의 target배열\n",
    "#df_test_x -> 시험 데이터의 feature테이블\n",
    "#df_test_y -> 시험 데이터의 target배열"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}