LendingClub/002-LendingClub.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Continuing with ways to explore this data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "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",
    "loansData = pd.read_csv('/home/sophie/projects/LendingClub/data/loansData.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       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  \n"
     ]
    }
   ],
   "source": [
    "#This is the most useful - gives a quick look at the \"spreadsheet\"\n",
    "print loansData.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#Split up FICO.Range\n",
    "loansData['FICO.Start'],loansData['FICO.End']=zip(*loansData['FICO.Range'].apply(lambda x: x.split('-',1)))\n",
    "\n",
    "#Change these values into integers\n",
    "loansData[['FICO.Start','FICO.End']] = loansData[['FICO.Start','FICO.End']].astype('int')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I want to calculate the mean difference between the two values of FICO.Range - see a.\n",
    "\n",
    "Though, I could still get a mean of 4 with varying ranges. \n",
    "\n",
    "So I also make b. \n",
    "\n",
    "I would like a one-liner to print out if there are any values above or below 4. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['36 months', '60 months'], dtype=object)"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = np.mean(loansData['FICO.End']-loansData['FICO.Start'])\n",
    "b = loansData['FICO.End']-loansData['FICO.Start']\n",
    "\n",
    "#List unique values in a DataFrame column\n",
    "b.unique()\n",
    "\n",
    "#can also apply .unique to the actual dataFrame to quickly look for strange values.\n",
    "loansData['Loan.Length'].unique()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Amount.Requested  Amount.Funded.By.Investors Interest.Rate Loan.Length  \\\n",
      "15825             10000                        9975         9.99%   36 months   \n",
      "33182             12000                       12000        11.71%   36 months   \n",
      "62127             10000                       10000        12.12%   36 months   \n",
      "99483             30000                       30000        19.05%   60 months   \n",
      "24168             17400                       17400        11.99%   36 months   \n",
      "\n",
      "             Loan.Purpose Debt.To.Income.Ratio State Home.Ownership  \\\n",
      "15825  debt_consolidation               14.30%    KS       MORTGAGE   \n",
      "33182         credit_card               18.78%    NJ           RENT   \n",
      "62127  debt_consolidation               17.72%    CA           RENT   \n",
      "99483         credit_card               21.25%    FL       MORTGAGE   \n",
      "24168         credit_card               12.47%    AZ           RENT   \n",
      "\n",
      "       Monthly.Income FICO.Range  Open.CREDIT.Lines  Revolving.CREDIT.Balance  \\\n",
      "15825         3833.33    695-699                 10                      9346   \n",
      "33182         3195.00    695-699                 11                     14469   \n",
      "62127         9000.00    695-699                 18                     20317   \n",
      "99483         6250.00    695-699                 12                     34927   \n",
      "24168         6833.33    695-699                  7                     26587   \n",
      "\n",
      "       Inquiries.in.the.Last.6.Months Employment.Length  FICO.Start  FICO.End  \n",
      "15825                               0           5 years         695       699  \n",
      "33182                               0           9 years         695       699  \n",
      "62127                               0           7 years         695       699  \n",
      "99483                               0           6 years         695       699  \n",
      "24168                               0           7 years         695       699  \n"
     ]
    }
   ],
   "source": [
    "#all in one line.\n",
    "loansDataNull = loansData[loansData['FICO.Start'].isin([695])]\n",
    "\n",
    "#show us the values. This prints out everything. or a select view if you use [:]\n",
    "print loansDataNull[0:5]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "153\n"
     ]
    }
   ],
   "source": [
    "#can we do it in one line? Yes. Passing variable d this time. \n",
    "#To search for particular items in a column\n",
    "#You can feed a variable into isin, but you can also make a list inside\n",
    "# e.g. isin([])\n",
    "d = [695]\n",
    "print len(loansData[loansData['FICO.Start'].isin(d)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Amount.Requested                       1000\n",
       "Amount.Funded.By.Investors            -0.01\n",
       "Interest.Rate                        10.00%\n",
       "Loan.Length                       36 months\n",
       "Loan.Purpose                            car\n",
       "Debt.To.Income.Ratio                     0%\n",
       "State                                    AK\n",
       "Home.Ownership                     MORTGAGE\n",
       "Monthly.Income                        588.5\n",
       "FICO.Range                          640-644\n",
       "Open.CREDIT.Lines                         2\n",
       "Revolving.CREDIT.Balance                  0\n",
       "Inquiries.in.the.Last.6.Months            0\n",
       "Employment.Length                    1 year\n",
       "FICO.Start                              640\n",
       "FICO.End                                644\n",
       "dtype: object"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Min value in each column\n",
    "loansData.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Amount.Requested                      35000\n",
       "Amount.Funded.By.Investors            35000\n",
       "Interest.Rate                         9.99%\n",
       "Loan.Length                       60 months\n",
       "Loan.Purpose                        wedding\n",
       "Debt.To.Income.Ratio                  9.99%\n",
       "State                                    WY\n",
       "Home.Ownership                         RENT\n",
       "Monthly.Income                       102750\n",
       "FICO.Range                          830-834\n",
       "Open.CREDIT.Lines                        38\n",
       "Revolving.CREDIT.Balance             270800\n",
       "Inquiries.in.the.Last.6.Months            9\n",
       "Employment.Length                       n/a\n",
       "FICO.Start                              830\n",
       "FICO.End                                834\n",
       "dtype: object"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Max value in each column\n",
    "loansData.max()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## These are extremely useful!! as it doesn't seem to matter what the type\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "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",
       "      <th>FICO.Start</th>\n",
       "      <th>FICO.End</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",
       "      <td>2500.000000</td>\n",
       "      <td>2500.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",
       "      <td>705.888000</td>\n",
       "      <td>709.888000</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",
       "      <td>35.033161</td>\n",
       "      <td>35.033161</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",
       "      <td>640.000000</td>\n",
       "      <td>644.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",
       "      <td>680.000000</td>\n",
       "      <td>684.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",
       "      <td>700.000000</td>\n",
       "      <td>704.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",
       "      <td>725.000000</td>\n",
       "      <td>729.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",
       "      <td>830.000000</td>\n",
       "      <td>834.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   FICO.Start     FICO.End  \n",
       "count                     2498.000000  2500.000000  2500.000000  \n",
       "mean                         0.906325   705.888000   709.888000  \n",
       "std                          1.231036    35.033161    35.033161  \n",
       "min                          0.000000   640.000000   644.000000  \n",
       "25%                          0.000000   680.000000   684.000000  \n",
       "50%                          0.000000   700.000000   704.000000  \n",
       "75%                          1.000000   725.000000   729.000000  \n",
       "max                          9.000000   830.000000   834.000000  "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#This should be one of the first commands on an unknown spreadsheet of data.\n",
    "loansData.describe()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Explore the columns which have less than 2500 - why is this?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "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",
      "FICO.Start                        2500\n",
      "FICO.End                          2500\n",
      "dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#list the names of the column headers - can we feed them into a loop to only print off those with less than the max \n",
    "#number of values?\n",
    "\n",
    "loansData['Monthly.Income'].count()\n",
    "print loansData.count()\n",
    "\n",
    "\n",
    "s = loansData.iloc[0]\n",
    "\n",
    "       "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2499"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loansData['Monthly.Income'].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2500\n",
      "(array([ 8, 10, 11, 12]),)\n",
      "Monthly.Income\n",
      "Open.CREDIT.Lines\n",
      "Revolving.CREDIT.Balance\n",
      "Inquiries.in.the.Last.6.Months\n"
     ]
    }
   ],
   "source": [
    "#How many columns are there?\n",
    "count_columns = len(loansData.count())\n",
    "\n",
    "#What is the maximum number of values?\n",
    "print max(loansData.count())\n",
    "\n",
    "#store this max value in the variable max_count\n",
    "max_count = max(loansData.count())\n",
    "\n",
    "#Which columns have less and how much do they have?\n",
    "columns_less = np.where(loansData.count() < max_count)\n",
    "\n",
    "print columns_less\n",
    "\n",
    "#And how much do they have?\n",
    "#for some reason you have to store dataFrames as s before you can use an array as an attribute\n",
    "\n",
    "#put the above into a variable\n",
    "nan_columns = s.index[columns_less]\n",
    "\n",
    "#print the names of the columns with less than the maximum value.\n",
    "for x in s.index[columns_less]: print x\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Amount.Requested\n",
      "Amount.Funded.By.Investors\n",
      "Interest.Rate\n",
      "Loan.Length\n",
      "Loan.Purpose\n",
      "Debt.To.Income.Ratio\n",
      "State\n",
      "Home.Ownership\n",
      "Monthly.Income\n",
      "FICO.Range\n",
      "Open.CREDIT.Lines\n",
      "Revolving.CREDIT.Balance\n",
      "Inquiries.in.the.Last.6.Months\n",
      "Employment.Length\n",
      "FICO.Start\n",
      "FICO.End\n"
     ]
    }
   ],
   "source": [
    "#to get the headings, say you want to sort by a particular one...\n",
    "for x in s.index[:]: print x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Amount.Requested\n",
      "Amount.Funded.By.Investors\n",
      "Interest.Rate\n",
      "Loan.Length\n",
      "Loan.Purpose\n",
      "Debt.To.Income.Ratio\n",
      "State\n",
      "Home.Ownership\n",
      "Monthly.Income\n",
      "FICO.Range\n",
      "Open.CREDIT.Lines\n",
      "Revolving.CREDIT.Balance\n",
      "Inquiries.in.the.Last.6.Months\n",
      "Employment.Length\n",
      "FICO.Start\n",
      "FICO.End\n"
     ]
    }
   ],
   "source": [
    "#Another way to do this    \n",
    "for x in loansData: \n",
    "    print x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Amount.Requested                            6000\n",
      "Amount.Funded.By.Investors                  6000\n",
      "Interest.Rate                             12.42%\n",
      "Loan.Length                            36 months\n",
      "Loan.Purpose                      major_purchase\n",
      "Debt.To.Income.Ratio                      16.66%\n",
      "State                                         NJ\n",
      "Home.Ownership                              RENT\n",
      "Monthly.Income                              3500\n",
      "FICO.Range                               675-679\n",
      "Open.CREDIT.Lines                              8\n",
      "Revolving.CREDIT.Balance                    7753\n",
      "Inquiries.in.the.Last.6.Months                 0\n",
      "Employment.Length                        5 years\n",
      "FICO.Start                                   675\n",
      "FICO.End                                     679\n",
      "Name: 38576, dtype: object\n"
     ]
    }
   ],
   "source": [
    "for x in loansData.iloc[x]:\n",
    "    if loansData.iloc[x]\n",
    "#lets see if sorting actually works?\n",
    "#can we loop over iloc to find the rows with nan?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Now lets try and drop off the na value, we know there is one.\n",
    "Need to keep playing with this one.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2500\n"
     ]
    }
   ],
   "source": [
    "loansData.dropna()\n",
    "print len(loansData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "77\n"
     ]
    }
   ],
   "source": [
    "#So how many NA, NaNs, Missing etc are there? You have to find the syntax for the nan EXACTLY or the following line won't\n",
    "#pick it up. \n",
    "\n",
    "#I think we have to perform a search on each column seperately\n",
    "\n",
    "print len(loansData[loansData['Employment.Length'].isin(['n/a'])])\n"
   ]
  }
 ],
 "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
}