Credit Default - Data Cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This research employed a binary variable, default payment (Yes = 1, No = 0), as the response variable. This study reviewed the literature and used the following 23 variables as explanatory variables:\n",
    "\n",
    "    X1 (LIMIT_BAL): Amount of the given credit (NT dollar)\n",
    "    X2 (SEX): Gender (1 = male; 2 = female).\n",
    "    X3 (EDUCATION): Education (1 = graduate school; 2 = university; 3 = high school; 4 = others).\n",
    "    X4 (MARRIAGE): Marital status (1 = married; 2 = single; 3 = others).\n",
    "    X5 (AGE): in years.\n",
    "    X6 - X11 (PAY_1-PAY_6): History of past payment. PAY_1 - Sept, PAY_2 - Aug, .. , PAY_6 - Apr\n",
    "        \n",
    "    The measurement scale for the repayment status is: -2 = no credit used, -1 = pay duly; 1 = payment delay for one month; 2 = payment delay for two months; . . .; 8 = payment delay for eight months; 9 = payment delay for nine months and above.\n",
    "        \n",
    "    X12-X17 (BILL_AMT1-BILL_AMT6): Amount of bill statement (NT dollar).\n",
    "        \n",
    "    X18-X23 (PAY_AMT1-PAY_AMT6): Amount of previous payment (NT dollar). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Business Understanding"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This data is provided by a Taiwanees Credit Card Company. It contains recent financial data for 30,000 account holders. The rows are labeled by whether the month after a six month historical data period, the account holder has defaulted."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Preperation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(\"default_of_credit_card_clients__updated.xls\")\n",
    "#df.rename(columns=df.iloc[0])"
   ]
  },
  {
   "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>ID</th>\n",
       "      <th>LIMIT_BAL</th>\n",
       "      <th>SEX</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>MARRIAGE</th>\n",
       "      <th>AGE</th>\n",
       "      <th>PAY_1</th>\n",
       "      <th>PAY_2</th>\n",
       "      <th>PAY_3</th>\n",
       "      <th>PAY_4</th>\n",
       "      <th>...</th>\n",
       "      <th>BILL_AMT4</th>\n",
       "      <th>BILL_AMT5</th>\n",
       "      <th>BILL_AMT6</th>\n",
       "      <th>PAY_AMT1</th>\n",
       "      <th>PAY_AMT2</th>\n",
       "      <th>PAY_AMT3</th>\n",
       "      <th>PAY_AMT4</th>\n",
       "      <th>PAY_AMT5</th>\n",
       "      <th>PAY_AMT6</th>\n",
       "      <th>default payment next month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>798fc410-45c1</td>\n",
       "      <td>20000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>689</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8a8c8f3b-8eb4</td>\n",
       "      <td>120000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>26</td>\n",
       "      <td>-1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>3272</td>\n",
       "      <td>3455</td>\n",
       "      <td>3261</td>\n",
       "      <td>0</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "      <td>2000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>85698822-43f5</td>\n",
       "      <td>90000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>14331</td>\n",
       "      <td>14948</td>\n",
       "      <td>15549</td>\n",
       "      <td>1518</td>\n",
       "      <td>1500</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>1000</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0737c11b-be42</td>\n",
       "      <td>50000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>28314</td>\n",
       "      <td>28959</td>\n",
       "      <td>29547</td>\n",
       "      <td>2000</td>\n",
       "      <td>2019</td>\n",
       "      <td>1200</td>\n",
       "      <td>1100</td>\n",
       "      <td>1069</td>\n",
       "      <td>1000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3b7f77cc-dbc0</td>\n",
       "      <td>50000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>57</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>20940</td>\n",
       "      <td>19146</td>\n",
       "      <td>19131</td>\n",
       "      <td>2000</td>\n",
       "      <td>36681</td>\n",
       "      <td>10000</td>\n",
       "      <td>9000</td>\n",
       "      <td>689</td>\n",
       "      <td>679</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              ID  LIMIT_BAL  SEX  EDUCATION  MARRIAGE  AGE PAY_1  PAY_2  \\\n",
       "0  798fc410-45c1      20000    2          2         1   24     2      2   \n",
       "1  8a8c8f3b-8eb4     120000    2          2         2   26    -1      2   \n",
       "2  85698822-43f5      90000    2          2         2   34     0      0   \n",
       "3  0737c11b-be42      50000    2          2         1   37     0      0   \n",
       "4  3b7f77cc-dbc0      50000    1          2         1   57    -1      0   \n",
       "\n",
       "   PAY_3  PAY_4             ...              BILL_AMT4  BILL_AMT5  BILL_AMT6  \\\n",
       "0     -1     -1             ...                      0          0          0   \n",
       "1      0      0             ...                   3272       3455       3261   \n",
       "2      0      0             ...                  14331      14948      15549   \n",
       "3      0      0             ...                  28314      28959      29547   \n",
       "4     -1      0             ...                  20940      19146      19131   \n",
       "\n",
       "   PAY_AMT1  PAY_AMT2  PAY_AMT3  PAY_AMT4  PAY_AMT5  PAY_AMT6  \\\n",
       "0         0       689         0         0         0         0   \n",
       "1         0      1000      1000      1000         0      2000   \n",
       "2      1518      1500      1000      1000      1000      5000   \n",
       "3      2000      2019      1200      1100      1069      1000   \n",
       "4      2000     36681     10000      9000       689       679   \n",
       "\n",
       "   default payment next month  \n",
       "0                           1  \n",
       "1                           1  \n",
       "2                           0  \n",
       "3                           0  \n",
       "4                           0  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30000, 25)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29687"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['ID'].nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There seems to be some duplicates in the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "id_counts = df['ID'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    29374\n",
       "2      313\n",
       "Name: ID, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_counts.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "313 IDs occur twice."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Boolean Mask\n",
    "bol_counts = id_counts ==2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8a6bbf3d-8a2d    True\n",
       "98b8a2ca-8988    True\n",
       "527c11ea-113e    True\n",
       "63b20368-1a04    True\n",
       "555a25cd-2d05    True\n",
       "Name: ID, dtype: bool"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bol_counts[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "dup_ids = id_counts.index[bol_counts]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "313"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(dup_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dup = df[df['ID'].isin(dup_ids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>ID</th>\n",
       "      <th>LIMIT_BAL</th>\n",
       "      <th>SEX</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>MARRIAGE</th>\n",
       "      <th>AGE</th>\n",
       "      <th>PAY_1</th>\n",
       "      <th>PAY_2</th>\n",
       "      <th>PAY_3</th>\n",
       "      <th>PAY_4</th>\n",
       "      <th>...</th>\n",
       "      <th>BILL_AMT4</th>\n",
       "      <th>BILL_AMT5</th>\n",
       "      <th>BILL_AMT6</th>\n",
       "      <th>PAY_AMT1</th>\n",
       "      <th>PAY_AMT2</th>\n",
       "      <th>PAY_AMT3</th>\n",
       "      <th>PAY_AMT4</th>\n",
       "      <th>PAY_AMT5</th>\n",
       "      <th>PAY_AMT6</th>\n",
       "      <th>default payment next month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>26bde6da-f148</td>\n",
       "      <td>180000</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>28</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>...</td>\n",
       "      <td>332</td>\n",
       "      <td>416</td>\n",
       "      <td>416</td>\n",
       "      <td>0</td>\n",
       "      <td>416</td>\n",
       "      <td>332</td>\n",
       "      <td>500</td>\n",
       "      <td>3500</td>\n",
       "      <td>832</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>42009c72-651f</td>\n",
       "      <td>150000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>34</td>\n",
       "      <td>-2</td>\n",
       "      <td>-2</td>\n",
       "      <td>-2</td>\n",
       "      <td>-2</td>\n",
       "      <td>...</td>\n",
       "      <td>116</td>\n",
       "      <td>0</td>\n",
       "      <td>1500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>116</td>\n",
       "      <td>0</td>\n",
       "      <td>1500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>26bde6da-f148</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296</th>\n",
       "      <td>42009c72-651f</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>73ed1381-3a3f</td>\n",
       "      <td>230000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-2</td>\n",
       "      <td>...</td>\n",
       "      <td>2100</td>\n",
       "      <td>767</td>\n",
       "      <td>6173</td>\n",
       "      <td>1710</td>\n",
       "      <td>1100</td>\n",
       "      <td>1000</td>\n",
       "      <td>767</td>\n",
       "      <td>6173</td>\n",
       "      <td>1000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340</th>\n",
       "      <td>4f95b36b-ab10</td>\n",
       "      <td>240000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>30</td>\n",
       "      <td>-2</td>\n",
       "      <td>-2</td>\n",
       "      <td>-2</td>\n",
       "      <td>-2</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "      <td>92</td>\n",
       "      <td>0</td>\n",
       "      <td>92</td>\n",
       "      <td>0</td>\n",
       "      <td>1883</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td>8d3a2576-a958</td>\n",
       "      <td>210000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>Not available</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>91143</td>\n",
       "      <td>82733</td>\n",
       "      <td>74125</td>\n",
       "      <td>4002</td>\n",
       "      <td>5000</td>\n",
       "      <td>3500</td>\n",
       "      <td>3000</td>\n",
       "      <td>2700</td>\n",
       "      <td>5000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>423</th>\n",
       "      <td>73ed1381-3a3f</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440</th>\n",
       "      <td>4f95b36b-ab10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>8d3a2576-a958</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ID  LIMIT_BAL  SEX  EDUCATION  MARRIAGE  AGE          PAY_1  \\\n",
       "141  26bde6da-f148     180000    1          2         2   28             -1   \n",
       "196  42009c72-651f     150000    2          2         1   34             -2   \n",
       "241  26bde6da-f148          0    0          0         0    0              0   \n",
       "296  42009c72-651f          0    0          0         0    0              0   \n",
       "323  73ed1381-3a3f     230000    2          2         1   28              0   \n",
       "340  4f95b36b-ab10     240000    2          2         2   30             -2   \n",
       "398  8d3a2576-a958     210000    2          2         1   30  Not available   \n",
       "423  73ed1381-3a3f          0    0          0         0    0              0   \n",
       "440  4f95b36b-ab10          0    0          0         0    0              0   \n",
       "498  8d3a2576-a958          0    0          0         0    0              0   \n",
       "\n",
       "     PAY_2  PAY_3  PAY_4             ...              BILL_AMT4  BILL_AMT5  \\\n",
       "141     -1     -1     -1             ...                    332        416   \n",
       "196     -2     -2     -2             ...                    116          0   \n",
       "241      0      0      0             ...                      0          0   \n",
       "296      0      0      0             ...                      0          0   \n",
       "323      0      0     -2             ...                   2100        767   \n",
       "340     -2     -2     -2             ...                      0         92   \n",
       "398      0      0      0             ...                  91143      82733   \n",
       "423      0      0      0             ...                      0          0   \n",
       "440      0      0      0             ...                      0          0   \n",
       "498      0      0      0             ...                      0          0   \n",
       "\n",
       "     BILL_AMT6  PAY_AMT1  PAY_AMT2  PAY_AMT3  PAY_AMT4  PAY_AMT5  PAY_AMT6  \\\n",
       "141        416         0       416       332       500      3500       832   \n",
       "196       1500         0         0       116         0      1500         0   \n",
       "241          0         0         0         0         0         0         0   \n",
       "296          0         0         0         0         0         0         0   \n",
       "323       6173      1710      1100      1000       767      6173      1000   \n",
       "340          0        92        92         0        92         0      1883   \n",
       "398      74125      4002      5000      3500      3000      2700      5000   \n",
       "423          0         0         0         0         0         0         0   \n",
       "440          0         0         0         0         0         0         0   \n",
       "498          0         0         0         0         0         0         0   \n",
       "\n",
       "     default payment next month  \n",
       "141                           0  \n",
       "196                           0  \n",
       "241                           0  \n",
       "296                           0  \n",
       "323                           1  \n",
       "340                           0  \n",
       "398                           0  \n",
       "423                           0  \n",
       "440                           0  \n",
       "498                           0  \n",
       "\n",
       "[10 rows x 25 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dup.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Seems like the duplicate rows have 0 for LIMIT_BAL etc."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "zero_mask = df == 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30000"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(zero_mask)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>ID</th>\n",
       "      <th>LIMIT_BAL</th>\n",
       "      <th>SEX</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>MARRIAGE</th>\n",
       "      <th>AGE</th>\n",
       "      <th>PAY_1</th>\n",
       "      <th>PAY_2</th>\n",
       "      <th>PAY_3</th>\n",
       "      <th>PAY_4</th>\n",
       "      <th>...</th>\n",
       "      <th>BILL_AMT4</th>\n",
       "      <th>BILL_AMT5</th>\n",
       "      <th>BILL_AMT6</th>\n",
       "      <th>PAY_AMT1</th>\n",
       "      <th>PAY_AMT2</th>\n",
       "      <th>PAY_AMT3</th>\n",
       "      <th>PAY_AMT4</th>\n",
       "      <th>PAY_AMT5</th>\n",
       "      <th>PAY_AMT6</th>\n",
       "      <th>default payment next month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>...</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ID  LIMIT_BAL    SEX  EDUCATION  MARRIAGE    AGE  PAY_1  PAY_2  PAY_3  \\\n",
       "0  False      False  False      False     False  False  False  False  False   \n",
       "1  False      False  False      False     False  False  False  False   True   \n",
       "2  False      False  False      False     False  False   True   True   True   \n",
       "3  False      False  False      False     False  False   True   True   True   \n",
       "4  False      False  False      False     False  False  False   True  False   \n",
       "\n",
       "   PAY_4             ...              BILL_AMT4  BILL_AMT5  BILL_AMT6  \\\n",
       "0  False             ...                   True       True       True   \n",
       "1   True             ...                  False      False      False   \n",
       "2   True             ...                  False      False      False   \n",
       "3   True             ...                  False      False      False   \n",
       "4   True             ...                  False      False      False   \n",
       "\n",
       "   PAY_AMT1  PAY_AMT2  PAY_AMT3  PAY_AMT4  PAY_AMT5  PAY_AMT6  \\\n",
       "0      True     False      True      True      True      True   \n",
       "1      True     False     False     False      True     False   \n",
       "2     False     False     False     False     False     False   \n",
       "3     False     False     False     False     False     False   \n",
       "4     False     False     False     False     False     False   \n",
       "\n",
       "   default payment next month  \n",
       "0                       False  \n",
       "1                       False  \n",
       "2                        True  \n",
       "3                        True  \n",
       "4                        True  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zero_mask.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Check among all rows where all columns are zero (except id)\n",
    "\n",
    "row_zero_mask = zero_mask.iloc[:,1:].all(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2    False\n",
       "3    False\n",
       "4    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "row_zero_mask.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "315"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(row_zero_mask)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So there are 315 rows that have zeros for every column excpet the ID. Since this number is greater than the number of duplicates (313), eliminating the 315 could solve both issues."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_df = df.loc[~row_zero_mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(29685, 25)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "29685"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df['ID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>LIMIT_BAL</th>\n",
       "      <th>SEX</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>MARRIAGE</th>\n",
       "      <th>AGE</th>\n",
       "      <th>PAY_2</th>\n",
       "      <th>PAY_3</th>\n",
       "      <th>PAY_4</th>\n",
       "      <th>PAY_5</th>\n",
       "      <th>PAY_6</th>\n",
       "      <th>...</th>\n",
       "      <th>BILL_AMT4</th>\n",
       "      <th>BILL_AMT5</th>\n",
       "      <th>BILL_AMT6</th>\n",
       "      <th>PAY_AMT1</th>\n",
       "      <th>PAY_AMT2</th>\n",
       "      <th>PAY_AMT3</th>\n",
       "      <th>PAY_AMT4</th>\n",
       "      <th>PAY_AMT5</th>\n",
       "      <th>PAY_AMT6</th>\n",
       "      <th>default payment next month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>2.968500e+04</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "      <td>29685.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>167519.948796</td>\n",
       "      <td>1.603335</td>\n",
       "      <td>1.852788</td>\n",
       "      <td>1.552198</td>\n",
       "      <td>35.481354</td>\n",
       "      <td>-0.134277</td>\n",
       "      <td>-0.166077</td>\n",
       "      <td>-0.221627</td>\n",
       "      <td>-0.266700</td>\n",
       "      <td>-0.291090</td>\n",
       "      <td>...</td>\n",
       "      <td>43245.439279</td>\n",
       "      <td>40307.628263</td>\n",
       "      <td>38888.682095</td>\n",
       "      <td>5672.886812</td>\n",
       "      <td>5.917545e+03</td>\n",
       "      <td>5229.295570</td>\n",
       "      <td>4826.770827</td>\n",
       "      <td>4805.203840</td>\n",
       "      <td>5219.023008</td>\n",
       "      <td>0.221054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>129716.478534</td>\n",
       "      <td>0.489214</td>\n",
       "      <td>0.789465</td>\n",
       "      <td>0.521869</td>\n",
       "      <td>9.212199</td>\n",
       "      <td>1.197440</td>\n",
       "      <td>1.197278</td>\n",
       "      <td>1.168278</td>\n",
       "      <td>1.133156</td>\n",
       "      <td>1.150653</td>\n",
       "      <td>...</td>\n",
       "      <td>64276.899483</td>\n",
       "      <td>60787.204536</td>\n",
       "      <td>59588.111810</td>\n",
       "      <td>16616.452239</td>\n",
       "      <td>2.310628e+04</td>\n",
       "      <td>17650.360969</td>\n",
       "      <td>15607.256127</td>\n",
       "      <td>15311.798994</td>\n",
       "      <td>17798.365183</td>\n",
       "      <td>0.414964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>10000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>21.000000</td>\n",
       "      <td>-2.000000</td>\n",
       "      <td>-2.000000</td>\n",
       "      <td>-2.000000</td>\n",
       "      <td>-2.000000</td>\n",
       "      <td>-2.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>-170000.000000</td>\n",
       "      <td>-81334.000000</td>\n",
       "      <td>-339603.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>50000.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>-1.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2329.000000</td>\n",
       "      <td>1760.000000</td>\n",
       "      <td>1257.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>8.320000e+02</td>\n",
       "      <td>390.000000</td>\n",
       "      <td>296.000000</td>\n",
       "      <td>250.000000</td>\n",
       "      <td>115.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>140000.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>19040.000000</td>\n",
       "      <td>18106.000000</td>\n",
       "      <td>17067.000000</td>\n",
       "      <td>2100.000000</td>\n",
       "      <td>2.008000e+03</td>\n",
       "      <td>1800.000000</td>\n",
       "      <td>1500.000000</td>\n",
       "      <td>1500.000000</td>\n",
       "      <td>1500.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>240000.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>41.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>54515.000000</td>\n",
       "      <td>50244.000000</td>\n",
       "      <td>49252.000000</td>\n",
       "      <td>5007.000000</td>\n",
       "      <td>5.000000e+03</td>\n",
       "      <td>4508.000000</td>\n",
       "      <td>4015.000000</td>\n",
       "      <td>4041.000000</td>\n",
       "      <td>4000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>79.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>891586.000000</td>\n",
       "      <td>927171.000000</td>\n",
       "      <td>961664.000000</td>\n",
       "      <td>873552.000000</td>\n",
       "      <td>1.684259e+06</td>\n",
       "      <td>896040.000000</td>\n",
       "      <td>621000.000000</td>\n",
       "      <td>426529.000000</td>\n",
       "      <td>528666.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            LIMIT_BAL           SEX     EDUCATION      MARRIAGE           AGE  \\\n",
       "count    29685.000000  29685.000000  29685.000000  29685.000000  29685.000000   \n",
       "mean    167519.948796      1.603335      1.852788      1.552198     35.481354   \n",
       "std     129716.478534      0.489214      0.789465      0.521869      9.212199   \n",
       "min      10000.000000      1.000000      0.000000      0.000000     21.000000   \n",
       "25%      50000.000000      1.000000      1.000000      1.000000     28.000000   \n",
       "50%     140000.000000      2.000000      2.000000      2.000000     34.000000   \n",
       "75%     240000.000000      2.000000      2.000000      2.000000     41.000000   \n",
       "max    1000000.000000      2.000000      6.000000      3.000000     79.000000   \n",
       "\n",
       "              PAY_2         PAY_3         PAY_4         PAY_5         PAY_6  \\\n",
       "count  29685.000000  29685.000000  29685.000000  29685.000000  29685.000000   \n",
       "mean      -0.134277     -0.166077     -0.221627     -0.266700     -0.291090   \n",
       "std        1.197440      1.197278      1.168278      1.133156      1.150653   \n",
       "min       -2.000000     -2.000000     -2.000000     -2.000000     -2.000000   \n",
       "25%       -1.000000     -1.000000     -1.000000     -1.000000     -1.000000   \n",
       "50%        0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "75%        0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "max        8.000000      8.000000      8.000000      8.000000      8.000000   \n",
       "\n",
       "                  ...                  BILL_AMT4      BILL_AMT5  \\\n",
       "count             ...               29685.000000   29685.000000   \n",
       "mean              ...               43245.439279   40307.628263   \n",
       "std               ...               64276.899483   60787.204536   \n",
       "min               ...             -170000.000000  -81334.000000   \n",
       "25%               ...                2329.000000    1760.000000   \n",
       "50%               ...               19040.000000   18106.000000   \n",
       "75%               ...               54515.000000   50244.000000   \n",
       "max               ...              891586.000000  927171.000000   \n",
       "\n",
       "           BILL_AMT6       PAY_AMT1      PAY_AMT2       PAY_AMT3  \\\n",
       "count   29685.000000   29685.000000  2.968500e+04   29685.000000   \n",
       "mean    38888.682095    5672.886812  5.917545e+03    5229.295570   \n",
       "std     59588.111810   16616.452239  2.310628e+04   17650.360969   \n",
       "min   -339603.000000       0.000000  0.000000e+00       0.000000   \n",
       "25%      1257.000000    1000.000000  8.320000e+02     390.000000   \n",
       "50%     17067.000000    2100.000000  2.008000e+03    1800.000000   \n",
       "75%     49252.000000    5007.000000  5.000000e+03    4508.000000   \n",
       "max    961664.000000  873552.000000  1.684259e+06  896040.000000   \n",
       "\n",
       "            PAY_AMT4       PAY_AMT5       PAY_AMT6  default payment next month  \n",
       "count   29685.000000   29685.000000   29685.000000                29685.000000  \n",
       "mean     4826.770827    4805.203840    5219.023008                    0.221054  \n",
       "std     15607.256127   15311.798994   17798.365183                    0.414964  \n",
       "min         0.000000       0.000000       0.000000                    0.000000  \n",
       "25%       296.000000     250.000000     115.000000                    0.000000  \n",
       "50%      1500.000000    1500.000000    1500.000000                    0.000000  \n",
       "75%      4015.000000    4041.000000    4000.000000                    0.000000  \n",
       "max    621000.000000  426529.000000  528666.000000                    1.000000  \n",
       "\n",
       "[8 rows x 23 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 29685 entries, 0 to 29999\n",
      "Data columns (total 25 columns):\n",
      "ID                            29685 non-null object\n",
      "LIMIT_BAL                     29685 non-null int64\n",
      "SEX                           29685 non-null int64\n",
      "EDUCATION                     29685 non-null int64\n",
      "MARRIAGE                      29685 non-null int64\n",
      "AGE                           29685 non-null int64\n",
      "PAY_1                         29685 non-null object\n",
      "PAY_2                         29685 non-null int64\n",
      "PAY_3                         29685 non-null int64\n",
      "PAY_4                         29685 non-null int64\n",
      "PAY_5                         29685 non-null int64\n",
      "PAY_6                         29685 non-null int64\n",
      "BILL_AMT1                     29685 non-null int64\n",
      "BILL_AMT2                     29685 non-null int64\n",
      "BILL_AMT3                     29685 non-null int64\n",
      "BILL_AMT4                     29685 non-null int64\n",
      "BILL_AMT5                     29685 non-null int64\n",
      "BILL_AMT6                     29685 non-null int64\n",
      "PAY_AMT1                      29685 non-null int64\n",
      "PAY_AMT2                      29685 non-null int64\n",
      "PAY_AMT3                      29685 non-null int64\n",
      "PAY_AMT4                      29685 non-null int64\n",
      "PAY_AMT5                      29685 non-null int64\n",
      "PAY_AMT6                      29685 non-null int64\n",
      "default payment next month    29685 non-null int64\n",
      "dtypes: int64(23), object(2)\n",
      "memory usage: 5.9+ MB\n"
     ]
    }
   ],
   "source": [
    "clean_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PAY_1 is of type object instead of int64. Let's have a look!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                13087\n",
       "-1                5047\n",
       "1                 3261\n",
       "Not available     3021\n",
       "-2                2476\n",
       "2                 2378\n",
       "3                  292\n",
       "4                   63\n",
       "5                   23\n",
       "8                   17\n",
       "6                   11\n",
       "7                    9\n",
       "Name: PAY_1, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df['PAY_1'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Contains 'Not Available'. Let's remove rows with this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "na_mask = clean_df['PAY_1'] != 'Not available'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_df2 = clean_df.loc[na_mask]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       " 0    13087\n",
       "-1     5047\n",
       " 1     3261\n",
       "-2     2476\n",
       " 2     2378\n",
       " 3      292\n",
       " 4       63\n",
       " 5       23\n",
       " 8       17\n",
       " 6       11\n",
       " 7        9\n",
       "Name: PAY_1, dtype: int64"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df2['PAY_1'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 26664 entries, 0 to 29999\n",
      "Data columns (total 25 columns):\n",
      "ID                            26664 non-null object\n",
      "LIMIT_BAL                     26664 non-null int64\n",
      "SEX                           26664 non-null int64\n",
      "EDUCATION                     26664 non-null int64\n",
      "MARRIAGE                      26664 non-null int64\n",
      "AGE                           26664 non-null int64\n",
      "PAY_1                         26664 non-null object\n",
      "PAY_2                         26664 non-null int64\n",
      "PAY_3                         26664 non-null int64\n",
      "PAY_4                         26664 non-null int64\n",
      "PAY_5                         26664 non-null int64\n",
      "PAY_6                         26664 non-null int64\n",
      "BILL_AMT1                     26664 non-null int64\n",
      "BILL_AMT2                     26664 non-null int64\n",
      "BILL_AMT3                     26664 non-null int64\n",
      "BILL_AMT4                     26664 non-null int64\n",
      "BILL_AMT5                     26664 non-null int64\n",
      "BILL_AMT6                     26664 non-null int64\n",
      "PAY_AMT1                      26664 non-null int64\n",
      "PAY_AMT2                      26664 non-null int64\n",
      "PAY_AMT3                      26664 non-null int64\n",
      "PAY_AMT4                      26664 non-null int64\n",
      "PAY_AMT5                      26664 non-null int64\n",
      "PAY_AMT6                      26664 non-null int64\n",
      "default payment next month    26664 non-null int64\n",
      "dtypes: int64(23), object(2)\n",
      "memory usage: 5.3+ MB\n"
     ]
    }
   ],
   "source": [
    "clean_df2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\tusha\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "#Convert from object to int64\n",
    "clean_df2['PAY_1'] = clean_df2['PAY_1'].astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 26664 entries, 0 to 29999\n",
      "Data columns (total 25 columns):\n",
      "ID                            26664 non-null object\n",
      "LIMIT_BAL                     26664 non-null int64\n",
      "SEX                           26664 non-null int64\n",
      "EDUCATION                     26664 non-null int64\n",
      "MARRIAGE                      26664 non-null int64\n",
      "AGE                           26664 non-null int64\n",
      "PAY_1                         26664 non-null int64\n",
      "PAY_2                         26664 non-null int64\n",
      "PAY_3                         26664 non-null int64\n",
      "PAY_4                         26664 non-null int64\n",
      "PAY_5                         26664 non-null int64\n",
      "PAY_6                         26664 non-null int64\n",
      "BILL_AMT1                     26664 non-null int64\n",
      "BILL_AMT2                     26664 non-null int64\n",
      "BILL_AMT3                     26664 non-null int64\n",
      "BILL_AMT4                     26664 non-null int64\n",
      "BILL_AMT5                     26664 non-null int64\n",
      "BILL_AMT6                     26664 non-null int64\n",
      "PAY_AMT1                      26664 non-null int64\n",
      "PAY_AMT2                      26664 non-null int64\n",
      "PAY_AMT3                      26664 non-null int64\n",
      "PAY_AMT4                      26664 non-null int64\n",
      "PAY_AMT5                      26664 non-null int64\n",
      "PAY_AMT6                      26664 non-null int64\n",
      "default payment next month    26664 non-null int64\n",
      "dtypes: int64(24), object(1)\n",
      "memory usage: 5.3+ MB\n"
     ]
    }
   ],
   "source": [
    "clean_df2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_df2.to_excel(\"clean_data.xls\")"
   ]
  },
  {
   "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.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}