데이터전처리.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "pd.options.display.float_format = '{:.5f}'.format  # 지수로 표현 안되게\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# 한글 폰트 문제 해결\n",
    "from matplotlib import font_manager, rc\n",
    "font_path = \"C:/Windows/Fonts/malgun.ttf\"\n",
    "font = font_manager.FontProperties(fname=font_path).get_name()\n",
    "rc('font', family=font)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* pop_cd : cd 고유번호 L011: 지역번호(01:서울, 1:10대, 022:인천20대) Y:성별번호 Y80(80대 남자) \n",
    "* year : 년도 \n",
    "* month : 월 \n",
    "* avg_score : 신용점수  \n",
    "* avg_rat:  신용등급 \n",
    "* city : 도시  \n",
    "* sex : 성별 \n",
    "* ages : 나이대 \n",
    "* population : pop_cd로 구분된 그룹에 포함된 고객인원 수/경제활동 인구수 \n",
    "* num_opencard : 카드개설 개수 \n",
    "* num_usecard : 사용카드 개수 \n",
    "* monthly_card_spend :  카드 소비 금액 \n",
    "* monthly_lc : 대출 약정 금액  \n",
    "* monthly_loan : 대출 금액 합계  \n",
    "* monthly_bk_loan : 은행대출 금액  \n",
    "* monthly_cd_loan : 카드대출 금액   \n",
    "* monthly_installments_loan : 할부 대출 금액 \n",
    "* monthly_insurance_loan : 보험 대출 금액 \n",
    "* monthly_sbk_loan : 저축은행 대출 금액 \n",
    "* loan_commitment : 한도 대출 금액 (마이너스 통장) \n",
    "* inst_rep_loanb : 분할 상환 대출 금액  \n",
    "* ls_rep_loanb : 일시 상환 대출 금액 \n",
    "* credit_loan : 신용 대출 금액 \n",
    "* mortgage_loan : 담보 대출 금액 \n",
    "* credit_card_payment : 신용카드 사용 금액 \n",
    "* credit_card_installments_payment : 신용카드 할부 사용 금액"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>pop_cd</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>avg_score</th>\n",
       "      <th>avg_rat</th>\n",
       "      <th>city</th>\n",
       "      <th>sex</th>\n",
       "      <th>ages</th>\n",
       "      <th>population</th>\n",
       "      <th>num_opencard</th>\n",
       "      <th>...</th>\n",
       "      <th>monthly_installments_loan</th>\n",
       "      <th>monthly_insurance_loan</th>\n",
       "      <th>monthly_sbk_loan</th>\n",
       "      <th>loan_commitment</th>\n",
       "      <th>inst_rep_loanb</th>\n",
       "      <th>ls_rep_loanb</th>\n",
       "      <th>credit_loan</th>\n",
       "      <th>mortgage_loan</th>\n",
       "      <th>credit_card_payment</th>\n",
       "      <th>credit_card_installments_payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L011</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>812</td>\n",
       "      <td>2</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10대</td>\n",
       "      <td>55000</td>\n",
       "      <td>1.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1120000</td>\n",
       "      <td>8210000</td>\n",
       "      <td>78020000</td>\n",
       "      <td>92170000</td>\n",
       "      <td>87260000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L011</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>773</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10대</td>\n",
       "      <td>380000</td>\n",
       "      <td>1.71000</td>\n",
       "      <td>...</td>\n",
       "      <td>510000</td>\n",
       "      <td>220000</td>\n",
       "      <td>1020000</td>\n",
       "      <td>680000</td>\n",
       "      <td>4660000</td>\n",
       "      <td>23300000</td>\n",
       "      <td>8260000</td>\n",
       "      <td>42410000</td>\n",
       "      <td>4000</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L011</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>771</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10대</td>\n",
       "      <td>387000</td>\n",
       "      <td>1.75000</td>\n",
       "      <td>...</td>\n",
       "      <td>490000</td>\n",
       "      <td>210000</td>\n",
       "      <td>1140000</td>\n",
       "      <td>640000</td>\n",
       "      <td>4370000</td>\n",
       "      <td>22930000</td>\n",
       "      <td>8300000</td>\n",
       "      <td>41710000</td>\n",
       "      <td>3000</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L011</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>770</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10대</td>\n",
       "      <td>412000</td>\n",
       "      <td>1.76000</td>\n",
       "      <td>...</td>\n",
       "      <td>510000</td>\n",
       "      <td>240000</td>\n",
       "      <td>1340000</td>\n",
       "      <td>510000</td>\n",
       "      <td>4770000</td>\n",
       "      <td>22160000</td>\n",
       "      <td>9460000</td>\n",
       "      <td>41040000</td>\n",
       "      <td>4000</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L011</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>771</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10대</td>\n",
       "      <td>414000</td>\n",
       "      <td>1.75000</td>\n",
       "      <td>...</td>\n",
       "      <td>490000</td>\n",
       "      <td>340000</td>\n",
       "      <td>1490000</td>\n",
       "      <td>640000</td>\n",
       "      <td>4740000</td>\n",
       "      <td>21960000</td>\n",
       "      <td>9660000</td>\n",
       "      <td>40850000</td>\n",
       "      <td>3000</td>\n",
       "      <td>1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3883</th>\n",
       "      <td>Y95</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>90대</td>\n",
       "      <td>232000</td>\n",
       "      <td>0.11000</td>\n",
       "      <td>...</td>\n",
       "      <td>40000</td>\n",
       "      <td>840000</td>\n",
       "      <td>450000</td>\n",
       "      <td>7290000</td>\n",
       "      <td>21340000</td>\n",
       "      <td>57900000</td>\n",
       "      <td>26130000</td>\n",
       "      <td>98590000</td>\n",
       "      <td>36000</td>\n",
       "      <td>11000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3884</th>\n",
       "      <td>Y95</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>90대</td>\n",
       "      <td>235000</td>\n",
       "      <td>0.11000</td>\n",
       "      <td>...</td>\n",
       "      <td>400000</td>\n",
       "      <td>840000</td>\n",
       "      <td>430000</td>\n",
       "      <td>7110000</td>\n",
       "      <td>21710000</td>\n",
       "      <td>57910000</td>\n",
       "      <td>30360000</td>\n",
       "      <td>99110000</td>\n",
       "      <td>36000</td>\n",
       "      <td>8000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3885</th>\n",
       "      <td>Y95</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>90대</td>\n",
       "      <td>239000</td>\n",
       "      <td>0.11000</td>\n",
       "      <td>...</td>\n",
       "      <td>1190000</td>\n",
       "      <td>790000</td>\n",
       "      <td>450000</td>\n",
       "      <td>6950000</td>\n",
       "      <td>21720000</td>\n",
       "      <td>57370000</td>\n",
       "      <td>32470000</td>\n",
       "      <td>98340000</td>\n",
       "      <td>36000</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3886</th>\n",
       "      <td>Y95</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>90대</td>\n",
       "      <td>243000</td>\n",
       "      <td>0.11000</td>\n",
       "      <td>...</td>\n",
       "      <td>1230000</td>\n",
       "      <td>860000</td>\n",
       "      <td>580000</td>\n",
       "      <td>6850000</td>\n",
       "      <td>21700000</td>\n",
       "      <td>57300000</td>\n",
       "      <td>33480000</td>\n",
       "      <td>97520000</td>\n",
       "      <td>32000</td>\n",
       "      <td>11000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3887</th>\n",
       "      <td>Y95</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>820</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>90대</td>\n",
       "      <td>265000</td>\n",
       "      <td>0.11000</td>\n",
       "      <td>...</td>\n",
       "      <td>1180000</td>\n",
       "      <td>930000</td>\n",
       "      <td>470000</td>\n",
       "      <td>6480000</td>\n",
       "      <td>21260000</td>\n",
       "      <td>58840000</td>\n",
       "      <td>32800000</td>\n",
       "      <td>98500000</td>\n",
       "      <td>35000</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3888 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     pop_cd  year  month  avg_score  avg_rat city  sex ages  population  \\\n",
       "0      L011  2016      1        812        2   서울  NaN  10대       55000   \n",
       "1      L011  2016      2        773        4   서울  NaN  10대      380000   \n",
       "2      L011  2016      3        771        4   서울  NaN  10대      387000   \n",
       "3      L011  2016      4        770        4   서울  NaN  10대      412000   \n",
       "4      L011  2016      5        771        4   서울  NaN  10대      414000   \n",
       "...     ...   ...    ...        ...      ...  ...  ...  ...         ...   \n",
       "3883    Y95  2017      8        819        3  NaN   여자  90대      232000   \n",
       "3884    Y95  2017      9        819        3  NaN   여자  90대      235000   \n",
       "3885    Y95  2017     10        819        3  NaN   여자  90대      239000   \n",
       "3886    Y95  2017     11        819        3  NaN   여자  90대      243000   \n",
       "3887    Y95  2017     12        820        3  NaN   여자  90대      265000   \n",
       "\n",
       "      num_opencard  ...  monthly_installments_loan  monthly_insurance_loan  \\\n",
       "0          1.44000  ...                          0                       0   \n",
       "1          1.71000  ...                     510000                  220000   \n",
       "2          1.75000  ...                     490000                  210000   \n",
       "3          1.76000  ...                     510000                  240000   \n",
       "4          1.75000  ...                     490000                  340000   \n",
       "...            ...  ...                        ...                     ...   \n",
       "3883       0.11000  ...                      40000                  840000   \n",
       "3884       0.11000  ...                     400000                  840000   \n",
       "3885       0.11000  ...                    1190000                  790000   \n",
       "3886       0.11000  ...                    1230000                  860000   \n",
       "3887       0.11000  ...                    1180000                  930000   \n",
       "\n",
       "      monthly_sbk_loan  loan_commitment  inst_rep_loanb  ls_rep_loanb  \\\n",
       "0                    0          1120000         8210000      78020000   \n",
       "1              1020000           680000         4660000      23300000   \n",
       "2              1140000           640000         4370000      22930000   \n",
       "3              1340000           510000         4770000      22160000   \n",
       "4              1490000           640000         4740000      21960000   \n",
       "...                ...              ...             ...           ...   \n",
       "3883            450000          7290000        21340000      57900000   \n",
       "3884            430000          7110000        21710000      57910000   \n",
       "3885            450000          6950000        21720000      57370000   \n",
       "3886            580000          6850000        21700000      57300000   \n",
       "3887            470000          6480000        21260000      58840000   \n",
       "\n",
       "      credit_loan  mortgage_loan  credit_card_payment  \\\n",
       "0        92170000       87260000                    0   \n",
       "1         8260000       42410000                 4000   \n",
       "2         8300000       41710000                 3000   \n",
       "3         9460000       41040000                 4000   \n",
       "4         9660000       40850000                 3000   \n",
       "...           ...            ...                  ...   \n",
       "3883     26130000       98590000                36000   \n",
       "3884     30360000       99110000                36000   \n",
       "3885     32470000       98340000                36000   \n",
       "3886     33480000       97520000                32000   \n",
       "3887     32800000       98500000                35000   \n",
       "\n",
       "      credit_card_installments_payment  \n",
       "0                                    0  \n",
       "1                                 1000  \n",
       "2                                 1000  \n",
       "3                                 1000  \n",
       "4                                 1000  \n",
       "...                                ...  \n",
       "3883                             11000  \n",
       "3884                              8000  \n",
       "3885                             12000  \n",
       "3886                             11000  \n",
       "3887                             10000  \n",
       "\n",
       "[3888 rows x 26 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./dataset/credit_card_data.csv', encoding='utf-8')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### age 범위 선정"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pop_cd                                 0\n",
       "year                                   0\n",
       "month                                  0\n",
       "avg_score                              0\n",
       "avg_rat                                0\n",
       "city                                 432\n",
       "sex                                 3456\n",
       "ages                                   0\n",
       "population                             0\n",
       "num_opencard                           0\n",
       "num_usecard                            0\n",
       "monthly_card_spend                     0\n",
       "monthly_lc                             0\n",
       "monthly_loan                           0\n",
       "monthly_bk_loan                        0\n",
       "monthly_cd_loan                      144\n",
       "monthly_installments_loan            123\n",
       "monthly_insurance_loan               249\n",
       "monthly_sbk_loan                     272\n",
       "loan_commitment                       10\n",
       "inst_rep_loanb                         6\n",
       "ls_rep_loanb                           0\n",
       "credit_loan                           14\n",
       "mortgage_loan                          0\n",
       "credit_card_payment                   18\n",
       "credit_card_installments_payment      23\n",
       "dtype: int64"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = df.replace(0, np.NaN)\n",
    "df1.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['10대' '90대']\n",
      "['10대' '90대']\n",
      "['10대' '90대']\n",
      "['10대' '80대' '90대']\n",
      "['10대']\n",
      "['10대']\n",
      "['10대']\n",
      "['10대']\n",
      "['10대' '90대']\n"
     ]
    }
   ],
   "source": [
    "print(df1[df1['monthly_cd_loan'].isna()]['ages'].unique())\n",
    "print(df1[df1['monthly_installments_loan'].isna()]['ages'].unique())\n",
    "print(df1[df1['monthly_insurance_loan'].isna()]['ages'].unique())\n",
    "print(df1[df1['monthly_sbk_loan'].isna()]['ages'].unique())\n",
    "print(df1[df1['loan_commitment'].isna()]['ages'].unique())\n",
    "print(df1[df1['inst_rep_loanb'].isna()]['ages'].unique())\n",
    "print(df1[df1['credit_loan'].isna()]['ages'].unique())\n",
    "print(df1[df1['credit_card_payment'].isna()]['ages'].unique())\n",
    "print(df1[df1['credit_card_installments_payment'].isna()]['ages'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "10대, 90대의 0인 컬럼이 많고, 대출이라는 특성상 주요타겟층이 아니라고 판단하여\n",
    "\n",
    "10대, 90대를 제외하고 분석"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(df[(df['ages'] == '10대') | (df['ages'] == '90대')].index)\n",
    "df = df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 변수 추가 1) 새로운 신용등급 계산 (20%비율로)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pop_cd</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>avg_score</th>\n",
       "      <th>avg_rat</th>\n",
       "      <th>city</th>\n",
       "      <th>sex</th>\n",
       "      <th>ages</th>\n",
       "      <th>population</th>\n",
       "      <th>num_opencard</th>\n",
       "      <th>...</th>\n",
       "      <th>monthly_insurance_loan</th>\n",
       "      <th>monthly_sbk_loan</th>\n",
       "      <th>loan_commitment</th>\n",
       "      <th>inst_rep_loanb</th>\n",
       "      <th>ls_rep_loanb</th>\n",
       "      <th>credit_loan</th>\n",
       "      <th>mortgage_loan</th>\n",
       "      <th>credit_card_payment</th>\n",
       "      <th>credit_card_installments_payment</th>\n",
       "      <th>new_avg_rat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>740</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1039000</td>\n",
       "      <td>3.93000</td>\n",
       "      <td>...</td>\n",
       "      <td>680000</td>\n",
       "      <td>1320000</td>\n",
       "      <td>3800000</td>\n",
       "      <td>10520000</td>\n",
       "      <td>9750000</td>\n",
       "      <td>14920000</td>\n",
       "      <td>35450000</td>\n",
       "      <td>254000</td>\n",
       "      <td>83000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1678000</td>\n",
       "      <td>3.59000</td>\n",
       "      <td>...</td>\n",
       "      <td>790000</td>\n",
       "      <td>1160000</td>\n",
       "      <td>5420000</td>\n",
       "      <td>12220000</td>\n",
       "      <td>12240000</td>\n",
       "      <td>19070000</td>\n",
       "      <td>42420000</td>\n",
       "      <td>278000</td>\n",
       "      <td>83000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1679000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>...</td>\n",
       "      <td>790000</td>\n",
       "      <td>1210000</td>\n",
       "      <td>5350000</td>\n",
       "      <td>12280000</td>\n",
       "      <td>12370000</td>\n",
       "      <td>19190000</td>\n",
       "      <td>42480000</td>\n",
       "      <td>267000</td>\n",
       "      <td>78000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1685000</td>\n",
       "      <td>3.61000</td>\n",
       "      <td>...</td>\n",
       "      <td>790000</td>\n",
       "      <td>1230000</td>\n",
       "      <td>5380000</td>\n",
       "      <td>12320000</td>\n",
       "      <td>12700000</td>\n",
       "      <td>19360000</td>\n",
       "      <td>42980000</td>\n",
       "      <td>292000</td>\n",
       "      <td>89000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>781</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1682000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>...</td>\n",
       "      <td>770000</td>\n",
       "      <td>1250000</td>\n",
       "      <td>5430000</td>\n",
       "      <td>12420000</td>\n",
       "      <td>12870000</td>\n",
       "      <td>19580000</td>\n",
       "      <td>43370000</td>\n",
       "      <td>285000</td>\n",
       "      <td>82000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3019</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>918000</td>\n",
       "      <td>0.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>1610000</td>\n",
       "      <td>290000</td>\n",
       "      <td>8600000</td>\n",
       "      <td>18710000</td>\n",
       "      <td>41980000</td>\n",
       "      <td>14140000</td>\n",
       "      <td>84270000</td>\n",
       "      <td>82000</td>\n",
       "      <td>43000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3020</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>926000</td>\n",
       "      <td>0.45000</td>\n",
       "      <td>...</td>\n",
       "      <td>1610000</td>\n",
       "      <td>310000</td>\n",
       "      <td>8550000</td>\n",
       "      <td>18750000</td>\n",
       "      <td>41870000</td>\n",
       "      <td>14230000</td>\n",
       "      <td>84120000</td>\n",
       "      <td>82000</td>\n",
       "      <td>39000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3021</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>934000</td>\n",
       "      <td>0.45000</td>\n",
       "      <td>...</td>\n",
       "      <td>1610000</td>\n",
       "      <td>430000</td>\n",
       "      <td>8630000</td>\n",
       "      <td>18760000</td>\n",
       "      <td>41770000</td>\n",
       "      <td>14250000</td>\n",
       "      <td>84200000</td>\n",
       "      <td>87000</td>\n",
       "      <td>49000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3022</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>943000</td>\n",
       "      <td>0.46000</td>\n",
       "      <td>...</td>\n",
       "      <td>1610000</td>\n",
       "      <td>470000</td>\n",
       "      <td>8570000</td>\n",
       "      <td>18690000</td>\n",
       "      <td>42130000</td>\n",
       "      <td>14330000</td>\n",
       "      <td>84390000</td>\n",
       "      <td>80000</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3023</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>996000</td>\n",
       "      <td>0.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>1640000</td>\n",
       "      <td>430000</td>\n",
       "      <td>8590000</td>\n",
       "      <td>18670000</td>\n",
       "      <td>41890000</td>\n",
       "      <td>14240000</td>\n",
       "      <td>84050000</td>\n",
       "      <td>81000</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3024 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     pop_cd  year  month  avg_score  avg_rat city  sex ages  population  \\\n",
       "0      L012  2016      1        740        4   서울  NaN  20대     1039000   \n",
       "1      L012  2016      2        782        4   서울  NaN  20대     1678000   \n",
       "2      L012  2016      3        782        4   서울  NaN  20대     1679000   \n",
       "3      L012  2016      4        782        4   서울  NaN  20대     1685000   \n",
       "4      L012  2016      5        781        4   서울  NaN  20대     1682000   \n",
       "...     ...   ...    ...        ...      ...  ...  ...  ...         ...   \n",
       "3019    Y85  2017      8        818        3  NaN   여자  80대      918000   \n",
       "3020    Y85  2017      9        818        3  NaN   여자  80대      926000   \n",
       "3021    Y85  2017     10        818        3  NaN   여자  80대      934000   \n",
       "3022    Y85  2017     11        819        3  NaN   여자  80대      943000   \n",
       "3023    Y85  2017     12        819        3  NaN   여자  80대      996000   \n",
       "\n",
       "      num_opencard  ...  monthly_insurance_loan  monthly_sbk_loan  \\\n",
       "0          3.93000  ...                  680000           1320000   \n",
       "1          3.59000  ...                  790000           1160000   \n",
       "2          3.60000  ...                  790000           1210000   \n",
       "3          3.61000  ...                  790000           1230000   \n",
       "4          3.60000  ...                  770000           1250000   \n",
       "...            ...  ...                     ...               ...   \n",
       "3019       0.44000  ...                 1610000            290000   \n",
       "3020       0.45000  ...                 1610000            310000   \n",
       "3021       0.45000  ...                 1610000            430000   \n",
       "3022       0.46000  ...                 1610000            470000   \n",
       "3023       0.44000  ...                 1640000            430000   \n",
       "\n",
       "      loan_commitment  inst_rep_loanb  ls_rep_loanb  credit_loan  \\\n",
       "0             3800000        10520000       9750000     14920000   \n",
       "1             5420000        12220000      12240000     19070000   \n",
       "2             5350000        12280000      12370000     19190000   \n",
       "3             5380000        12320000      12700000     19360000   \n",
       "4             5430000        12420000      12870000     19580000   \n",
       "...               ...             ...           ...          ...   \n",
       "3019          8600000        18710000      41980000     14140000   \n",
       "3020          8550000        18750000      41870000     14230000   \n",
       "3021          8630000        18760000      41770000     14250000   \n",
       "3022          8570000        18690000      42130000     14330000   \n",
       "3023          8590000        18670000      41890000     14240000   \n",
       "\n",
       "      mortgage_loan  credit_card_payment  credit_card_installments_payment  \\\n",
       "0          35450000               254000                             83000   \n",
       "1          42420000               278000                             83000   \n",
       "2          42480000               267000                             78000   \n",
       "3          42980000               292000                             89000   \n",
       "4          43370000               285000                             82000   \n",
       "...             ...                  ...                               ...   \n",
       "3019       84270000                82000                             43000   \n",
       "3020       84120000                82000                             39000   \n",
       "3021       84200000                87000                             49000   \n",
       "3022       84390000                80000                             44000   \n",
       "3023       84050000                81000                             44000   \n",
       "\n",
       "      new_avg_rat  \n",
       "0               5  \n",
       "1               5  \n",
       "2               5  \n",
       "3               5  \n",
       "4               5  \n",
       "...           ...  \n",
       "3019            2  \n",
       "3020            2  \n",
       "3021            2  \n",
       "3022            2  \n",
       "3023            2  \n",
       "\n",
       "[3024 rows x 27 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "score_max = df['avg_score'].max()+1\n",
    "score_20 = df['avg_score'].quantile(0.2)\n",
    "score_40 = df['avg_score'].quantile(0.4)\n",
    "score_60 = df['avg_score'].quantile(0.6)\n",
    "score_80 = df['avg_score'].quantile(0.8)\n",
    "bins = [score_max, score_80, score_60, score_40, score_20]\n",
    "df['new_avg_rat'] = np.digitize(df['avg_score'], bins)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 변수 추가 2) 체크카드 사용량"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "월 카드 총 이용금액(monthly_card_spend) –(신용카드 일시불 이용금액(credit_card_payment) - 신용카드 할부 이용금액(credit_card_installments_payment))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead 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>pop_cd</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>avg_score</th>\n",
       "      <th>avg_rat</th>\n",
       "      <th>city</th>\n",
       "      <th>sex</th>\n",
       "      <th>ages</th>\n",
       "      <th>population</th>\n",
       "      <th>num_opencard</th>\n",
       "      <th>...</th>\n",
       "      <th>monthly_sbk_loan</th>\n",
       "      <th>loan_commitment</th>\n",
       "      <th>inst_rep_loanb</th>\n",
       "      <th>ls_rep_loanb</th>\n",
       "      <th>credit_loan</th>\n",
       "      <th>mortgage_loan</th>\n",
       "      <th>credit_card_payment</th>\n",
       "      <th>credit_card_installments_payment</th>\n",
       "      <th>new_avg_rat</th>\n",
       "      <th>debit_card_payment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>740</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1039000</td>\n",
       "      <td>3.93000</td>\n",
       "      <td>...</td>\n",
       "      <td>1320000</td>\n",
       "      <td>3800000</td>\n",
       "      <td>10520000</td>\n",
       "      <td>9750000</td>\n",
       "      <td>14920000</td>\n",
       "      <td>35450000</td>\n",
       "      <td>254000</td>\n",
       "      <td>83000</td>\n",
       "      <td>5</td>\n",
       "      <td>473000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1678000</td>\n",
       "      <td>3.59000</td>\n",
       "      <td>...</td>\n",
       "      <td>1160000</td>\n",
       "      <td>5420000</td>\n",
       "      <td>12220000</td>\n",
       "      <td>12240000</td>\n",
       "      <td>19070000</td>\n",
       "      <td>42420000</td>\n",
       "      <td>278000</td>\n",
       "      <td>83000</td>\n",
       "      <td>5</td>\n",
       "      <td>429000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1679000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>...</td>\n",
       "      <td>1210000</td>\n",
       "      <td>5350000</td>\n",
       "      <td>12280000</td>\n",
       "      <td>12370000</td>\n",
       "      <td>19190000</td>\n",
       "      <td>42480000</td>\n",
       "      <td>267000</td>\n",
       "      <td>78000</td>\n",
       "      <td>5</td>\n",
       "      <td>445000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1685000</td>\n",
       "      <td>3.61000</td>\n",
       "      <td>...</td>\n",
       "      <td>1230000</td>\n",
       "      <td>5380000</td>\n",
       "      <td>12320000</td>\n",
       "      <td>12700000</td>\n",
       "      <td>19360000</td>\n",
       "      <td>42980000</td>\n",
       "      <td>292000</td>\n",
       "      <td>89000</td>\n",
       "      <td>5</td>\n",
       "      <td>479000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>781</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1682000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>...</td>\n",
       "      <td>1250000</td>\n",
       "      <td>5430000</td>\n",
       "      <td>12420000</td>\n",
       "      <td>12870000</td>\n",
       "      <td>19580000</td>\n",
       "      <td>43370000</td>\n",
       "      <td>285000</td>\n",
       "      <td>82000</td>\n",
       "      <td>5</td>\n",
       "      <td>453000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3019</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>918000</td>\n",
       "      <td>0.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>290000</td>\n",
       "      <td>8600000</td>\n",
       "      <td>18710000</td>\n",
       "      <td>41980000</td>\n",
       "      <td>14140000</td>\n",
       "      <td>84270000</td>\n",
       "      <td>82000</td>\n",
       "      <td>43000</td>\n",
       "      <td>2</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3020</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>926000</td>\n",
       "      <td>0.45000</td>\n",
       "      <td>...</td>\n",
       "      <td>310000</td>\n",
       "      <td>8550000</td>\n",
       "      <td>18750000</td>\n",
       "      <td>41870000</td>\n",
       "      <td>14230000</td>\n",
       "      <td>84120000</td>\n",
       "      <td>82000</td>\n",
       "      <td>39000</td>\n",
       "      <td>2</td>\n",
       "      <td>69000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3021</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>934000</td>\n",
       "      <td>0.45000</td>\n",
       "      <td>...</td>\n",
       "      <td>430000</td>\n",
       "      <td>8630000</td>\n",
       "      <td>18760000</td>\n",
       "      <td>41770000</td>\n",
       "      <td>14250000</td>\n",
       "      <td>84200000</td>\n",
       "      <td>87000</td>\n",
       "      <td>49000</td>\n",
       "      <td>2</td>\n",
       "      <td>74000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3022</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>943000</td>\n",
       "      <td>0.46000</td>\n",
       "      <td>...</td>\n",
       "      <td>470000</td>\n",
       "      <td>8570000</td>\n",
       "      <td>18690000</td>\n",
       "      <td>42130000</td>\n",
       "      <td>14330000</td>\n",
       "      <td>84390000</td>\n",
       "      <td>80000</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "      <td>76000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3023</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>996000</td>\n",
       "      <td>0.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>430000</td>\n",
       "      <td>8590000</td>\n",
       "      <td>18670000</td>\n",
       "      <td>41890000</td>\n",
       "      <td>14240000</td>\n",
       "      <td>84050000</td>\n",
       "      <td>81000</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "      <td>75000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3024 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     pop_cd  year  month  avg_score  avg_rat city  sex ages  population  \\\n",
       "0      L012  2016      1        740        4   서울  NaN  20대     1039000   \n",
       "1      L012  2016      2        782        4   서울  NaN  20대     1678000   \n",
       "2      L012  2016      3        782        4   서울  NaN  20대     1679000   \n",
       "3      L012  2016      4        782        4   서울  NaN  20대     1685000   \n",
       "4      L012  2016      5        781        4   서울  NaN  20대     1682000   \n",
       "...     ...   ...    ...        ...      ...  ...  ...  ...         ...   \n",
       "3019    Y85  2017      8        818        3  NaN   여자  80대      918000   \n",
       "3020    Y85  2017      9        818        3  NaN   여자  80대      926000   \n",
       "3021    Y85  2017     10        818        3  NaN   여자  80대      934000   \n",
       "3022    Y85  2017     11        819        3  NaN   여자  80대      943000   \n",
       "3023    Y85  2017     12        819        3  NaN   여자  80대      996000   \n",
       "\n",
       "      num_opencard  ...  monthly_sbk_loan  loan_commitment  inst_rep_loanb  \\\n",
       "0          3.93000  ...           1320000          3800000        10520000   \n",
       "1          3.59000  ...           1160000          5420000        12220000   \n",
       "2          3.60000  ...           1210000          5350000        12280000   \n",
       "3          3.61000  ...           1230000          5380000        12320000   \n",
       "4          3.60000  ...           1250000          5430000        12420000   \n",
       "...            ...  ...               ...              ...             ...   \n",
       "3019       0.44000  ...            290000          8600000        18710000   \n",
       "3020       0.45000  ...            310000          8550000        18750000   \n",
       "3021       0.45000  ...            430000          8630000        18760000   \n",
       "3022       0.46000  ...            470000          8570000        18690000   \n",
       "3023       0.44000  ...            430000          8590000        18670000   \n",
       "\n",
       "      ls_rep_loanb  credit_loan  mortgage_loan  credit_card_payment  \\\n",
       "0          9750000     14920000       35450000               254000   \n",
       "1         12240000     19070000       42420000               278000   \n",
       "2         12370000     19190000       42480000               267000   \n",
       "3         12700000     19360000       42980000               292000   \n",
       "4         12870000     19580000       43370000               285000   \n",
       "...            ...          ...            ...                  ...   \n",
       "3019      41980000     14140000       84270000                82000   \n",
       "3020      41870000     14230000       84120000                82000   \n",
       "3021      41770000     14250000       84200000                87000   \n",
       "3022      42130000     14330000       84390000                80000   \n",
       "3023      41890000     14240000       84050000                81000   \n",
       "\n",
       "      credit_card_installments_payment  new_avg_rat  debit_card_payment  \n",
       "0                                83000            5              473000  \n",
       "1                                83000            5              429000  \n",
       "2                                78000            5              445000  \n",
       "3                                89000            5              479000  \n",
       "4                                82000            5              453000  \n",
       "...                                ...          ...                 ...  \n",
       "3019                             43000            2               75000  \n",
       "3020                             39000            2               69000  \n",
       "3021                             49000            2               74000  \n",
       "3022                             44000            2               76000  \n",
       "3023                             44000            2               75000  \n",
       "\n",
       "[3024 rows x 28 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['debit_card_payment'] = df['monthly_card_spend'] - df['credit_card_payment'] - df['credit_card_installments_payment']\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     3024.00000\n",
       "mean    304507.60582\n",
       "std     159245.42734\n",
       "min      24000.00000\n",
       "25%     137000.00000\n",
       "50%     361500.00000\n",
       "75%     447000.00000\n",
       "max     587000.00000\n",
       "Name: debit_card_payment, dtype: float64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['debit_card_payment'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 변수 추가 3) 한 달 이내에 사용 가능한 소비량 (20%씩 범주화)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "소비량 = 신용카드 일시불 사용량 + 체크카드 사용량"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pop_cd</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>avg_score</th>\n",
       "      <th>avg_rat</th>\n",
       "      <th>city</th>\n",
       "      <th>sex</th>\n",
       "      <th>ages</th>\n",
       "      <th>population</th>\n",
       "      <th>num_opencard</th>\n",
       "      <th>...</th>\n",
       "      <th>loan_commitment</th>\n",
       "      <th>inst_rep_loanb</th>\n",
       "      <th>ls_rep_loanb</th>\n",
       "      <th>credit_loan</th>\n",
       "      <th>mortgage_loan</th>\n",
       "      <th>credit_card_payment</th>\n",
       "      <th>credit_card_installments_payment</th>\n",
       "      <th>new_avg_rat</th>\n",
       "      <th>debit_card_payment</th>\n",
       "      <th>monthly_spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>740</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1039000</td>\n",
       "      <td>3.93000</td>\n",
       "      <td>...</td>\n",
       "      <td>3800000</td>\n",
       "      <td>10520000</td>\n",
       "      <td>9750000</td>\n",
       "      <td>14920000</td>\n",
       "      <td>35450000</td>\n",
       "      <td>254000</td>\n",
       "      <td>83000</td>\n",
       "      <td>5</td>\n",
       "      <td>473000</td>\n",
       "      <td>727000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1678000</td>\n",
       "      <td>3.59000</td>\n",
       "      <td>...</td>\n",
       "      <td>5420000</td>\n",
       "      <td>12220000</td>\n",
       "      <td>12240000</td>\n",
       "      <td>19070000</td>\n",
       "      <td>42420000</td>\n",
       "      <td>278000</td>\n",
       "      <td>83000</td>\n",
       "      <td>5</td>\n",
       "      <td>429000</td>\n",
       "      <td>707000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1679000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>...</td>\n",
       "      <td>5350000</td>\n",
       "      <td>12280000</td>\n",
       "      <td>12370000</td>\n",
       "      <td>19190000</td>\n",
       "      <td>42480000</td>\n",
       "      <td>267000</td>\n",
       "      <td>78000</td>\n",
       "      <td>5</td>\n",
       "      <td>445000</td>\n",
       "      <td>712000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1685000</td>\n",
       "      <td>3.61000</td>\n",
       "      <td>...</td>\n",
       "      <td>5380000</td>\n",
       "      <td>12320000</td>\n",
       "      <td>12700000</td>\n",
       "      <td>19360000</td>\n",
       "      <td>42980000</td>\n",
       "      <td>292000</td>\n",
       "      <td>89000</td>\n",
       "      <td>5</td>\n",
       "      <td>479000</td>\n",
       "      <td>771000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>781</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1682000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>...</td>\n",
       "      <td>5430000</td>\n",
       "      <td>12420000</td>\n",
       "      <td>12870000</td>\n",
       "      <td>19580000</td>\n",
       "      <td>43370000</td>\n",
       "      <td>285000</td>\n",
       "      <td>82000</td>\n",
       "      <td>5</td>\n",
       "      <td>453000</td>\n",
       "      <td>738000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3019</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>918000</td>\n",
       "      <td>0.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>8600000</td>\n",
       "      <td>18710000</td>\n",
       "      <td>41980000</td>\n",
       "      <td>14140000</td>\n",
       "      <td>84270000</td>\n",
       "      <td>82000</td>\n",
       "      <td>43000</td>\n",
       "      <td>2</td>\n",
       "      <td>75000</td>\n",
       "      <td>157000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3020</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>926000</td>\n",
       "      <td>0.45000</td>\n",
       "      <td>...</td>\n",
       "      <td>8550000</td>\n",
       "      <td>18750000</td>\n",
       "      <td>41870000</td>\n",
       "      <td>14230000</td>\n",
       "      <td>84120000</td>\n",
       "      <td>82000</td>\n",
       "      <td>39000</td>\n",
       "      <td>2</td>\n",
       "      <td>69000</td>\n",
       "      <td>151000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3021</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>934000</td>\n",
       "      <td>0.45000</td>\n",
       "      <td>...</td>\n",
       "      <td>8630000</td>\n",
       "      <td>18760000</td>\n",
       "      <td>41770000</td>\n",
       "      <td>14250000</td>\n",
       "      <td>84200000</td>\n",
       "      <td>87000</td>\n",
       "      <td>49000</td>\n",
       "      <td>2</td>\n",
       "      <td>74000</td>\n",
       "      <td>161000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3022</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>943000</td>\n",
       "      <td>0.46000</td>\n",
       "      <td>...</td>\n",
       "      <td>8570000</td>\n",
       "      <td>18690000</td>\n",
       "      <td>42130000</td>\n",
       "      <td>14330000</td>\n",
       "      <td>84390000</td>\n",
       "      <td>80000</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "      <td>76000</td>\n",
       "      <td>156000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3023</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>996000</td>\n",
       "      <td>0.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>8590000</td>\n",
       "      <td>18670000</td>\n",
       "      <td>41890000</td>\n",
       "      <td>14240000</td>\n",
       "      <td>84050000</td>\n",
       "      <td>81000</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "      <td>75000</td>\n",
       "      <td>156000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3024 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     pop_cd  year  month  avg_score  avg_rat city  sex ages  population  \\\n",
       "0      L012  2016      1        740        4   서울  NaN  20대     1039000   \n",
       "1      L012  2016      2        782        4   서울  NaN  20대     1678000   \n",
       "2      L012  2016      3        782        4   서울  NaN  20대     1679000   \n",
       "3      L012  2016      4        782        4   서울  NaN  20대     1685000   \n",
       "4      L012  2016      5        781        4   서울  NaN  20대     1682000   \n",
       "...     ...   ...    ...        ...      ...  ...  ...  ...         ...   \n",
       "3019    Y85  2017      8        818        3  NaN   여자  80대      918000   \n",
       "3020    Y85  2017      9        818        3  NaN   여자  80대      926000   \n",
       "3021    Y85  2017     10        818        3  NaN   여자  80대      934000   \n",
       "3022    Y85  2017     11        819        3  NaN   여자  80대      943000   \n",
       "3023    Y85  2017     12        819        3  NaN   여자  80대      996000   \n",
       "\n",
       "      num_opencard  ...  loan_commitment  inst_rep_loanb  ls_rep_loanb  \\\n",
       "0          3.93000  ...          3800000        10520000       9750000   \n",
       "1          3.59000  ...          5420000        12220000      12240000   \n",
       "2          3.60000  ...          5350000        12280000      12370000   \n",
       "3          3.61000  ...          5380000        12320000      12700000   \n",
       "4          3.60000  ...          5430000        12420000      12870000   \n",
       "...            ...  ...              ...             ...           ...   \n",
       "3019       0.44000  ...          8600000        18710000      41980000   \n",
       "3020       0.45000  ...          8550000        18750000      41870000   \n",
       "3021       0.45000  ...          8630000        18760000      41770000   \n",
       "3022       0.46000  ...          8570000        18690000      42130000   \n",
       "3023       0.44000  ...          8590000        18670000      41890000   \n",
       "\n",
       "      credit_loan  mortgage_loan  credit_card_payment  \\\n",
       "0        14920000       35450000               254000   \n",
       "1        19070000       42420000               278000   \n",
       "2        19190000       42480000               267000   \n",
       "3        19360000       42980000               292000   \n",
       "4        19580000       43370000               285000   \n",
       "...           ...            ...                  ...   \n",
       "3019     14140000       84270000                82000   \n",
       "3020     14230000       84120000                82000   \n",
       "3021     14250000       84200000                87000   \n",
       "3022     14330000       84390000                80000   \n",
       "3023     14240000       84050000                81000   \n",
       "\n",
       "      credit_card_installments_payment  new_avg_rat  debit_card_payment  \\\n",
       "0                                83000            5              473000   \n",
       "1                                83000            5              429000   \n",
       "2                                78000            5              445000   \n",
       "3                                89000            5              479000   \n",
       "4                                82000            5              453000   \n",
       "...                                ...          ...                 ...   \n",
       "3019                             43000            2               75000   \n",
       "3020                             39000            2               69000   \n",
       "3021                             49000            2               74000   \n",
       "3022                             44000            2               76000   \n",
       "3023                             44000            2               75000   \n",
       "\n",
       "      monthly_spend  \n",
       "0            727000  \n",
       "1            707000  \n",
       "2            712000  \n",
       "3            771000  \n",
       "4            738000  \n",
       "...             ...  \n",
       "3019         157000  \n",
       "3020         151000  \n",
       "3021         161000  \n",
       "3022         156000  \n",
       "3023         156000  \n",
       "\n",
       "[3024 rows x 29 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['monthly_spend'] = df['credit_card_payment'] + df['debit_card_payment']\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count      3024.00000\n",
       "mean     838217.92328\n",
       "std      474981.12482\n",
       "min       43000.00000\n",
       "25%      417000.00000\n",
       "50%      755000.00000\n",
       "75%     1282000.00000\n",
       "max     1914000.00000\n",
       "Name: monthly_spend, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['monthly_spend'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pop_cd</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>avg_score</th>\n",
       "      <th>avg_rat</th>\n",
       "      <th>city</th>\n",
       "      <th>sex</th>\n",
       "      <th>ages</th>\n",
       "      <th>population</th>\n",
       "      <th>num_opencard</th>\n",
       "      <th>...</th>\n",
       "      <th>inst_rep_loanb</th>\n",
       "      <th>ls_rep_loanb</th>\n",
       "      <th>credit_loan</th>\n",
       "      <th>mortgage_loan</th>\n",
       "      <th>credit_card_payment</th>\n",
       "      <th>credit_card_installments_payment</th>\n",
       "      <th>new_avg_rat</th>\n",
       "      <th>debit_card_payment</th>\n",
       "      <th>monthly_spend</th>\n",
       "      <th>monthly_spend_rat</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>740</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1039000</td>\n",
       "      <td>3.93000</td>\n",
       "      <td>...</td>\n",
       "      <td>10520000</td>\n",
       "      <td>9750000</td>\n",
       "      <td>14920000</td>\n",
       "      <td>35450000</td>\n",
       "      <td>254000</td>\n",
       "      <td>83000</td>\n",
       "      <td>5</td>\n",
       "      <td>473000</td>\n",
       "      <td>727000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1678000</td>\n",
       "      <td>3.59000</td>\n",
       "      <td>...</td>\n",
       "      <td>12220000</td>\n",
       "      <td>12240000</td>\n",
       "      <td>19070000</td>\n",
       "      <td>42420000</td>\n",
       "      <td>278000</td>\n",
       "      <td>83000</td>\n",
       "      <td>5</td>\n",
       "      <td>429000</td>\n",
       "      <td>707000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1679000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>...</td>\n",
       "      <td>12280000</td>\n",
       "      <td>12370000</td>\n",
       "      <td>19190000</td>\n",
       "      <td>42480000</td>\n",
       "      <td>267000</td>\n",
       "      <td>78000</td>\n",
       "      <td>5</td>\n",
       "      <td>445000</td>\n",
       "      <td>712000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>4</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1685000</td>\n",
       "      <td>3.61000</td>\n",
       "      <td>...</td>\n",
       "      <td>12320000</td>\n",
       "      <td>12700000</td>\n",
       "      <td>19360000</td>\n",
       "      <td>42980000</td>\n",
       "      <td>292000</td>\n",
       "      <td>89000</td>\n",
       "      <td>5</td>\n",
       "      <td>479000</td>\n",
       "      <td>771000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>781</td>\n",
       "      <td>4</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>1682000</td>\n",
       "      <td>3.60000</td>\n",
       "      <td>...</td>\n",
       "      <td>12420000</td>\n",
       "      <td>12870000</td>\n",
       "      <td>19580000</td>\n",
       "      <td>43370000</td>\n",
       "      <td>285000</td>\n",
       "      <td>82000</td>\n",
       "      <td>5</td>\n",
       "      <td>453000</td>\n",
       "      <td>738000</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3019</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>8</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>918000</td>\n",
       "      <td>0.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>18710000</td>\n",
       "      <td>41980000</td>\n",
       "      <td>14140000</td>\n",
       "      <td>84270000</td>\n",
       "      <td>82000</td>\n",
       "      <td>43000</td>\n",
       "      <td>2</td>\n",
       "      <td>75000</td>\n",
       "      <td>157000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3020</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>9</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>926000</td>\n",
       "      <td>0.45000</td>\n",
       "      <td>...</td>\n",
       "      <td>18750000</td>\n",
       "      <td>41870000</td>\n",
       "      <td>14230000</td>\n",
       "      <td>84120000</td>\n",
       "      <td>82000</td>\n",
       "      <td>39000</td>\n",
       "      <td>2</td>\n",
       "      <td>69000</td>\n",
       "      <td>151000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3021</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>10</td>\n",
       "      <td>818</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>934000</td>\n",
       "      <td>0.45000</td>\n",
       "      <td>...</td>\n",
       "      <td>18760000</td>\n",
       "      <td>41770000</td>\n",
       "      <td>14250000</td>\n",
       "      <td>84200000</td>\n",
       "      <td>87000</td>\n",
       "      <td>49000</td>\n",
       "      <td>2</td>\n",
       "      <td>74000</td>\n",
       "      <td>161000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3022</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>11</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>943000</td>\n",
       "      <td>0.46000</td>\n",
       "      <td>...</td>\n",
       "      <td>18690000</td>\n",
       "      <td>42130000</td>\n",
       "      <td>14330000</td>\n",
       "      <td>84390000</td>\n",
       "      <td>80000</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "      <td>76000</td>\n",
       "      <td>156000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3023</th>\n",
       "      <td>Y85</td>\n",
       "      <td>2017</td>\n",
       "      <td>12</td>\n",
       "      <td>819</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>여자</td>\n",
       "      <td>80대</td>\n",
       "      <td>996000</td>\n",
       "      <td>0.44000</td>\n",
       "      <td>...</td>\n",
       "      <td>18670000</td>\n",
       "      <td>41890000</td>\n",
       "      <td>14240000</td>\n",
       "      <td>84050000</td>\n",
       "      <td>81000</td>\n",
       "      <td>44000</td>\n",
       "      <td>2</td>\n",
       "      <td>75000</td>\n",
       "      <td>156000</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3024 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     pop_cd  year  month  avg_score  avg_rat city  sex ages  population  \\\n",
       "0      L012  2016      1        740        4   서울  NaN  20대     1039000   \n",
       "1      L012  2016      2        782        4   서울  NaN  20대     1678000   \n",
       "2      L012  2016      3        782        4   서울  NaN  20대     1679000   \n",
       "3      L012  2016      4        782        4   서울  NaN  20대     1685000   \n",
       "4      L012  2016      5        781        4   서울  NaN  20대     1682000   \n",
       "...     ...   ...    ...        ...      ...  ...  ...  ...         ...   \n",
       "3019    Y85  2017      8        818        3  NaN   여자  80대      918000   \n",
       "3020    Y85  2017      9        818        3  NaN   여자  80대      926000   \n",
       "3021    Y85  2017     10        818        3  NaN   여자  80대      934000   \n",
       "3022    Y85  2017     11        819        3  NaN   여자  80대      943000   \n",
       "3023    Y85  2017     12        819        3  NaN   여자  80대      996000   \n",
       "\n",
       "      num_opencard  ...  inst_rep_loanb  ls_rep_loanb  credit_loan  \\\n",
       "0          3.93000  ...        10520000       9750000     14920000   \n",
       "1          3.59000  ...        12220000      12240000     19070000   \n",
       "2          3.60000  ...        12280000      12370000     19190000   \n",
       "3          3.61000  ...        12320000      12700000     19360000   \n",
       "4          3.60000  ...        12420000      12870000     19580000   \n",
       "...            ...  ...             ...           ...          ...   \n",
       "3019       0.44000  ...        18710000      41980000     14140000   \n",
       "3020       0.45000  ...        18750000      41870000     14230000   \n",
       "3021       0.45000  ...        18760000      41770000     14250000   \n",
       "3022       0.46000  ...        18690000      42130000     14330000   \n",
       "3023       0.44000  ...        18670000      41890000     14240000   \n",
       "\n",
       "      mortgage_loan  credit_card_payment  credit_card_installments_payment  \\\n",
       "0          35450000               254000                             83000   \n",
       "1          42420000               278000                             83000   \n",
       "2          42480000               267000                             78000   \n",
       "3          42980000               292000                             89000   \n",
       "4          43370000               285000                             82000   \n",
       "...             ...                  ...                               ...   \n",
       "3019       84270000                82000                             43000   \n",
       "3020       84120000                82000                             39000   \n",
       "3021       84200000                87000                             49000   \n",
       "3022       84390000                80000                             44000   \n",
       "3023       84050000                81000                             44000   \n",
       "\n",
       "      new_avg_rat  debit_card_payment  monthly_spend  monthly_spend_rat  \n",
       "0               5              473000         727000                  3  \n",
       "1               5              429000         707000                  3  \n",
       "2               5              445000         712000                  3  \n",
       "3               5              479000         771000                  3  \n",
       "4               5              453000         738000                  3  \n",
       "...           ...                 ...            ...                ...  \n",
       "3019            2               75000         157000                  5  \n",
       "3020            2               69000         151000                  5  \n",
       "3021            2               74000         161000                  5  \n",
       "3022            2               76000         156000                  5  \n",
       "3023            2               75000         156000                  5  \n",
       "\n",
       "[3024 rows x 30 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 20% 비율로 범주화\n",
    "spend_max = df['monthly_spend'].max()+1\n",
    "spend_20 = df['monthly_spend'].quantile(0.2)\n",
    "spend_40 = df['monthly_spend'].quantile(0.4)\n",
    "spend_60 = df['monthly_spend'].quantile(0.6)\n",
    "spend_80 = df['monthly_spend'].quantile(0.8)\n",
    "bins = [spend_max, spend_80, spend_60, spend_40, spend_20]\n",
    "df['monthly_spend_rat'] = np.digitize(df['monthly_spend'], bins)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "monthly_spend_rat\n",
       "1    606\n",
       "2    604\n",
       "3    606\n",
       "4    603\n",
       "5    605\n",
       "Name: pop_cd, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('monthly_spend_rat').count()['pop_cd']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 컬럼 순서 변경\n",
    "df = df[['pop_cd', 'year', 'month', 'avg_score', 'avg_rat', 'new_avg_rat', 'city', 'sex',\n",
    "       'ages', 'monthly_spend_rat', 'population', 'num_opencard', 'num_usecard',\n",
    "       'monthly_card_spend', 'monthly_lc', 'monthly_loan', 'monthly_bk_loan',\n",
    "       'monthly_cd_loan', 'monthly_installments_loan',\n",
    "       'monthly_insurance_loan', 'monthly_sbk_loan', 'loan_commitment',\n",
    "       'inst_rep_loanb', 'ls_rep_loanb', 'credit_loan', 'mortgage_loan',\n",
    "       'credit_card_payment', 'credit_card_installments_payment',\n",
    "       'debit_card_payment', 'monthly_spend']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 컬럼 * population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pop_cd</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>avg_score</th>\n",
       "      <th>avg_rat</th>\n",
       "      <th>new_avg_rat</th>\n",
       "      <th>city</th>\n",
       "      <th>sex</th>\n",
       "      <th>ages</th>\n",
       "      <th>monthly_spend_rat</th>\n",
       "      <th>...</th>\n",
       "      <th>total_monthly_sbk_loan</th>\n",
       "      <th>total_loan_commitment</th>\n",
       "      <th>total_inst_rep_loanb</th>\n",
       "      <th>total_ls_rep_loanb</th>\n",
       "      <th>total_credit_loan</th>\n",
       "      <th>total_mortgage_loan</th>\n",
       "      <th>total_credit_card_payment</th>\n",
       "      <th>total_credit_card_installments_payment</th>\n",
       "      <th>total_debit_card_payment</th>\n",
       "      <th>total_monthly_spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>1</td>\n",
       "      <td>740</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1371480000000</td>\n",
       "      <td>3948200000000</td>\n",
       "      <td>10930280000000</td>\n",
       "      <td>10130250000000</td>\n",
       "      <td>15501880000000</td>\n",
       "      <td>36832550000000</td>\n",
       "      <td>263906000000</td>\n",
       "      <td>86237000000</td>\n",
       "      <td>491447000000</td>\n",
       "      <td>755353000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>2</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>1946480000000</td>\n",
       "      <td>9094760000000</td>\n",
       "      <td>20505160000000</td>\n",
       "      <td>20538720000000</td>\n",
       "      <td>31999460000000</td>\n",
       "      <td>71180760000000</td>\n",
       "      <td>466484000000</td>\n",
       "      <td>139274000000</td>\n",
       "      <td>719862000000</td>\n",
       "      <td>1186346000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>L012</td>\n",
       "      <td>2016</td>\n",
       "      <td>3</td>\n",
       "      <td>782</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>서울</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20대</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>2031590000000</td>\n",
       "      <td>8982650000000</td>\n",
       "      <td>20618120000000</td>\n",
       "      <td>20769230000000</td>\n",
       "      <td>32220010000000</td>\n",
       "      <td>71323920000000</td>\n",
       "      <td>448293000000</td>\n",
       "      <td>130962000000</td>\n",
       "      <td>747155000000</td>\n",
       "      <td>1195448000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  pop_cd  year  month  avg_score  avg_rat  new_avg_rat city  sex ages  \\\n",
       "0   L012  2016      1        740        4            5   서울  NaN  20대   \n",
       "1   L012  2016      2        782        4            5   서울  NaN  20대   \n",
       "2   L012  2016      3        782        4            5   서울  NaN  20대   \n",
       "\n",
       "   monthly_spend_rat  ...  total_monthly_sbk_loan  total_loan_commitment  \\\n",
       "0                  3  ...           1371480000000          3948200000000   \n",
       "1                  3  ...           1946480000000          9094760000000   \n",
       "2                  3  ...           2031590000000          8982650000000   \n",
       "\n",
       "   total_inst_rep_loanb  total_ls_rep_loanb  total_credit_loan  \\\n",
       "0        10930280000000      10130250000000     15501880000000   \n",
       "1        20505160000000      20538720000000     31999460000000   \n",
       "2        20618120000000      20769230000000     32220010000000   \n",
       "\n",
       "   total_mortgage_loan  total_credit_card_payment  \\\n",
       "0       36832550000000               263906000000   \n",
       "1       71180760000000               466484000000   \n",
       "2       71323920000000               448293000000   \n",
       "\n",
       "   total_credit_card_installments_payment  total_debit_card_payment  \\\n",
       "0                             86237000000              491447000000   \n",
       "1                            139274000000              719862000000   \n",
       "2                            130962000000              747155000000   \n",
       "\n",
       "   total_monthly_spend  \n",
       "0         755353000000  \n",
       "1        1186346000000  \n",
       "2        1195448000000  \n",
       "\n",
       "[3 rows x 32 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_total = df.iloc[:, :11]\n",
    "for col in df.columns[3:]:\n",
    "    if type(df['{}'.format(col)][0]) in [np.int64, np.float64, np.int32] and col!='population':\n",
    "        colname = 'total_' + col\n",
    "        df_total['{}'.format(colname)] = df['{}'.format(col)] * df.population\n",
    "df_total.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 시계열을 위해 '년-월-일' 형식으로 변경, 매월 1일로 임의 설정\n",
    "df['year'] = df['year'].apply(str)\n",
    "df['month'] = df['month'].apply(str)\n",
    "df['date'] = df['year'] + ' ,' + df['month'] + ' ,1'\n",
    "df['date'] = pd.to_datetime(df['date'])\n",
    "\n",
    "df_total['year'] = df_total['year'].apply(str)\n",
    "df_total['month'] = df_total['month'].apply(str)\n",
    "df_total['date'] = df_total['year'] + ' ,' + df_total['month'] + ' ,1'\n",
    "df_total['date'] = pd.to_datetime(df_total['date'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "도시별, 성별 데이터프레임 분리"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 도시별, 성별 데이터프레임 분리\n",
    "city_ages = df[df['pop_cd'].str.startswith('L')]\n",
    "sex_ages = df[df['pop_cd'].str.startswith('Y')]\n",
    "# 필요없는 컬럼 삭제\n",
    "city_ages = city_ages.drop('sex', axis=1)\n",
    "sex_ages = sex_ages.drop('city', axis=1)\n",
    "\n",
    "# 도시별, 성별 데이터프레임 분리\n",
    "city_ages_total = df_total[df_total['pop_cd'].str.startswith('L')]\n",
    "sex_ages_total = df_total[df_total['pop_cd'].str.startswith('Y')]\n",
    "# 필요없는 컬럼 삭제\n",
    "city_ages_total = city_ages_total.drop('sex', axis=1)\n",
    "sex_ages_total = sex_ages_total.drop('city', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### dataset -> csv 저장"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('./dataset/credit_card_dataset.csv', encoding='utf-8', index=False)\n",
    "df_total.to_csv('./dataset/credit_card_dataset_total.csv', encoding='utf-8', index=False)\n",
    "city_ages.to_csv('./dataset/credit_card_dataset_city.csv', encoding='utf-8', index=False)\n",
    "sex_ages.to_csv('./dataset/credit_card_dataset_sex.csv', encoding='utf-8', index=False)\n",
    "city_ages_total.to_csv('./dataset/credit_card_dataset_city_total.csv', encoding='utf-8', index=False)\n",
    "sex_ages_total.to_csv('./dataset/credit_card_dataset_sex_total.csv', encoding='utf-8', index=False)"
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "cb5285e2cda14a4805400a189e0eabfa02913f397385f5c221a1fc076b50ea98"
  },
  "kernelspec": {
   "display_name": "Python 3.7.12 ('base')",
   "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.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}