bonds_ts.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "from sklearn.preprocessing import Imputer\n",
    "import seaborn as sns\n",
    "import datetime\n",
    "import csv\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('ML_Bond_metadata_corrected_dates.csv', header=0, parse_dates=['issue date',\n",
    "                                                        'ratingAgency1EffectiveDate', 'ratingAgency2EffectiveDate'])\n",
    "data = pd.read_csv('dataset.csv', header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 17261 entries, 0 to 17260\n",
      "Data columns (total 24 columns):\n",
      "isin                          17261 non-null object\n",
      "issuer                        17261 non-null int64\n",
      "issue date                    17260 non-null datetime64[ns]\n",
      "Market                        17261 non-null int64\n",
      "amtIssued                     17261 non-null int64\n",
      "amtOutstanding                17261 non-null int64\n",
      "collateralType                17261 non-null int64\n",
      "coupon                        17261 non-null float64\n",
      "couponFrequency               16553 non-null float64\n",
      "couponType                    17261 non-null int64\n",
      "industryGroup                 17261 non-null int64\n",
      "industrySector                17261 non-null int64\n",
      "industrySubgroup              17261 non-null int64\n",
      "maturity                      17023 non-null object\n",
      "maturityType                  17261 non-null int64\n",
      "securityType                  17261 non-null int64\n",
      "paymentRank                   17261 non-null int64\n",
      "144aFlag                      17261 non-null int64\n",
      "ratingAgency1Rating           17261 non-null int64\n",
      "ratingAgency1Watch            17261 non-null int64\n",
      "ratingAgency1EffectiveDate    15480 non-null datetime64[ns]\n",
      "ratingAgency2Rating           17261 non-null int64\n",
      "ratingAgency2Watch            17261 non-null int64\n",
      "ratingAgency2EffectiveDate    15995 non-null datetime64[ns]\n",
      "dtypes: datetime64[ns](3), float64(2), int64(17), object(2)\n",
      "memory usage: 3.2+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>isin</th>\n",
       "      <th>issuer</th>\n",
       "      <th>issue date</th>\n",
       "      <th>Market</th>\n",
       "      <th>amtIssued</th>\n",
       "      <th>amtOutstanding</th>\n",
       "      <th>collateralType</th>\n",
       "      <th>coupon</th>\n",
       "      <th>couponFrequency</th>\n",
       "      <th>couponType</th>\n",
       "      <th>...</th>\n",
       "      <th>maturityType</th>\n",
       "      <th>securityType</th>\n",
       "      <th>paymentRank</th>\n",
       "      <th>144aFlag</th>\n",
       "      <th>ratingAgency1Rating</th>\n",
       "      <th>ratingAgency1Watch</th>\n",
       "      <th>ratingAgency1EffectiveDate</th>\n",
       "      <th>ratingAgency2Rating</th>\n",
       "      <th>ratingAgency2Watch</th>\n",
       "      <th>ratingAgency2EffectiveDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>isin0</td>\n",
       "      <td>490</td>\n",
       "      <td>2011-08-12</td>\n",
       "      <td>2</td>\n",
       "      <td>183260000</td>\n",
       "      <td>183260000</td>\n",
       "      <td>19</td>\n",
       "      <td>10.000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>isin1</td>\n",
       "      <td>1193</td>\n",
       "      <td>2010-10-09</td>\n",
       "      <td>2</td>\n",
       "      <td>261800000</td>\n",
       "      <td>198970000</td>\n",
       "      <td>26</td>\n",
       "      <td>5.500</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>isin2</td>\n",
       "      <td>2943</td>\n",
       "      <td>2014-11-14</td>\n",
       "      <td>2</td>\n",
       "      <td>78540000</td>\n",
       "      <td>78540000</td>\n",
       "      <td>28</td>\n",
       "      <td>5.750</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>10</td>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>isin3</td>\n",
       "      <td>3707</td>\n",
       "      <td>2013-04-03</td>\n",
       "      <td>1</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>28</td>\n",
       "      <td>4.750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>18</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-16</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "      <td>2013-02-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>isin4</td>\n",
       "      <td>3711</td>\n",
       "      <td>2014-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>12</td>\n",
       "      <td>7.125</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>isin5</td>\n",
       "      <td>3711</td>\n",
       "      <td>2014-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>12</td>\n",
       "      <td>7.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>isin6</td>\n",
       "      <td>3278</td>\n",
       "      <td>2012-12-17</td>\n",
       "      <td>1</td>\n",
       "      <td>523600000</td>\n",
       "      <td>523600000</td>\n",
       "      <td>5</td>\n",
       "      <td>1.500</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>2012-12-24</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-07-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>isin7</td>\n",
       "      <td>2238</td>\n",
       "      <td>1985-03-09</td>\n",
       "      <td>1</td>\n",
       "      <td>523600000</td>\n",
       "      <td>135720000</td>\n",
       "      <td>12</td>\n",
       "      <td>1.000</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-03-19</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-11-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>isin8</td>\n",
       "      <td>404</td>\n",
       "      <td>2013-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>837760000</td>\n",
       "      <td>772920000</td>\n",
       "      <td>22</td>\n",
       "      <td>7.375</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-04-17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>isin9</td>\n",
       "      <td>1167</td>\n",
       "      <td>2014-02-27</td>\n",
       "      <td>1</td>\n",
       "      <td>418880000</td>\n",
       "      <td>418880000</td>\n",
       "      <td>22</td>\n",
       "      <td>2.803</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>19</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-03-27</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>2014-07-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    isin  issuer issue date  Market   amtIssued  amtOutstanding  \\\n",
       "0  isin0     490 2011-08-12       2   183260000       183260000   \n",
       "1  isin1    1193 2010-10-09       2   261800000       198970000   \n",
       "2  isin2    2943 2014-11-14       2    78540000        78540000   \n",
       "3  isin3    3707 2013-04-03       1  1570800000      1570800000   \n",
       "4  isin4    3711 2014-11-25       0  1309000000      1309000000   \n",
       "5  isin5    3711 2014-11-25       0  1309000000      1309000000   \n",
       "6  isin6    3278 2012-12-17       1   523600000       523600000   \n",
       "7  isin7    2238 1985-03-09       1   523600000       135720000   \n",
       "8  isin8     404 2013-11-25       0   837760000       772920000   \n",
       "9  isin9    1167 2014-02-27       1   418880000       418880000   \n",
       "\n",
       "   collateralType  coupon  couponFrequency  couponType  \\\n",
       "0              19  10.000              2.0           1   \n",
       "1              26   5.500              2.0           1   \n",
       "2              28   5.750              2.0           1   \n",
       "3              28   4.750              1.0           6   \n",
       "4              12   7.125              1.0           6   \n",
       "5              12   7.000              1.0           6   \n",
       "6               5   1.500              1.0           1   \n",
       "7              12   1.000              4.0           3   \n",
       "8              22   7.375              2.0           1   \n",
       "9              22   2.803              2.0           1   \n",
       "\n",
       "              ...              maturityType  securityType  paymentRank  \\\n",
       "0             ...                         5             3            3   \n",
       "1             ...                        10             5            8   \n",
       "2             ...                        10             5            9   \n",
       "3             ...                         5             2            9   \n",
       "4             ...                        13             3            5   \n",
       "5             ...                        13             3            5   \n",
       "6             ...                         0             2            8   \n",
       "7             ...                        13             3            5   \n",
       "8             ...                         5             5            2   \n",
       "9             ...                         0             5            2   \n",
       "\n",
       "  144aFlag  ratingAgency1Rating  ratingAgency1Watch  \\\n",
       "0        0                    0                   0   \n",
       "1        0                    0                   0   \n",
       "2        1                    0                   0   \n",
       "3        0                   18                   0   \n",
       "4        0                   15                   0   \n",
       "5        0                   15                   0   \n",
       "6        0                    5                   0   \n",
       "7        0                   19                   0   \n",
       "8        1                   16                   0   \n",
       "9        1                   19                   0   \n",
       "\n",
       "   ratingAgency1EffectiveDate  ratingAgency2Rating  ratingAgency2Watch  \\\n",
       "0                         NaT                    0                   0   \n",
       "1                         NaT                    0                   0   \n",
       "2                         NaT                    0                   0   \n",
       "3                  2016-03-16                   22                   0   \n",
       "4                  2016-03-16                    0                   0   \n",
       "5                  2016-03-16                    0                   0   \n",
       "6                  2012-12-24                    1                   0   \n",
       "7                  2015-03-19                   16                   0   \n",
       "8                  2014-04-17                    0                   0   \n",
       "9                  2014-03-27                   16                   0   \n",
       "\n",
       "   ratingAgency2EffectiveDate  \n",
       "0                         NaT  \n",
       "1                         NaT  \n",
       "2                         NaT  \n",
       "3                  2013-02-05  \n",
       "4                         NaT  \n",
       "5                         NaT  \n",
       "6                  2014-07-11  \n",
       "7                  2014-11-02  \n",
       "8                         NaT  \n",
       "9                  2014-07-03  \n",
       "\n",
       "[10 rows x 24 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import timedelta, date\n",
    "\n",
    "def daterange(start_date, end_date):\n",
    "    for n in range(int ((end_date - start_date).days)):\n",
    "        yield start_date + timedelta(n)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>isin</th>\n",
       "      <th>issuer</th>\n",
       "      <th>issue date</th>\n",
       "      <th>Market</th>\n",
       "      <th>amtIssued</th>\n",
       "      <th>amtOutstanding</th>\n",
       "      <th>collateralType</th>\n",
       "      <th>coupon</th>\n",
       "      <th>couponFrequency</th>\n",
       "      <th>couponType</th>\n",
       "      <th>...</th>\n",
       "      <th>05Jun2016_B</th>\n",
       "      <th>05Jun2016_S</th>\n",
       "      <th>06Jun2016_B</th>\n",
       "      <th>06Jun2016_S</th>\n",
       "      <th>07Jun2016_B</th>\n",
       "      <th>07Jun2016_S</th>\n",
       "      <th>08Jun2016_B</th>\n",
       "      <th>08Jun2016_S</th>\n",
       "      <th>09Jun2016_B</th>\n",
       "      <th>09Jun2016_S</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>isin0</td>\n",
       "      <td>490</td>\n",
       "      <td>2011-08-12</td>\n",
       "      <td>2</td>\n",
       "      <td>183260000</td>\n",
       "      <td>183260000</td>\n",
       "      <td>19</td>\n",
       "      <td>10.000</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>isin1</td>\n",
       "      <td>1193</td>\n",
       "      <td>2010-10-09</td>\n",
       "      <td>2</td>\n",
       "      <td>261800000</td>\n",
       "      <td>198970000</td>\n",
       "      <td>26</td>\n",
       "      <td>5.500</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>isin2</td>\n",
       "      <td>2943</td>\n",
       "      <td>2014-11-14</td>\n",
       "      <td>2</td>\n",
       "      <td>78540000</td>\n",
       "      <td>78540000</td>\n",
       "      <td>28</td>\n",
       "      <td>5.750</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>isin3</td>\n",
       "      <td>3707</td>\n",
       "      <td>2013-04-03</td>\n",
       "      <td>1</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>28</td>\n",
       "      <td>4.750</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>isin4</td>\n",
       "      <td>3711</td>\n",
       "      <td>2014-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>12</td>\n",
       "      <td>7.125</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>isin5</td>\n",
       "      <td>3711</td>\n",
       "      <td>2014-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>12</td>\n",
       "      <td>7.000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>isin6</td>\n",
       "      <td>3278</td>\n",
       "      <td>2012-12-17</td>\n",
       "      <td>1</td>\n",
       "      <td>523600000</td>\n",
       "      <td>523600000</td>\n",
       "      <td>5</td>\n",
       "      <td>1.500</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>isin7</td>\n",
       "      <td>2238</td>\n",
       "      <td>1985-03-09</td>\n",
       "      <td>1</td>\n",
       "      <td>523600000</td>\n",
       "      <td>135720000</td>\n",
       "      <td>12</td>\n",
       "      <td>1.000</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>isin8</td>\n",
       "      <td>404</td>\n",
       "      <td>2013-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>837760000</td>\n",
       "      <td>772920000</td>\n",
       "      <td>22</td>\n",
       "      <td>7.375</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>isin9</td>\n",
       "      <td>1167</td>\n",
       "      <td>2014-02-27</td>\n",
       "      <td>1</td>\n",
       "      <td>418880000</td>\n",
       "      <td>418880000</td>\n",
       "      <td>22</td>\n",
       "      <td>2.803</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 196 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    isin  issuer issue date  Market   amtIssued  amtOutstanding  \\\n",
       "0  isin0     490 2011-08-12       2   183260000       183260000   \n",
       "1  isin1    1193 2010-10-09       2   261800000       198970000   \n",
       "2  isin2    2943 2014-11-14       2    78540000        78540000   \n",
       "3  isin3    3707 2013-04-03       1  1570800000      1570800000   \n",
       "4  isin4    3711 2014-11-25       0  1309000000      1309000000   \n",
       "5  isin5    3711 2014-11-25       0  1309000000      1309000000   \n",
       "6  isin6    3278 2012-12-17       1   523600000       523600000   \n",
       "7  isin7    2238 1985-03-09       1   523600000       135720000   \n",
       "8  isin8     404 2013-11-25       0   837760000       772920000   \n",
       "9  isin9    1167 2014-02-27       1   418880000       418880000   \n",
       "\n",
       "   collateralType  coupon  couponFrequency  couponType     ...       \\\n",
       "0              19  10.000              2.0           1     ...        \n",
       "1              26   5.500              2.0           1     ...        \n",
       "2              28   5.750              2.0           1     ...        \n",
       "3              28   4.750              1.0           6     ...        \n",
       "4              12   7.125              1.0           6     ...        \n",
       "5              12   7.000              1.0           6     ...        \n",
       "6               5   1.500              1.0           1     ...        \n",
       "7              12   1.000              4.0           3     ...        \n",
       "8              22   7.375              2.0           1     ...        \n",
       "9              22   2.803              2.0           1     ...        \n",
       "\n",
       "   05Jun2016_B  05Jun2016_S  06Jun2016_B 06Jun2016_S  07Jun2016_B  \\\n",
       "0          0.0          0.0          0.0         0.0          0.0   \n",
       "1          0.0          0.0          0.0         0.0          0.0   \n",
       "2          0.0          0.0          0.0         0.0          0.0   \n",
       "3          0.0          0.0          0.0         0.0          0.0   \n",
       "4          0.0          0.0          0.0         0.0          0.0   \n",
       "5          0.0          0.0          0.0         0.0          0.0   \n",
       "6          0.0          0.0          0.0         0.0          0.0   \n",
       "7          0.0          0.0          0.0         0.0          0.0   \n",
       "8          0.0          0.0          0.0         0.0          0.0   \n",
       "9          0.0          0.0          0.0         0.0          0.0   \n",
       "\n",
       "   07Jun2016_S  08Jun2016_B  08Jun2016_S  09Jun2016_B  09Jun2016_S  \n",
       "0          0.0          0.0          0.0          0.0          0.0  \n",
       "1          0.0          0.0          0.0          0.0          0.0  \n",
       "2          0.0          0.0          0.0          0.0          0.0  \n",
       "3          0.0          0.0          0.0          0.0          0.0  \n",
       "4          0.0          0.0          0.0          0.0          0.0  \n",
       "5          0.0          0.0          0.0          0.0          0.0  \n",
       "6          0.0          0.0          0.0          0.0          0.0  \n",
       "7          0.0          0.0          0.0          0.0          0.0  \n",
       "8          0.0          0.0          0.0          0.0          0.0  \n",
       "9          0.0          0.0          0.0          0.0          0.0  \n",
       "\n",
       "[10 rows x 196 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = date(2016, 3, 16)\n",
    "end_date = date(2016, 6, 10)\n",
    "for single_date in daterange(start_date, end_date):\n",
    "    df[single_date.strftime(\"%d%b%Y\")+'_B'] = np.zeros(df.shape[0])\n",
    "    df[single_date.strftime(\"%d%b%Y\")+'_S'] = np.zeros(df.shape[0])\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100000\n",
      "200000\n",
      "300000\n",
      "400000\n",
      "500000\n",
      "600000\n",
      "700000\n",
      "800000\n",
      "900000\n",
      "1000000\n",
      "1100000\n",
      "1200000\n",
      "1300000\n",
      "1400000\n",
      "1500000\n",
      "1600000\n",
      "1700000\n",
      "1800000\n",
      "1900000\n",
      "2000000\n",
      "2100000\n",
      "2200000\n",
      "2300000\n"
     ]
    }
   ],
   "source": [
    "i=0\n",
    "while i<data.shape[0]:\n",
    "    #print i\n",
    "    j=i+1\n",
    "    while j<data.shape[0] and data['isin'][j]==data['isin'][i]:\n",
    "        if j%100000==0:\n",
    "            print j\n",
    "        j=j+1\n",
    "    for k in range(i, j):\n",
    "        if data['date'][k][0] == ' ':\n",
    "            x = '0' + data['date'][k][1:]\n",
    "        else:\n",
    "            x = data['date'][k]\n",
    "        df.loc[df['isin']==data['isin'][i], x + '_' + data['side'][k]] += data['volume'][k]\n",
    "    i=j"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "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>isin</th>\n",
       "      <th>issuer</th>\n",
       "      <th>issue date</th>\n",
       "      <th>Market</th>\n",
       "      <th>amtIssued</th>\n",
       "      <th>amtOutstanding</th>\n",
       "      <th>issued2</th>\n",
       "      <th>outstanding2</th>\n",
       "      <th>collateralType</th>\n",
       "      <th>collateral2</th>\n",
       "      <th>...</th>\n",
       "      <th>05Jun2016_B</th>\n",
       "      <th>05Jun2016_S</th>\n",
       "      <th>06Jun2016_B</th>\n",
       "      <th>06Jun2016_S</th>\n",
       "      <th>07Jun2016_B</th>\n",
       "      <th>07Jun2016_S</th>\n",
       "      <th>08Jun2016_B</th>\n",
       "      <th>08Jun2016_S</th>\n",
       "      <th>09Jun2016_B</th>\n",
       "      <th>09Jun2016_S</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>isin0</td>\n",
       "      <td>490</td>\n",
       "      <td>2011-08-12</td>\n",
       "      <td>2</td>\n",
       "      <td>183260000</td>\n",
       "      <td>183260000</td>\n",
       "      <td>3.358423e+16</td>\n",
       "      <td>3.358423e+16</td>\n",
       "      <td>19</td>\n",
       "      <td>361</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>1</th>\n",
       "      <td>isin1</td>\n",
       "      <td>1193</td>\n",
       "      <td>2010-10-09</td>\n",
       "      <td>2</td>\n",
       "      <td>261800000</td>\n",
       "      <td>198970000</td>\n",
       "      <td>6.853924e+16</td>\n",
       "      <td>3.958906e+16</td>\n",
       "      <td>26</td>\n",
       "      <td>676</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>100000</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>isin2</td>\n",
       "      <td>2943</td>\n",
       "      <td>2014-11-14</td>\n",
       "      <td>2</td>\n",
       "      <td>78540000</td>\n",
       "      <td>78540000</td>\n",
       "      <td>6.168532e+15</td>\n",
       "      <td>6.168532e+15</td>\n",
       "      <td>28</td>\n",
       "      <td>784</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>3</th>\n",
       "      <td>isin3</td>\n",
       "      <td>3707</td>\n",
       "      <td>2013-04-03</td>\n",
       "      <td>1</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>28</td>\n",
       "      <td>784</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>310000</td>\n",
       "      <td>0</td>\n",
       "      <td>210000</td>\n",
       "      <td>0</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>isin4</td>\n",
       "      <td>3711</td>\n",
       "      <td>2014-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1.713481e+18</td>\n",
       "      <td>1.713481e+18</td>\n",
       "      <td>12</td>\n",
       "      <td>144</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>5</th>\n",
       "      <td>isin5</td>\n",
       "      <td>3711</td>\n",
       "      <td>2014-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1.713481e+18</td>\n",
       "      <td>1.713481e+18</td>\n",
       "      <td>12</td>\n",
       "      <td>144</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>6</th>\n",
       "      <td>isin6</td>\n",
       "      <td>3278</td>\n",
       "      <td>2012-12-17</td>\n",
       "      <td>1</td>\n",
       "      <td>523600000</td>\n",
       "      <td>523600000</td>\n",
       "      <td>2.741570e+17</td>\n",
       "      <td>2.741570e+17</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>120000</td>\n",
       "      <td>3990000</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>7</th>\n",
       "      <td>isin7</td>\n",
       "      <td>2238</td>\n",
       "      <td>1985-03-09</td>\n",
       "      <td>1</td>\n",
       "      <td>523600000</td>\n",
       "      <td>135720000</td>\n",
       "      <td>2.741570e+17</td>\n",
       "      <td>1.841992e+16</td>\n",
       "      <td>12</td>\n",
       "      <td>144</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>8</th>\n",
       "      <td>isin8</td>\n",
       "      <td>404</td>\n",
       "      <td>2013-11-25</td>\n",
       "      <td>0</td>\n",
       "      <td>837760000</td>\n",
       "      <td>772920000</td>\n",
       "      <td>7.018418e+17</td>\n",
       "      <td>5.974053e+17</td>\n",
       "      <td>22</td>\n",
       "      <td>484</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100000</td>\n",
       "      <td>0</td>\n",
       "      <td>210000</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>9</th>\n",
       "      <td>isin9</td>\n",
       "      <td>1167</td>\n",
       "      <td>2014-02-27</td>\n",
       "      <td>1</td>\n",
       "      <td>418880000</td>\n",
       "      <td>418880000</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>22</td>\n",
       "      <td>484</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>10</th>\n",
       "      <td>isin10</td>\n",
       "      <td>1167</td>\n",
       "      <td>2014-02-27</td>\n",
       "      <td>1</td>\n",
       "      <td>418880000</td>\n",
       "      <td>418880000</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>22</td>\n",
       "      <td>484</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>11</th>\n",
       "      <td>isin11</td>\n",
       "      <td>1167</td>\n",
       "      <td>2014-02-27</td>\n",
       "      <td>1</td>\n",
       "      <td>418880000</td>\n",
       "      <td>418880000</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>22</td>\n",
       "      <td>484</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>30000</td>\n",
       "      <td>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>isin12</td>\n",
       "      <td>1167</td>\n",
       "      <td>2014-02-27</td>\n",
       "      <td>1</td>\n",
       "      <td>418880000</td>\n",
       "      <td>418880000</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>22</td>\n",
       "      <td>484</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>10000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>isin13</td>\n",
       "      <td>1167</td>\n",
       "      <td>2014-02-27</td>\n",
       "      <td>1</td>\n",
       "      <td>418880000</td>\n",
       "      <td>418880000</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>1.754605e+17</td>\n",
       "      <td>22</td>\n",
       "      <td>484</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100000</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>14</th>\n",
       "      <td>isin14</td>\n",
       "      <td>11</td>\n",
       "      <td>2014-10-04</td>\n",
       "      <td>2</td>\n",
       "      <td>424120000</td>\n",
       "      <td>480830000</td>\n",
       "      <td>1.798778e+17</td>\n",
       "      <td>2.311975e+17</td>\n",
       "      <td>22</td>\n",
       "      <td>484</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3680000</td>\n",
       "      <td>3680000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3680000</td>\n",
       "      <td>3680000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>isin15</td>\n",
       "      <td>12</td>\n",
       "      <td>2012-02-16</td>\n",
       "      <td>1</td>\n",
       "      <td>523600000</td>\n",
       "      <td>523600000</td>\n",
       "      <td>2.741570e+17</td>\n",
       "      <td>2.741570e+17</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2190000</td>\n",
       "      <td>14630000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>220000</td>\n",
       "      <td>200000</td>\n",
       "      <td>720000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>isin16</td>\n",
       "      <td>12</td>\n",
       "      <td>2012-02-16</td>\n",
       "      <td>1</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1.713481e+18</td>\n",
       "      <td>1.713481e+18</td>\n",
       "      <td>5</td>\n",
       "      <td>25</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>700000</td>\n",
       "      <td>380000</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>isin17</td>\n",
       "      <td>12</td>\n",
       "      <td>2012-02-16</td>\n",
       "      <td>1</td>\n",
       "      <td>785400000</td>\n",
       "      <td>785400000</td>\n",
       "      <td>6.168532e+17</td>\n",
       "      <td>6.168532e+17</td>\n",
       "      <td>5</td>\n",
       "      <td>25</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>18</th>\n",
       "      <td>isin18</td>\n",
       "      <td>13</td>\n",
       "      <td>2011-03-29</td>\n",
       "      <td>1</td>\n",
       "      <td>680680000</td>\n",
       "      <td>680680000</td>\n",
       "      <td>4.633253e+17</td>\n",
       "      <td>4.633253e+17</td>\n",
       "      <td>26</td>\n",
       "      <td>676</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>11510000</td>\n",
       "      <td>11510000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>isin19</td>\n",
       "      <td>18</td>\n",
       "      <td>1996-06-11</td>\n",
       "      <td>2</td>\n",
       "      <td>52360000</td>\n",
       "      <td>52360000</td>\n",
       "      <td>2.741570e+15</td>\n",
       "      <td>2.741570e+15</td>\n",
       "      <td>24</td>\n",
       "      <td>576</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10000</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>20</th>\n",
       "      <td>isin20</td>\n",
       "      <td>18</td>\n",
       "      <td>1997-09-30</td>\n",
       "      <td>2</td>\n",
       "      <td>26180000</td>\n",
       "      <td>26180000</td>\n",
       "      <td>6.853924e+14</td>\n",
       "      <td>6.853924e+14</td>\n",
       "      <td>24</td>\n",
       "      <td>576</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>21</th>\n",
       "      <td>isin21</td>\n",
       "      <td>3144</td>\n",
       "      <td>1993-09-03</td>\n",
       "      <td>0</td>\n",
       "      <td>261800000</td>\n",
       "      <td>141960000</td>\n",
       "      <td>6.853924e+16</td>\n",
       "      <td>2.015264e+16</td>\n",
       "      <td>28</td>\n",
       "      <td>784</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>22</th>\n",
       "      <td>isin22</td>\n",
       "      <td>3144</td>\n",
       "      <td>1993-07-29</td>\n",
       "      <td>0</td>\n",
       "      <td>157080000</td>\n",
       "      <td>157080000</td>\n",
       "      <td>2.467413e+16</td>\n",
       "      <td>2.467413e+16</td>\n",
       "      <td>28</td>\n",
       "      <td>784</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>23</th>\n",
       "      <td>isin23</td>\n",
       "      <td>3142</td>\n",
       "      <td>2003-12-19</td>\n",
       "      <td>0</td>\n",
       "      <td>522850000</td>\n",
       "      <td>522850000</td>\n",
       "      <td>2.733721e+17</td>\n",
       "      <td>2.733721e+17</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>120000</td>\n",
       "      <td>0</td>\n",
       "      <td>80000</td>\n",
       "      <td>30000</td>\n",
       "      <td>630000</td>\n",
       "      <td>40000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>isin24</td>\n",
       "      <td>24</td>\n",
       "      <td>2015-07-05</td>\n",
       "      <td>1</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>28</td>\n",
       "      <td>784</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12560000</td>\n",
       "      <td>2620000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2620000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>isin25</td>\n",
       "      <td>29</td>\n",
       "      <td>2013-03-27</td>\n",
       "      <td>0</td>\n",
       "      <td>523600000</td>\n",
       "      <td>523600000</td>\n",
       "      <td>2.741570e+17</td>\n",
       "      <td>2.741570e+17</td>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>60000</td>\n",
       "      <td>160000</td>\n",
       "      <td>0</td>\n",
       "      <td>40000</td>\n",
       "      <td>540000</td>\n",
       "      <td>4920000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>isin26</td>\n",
       "      <td>3470</td>\n",
       "      <td>2013-08-22</td>\n",
       "      <td>1</td>\n",
       "      <td>261800000</td>\n",
       "      <td>261800000</td>\n",
       "      <td>6.853924e+16</td>\n",
       "      <td>6.853924e+16</td>\n",
       "      <td>28</td>\n",
       "      <td>784</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>27</th>\n",
       "      <td>isin27</td>\n",
       "      <td>24</td>\n",
       "      <td>2011-08-11</td>\n",
       "      <td>1</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>26</td>\n",
       "      <td>676</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1050000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8640000</td>\n",
       "      <td>0</td>\n",
       "      <td>4290000</td>\n",
       "      <td>0</td>\n",
       "      <td>260000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>isin28</td>\n",
       "      <td>24</td>\n",
       "      <td>2013-08-08</td>\n",
       "      <td>1</td>\n",
       "      <td>1832600000</td>\n",
       "      <td>1832600000</td>\n",
       "      <td>3.358423e+18</td>\n",
       "      <td>3.358423e+18</td>\n",
       "      <td>26</td>\n",
       "      <td>676</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>210000</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>29</th>\n",
       "      <td>isin29</td>\n",
       "      <td>24</td>\n",
       "      <td>2013-08-08</td>\n",
       "      <td>1</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>26</td>\n",
       "      <td>676</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>11510000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>30 rows × 202 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      isin  issuer  issue date  Market   amtIssued  amtOutstanding  \\\n",
       "0    isin0     490  2011-08-12       2   183260000       183260000   \n",
       "1    isin1    1193  2010-10-09       2   261800000       198970000   \n",
       "2    isin2    2943  2014-11-14       2    78540000        78540000   \n",
       "3    isin3    3707  2013-04-03       1  1570800000      1570800000   \n",
       "4    isin4    3711  2014-11-25       0  1309000000      1309000000   \n",
       "5    isin5    3711  2014-11-25       0  1309000000      1309000000   \n",
       "6    isin6    3278  2012-12-17       1   523600000       523600000   \n",
       "7    isin7    2238  1985-03-09       1   523600000       135720000   \n",
       "8    isin8     404  2013-11-25       0   837760000       772920000   \n",
       "9    isin9    1167  2014-02-27       1   418880000       418880000   \n",
       "10  isin10    1167  2014-02-27       1   418880000       418880000   \n",
       "11  isin11    1167  2014-02-27       1   418880000       418880000   \n",
       "12  isin12    1167  2014-02-27       1   418880000       418880000   \n",
       "13  isin13    1167  2014-02-27       1   418880000       418880000   \n",
       "14  isin14      11  2014-10-04       2   424120000       480830000   \n",
       "15  isin15      12  2012-02-16       1   523600000       523600000   \n",
       "16  isin16      12  2012-02-16       1  1309000000      1309000000   \n",
       "17  isin17      12  2012-02-16       1   785400000       785400000   \n",
       "18  isin18      13  2011-03-29       1   680680000       680680000   \n",
       "19  isin19      18  1996-06-11       2    52360000        52360000   \n",
       "20  isin20      18  1997-09-30       2    26180000        26180000   \n",
       "21  isin21    3144  1993-09-03       0   261800000       141960000   \n",
       "22  isin22    3144  1993-07-29       0   157080000       157080000   \n",
       "23  isin23    3142  2003-12-19       0   522850000       522850000   \n",
       "24  isin24      24  2015-07-05       1  1570800000      1570800000   \n",
       "25  isin25      29  2013-03-27       0   523600000       523600000   \n",
       "26  isin26    3470  2013-08-22       1   261800000       261800000   \n",
       "27  isin27      24  2011-08-11       1  1570800000      1570800000   \n",
       "28  isin28      24  2013-08-08       1  1832600000      1832600000   \n",
       "29  isin29      24  2013-08-08       1  1570800000      1570800000   \n",
       "\n",
       "         issued2  outstanding2  collateralType  collateral2     ...       \\\n",
       "0   3.358423e+16  3.358423e+16              19          361     ...        \n",
       "1   6.853924e+16  3.958906e+16              26          676     ...        \n",
       "2   6.168532e+15  6.168532e+15              28          784     ...        \n",
       "3   2.467413e+18  2.467413e+18              28          784     ...        \n",
       "4   1.713481e+18  1.713481e+18              12          144     ...        \n",
       "5   1.713481e+18  1.713481e+18              12          144     ...        \n",
       "6   2.741570e+17  2.741570e+17               5           25     ...        \n",
       "7   2.741570e+17  1.841992e+16              12          144     ...        \n",
       "8   7.018418e+17  5.974053e+17              22          484     ...        \n",
       "9   1.754605e+17  1.754605e+17              22          484     ...        \n",
       "10  1.754605e+17  1.754605e+17              22          484     ...        \n",
       "11  1.754605e+17  1.754605e+17              22          484     ...        \n",
       "12  1.754605e+17  1.754605e+17              22          484     ...        \n",
       "13  1.754605e+17  1.754605e+17              22          484     ...        \n",
       "14  1.798778e+17  2.311975e+17              22          484     ...        \n",
       "15  2.741570e+17  2.741570e+17               5           25     ...        \n",
       "16  1.713481e+18  1.713481e+18               5           25     ...        \n",
       "17  6.168532e+17  6.168532e+17               5           25     ...        \n",
       "18  4.633253e+17  4.633253e+17              26          676     ...        \n",
       "19  2.741570e+15  2.741570e+15              24          576     ...        \n",
       "20  6.853924e+14  6.853924e+14              24          576     ...        \n",
       "21  6.853924e+16  2.015264e+16              28          784     ...        \n",
       "22  2.467413e+16  2.467413e+16              28          784     ...        \n",
       "23  2.733721e+17  2.733721e+17               5           25     ...        \n",
       "24  2.467413e+18  2.467413e+18              28          784     ...        \n",
       "25  2.741570e+17  2.741570e+17               5           25     ...        \n",
       "26  6.853924e+16  6.853924e+16              28          784     ...        \n",
       "27  2.467413e+18  2.467413e+18              26          676     ...        \n",
       "28  3.358423e+18  3.358423e+18              26          676     ...        \n",
       "29  2.467413e+18  2.467413e+18              26          676     ...        \n",
       "\n",
       "    05Jun2016_B  05Jun2016_S  06Jun2016_B  06Jun2016_S  07Jun2016_B  \\\n",
       "0             0            0            0            0            0   \n",
       "1             0            0            0            0            0   \n",
       "2             0            0            0            0            0   \n",
       "3             0            0            0            0       310000   \n",
       "4             0            0            0            0            0   \n",
       "5             0            0            0            0            0   \n",
       "6             0            0       120000      3990000            0   \n",
       "7             0            0            0            0            0   \n",
       "8             0            0       100000            0       210000   \n",
       "9             0            0            0            0            0   \n",
       "10            0            0            0            0            0   \n",
       "11            0            0            0            0            0   \n",
       "12            0            0            0            0            0   \n",
       "13            0            0       100000            0            0   \n",
       "14            0            0      3680000      3680000            0   \n",
       "15            0            0      2190000     14630000            0   \n",
       "16            0            0            0            0            0   \n",
       "17            0            0            0            0            0   \n",
       "18            0            0            0            0            0   \n",
       "19            0            0            0            0        10000   \n",
       "20            0            0            0            0            0   \n",
       "21            0            0            0            0            0   \n",
       "22            0            0            0            0            0   \n",
       "23            0            0            0            0       120000   \n",
       "24            0            0     12560000      2620000            0   \n",
       "25            0            0            0        60000       160000   \n",
       "26            0            0            0            0            0   \n",
       "27            0            0      1050000            0            0   \n",
       "28            0            0       210000            0            0   \n",
       "29            0            0            0            0            0   \n",
       "\n",
       "    07Jun2016_S  08Jun2016_B 08Jun2016_S  09Jun2016_B  09Jun2016_S  \n",
       "0             0            0           0            0            0  \n",
       "1             0            0           0       100000        50000  \n",
       "2             0            0           0            0            0  \n",
       "3             0       210000           0        50000        50000  \n",
       "4             0            0           0            0            0  \n",
       "5             0            0           0            0            0  \n",
       "6             0            0           0            0            0  \n",
       "7             0            0           0            0            0  \n",
       "8             0            0           0            0            0  \n",
       "9             0            0           0            0            0  \n",
       "10            0            0           0            0            0  \n",
       "11            0        30000       10000            0            0  \n",
       "12            0            0       10000            0            0  \n",
       "13            0            0           0            0            0  \n",
       "14            0            0           0      3680000      3680000  \n",
       "15            0            0      220000       200000       720000  \n",
       "16            0       700000      380000            0        10000  \n",
       "17            0            0           0            0            0  \n",
       "18            0     11510000    11510000            0            0  \n",
       "19            0            0           0            0            0  \n",
       "20            0            0           0            0            0  \n",
       "21            0            0           0            0            0  \n",
       "22            0            0           0            0            0  \n",
       "23            0        80000       30000       630000        40000  \n",
       "24            0            0     2620000            0            0  \n",
       "25            0        40000      540000      4920000            0  \n",
       "26            0            0           0            0            0  \n",
       "27      8640000            0     4290000            0       260000  \n",
       "28            0            0           0            0            0  \n",
       "29            0            0           0            0     11510000  \n",
       "\n",
       "[30 rows x 202 columns]"
      ]
     },
     "execution_count": 166,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df.to_csv('data.csv')\n",
    "df = pd.read_csv('data.csv', header=0)\n",
    "df.head(30)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#df['rating12'] = df['ratingAgency1Rating']*df['ratingAgency1Rating']\n",
    "df['collateral2'] = df['collateralType']*df['collateralType']\n",
    "df.to_csv('data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>issuer</th>\n",
       "      <th>Market</th>\n",
       "      <th>amtIssued</th>\n",
       "      <th>amtOutstanding</th>\n",
       "      <th>issued2</th>\n",
       "      <th>outstanding2</th>\n",
       "      <th>collateralType</th>\n",
       "      <th>collateral2</th>\n",
       "      <th>coupon</th>\n",
       "      <th>coupon2</th>\n",
       "      <th>...</th>\n",
       "      <th>31May2016_S</th>\n",
       "      <th>01Jun2016_S</th>\n",
       "      <th>02Jun2016_S</th>\n",
       "      <th>03Jun2016_S</th>\n",
       "      <th>04Jun2016_S</th>\n",
       "      <th>05Jun2016_S</th>\n",
       "      <th>06Jun2016_S</th>\n",
       "      <th>07Jun2016_S</th>\n",
       "      <th>08Jun2016_S</th>\n",
       "      <th>09Jun2016_S</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>490</td>\n",
       "      <td>2</td>\n",
       "      <td>183260000</td>\n",
       "      <td>183260000</td>\n",
       "      <td>3.358423e+16</td>\n",
       "      <td>3.358423e+16</td>\n",
       "      <td>19</td>\n",
       "      <td>361</td>\n",
       "      <td>10.000</td>\n",
       "      <td>100.000000</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>1</th>\n",
       "      <td>1193</td>\n",
       "      <td>2</td>\n",
       "      <td>261800000</td>\n",
       "      <td>198970000</td>\n",
       "      <td>6.853924e+16</td>\n",
       "      <td>3.958906e+16</td>\n",
       "      <td>26</td>\n",
       "      <td>676</td>\n",
       "      <td>5.500</td>\n",
       "      <td>30.250000</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>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2943</td>\n",
       "      <td>2</td>\n",
       "      <td>78540000</td>\n",
       "      <td>78540000</td>\n",
       "      <td>6.168532e+15</td>\n",
       "      <td>6.168532e+15</td>\n",
       "      <td>28</td>\n",
       "      <td>784</td>\n",
       "      <td>5.750</td>\n",
       "      <td>33.062500</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>3</th>\n",
       "      <td>3707</td>\n",
       "      <td>1</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>1570800000</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>2.467413e+18</td>\n",
       "      <td>28</td>\n",
       "      <td>784</td>\n",
       "      <td>4.750</td>\n",
       "      <td>22.562500</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>210000</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>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3711</td>\n",
       "      <td>0</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1309000000</td>\n",
       "      <td>1.713481e+18</td>\n",
       "      <td>1.713481e+18</td>\n",
       "      <td>12</td>\n",
       "      <td>144</td>\n",
       "      <td>7.125</td>\n",
       "      <td>50.765625</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>5 rows × 108 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   issuer  Market   amtIssued  amtOutstanding       issued2  outstanding2  \\\n",
       "0     490       2   183260000       183260000  3.358423e+16  3.358423e+16   \n",
       "1    1193       2   261800000       198970000  6.853924e+16  3.958906e+16   \n",
       "2    2943       2    78540000        78540000  6.168532e+15  6.168532e+15   \n",
       "3    3707       1  1570800000      1570800000  2.467413e+18  2.467413e+18   \n",
       "4    3711       0  1309000000      1309000000  1.713481e+18  1.713481e+18   \n",
       "\n",
       "   collateralType  collateral2  coupon     coupon2     ...       31May2016_S  \\\n",
       "0              19          361  10.000  100.000000     ...                 0   \n",
       "1              26          676   5.500   30.250000     ...                 0   \n",
       "2              28          784   5.750   33.062500     ...                 0   \n",
       "3              28          784   4.750   22.562500     ...                 0   \n",
       "4              12          144   7.125   50.765625     ...                 0   \n",
       "\n",
       "   01Jun2016_S  02Jun2016_S  03Jun2016_S  04Jun2016_S  05Jun2016_S  \\\n",
       "0            0            0            0            0            0   \n",
       "1            0            0            0            0            0   \n",
       "2            0            0            0            0            0   \n",
       "3       210000            0            0            0            0   \n",
       "4            0            0            0            0            0   \n",
       "\n",
       "   06Jun2016_S  07Jun2016_S  08Jun2016_S  09Jun2016_S  \n",
       "0            0            0            0            0  \n",
       "1            0            0            0        50000  \n",
       "2            0            0            0            0  \n",
       "3            0            0            0        50000  \n",
       "4            0            0            0            0  \n",
       "\n",
       "[5 rows x 108 columns]"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = df.copy()\n",
    "del train['issue date'], train['ratingAgency1EffectiveDate'], train['ratingAgency2EffectiveDate']\n",
    "del train['couponFrequency'], train['maturity'], train['couponType']\n",
    "train_b = train.ix[:, 1:24].copy()\n",
    "train_s = train.ix[:, 1:24].copy()\n",
    "start_date = date(2016, 3, 16)\n",
    "end_date = date(2016, 6, 9)\n",
    "for single_date in daterange(start_date, end_date):\n",
    "    train_b[single_date.strftime(\"%d%b%Y\")+'_B'] = train[single_date.strftime(\"%d%b%Y\")+'_B']\n",
    "    train_s[single_date.strftime(\"%d%b%Y\")+'_S'] = train[single_date.strftime(\"%d%b%Y\")+'_S']\n",
    "    \n",
    "test_b = train.ix[:, 1:24].copy()\n",
    "test_s = train.ix[:, 1:24].copy()\n",
    "start_date = date(2016, 3, 17)\n",
    "end_date = date(2016, 6, 10)\n",
    "for single_date in daterange(start_date, end_date):\n",
    "    test_b[single_date.strftime(\"%d%b%Y\")+'_B'] = train[single_date.strftime(\"%d%b%Y\")+'_B']\n",
    "    test_s[single_date.strftime(\"%d%b%Y\")+'_S'] = train[single_date.strftime(\"%d%b%Y\")+'_S']\n",
    "    \n",
    "test_s.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RandomForestRegressor(bootstrap=True, criterion='mse', max_depth=None,\n",
       "           max_features='auto', max_leaf_nodes=None, min_samples_leaf=50,\n",
       "           min_samples_split=2, min_weight_fraction_leaf=0.0,\n",
       "           n_estimators=200, n_jobs=1, oob_score=False, random_state=None,\n",
       "           verbose=0, warm_start=False)"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "rfc = RandomForestRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "rfc.fit(train_b, train['09Jun2016_B'])\n",
    "rfc1 = RandomForestRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "rfc1.fit(train_s, train['09Jun2016_S'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "test_buy = rfc.predict(test_b)\n",
    "test_sell = rfc1.predict(test_s)\n",
    "fw = open('out4.csv', 'w')\n",
    "w = csv.writer(fw)\n",
    "w.writerow(('isin', 'buyvolume', 'sellvolume'))\n",
    "fw1 = open('out4_r.csv', 'w')\n",
    "w1 = csv.writer(fw1)\n",
    "w1.writerow(('isin', 'buyvolume', 'sellvolume'))\n",
    "for i in range(df.shape[0]):\n",
    "    w.writerow((df['isin'][i], 3*test_buy[i], 3*test_sell[i]))\n",
    "    w1.writerow((df['isin'][i], 3*test_sell[i], 3*test_buy[i]))\n",
    "fw.close()\n",
    "fw1.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GradientBoostingRegressor(alpha=0.9, init=None, learning_rate=0.1, loss='ls',\n",
       "             max_depth=3, max_features=None, max_leaf_nodes=None,\n",
       "             min_samples_leaf=50, min_samples_split=2,\n",
       "             min_weight_fraction_leaf=0.0, n_estimators=200,\n",
       "             presort='auto', random_state=None, subsample=1.0, verbose=0,\n",
       "             warm_start=False)"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.ensemble import GradientBoostingRegressor\n",
    "gbr = GradientBoostingRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "gbr.fit(train_b, train['09Jun2016_B'])\n",
    "gbr1 = GradientBoostingRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "gbr1.fit(train_s, train['09Jun2016_S'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_buy = gbr.predict(test_b)\n",
    "test_sell = gbr1.predict(test_s)\n",
    "fw = open('out6.csv', 'w')\n",
    "w = csv.writer(fw)\n",
    "w.writerow(('isin', 'buyvolume', 'sellvolume'))\n",
    "fw1 = open('out6_r.csv', 'w')\n",
    "w1 = csv.writer(fw1)\n",
    "w1.writerow(('isin', 'buyvolume', 'sellvolume'))\n",
    "for i in range(df.shape[0]):\n",
    "    w.writerow((df['isin'][i], 3*test_buy[i], 3*test_sell[i]))\n",
    "    w1.writerow((df['isin'][i], 3*test_sell[i], 3*test_buy[i]))\n",
    "fw.close()\n",
    "fw1.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "GradientBoostingRegressor(alpha=0.9, init=None, learning_rate=0.1, loss='ls',\n",
       "             max_depth=3, max_features=None, max_leaf_nodes=None,\n",
       "             min_samples_leaf=50, min_samples_split=2,\n",
       "             min_weight_fraction_leaf=0.0, n_estimators=200,\n",
       "             presort='auto', random_state=None, subsample=1.0, verbose=0,\n",
       "             warm_start=False)"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "rfc = RandomForestRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "rfc.fit(train_b, train['09Jun2016_B'])\n",
    "rfc1 = RandomForestRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "rfc1.fit(train_s, train['09Jun2016_S'])\n",
    "\n",
    "from sklearn.linear_model import LinearRegression\n",
    "lr = LinearRegression()\n",
    "lr.fit(train_b, train['09Jun2016_B'])\n",
    "lr1 = LinearRegression()\n",
    "lr1.fit(train_s, train['09Jun2016_S'])\n",
    "\n",
    "from sklearn.ensemble import GradientBoostingRegressor\n",
    "gbr = GradientBoostingRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "gbr.fit(train_b, train['09Jun2016_B'])\n",
    "gbr1 = GradientBoostingRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "gbr1.fit(train_s, train['09Jun2016_S'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "x = data[data['side']=='B']['volume'].min()\n",
    "y = data[data['side']=='S']['volume'].min()\n",
    "\n",
    "test_buy = lr.predict(test_b)\n",
    "test_sell = lr1.predict(test_s)\n",
    "test_buy1 = rfc.predict(test_b)\n",
    "test_sell1 = rfc1.predict(test_s)\n",
    "for i in range(df.shape[0]):\n",
    "    if test_buy[i]<0:\n",
    "        test_buy[i] = x\n",
    "    if test_sell[i]<0:\n",
    "        test_sell[i] = y\n",
    "    if test_buy1[i]<0:\n",
    "        test_buy1[i] = x\n",
    "    if test_sell1[i]<0:\n",
    "        test_sell1[i] = y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "del train_b['16Mar2016_B'], train_s['16Mar2016_S']\n",
    "train_b['09Jun2016_B'] = train['09Jun2016_B']\n",
    "train_s['09Jun2016_S'] = train['09Jun2016_S']\n",
    "\n",
    "train['10Jun2016_B'] = (2*test_buy+test_buy1)/3.0\n",
    "train['10Jun2016_S'] = (2*test_sell+test_sell1)/3.0\n",
    "del test_b['17Mar2016_B'], test_s['17Mar2016_S']\n",
    "test_b['10Jun2016_B'] = train['10Jun2016_B']\n",
    "test_s['10Jun2016_S'] = train['10Jun2016_S']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LinearRegression(copy_X=True, fit_intercept=True, n_jobs=1, normalize=False)"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "rfc = RandomForestRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "rfc.fit(train_b, train['10Jun2016_B'])\n",
    "rfc1 = RandomForestRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "rfc1.fit(train_s, train['10Jun2016_S'])\n",
    "\n",
    "from sklearn.linear_model import LinearRegression\n",
    "lr = LinearRegression()\n",
    "lr.fit(train_b, train['10Jun2016_B'])\n",
    "lr1 = LinearRegression()\n",
    "lr1.fit(train_s, train['10Jun2016_S'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "x = data[data['side']=='B']['volume'].min()\n",
    "y = data[data['side']=='S']['volume'].min()\n",
    "\n",
    "test_buy = lr.predict(test_b)\n",
    "test_sell = lr1.predict(test_s)\n",
    "test_buy1 = rfc.predict(test_b)\n",
    "test_sell1 = rfc1.predict(test_s)\n",
    "for i in range(df.shape[0]):\n",
    "    if test_buy[i]<0:\n",
    "        test_buy[i] = x\n",
    "    if test_sell[i]<0:\n",
    "        test_sell[i] = y\n",
    "    if test_buy1[i]<0:\n",
    "        test_buy1[i] = x\n",
    "    if test_sell1[i]<0:\n",
    "        test_sell1[i] = y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "del train_b['17Mar2016_B'], train_s['17Mar2016_S']\n",
    "train_b['10Jun2016_B'] = train['10Jun2016_B']\n",
    "train_s['10Jun2016_S'] = train['10Jun2016_S']\n",
    "\n",
    "train['13Jun2016_B'] = (2*test_buy+test_buy1)/3.0\n",
    "train['13Jun2016_S'] = (2*test_sell+test_sell1)/3.0\n",
    "del test_b['18Mar2016_B'], test_s['18Mar2016_S']\n",
    "test_b['13Jun2016_B'] = train['13Jun2016_B']\n",
    "test_s['13Jun2016_S'] = train['13Jun2016_S']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LinearRegression(copy_X=True, fit_intercept=True, n_jobs=1, normalize=False)"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.ensemble import RandomForestRegressor\n",
    "rfc = RandomForestRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "rfc.fit(train_b, train['13Jun2016_B'])\n",
    "rfc1 = RandomForestRegressor(n_estimators=200, min_samples_split=2, min_samples_leaf=50)\n",
    "rfc1.fit(train_s, train['13Jun2016_S'])\n",
    "\n",
    "from sklearn.linear_model import LinearRegression\n",
    "lr = LinearRegression()\n",
    "lr.fit(train_b, train['13Jun2016_B'])\n",
    "lr1 = LinearRegression()\n",
    "lr1.fit(train_s, train['13Jun2016_S'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "x = data[data['side']=='B']['volume'].min()\n",
    "y = data[data['side']=='S']['volume'].min()\n",
    "\n",
    "test_buy = lr.predict(test_b)\n",
    "test_sell = lr1.predict(test_s)\n",
    "test_buy1 = rfc.predict(test_b)\n",
    "test_sell1 = rfc1.predict(test_s)\n",
    "for i in range(df.shape[0]):\n",
    "    if test_buy[i]<0:\n",
    "        test_buy[i] = x\n",
    "    if test_sell[i]<0:\n",
    "        test_sell[i] = y\n",
    "    if test_buy1[i]<0:\n",
    "        test_buy1[i] = x\n",
    "    if test_sell1[i]<0:\n",
    "        test_sell1[i] = y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "train['14Jun2016_B'] = (2*test_buy+test_buy1)/3.0\n",
    "train['14Jun2016_S'] = (2*test_sell+test_sell1)/3.0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "fw = open('out15.csv', 'w')\n",
    "w = csv.writer(fw)\n",
    "w.writerow(('isin', 'buyvolume', 'sellvolume'))\n",
    "fw1 = open('out15_r.csv', 'w')\n",
    "w1 = csv.writer(fw1)\n",
    "w1.writerow(('isin', 'buyvolume', 'sellvolume'))\n",
    "for i in range(df.shape[0]):\n",
    "    w.writerow((df['isin'][i], train['10Jun2016_B'][i]+train['13Jun2016_B'][i]+train['14Jun2016_B'][i], train['10Jun2016_S'][i]+train['13Jun2016_S'][i]+train['14Jun2016_S'][i]))\n",
    "    w1.writerow((df['isin'][i], train['10Jun2016_S'][i]+train['13Jun2016_S'][i]+train['14Jun2016_S'][i], train['10Jun2016_B'][i]+train['13Jun2016_B'][i]+train['14Jun2016_B'][i]))\n",
    "fw.close()\n",
    "fw1.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1000\n",
      "2000\n",
      "3000\n",
      "4000\n",
      "5000\n",
      "6000\n",
      "7000\n",
      "8000\n",
      "9000\n",
      "10000\n",
      "11000\n",
      "12000\n",
      "13000\n",
      "14000\n",
      "15000\n",
      "16000\n",
      "17000\n"
     ]
    }
   ],
   "source": [
    "x = data[data['side']=='B']['volume'].min()\n",
    "y = data[data['side']=='S']['volume'].min()\n",
    "\n",
    "test_buy = lr.predict(test_b)\n",
    "test_sell = lr1.predict(test_s)\n",
    "test_buy1 = rfc.predict(test_b)\n",
    "test_sell1 = rfc1.predict(test_s)\n",
    "test_buy2 = gbr.predict(test_b)\n",
    "test_sell2 = gbr1.predict(test_s)\n",
    "fw = open('out16.csv', 'w')\n",
    "w = csv.writer(fw)\n",
    "w.writerow(('isin', 'buyvolume', 'sellvolume'))\n",
    "fw1 = open('out16_r.csv', 'w')\n",
    "w1 = csv.writer(fw1)\n",
    "w1.writerow(('isin', 'buyvolume', 'sellvolume'))\n",
    "\n",
    "for i in range(df.shape[0]):\n",
    "    if i%1000==0:\n",
    "        print i\n",
    "    if test_buy[i]<0:\n",
    "        test_buy[i] = x\n",
    "    if test_sell[i]<0:\n",
    "        test_sell[i] = y\n",
    "    if test_buy1[i]<0:\n",
    "        test_buy1[i] = x\n",
    "    if test_sell1[i]<0:\n",
    "        test_sell1[i] = y\n",
    "    if test_buy2[i]<0:\n",
    "        test_buy2[i] = x\n",
    "    if test_sell2[i]<0:\n",
    "        test_sell2[i] = y\n",
    "    w.writerow((df['isin'][i], 3*(test_buy[i]+test_buy1[i]+test_buy2[i])/3.0, 3*(test_sell[i]+test_sell1[i]+test_sell2[i])/3.0))\n",
    "    w1.writerow((df['isin'][i], 3*(test_sell[i]+test_sell1[i]+test_sell2[i])/3.0, 3*(test_buy[i]+test_buy1[i]+test_buy2[i])/3.0))\n",
    "fw.close()\n",
    "fw1.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}