random forest.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy\n",
    "import pandas\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>date</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>x21</th>\n",
       "      <th>x1</th>\n",
       "      <th>good fund</th>\n",
       "      <th>irisk</th>\n",
       "      <th>x20</th>\n",
       "      <th>x4</th>\n",
       "      <th>1M Return</th>\n",
       "      <th>2M Return</th>\n",
       "      <th>3M Return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>2013-06-30 00:00:00+00:00</td>\n",
       "      <td>00130H105</td>\n",
       "      <td>-1027487.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.839918</td>\n",
       "      <td>128058539.0</td>\n",
       "      <td>0.798176</td>\n",
       "      <td>-0.034622</td>\n",
       "      <td>-0.046142</td>\n",
       "      <td>0.031842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2013-09-30 00:00:00+00:00</td>\n",
       "      <td>00130H105</td>\n",
       "      <td>-1960657.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.837995</td>\n",
       "      <td>-11942000.0</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>0.045633</td>\n",
       "      <td>0.126254</td>\n",
       "      <td>0.084477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2013-12-31 00:00:00+00:00</td>\n",
       "      <td>00130H105</td>\n",
       "      <td>2062600.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.832036</td>\n",
       "      <td>40780660.0</td>\n",
       "      <td>0.342075</td>\n",
       "      <td>-0.001382</td>\n",
       "      <td>0.088036</td>\n",
       "      <td>0.137687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>2014-03-31 00:00:00+00:00</td>\n",
       "      <td>00130H105</td>\n",
       "      <td>266891.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.835726</td>\n",
       "      <td>1291060.0</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>0.046886</td>\n",
       "      <td>-0.015851</td>\n",
       "      <td>-0.031099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2014-06-30 00:00:00+00:00</td>\n",
       "      <td>00130H105</td>\n",
       "      <td>799495.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.841958</td>\n",
       "      <td>26689280.0</td>\n",
       "      <td>0.684151</td>\n",
       "      <td>0.102837</td>\n",
       "      <td>0.088936</td>\n",
       "      <td>0.126996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11294</th>\n",
       "      <td>11294</td>\n",
       "      <td>2018-12-31 00:00:00+00:00</td>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-29936.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.858119</td>\n",
       "      <td>-6487000.0</td>\n",
       "      <td>0.226586</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11295</th>\n",
       "      <td>11295</td>\n",
       "      <td>2019-03-31 00:00:00+00:00</td>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-21499.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.858006</td>\n",
       "      <td>-936000.0</td>\n",
       "      <td>0.226586</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11296</th>\n",
       "      <td>11296</td>\n",
       "      <td>2019-06-30 00:00:00+00:00</td>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-17523.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.861501</td>\n",
       "      <td>-6299000.0</td>\n",
       "      <td>0.075529</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11297</th>\n",
       "      <td>11297</td>\n",
       "      <td>2019-09-30 00:00:00+00:00</td>\n",
       "      <td>Y09827109</td>\n",
       "      <td>45587.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.853966</td>\n",
       "      <td>11642000.0</td>\n",
       "      <td>0.226586</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11298</th>\n",
       "      <td>11298</td>\n",
       "      <td>2020-03-31 00:00:00+00:00</td>\n",
       "      <td>Y09827109</td>\n",
       "      <td>45587.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.853966</td>\n",
       "      <td>11642000.0</td>\n",
       "      <td>0.226586</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11299 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Unnamed: 0                       date   stock_id        x21    x1  \\\n",
       "0               0  2013-06-30 00:00:00+00:00  00130H105 -1027487.0   9.0   \n",
       "1               1  2013-09-30 00:00:00+00:00  00130H105 -1960657.0   9.0   \n",
       "2               2  2013-12-31 00:00:00+00:00  00130H105  2062600.0  10.0   \n",
       "3               3  2014-03-31 00:00:00+00:00  00130H105   266891.0  10.0   \n",
       "4               4  2014-06-30 00:00:00+00:00  00130H105   799495.0   9.0   \n",
       "...           ...                        ...        ...        ...   ...   \n",
       "11294       11294  2018-12-31 00:00:00+00:00  Y09827109   -29936.0   4.0   \n",
       "11295       11295  2019-03-31 00:00:00+00:00  Y09827109   -21499.0   7.0   \n",
       "11296       11296  2019-06-30 00:00:00+00:00  Y09827109   -17523.0   3.0   \n",
       "11297       11297  2019-09-30 00:00:00+00:00  Y09827109    45587.0   5.0   \n",
       "11298       11298  2020-03-31 00:00:00+00:00  Y09827109    45587.0   5.0   \n",
       "\n",
       "       good fund     irisk          x20        x4  1M Return  2M Return  \\\n",
       "0            2.0  0.839918  128058539.0  0.798176  -0.034622  -0.046142   \n",
       "1            1.0  0.837995  -11942000.0  0.456100   0.045633   0.126254   \n",
       "2            1.0  0.832036   40780660.0  0.342075  -0.001382   0.088036   \n",
       "3            2.0  0.835726    1291060.0  0.456100   0.046886  -0.015851   \n",
       "4            2.0  0.841958   26689280.0  0.684151   0.102837   0.088936   \n",
       "...          ...       ...          ...       ...        ...        ...   \n",
       "11294        1.0  0.858119   -6487000.0  0.226586  -0.092438  -0.122324   \n",
       "11295        2.0  0.858006    -936000.0  0.226586  -0.092438  -0.122324   \n",
       "11296        1.0  0.861501   -6299000.0  0.075529  -0.092438  -0.122324   \n",
       "11297        2.0  0.853966   11642000.0  0.226586  -0.092438  -0.122324   \n",
       "11298        2.0  0.853966   11642000.0  0.226586  -0.092438  -0.122324   \n",
       "\n",
       "       3M Return  \n",
       "0       0.031842  \n",
       "1       0.084477  \n",
       "2       0.137687  \n",
       "3      -0.031099  \n",
       "4       0.126996  \n",
       "...          ...  \n",
       "11294  -0.100983  \n",
       "11295  -0.100983  \n",
       "11296  -0.100983  \n",
       "11297  -0.100983  \n",
       "11298  -0.100983  \n",
       "\n",
       "[11299 rows x 12 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors = pandas.read_csv('Final Merge file.csv')\n",
    "factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stock_id</th>\n",
       "      <th>x21</th>\n",
       "      <th>x1</th>\n",
       "      <th>good fund</th>\n",
       "      <th>irisk</th>\n",
       "      <th>x20</th>\n",
       "      <th>x4</th>\n",
       "      <th>1M Return</th>\n",
       "      <th>2M Return</th>\n",
       "      <th>3M Return</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-06-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>-1027487.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.839918</td>\n",
       "      <td>128058539.0</td>\n",
       "      <td>0.798176</td>\n",
       "      <td>-0.034622</td>\n",
       "      <td>-0.046142</td>\n",
       "      <td>0.031842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-09-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>-1960657.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.837995</td>\n",
       "      <td>-11942000.0</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>0.045633</td>\n",
       "      <td>0.126254</td>\n",
       "      <td>0.084477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-12-31 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>2062600.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.832036</td>\n",
       "      <td>40780660.0</td>\n",
       "      <td>0.342075</td>\n",
       "      <td>-0.001382</td>\n",
       "      <td>0.088036</td>\n",
       "      <td>0.137687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-03-31 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>266891.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.835726</td>\n",
       "      <td>1291060.0</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>0.046886</td>\n",
       "      <td>-0.015851</td>\n",
       "      <td>-0.031099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>799495.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.841958</td>\n",
       "      <td>26689280.0</td>\n",
       "      <td>0.684151</td>\n",
       "      <td>0.102837</td>\n",
       "      <td>0.088936</td>\n",
       "      <td>0.126996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-12-31 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-29936.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.858119</td>\n",
       "      <td>-6487000.0</td>\n",
       "      <td>0.226586</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-03-31 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-21499.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.858006</td>\n",
       "      <td>-936000.0</td>\n",
       "      <td>0.226586</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-30 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-17523.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.861501</td>\n",
       "      <td>-6299000.0</td>\n",
       "      <td>0.075529</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-30 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>45587.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.853966</td>\n",
       "      <td>11642000.0</td>\n",
       "      <td>0.226586</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-03-31 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>45587.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.853966</td>\n",
       "      <td>11642000.0</td>\n",
       "      <td>0.226586</td>\n",
       "      <td>-0.092438</td>\n",
       "      <td>-0.122324</td>\n",
       "      <td>-0.100983</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11299 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id        x21    x1  good fund     irisk  \\\n",
       "date                                                                         \n",
       "2013-06-30 00:00:00+00:00  00130H105 -1027487.0   9.0        2.0  0.839918   \n",
       "2013-09-30 00:00:00+00:00  00130H105 -1960657.0   9.0        1.0  0.837995   \n",
       "2013-12-31 00:00:00+00:00  00130H105  2062600.0  10.0        1.0  0.832036   \n",
       "2014-03-31 00:00:00+00:00  00130H105   266891.0  10.0        2.0  0.835726   \n",
       "2014-06-30 00:00:00+00:00  00130H105   799495.0   9.0        2.0  0.841958   \n",
       "...                              ...        ...   ...        ...       ...   \n",
       "2018-12-31 00:00:00+00:00  Y09827109   -29936.0   4.0        1.0  0.858119   \n",
       "2019-03-31 00:00:00+00:00  Y09827109   -21499.0   7.0        2.0  0.858006   \n",
       "2019-06-30 00:00:00+00:00  Y09827109   -17523.0   3.0        1.0  0.861501   \n",
       "2019-09-30 00:00:00+00:00  Y09827109    45587.0   5.0        2.0  0.853966   \n",
       "2020-03-31 00:00:00+00:00  Y09827109    45587.0   5.0        2.0  0.853966   \n",
       "\n",
       "                                   x20        x4  1M Return  2M Return  \\\n",
       "date                                                                     \n",
       "2013-06-30 00:00:00+00:00  128058539.0  0.798176  -0.034622  -0.046142   \n",
       "2013-09-30 00:00:00+00:00  -11942000.0  0.456100   0.045633   0.126254   \n",
       "2013-12-31 00:00:00+00:00   40780660.0  0.342075  -0.001382   0.088036   \n",
       "2014-03-31 00:00:00+00:00    1291060.0  0.456100   0.046886  -0.015851   \n",
       "2014-06-30 00:00:00+00:00   26689280.0  0.684151   0.102837   0.088936   \n",
       "...                                ...       ...        ...        ...   \n",
       "2018-12-31 00:00:00+00:00   -6487000.0  0.226586  -0.092438  -0.122324   \n",
       "2019-03-31 00:00:00+00:00    -936000.0  0.226586  -0.092438  -0.122324   \n",
       "2019-06-30 00:00:00+00:00   -6299000.0  0.075529  -0.092438  -0.122324   \n",
       "2019-09-30 00:00:00+00:00   11642000.0  0.226586  -0.092438  -0.122324   \n",
       "2020-03-31 00:00:00+00:00   11642000.0  0.226586  -0.092438  -0.122324   \n",
       "\n",
       "                           3M Return  \n",
       "date                                  \n",
       "2013-06-30 00:00:00+00:00   0.031842  \n",
       "2013-09-30 00:00:00+00:00   0.084477  \n",
       "2013-12-31 00:00:00+00:00   0.137687  \n",
       "2014-03-31 00:00:00+00:00  -0.031099  \n",
       "2014-06-30 00:00:00+00:00   0.126996  \n",
       "...                              ...  \n",
       "2018-12-31 00:00:00+00:00  -0.100983  \n",
       "2019-03-31 00:00:00+00:00  -0.100983  \n",
       "2019-06-30 00:00:00+00:00  -0.100983  \n",
       "2019-09-30 00:00:00+00:00  -0.100983  \n",
       "2020-03-31 00:00:00+00:00  -0.100983  \n",
       "\n",
       "[11299 rows x 10 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors['date'] = pandas.to_datetime(factors['date'])\n",
    "factors = factors.set_index('date')\n",
    "factors = factors[['stock_id','x21','x1','good fund','irisk','x20','x4','1M Return','2M Return','3M Return']]\n",
    "factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>3M return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-12-23 00:00:00+00:00</td>\n",
       "      <td>001038108</td>\n",
       "      <td>0.023255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-04-15 00:00:00+00:00</td>\n",
       "      <td>87243T100</td>\n",
       "      <td>-0.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-10-17 00:00:00+00:00</td>\n",
       "      <td>925490203</td>\n",
       "      <td>0.068493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-02-14 00:00:00+00:00</td>\n",
       "      <td>83607W102</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-07-25 00:00:00+00:00</td>\n",
       "      <td>320734106</td>\n",
       "      <td>-0.118870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001331</th>\n",
       "      <td>2020-05-27 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.339271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001332</th>\n",
       "      <td>2020-05-28 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.386451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001333</th>\n",
       "      <td>2020-05-29 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.330361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001334</th>\n",
       "      <td>2020-05-29 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.352361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001335</th>\n",
       "      <td>2020-05-29 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.205942</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20001336 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               date   stock_id  3M return\n",
       "0         2013-12-23 00:00:00+00:00  001038108   0.023255\n",
       "1         2015-04-15 00:00:00+00:00  87243T100  -0.272727\n",
       "2         2013-10-17 00:00:00+00:00  925490203   0.068493\n",
       "3         2014-02-14 00:00:00+00:00  83607W102   0.000000\n",
       "4         2014-07-25 00:00:00+00:00  320734106  -0.118870\n",
       "...                             ...        ...        ...\n",
       "20001331  2020-05-27 00:00:00+00:00  98979F107  -0.339271\n",
       "20001332  2020-05-28 00:00:00+00:00  98979F107  -0.386451\n",
       "20001333  2020-05-29 00:00:00+00:00  98979F107  -0.330361\n",
       "20001334  2020-05-29 00:00:00+00:00  98979F107  -0.352361\n",
       "20001335  2020-05-29 00:00:00+00:00  98979F107  -0.205942\n",
       "\n",
       "[20001336 rows x 3 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_val = pandas.read_csv('3M return.csv')\n",
    "pred_val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>pred</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2013-12-23 00:00:00+00:00</td>\n",
       "      <td>001038108</td>\n",
       "      <td>0.023255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015-04-15 00:00:00+00:00</td>\n",
       "      <td>87243T100</td>\n",
       "      <td>-0.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013-10-17 00:00:00+00:00</td>\n",
       "      <td>925490203</td>\n",
       "      <td>0.068493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014-02-14 00:00:00+00:00</td>\n",
       "      <td>83607W102</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2014-07-25 00:00:00+00:00</td>\n",
       "      <td>320734106</td>\n",
       "      <td>-0.118870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001331</th>\n",
       "      <td>2020-05-27 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.339271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001332</th>\n",
       "      <td>2020-05-28 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.386451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001333</th>\n",
       "      <td>2020-05-29 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.330361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001334</th>\n",
       "      <td>2020-05-29 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.352361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20001335</th>\n",
       "      <td>2020-05-29 00:00:00+00:00</td>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.205942</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20001336 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                               date   stock_id      pred\n",
       "0         2013-12-23 00:00:00+00:00  001038108  0.023255\n",
       "1         2015-04-15 00:00:00+00:00  87243T100 -0.272727\n",
       "2         2013-10-17 00:00:00+00:00  925490203  0.068493\n",
       "3         2014-02-14 00:00:00+00:00  83607W102  0.000000\n",
       "4         2014-07-25 00:00:00+00:00  320734106 -0.118870\n",
       "...                             ...        ...       ...\n",
       "20001331  2020-05-27 00:00:00+00:00  98979F107 -0.339271\n",
       "20001332  2020-05-28 00:00:00+00:00  98979F107 -0.386451\n",
       "20001333  2020-05-29 00:00:00+00:00  98979F107 -0.330361\n",
       "20001334  2020-05-29 00:00:00+00:00  98979F107 -0.352361\n",
       "20001335  2020-05-29 00:00:00+00:00  98979F107 -0.205942\n",
       "\n",
       "[20001336 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_val.columns = ['date','stock_id','pred']\n",
    "pred_val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "pred_val['date'] = pandas.to_datetime(pred_val['date'])\n",
    "pred_val= pred_val.set_index('date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stock_id</th>\n",
       "      <th>pred</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-12-23 00:00:00+00:00</th>\n",
       "      <td>001038108</td>\n",
       "      <td>0.023255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-04-15 00:00:00+00:00</th>\n",
       "      <td>87243T100</td>\n",
       "      <td>-0.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-10-17 00:00:00+00:00</th>\n",
       "      <td>925490203</td>\n",
       "      <td>0.068493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-02-14 00:00:00+00:00</th>\n",
       "      <td>83607W102</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-07-25 00:00:00+00:00</th>\n",
       "      <td>320734106</td>\n",
       "      <td>-0.118870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-27 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.339271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-28 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.386451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.330361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.352361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.205942</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20001336 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id      pred\n",
       "date                                          \n",
       "2013-12-23 00:00:00+00:00  001038108  0.023255\n",
       "2015-04-15 00:00:00+00:00  87243T100 -0.272727\n",
       "2013-10-17 00:00:00+00:00  925490203  0.068493\n",
       "2014-02-14 00:00:00+00:00  83607W102  0.000000\n",
       "2014-07-25 00:00:00+00:00  320734106 -0.118870\n",
       "...                              ...       ...\n",
       "2020-05-27 00:00:00+00:00  98979F107 -0.339271\n",
       "2020-05-28 00:00:00+00:00  98979F107 -0.386451\n",
       "2020-05-29 00:00:00+00:00  98979F107 -0.330361\n",
       "2020-05-29 00:00:00+00:00  98979F107 -0.352361\n",
       "2020-05-29 00:00:00+00:00  98979F107 -0.205942\n",
       "\n",
       "[20001336 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stock_id</th>\n",
       "      <th>pred</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-09-23 00:00:00+00:00</th>\n",
       "      <td>001038108</td>\n",
       "      <td>0.023255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015-01-15 00:00:00+00:00</th>\n",
       "      <td>87243T100</td>\n",
       "      <td>-0.272727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-07-17 00:00:00+00:00</th>\n",
       "      <td>925490203</td>\n",
       "      <td>0.068493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-11-14 00:00:00+00:00</th>\n",
       "      <td>83607W102</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-04-25 00:00:00+00:00</th>\n",
       "      <td>320734106</td>\n",
       "      <td>-0.118870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-27 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.339271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-28 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.386451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.330361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.352361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>-0.205942</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20001336 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id      pred\n",
       "date                                          \n",
       "2013-09-23 00:00:00+00:00  001038108  0.023255\n",
       "2015-01-15 00:00:00+00:00  87243T100 -0.272727\n",
       "2013-07-17 00:00:00+00:00  925490203  0.068493\n",
       "2013-11-14 00:00:00+00:00  83607W102  0.000000\n",
       "2014-04-25 00:00:00+00:00  320734106 -0.118870\n",
       "...                              ...       ...\n",
       "2020-02-27 00:00:00+00:00  98979F107 -0.339271\n",
       "2020-02-28 00:00:00+00:00  98979F107 -0.386451\n",
       "2020-02-29 00:00:00+00:00  98979F107 -0.330361\n",
       "2020-02-29 00:00:00+00:00  98979F107 -0.352361\n",
       "2020-02-29 00:00:00+00:00  98979F107 -0.205942\n",
       "\n",
       "[20001336 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_val.index = pred_val.index - pandas.DateOffset(months = 3)\n",
    "pred_val"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>stock_id</th>\n",
       "      <th>34958R19</th>\n",
       "      <th>46629U18</th>\n",
       "      <th>92549022</th>\n",
       "      <th>000144899</th>\n",
       "      <th>000279208</th>\n",
       "      <th>000303107</th>\n",
       "      <th>000304105</th>\n",
       "      <th>000307108</th>\n",
       "      <th>000360206</th>\n",
       "      <th>000361105</th>\n",
       "      <th>...</th>\n",
       "      <th>Y9013V159</th>\n",
       "      <th>Y9361F111</th>\n",
       "      <th>Y9381W102</th>\n",
       "      <th>Y9384M101</th>\n",
       "      <th>Y95308105</th>\n",
       "      <th>Y9532S109</th>\n",
       "      <th>Y9723Q102</th>\n",
       "      <th>Y9891F102</th>\n",
       "      <th>Y989DF109</th>\n",
       "      <th>Y9T10P105</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-02-01 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-02 00:00:00+00:00</th>\n",
       "      <td>-0.158420</td>\n",
       "      <td>-0.361702</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.348392</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.291957</td>\n",
       "      <td>-0.080537</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.040562</td>\n",
       "      <td>0.031674</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.506849</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.051613</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-03 00:00:00+00:00</th>\n",
       "      <td>-0.155415</td>\n",
       "      <td>-0.315217</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.380282</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.316541</td>\n",
       "      <td>-0.063068</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.040562</td>\n",
       "      <td>0.031674</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.506849</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.051613</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-07 00:00:00+00:00</th>\n",
       "      <td>-0.203301</td>\n",
       "      <td>-0.263954</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.352654</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.328222</td>\n",
       "      <td>-0.049620</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.040562</td>\n",
       "      <td>0.031674</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.479452</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.051613</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-08 00:00:00+00:00</th>\n",
       "      <td>-0.325213</td>\n",
       "      <td>-0.252045</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.451212</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.322355</td>\n",
       "      <td>-0.040286</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.040562</td>\n",
       "      <td>0.031674</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.479452</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.051613</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-21 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>-0.331665</td>\n",
       "      <td>-0.509002</td>\n",
       "      <td>-0.020057</td>\n",
       "      <td>-0.602394</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.407731</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.191442</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-22 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>-0.379141</td>\n",
       "      <td>-0.500000</td>\n",
       "      <td>-0.023607</td>\n",
       "      <td>-0.603978</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.425997</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.191442</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-27 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>-0.309479</td>\n",
       "      <td>-0.085714</td>\n",
       "      <td>0.029588</td>\n",
       "      <td>-0.521162</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.393574</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.268746</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-28 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>-0.335979</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.009871</td>\n",
       "      <td>-0.534456</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.361646</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.268746</td>\n",
       "      <td>0.542222</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-2.97</td>\n",
       "      <td>-0.868366</td>\n",
       "      <td>-0.214916</td>\n",
       "      <td>0.041409</td>\n",
       "      <td>-1.603185</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.248289</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.616277</td>\n",
       "      <td>1.626667</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1102 rows × 28841 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "stock_id                    34958R19   46629U18   92549022  000144899  \\\n",
       "date                                                                    \n",
       "2013-02-01 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2013-02-02 00:00:00+00:00  -0.158420  -0.361702        NaN        NaN   \n",
       "2013-02-03 00:00:00+00:00  -0.155415  -0.315217        NaN        NaN   \n",
       "2013-02-07 00:00:00+00:00  -0.203301  -0.263954        NaN        NaN   \n",
       "2013-02-08 00:00:00+00:00  -0.325213  -0.252045        NaN        NaN   \n",
       "...                              ...        ...        ...        ...   \n",
       "2020-02-21 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2020-02-22 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2020-02-27 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2020-02-28 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2020-02-29 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "\n",
       "stock_id                   000279208  000303107  000304105  000307108  \\\n",
       "date                                                                    \n",
       "2013-02-01 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2013-02-02 00:00:00+00:00        0.0        NaN   0.348392        NaN   \n",
       "2013-02-03 00:00:00+00:00        0.0        NaN   0.380282        NaN   \n",
       "2013-02-07 00:00:00+00:00        0.0        NaN   0.352654        NaN   \n",
       "2013-02-08 00:00:00+00:00        0.0        NaN   0.451212        NaN   \n",
       "...                              ...        ...        ...        ...   \n",
       "2020-02-21 00:00:00+00:00        NaN      -0.99  -0.331665  -0.509002   \n",
       "2020-02-22 00:00:00+00:00        NaN      -0.99  -0.379141  -0.500000   \n",
       "2020-02-27 00:00:00+00:00        NaN      -0.99  -0.309479  -0.085714   \n",
       "2020-02-28 00:00:00+00:00        NaN      -0.99  -0.335979   0.000000   \n",
       "2020-02-29 00:00:00+00:00        NaN      -2.97  -0.868366  -0.214916   \n",
       "\n",
       "stock_id                   000360206  000361105  ...  Y9013V159  Y9361F111  \\\n",
       "date                                             ...                         \n",
       "2013-02-01 00:00:00+00:00        NaN        NaN  ...        NaN        NaN   \n",
       "2013-02-02 00:00:00+00:00   0.291957  -0.080537  ...        0.0   0.040562   \n",
       "2013-02-03 00:00:00+00:00   0.316541  -0.063068  ...        0.0   0.040562   \n",
       "2013-02-07 00:00:00+00:00   0.328222  -0.049620  ...        0.0   0.040562   \n",
       "2013-02-08 00:00:00+00:00   0.322355  -0.040286  ...        0.0   0.040562   \n",
       "...                              ...        ...  ...        ...        ...   \n",
       "2020-02-21 00:00:00+00:00  -0.020057  -0.602394  ...        NaN        NaN   \n",
       "2020-02-22 00:00:00+00:00  -0.023607  -0.603978  ...        NaN        NaN   \n",
       "2020-02-27 00:00:00+00:00   0.029588  -0.521162  ...        NaN        NaN   \n",
       "2020-02-28 00:00:00+00:00   0.009871  -0.534456  ...        NaN        NaN   \n",
       "2020-02-29 00:00:00+00:00   0.041409  -1.603185  ...        NaN        NaN   \n",
       "\n",
       "stock_id                   Y9381W102  Y9384M101  Y95308105  Y9532S109  \\\n",
       "date                                                                    \n",
       "2013-02-01 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2013-02-02 00:00:00+00:00   0.031674        NaN        NaN   0.506849   \n",
       "2013-02-03 00:00:00+00:00   0.031674        NaN        NaN   0.506849   \n",
       "2013-02-07 00:00:00+00:00   0.031674        NaN        NaN   0.479452   \n",
       "2013-02-08 00:00:00+00:00   0.031674        NaN        NaN   0.479452   \n",
       "...                              ...        ...        ...        ...   \n",
       "2020-02-21 00:00:00+00:00        NaN        NaN   0.407731        NaN   \n",
       "2020-02-22 00:00:00+00:00        NaN        NaN   0.425997        NaN   \n",
       "2020-02-27 00:00:00+00:00        NaN        NaN   0.393574        NaN   \n",
       "2020-02-28 00:00:00+00:00        NaN        NaN   0.361646        NaN   \n",
       "2020-02-29 00:00:00+00:00        NaN        NaN   1.248289        NaN   \n",
       "\n",
       "stock_id                   Y9723Q102  Y9891F102  Y989DF109  Y9T10P105  \n",
       "date                                                                   \n",
       "2013-02-01 00:00:00+00:00        NaN        NaN        NaN        NaN  \n",
       "2013-02-02 00:00:00+00:00        0.0   0.051613        NaN        NaN  \n",
       "2013-02-03 00:00:00+00:00        0.0   0.051613        NaN        NaN  \n",
       "2013-02-07 00:00:00+00:00        0.0   0.051613        NaN        NaN  \n",
       "2013-02-08 00:00:00+00:00        0.0   0.051613        NaN        NaN  \n",
       "...                              ...        ...        ...        ...  \n",
       "2020-02-21 00:00:00+00:00        NaN  -0.191442   0.000000        NaN  \n",
       "2020-02-22 00:00:00+00:00        NaN  -0.191442   0.000000        NaN  \n",
       "2020-02-27 00:00:00+00:00        NaN  -0.268746   0.000000        NaN  \n",
       "2020-02-28 00:00:00+00:00        NaN  -0.268746   0.542222        NaN  \n",
       "2020-02-29 00:00:00+00:00        NaN  -0.616277   1.626667        NaN  \n",
       "\n",
       "[1102 rows x 28841 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_table = pandas.pivot_table(pred_val, values = 'pred', index = ['date'],columns = 'stock_id',aggfunc = numpy.sum)\n",
    "pred_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date                       stock_id \n",
       "2013-02-01 00:00:00+00:00  18383Q747    0.412476\n",
       "                           25459W151    4.360073\n",
       "                           25459W318   -0.990158\n",
       "                           268461852   -0.196000\n",
       "                           303046106   -0.125000\n",
       "                                          ...   \n",
       "2020-02-29 00:00:00+00:00  Y88965101    3.355618\n",
       "                           Y8897Y164   -2.439909\n",
       "                           Y95308105    1.248289\n",
       "                           Y9891F102   -0.616277\n",
       "                           Y989DF109    1.626667\n",
       "Length: 45480210, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_factor = pred_table.asfreq(freq = '1d', method = 'ffill').stack()\n",
    "pred_factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stock_id</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-02-01 00:00:00+00:00</th>\n",
       "      <td>18383Q747</td>\n",
       "      <td>0.412476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-01 00:00:00+00:00</th>\n",
       "      <td>25459W151</td>\n",
       "      <td>4.360073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-01 00:00:00+00:00</th>\n",
       "      <td>25459W318</td>\n",
       "      <td>-0.990158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-01 00:00:00+00:00</th>\n",
       "      <td>268461852</td>\n",
       "      <td>-0.196000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-02-01 00:00:00+00:00</th>\n",
       "      <td>303046106</td>\n",
       "      <td>-0.125000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>Y88965101</td>\n",
       "      <td>3.355618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>Y8897Y164</td>\n",
       "      <td>-2.439909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>Y95308105</td>\n",
       "      <td>1.248289</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>Y9891F102</td>\n",
       "      <td>-0.616277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>Y989DF109</td>\n",
       "      <td>1.626667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>45480210 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id         0\n",
       "date                                          \n",
       "2013-02-01 00:00:00+00:00  18383Q747  0.412476\n",
       "2013-02-01 00:00:00+00:00  25459W151  4.360073\n",
       "2013-02-01 00:00:00+00:00  25459W318 -0.990158\n",
       "2013-02-01 00:00:00+00:00  268461852 -0.196000\n",
       "2013-02-01 00:00:00+00:00  303046106 -0.125000\n",
       "...                              ...       ...\n",
       "2020-02-29 00:00:00+00:00  Y88965101  3.355618\n",
       "2020-02-29 00:00:00+00:00  Y8897Y164 -2.439909\n",
       "2020-02-29 00:00:00+00:00  Y95308105  1.248289\n",
       "2020-02-29 00:00:00+00:00  Y9891F102 -0.616277\n",
       "2020-02-29 00:00:00+00:00  Y989DF109  1.626667\n",
       "\n",
       "[45480210 rows x 2 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pred_factor = pandas.DataFrame(pred_factor)\n",
    "pred_factor = pred_factor.reset_index('stock_id')\n",
    "pred_factor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stock_id</th>\n",
       "      <th>x21</th>\n",
       "      <th>x1</th>\n",
       "      <th>good fund</th>\n",
       "      <th>irisk</th>\n",
       "      <th>x20</th>\n",
       "      <th>x4</th>\n",
       "      <th>1M Return</th>\n",
       "      <th>2M Return</th>\n",
       "      <th>3M Return</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-06-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>-1027487.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.839918</td>\n",
       "      <td>1.280585e+08</td>\n",
       "      <td>0.798176</td>\n",
       "      <td>-0.034622</td>\n",
       "      <td>-0.046142</td>\n",
       "      <td>0.031842</td>\n",
       "      <td>0.168954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-09-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>-1960657.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.837995</td>\n",
       "      <td>-1.194200e+07</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>0.045633</td>\n",
       "      <td>0.126254</td>\n",
       "      <td>0.084477</td>\n",
       "      <td>0.137687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-03-31 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>266891.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.835726</td>\n",
       "      <td>1.291060e+06</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>0.046886</td>\n",
       "      <td>-0.015851</td>\n",
       "      <td>-0.031099</td>\n",
       "      <td>0.126996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>799495.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.841958</td>\n",
       "      <td>2.668928e+07</td>\n",
       "      <td>0.684151</td>\n",
       "      <td>0.102837</td>\n",
       "      <td>0.088936</td>\n",
       "      <td>0.126996</td>\n",
       "      <td>0.005674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>1061556.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.838406</td>\n",
       "      <td>-1.421000e+06</td>\n",
       "      <td>0.570125</td>\n",
       "      <td>-0.063900</td>\n",
       "      <td>-0.088103</td>\n",
       "      <td>0.005674</td>\n",
       "      <td>-0.053360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>1427864.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0.858612</td>\n",
       "      <td>3.098040e+08</td>\n",
       "      <td>2.265861</td>\n",
       "      <td>0.036835</td>\n",
       "      <td>0.024635</td>\n",
       "      <td>0.000342</td>\n",
       "      <td>0.290633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-31 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>1497676.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.858648</td>\n",
       "      <td>9.532270e+08</td>\n",
       "      <td>2.341390</td>\n",
       "      <td>0.047504</td>\n",
       "      <td>0.244781</td>\n",
       "      <td>0.290633</td>\n",
       "      <td>0.142796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-06-30 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-931031.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.860360</td>\n",
       "      <td>1.312300e+07</td>\n",
       "      <td>2.190332</td>\n",
       "      <td>-0.027540</td>\n",
       "      <td>0.061738</td>\n",
       "      <td>0.142796</td>\n",
       "      <td>-0.016334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-09-30 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>4266231.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0.858136</td>\n",
       "      <td>1.180789e+09</td>\n",
       "      <td>2.870091</td>\n",
       "      <td>0.003351</td>\n",
       "      <td>0.036319</td>\n",
       "      <td>-0.016334</td>\n",
       "      <td>0.062756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-1875008.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.859313</td>\n",
       "      <td>-1.993160e+08</td>\n",
       "      <td>3.625378</td>\n",
       "      <td>-0.053775</td>\n",
       "      <td>0.059207</td>\n",
       "      <td>0.062756</td>\n",
       "      <td>-0.132044</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9866 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id        x21    x1  good fund     irisk  \\\n",
       "date                                                                         \n",
       "2013-06-30 00:00:00+00:00  00130H105 -1027487.0   9.0        2.0  0.839918   \n",
       "2013-09-30 00:00:00+00:00  00130H105 -1960657.0   9.0        1.0  0.837995   \n",
       "2014-03-31 00:00:00+00:00  00130H105   266891.0  10.0        2.0  0.835726   \n",
       "2014-06-30 00:00:00+00:00  00130H105   799495.0   9.0        2.0  0.841958   \n",
       "2014-09-30 00:00:00+00:00  00130H105  1061556.0   9.0        2.0  0.838406   \n",
       "...                              ...        ...   ...        ...       ...   \n",
       "2016-12-31 00:00:00+00:00  Y09827109  1427864.0  61.0       27.0  0.858612   \n",
       "2017-03-31 00:00:00+00:00  Y09827109  1497676.0  59.0       28.0  0.858648   \n",
       "2017-06-30 00:00:00+00:00  Y09827109  -931031.0  60.0       31.0  0.860360   \n",
       "2017-09-30 00:00:00+00:00  Y09827109  4266231.0  77.0       35.0  0.858136   \n",
       "2017-12-31 00:00:00+00:00  Y09827109 -1875008.0  99.0       40.0  0.859313   \n",
       "\n",
       "                                    x20        x4  1M Return  2M Return  \\\n",
       "date                                                                      \n",
       "2013-06-30 00:00:00+00:00  1.280585e+08  0.798176  -0.034622  -0.046142   \n",
       "2013-09-30 00:00:00+00:00 -1.194200e+07  0.456100   0.045633   0.126254   \n",
       "2014-03-31 00:00:00+00:00  1.291060e+06  0.456100   0.046886  -0.015851   \n",
       "2014-06-30 00:00:00+00:00  2.668928e+07  0.684151   0.102837   0.088936   \n",
       "2014-09-30 00:00:00+00:00 -1.421000e+06  0.570125  -0.063900  -0.088103   \n",
       "...                                 ...       ...        ...        ...   \n",
       "2016-12-31 00:00:00+00:00  3.098040e+08  2.265861   0.036835   0.024635   \n",
       "2017-03-31 00:00:00+00:00  9.532270e+08  2.341390   0.047504   0.244781   \n",
       "2017-06-30 00:00:00+00:00  1.312300e+07  2.190332  -0.027540   0.061738   \n",
       "2017-09-30 00:00:00+00:00  1.180789e+09  2.870091   0.003351   0.036319   \n",
       "2017-12-31 00:00:00+00:00 -1.993160e+08  3.625378  -0.053775   0.059207   \n",
       "\n",
       "                           3M Return         0  \n",
       "date                                            \n",
       "2013-06-30 00:00:00+00:00   0.031842  0.168954  \n",
       "2013-09-30 00:00:00+00:00   0.084477  0.137687  \n",
       "2014-03-31 00:00:00+00:00  -0.031099  0.126996  \n",
       "2014-06-30 00:00:00+00:00   0.126996  0.005674  \n",
       "2014-09-30 00:00:00+00:00   0.005674 -0.053360  \n",
       "...                              ...       ...  \n",
       "2016-12-31 00:00:00+00:00   0.000342  0.290633  \n",
       "2017-03-31 00:00:00+00:00   0.290633  0.142796  \n",
       "2017-06-30 00:00:00+00:00   0.142796 -0.016334  \n",
       "2017-09-30 00:00:00+00:00  -0.016334  0.062756  \n",
       "2017-12-31 00:00:00+00:00   0.062756 -0.132044  \n",
       "\n",
       "[9866 rows x 11 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors = pandas.merge(factors, pred_factor, 'right',on = ['date','stock_id'])\n",
    "factors = factors.dropna()\n",
    "factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DatetimeIndex(['2013-06-30 00:00:00+00:00', '2013-09-30 00:00:00+00:00',\n",
       "               '2014-03-31 00:00:00+00:00', '2014-06-30 00:00:00+00:00',\n",
       "               '2014-09-30 00:00:00+00:00', '2015-03-31 00:00:00+00:00',\n",
       "               '2015-06-30 00:00:00+00:00', '2015-09-30 00:00:00+00:00',\n",
       "               '2015-12-31 00:00:00+00:00', '2016-03-31 00:00:00+00:00',\n",
       "               '2016-06-30 00:00:00+00:00', '2016-09-30 00:00:00+00:00',\n",
       "               '2016-12-31 00:00:00+00:00', '2017-03-31 00:00:00+00:00',\n",
       "               '2017-06-30 00:00:00+00:00', '2017-09-30 00:00:00+00:00',\n",
       "               '2017-12-31 00:00:00+00:00', '2018-03-31 00:00:00+00:00',\n",
       "               '2018-06-30 00:00:00+00:00', '2018-09-30 00:00:00+00:00',\n",
       "               '2018-12-31 00:00:00+00:00', '2019-03-31 00:00:00+00:00',\n",
       "               '2019-06-30 00:00:00+00:00', '2019-09-30 00:00:00+00:00'],\n",
       "              dtype='datetime64[ns, UTC]', name='date', freq=None)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors.index.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "factors.columns = ['stock_id','x21','x1','good fund','irisk','x20','x4','1M Return','2M Return','3M Return','return']\n",
    "train = factors[:'2017,10,01']\n",
    "test = factors['2017,10,01':]\n",
    "feat = ['x21','x1','good fund','irisk','x20','x4','1M Return','2M Return','3M Return']\n",
    "train_feature = train[feat]\n",
    "train_return = train['return']\n",
    "test_feature = test[feat]\n",
    "test_return = test['return']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>x21</th>\n",
       "      <th>x1</th>\n",
       "      <th>good fund</th>\n",
       "      <th>irisk</th>\n",
       "      <th>x20</th>\n",
       "      <th>x4</th>\n",
       "      <th>1M Return</th>\n",
       "      <th>2M Return</th>\n",
       "      <th>3M Return</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2013-06-30 00:00:00+00:00</th>\n",
       "      <td>-1027487.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.839918</td>\n",
       "      <td>1.280585e+08</td>\n",
       "      <td>0.798176</td>\n",
       "      <td>-0.034622</td>\n",
       "      <td>-0.046142</td>\n",
       "      <td>0.031842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013-09-30 00:00:00+00:00</th>\n",
       "      <td>-1960657.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.837995</td>\n",
       "      <td>-1.194200e+07</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>0.045633</td>\n",
       "      <td>0.126254</td>\n",
       "      <td>0.084477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-03-31 00:00:00+00:00</th>\n",
       "      <td>266891.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.835726</td>\n",
       "      <td>1.291060e+06</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>0.046886</td>\n",
       "      <td>-0.015851</td>\n",
       "      <td>-0.031099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-06-30 00:00:00+00:00</th>\n",
       "      <td>799495.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.841958</td>\n",
       "      <td>2.668928e+07</td>\n",
       "      <td>0.684151</td>\n",
       "      <td>0.102837</td>\n",
       "      <td>0.088936</td>\n",
       "      <td>0.126996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014-09-30 00:00:00+00:00</th>\n",
       "      <td>1061556.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.838406</td>\n",
       "      <td>-1.421000e+06</td>\n",
       "      <td>0.570125</td>\n",
       "      <td>-0.063900</td>\n",
       "      <td>-0.088103</td>\n",
       "      <td>0.005674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31 00:00:00+00:00</th>\n",
       "      <td>-275203.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.853315</td>\n",
       "      <td>1.248487e+07</td>\n",
       "      <td>0.548697</td>\n",
       "      <td>0.013215</td>\n",
       "      <td>0.094596</td>\n",
       "      <td>0.161766</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-03-31 00:00:00+00:00</th>\n",
       "      <td>17462287.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.864949</td>\n",
       "      <td>2.701012e+09</td>\n",
       "      <td>1.963746</td>\n",
       "      <td>0.150481</td>\n",
       "      <td>0.064416</td>\n",
       "      <td>0.202990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-06-30 00:00:00+00:00</th>\n",
       "      <td>-314233.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.859041</td>\n",
       "      <td>-3.262900e+07</td>\n",
       "      <td>2.265861</td>\n",
       "      <td>0.006737</td>\n",
       "      <td>-0.001962</td>\n",
       "      <td>0.156975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-09-30 00:00:00+00:00</th>\n",
       "      <td>-4078255.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.857151</td>\n",
       "      <td>-4.044620e+08</td>\n",
       "      <td>2.416918</td>\n",
       "      <td>-0.023709</td>\n",
       "      <td>0.110167</td>\n",
       "      <td>0.117647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016-12-31 00:00:00+00:00</th>\n",
       "      <td>1427864.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0.858612</td>\n",
       "      <td>3.098040e+08</td>\n",
       "      <td>2.265861</td>\n",
       "      <td>0.036835</td>\n",
       "      <td>0.024635</td>\n",
       "      <td>0.000342</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5198 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  x21    x1  good fund     irisk  \\\n",
       "date                                                               \n",
       "2013-06-30 00:00:00+00:00  -1027487.0   9.0        2.0  0.839918   \n",
       "2013-09-30 00:00:00+00:00  -1960657.0   9.0        1.0  0.837995   \n",
       "2014-03-31 00:00:00+00:00    266891.0  10.0        2.0  0.835726   \n",
       "2014-06-30 00:00:00+00:00    799495.0   9.0        2.0  0.841958   \n",
       "2014-09-30 00:00:00+00:00   1061556.0   9.0        2.0  0.838406   \n",
       "...                               ...   ...        ...       ...   \n",
       "2016-12-31 00:00:00+00:00   -275203.0  22.0        7.0  0.853315   \n",
       "2016-03-31 00:00:00+00:00  17462287.0  51.0       18.0  0.864949   \n",
       "2016-06-30 00:00:00+00:00   -314233.0  55.0       22.0  0.859041   \n",
       "2016-09-30 00:00:00+00:00  -4078255.0  54.0       24.0  0.857151   \n",
       "2016-12-31 00:00:00+00:00   1427864.0  61.0       27.0  0.858612   \n",
       "\n",
       "                                    x20        x4  1M Return  2M Return  \\\n",
       "date                                                                      \n",
       "2013-06-30 00:00:00+00:00  1.280585e+08  0.798176  -0.034622  -0.046142   \n",
       "2013-09-30 00:00:00+00:00 -1.194200e+07  0.456100   0.045633   0.126254   \n",
       "2014-03-31 00:00:00+00:00  1.291060e+06  0.456100   0.046886  -0.015851   \n",
       "2014-06-30 00:00:00+00:00  2.668928e+07  0.684151   0.102837   0.088936   \n",
       "2014-09-30 00:00:00+00:00 -1.421000e+06  0.570125  -0.063900  -0.088103   \n",
       "...                                 ...       ...        ...        ...   \n",
       "2016-12-31 00:00:00+00:00  1.248487e+07  0.548697   0.013215   0.094596   \n",
       "2016-03-31 00:00:00+00:00  2.701012e+09  1.963746   0.150481   0.064416   \n",
       "2016-06-30 00:00:00+00:00 -3.262900e+07  2.265861   0.006737  -0.001962   \n",
       "2016-09-30 00:00:00+00:00 -4.044620e+08  2.416918  -0.023709   0.110167   \n",
       "2016-12-31 00:00:00+00:00  3.098040e+08  2.265861   0.036835   0.024635   \n",
       "\n",
       "                           3M Return  \n",
       "date                                  \n",
       "2013-06-30 00:00:00+00:00   0.031842  \n",
       "2013-09-30 00:00:00+00:00   0.084477  \n",
       "2014-03-31 00:00:00+00:00  -0.031099  \n",
       "2014-06-30 00:00:00+00:00   0.126996  \n",
       "2014-09-30 00:00:00+00:00   0.005674  \n",
       "...                              ...  \n",
       "2016-12-31 00:00:00+00:00   0.161766  \n",
       "2016-03-31 00:00:00+00:00   0.202990  \n",
       "2016-06-30 00:00:00+00:00   0.156975  \n",
       "2016-09-30 00:00:00+00:00   0.117647  \n",
       "2016-12-31 00:00:00+00:00   0.000342  \n",
       "\n",
       "[5198 rows x 9 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Mean Square Error: 0.02477\n"
     ]
    }
   ],
   "source": [
    "rf = RandomForestRegressor(n_estimators = 10, random_state = 42)\n",
    "rf.fit(train_feature,train_return)\n",
    "test_predict = rf.predict(test_feature)\n",
    "errors = numpy.mean((test_predict - test_return)**2)\n",
    "print('Mean Square Error:', round(errors, 5))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/angli/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>stock_id</th>\n",
       "      <th>x21</th>\n",
       "      <th>x1</th>\n",
       "      <th>good fund</th>\n",
       "      <th>irisk</th>\n",
       "      <th>x20</th>\n",
       "      <th>x4</th>\n",
       "      <th>1M Return</th>\n",
       "      <th>2M Return</th>\n",
       "      <th>3M Return</th>\n",
       "      <th>return</th>\n",
       "      <th>prediction</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-03-31 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>1120647.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.837398</td>\n",
       "      <td>1.189200e+07</td>\n",
       "      <td>0.456100</td>\n",
       "      <td>-0.018229</td>\n",
       "      <td>-0.046472</td>\n",
       "      <td>-0.032314</td>\n",
       "      <td>-0.016493</td>\n",
       "      <td>0.065199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-06-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>1071697.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.843477</td>\n",
       "      <td>1.172300e+07</td>\n",
       "      <td>0.798176</td>\n",
       "      <td>-0.049614</td>\n",
       "      <td>-0.001777</td>\n",
       "      <td>-0.016493</td>\n",
       "      <td>-0.053435</td>\n",
       "      <td>-0.018978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-09-30 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>1170723.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.850096</td>\n",
       "      <td>1.257500e+07</td>\n",
       "      <td>0.798176</td>\n",
       "      <td>-0.013429</td>\n",
       "      <td>-0.012545</td>\n",
       "      <td>-0.053435</td>\n",
       "      <td>-0.030439</td>\n",
       "      <td>-0.020228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>238840.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.843434</td>\n",
       "      <td>1.674000e+06</td>\n",
       "      <td>0.684151</td>\n",
       "      <td>0.020735</td>\n",
       "      <td>-0.017241</td>\n",
       "      <td>-0.030439</td>\n",
       "      <td>0.071631</td>\n",
       "      <td>0.051284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-03-31 00:00:00+00:00</th>\n",
       "      <td>00130H105</td>\n",
       "      <td>625526.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.846921</td>\n",
       "      <td>9.838000e+06</td>\n",
       "      <td>1.026226</td>\n",
       "      <td>0.033119</td>\n",
       "      <td>0.049861</td>\n",
       "      <td>0.071631</td>\n",
       "      <td>0.242870</td>\n",
       "      <td>0.087090</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-09-30 00:00:00+00:00</th>\n",
       "      <td>V7780T103</td>\n",
       "      <td>1112898.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.858419</td>\n",
       "      <td>6.516600e+07</td>\n",
       "      <td>1.851852</td>\n",
       "      <td>0.038838</td>\n",
       "      <td>-0.102097</td>\n",
       "      <td>-0.114605</td>\n",
       "      <td>0.278769</td>\n",
       "      <td>0.048757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-03-31 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>1497676.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.858648</td>\n",
       "      <td>9.532270e+08</td>\n",
       "      <td>2.341390</td>\n",
       "      <td>0.047504</td>\n",
       "      <td>0.244781</td>\n",
       "      <td>0.290633</td>\n",
       "      <td>0.142796</td>\n",
       "      <td>0.122736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-06-30 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-931031.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>0.860360</td>\n",
       "      <td>1.312300e+07</td>\n",
       "      <td>2.190332</td>\n",
       "      <td>-0.027540</td>\n",
       "      <td>0.061738</td>\n",
       "      <td>0.142796</td>\n",
       "      <td>-0.016334</td>\n",
       "      <td>-0.005493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-09-30 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>4266231.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0.858136</td>\n",
       "      <td>1.180789e+09</td>\n",
       "      <td>2.870091</td>\n",
       "      <td>0.003351</td>\n",
       "      <td>0.036319</td>\n",
       "      <td>-0.016334</td>\n",
       "      <td>0.062756</td>\n",
       "      <td>0.140838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31 00:00:00+00:00</th>\n",
       "      <td>Y09827109</td>\n",
       "      <td>-1875008.0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0.859313</td>\n",
       "      <td>-1.993160e+08</td>\n",
       "      <td>3.625378</td>\n",
       "      <td>-0.053775</td>\n",
       "      <td>0.059207</td>\n",
       "      <td>0.062756</td>\n",
       "      <td>-0.132044</td>\n",
       "      <td>-0.060407</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4668 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id        x21    x1  good fund     irisk  \\\n",
       "date                                                                         \n",
       "2017-03-31 00:00:00+00:00  00130H105  1120647.0  11.0        2.0  0.837398   \n",
       "2017-06-30 00:00:00+00:00  00130H105  1071697.0  13.0        5.0  0.843477   \n",
       "2017-09-30 00:00:00+00:00  00130H105  1170723.0  16.0        5.0  0.850096   \n",
       "2017-12-31 00:00:00+00:00  00130H105   238840.0  17.0        2.0  0.843434   \n",
       "2018-03-31 00:00:00+00:00  00130H105   625526.0  16.0        2.0  0.846921   \n",
       "...                              ...        ...   ...        ...       ...   \n",
       "2019-09-30 00:00:00+00:00  V7780T103  1112898.0  42.0       12.0  0.858419   \n",
       "2017-03-31 00:00:00+00:00  Y09827109  1497676.0  59.0       28.0  0.858648   \n",
       "2017-06-30 00:00:00+00:00  Y09827109  -931031.0  60.0       31.0  0.860360   \n",
       "2017-09-30 00:00:00+00:00  Y09827109  4266231.0  77.0       35.0  0.858136   \n",
       "2017-12-31 00:00:00+00:00  Y09827109 -1875008.0  99.0       40.0  0.859313   \n",
       "\n",
       "                                    x20        x4  1M Return  2M Return  \\\n",
       "date                                                                      \n",
       "2017-03-31 00:00:00+00:00  1.189200e+07  0.456100  -0.018229  -0.046472   \n",
       "2017-06-30 00:00:00+00:00  1.172300e+07  0.798176  -0.049614  -0.001777   \n",
       "2017-09-30 00:00:00+00:00  1.257500e+07  0.798176  -0.013429  -0.012545   \n",
       "2017-12-31 00:00:00+00:00  1.674000e+06  0.684151   0.020735  -0.017241   \n",
       "2018-03-31 00:00:00+00:00  9.838000e+06  1.026226   0.033119   0.049861   \n",
       "...                                 ...       ...        ...        ...   \n",
       "2019-09-30 00:00:00+00:00  6.516600e+07  1.851852   0.038838  -0.102097   \n",
       "2017-03-31 00:00:00+00:00  9.532270e+08  2.341390   0.047504   0.244781   \n",
       "2017-06-30 00:00:00+00:00  1.312300e+07  2.190332  -0.027540   0.061738   \n",
       "2017-09-30 00:00:00+00:00  1.180789e+09  2.870091   0.003351   0.036319   \n",
       "2017-12-31 00:00:00+00:00 -1.993160e+08  3.625378  -0.053775   0.059207   \n",
       "\n",
       "                           3M Return    return  prediction  \n",
       "date                                                        \n",
       "2017-03-31 00:00:00+00:00  -0.032314 -0.016493    0.065199  \n",
       "2017-06-30 00:00:00+00:00  -0.016493 -0.053435   -0.018978  \n",
       "2017-09-30 00:00:00+00:00  -0.053435 -0.030439   -0.020228  \n",
       "2017-12-31 00:00:00+00:00  -0.030439  0.071631    0.051284  \n",
       "2018-03-31 00:00:00+00:00   0.071631  0.242870    0.087090  \n",
       "...                              ...       ...         ...  \n",
       "2019-09-30 00:00:00+00:00  -0.114605  0.278769    0.048757  \n",
       "2017-03-31 00:00:00+00:00   0.290633  0.142796    0.122736  \n",
       "2017-06-30 00:00:00+00:00   0.142796 -0.016334   -0.005493  \n",
       "2017-09-30 00:00:00+00:00  -0.016334  0.062756    0.140838  \n",
       "2017-12-31 00:00:00+00:00   0.062756 -0.132044   -0.060407  \n",
       "\n",
       "[4668 rows x 12 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test['prediction'] = test_predict\n",
    "test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "test.to_csv('prediction.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}