LendingClub/003-LendingClub.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Picking up from last time there is something I want to continue with:\n",
    " - Some columns have less than 2500 values. We don't know why those columns are missing or where they are.\n",
    "\n",
    "Still to do to follow the exercise:                                                                                    \n",
    "1) Remove outliers   \n",
    "2) Remove rows with NA    \n",
    "3) Save the code in a way I can still use it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "# first we ingest the data from the source on the web\n",
    "# this contains a reduced version of the data set from Lending Club\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "LD = pd.read_csv('/home/sophie/projects/LendingClub/data/loansData.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Amount.Requested                    int64\n",
       "Amount.Funded.By.Investors        float64\n",
       "Interest.Rate                      object\n",
       "Loan.Length                        object\n",
       "Loan.Purpose                       object\n",
       "Debt.To.Income.Ratio               object\n",
       "State                              object\n",
       "Home.Ownership                     object\n",
       "Monthly.Income                    float64\n",
       "FICO.Range                         object\n",
       "Open.CREDIT.Lines                 float64\n",
       "Revolving.CREDIT.Balance          float64\n",
       "Inquiries.in.the.Last.6.Months    float64\n",
       "Employment.Length                  object\n",
       "dtype: object"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "LD.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Amount.Requested                  2500\n",
      "Amount.Funded.By.Investors        2500\n",
      "Interest.Rate                     2500\n",
      "Loan.Length                       2500\n",
      "Loan.Purpose                      2500\n",
      "Debt.To.Income.Ratio              2500\n",
      "State                             2500\n",
      "Home.Ownership                    2500\n",
      "Monthly.Income                    2499\n",
      "FICO.Range                        2500\n",
      "Open.CREDIT.Lines                 2498\n",
      "Revolving.CREDIT.Balance          2498\n",
      "Inquiries.in.the.Last.6.Months    2498\n",
      "Employment.Length                 2500\n",
      "dtype: int64\n",
      "2498\n",
      "2500\n"
     ]
    }
   ],
   "source": [
    "#count ignores instances where there are n/a or null values. \n",
    "print LD.count()\n",
    "\n",
    "#This gives shorter columns, but not sure what happens when you get to calculations.\n",
    "print len(LD.dropna())\n",
    "\n",
    "#Only drop if all columns are nan\n",
    "print len(LD.dropna(how='all'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "Amount.Requested                  0\n",
      "Amount.Funded.By.Investors        0\n",
      "Interest.Rate                     0\n",
      "Loan.Length                       0\n",
      "Loan.Purpose                      0\n",
      "Debt.To.Income.Ratio              0\n",
      "State                             0\n",
      "Home.Ownership                    0\n",
      "Monthly.Income                    1\n",
      "FICO.Range                        0\n",
      "Open.CREDIT.Lines                 2\n",
      "Revolving.CREDIT.Balance          2\n",
      "Inquiries.in.the.Last.6.Months    2\n",
      "Employment.Length                 0\n",
      "dtype: int64\n",
      "7\n"
     ]
    }
   ],
   "source": [
    "#what are the null values?\n",
    "\n",
    "#Tells me if there are any null values. Though this is only for columns with numbers.\n",
    "print LD.isnull().values.any()\n",
    "\n",
    "#Tells me the number of null values in each column\n",
    "print LD.isnull().sum()\n",
    "\n",
    "#Total number over all columns\n",
    "print LD.isnull().sum().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Amount.Requested</th>\n",
       "      <th>Amount.Funded.By.Investors</th>\n",
       "      <th>Interest.Rate</th>\n",
       "      <th>Loan.Length</th>\n",
       "      <th>Loan.Purpose</th>\n",
       "      <th>Debt.To.Income.Ratio</th>\n",
       "      <th>State</th>\n",
       "      <th>Home.Ownership</th>\n",
       "      <th>Monthly.Income</th>\n",
       "      <th>FICO.Range</th>\n",
       "      <th>Open.CREDIT.Lines</th>\n",
       "      <th>Revolving.CREDIT.Balance</th>\n",
       "      <th>Inquiries.in.the.Last.6.Months</th>\n",
       "      <th>Employment.Length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>81174</th>\n",
       "      <td>20000</td>\n",
       "      <td>20000</td>\n",
       "      <td>8.90%</td>\n",
       "      <td>36 months</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>14.90%</td>\n",
       "      <td>SC</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>6541.67</td>\n",
       "      <td>735-739</td>\n",
       "      <td>14</td>\n",
       "      <td>14272</td>\n",
       "      <td>2</td>\n",
       "      <td>&lt; 1 year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99592</th>\n",
       "      <td>19200</td>\n",
       "      <td>19200</td>\n",
       "      <td>12.12%</td>\n",
       "      <td>36 months</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>28.36%</td>\n",
       "      <td>TX</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>4583.33</td>\n",
       "      <td>715-719</td>\n",
       "      <td>12</td>\n",
       "      <td>11140</td>\n",
       "      <td>1</td>\n",
       "      <td>2 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80059</th>\n",
       "      <td>35000</td>\n",
       "      <td>35000</td>\n",
       "      <td>21.98%</td>\n",
       "      <td>60 months</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>23.81%</td>\n",
       "      <td>CA</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>11500.00</td>\n",
       "      <td>690-694</td>\n",
       "      <td>14</td>\n",
       "      <td>21977</td>\n",
       "      <td>1</td>\n",
       "      <td>2 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15825</th>\n",
       "      <td>10000</td>\n",
       "      <td>9975</td>\n",
       "      <td>9.99%</td>\n",
       "      <td>36 months</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>14.30%</td>\n",
       "      <td>KS</td>\n",
       "      <td>MORTGAGE</td>\n",
       "      <td>3833.33</td>\n",
       "      <td>695-699</td>\n",
       "      <td>10</td>\n",
       "      <td>9346</td>\n",
       "      <td>0</td>\n",
       "      <td>5 years</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33182</th>\n",
       "      <td>12000</td>\n",
       "      <td>12000</td>\n",
       "      <td>11.71%</td>\n",
       "      <td>36 months</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>18.78%</td>\n",
       "      <td>NJ</td>\n",
       "      <td>RENT</td>\n",
       "      <td>3195.00</td>\n",
       "      <td>695-699</td>\n",
       "      <td>11</td>\n",
       "      <td>14469</td>\n",
       "      <td>0</td>\n",
       "      <td>9 years</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Amount.Requested  Amount.Funded.By.Investors Interest.Rate Loan.Length  \\\n",
       "81174             20000                       20000         8.90%   36 months   \n",
       "99592             19200                       19200        12.12%   36 months   \n",
       "80059             35000                       35000        21.98%   60 months   \n",
       "15825             10000                        9975         9.99%   36 months   \n",
       "33182             12000                       12000        11.71%   36 months   \n",
       "\n",
       "             Loan.Purpose Debt.To.Income.Ratio State Home.Ownership  \\\n",
       "81174  debt_consolidation               14.90%    SC       MORTGAGE   \n",
       "99592  debt_consolidation               28.36%    TX       MORTGAGE   \n",
       "80059  debt_consolidation               23.81%    CA       MORTGAGE   \n",
       "15825  debt_consolidation               14.30%    KS       MORTGAGE   \n",
       "33182         credit_card               18.78%    NJ           RENT   \n",
       "\n",
       "       Monthly.Income FICO.Range  Open.CREDIT.Lines  Revolving.CREDIT.Balance  \\\n",
       "81174         6541.67    735-739                 14                     14272   \n",
       "99592         4583.33    715-719                 12                     11140   \n",
       "80059        11500.00    690-694                 14                     21977   \n",
       "15825         3833.33    695-699                 10                      9346   \n",
       "33182         3195.00    695-699                 11                     14469   \n",
       "\n",
       "       Inquiries.in.the.Last.6.Months Employment.Length  \n",
       "81174                               2          < 1 year  \n",
       "99592                               1           2 years  \n",
       "80059                               1           2 years  \n",
       "15825                               0           5 years  \n",
       "33182                               0           9 years  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "LD.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Amount.Requested</th>\n",
       "      <th>Amount.Funded.By.Investors</th>\n",
       "      <th>Monthly.Income</th>\n",
       "      <th>Open.CREDIT.Lines</th>\n",
       "      <th>Revolving.CREDIT.Balance</th>\n",
       "      <th>Inquiries.in.the.Last.6.Months</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2500.000000</td>\n",
       "      <td>2500.000000</td>\n",
       "      <td>2499.000000</td>\n",
       "      <td>2498.000000</td>\n",
       "      <td>2498.000000</td>\n",
       "      <td>2498.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>12406.500000</td>\n",
       "      <td>12001.573236</td>\n",
       "      <td>5688.931321</td>\n",
       "      <td>10.075661</td>\n",
       "      <td>15244.559648</td>\n",
       "      <td>0.906325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>7801.544872</td>\n",
       "      <td>7745.320754</td>\n",
       "      <td>3963.118185</td>\n",
       "      <td>4.508644</td>\n",
       "      <td>18308.549795</td>\n",
       "      <td>1.231036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1000.000000</td>\n",
       "      <td>-0.010000</td>\n",
       "      <td>588.500000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>6000.000000</td>\n",
       "      <td>6000.000000</td>\n",
       "      <td>3500.000000</td>\n",
       "      <td>7.000000</td>\n",
       "      <td>5585.750000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>10000.000000</td>\n",
       "      <td>10000.000000</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>10962.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>17000.000000</td>\n",
       "      <td>16000.000000</td>\n",
       "      <td>6800.000000</td>\n",
       "      <td>13.000000</td>\n",
       "      <td>18888.750000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>35000.000000</td>\n",
       "      <td>35000.000000</td>\n",
       "      <td>102750.000000</td>\n",
       "      <td>38.000000</td>\n",
       "      <td>270800.000000</td>\n",
       "      <td>9.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Amount.Requested  Amount.Funded.By.Investors  Monthly.Income  \\\n",
       "count       2500.000000                 2500.000000     2499.000000   \n",
       "mean       12406.500000                12001.573236     5688.931321   \n",
       "std         7801.544872                 7745.320754     3963.118185   \n",
       "min         1000.000000                   -0.010000      588.500000   \n",
       "25%         6000.000000                 6000.000000     3500.000000   \n",
       "50%        10000.000000                10000.000000     5000.000000   \n",
       "75%        17000.000000                16000.000000     6800.000000   \n",
       "max        35000.000000                35000.000000   102750.000000   \n",
       "\n",
       "       Open.CREDIT.Lines  Revolving.CREDIT.Balance  \\\n",
       "count        2498.000000               2498.000000   \n",
       "mean           10.075661              15244.559648   \n",
       "std             4.508644              18308.549795   \n",
       "min             2.000000                  0.000000   \n",
       "25%             7.000000               5585.750000   \n",
       "50%             9.000000              10962.000000   \n",
       "75%            13.000000              18888.750000   \n",
       "max            38.000000             270800.000000   \n",
       "\n",
       "       Inquiries.in.the.Last.6.Months  \n",
       "count                     2498.000000  \n",
       "mean                         0.906325  \n",
       "std                          1.231036  \n",
       "min                          0.000000  \n",
       "25%                          0.000000  \n",
       "50%                          0.000000  \n",
       "75%                          1.000000  \n",
       "max                          9.000000  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "LD.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next: remove outliers above the 95th pcntile."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}