Step6 XY Matching.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 3523 entries, 1996-12-31 to 2016-12-23\n",
      "Columns: 132 entries, PET.RCLC3.D to dollar_index\n",
      "dtypes: float64(132)\n",
      "memory usage: 3.6 MB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime \n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "feature_data = pd.read_pickle(\"feature_data_ready.pkl\")\n",
    "response = pd.read_pickle(\"Step2_Y_variable.pkl\")\n",
    "feature_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 5031 entries, 1996-12-31 to 2016-12-23\n",
      "Data columns (total 26 columns):\n",
      "Price                          5031 non-null float64\n",
      "Daily_Return                   5031 non-null float64\n",
      "Pre1MonthAvgDailyRet           5031 non-null object\n",
      "Pre2MonthAvgDailyRet           5031 non-null object\n",
      "Pre3MonthAvgDailyRet           5031 non-null object\n",
      "Pre6MonthAvgDailyRet           5031 non-null object\n",
      "Pre1MonthVolDaily              5031 non-null object\n",
      "Pre2MonthVolDaily              5031 non-null object\n",
      "Pre3MonthVolDaily              5031 non-null object\n",
      "Pre6MonthVolDaily              5031 non-null object\n",
      "Pre1MonthDailyPriceRange       5031 non-null object\n",
      "Pre2MonthDailyPriceRange       5031 non-null object\n",
      "Pre3MonthDailyPriceRange       5031 non-null object\n",
      "Pre6MonthDailyPriceRange       5031 non-null object\n",
      "next1MonthAvgDailyRet          5031 non-null object\n",
      "next2MonthAvgDailyRet          5031 non-null object\n",
      "next3MonthAvgDailyRet          5031 non-null object\n",
      "next6MonthAvgDailyRet          5031 non-null object\n",
      "next1MonthVolDaily             5031 non-null object\n",
      "next2MonthVolDaily             5031 non-null object\n",
      "next3MonthVolDaily             5031 non-null object\n",
      "next6MonthVolDaily             5031 non-null object\n",
      "next1MonthAvgDailyRet_Label    5031 non-null float64\n",
      "next2MonthAvgDailyRet_Label    5031 non-null float64\n",
      "next3MonthAvgDailyRet_Label    5031 non-null float64\n",
      "next6MonthAvgDailyRet_Label    5031 non-null float64\n",
      "dtypes: float64(6), object(20)\n",
      "memory usage: 1.0+ MB\n"
     ]
    }
   ],
   "source": [
    "response_data = response['19961231':'20161223']\n",
    "response_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 3523 entries, 1996-12-31 to 2016-12-23\n",
      "Data columns (total 26 columns):\n",
      "Price                          3523 non-null float64\n",
      "Daily_Return                   3523 non-null float64\n",
      "Pre1MonthAvgDailyRet           3523 non-null object\n",
      "Pre2MonthAvgDailyRet           3523 non-null object\n",
      "Pre3MonthAvgDailyRet           3523 non-null object\n",
      "Pre6MonthAvgDailyRet           3523 non-null object\n",
      "Pre1MonthVolDaily              3523 non-null object\n",
      "Pre2MonthVolDaily              3523 non-null object\n",
      "Pre3MonthVolDaily              3523 non-null object\n",
      "Pre6MonthVolDaily              3523 non-null object\n",
      "Pre1MonthDailyPriceRange       3523 non-null object\n",
      "Pre2MonthDailyPriceRange       3523 non-null object\n",
      "Pre3MonthDailyPriceRange       3523 non-null object\n",
      "Pre6MonthDailyPriceRange       3523 non-null object\n",
      "next1MonthAvgDailyRet          3523 non-null object\n",
      "next2MonthAvgDailyRet          3523 non-null object\n",
      "next3MonthAvgDailyRet          3523 non-null object\n",
      "next6MonthAvgDailyRet          3523 non-null object\n",
      "next1MonthVolDaily             3523 non-null object\n",
      "next2MonthVolDaily             3523 non-null object\n",
      "next3MonthVolDaily             3523 non-null object\n",
      "next6MonthVolDaily             3523 non-null object\n",
      "next1MonthAvgDailyRet_Label    3523 non-null float64\n",
      "next2MonthAvgDailyRet_Label    3523 non-null float64\n",
      "next3MonthAvgDailyRet_Label    3523 non-null float64\n",
      "next6MonthAvgDailyRet_Label    3523 non-null float64\n",
      "dtypes: float64(6), object(20)\n",
      "memory usage: 743.1+ KB\n"
     ]
    }
   ],
   "source": [
    "refer_time = feature_data.index\n",
    "response_data = response_data.loc[refer_time,:]\n",
    "response_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Price                          0\n",
       "Daily_Return                   0\n",
       "Pre1MonthAvgDailyRet           0\n",
       "Pre2MonthAvgDailyRet           0\n",
       "Pre3MonthAvgDailyRet           0\n",
       "Pre6MonthAvgDailyRet           0\n",
       "Pre1MonthVolDaily              0\n",
       "Pre2MonthVolDaily              0\n",
       "Pre3MonthVolDaily              0\n",
       "Pre6MonthVolDaily              0\n",
       "Pre1MonthDailyPriceRange       0\n",
       "Pre2MonthDailyPriceRange       0\n",
       "Pre3MonthDailyPriceRange       0\n",
       "Pre6MonthDailyPriceRange       0\n",
       "next1MonthAvgDailyRet          0\n",
       "next2MonthAvgDailyRet          0\n",
       "next3MonthAvgDailyRet          0\n",
       "next6MonthAvgDailyRet          0\n",
       "next1MonthVolDaily             0\n",
       "next2MonthVolDaily             0\n",
       "next3MonthVolDaily             0\n",
       "next6MonthVolDaily             0\n",
       "next1MonthAvgDailyRet_Label    0\n",
       "next2MonthAvgDailyRet_Label    0\n",
       "next3MonthAvgDailyRet_Label    0\n",
       "next6MonthAvgDailyRet_Label    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response_data.apply(lambda x: sum(x.isna()),axis=0)"
   ]
  },
  {
   "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>Price</th>\n",
       "      <th>Daily_Return</th>\n",
       "      <th>Pre1MonthAvgDailyRet</th>\n",
       "      <th>Pre2MonthAvgDailyRet</th>\n",
       "      <th>Pre3MonthAvgDailyRet</th>\n",
       "      <th>Pre6MonthAvgDailyRet</th>\n",
       "      <th>Pre1MonthVolDaily</th>\n",
       "      <th>Pre2MonthVolDaily</th>\n",
       "      <th>Pre3MonthVolDaily</th>\n",
       "      <th>Pre6MonthVolDaily</th>\n",
       "      <th>...</th>\n",
       "      <th>next3MonthAvgDailyRet</th>\n",
       "      <th>next6MonthAvgDailyRet</th>\n",
       "      <th>next1MonthVolDaily</th>\n",
       "      <th>next2MonthVolDaily</th>\n",
       "      <th>next3MonthVolDaily</th>\n",
       "      <th>next6MonthVolDaily</th>\n",
       "      <th>next1MonthAvgDailyRet_Label</th>\n",
       "      <th>next2MonthAvgDailyRet_Label</th>\n",
       "      <th>next3MonthAvgDailyRet_Label</th>\n",
       "      <th>next6MonthAvgDailyRet_Label</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>1996-12-31</th>\n",
       "      <td>25.90</td>\n",
       "      <td>0.021696</td>\n",
       "      <td>0.00368008</td>\n",
       "      <td>0.00130641</td>\n",
       "      <td>0.0010388</td>\n",
       "      <td>0.00172152</td>\n",
       "      <td>0.0248342</td>\n",
       "      <td>0.027265</td>\n",
       "      <td>0.0244861</td>\n",
       "      <td>0.0220687</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.00374377</td>\n",
       "      <td>-0.00196119</td>\n",
       "      <td>0.0170061</td>\n",
       "      <td>0.0169067</td>\n",
       "      <td>0.018873</td>\n",
       "      <td>0.0188191</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997-01-02</th>\n",
       "      <td>25.55</td>\n",
       "      <td>-0.013514</td>\n",
       "      <td>0.004538</td>\n",
       "      <td>0.00332857</td>\n",
       "      <td>0.00150423</td>\n",
       "      <td>0.00178124</td>\n",
       "      <td>0.0245375</td>\n",
       "      <td>0.0265703</td>\n",
       "      <td>0.0247508</td>\n",
       "      <td>0.0221125</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.00413747</td>\n",
       "      <td>-0.00161174</td>\n",
       "      <td>0.0172481</td>\n",
       "      <td>0.0172916</td>\n",
       "      <td>0.0191414</td>\n",
       "      <td>0.0188243</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997-01-03</th>\n",
       "      <td>25.55</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00188525</td>\n",
       "      <td>0.00291779</td>\n",
       "      <td>0.000749758</td>\n",
       "      <td>0.0015721</td>\n",
       "      <td>0.0233033</td>\n",
       "      <td>0.0263725</td>\n",
       "      <td>0.0244826</td>\n",
       "      <td>0.0221326</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.0043823</td>\n",
       "      <td>-0.00196595</td>\n",
       "      <td>0.0172682</td>\n",
       "      <td>0.0173097</td>\n",
       "      <td>0.0191855</td>\n",
       "      <td>0.0192067</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997-01-06</th>\n",
       "      <td>26.25</td>\n",
       "      <td>0.027397</td>\n",
       "      <td>0.00018301</td>\n",
       "      <td>0.0033726</td>\n",
       "      <td>0.000814664</td>\n",
       "      <td>0.00169035</td>\n",
       "      <td>0.0238642</td>\n",
       "      <td>0.0265947</td>\n",
       "      <td>0.0244752</td>\n",
       "      <td>0.0220843</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.00473429</td>\n",
       "      <td>-0.00220085</td>\n",
       "      <td>0.0165381</td>\n",
       "      <td>0.0172954</td>\n",
       "      <td>0.0187988</td>\n",
       "      <td>0.0191022</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1997-01-07</th>\n",
       "      <td>26.25</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.00162497</td>\n",
       "      <td>0.00394716</td>\n",
       "      <td>0.000930716</td>\n",
       "      <td>0.00183826</td>\n",
       "      <td>0.0246252</td>\n",
       "      <td>0.026858</td>\n",
       "      <td>0.0245837</td>\n",
       "      <td>0.0221938</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.00465183</td>\n",
       "      <td>-0.00218032</td>\n",
       "      <td>0.0173775</td>\n",
       "      <td>0.0174851</td>\n",
       "      <td>0.0188307</td>\n",
       "      <td>0.0191059</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Price  Daily_Return Pre1MonthAvgDailyRet Pre2MonthAvgDailyRet  \\\n",
       "Date                                                                        \n",
       "1996-12-31  25.90      0.021696           0.00368008           0.00130641   \n",
       "1997-01-02  25.55     -0.013514             0.004538           0.00332857   \n",
       "1997-01-03  25.55      0.000000           0.00188525           0.00291779   \n",
       "1997-01-06  26.25      0.027397           0.00018301            0.0033726   \n",
       "1997-01-07  26.25      0.000000           0.00162497           0.00394716   \n",
       "\n",
       "           Pre3MonthAvgDailyRet Pre6MonthAvgDailyRet Pre1MonthVolDaily  \\\n",
       "Date                                                                     \n",
       "1996-12-31            0.0010388           0.00172152         0.0248342   \n",
       "1997-01-02           0.00150423           0.00178124         0.0245375   \n",
       "1997-01-03          0.000749758            0.0015721         0.0233033   \n",
       "1997-01-06          0.000814664           0.00169035         0.0238642   \n",
       "1997-01-07          0.000930716           0.00183826         0.0246252   \n",
       "\n",
       "           Pre2MonthVolDaily Pre3MonthVolDaily Pre6MonthVolDaily  \\\n",
       "Date                                                               \n",
       "1996-12-31          0.027265         0.0244861         0.0220687   \n",
       "1997-01-02         0.0265703         0.0247508         0.0221125   \n",
       "1997-01-03         0.0263725         0.0244826         0.0221326   \n",
       "1997-01-06         0.0265947         0.0244752         0.0220843   \n",
       "1997-01-07          0.026858         0.0245837         0.0221938   \n",
       "\n",
       "                       ...             next3MonthAvgDailyRet  \\\n",
       "Date                   ...                                     \n",
       "1996-12-31             ...                       -0.00374377   \n",
       "1997-01-02             ...                       -0.00413747   \n",
       "1997-01-03             ...                        -0.0043823   \n",
       "1997-01-06             ...                       -0.00473429   \n",
       "1997-01-07             ...                       -0.00465183   \n",
       "\n",
       "           next6MonthAvgDailyRet next1MonthVolDaily next2MonthVolDaily  \\\n",
       "Date                                                                     \n",
       "1996-12-31           -0.00196119          0.0170061          0.0169067   \n",
       "1997-01-02           -0.00161174          0.0172481          0.0172916   \n",
       "1997-01-03           -0.00196595          0.0172682          0.0173097   \n",
       "1997-01-06           -0.00220085          0.0165381          0.0172954   \n",
       "1997-01-07           -0.00218032          0.0173775          0.0174851   \n",
       "\n",
       "           next3MonthVolDaily next6MonthVolDaily next1MonthAvgDailyRet_Label  \\\n",
       "Date                                                                           \n",
       "1996-12-31           0.018873          0.0188191                         5.0   \n",
       "1997-01-02          0.0191414          0.0188243                         5.0   \n",
       "1997-01-03          0.0191855          0.0192067                         5.0   \n",
       "1997-01-06          0.0187988          0.0191022                         5.0   \n",
       "1997-01-07          0.0188307          0.0191059                         5.0   \n",
       "\n",
       "           next2MonthAvgDailyRet_Label next3MonthAvgDailyRet_Label  \\\n",
       "Date                                                                 \n",
       "1996-12-31                         6.0                         6.0   \n",
       "1997-01-02                         6.0                         6.0   \n",
       "1997-01-03                         6.0                         6.0   \n",
       "1997-01-06                         6.0                         6.0   \n",
       "1997-01-07                         6.0                         6.0   \n",
       "\n",
       "           next6MonthAvgDailyRet_Label  \n",
       "Date                                    \n",
       "1996-12-31                         6.0  \n",
       "1997-01-02                         6.0  \n",
       "1997-01-03                         6.0  \n",
       "1997-01-06                         6.0  \n",
       "1997-01-07                         6.0  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "response_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "feature_data = feature_data.join(response_data.iloc[:,list(range(2,10))])\n",
    "sum(feature_data.apply(lambda x: sum(x.isna())))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "response_data.to_pickle(\"response_data.pkl\")\n",
    "feature_data.to_pickle(\"feature_data_final_ready.pkl\")"
   ]
  },
  {
   "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}