Untitled1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "prices = pd.read_csv('prices.csv')\n",
    "factors = pd.read_csv('13f.csv')"
   ]
  },
  {
   "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>pSP_CUSIP</th>\n",
       "      <th>pSP_EXCHANGE</th>\n",
       "      <th>pSP_TICKER</th>\n",
       "      <th>pSP_DATE</th>\n",
       "      <th>pSP_VOLUME</th>\n",
       "      <th>pSP_OPEN</th>\n",
       "      <th>pSP_HIGH</th>\n",
       "      <th>pSP_LOW</th>\n",
       "      <th>pSP_CLOSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-02</td>\n",
       "      <td>2808000</td>\n",
       "      <td>13.1250</td>\n",
       "      <td>13.6875</td>\n",
       "      <td>13.0625</td>\n",
       "      <td>13.5938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-03</td>\n",
       "      <td>5008800</td>\n",
       "      <td>13.5312</td>\n",
       "      <td>13.8438</td>\n",
       "      <td>13.5000</td>\n",
       "      <td>13.8125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-04</td>\n",
       "      <td>4276000</td>\n",
       "      <td>13.7188</td>\n",
       "      <td>13.9375</td>\n",
       "      <td>13.5938</td>\n",
       "      <td>13.9375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-05</td>\n",
       "      <td>2618400</td>\n",
       "      <td>13.8750</td>\n",
       "      <td>14.1250</td>\n",
       "      <td>13.8125</td>\n",
       "      <td>13.8438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-08</td>\n",
       "      <td>3471200</td>\n",
       "      <td>13.7812</td>\n",
       "      <td>13.8750</td>\n",
       "      <td>12.8125</td>\n",
       "      <td>13.0938</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>105864349</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-22</td>\n",
       "      <td>14608310</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.2205</td>\n",
       "      <td>0.2000</td>\n",
       "      <td>0.2148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105864350</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>13622890</td>\n",
       "      <td>0.2101</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.2050</td>\n",
       "      <td>0.2108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105864351</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-27</td>\n",
       "      <td>220125900</td>\n",
       "      <td>0.1460</td>\n",
       "      <td>0.1652</td>\n",
       "      <td>0.1438</td>\n",
       "      <td>0.1632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105864352</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-28</td>\n",
       "      <td>65927280</td>\n",
       "      <td>0.1705</td>\n",
       "      <td>0.1730</td>\n",
       "      <td>0.1550</td>\n",
       "      <td>0.1594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105864353</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>33669380</td>\n",
       "      <td>0.1600</td>\n",
       "      <td>0.1620</td>\n",
       "      <td>0.1540</td>\n",
       "      <td>0.1577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105864354 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           pSP_CUSIP pSP_EXCHANGE pSP_TICKER    pSP_DATE  pSP_VOLUME  \\\n",
       "0          013817101       N          AA      1996-01-02     2808000   \n",
       "1          013817101       N          AA      1996-01-03     5008800   \n",
       "2          013817101       N          AA      1996-01-04     4276000   \n",
       "3          013817101       N          AA      1996-01-05     2618400   \n",
       "4          013817101       N          AA      1996-01-08     3471200   \n",
       "...              ...          ...        ...         ...         ...   \n",
       "105864349  98979F107            A        ZOM  2020-05-22    14608310   \n",
       "105864350  98979F107            A        ZOM  2020-05-26    13622890   \n",
       "105864351  98979F107            A        ZOM  2020-05-27   220125900   \n",
       "105864352  98979F107            A        ZOM  2020-05-28    65927280   \n",
       "105864353  98979F107            A        ZOM  2020-05-29    33669380   \n",
       "\n",
       "           pSP_OPEN  pSP_HIGH  pSP_LOW  pSP_CLOSE  \n",
       "0           13.1250   13.6875  13.0625    13.5938  \n",
       "1           13.5312   13.8438  13.5000    13.8125  \n",
       "2           13.7188   13.9375  13.5938    13.9375  \n",
       "3           13.8750   14.1250  13.8125    13.8438  \n",
       "4           13.7812   13.8750  12.8125    13.0938  \n",
       "...             ...       ...      ...        ...  \n",
       "105864349    0.2200    0.2205   0.2000     0.2148  \n",
       "105864350    0.2101    0.2200   0.2050     0.2108  \n",
       "105864351    0.1460    0.1652   0.1438     0.1632  \n",
       "105864352    0.1705    0.1730   0.1550     0.1594  \n",
       "105864353    0.1600    0.1620   0.1540     0.1577  \n",
       "\n",
       "[105864354 rows x 9 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prices_copy = prices.copy()\n",
    "prices"
   ]
  },
  {
   "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>iRECORD_ID</th>\n",
       "      <th>iCIK</th>\n",
       "      <th>iCUSIP</th>\n",
       "      <th>iPERIOD_END</th>\n",
       "      <th>iFILING_DATE</th>\n",
       "      <th>iAMEND</th>\n",
       "      <th>iRESTATEMENT</th>\n",
       "      <th>iTYPE</th>\n",
       "      <th>iQTY</th>\n",
       "      <th>iMARKET_VALUE</th>\n",
       "      <th>iLONG_FRACTION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4443748</td>\n",
       "      <td>216235</td>\n",
       "      <td>055622104</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28287.0</td>\n",
       "      <td>3134550.0</td>\n",
       "      <td>0.009282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4443749</td>\n",
       "      <td>216235</td>\n",
       "      <td>020039103</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>79199.0</td>\n",
       "      <td>5690620.0</td>\n",
       "      <td>0.016851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4443750</td>\n",
       "      <td>216235</td>\n",
       "      <td>713448108</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>186140.0</td>\n",
       "      <td>5795950.0</td>\n",
       "      <td>0.017163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4443751</td>\n",
       "      <td>216235</td>\n",
       "      <td>002824100</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>169555.0</td>\n",
       "      <td>6290420.0</td>\n",
       "      <td>0.018627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4443752</td>\n",
       "      <td>216235</td>\n",
       "      <td>05964H105</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>2000-01-03</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16897.0</td>\n",
       "      <td>174250.0</td>\n",
       "      <td>0.000516</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>57569231</th>\n",
       "      <td>74561590</td>\n",
       "      <td>1730479</td>\n",
       "      <td>039483102</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>2020-04-23</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>0.000086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57569232</th>\n",
       "      <td>74561591</td>\n",
       "      <td>1730479</td>\n",
       "      <td>88160R101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>2020-04-23</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>0.000102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57569233</th>\n",
       "      <td>74561592</td>\n",
       "      <td>1730479</td>\n",
       "      <td>921937793</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>2020-04-23</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>0.000196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57569234</th>\n",
       "      <td>74561593</td>\n",
       "      <td>1730479</td>\n",
       "      <td>863667101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>2020-04-23</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1030.0</td>\n",
       "      <td>171000.0</td>\n",
       "      <td>0.001341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57569235</th>\n",
       "      <td>74561594</td>\n",
       "      <td>1730479</td>\n",
       "      <td>232806109</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>2020-04-23</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.000016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57569236 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          iRECORD_ID     iCIK     iCUSIP iPERIOD_END iFILING_DATE  iAMEND  \\\n",
       "0            4443748   216235  055622104  1999-09-30   2000-01-03       0   \n",
       "1            4443749   216235  020039103  1999-09-30   2000-01-03       0   \n",
       "2            4443750   216235  713448108  1999-09-30   2000-01-03       0   \n",
       "3            4443751   216235  002824100  1999-09-30   2000-01-03       0   \n",
       "4            4443752   216235  05964H105  1999-09-30   2000-01-03       0   \n",
       "...              ...      ...        ...         ...          ...     ...   \n",
       "57569231    74561590  1730479  039483102  2020-03-31   2020-04-23       0   \n",
       "57569232    74561591  1730479  88160R101  2020-03-31   2020-04-23       0   \n",
       "57569233    74561592  1730479  921937793  2020-03-31   2020-04-23       0   \n",
       "57569234    74561593  1730479  863667101  2020-03-31   2020-04-23       0   \n",
       "57569235    74561594  1730479  232806109  2020-03-31   2020-04-23       0   \n",
       "\n",
       "          iRESTATEMENT  iTYPE      iQTY  iMARKET_VALUE  iLONG_FRACTION  \n",
       "0                    0      0   28287.0      3134550.0        0.009282  \n",
       "1                    0      0   79199.0      5690620.0        0.016851  \n",
       "2                    0      0  186140.0      5795950.0        0.017163  \n",
       "3                    0      0  169555.0      6290420.0        0.018627  \n",
       "4                    0      0   16897.0       174250.0        0.000516  \n",
       "...                ...    ...       ...            ...             ...  \n",
       "57569231             0      0     300.0        11000.0        0.000086  \n",
       "57569232             0      0      25.0        13000.0        0.000102  \n",
       "57569233             0      0     230.0        25000.0        0.000196  \n",
       "57569234             0      0    1030.0       171000.0        0.001341  \n",
       "57569235             0      0     100.0         2000.0        0.000016  \n",
       "\n",
       "[57569236 rows x 11 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors_copy = factors.copy()\n",
    "factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "prices.columns = ['stock_id','exchange','ticker','date','valume','open','high','low','close']\n",
    "factors.columns = ['record_id','iCIK','stock_id','period_end','date','iAMEND','iRESTATEMENT','iTYPE','iQTY','iMARKET_VALUE','iLONG_FRACTION']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>exchange</th>\n",
       "      <th>ticker</th>\n",
       "      <th>valume</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1996-01-02 00:00:00+00:00</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>2808000</td>\n",
       "      <td>13.1250</td>\n",
       "      <td>13.6875</td>\n",
       "      <td>13.0625</td>\n",
       "      <td>13.5938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-03 00:00:00+00:00</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>5008800</td>\n",
       "      <td>13.5312</td>\n",
       "      <td>13.8438</td>\n",
       "      <td>13.5000</td>\n",
       "      <td>13.8125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-04 00:00:00+00:00</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>4276000</td>\n",
       "      <td>13.7188</td>\n",
       "      <td>13.9375</td>\n",
       "      <td>13.5938</td>\n",
       "      <td>13.9375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-05 00:00:00+00:00</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>2618400</td>\n",
       "      <td>13.8750</td>\n",
       "      <td>14.1250</td>\n",
       "      <td>13.8125</td>\n",
       "      <td>13.8438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1996-01-08 00:00:00+00:00</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>3471200</td>\n",
       "      <td>13.7812</td>\n",
       "      <td>13.8750</td>\n",
       "      <td>12.8125</td>\n",
       "      <td>13.0938</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-22 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>14608310</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.2205</td>\n",
       "      <td>0.2000</td>\n",
       "      <td>0.2148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-26 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>13622890</td>\n",
       "      <td>0.2101</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.2050</td>\n",
       "      <td>0.2108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-27 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>220125900</td>\n",
       "      <td>0.1460</td>\n",
       "      <td>0.1652</td>\n",
       "      <td>0.1438</td>\n",
       "      <td>0.1632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-28 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>65927280</td>\n",
       "      <td>0.1705</td>\n",
       "      <td>0.1730</td>\n",
       "      <td>0.1550</td>\n",
       "      <td>0.1594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>33669380</td>\n",
       "      <td>0.1600</td>\n",
       "      <td>0.1620</td>\n",
       "      <td>0.1540</td>\n",
       "      <td>0.1577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105864354 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id exchange  ticker     valume     open  \\\n",
       "date                                                                        \n",
       "1996-01-02 00:00:00+00:00  013817101   N       AA        2808000  13.1250   \n",
       "1996-01-03 00:00:00+00:00  013817101   N       AA        5008800  13.5312   \n",
       "1996-01-04 00:00:00+00:00  013817101   N       AA        4276000  13.7188   \n",
       "1996-01-05 00:00:00+00:00  013817101   N       AA        2618400  13.8750   \n",
       "1996-01-08 00:00:00+00:00  013817101   N       AA        3471200  13.7812   \n",
       "...                              ...      ...     ...        ...      ...   \n",
       "2020-05-22 00:00:00+00:00  98979F107        A     ZOM   14608310   0.2200   \n",
       "2020-05-26 00:00:00+00:00  98979F107        A     ZOM   13622890   0.2101   \n",
       "2020-05-27 00:00:00+00:00  98979F107        A     ZOM  220125900   0.1460   \n",
       "2020-05-28 00:00:00+00:00  98979F107        A     ZOM   65927280   0.1705   \n",
       "2020-05-29 00:00:00+00:00  98979F107        A     ZOM   33669380   0.1600   \n",
       "\n",
       "                              high      low    close  \n",
       "date                                                  \n",
       "1996-01-02 00:00:00+00:00  13.6875  13.0625  13.5938  \n",
       "1996-01-03 00:00:00+00:00  13.8438  13.5000  13.8125  \n",
       "1996-01-04 00:00:00+00:00  13.9375  13.5938  13.9375  \n",
       "1996-01-05 00:00:00+00:00  14.1250  13.8125  13.8438  \n",
       "1996-01-08 00:00:00+00:00  13.8750  12.8125  13.0938  \n",
       "...                            ...      ...      ...  \n",
       "2020-05-22 00:00:00+00:00   0.2205   0.2000   0.2148  \n",
       "2020-05-26 00:00:00+00:00   0.2200   0.2050   0.2108  \n",
       "2020-05-27 00:00:00+00:00   0.1652   0.1438   0.1632  \n",
       "2020-05-28 00:00:00+00:00   0.1730   0.1550   0.1594  \n",
       "2020-05-29 00:00:00+00:00   0.1620   0.1540   0.1577  \n",
       "\n",
       "[105864354 rows x 8 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prices = prices.set_index('date')\n",
    "prices.index = pd.DatetimeIndex.tz_localize(pd.to_datetime(prices.index), tz = 'UTC')\n",
    "prices"
   ]
  },
  {
   "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>record_id</th>\n",
       "      <th>iCIK</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>period_end</th>\n",
       "      <th>iAMEND</th>\n",
       "      <th>iRESTATEMENT</th>\n",
       "      <th>iTYPE</th>\n",
       "      <th>iQTY</th>\n",
       "      <th>iMARKET_VALUE</th>\n",
       "      <th>iLONG_FRACTION</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>2000-01-03 00:00:00+00:00</th>\n",
       "      <td>4443748</td>\n",
       "      <td>216235</td>\n",
       "      <td>055622104</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>28287.0</td>\n",
       "      <td>3134550.0</td>\n",
       "      <td>0.009282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-03 00:00:00+00:00</th>\n",
       "      <td>4443749</td>\n",
       "      <td>216235</td>\n",
       "      <td>020039103</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>79199.0</td>\n",
       "      <td>5690620.0</td>\n",
       "      <td>0.016851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-03 00:00:00+00:00</th>\n",
       "      <td>4443750</td>\n",
       "      <td>216235</td>\n",
       "      <td>713448108</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>186140.0</td>\n",
       "      <td>5795950.0</td>\n",
       "      <td>0.017163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-03 00:00:00+00:00</th>\n",
       "      <td>4443751</td>\n",
       "      <td>216235</td>\n",
       "      <td>002824100</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>169555.0</td>\n",
       "      <td>6290420.0</td>\n",
       "      <td>0.018627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000-01-03 00:00:00+00:00</th>\n",
       "      <td>4443752</td>\n",
       "      <td>216235</td>\n",
       "      <td>05964H105</td>\n",
       "      <td>1999-09-30</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>16897.0</td>\n",
       "      <td>174250.0</td>\n",
       "      <td>0.000516</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>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561590</td>\n",
       "      <td>1730479</td>\n",
       "      <td>039483102</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>0.000086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561591</td>\n",
       "      <td>1730479</td>\n",
       "      <td>88160R101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>0.000102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561592</td>\n",
       "      <td>1730479</td>\n",
       "      <td>921937793</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>0.000196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561593</td>\n",
       "      <td>1730479</td>\n",
       "      <td>863667101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1030.0</td>\n",
       "      <td>171000.0</td>\n",
       "      <td>0.001341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561594</td>\n",
       "      <td>1730479</td>\n",
       "      <td>232806109</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.000016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57569236 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           record_id     iCIK   stock_id  period_end  iAMEND  \\\n",
       "date                                                                           \n",
       "2000-01-03 00:00:00+00:00    4443748   216235  055622104  1999-09-30       0   \n",
       "2000-01-03 00:00:00+00:00    4443749   216235  020039103  1999-09-30       0   \n",
       "2000-01-03 00:00:00+00:00    4443750   216235  713448108  1999-09-30       0   \n",
       "2000-01-03 00:00:00+00:00    4443751   216235  002824100  1999-09-30       0   \n",
       "2000-01-03 00:00:00+00:00    4443752   216235  05964H105  1999-09-30       0   \n",
       "...                              ...      ...        ...         ...     ...   \n",
       "2020-04-23 00:00:00+00:00   74561590  1730479  039483102  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561591  1730479  88160R101  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561592  1730479  921937793  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561593  1730479  863667101  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561594  1730479  232806109  2020-03-31       0   \n",
       "\n",
       "                           iRESTATEMENT  iTYPE      iQTY  iMARKET_VALUE  \\\n",
       "date                                                                      \n",
       "2000-01-03 00:00:00+00:00             0      0   28287.0      3134550.0   \n",
       "2000-01-03 00:00:00+00:00             0      0   79199.0      5690620.0   \n",
       "2000-01-03 00:00:00+00:00             0      0  186140.0      5795950.0   \n",
       "2000-01-03 00:00:00+00:00             0      0  169555.0      6290420.0   \n",
       "2000-01-03 00:00:00+00:00             0      0   16897.0       174250.0   \n",
       "...                                 ...    ...       ...            ...   \n",
       "2020-04-23 00:00:00+00:00             0      0     300.0        11000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0      25.0        13000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0     230.0        25000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0    1030.0       171000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0     100.0         2000.0   \n",
       "\n",
       "                           iLONG_FRACTION  \n",
       "date                                       \n",
       "2000-01-03 00:00:00+00:00        0.009282  \n",
       "2000-01-03 00:00:00+00:00        0.016851  \n",
       "2000-01-03 00:00:00+00:00        0.017163  \n",
       "2000-01-03 00:00:00+00:00        0.018627  \n",
       "2000-01-03 00:00:00+00:00        0.000516  \n",
       "...                                   ...  \n",
       "2020-04-23 00:00:00+00:00        0.000086  \n",
       "2020-04-23 00:00:00+00:00        0.000102  \n",
       "2020-04-23 00:00:00+00:00        0.000196  \n",
       "2020-04-23 00:00:00+00:00        0.001341  \n",
       "2020-04-23 00:00:00+00:00        0.000016  \n",
       "\n",
       "[57569236 rows x 10 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors = factors.set_index('date')\n",
    "factors.index = pd.DatetimeIndex.tz_localize(pd.to_datetime(factors.index), tz = 'UTC')\n",
    "factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "prices = prices['2017-01-01':]"
   ]
  },
  {
   "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></th>\n",
       "      <th>stock_id</th>\n",
       "      <th>exchange</th>\n",
       "      <th>ticker</th>\n",
       "      <th>valume</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-08-15 00:00:00+00:00</th>\n",
       "      <td>P1507A122</td>\n",
       "      <td>PK</td>\n",
       "      <td>BRPBF</td>\n",
       "      <td>0</td>\n",
       "      <td>2.2900</td>\n",
       "      <td>2.2900</td>\n",
       "      <td>2.2900</td>\n",
       "      <td>2.2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-12 00:00:00+00:00</th>\n",
       "      <td>67090W208</td>\n",
       "      <td>PK</td>\n",
       "      <td>NVGLF</td>\n",
       "      <td>0</td>\n",
       "      <td>0.2260</td>\n",
       "      <td>0.2300</td>\n",
       "      <td>0.2260</td>\n",
       "      <td>0.2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-05 00:00:00+00:00</th>\n",
       "      <td>T2320M117</td>\n",
       "      <td>PK</td>\n",
       "      <td>BZZFF</td>\n",
       "      <td>0</td>\n",
       "      <td>13.6000</td>\n",
       "      <td>13.6000</td>\n",
       "      <td>13.6000</td>\n",
       "      <td>13.6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-29 00:00:00+00:00</th>\n",
       "      <td>009126202</td>\n",
       "      <td>PK</td>\n",
       "      <td>AIQUY</td>\n",
       "      <td>212051</td>\n",
       "      <td>24.5455</td>\n",
       "      <td>24.6455</td>\n",
       "      <td>24.4182</td>\n",
       "      <td>24.6091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-12 00:00:00+00:00</th>\n",
       "      <td>04650Q107</td>\n",
       "      <td>PK</td>\n",
       "      <td>ACPGF</td>\n",
       "      <td>0</td>\n",
       "      <td>0.3500</td>\n",
       "      <td>0.3500</td>\n",
       "      <td>0.3500</td>\n",
       "      <td>0.3500</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-22 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>14608310</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.2205</td>\n",
       "      <td>0.2000</td>\n",
       "      <td>0.2148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-26 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>13622890</td>\n",
       "      <td>0.2101</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.2050</td>\n",
       "      <td>0.2108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-27 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>220125900</td>\n",
       "      <td>0.1460</td>\n",
       "      <td>0.1652</td>\n",
       "      <td>0.1438</td>\n",
       "      <td>0.1632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-28 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>65927280</td>\n",
       "      <td>0.1705</td>\n",
       "      <td>0.1730</td>\n",
       "      <td>0.1550</td>\n",
       "      <td>0.1594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>33669380</td>\n",
       "      <td>0.1600</td>\n",
       "      <td>0.1620</td>\n",
       "      <td>0.1540</td>\n",
       "      <td>0.1577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14704797 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id exchange ticker     valume     open  \\\n",
       "date                                                                       \n",
       "2018-08-15 00:00:00+00:00  P1507A122       PK  BRPBF          0   2.2900   \n",
       "2018-02-12 00:00:00+00:00  67090W208       PK  NVGLF          0   0.2260   \n",
       "2019-07-05 00:00:00+00:00  T2320M117       PK  BZZFF          0  13.6000   \n",
       "2018-01-29 00:00:00+00:00  009126202       PK  AIQUY     212051  24.5455   \n",
       "2017-04-12 00:00:00+00:00  04650Q107       PK  ACPGF          0   0.3500   \n",
       "...                              ...      ...    ...        ...      ...   \n",
       "2020-05-22 00:00:00+00:00  98979F107        A    ZOM   14608310   0.2200   \n",
       "2020-05-26 00:00:00+00:00  98979F107        A    ZOM   13622890   0.2101   \n",
       "2020-05-27 00:00:00+00:00  98979F107        A    ZOM  220125900   0.1460   \n",
       "2020-05-28 00:00:00+00:00  98979F107        A    ZOM   65927280   0.1705   \n",
       "2020-05-29 00:00:00+00:00  98979F107        A    ZOM   33669380   0.1600   \n",
       "\n",
       "                              high      low    close  \n",
       "date                                                  \n",
       "2018-08-15 00:00:00+00:00   2.2900   2.2900   2.2900  \n",
       "2018-02-12 00:00:00+00:00   0.2300   0.2260   0.2300  \n",
       "2019-07-05 00:00:00+00:00  13.6000  13.6000  13.6000  \n",
       "2018-01-29 00:00:00+00:00  24.6455  24.4182  24.6091  \n",
       "2017-04-12 00:00:00+00:00   0.3500   0.3500   0.3500  \n",
       "...                            ...      ...      ...  \n",
       "2020-05-22 00:00:00+00:00   0.2205   0.2000   0.2148  \n",
       "2020-05-26 00:00:00+00:00   0.2200   0.2050   0.2108  \n",
       "2020-05-27 00:00:00+00:00   0.1652   0.1438   0.1632  \n",
       "2020-05-28 00:00:00+00:00   0.1730   0.1550   0.1594  \n",
       "2020-05-29 00:00:00+00:00   0.1620   0.1540   0.1577  \n",
       "\n",
       "[14704797 rows x 8 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors = factors['2017-01-01':]\n",
    "prices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>record_id</th>\n",
       "      <th>iCIK</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>period_end</th>\n",
       "      <th>iAMEND</th>\n",
       "      <th>iRESTATEMENT</th>\n",
       "      <th>iTYPE</th>\n",
       "      <th>iQTY</th>\n",
       "      <th>iMARKET_VALUE</th>\n",
       "      <th>iLONG_FRACTION</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>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234727</td>\n",
       "      <td>866780</td>\n",
       "      <td>74347R693</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23118.0</td>\n",
       "      <td>2242000.0</td>\n",
       "      <td>0.006441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234728</td>\n",
       "      <td>866780</td>\n",
       "      <td>494368103</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>26134.0</td>\n",
       "      <td>2982000.0</td>\n",
       "      <td>0.008567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234729</td>\n",
       "      <td>866780</td>\n",
       "      <td>458140100</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15529.0</td>\n",
       "      <td>563000.0</td>\n",
       "      <td>0.001617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234730</td>\n",
       "      <td>866780</td>\n",
       "      <td>74347R206</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>61423.0</td>\n",
       "      <td>5290000.0</td>\n",
       "      <td>0.015197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234731</td>\n",
       "      <td>866780</td>\n",
       "      <td>464287523</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29548.0</td>\n",
       "      <td>3626000.0</td>\n",
       "      <td>0.010417</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>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561590</td>\n",
       "      <td>1730479</td>\n",
       "      <td>039483102</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>0.000086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561591</td>\n",
       "      <td>1730479</td>\n",
       "      <td>88160R101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>0.000102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561592</td>\n",
       "      <td>1730479</td>\n",
       "      <td>921937793</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>0.000196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561593</td>\n",
       "      <td>1730479</td>\n",
       "      <td>863667101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1030.0</td>\n",
       "      <td>171000.0</td>\n",
       "      <td>0.001341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561594</td>\n",
       "      <td>1730479</td>\n",
       "      <td>232806109</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.000016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15320871 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           record_id     iCIK   stock_id  period_end  iAMEND  \\\n",
       "date                                                                           \n",
       "2017-01-03 00:00:00+00:00   59234727   866780  74347R693  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234728   866780  494368103  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234729   866780  458140100  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234730   866780  74347R206  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234731   866780  464287523  2016-12-31       0   \n",
       "...                              ...      ...        ...         ...     ...   \n",
       "2020-04-23 00:00:00+00:00   74561590  1730479  039483102  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561591  1730479  88160R101  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561592  1730479  921937793  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561593  1730479  863667101  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561594  1730479  232806109  2020-03-31       0   \n",
       "\n",
       "                           iRESTATEMENT  iTYPE     iQTY  iMARKET_VALUE  \\\n",
       "date                                                                     \n",
       "2017-01-03 00:00:00+00:00             0      0  23118.0      2242000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  26134.0      2982000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  15529.0       563000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  61423.0      5290000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  29548.0      3626000.0   \n",
       "...                                 ...    ...      ...            ...   \n",
       "2020-04-23 00:00:00+00:00             0      0    300.0        11000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0     25.0        13000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0    230.0        25000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0   1030.0       171000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0    100.0         2000.0   \n",
       "\n",
       "                           iLONG_FRACTION  \n",
       "date                                       \n",
       "2017-01-03 00:00:00+00:00        0.006441  \n",
       "2017-01-03 00:00:00+00:00        0.008567  \n",
       "2017-01-03 00:00:00+00:00        0.001617  \n",
       "2017-01-03 00:00:00+00:00        0.015197  \n",
       "2017-01-03 00:00:00+00:00        0.010417  \n",
       "...                                   ...  \n",
       "2020-04-23 00:00:00+00:00        0.000086  \n",
       "2020-04-23 00:00:00+00:00        0.000102  \n",
       "2020-04-23 00:00:00+00:00        0.000196  \n",
       "2020-04-23 00:00:00+00:00        0.001341  \n",
       "2020-04-23 00:00:00+00:00        0.000016  \n",
       "\n",
       "[15320871 rows x 10 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>close</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>2018-08-15 00:00:00+00:00</th>\n",
       "      <td>P1507A122</td>\n",
       "      <td>2.2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-12 00:00:00+00:00</th>\n",
       "      <td>67090W208</td>\n",
       "      <td>0.2300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-07-05 00:00:00+00:00</th>\n",
       "      <td>T2320M117</td>\n",
       "      <td>13.6000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-01-29 00:00:00+00:00</th>\n",
       "      <td>009126202</td>\n",
       "      <td>24.6091</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-12 00:00:00+00:00</th>\n",
       "      <td>04650Q107</td>\n",
       "      <td>0.3500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-22 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>0.2148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-26 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>0.2108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-27 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>0.1632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-28 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>0.1594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-05-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>0.1577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14704797 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id    close\n",
       "date                                         \n",
       "2018-08-15 00:00:00+00:00  P1507A122   2.2900\n",
       "2018-02-12 00:00:00+00:00  67090W208   0.2300\n",
       "2019-07-05 00:00:00+00:00  T2320M117  13.6000\n",
       "2018-01-29 00:00:00+00:00  009126202  24.6091\n",
       "2017-04-12 00:00:00+00:00  04650Q107   0.3500\n",
       "...                              ...      ...\n",
       "2020-05-22 00:00:00+00:00  98979F107   0.2148\n",
       "2020-05-26 00:00:00+00:00  98979F107   0.2108\n",
       "2020-05-27 00:00:00+00:00  98979F107   0.1632\n",
       "2020-05-28 00:00:00+00:00  98979F107   0.1594\n",
       "2020-05-29 00:00:00+00:00  98979F107   0.1577\n",
       "\n",
       "[14704797 rows x 2 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_use = prices[['stock_id','close']]\n",
    "price_use"
   ]
  },
  {
   "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>close</th>\n",
       "      <th>3MA close</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-08-15 00:00:00+00:00</th>\n",
       "      <td>P1507A122</td>\n",
       "      <td>2.2900</td>\n",
       "      <td>2.2900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-12 00:00:00+00:00</th>\n",
       "      <td>04650Q107</td>\n",
       "      <td>0.3500</td>\n",
       "      <td>0.5722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-09 00:00:00+00:00</th>\n",
       "      <td>67090W208</td>\n",
       "      <td>0.2300</td>\n",
       "      <td>0.3909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-09-08 00:00:00+00:00</th>\n",
       "      <td>02153X108</td>\n",
       "      <td>100.0000</td>\n",
       "      <td>73.0500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-06-06 00:00:00+00:00</th>\n",
       "      <td>641266101</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-01 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-08 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-15 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-22 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14704797 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id     close  3MA close\n",
       "date                                                     \n",
       "2018-08-15 00:00:00+00:00  P1507A122    2.2900     2.2900\n",
       "2017-04-12 00:00:00+00:00  04650Q107    0.3500     0.5722\n",
       "2018-02-09 00:00:00+00:00  67090W208    0.2300     0.3909\n",
       "2017-09-08 00:00:00+00:00  02153X108  100.0000    73.0500\n",
       "2017-06-06 00:00:00+00:00  641266101    0.0001     0.0001\n",
       "...                              ...       ...        ...\n",
       "2020-02-01 00:00:00+00:00  98979F107       NaN     0.1759\n",
       "2020-02-08 00:00:00+00:00  98979F107       NaN     0.2347\n",
       "2020-02-15 00:00:00+00:00  98979F107       NaN     0.2310\n",
       "2020-02-22 00:00:00+00:00  98979F107       NaN     0.2148\n",
       "2020-02-29 00:00:00+00:00  98979F107       NaN     0.1577\n",
       "\n",
       "[14704797 rows x 3 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_use2 = price_use.copy()\n",
    "price_use2['date'] =price_use.index-pd.DateOffset(months = 3)\n",
    "price_use2 = price_use2.set_index('date')\n",
    "price_use2.columns = ['stock_id','3MA close']\n",
    "price_new = pd.merge(price_use,price_use2, 'right', on = ['date', 'stock_id'])\n",
    "price_new"
   ]
  },
  {
   "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>close</th>\n",
       "      <th>3MA close</th>\n",
       "      <th>return</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-08-15 00:00:00+00:00</th>\n",
       "      <td>P1507A122</td>\n",
       "      <td>2.2900</td>\n",
       "      <td>2.2900</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-04-12 00:00:00+00:00</th>\n",
       "      <td>04650Q107</td>\n",
       "      <td>0.3500</td>\n",
       "      <td>0.5722</td>\n",
       "      <td>0.634857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-09 00:00:00+00:00</th>\n",
       "      <td>67090W208</td>\n",
       "      <td>0.2300</td>\n",
       "      <td>0.3909</td>\n",
       "      <td>0.699565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-09-08 00:00:00+00:00</th>\n",
       "      <td>02153X108</td>\n",
       "      <td>100.0000</td>\n",
       "      <td>73.0500</td>\n",
       "      <td>-0.269500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-06-06 00:00:00+00:00</th>\n",
       "      <td>641266101</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.0001</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-01 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1759</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-08 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2347</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-15 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2310</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-22 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.2148</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-29 00:00:00+00:00</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.1577</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14704797 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            stock_id     close  3MA close    return\n",
       "date                                                               \n",
       "2018-08-15 00:00:00+00:00  P1507A122    2.2900     2.2900  0.000000\n",
       "2017-04-12 00:00:00+00:00  04650Q107    0.3500     0.5722  0.634857\n",
       "2018-02-09 00:00:00+00:00  67090W208    0.2300     0.3909  0.699565\n",
       "2017-09-08 00:00:00+00:00  02153X108  100.0000    73.0500 -0.269500\n",
       "2017-06-06 00:00:00+00:00  641266101    0.0001     0.0001  0.000000\n",
       "...                              ...       ...        ...       ...\n",
       "2020-02-01 00:00:00+00:00  98979F107       NaN     0.1759       NaN\n",
       "2020-02-08 00:00:00+00:00  98979F107       NaN     0.2347       NaN\n",
       "2020-02-15 00:00:00+00:00  98979F107       NaN     0.2310       NaN\n",
       "2020-02-22 00:00:00+00:00  98979F107       NaN     0.2148       NaN\n",
       "2020-02-29 00:00:00+00:00  98979F107       NaN     0.1577       NaN\n",
       "\n",
       "[14704797 rows x 4 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_new['return'] = (price_new['3MA close']-price_new['close'])/(price_new['close'])\n",
    "price_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "price_new = price_new[['stock_id','return']]\n",
    "price_new = price_new.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>000144899</th>\n",
       "      <th>000303107</th>\n",
       "      <th>000304105</th>\n",
       "      <th>000307108</th>\n",
       "      <th>000360206</th>\n",
       "      <th>000361105</th>\n",
       "      <th>000375204</th>\n",
       "      <th>00037T103</th>\n",
       "      <th>...</th>\n",
       "      <th>Y8897Y701</th>\n",
       "      <th>Y8897Y800</th>\n",
       "      <th>Y8904F141</th>\n",
       "      <th>Y8904F158</th>\n",
       "      <th>Y89412103</th>\n",
       "      <th>Y9384M101</th>\n",
       "      <th>Y95308105</th>\n",
       "      <th>Y9723Q102</th>\n",
       "      <th>Y9891F102</th>\n",
       "      <th>Y989DF109</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>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>0.231360</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.145833</td>\n",
       "      <td>-0.90</td>\n",
       "      <td>0.320170</td>\n",
       "      <td>0.103627</td>\n",
       "      <td>0.029985</td>\n",
       "      <td>0.028571</td>\n",
       "      <td>0.092105</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.542734</td>\n",
       "      <td>-0.542734</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.116279</td>\n",
       "      <td>0.148437</td>\n",
       "      <td>0.135135</td>\n",
       "      <td>0.034351</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-04 00:00:00+00:00</th>\n",
       "      <td>0.231360</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>0.145833</td>\n",
       "      <td>-0.90</td>\n",
       "      <td>0.281108</td>\n",
       "      <td>0.084525</td>\n",
       "      <td>0.019062</td>\n",
       "      <td>0.023026</td>\n",
       "      <td>0.097801</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.548117</td>\n",
       "      <td>-0.548117</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.116279</td>\n",
       "      <td>0.148437</td>\n",
       "      <td>0.108187</td>\n",
       "      <td>-0.026978</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-05 00:00:00+00:00</th>\n",
       "      <td>0.231360</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>0.145833</td>\n",
       "      <td>-0.90</td>\n",
       "      <td>0.234770</td>\n",
       "      <td>0.086207</td>\n",
       "      <td>0.033233</td>\n",
       "      <td>0.038116</td>\n",
       "      <td>0.070037</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.648148</td>\n",
       "      <td>-0.648148</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.116279</td>\n",
       "      <td>0.148437</td>\n",
       "      <td>0.107246</td>\n",
       "      <td>-0.018416</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-06 00:00:00+00:00</th>\n",
       "      <td>0.124012</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>0.145833</td>\n",
       "      <td>-0.90</td>\n",
       "      <td>0.210570</td>\n",
       "      <td>0.118037</td>\n",
       "      <td>0.051988</td>\n",
       "      <td>0.036212</td>\n",
       "      <td>0.079167</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.734694</td>\n",
       "      <td>-0.734694</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.116279</td>\n",
       "      <td>0.148437</td>\n",
       "      <td>0.100865</td>\n",
       "      <td>-0.044037</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-10 00:00:00+00:00</th>\n",
       "      <td>0.130354</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>0.145833</td>\n",
       "      <td>-0.90</td>\n",
       "      <td>0.218636</td>\n",
       "      <td>0.042929</td>\n",
       "      <td>0.029940</td>\n",
       "      <td>0.046202</td>\n",
       "      <td>0.044796</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.771930</td>\n",
       "      <td>-0.771930</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.116279</td>\n",
       "      <td>0.148437</td>\n",
       "      <td>0.110787</td>\n",
       "      <td>-0.032550</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</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-20 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>-0.218663</td>\n",
       "      <td>0.060000</td>\n",
       "      <td>-0.109046</td>\n",
       "      <td>-0.603217</td>\n",
       "      <td>-0.220576</td>\n",
       "      <td>0.269841</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>0.149177</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.086420</td>\n",
       "      <td>0.000000</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>-0.99</td>\n",
       "      <td>-0.256267</td>\n",
       "      <td>0.227273</td>\n",
       "      <td>-0.062540</td>\n",
       "      <td>-0.607697</td>\n",
       "      <td>-0.227178</td>\n",
       "      <td>-0.523810</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>0.269966</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.086420</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-02-26 00:00:00+00:00</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.99</td>\n",
       "      <td>-0.260328</td>\n",
       "      <td>0.295455</td>\n",
       "      <td>0.031250</td>\n",
       "      <td>-0.503992</td>\n",
       "      <td>-0.133809</td>\n",
       "      <td>0.269841</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>0.263282</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.048843</td>\n",
       "      <td>0.000000</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>-0.99</td>\n",
       "      <td>-0.237168</td>\n",
       "      <td>0.454545</td>\n",
       "      <td>-0.045320</td>\n",
       "      <td>-0.405035</td>\n",
       "      <td>-0.108884</td>\n",
       "      <td>-0.576720</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>0.326115</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.139781</td>\n",
       "      <td>0.000000</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>-0.99</td>\n",
       "      <td>-0.231946</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>-0.014361</td>\n",
       "      <td>-0.399711</td>\n",
       "      <td>-0.090573</td>\n",
       "      <td>-0.576720</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>0.215640</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.139781</td>\n",
       "      <td>0.542222</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>638 rows × 20886 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "stock_id                    34958R19   46629U18  000144899  000303107  \\\n",
       "date                                                                    \n",
       "2017-01-03 00:00:00+00:00   0.231360       0.00   0.145833      -0.90   \n",
       "2017-01-04 00:00:00+00:00   0.231360      -0.99   0.145833      -0.90   \n",
       "2017-01-05 00:00:00+00:00   0.231360      -0.99   0.145833      -0.90   \n",
       "2017-01-06 00:00:00+00:00   0.124012      -0.99   0.145833      -0.90   \n",
       "2017-01-10 00:00:00+00:00   0.130354      -0.99   0.145833      -0.90   \n",
       "...                              ...        ...        ...        ...   \n",
       "2020-02-20 00:00:00+00:00        NaN        NaN        NaN      -0.99   \n",
       "2020-02-21 00:00:00+00:00        NaN        NaN        NaN      -0.99   \n",
       "2020-02-26 00:00:00+00:00        NaN        NaN        NaN      -0.99   \n",
       "2020-02-27 00:00:00+00:00        NaN        NaN        NaN      -0.99   \n",
       "2020-02-28 00:00:00+00:00        NaN        NaN        NaN      -0.99   \n",
       "\n",
       "stock_id                   000304105  000307108  000360206  000361105  \\\n",
       "date                                                                    \n",
       "2017-01-03 00:00:00+00:00   0.320170   0.103627   0.029985   0.028571   \n",
       "2017-01-04 00:00:00+00:00   0.281108   0.084525   0.019062   0.023026   \n",
       "2017-01-05 00:00:00+00:00   0.234770   0.086207   0.033233   0.038116   \n",
       "2017-01-06 00:00:00+00:00   0.210570   0.118037   0.051988   0.036212   \n",
       "2017-01-10 00:00:00+00:00   0.218636   0.042929   0.029940   0.046202   \n",
       "...                              ...        ...        ...        ...   \n",
       "2020-02-20 00:00:00+00:00  -0.218663   0.060000  -0.109046  -0.603217   \n",
       "2020-02-21 00:00:00+00:00  -0.256267   0.227273  -0.062540  -0.607697   \n",
       "2020-02-26 00:00:00+00:00  -0.260328   0.295455   0.031250  -0.503992   \n",
       "2020-02-27 00:00:00+00:00  -0.237168   0.454545  -0.045320  -0.405035   \n",
       "2020-02-28 00:00:00+00:00  -0.231946   0.066667  -0.014361  -0.399711   \n",
       "\n",
       "stock_id                   000375204  00037T103  ...  Y8897Y701  Y8897Y800  \\\n",
       "date                                             ...                         \n",
       "2017-01-03 00:00:00+00:00   0.092105   0.000000  ...  -0.542734  -0.542734   \n",
       "2017-01-04 00:00:00+00:00   0.097801   0.000000  ...  -0.548117  -0.548117   \n",
       "2017-01-05 00:00:00+00:00   0.070037   0.000000  ...  -0.648148  -0.648148   \n",
       "2017-01-06 00:00:00+00:00   0.079167   0.000000  ...  -0.734694  -0.734694   \n",
       "2017-01-10 00:00:00+00:00   0.044796   0.000000  ...  -0.771930  -0.771930   \n",
       "...                              ...        ...  ...        ...        ...   \n",
       "2020-02-20 00:00:00+00:00  -0.220576   0.269841  ...        NaN        NaN   \n",
       "2020-02-21 00:00:00+00:00  -0.227178  -0.523810  ...        NaN        NaN   \n",
       "2020-02-26 00:00:00+00:00  -0.133809   0.269841  ...        NaN        NaN   \n",
       "2020-02-27 00:00:00+00:00  -0.108884  -0.576720  ...        NaN        NaN   \n",
       "2020-02-28 00:00:00+00:00  -0.090573  -0.576720  ...        NaN        NaN   \n",
       "\n",
       "stock_id                   Y8904F141  Y8904F158  Y89412103  Y9384M101  \\\n",
       "date                                                                    \n",
       "2017-01-03 00:00:00+00:00        0.0  -0.116279   0.148437   0.135135   \n",
       "2017-01-04 00:00:00+00:00        0.0  -0.116279   0.148437   0.108187   \n",
       "2017-01-05 00:00:00+00:00        0.0  -0.116279   0.148437   0.107246   \n",
       "2017-01-06 00:00:00+00:00        0.0  -0.116279   0.148437   0.100865   \n",
       "2017-01-10 00:00:00+00:00        0.0  -0.116279   0.148437   0.110787   \n",
       "...                              ...        ...        ...        ...   \n",
       "2020-02-20 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2020-02-21 00:00:00+00:00        NaN        NaN        NaN        NaN   \n",
       "2020-02-26 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",
       "\n",
       "stock_id                   Y95308105  Y9723Q102  Y9891F102  Y989DF109  \n",
       "date                                                                   \n",
       "2017-01-03 00:00:00+00:00   0.034351        0.0   0.000000        NaN  \n",
       "2017-01-04 00:00:00+00:00  -0.026978        0.0   0.000000        NaN  \n",
       "2017-01-05 00:00:00+00:00  -0.018416        0.0   0.000000        NaN  \n",
       "2017-01-06 00:00:00+00:00  -0.044037        0.0   0.000000        NaN  \n",
       "2017-01-10 00:00:00+00:00  -0.032550        0.0   0.000000        NaN  \n",
       "...                              ...        ...        ...        ...  \n",
       "2020-02-20 00:00:00+00:00   0.149177        NaN  -0.086420   0.000000  \n",
       "2020-02-21 00:00:00+00:00   0.269966        NaN  -0.086420   0.000000  \n",
       "2020-02-26 00:00:00+00:00   0.263282        NaN  -0.048843   0.000000  \n",
       "2020-02-27 00:00:00+00:00   0.326115        NaN  -0.139781   0.000000  \n",
       "2020-02-28 00:00:00+00:00   0.215640        NaN  -0.139781   0.542222  \n",
       "\n",
       "[638 rows x 20886 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_table = pd.pivot_table(price_new, values = 'return',index = ['date'],columns = 'stock_id',aggfunc=np.mean)\n",
    "price_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stock_id\n",
       " 34958R19    -0.186320\n",
       " 46629U18    21.396470\n",
       "000144899     0.175823\n",
       "000303107    44.165078\n",
       "000304105    -0.017795\n",
       "               ...    \n",
       "Y9384M101     0.017580\n",
       "Y95308105    -0.013662\n",
       "Y9723Q102     0.000000\n",
       "Y9891F102    -0.020187\n",
       "Y989DF109    -0.127794\n",
       "Length: 20886, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_mean = price_table.mean()\n",
    "price_mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "stock_id\n",
       "09606H101        -0.994810\n",
       "39013L809        -0.994444\n",
       "63000Y103        -0.992734\n",
       "Y2109Q606        -0.962507\n",
       "Y2109Q408        -0.956388\n",
       "                 ...      \n",
       "302669403     66969.475203\n",
       "89156F101     75521.897263\n",
       "80402L109     82499.790840\n",
       "212343206    128786.369853\n",
       "21925H100    208864.694520\n",
       "Length: 20886, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_mean = price_mean.sort_values(ascending = True)\n",
    "price_mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "MEAN = price_mean.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "high_perf = price_mean[price_mean >= MEAN*1.2]\n",
    "low_perf = price_mean[price_mean <= MEAN*0.8]\n",
    "med_perf = price_mean[price_mean >= MEAN*0.8]\n",
    "med_perf = med_perf[med_perf <= MEAN*1.2]\n",
    "# med_perf\n",
    "# high_perf\n",
    "# low_perf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "185.84144977832347"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_vol = price_table.std()\n",
    "MEAN_vol = price_vol.mean()\n",
    "price_vol = price_vol.sort_values(ascending = True)\n",
    "high_vol = price_vol[price_vol >= MEAN_vol*1.2]\n",
    "low_vol = price_vol[price_vol <= MEAN_vol*0.8]\n",
    "med_vol = price_vol[price_vol >= MEAN_vol*0.8]\n",
    "med_vol = med_vol[med_vol <=MEAN_vol*1.2]\n",
    "MEAN_vol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>pSP_CUSIP</th>\n",
       "      <th>pSP_EXCHANGE</th>\n",
       "      <th>pSP_TICKER</th>\n",
       "      <th>pSP_DATE</th>\n",
       "      <th>pSP_VOLUME</th>\n",
       "      <th>pSP_OPEN</th>\n",
       "      <th>pSP_HIGH</th>\n",
       "      <th>pSP_LOW</th>\n",
       "      <th>pSP_CLOSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-02</td>\n",
       "      <td>2808000</td>\n",
       "      <td>13.1250</td>\n",
       "      <td>13.6875</td>\n",
       "      <td>13.0625</td>\n",
       "      <td>13.5938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-03</td>\n",
       "      <td>5008800</td>\n",
       "      <td>13.5312</td>\n",
       "      <td>13.8438</td>\n",
       "      <td>13.5000</td>\n",
       "      <td>13.8125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-04</td>\n",
       "      <td>4276000</td>\n",
       "      <td>13.7188</td>\n",
       "      <td>13.9375</td>\n",
       "      <td>13.5938</td>\n",
       "      <td>13.9375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-05</td>\n",
       "      <td>2618400</td>\n",
       "      <td>13.8750</td>\n",
       "      <td>14.1250</td>\n",
       "      <td>13.8125</td>\n",
       "      <td>13.8438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>013817101</td>\n",
       "      <td>N</td>\n",
       "      <td>AA</td>\n",
       "      <td>1996-01-08</td>\n",
       "      <td>3471200</td>\n",
       "      <td>13.7812</td>\n",
       "      <td>13.8750</td>\n",
       "      <td>12.8125</td>\n",
       "      <td>13.0938</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>105864349</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-22</td>\n",
       "      <td>14608310</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.2205</td>\n",
       "      <td>0.2000</td>\n",
       "      <td>0.2148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105864350</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>13622890</td>\n",
       "      <td>0.2101</td>\n",
       "      <td>0.2200</td>\n",
       "      <td>0.2050</td>\n",
       "      <td>0.2108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105864351</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-27</td>\n",
       "      <td>220125900</td>\n",
       "      <td>0.1460</td>\n",
       "      <td>0.1652</td>\n",
       "      <td>0.1438</td>\n",
       "      <td>0.1632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105864352</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-28</td>\n",
       "      <td>65927280</td>\n",
       "      <td>0.1705</td>\n",
       "      <td>0.1730</td>\n",
       "      <td>0.1550</td>\n",
       "      <td>0.1594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105864353</th>\n",
       "      <td>98979F107</td>\n",
       "      <td>A</td>\n",
       "      <td>ZOM</td>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>33669380</td>\n",
       "      <td>0.1600</td>\n",
       "      <td>0.1620</td>\n",
       "      <td>0.1540</td>\n",
       "      <td>0.1577</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105864354 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           pSP_CUSIP pSP_EXCHANGE pSP_TICKER    pSP_DATE  pSP_VOLUME  \\\n",
       "0          013817101       N          AA      1996-01-02     2808000   \n",
       "1          013817101       N          AA      1996-01-03     5008800   \n",
       "2          013817101       N          AA      1996-01-04     4276000   \n",
       "3          013817101       N          AA      1996-01-05     2618400   \n",
       "4          013817101       N          AA      1996-01-08     3471200   \n",
       "...              ...          ...        ...         ...         ...   \n",
       "105864349  98979F107            A        ZOM  2020-05-22    14608310   \n",
       "105864350  98979F107            A        ZOM  2020-05-26    13622890   \n",
       "105864351  98979F107            A        ZOM  2020-05-27   220125900   \n",
       "105864352  98979F107            A        ZOM  2020-05-28    65927280   \n",
       "105864353  98979F107            A        ZOM  2020-05-29    33669380   \n",
       "\n",
       "           pSP_OPEN  pSP_HIGH  pSP_LOW  pSP_CLOSE  \n",
       "0           13.1250   13.6875  13.0625    13.5938  \n",
       "1           13.5312   13.8438  13.5000    13.8125  \n",
       "2           13.7188   13.9375  13.5938    13.9375  \n",
       "3           13.8750   14.1250  13.8125    13.8438  \n",
       "4           13.7812   13.8750  12.8125    13.0938  \n",
       "...             ...       ...      ...        ...  \n",
       "105864349    0.2200    0.2205   0.2000     0.2148  \n",
       "105864350    0.2101    0.2200   0.2050     0.2108  \n",
       "105864351    0.1460    0.1652   0.1438     0.1632  \n",
       "105864352    0.1705    0.1730   0.1550     0.1594  \n",
       "105864353    0.1600    0.1620   0.1540     0.1577  \n",
       "\n",
       "[105864354 rows x 9 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prices_copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>pSP_CUSIP</th>\n",
       "      <th>pSP_EXCHANGE</th>\n",
       "      <th>pSP_TICKER</th>\n",
       "      <th>pSP_DATE</th>\n",
       "      <th>pSP_VOLUME</th>\n",
       "      <th>pSP_OPEN</th>\n",
       "      <th>pSP_HIGH</th>\n",
       "      <th>pSP_LOW</th>\n",
       "      <th>pSP_CLOSE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>157720</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2014-06-02</td>\n",
       "      <td>92179668</td>\n",
       "      <td>90.5657</td>\n",
       "      <td>90.6900</td>\n",
       "      <td>88.9285</td>\n",
       "      <td>89.8071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193070</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2014-05-30</td>\n",
       "      <td>140860202</td>\n",
       "      <td>91.1400</td>\n",
       "      <td>92.0243</td>\n",
       "      <td>89.8428</td>\n",
       "      <td>90.4285</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>521057</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2014-05-29</td>\n",
       "      <td>93737728</td>\n",
       "      <td>89.6928</td>\n",
       "      <td>90.9814</td>\n",
       "      <td>89.6814</td>\n",
       "      <td>90.7685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1653578</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2014-05-28</td>\n",
       "      <td>78793704</td>\n",
       "      <td>89.4314</td>\n",
       "      <td>89.9757</td>\n",
       "      <td>89.1114</td>\n",
       "      <td>89.1443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1728329</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2014-05-27</td>\n",
       "      <td>86840486</td>\n",
       "      <td>87.9828</td>\n",
       "      <td>89.4085</td>\n",
       "      <td>87.9471</td>\n",
       "      <td>89.3757</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>105141867</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-05-22</td>\n",
       "      <td>20450750</td>\n",
       "      <td>315.7700</td>\n",
       "      <td>319.2300</td>\n",
       "      <td>315.3500</td>\n",
       "      <td>318.8900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105141868</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-05-26</td>\n",
       "      <td>31380450</td>\n",
       "      <td>323.5000</td>\n",
       "      <td>324.2400</td>\n",
       "      <td>316.5000</td>\n",
       "      <td>316.7300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105141869</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-05-27</td>\n",
       "      <td>28236270</td>\n",
       "      <td>316.1400</td>\n",
       "      <td>318.7100</td>\n",
       "      <td>313.0900</td>\n",
       "      <td>318.1100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105141870</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-05-28</td>\n",
       "      <td>33449100</td>\n",
       "      <td>316.7700</td>\n",
       "      <td>323.4400</td>\n",
       "      <td>315.6300</td>\n",
       "      <td>318.2500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105141871</th>\n",
       "      <td>037833100</td>\n",
       "      <td>O</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>2020-05-29</td>\n",
       "      <td>38399530</td>\n",
       "      <td>319.2500</td>\n",
       "      <td>321.1500</td>\n",
       "      <td>316.4700</td>\n",
       "      <td>317.9400</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6145 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           pSP_CUSIP pSP_EXCHANGE pSP_TICKER    pSP_DATE  pSP_VOLUME  \\\n",
       "157720     037833100            O       AAPL  2014-06-02    92179668   \n",
       "193070     037833100            O       AAPL  2014-05-30   140860202   \n",
       "521057     037833100            O       AAPL  2014-05-29    93737728   \n",
       "1653578    037833100            O       AAPL  2014-05-28    78793704   \n",
       "1728329    037833100            O       AAPL  2014-05-27    86840486   \n",
       "...              ...          ...        ...         ...         ...   \n",
       "105141867  037833100            O       AAPL  2020-05-22    20450750   \n",
       "105141868  037833100            O       AAPL  2020-05-26    31380450   \n",
       "105141869  037833100            O       AAPL  2020-05-27    28236270   \n",
       "105141870  037833100            O       AAPL  2020-05-28    33449100   \n",
       "105141871  037833100            O       AAPL  2020-05-29    38399530   \n",
       "\n",
       "           pSP_OPEN  pSP_HIGH   pSP_LOW  pSP_CLOSE  \n",
       "157720      90.5657   90.6900   88.9285    89.8071  \n",
       "193070      91.1400   92.0243   89.8428    90.4285  \n",
       "521057      89.6928   90.9814   89.6814    90.7685  \n",
       "1653578     89.4314   89.9757   89.1114    89.1443  \n",
       "1728329     87.9828   89.4085   87.9471    89.3757  \n",
       "...             ...       ...       ...        ...  \n",
       "105141867  315.7700  319.2300  315.3500   318.8900  \n",
       "105141868  323.5000  324.2400  316.5000   316.7300  \n",
       "105141869  316.1400  318.7100  313.0900   318.1100  \n",
       "105141870  316.7700  323.4400  315.6300   318.2500  \n",
       "105141871  319.2500  321.1500  316.4700   317.9400  \n",
       "\n",
       "[6145 rows x 9 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prices_copy[prices_copy['pSP_TICKER'] == 'AAPL']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>record_id</th>\n",
       "      <th>iCIK</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>period_end</th>\n",
       "      <th>iAMEND</th>\n",
       "      <th>iRESTATEMENT</th>\n",
       "      <th>iTYPE</th>\n",
       "      <th>iQTY</th>\n",
       "      <th>iMARKET_VALUE</th>\n",
       "      <th>iLONG_FRACTION</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>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234727</td>\n",
       "      <td>866780</td>\n",
       "      <td>74347R693</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23118.0</td>\n",
       "      <td>2242000.0</td>\n",
       "      <td>0.006441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234728</td>\n",
       "      <td>866780</td>\n",
       "      <td>494368103</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>26134.0</td>\n",
       "      <td>2982000.0</td>\n",
       "      <td>0.008567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234729</td>\n",
       "      <td>866780</td>\n",
       "      <td>458140100</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15529.0</td>\n",
       "      <td>563000.0</td>\n",
       "      <td>0.001617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234730</td>\n",
       "      <td>866780</td>\n",
       "      <td>74347R206</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>61423.0</td>\n",
       "      <td>5290000.0</td>\n",
       "      <td>0.015197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234731</td>\n",
       "      <td>866780</td>\n",
       "      <td>464287523</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29548.0</td>\n",
       "      <td>3626000.0</td>\n",
       "      <td>0.010417</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>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561590</td>\n",
       "      <td>1730479</td>\n",
       "      <td>039483102</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>0.000086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561591</td>\n",
       "      <td>1730479</td>\n",
       "      <td>88160R101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>0.000102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561592</td>\n",
       "      <td>1730479</td>\n",
       "      <td>921937793</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>0.000196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561593</td>\n",
       "      <td>1730479</td>\n",
       "      <td>863667101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1030.0</td>\n",
       "      <td>171000.0</td>\n",
       "      <td>0.001341</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561594</td>\n",
       "      <td>1730479</td>\n",
       "      <td>232806109</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2000.0</td>\n",
       "      <td>0.000016</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>15320871 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           record_id     iCIK   stock_id  period_end  iAMEND  \\\n",
       "date                                                                           \n",
       "2017-01-03 00:00:00+00:00   59234727   866780  74347R693  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234728   866780  494368103  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234729   866780  458140100  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234730   866780  74347R206  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234731   866780  464287523  2016-12-31       0   \n",
       "...                              ...      ...        ...         ...     ...   \n",
       "2020-04-23 00:00:00+00:00   74561590  1730479  039483102  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561591  1730479  88160R101  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561592  1730479  921937793  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561593  1730479  863667101  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561594  1730479  232806109  2020-03-31       0   \n",
       "\n",
       "                           iRESTATEMENT  iTYPE     iQTY  iMARKET_VALUE  \\\n",
       "date                                                                     \n",
       "2017-01-03 00:00:00+00:00             0      0  23118.0      2242000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  26134.0      2982000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  15529.0       563000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  61423.0      5290000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  29548.0      3626000.0   \n",
       "...                                 ...    ...      ...            ...   \n",
       "2020-04-23 00:00:00+00:00             0      0    300.0        11000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0     25.0        13000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0    230.0        25000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0   1030.0       171000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0    100.0         2000.0   \n",
       "\n",
       "                           iLONG_FRACTION  \n",
       "date                                       \n",
       "2017-01-03 00:00:00+00:00        0.006441  \n",
       "2017-01-03 00:00:00+00:00        0.008567  \n",
       "2017-01-03 00:00:00+00:00        0.001617  \n",
       "2017-01-03 00:00:00+00:00        0.015197  \n",
       "2017-01-03 00:00:00+00:00        0.010417  \n",
       "...                                   ...  \n",
       "2020-04-23 00:00:00+00:00        0.000086  \n",
       "2020-04-23 00:00:00+00:00        0.000102  \n",
       "2020-04-23 00:00:00+00:00        0.000196  \n",
       "2020-04-23 00:00:00+00:00        0.001341  \n",
       "2020-04-23 00:00:00+00:00        0.000016  \n",
       "\n",
       "[15320871 rows x 10 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>record_id</th>\n",
       "      <th>iCIK</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>period_end</th>\n",
       "      <th>iAMEND</th>\n",
       "      <th>iRESTATEMENT</th>\n",
       "      <th>iTYPE</th>\n",
       "      <th>iQTY</th>\n",
       "      <th>iMARKET_VALUE</th>\n",
       "      <th>iLONG_FRACTION</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>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234727</td>\n",
       "      <td>866780</td>\n",
       "      <td>74347R693</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>23118.0</td>\n",
       "      <td>2242000.0</td>\n",
       "      <td>0.006441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234728</td>\n",
       "      <td>866780</td>\n",
       "      <td>494368103</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>26134.0</td>\n",
       "      <td>2982000.0</td>\n",
       "      <td>0.008567</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234729</td>\n",
       "      <td>866780</td>\n",
       "      <td>458140100</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15529.0</td>\n",
       "      <td>563000.0</td>\n",
       "      <td>0.001617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234730</td>\n",
       "      <td>866780</td>\n",
       "      <td>74347R206</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>61423.0</td>\n",
       "      <td>5290000.0</td>\n",
       "      <td>0.015197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-01-03 00:00:00+00:00</th>\n",
       "      <td>59234731</td>\n",
       "      <td>866780</td>\n",
       "      <td>464287523</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>29548.0</td>\n",
       "      <td>3626000.0</td>\n",
       "      <td>0.010417</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>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561589</td>\n",
       "      <td>1730479</td>\n",
       "      <td>808513105</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3405.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>0.000894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561590</td>\n",
       "      <td>1730479</td>\n",
       "      <td>039483102</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>300.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>0.000086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561591</td>\n",
       "      <td>1730479</td>\n",
       "      <td>88160R101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>13000.0</td>\n",
       "      <td>0.000102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561592</td>\n",
       "      <td>1730479</td>\n",
       "      <td>921937793</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>0.000196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020-04-23 00:00:00+00:00</th>\n",
       "      <td>74561593</td>\n",
       "      <td>1730479</td>\n",
       "      <td>863667101</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1030.0</td>\n",
       "      <td>171000.0</td>\n",
       "      <td>0.001341</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>14385622 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                           record_id     iCIK   stock_id  period_end  iAMEND  \\\n",
       "date                                                                           \n",
       "2017-01-03 00:00:00+00:00   59234727   866780  74347R693  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234728   866780  494368103  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234729   866780  458140100  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234730   866780  74347R206  2016-12-31       0   \n",
       "2017-01-03 00:00:00+00:00   59234731   866780  464287523  2016-12-31       0   \n",
       "...                              ...      ...        ...         ...     ...   \n",
       "2020-04-23 00:00:00+00:00   74561589  1730479  808513105  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561590  1730479  039483102  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561591  1730479  88160R101  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561592  1730479  921937793  2020-03-31       0   \n",
       "2020-04-23 00:00:00+00:00   74561593  1730479  863667101  2020-03-31       0   \n",
       "\n",
       "                           iRESTATEMENT  iTYPE     iQTY  iMARKET_VALUE  \\\n",
       "date                                                                     \n",
       "2017-01-03 00:00:00+00:00             0      0  23118.0      2242000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  26134.0      2982000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  15529.0       563000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  61423.0      5290000.0   \n",
       "2017-01-03 00:00:00+00:00             0      0  29548.0      3626000.0   \n",
       "...                                 ...    ...      ...            ...   \n",
       "2020-04-23 00:00:00+00:00             0      0   3405.0       114000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0    300.0        11000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0     25.0        13000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0    230.0        25000.0   \n",
       "2020-04-23 00:00:00+00:00             0      0   1030.0       171000.0   \n",
       "\n",
       "                           iLONG_FRACTION  \n",
       "date                                       \n",
       "2017-01-03 00:00:00+00:00        0.006441  \n",
       "2017-01-03 00:00:00+00:00        0.008567  \n",
       "2017-01-03 00:00:00+00:00        0.001617  \n",
       "2017-01-03 00:00:00+00:00        0.015197  \n",
       "2017-01-03 00:00:00+00:00        0.010417  \n",
       "...                                   ...  \n",
       "2020-04-23 00:00:00+00:00        0.000894  \n",
       "2020-04-23 00:00:00+00:00        0.000086  \n",
       "2020-04-23 00:00:00+00:00        0.000102  \n",
       "2020-04-23 00:00:00+00:00        0.000196  \n",
       "2020-04-23 00:00:00+00:00        0.001341  \n",
       "\n",
       "[14385622 rows x 10 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "factors2 = factors[factors['iMARKET_VALUE'] >= 10000]\n",
    "factors2"
   ]
  },
  {
   "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
}