bureau_check.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['prev_check2_clean.csv',\n",
       " 'application_test.csv',\n",
       " '.DS_Store',\n",
       " 'HomeCredit_columns_description.csv',\n",
       " 'POS_CASH_balance.csv',\n",
       " 'credit_card_balance.csv',\n",
       " 'installments_payments.csv',\n",
       " 'application_train.csv',\n",
       " 'bureau.csv',\n",
       " 'bu_clean.csv',\n",
       " 'prev_check1_clean.csv',\n",
       " 'previous_application.csv',\n",
       " 'HomeCredit_columns_description.xls',\n",
       " 'bureau_balance.csv',\n",
       " 'sample_submission.csv']"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os\n",
    "cwd = os.getcwd()\n",
    "data_dir = cwd + '/dataset'\n",
    "os.listdir(data_dir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import plotly.offline as py\n",
    "import plotly.graph_objs as go\n",
    "import plotly.tools as tls\n",
    "pd.set_option('max_columns',500)\n",
    "pd.set_option('max_rows',500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "bureau_data = pd.read_csv(data_dir+'/bureau.csv')\n",
    "bureau_balance_data = pd.read_csv(data_dir+'/bureau_balance.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SK_ID_CURR</th>\n",
       "      <th>SK_ID_BUREAU</th>\n",
       "      <th>CREDIT_ACTIVE</th>\n",
       "      <th>CREDIT_CURRENCY</th>\n",
       "      <th>DAYS_CREDIT</th>\n",
       "      <th>CREDIT_DAY_OVERDUE</th>\n",
       "      <th>DAYS_CREDIT_ENDDATE</th>\n",
       "      <th>DAYS_ENDDATE_FACT</th>\n",
       "      <th>AMT_CREDIT_MAX_OVERDUE</th>\n",
       "      <th>CNT_CREDIT_PROLONG</th>\n",
       "      <th>AMT_CREDIT_SUM</th>\n",
       "      <th>AMT_CREDIT_SUM_DEBT</th>\n",
       "      <th>AMT_CREDIT_SUM_LIMIT</th>\n",
       "      <th>AMT_CREDIT_SUM_OVERDUE</th>\n",
       "      <th>CREDIT_TYPE</th>\n",
       "      <th>DAYS_CREDIT_UPDATE</th>\n",
       "      <th>AMT_ANNUITY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714462</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-497</td>\n",
       "      <td>0</td>\n",
       "      <td>-153.0</td>\n",
       "      <td>-153.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>91323.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-131</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714463</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-208</td>\n",
       "      <td>0</td>\n",
       "      <td>1075.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>171342.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Credit card</td>\n",
       "      <td>-20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714464</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-203</td>\n",
       "      <td>0</td>\n",
       "      <td>528.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>464323.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714465</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-203</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Credit card</td>\n",
       "      <td>-16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714466</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-629</td>\n",
       "      <td>0</td>\n",
       "      <td>1197.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77674.5</td>\n",
       "      <td>0</td>\n",
       "      <td>2700000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-21</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SK_ID_CURR  SK_ID_BUREAU CREDIT_ACTIVE CREDIT_CURRENCY  DAYS_CREDIT  \\\n",
       "0      215354       5714462        Closed      currency 1         -497   \n",
       "1      215354       5714463        Active      currency 1         -208   \n",
       "2      215354       5714464        Active      currency 1         -203   \n",
       "3      215354       5714465        Active      currency 1         -203   \n",
       "4      215354       5714466        Active      currency 1         -629   \n",
       "\n",
       "   CREDIT_DAY_OVERDUE  DAYS_CREDIT_ENDDATE  DAYS_ENDDATE_FACT  \\\n",
       "0                   0               -153.0             -153.0   \n",
       "1                   0               1075.0                NaN   \n",
       "2                   0                528.0                NaN   \n",
       "3                   0                  NaN                NaN   \n",
       "4                   0               1197.0                NaN   \n",
       "\n",
       "   AMT_CREDIT_MAX_OVERDUE  CNT_CREDIT_PROLONG  AMT_CREDIT_SUM  \\\n",
       "0                     NaN                   0         91323.0   \n",
       "1                     NaN                   0        225000.0   \n",
       "2                     NaN                   0        464323.5   \n",
       "3                     NaN                   0         90000.0   \n",
       "4                 77674.5                   0       2700000.0   \n",
       "\n",
       "   AMT_CREDIT_SUM_DEBT  AMT_CREDIT_SUM_LIMIT  AMT_CREDIT_SUM_OVERDUE  \\\n",
       "0                  0.0                   NaN                     0.0   \n",
       "1             171342.0                   NaN                     0.0   \n",
       "2                  NaN                   NaN                     0.0   \n",
       "3                  NaN                   NaN                     0.0   \n",
       "4                  NaN                   NaN                     0.0   \n",
       "\n",
       "       CREDIT_TYPE  DAYS_CREDIT_UPDATE  AMT_ANNUITY  \n",
       "0  Consumer credit                -131          NaN  \n",
       "1      Credit card                 -20          NaN  \n",
       "2  Consumer credit                 -16          NaN  \n",
       "3      Credit card                 -16          NaN  \n",
       "4  Consumer credit                 -21          NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bureau_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SK_ID_BUREAU</th>\n",
       "      <th>MONTHS_BALANCE</th>\n",
       "      <th>STATUS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5715448</td>\n",
       "      <td>0</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5715448</td>\n",
       "      <td>-1</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5715448</td>\n",
       "      <td>-2</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5715448</td>\n",
       "      <td>-3</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5715448</td>\n",
       "      <td>-4</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SK_ID_BUREAU  MONTHS_BALANCE STATUS\n",
       "0       5715448               0      C\n",
       "1       5715448              -1      C\n",
       "2       5715448              -2      C\n",
       "3       5715448              -3      C\n",
       "4       5715448              -4      C"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bureau_balance_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#bureau_balance_data.loc[bureau_balance_data[\"SK_ID_BUREAU\"] == 5714464].empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#bureau_data.loc[bureau_data[\"SK_ID_BUREAU\"]==5714464]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#bureau_data.loc[bureau_data[\"SK_ID_CURR\"]==215354]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(set(bureau_data[\"CREDIT_CURRENCY\"].values))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#bureau_data.loc[bureau_data[\"CREDIT_DAY_OVERDUE\"]!=0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#set(bureau_balance_data['STATUS'].values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#bureau_balance_data.loc[bureau_balance_data['STATUS']=='5']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#bureau_data.loc[bureau_data['SK_ID_BUREAU'] == 5230570]"
   ]
  },
  {
   "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>SK_ID_CURR</th>\n",
       "      <th>SK_ID_BUREAU</th>\n",
       "      <th>CREDIT_ACTIVE</th>\n",
       "      <th>CREDIT_CURRENCY</th>\n",
       "      <th>DAYS_CREDIT</th>\n",
       "      <th>CREDIT_DAY_OVERDUE</th>\n",
       "      <th>DAYS_CREDIT_ENDDATE</th>\n",
       "      <th>DAYS_ENDDATE_FACT</th>\n",
       "      <th>AMT_CREDIT_MAX_OVERDUE</th>\n",
       "      <th>CNT_CREDIT_PROLONG</th>\n",
       "      <th>AMT_CREDIT_SUM</th>\n",
       "      <th>AMT_CREDIT_SUM_DEBT</th>\n",
       "      <th>AMT_CREDIT_SUM_LIMIT</th>\n",
       "      <th>AMT_CREDIT_SUM_OVERDUE</th>\n",
       "      <th>CREDIT_TYPE</th>\n",
       "      <th>DAYS_CREDIT_UPDATE</th>\n",
       "      <th>AMT_ANNUITY</th>\n",
       "      <th>COUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714462</td>\n",
       "      <td>Closed</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-497</td>\n",
       "      <td>0</td>\n",
       "      <td>-153.0</td>\n",
       "      <td>-153.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>91323.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-131</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714463</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-208</td>\n",
       "      <td>0</td>\n",
       "      <td>1075.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>171342.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Credit card</td>\n",
       "      <td>-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714464</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-203</td>\n",
       "      <td>0</td>\n",
       "      <td>528.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>464323.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714465</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-203</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>90000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Credit card</td>\n",
       "      <td>-16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>215354</td>\n",
       "      <td>5714466</td>\n",
       "      <td>Active</td>\n",
       "      <td>currency 1</td>\n",
       "      <td>-629</td>\n",
       "      <td>0</td>\n",
       "      <td>1197.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77674.5</td>\n",
       "      <td>0</td>\n",
       "      <td>2700000.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Consumer credit</td>\n",
       "      <td>-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SK_ID_CURR  SK_ID_BUREAU CREDIT_ACTIVE CREDIT_CURRENCY  DAYS_CREDIT  \\\n",
       "0      215354       5714462        Closed      currency 1         -497   \n",
       "1      215354       5714463        Active      currency 1         -208   \n",
       "2      215354       5714464        Active      currency 1         -203   \n",
       "3      215354       5714465        Active      currency 1         -203   \n",
       "4      215354       5714466        Active      currency 1         -629   \n",
       "\n",
       "   CREDIT_DAY_OVERDUE  DAYS_CREDIT_ENDDATE  DAYS_ENDDATE_FACT  \\\n",
       "0                   0               -153.0             -153.0   \n",
       "1                   0               1075.0                NaN   \n",
       "2                   0                528.0                NaN   \n",
       "3                   0                  NaN                NaN   \n",
       "4                   0               1197.0                NaN   \n",
       "\n",
       "   AMT_CREDIT_MAX_OVERDUE  CNT_CREDIT_PROLONG  AMT_CREDIT_SUM  \\\n",
       "0                     NaN                   0         91323.0   \n",
       "1                     NaN                   0        225000.0   \n",
       "2                     NaN                   0        464323.5   \n",
       "3                     NaN                   0         90000.0   \n",
       "4                 77674.5                   0       2700000.0   \n",
       "\n",
       "   AMT_CREDIT_SUM_DEBT  AMT_CREDIT_SUM_LIMIT  AMT_CREDIT_SUM_OVERDUE  \\\n",
       "0                  0.0                   NaN                     0.0   \n",
       "1             171342.0                   NaN                     0.0   \n",
       "2                  NaN                   NaN                     0.0   \n",
       "3                  NaN                   NaN                     0.0   \n",
       "4                  NaN                   NaN                     0.0   \n",
       "\n",
       "       CREDIT_TYPE  DAYS_CREDIT_UPDATE  AMT_ANNUITY  COUNT  \n",
       "0  Consumer credit                -131          NaN      1  \n",
       "1      Credit card                 -20          NaN      1  \n",
       "2  Consumer credit                 -16          NaN      1  \n",
       "3      Credit card                 -16          NaN      1  \n",
       "4  Consumer credit                 -21          NaN      1  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#bureau_data['BALANCE_STATUS'] = 'Unknown'\n",
    "bureau_data['COUNT'] = 1\n",
    "bureau_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'COUNT_CREDIT = groupby(SK_ID_CURR).countof(CREDIT_ACTIVE)\\nMAX_OVERDUE_DAY = groupby(SK_ID_CURR).maxof(CREDIT_DAY_OVERDUE)\\nSUM_DEBT = groupby(SK_ID_CURR).sumof(AMT_CREDIT_SUM_DEBT)\\nSUM_OVERDUE = groupby(SK_ID_CURR).sumof(AMT_CREDIT_SUM_OVERDUE)'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''COUNT_CREDIT = groupby(SK_ID_CURR).countof(CREDIT_ACTIVE)\n",
    "MAX_OVERDUE_DAY = groupby(SK_ID_CURR).maxof(CREDIT_DAY_OVERDUE)\n",
    "SUM_DEBT = groupby(SK_ID_CURR).sumof(AMT_CREDIT_SUM_DEBT)\n",
    "SUM_OVERDUE = groupby(SK_ID_CURR).sumof(AMT_CREDIT_SUM_OVERDUE)'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 从bureau.csv中提取出特征"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 按credit_active分类，得到每一个sk_id_curr的当前各类的credit的数量 **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_credit = bureau_data[[\"SK_ID_CURR\",'CREDIT_ACTIVE','COUNT']].groupby([\"SK_ID_CURR\",'CREDIT_ACTIVE']).count()\n",
    "count_credit = count_credit.unstack(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "count_credit = count_credit.fillna(0).astype(int)\n",
    "#count_credit['COUNT'][\"Active\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "active_credit = count_credit['COUNT']['Active']\n",
    "baddebt_credit = count_credit['COUNT']['Bad debt']\n",
    "close_credit = count_credit['COUNT']['Closed']\n",
    "sold_credit = count_credit['COUNT']['Sold']  #Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data = pd.DataFrame({\"CNT_ACTIVE_CREDIT\":active_credit,\n",
    "                           \"CNT_BADDEBT_CREDIT\":baddebt_credit,\n",
    "                           \"CNT_CLOSE_CREDIT\":close_credit,\n",
    "                           \"CNT_SOLD_CREDIT\":sold_credit})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 305811 entries, 100001 to 456255\n",
      "Data columns (total 4 columns):\n",
      "CNT_ACTIVE_CREDIT     305811 non-null int64\n",
      "CNT_BADDEBT_CREDIT    305811 non-null int64\n",
      "CNT_CLOSE_CREDIT      305811 non-null int64\n",
      "CNT_SOLD_CREDIT       305811 non-null int64\n",
      "dtypes: int64(4)\n",
      "memory usage: 11.7 MB\n"
     ]
    }
   ],
   "source": [
    "clean_data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 得到每一个sk_id_curr的最大overdue_day **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "max_overdue_day = bureau_data[['SK_ID_CURR','CREDIT_DAY_OVERDUE']].groupby('SK_ID_CURR').max() #DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 305811 entries, 100001 to 456255\n",
      "Data columns (total 1 columns):\n",
      "CREDIT_DAY_OVERDUE    305811 non-null int64\n",
      "dtypes: int64(1)\n",
      "memory usage: 4.7 MB\n"
     ]
    }
   ],
   "source": [
    "max_overdue_day.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 得到每一个sk_id_curr的总负债（bureau.csv中) **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "sum_debt = bureau_data[['SK_ID_CURR','AMT_CREDIT_SUM_DEBT']].groupby('SK_ID_CURR').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 305811 entries, 100001 to 456255\n",
      "Data columns (total 1 columns):\n",
      "AMT_CREDIT_SUM_DEBT    305811 non-null float64\n",
      "dtypes: float64(1)\n",
      "memory usage: 4.7 MB\n"
     ]
    }
   ],
   "source": [
    "sum_debt.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 得到每一个sk_id_curr的总overdue的数值 (bureau.csv中）**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "sum_over = bureau_data[['SK_ID_CURR','AMT_CREDIT_SUM_OVERDUE']].groupby('SK_ID_CURR').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 305811 entries, 100001 to 456255\n",
      "Data columns (total 1 columns):\n",
      "AMT_CREDIT_SUM_OVERDUE    305811 non-null float64\n",
      "dtypes: float64(1)\n",
      "memory usage: 4.7 MB\n"
     ]
    }
   ],
   "source": [
    "sum_over.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "** 把所有的数据Join成一张表格**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_data = pd.concat([clean_data,max_overdue_day,sum_debt,sum_over],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>BU_ACTIVE_CREDIT</th>\n",
       "      <th>BU_BADDEBT_CREDIT</th>\n",
       "      <th>BU_CLOSE_CREDIT</th>\n",
       "      <th>BU_SOLD_CREDIT</th>\n",
       "      <th>BU_MAX_DAY_OVERDUE</th>\n",
       "      <th>BU_SUM_DEBT</th>\n",
       "      <th>BU_SUM_OVERDUE</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SK_ID_CURR</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100001</th>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>596686.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100002</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>245781.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100003</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100004</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100005</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>568408.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            BU_ACTIVE_CREDIT  BU_BADDEBT_CREDIT  BU_CLOSE_CREDIT  \\\n",
       "SK_ID_CURR                                                         \n",
       "100001                     3                  0                4   \n",
       "100002                     2                  0                6   \n",
       "100003                     1                  0                3   \n",
       "100004                     0                  0                2   \n",
       "100005                     2                  0                1   \n",
       "\n",
       "            BU_SOLD_CREDIT  BU_MAX_DAY_OVERDUE  BU_SUM_DEBT  BU_SUM_OVERDUE  \n",
       "SK_ID_CURR                                                                   \n",
       "100001                   0                   0     596686.5             0.0  \n",
       "100002                   0                   0     245781.0             0.0  \n",
       "100003                   0                   0          0.0             0.0  \n",
       "100004                   0                   0          0.0             0.0  \n",
       "100005                   0                   0     568408.5             0.0  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "colomns = ['BU_ACTIVE_CREDIT',\n",
    "           'BU_BADDEBT_CREDIT',\n",
    "           'BU_CLOSE_CREDIT',\n",
    "           'BU_SOLD_CREDIT',\n",
    "           'BU_MAX_DAY_OVERDUE',\n",
    "           'BU_SUM_DEBT',\n",
    "           'BU_SUM_OVERDUE']\n",
    "clean_data.columns = colomns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#clean_data.set_index('SK_ID_CURR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#clean_data = clean_data.reset_index()\n",
    "clean_data.to_csv((data_dir+'/bu_clean.csv'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 用bureau_balance中的数据得出信用的信息\n",
    "** 因为考虑到数据量大，且大量的数据在bureau_data中有反映，故暂时不处理 **"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"status_dict = {'5':5,\\n               '4':4,\\n               '3':3,\\n               '2':2,\\n               '1':1,\\n               '0':0,\\n               'C':-1,\\n               'X':-2}\\nbureau_balance_data['STATUS'] = bureau_balance_data['STATUS'].map(status_dict).astype(int)\\n#bureau_data['BALANCE_STATUS'] = 'Unknown'\\n#bureau_data['MONTHS_BALANCE'] = 1\\nbureau_id = bureau_data['SK_ID_BUREAU'].values\\nstatus = []\\nmonth_balance = []\\nfor i,b_id in enumerate(bureau_id):\\n    tmp = bureau_balance_data.loc[bureau_balance_data['SK_ID_BUREAU']==b_id]\\n    if i % 1000 == 0:\\n        print(i)\\n    if not tmp.empty:\\n        status_tmp = np.max(tmp['STATUS'])\\n        status.append(status_tmp)\\n        month_balance.append(np.max(tmp.loc[tmp['STATUS'] ==status_tmp]['MONTHS_BALANCE'].values))\\n        #bureau_data.loc[bureau_data['SK_ID_BUREAU'] == b_id,'BALANCE_STATUS'] = status\\n        #bureau_data.loc[bureau_data['SK_ID_BUREAU'] == b_id,'MONTHS_BALANCE'] = month_balance            \""
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''status_dict = {'5':5,\n",
    "               '4':4,\n",
    "               '3':3,\n",
    "               '2':2,\n",
    "               '1':1,\n",
    "               '0':0,\n",
    "               'C':-1,\n",
    "               'X':-2}\n",
    "bureau_balance_data['STATUS'] = bureau_balance_data['STATUS'].map(status_dict).astype(int)\n",
    "#bureau_data['BALANCE_STATUS'] = 'Unknown'\n",
    "#bureau_data['MONTHS_BALANCE'] = 1\n",
    "bureau_id = bureau_data['SK_ID_BUREAU'].values\n",
    "status = []\n",
    "month_balance = []\n",
    "for i,b_id in enumerate(bureau_id):\n",
    "    tmp = bureau_balance_data.loc[bureau_balance_data['SK_ID_BUREAU']==b_id]\n",
    "    if i % 1000 == 0:\n",
    "        print(i)\n",
    "    if not tmp.empty:\n",
    "        status_tmp = np.max(tmp['STATUS'])\n",
    "        status.append(status_tmp)\n",
    "        month_balance.append(np.max(tmp.loc[tmp['STATUS'] ==status_tmp]['MONTHS_BALANCE'].values))\n",
    "        #bureau_data.loc[bureau_data['SK_ID_BUREAU'] == b_id,'BALANCE_STATUS'] = status\n",
    "        #bureau_data.loc[bureau_data['SK_ID_BUREAU'] == b_id,'MONTHS_BALANCE'] = month_balance            '''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "#%timeit tmp = bureau_balance_data.loc[bureau_balance_data['SK_ID_BUREAU']==5230570]\n",
    "#%timeit tmp.empty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "6.21 ms ± 518 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)\n"
     ]
    }
   ],
   "source": [
    "a = bureau_data['SK_ID_BUREAU'].values\n",
    "np.shape(a)\n",
    "%timeit np.append(a, np.ndarray([1]) ,axis = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}