Groupby_and_Merge_Tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# テーブルの結合 (groupby, agg, merge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!git clone https://github.com/nejumi/fe_workshop.git"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import scipy as sp\n",
    "from scipy import optimize\n",
    "import pandas as pd \n",
    "from pandas import DataFrame, Series\n",
    "pd.set_option('display.max_columns', 100)\n",
    "\n",
    "from google.colab import files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# collaboratoryにLendingClub50000.csv, spx2.csvをアップロードする。\n",
    "#uploaded = files.upload()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# ファイルを読み込む\n",
    "df = pd.read_csv('fe_workshop/dataset/LendingClub50000.csv')\n",
    "spx = pd.read_csv('fe_workshop/dataset/spx2.csv', parse_dates=['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>member_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>purpose_type</th>\n",
       "      <th>purpose_detail</th>\n",
       "      <th>title</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>addr_state</th>\n",
       "      <th>dti</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>earliest_cr_line</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_last_delinq</th>\n",
       "      <th>mths_since_last_record</th>\n",
       "      <th>open_acc</th>\n",
       "      <th>pub_rec</th>\n",
       "      <th>revol_bal</th>\n",
       "      <th>revol_util</th>\n",
       "      <th>total_acc</th>\n",
       "      <th>initial_list_status</th>\n",
       "      <th>collections_12_mths_ex_med</th>\n",
       "      <th>mths_since_last_major_derog</th>\n",
       "      <th>application_type</th>\n",
       "      <th>acc_now_delinq</th>\n",
       "      <th>tot_coll_amt</th>\n",
       "      <th>tot_cur_bal</th>\n",
       "      <th>bad_loan</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1529851</td>\n",
       "      <td>1793711</td>\n",
       "      <td>2012</td>\n",
       "      <td>9</td>\n",
       "      <td>16000.0</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt Consolidation / Final Wedding Exp</td>\n",
       "      <td>PwC</td>\n",
       "      <td>3.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>90400.0</td>\n",
       "      <td>080xx</td>\n",
       "      <td>NJ</td>\n",
       "      <td>23.72</td>\n",
       "      <td>A</td>\n",
       "      <td>A4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Oct-2003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3946.0</td>\n",
       "      <td>22.4</td>\n",
       "      <td>42.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>164787.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1824764</td>\n",
       "      <td>2126933</td>\n",
       "      <td>2012</td>\n",
       "      <td>11</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Crush Credit Cards</td>\n",
       "      <td>Morgan Stanley Smith Barney</td>\n",
       "      <td>2.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>42500.0</td>\n",
       "      <td>217xx</td>\n",
       "      <td>MD</td>\n",
       "      <td>17.34</td>\n",
       "      <td>B</td>\n",
       "      <td>B3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Dec-2004</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10927.0</td>\n",
       "      <td>63.5</td>\n",
       "      <td>30.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8806.0</td>\n",
       "      <td>96627.0</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>403548</td>\n",
       "      <td>442721</td>\n",
       "      <td>2009</td>\n",
       "      <td>5</td>\n",
       "      <td>19200.0</td>\n",
       "      <td>wedding</td>\n",
       "      <td>Consolidate debt and pay for wedding</td>\n",
       "      <td>Aggregate Knowledge</td>\n",
       "      <td>1.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>95000.0</td>\n",
       "      <td>940xx</td>\n",
       "      <td>CA</td>\n",
       "      <td>7.58</td>\n",
       "      <td>B</td>\n",
       "      <td>B4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Mar-1999</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14006.0</td>\n",
       "      <td>20.3</td>\n",
       "      <td>26.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>646411</td>\n",
       "      <td>799671</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>Wells BofA Credit Card Refi</td>\n",
       "      <td>Emerson Process Management</td>\n",
       "      <td>1.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>85000.0</td>\n",
       "      <td>926xx</td>\n",
       "      <td>CA</td>\n",
       "      <td>17.07</td>\n",
       "      <td>B</td>\n",
       "      <td>B5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Jun-1999</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36280.0</td>\n",
       "      <td>47.6</td>\n",
       "      <td>25.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>552526</td>\n",
       "      <td>711946</td>\n",
       "      <td>2010</td>\n",
       "      <td>7</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>other</td>\n",
       "      <td>Debt Consolidation</td>\n",
       "      <td>Home Depot</td>\n",
       "      <td>1.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>63500.0</td>\n",
       "      <td>303xx</td>\n",
       "      <td>GA</td>\n",
       "      <td>19.50</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Oct-2002</td>\n",
       "      <td>2.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>595.0</td>\n",
       "      <td>11.4</td>\n",
       "      <td>24.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ID  member_id  year  month  loan_amount        purpose_type  \\\n",
       "0  1529851    1793711  2012      9      16000.0  debt_consolidation   \n",
       "1  1824764    2126933  2012     11       3600.0  debt_consolidation   \n",
       "2   403548     442721  2009      5      19200.0             wedding   \n",
       "3   646411     799671  2011      1      21000.0         credit_card   \n",
       "4   552526     711946  2010      7       4000.0               other   \n",
       "\n",
       "                           purpose_detail                        title  \\\n",
       "0  Debt Consolidation / Final Wedding Exp                          PwC   \n",
       "1                      Crush Credit Cards  Morgan Stanley Smith Barney   \n",
       "2    Consolidate debt and pay for wedding          Aggregate Knowledge   \n",
       "3             Wells BofA Credit Card Refi   Emerson Process Management   \n",
       "4                      Debt Consolidation                   Home Depot   \n",
       "\n",
       "   emp_length home_ownership  annual_inc zip_code addr_state    dti grade  \\\n",
       "0         3.0           RENT     90400.0    080xx         NJ  23.72     A   \n",
       "1         2.0           RENT     42500.0    217xx         MD  17.34     B   \n",
       "2         1.0           RENT     95000.0    940xx         CA   7.58     B   \n",
       "3         1.0           RENT     85000.0    926xx         CA  17.07     B   \n",
       "4         1.0           RENT     63500.0    303xx         GA  19.50     C   \n",
       "\n",
       "  sub_grade  delinq_2yrs earliest_cr_line  inq_last_6mths  \\\n",
       "0        A4          0.0         Oct-2003             0.0   \n",
       "1        B3          0.0         Dec-2004             0.0   \n",
       "2        B4          0.0         Mar-1999             0.0   \n",
       "3        B5          0.0         Jun-1999             1.0   \n",
       "4        C4          1.0         Oct-2002             2.0   \n",
       "\n",
       "   mths_since_last_delinq  mths_since_last_record  open_acc  pub_rec  \\\n",
       "0                     NaN                     NaN      21.0      0.0   \n",
       "1                     NaN                     NaN      16.0      0.0   \n",
       "2                     NaN                     NaN      11.0      0.0   \n",
       "3                     NaN                     NaN      12.0      0.0   \n",
       "4                    23.0                     NaN      16.0      0.0   \n",
       "\n",
       "   revol_bal  revol_util  total_acc initial_list_status  \\\n",
       "0     3946.0        22.4       42.0                   f   \n",
       "1    10927.0        63.5       30.0                   f   \n",
       "2    14006.0        20.3       26.0                   f   \n",
       "3    36280.0        47.6       25.0                   f   \n",
       "4      595.0        11.4       24.0                   f   \n",
       "\n",
       "   collections_12_mths_ex_med  mths_since_last_major_derog application_type  \\\n",
       "0                         0.0                          NaN       INDIVIDUAL   \n",
       "1                         0.0                          NaN       INDIVIDUAL   \n",
       "2                         0.0                          NaN       INDIVIDUAL   \n",
       "3                         0.0                          NaN       INDIVIDUAL   \n",
       "4                         0.0                          NaN       INDIVIDUAL   \n",
       "\n",
       "   acc_now_delinq  tot_coll_amt  tot_cur_bal  bad_loan  \n",
       "0             0.0           0.0     164787.0     False  \n",
       "1             0.0        8806.0      96627.0     False  \n",
       "2             0.0           NaN          NaN     False  \n",
       "3             0.0           NaN          NaN     False  \n",
       "4             0.0           NaN          NaN     False  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1986-01-02</td>\n",
       "      <td>209.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1986-01-03</td>\n",
       "      <td>210.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1986-01-06</td>\n",
       "      <td>210.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986-01-07</td>\n",
       "      <td>213.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1986-01-08</td>\n",
       "      <td>207.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1986-01-09</td>\n",
       "      <td>206.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1986-01-10</td>\n",
       "      <td>205.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1986-01-13</td>\n",
       "      <td>206.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1986-01-14</td>\n",
       "      <td>206.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1986-01-15</td>\n",
       "      <td>208.26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date   close\n",
       "0 1986-01-02  209.59\n",
       "1 1986-01-03  210.88\n",
       "2 1986-01-06  210.65\n",
       "3 1986-01-07  213.80\n",
       "4 1986-01-08  207.97\n",
       "5 1986-01-09  206.11\n",
       "6 1986-01-10  205.96\n",
       "7 1986-01-13  206.72\n",
       "8 1986-01-14  206.64\n",
       "9 1986-01-15  208.26"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spx.iloc[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 年と月を日付から抽出する\n",
    "spx['year'] = spx.date.dt.year\n",
    "spx['month'] = spx.date.dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>close</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1986-01-02</td>\n",
       "      <td>209.59</td>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1986-01-03</td>\n",
       "      <td>210.88</td>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1986-01-06</td>\n",
       "      <td>210.65</td>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986-01-07</td>\n",
       "      <td>213.80</td>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1986-01-08</td>\n",
       "      <td>207.97</td>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date   close  year  month\n",
       "0 1986-01-02  209.59  1986      1\n",
       "1 1986-01-03  210.88  1986      1\n",
       "2 1986-01-06  210.65  1986      1\n",
       "3 1986-01-07  213.80  1986      1\n",
       "4 1986-01-08  207.97  1986      1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spx.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>close_mean</th>\n",
       "      <th>close_min</th>\n",
       "      <th>close_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1986</td>\n",
       "      <td>1</td>\n",
       "      <td>208.194091</td>\n",
       "      <td>203.49</td>\n",
       "      <td>213.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1986</td>\n",
       "      <td>2</td>\n",
       "      <td>219.365263</td>\n",
       "      <td>212.79</td>\n",
       "      <td>226.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1986</td>\n",
       "      <td>3</td>\n",
       "      <td>232.327000</td>\n",
       "      <td>224.34</td>\n",
       "      <td>238.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986</td>\n",
       "      <td>4</td>\n",
       "      <td>237.967727</td>\n",
       "      <td>228.63</td>\n",
       "      <td>244.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1986</td>\n",
       "      <td>5</td>\n",
       "      <td>238.459048</td>\n",
       "      <td>232.76</td>\n",
       "      <td>247.98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  month  close_mean  close_min  close_max\n",
       "0  1986      1  208.194091     203.49     213.80\n",
       "1  1986      2  219.365263     212.79     226.92\n",
       "2  1986      3  232.327000     224.34     238.97\n",
       "3  1986      4  237.967727     228.63     244.74\n",
       "4  1986      5  238.459048     232.76     247.98"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 年と月でgroupbyしてそれぞれS&P終値の統計値を算出する\n",
    "summary = spx.groupby(['year', 'month']).agg(['mean', 'min', 'max'])\n",
    "summary.columns = [\"_\".join(x) for x in summary.columns.ravel()]\n",
    "summary.reset_index(inplace=True)\n",
    "\n",
    "summary.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# ローンデータとマージする\n",
    "df = df.merge(summary, on=['year', 'month'], how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>member_id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>loan_amount</th>\n",
       "      <th>purpose_type</th>\n",
       "      <th>purpose_detail</th>\n",
       "      <th>title</th>\n",
       "      <th>emp_length</th>\n",
       "      <th>home_ownership</th>\n",
       "      <th>annual_inc</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>addr_state</th>\n",
       "      <th>dti</th>\n",
       "      <th>grade</th>\n",
       "      <th>sub_grade</th>\n",
       "      <th>delinq_2yrs</th>\n",
       "      <th>earliest_cr_line</th>\n",
       "      <th>inq_last_6mths</th>\n",
       "      <th>mths_since_last_delinq</th>\n",
       "      <th>mths_since_last_record</th>\n",
       "      <th>open_acc</th>\n",
       "      <th>pub_rec</th>\n",
       "      <th>revol_bal</th>\n",
       "      <th>revol_util</th>\n",
       "      <th>total_acc</th>\n",
       "      <th>initial_list_status</th>\n",
       "      <th>collections_12_mths_ex_med</th>\n",
       "      <th>mths_since_last_major_derog</th>\n",
       "      <th>application_type</th>\n",
       "      <th>acc_now_delinq</th>\n",
       "      <th>tot_coll_amt</th>\n",
       "      <th>tot_cur_bal</th>\n",
       "      <th>bad_loan</th>\n",
       "      <th>close_mean</th>\n",
       "      <th>close_min</th>\n",
       "      <th>close_max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1529851</td>\n",
       "      <td>1793711</td>\n",
       "      <td>2012</td>\n",
       "      <td>9</td>\n",
       "      <td>16000.0</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Debt Consolidation / Final Wedding Exp</td>\n",
       "      <td>PwC</td>\n",
       "      <td>3.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>90400.0</td>\n",
       "      <td>080xx</td>\n",
       "      <td>NJ</td>\n",
       "      <td>23.72</td>\n",
       "      <td>A</td>\n",
       "      <td>A4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Oct-2003</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3946.0</td>\n",
       "      <td>22.4</td>\n",
       "      <td>42.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>164787.0</td>\n",
       "      <td>False</td>\n",
       "      <td>1443.419474</td>\n",
       "      <td>1403.44</td>\n",
       "      <td>1465.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1824764</td>\n",
       "      <td>2126933</td>\n",
       "      <td>2012</td>\n",
       "      <td>11</td>\n",
       "      <td>3600.0</td>\n",
       "      <td>debt_consolidation</td>\n",
       "      <td>Crush Credit Cards</td>\n",
       "      <td>Morgan Stanley Smith Barney</td>\n",
       "      <td>2.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>42500.0</td>\n",
       "      <td>217xx</td>\n",
       "      <td>MD</td>\n",
       "      <td>17.34</td>\n",
       "      <td>B</td>\n",
       "      <td>B3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Dec-2004</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10927.0</td>\n",
       "      <td>63.5</td>\n",
       "      <td>30.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8806.0</td>\n",
       "      <td>96627.0</td>\n",
       "      <td>False</td>\n",
       "      <td>1394.512381</td>\n",
       "      <td>1353.33</td>\n",
       "      <td>1428.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>403548</td>\n",
       "      <td>442721</td>\n",
       "      <td>2009</td>\n",
       "      <td>5</td>\n",
       "      <td>19200.0</td>\n",
       "      <td>wedding</td>\n",
       "      <td>Consolidate debt and pay for wedding</td>\n",
       "      <td>Aggregate Knowledge</td>\n",
       "      <td>1.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>95000.0</td>\n",
       "      <td>940xx</td>\n",
       "      <td>CA</td>\n",
       "      <td>7.58</td>\n",
       "      <td>B</td>\n",
       "      <td>B4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Mar-1999</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14006.0</td>\n",
       "      <td>20.3</td>\n",
       "      <td>26.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>902.408500</td>\n",
       "      <td>877.52</td>\n",
       "      <td>929.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>646411</td>\n",
       "      <td>799671</td>\n",
       "      <td>2011</td>\n",
       "      <td>1</td>\n",
       "      <td>21000.0</td>\n",
       "      <td>credit_card</td>\n",
       "      <td>Wells BofA Credit Card Refi</td>\n",
       "      <td>Emerson Process Management</td>\n",
       "      <td>1.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>85000.0</td>\n",
       "      <td>926xx</td>\n",
       "      <td>CA</td>\n",
       "      <td>17.07</td>\n",
       "      <td>B</td>\n",
       "      <td>B5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Jun-1999</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36280.0</td>\n",
       "      <td>47.6</td>\n",
       "      <td>25.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1282.618500</td>\n",
       "      <td>1269.75</td>\n",
       "      <td>1299.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>552526</td>\n",
       "      <td>711946</td>\n",
       "      <td>2010</td>\n",
       "      <td>7</td>\n",
       "      <td>4000.0</td>\n",
       "      <td>other</td>\n",
       "      <td>Debt Consolidation</td>\n",
       "      <td>Home Depot</td>\n",
       "      <td>1.0</td>\n",
       "      <td>RENT</td>\n",
       "      <td>63500.0</td>\n",
       "      <td>303xx</td>\n",
       "      <td>GA</td>\n",
       "      <td>19.50</td>\n",
       "      <td>C</td>\n",
       "      <td>C4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Oct-2002</td>\n",
       "      <td>2.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>595.0</td>\n",
       "      <td>11.4</td>\n",
       "      <td>24.0</td>\n",
       "      <td>f</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>INDIVIDUAL</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1079.803333</td>\n",
       "      <td>1022.58</td>\n",
       "      <td>1115.01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ID  member_id  year  month  loan_amount        purpose_type  \\\n",
       "0  1529851    1793711  2012      9      16000.0  debt_consolidation   \n",
       "1  1824764    2126933  2012     11       3600.0  debt_consolidation   \n",
       "2   403548     442721  2009      5      19200.0             wedding   \n",
       "3   646411     799671  2011      1      21000.0         credit_card   \n",
       "4   552526     711946  2010      7       4000.0               other   \n",
       "\n",
       "                           purpose_detail                        title  \\\n",
       "0  Debt Consolidation / Final Wedding Exp                          PwC   \n",
       "1                      Crush Credit Cards  Morgan Stanley Smith Barney   \n",
       "2    Consolidate debt and pay for wedding          Aggregate Knowledge   \n",
       "3             Wells BofA Credit Card Refi   Emerson Process Management   \n",
       "4                      Debt Consolidation                   Home Depot   \n",
       "\n",
       "   emp_length home_ownership  annual_inc zip_code addr_state    dti grade  \\\n",
       "0         3.0           RENT     90400.0    080xx         NJ  23.72     A   \n",
       "1         2.0           RENT     42500.0    217xx         MD  17.34     B   \n",
       "2         1.0           RENT     95000.0    940xx         CA   7.58     B   \n",
       "3         1.0           RENT     85000.0    926xx         CA  17.07     B   \n",
       "4         1.0           RENT     63500.0    303xx         GA  19.50     C   \n",
       "\n",
       "  sub_grade  delinq_2yrs earliest_cr_line  inq_last_6mths  \\\n",
       "0        A4          0.0         Oct-2003             0.0   \n",
       "1        B3          0.0         Dec-2004             0.0   \n",
       "2        B4          0.0         Mar-1999             0.0   \n",
       "3        B5          0.0         Jun-1999             1.0   \n",
       "4        C4          1.0         Oct-2002             2.0   \n",
       "\n",
       "   mths_since_last_delinq  mths_since_last_record  open_acc  pub_rec  \\\n",
       "0                     NaN                     NaN      21.0      0.0   \n",
       "1                     NaN                     NaN      16.0      0.0   \n",
       "2                     NaN                     NaN      11.0      0.0   \n",
       "3                     NaN                     NaN      12.0      0.0   \n",
       "4                    23.0                     NaN      16.0      0.0   \n",
       "\n",
       "   revol_bal  revol_util  total_acc initial_list_status  \\\n",
       "0     3946.0        22.4       42.0                   f   \n",
       "1    10927.0        63.5       30.0                   f   \n",
       "2    14006.0        20.3       26.0                   f   \n",
       "3    36280.0        47.6       25.0                   f   \n",
       "4      595.0        11.4       24.0                   f   \n",
       "\n",
       "   collections_12_mths_ex_med  mths_since_last_major_derog application_type  \\\n",
       "0                         0.0                          NaN       INDIVIDUAL   \n",
       "1                         0.0                          NaN       INDIVIDUAL   \n",
       "2                         0.0                          NaN       INDIVIDUAL   \n",
       "3                         0.0                          NaN       INDIVIDUAL   \n",
       "4                         0.0                          NaN       INDIVIDUAL   \n",
       "\n",
       "   acc_now_delinq  tot_coll_amt  tot_cur_bal  bad_loan   close_mean  \\\n",
       "0             0.0           0.0     164787.0     False  1443.419474   \n",
       "1             0.0        8806.0      96627.0     False  1394.512381   \n",
       "2             0.0           NaN          NaN     False   902.408500   \n",
       "3             0.0           NaN          NaN     False  1282.618500   \n",
       "4             0.0           NaN          NaN     False  1079.803333   \n",
       "\n",
       "   close_min  close_max  \n",
       "0    1403.44    1465.77  \n",
       "1    1353.33    1428.39  \n",
       "2     877.52     929.23  \n",
       "3    1269.75    1299.54  \n",
       "4    1022.58    1115.01  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.to_csv('LendingClub50000_spx.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# ダウンロードしたら、DataRobotでもう一度予測してみよう\n",
    "files.download('LendingClub50000_spx.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}