Low Vol v2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(datetime.datetime(1963, 7, 31, 0, 0), datetime.datetime(2018, 6, 29, 0, 0))"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datetime.datetime(1963,7,31), datetime.datetime(2018, 6, 29, 0, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv('div_filled.csv', low_memory=False).drop(columns=['datetime date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "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>PERMNO</th>\n",
       "      <th>date</th>\n",
       "      <th>DIVAMT</th>\n",
       "      <th>PRC</th>\n",
       "      <th>SHROUT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1985/12/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/01/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.37500</td>\n",
       "      <td>3680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/02/28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.25000</td>\n",
       "      <td>3680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/03/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.43750</td>\n",
       "      <td>3680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/04/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/05/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.10938</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/06/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.09375</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/07/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.84375</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/08/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.09375</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/09/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.03125</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/10/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.78125</td>\n",
       "      <td>3843.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/11/28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.82813</td>\n",
       "      <td>3843.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986/12/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.51563</td>\n",
       "      <td>3843.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987/01/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987/02/27</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987/03/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.25000</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987/04/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.23438</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987/05/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.21875</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987/06/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10001</td>\n",
       "      <td>1985/12/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/01/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/02/28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.25000</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/03/31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/04/30</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/05/30</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/06/30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/07/31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.06250</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/08/29</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.50000</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/09/30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>991.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986/10/31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.62500</td>\n",
       "      <td>991.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098016</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/01/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.20000</td>\n",
       "      <td>132056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098017</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/02/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.92999</td>\n",
       "      <td>132056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098018</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/03/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>229.77000</td>\n",
       "      <td>133858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098019</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/04/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>240.75999</td>\n",
       "      <td>133945.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098020</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/05/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>223.23000</td>\n",
       "      <td>147543.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098021</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/06/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.28000</td>\n",
       "      <td>148015.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098022</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/07/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>234.78999</td>\n",
       "      <td>148693.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098023</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/08/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.00999</td>\n",
       "      <td>149239.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098024</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/09/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>204.03000</td>\n",
       "      <td>149825.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098025</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/10/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>197.73000</td>\n",
       "      <td>149891.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098026</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/11/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>189.39999</td>\n",
       "      <td>149964.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098027</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016/12/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>213.69000</td>\n",
       "      <td>161561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098028</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/01/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>251.92999</td>\n",
       "      <td>161670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098029</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/02/28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>249.99001</td>\n",
       "      <td>161670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098030</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/03/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>278.29999</td>\n",
       "      <td>164164.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098031</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/04/28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>314.07001</td>\n",
       "      <td>164260.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098032</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/05/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.01001</td>\n",
       "      <td>164260.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098033</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/06/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>361.60999</td>\n",
       "      <td>166863.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098034</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/07/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>323.47000</td>\n",
       "      <td>166887.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098035</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/08/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>355.89999</td>\n",
       "      <td>166887.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098036</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/09/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.10001</td>\n",
       "      <td>168017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098037</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/10/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>331.53000</td>\n",
       "      <td>168067.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098038</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/11/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>308.85001</td>\n",
       "      <td>168067.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098039</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017/12/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>311.35001</td>\n",
       "      <td>168797.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098040</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018/01/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>354.31000</td>\n",
       "      <td>168797.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098041</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018/02/28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>343.06000</td>\n",
       "      <td>168920.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098042</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018/03/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>266.13000</td>\n",
       "      <td>169750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098043</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018/04/30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>293.89999</td>\n",
       "      <td>169794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098044</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018/05/31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>284.73001</td>\n",
       "      <td>169794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098045</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018/06/29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>342.95001</td>\n",
       "      <td>170516.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4098046 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         PERMNO        date    DIVAMT        PRC    SHROUT\n",
       "0         10000  1985/12/31  0.000000        NaN       NaN\n",
       "1         10000  1986/01/31  0.000000    4.37500    3680.0\n",
       "2         10000  1986/02/28  0.000000    3.25000    3680.0\n",
       "3         10000  1986/03/31  0.000000    4.43750    3680.0\n",
       "4         10000  1986/04/30  0.000000    4.00000    3793.0\n",
       "5         10000  1986/05/30  0.000000    3.10938    3793.0\n",
       "6         10000  1986/06/30  0.000000    3.09375    3793.0\n",
       "7         10000  1986/07/31  0.000000    2.84375    3793.0\n",
       "8         10000  1986/08/29  0.000000    1.09375    3793.0\n",
       "9         10000  1986/09/30  0.000000    1.03125    3793.0\n",
       "10        10000  1986/10/31  0.000000    0.78125    3843.0\n",
       "11        10000  1986/11/28  0.000000    0.82813    3843.0\n",
       "12        10000  1986/12/31  0.000000    0.51563    3843.0\n",
       "13        10000  1987/01/30  0.000000    0.40625    3893.0\n",
       "14        10000  1987/02/27  0.000000    0.40625    3893.0\n",
       "15        10000  1987/03/31  0.000000    0.25000    3893.0\n",
       "16        10000  1987/04/30  0.000000    0.23438    3893.0\n",
       "17        10000  1987/05/29  0.000000    0.21875    3893.0\n",
       "18        10000  1987/06/30  0.000000        NaN    3893.0\n",
       "19        10001  1985/12/31  0.000000        NaN       NaN\n",
       "20        10001  1986/01/31  0.000000    6.12500     985.0\n",
       "21        10001  1986/02/28  0.000000    6.25000     985.0\n",
       "22        10001  1986/03/31  0.031667    6.31250     985.0\n",
       "23        10001  1986/04/30  0.031667    6.37500     985.0\n",
       "24        10001  1986/05/30  0.031667    6.31250     985.0\n",
       "25        10001  1986/06/30  0.035000    6.12500     985.0\n",
       "26        10001  1986/07/31  0.035000    6.06250     985.0\n",
       "27        10001  1986/08/29  0.035000    6.50000     985.0\n",
       "28        10001  1986/09/30  0.035000    6.37500     991.0\n",
       "29        10001  1986/10/31  0.035000    6.62500     991.0\n",
       "...         ...         ...       ...        ...       ...\n",
       "4098016   93436  2016/01/29  0.000000  191.20000  132056.0\n",
       "4098017   93436  2016/02/29  0.000000  191.92999  132056.0\n",
       "4098018   93436  2016/03/31  0.000000  229.77000  133858.0\n",
       "4098019   93436  2016/04/29  0.000000  240.75999  133945.0\n",
       "4098020   93436  2016/05/31  0.000000  223.23000  147543.0\n",
       "4098021   93436  2016/06/30  0.000000  212.28000  148015.0\n",
       "4098022   93436  2016/07/29  0.000000  234.78999  148693.0\n",
       "4098023   93436  2016/08/31  0.000000  212.00999  149239.0\n",
       "4098024   93436  2016/09/30  0.000000  204.03000  149825.0\n",
       "4098025   93436  2016/10/31  0.000000  197.73000  149891.0\n",
       "4098026   93436  2016/11/30  0.000000  189.39999  149964.0\n",
       "4098027   93436  2016/12/30  0.000000  213.69000  161561.0\n",
       "4098028   93436  2017/01/31  0.000000  251.92999  161670.0\n",
       "4098029   93436  2017/02/28  0.000000  249.99001  161670.0\n",
       "4098030   93436  2017/03/31  0.000000  278.29999  164164.0\n",
       "4098031   93436  2017/04/28  0.000000  314.07001  164260.0\n",
       "4098032   93436  2017/05/31  0.000000  341.01001  164260.0\n",
       "4098033   93436  2017/06/30  0.000000  361.60999  166863.0\n",
       "4098034   93436  2017/07/31  0.000000  323.47000  166887.0\n",
       "4098035   93436  2017/08/31  0.000000  355.89999  166887.0\n",
       "4098036   93436  2017/09/29  0.000000  341.10001  168017.0\n",
       "4098037   93436  2017/10/31  0.000000  331.53000  168067.0\n",
       "4098038   93436  2017/11/30  0.000000  308.85001  168067.0\n",
       "4098039   93436  2017/12/29  0.000000  311.35001  168797.0\n",
       "4098040   93436  2018/01/31  0.000000  354.31000  168797.0\n",
       "4098041   93436  2018/02/28  0.000000  343.06000  168920.0\n",
       "4098042   93436  2018/03/29  0.000000  266.13000  169750.0\n",
       "4098043   93436  2018/04/30  0.000000  293.89999  169794.0\n",
       "4098044   93436  2018/05/31  0.000000  284.73001  169794.0\n",
       "4098045   93436  2018/06/29  0.000000  342.95001  170516.0\n",
       "\n",
       "[4098046 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter data, get when are the dates used\n",
    "head, mid, tail = [], [], []\n",
    "for i1 in range(1964, 2018):\n",
    "    for i2 in range(2,13):\n",
    "        mid.append(datetime.datetime(i1, i2, 1)-datetime.timedelta(days = 1))\n",
    "    mid.append(datetime.datetime(i1+1, 1, 1)-datetime.timedelta(days = 1))\n",
    "\n",
    "for i1 in range(8, 13):\n",
    "    head.append(datetime.datetime(1963, i1, 1)-datetime.timedelta(days = 1))\n",
    "head.append(datetime.datetime(1964, 1, 1)-datetime.timedelta(days = 1))\n",
    "    \n",
    "for i1 in range(2, 8):\n",
    "    tail.append(datetime.datetime(2018,i1,1)-datetime.timedelta(days = 1))\n",
    "\n",
    "dates_month = head+mid+tail # The index of the data file, in months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[datetime.datetime(1963, 7, 31, 0, 0),\n",
       " datetime.datetime(1963, 8, 31, 0, 0),\n",
       " datetime.datetime(1963, 9, 30, 0, 0),\n",
       " datetime.datetime(1963, 10, 31, 0, 0),\n",
       " datetime.datetime(1963, 11, 30, 0, 0),\n",
       " datetime.datetime(1963, 12, 31, 0, 0),\n",
       " datetime.datetime(1964, 1, 31, 0, 0),\n",
       " datetime.datetime(1964, 2, 29, 0, 0),\n",
       " datetime.datetime(1964, 3, 31, 0, 0),\n",
       " datetime.datetime(1964, 4, 30, 0, 0),\n",
       " datetime.datetime(1964, 5, 31, 0, 0),\n",
       " datetime.datetime(1964, 6, 30, 0, 0),\n",
       " datetime.datetime(1964, 7, 31, 0, 0),\n",
       " datetime.datetime(1964, 8, 31, 0, 0),\n",
       " datetime.datetime(1964, 9, 30, 0, 0),\n",
       " datetime.datetime(1964, 10, 31, 0, 0),\n",
       " datetime.datetime(1964, 11, 30, 0, 0),\n",
       " datetime.datetime(1964, 12, 31, 0, 0),\n",
       " datetime.datetime(1965, 1, 31, 0, 0),\n",
       " datetime.datetime(1965, 2, 28, 0, 0),\n",
       " datetime.datetime(1965, 3, 31, 0, 0),\n",
       " datetime.datetime(1965, 4, 30, 0, 0),\n",
       " datetime.datetime(1965, 5, 31, 0, 0),\n",
       " datetime.datetime(1965, 6, 30, 0, 0),\n",
       " datetime.datetime(1965, 7, 31, 0, 0),\n",
       " datetime.datetime(1965, 8, 31, 0, 0),\n",
       " datetime.datetime(1965, 9, 30, 0, 0),\n",
       " datetime.datetime(1965, 10, 31, 0, 0),\n",
       " datetime.datetime(1965, 11, 30, 0, 0),\n",
       " datetime.datetime(1965, 12, 31, 0, 0),\n",
       " datetime.datetime(1966, 1, 31, 0, 0),\n",
       " datetime.datetime(1966, 2, 28, 0, 0),\n",
       " datetime.datetime(1966, 3, 31, 0, 0),\n",
       " datetime.datetime(1966, 4, 30, 0, 0),\n",
       " datetime.datetime(1966, 5, 31, 0, 0),\n",
       " datetime.datetime(1966, 6, 30, 0, 0),\n",
       " datetime.datetime(1966, 7, 31, 0, 0),\n",
       " datetime.datetime(1966, 8, 31, 0, 0),\n",
       " datetime.datetime(1966, 9, 30, 0, 0),\n",
       " datetime.datetime(1966, 10, 31, 0, 0),\n",
       " datetime.datetime(1966, 11, 30, 0, 0),\n",
       " datetime.datetime(1966, 12, 31, 0, 0),\n",
       " datetime.datetime(1967, 1, 31, 0, 0),\n",
       " datetime.datetime(1967, 2, 28, 0, 0),\n",
       " datetime.datetime(1967, 3, 31, 0, 0),\n",
       " datetime.datetime(1967, 4, 30, 0, 0),\n",
       " datetime.datetime(1967, 5, 31, 0, 0),\n",
       " datetime.datetime(1967, 6, 30, 0, 0),\n",
       " datetime.datetime(1967, 7, 31, 0, 0),\n",
       " datetime.datetime(1967, 8, 31, 0, 0),\n",
       " datetime.datetime(1967, 9, 30, 0, 0),\n",
       " datetime.datetime(1967, 10, 31, 0, 0),\n",
       " datetime.datetime(1967, 11, 30, 0, 0),\n",
       " datetime.datetime(1967, 12, 31, 0, 0),\n",
       " datetime.datetime(1968, 1, 31, 0, 0),\n",
       " datetime.datetime(1968, 2, 29, 0, 0),\n",
       " datetime.datetime(1968, 3, 31, 0, 0),\n",
       " datetime.datetime(1968, 4, 30, 0, 0),\n",
       " datetime.datetime(1968, 5, 31, 0, 0),\n",
       " datetime.datetime(1968, 6, 30, 0, 0),\n",
       " datetime.datetime(1968, 7, 31, 0, 0),\n",
       " datetime.datetime(1968, 8, 31, 0, 0),\n",
       " datetime.datetime(1968, 9, 30, 0, 0),\n",
       " datetime.datetime(1968, 10, 31, 0, 0),\n",
       " datetime.datetime(1968, 11, 30, 0, 0),\n",
       " datetime.datetime(1968, 12, 31, 0, 0),\n",
       " datetime.datetime(1969, 1, 31, 0, 0),\n",
       " datetime.datetime(1969, 2, 28, 0, 0),\n",
       " datetime.datetime(1969, 3, 31, 0, 0),\n",
       " datetime.datetime(1969, 4, 30, 0, 0),\n",
       " datetime.datetime(1969, 5, 31, 0, 0),\n",
       " datetime.datetime(1969, 6, 30, 0, 0),\n",
       " datetime.datetime(1969, 7, 31, 0, 0),\n",
       " datetime.datetime(1969, 8, 31, 0, 0),\n",
       " datetime.datetime(1969, 9, 30, 0, 0),\n",
       " datetime.datetime(1969, 10, 31, 0, 0),\n",
       " datetime.datetime(1969, 11, 30, 0, 0),\n",
       " datetime.datetime(1969, 12, 31, 0, 0),\n",
       " datetime.datetime(1970, 1, 31, 0, 0),\n",
       " datetime.datetime(1970, 2, 28, 0, 0),\n",
       " datetime.datetime(1970, 3, 31, 0, 0),\n",
       " datetime.datetime(1970, 4, 30, 0, 0),\n",
       " datetime.datetime(1970, 5, 31, 0, 0),\n",
       " datetime.datetime(1970, 6, 30, 0, 0),\n",
       " datetime.datetime(1970, 7, 31, 0, 0),\n",
       " datetime.datetime(1970, 8, 31, 0, 0),\n",
       " datetime.datetime(1970, 9, 30, 0, 0),\n",
       " datetime.datetime(1970, 10, 31, 0, 0),\n",
       " datetime.datetime(1970, 11, 30, 0, 0),\n",
       " datetime.datetime(1970, 12, 31, 0, 0),\n",
       " datetime.datetime(1971, 1, 31, 0, 0),\n",
       " datetime.datetime(1971, 2, 28, 0, 0),\n",
       " datetime.datetime(1971, 3, 31, 0, 0),\n",
       " datetime.datetime(1971, 4, 30, 0, 0),\n",
       " datetime.datetime(1971, 5, 31, 0, 0),\n",
       " datetime.datetime(1971, 6, 30, 0, 0),\n",
       " datetime.datetime(1971, 7, 31, 0, 0),\n",
       " datetime.datetime(1971, 8, 31, 0, 0),\n",
       " datetime.datetime(1971, 9, 30, 0, 0),\n",
       " datetime.datetime(1971, 10, 31, 0, 0),\n",
       " datetime.datetime(1971, 11, 30, 0, 0),\n",
       " datetime.datetime(1971, 12, 31, 0, 0),\n",
       " datetime.datetime(1972, 1, 31, 0, 0),\n",
       " datetime.datetime(1972, 2, 29, 0, 0),\n",
       " datetime.datetime(1972, 3, 31, 0, 0),\n",
       " datetime.datetime(1972, 4, 30, 0, 0),\n",
       " datetime.datetime(1972, 5, 31, 0, 0),\n",
       " datetime.datetime(1972, 6, 30, 0, 0),\n",
       " datetime.datetime(1972, 7, 31, 0, 0),\n",
       " datetime.datetime(1972, 8, 31, 0, 0),\n",
       " datetime.datetime(1972, 9, 30, 0, 0),\n",
       " datetime.datetime(1972, 10, 31, 0, 0),\n",
       " datetime.datetime(1972, 11, 30, 0, 0),\n",
       " datetime.datetime(1972, 12, 31, 0, 0),\n",
       " datetime.datetime(1973, 1, 31, 0, 0),\n",
       " datetime.datetime(1973, 2, 28, 0, 0),\n",
       " datetime.datetime(1973, 3, 31, 0, 0),\n",
       " datetime.datetime(1973, 4, 30, 0, 0),\n",
       " datetime.datetime(1973, 5, 31, 0, 0),\n",
       " datetime.datetime(1973, 6, 30, 0, 0),\n",
       " datetime.datetime(1973, 7, 31, 0, 0),\n",
       " datetime.datetime(1973, 8, 31, 0, 0),\n",
       " datetime.datetime(1973, 9, 30, 0, 0),\n",
       " datetime.datetime(1973, 10, 31, 0, 0),\n",
       " datetime.datetime(1973, 11, 30, 0, 0),\n",
       " datetime.datetime(1973, 12, 31, 0, 0),\n",
       " datetime.datetime(1974, 1, 31, 0, 0),\n",
       " datetime.datetime(1974, 2, 28, 0, 0),\n",
       " datetime.datetime(1974, 3, 31, 0, 0),\n",
       " datetime.datetime(1974, 4, 30, 0, 0),\n",
       " datetime.datetime(1974, 5, 31, 0, 0),\n",
       " datetime.datetime(1974, 6, 30, 0, 0),\n",
       " datetime.datetime(1974, 7, 31, 0, 0),\n",
       " datetime.datetime(1974, 8, 31, 0, 0),\n",
       " datetime.datetime(1974, 9, 30, 0, 0),\n",
       " datetime.datetime(1974, 10, 31, 0, 0),\n",
       " datetime.datetime(1974, 11, 30, 0, 0),\n",
       " datetime.datetime(1974, 12, 31, 0, 0),\n",
       " datetime.datetime(1975, 1, 31, 0, 0),\n",
       " datetime.datetime(1975, 2, 28, 0, 0),\n",
       " datetime.datetime(1975, 3, 31, 0, 0),\n",
       " datetime.datetime(1975, 4, 30, 0, 0),\n",
       " datetime.datetime(1975, 5, 31, 0, 0),\n",
       " datetime.datetime(1975, 6, 30, 0, 0),\n",
       " datetime.datetime(1975, 7, 31, 0, 0),\n",
       " datetime.datetime(1975, 8, 31, 0, 0),\n",
       " datetime.datetime(1975, 9, 30, 0, 0),\n",
       " datetime.datetime(1975, 10, 31, 0, 0),\n",
       " datetime.datetime(1975, 11, 30, 0, 0),\n",
       " datetime.datetime(1975, 12, 31, 0, 0),\n",
       " datetime.datetime(1976, 1, 31, 0, 0),\n",
       " datetime.datetime(1976, 2, 29, 0, 0),\n",
       " datetime.datetime(1976, 3, 31, 0, 0),\n",
       " datetime.datetime(1976, 4, 30, 0, 0),\n",
       " datetime.datetime(1976, 5, 31, 0, 0),\n",
       " datetime.datetime(1976, 6, 30, 0, 0),\n",
       " datetime.datetime(1976, 7, 31, 0, 0),\n",
       " datetime.datetime(1976, 8, 31, 0, 0),\n",
       " datetime.datetime(1976, 9, 30, 0, 0),\n",
       " datetime.datetime(1976, 10, 31, 0, 0),\n",
       " datetime.datetime(1976, 11, 30, 0, 0),\n",
       " datetime.datetime(1976, 12, 31, 0, 0),\n",
       " datetime.datetime(1977, 1, 31, 0, 0),\n",
       " datetime.datetime(1977, 2, 28, 0, 0),\n",
       " datetime.datetime(1977, 3, 31, 0, 0),\n",
       " datetime.datetime(1977, 4, 30, 0, 0),\n",
       " datetime.datetime(1977, 5, 31, 0, 0),\n",
       " datetime.datetime(1977, 6, 30, 0, 0),\n",
       " datetime.datetime(1977, 7, 31, 0, 0),\n",
       " datetime.datetime(1977, 8, 31, 0, 0),\n",
       " datetime.datetime(1977, 9, 30, 0, 0),\n",
       " datetime.datetime(1977, 10, 31, 0, 0),\n",
       " datetime.datetime(1977, 11, 30, 0, 0),\n",
       " datetime.datetime(1977, 12, 31, 0, 0),\n",
       " datetime.datetime(1978, 1, 31, 0, 0),\n",
       " datetime.datetime(1978, 2, 28, 0, 0),\n",
       " datetime.datetime(1978, 3, 31, 0, 0),\n",
       " datetime.datetime(1978, 4, 30, 0, 0),\n",
       " datetime.datetime(1978, 5, 31, 0, 0),\n",
       " datetime.datetime(1978, 6, 30, 0, 0),\n",
       " datetime.datetime(1978, 7, 31, 0, 0),\n",
       " datetime.datetime(1978, 8, 31, 0, 0),\n",
       " datetime.datetime(1978, 9, 30, 0, 0),\n",
       " datetime.datetime(1978, 10, 31, 0, 0),\n",
       " datetime.datetime(1978, 11, 30, 0, 0),\n",
       " datetime.datetime(1978, 12, 31, 0, 0),\n",
       " datetime.datetime(1979, 1, 31, 0, 0),\n",
       " datetime.datetime(1979, 2, 28, 0, 0),\n",
       " datetime.datetime(1979, 3, 31, 0, 0),\n",
       " datetime.datetime(1979, 4, 30, 0, 0),\n",
       " datetime.datetime(1979, 5, 31, 0, 0),\n",
       " datetime.datetime(1979, 6, 30, 0, 0),\n",
       " datetime.datetime(1979, 7, 31, 0, 0),\n",
       " datetime.datetime(1979, 8, 31, 0, 0),\n",
       " datetime.datetime(1979, 9, 30, 0, 0),\n",
       " datetime.datetime(1979, 10, 31, 0, 0),\n",
       " datetime.datetime(1979, 11, 30, 0, 0),\n",
       " datetime.datetime(1979, 12, 31, 0, 0),\n",
       " datetime.datetime(1980, 1, 31, 0, 0),\n",
       " datetime.datetime(1980, 2, 29, 0, 0),\n",
       " datetime.datetime(1980, 3, 31, 0, 0),\n",
       " datetime.datetime(1980, 4, 30, 0, 0),\n",
       " datetime.datetime(1980, 5, 31, 0, 0),\n",
       " datetime.datetime(1980, 6, 30, 0, 0),\n",
       " datetime.datetime(1980, 7, 31, 0, 0),\n",
       " datetime.datetime(1980, 8, 31, 0, 0),\n",
       " datetime.datetime(1980, 9, 30, 0, 0),\n",
       " datetime.datetime(1980, 10, 31, 0, 0),\n",
       " datetime.datetime(1980, 11, 30, 0, 0),\n",
       " datetime.datetime(1980, 12, 31, 0, 0),\n",
       " datetime.datetime(1981, 1, 31, 0, 0),\n",
       " datetime.datetime(1981, 2, 28, 0, 0),\n",
       " datetime.datetime(1981, 3, 31, 0, 0),\n",
       " datetime.datetime(1981, 4, 30, 0, 0),\n",
       " datetime.datetime(1981, 5, 31, 0, 0),\n",
       " datetime.datetime(1981, 6, 30, 0, 0),\n",
       " datetime.datetime(1981, 7, 31, 0, 0),\n",
       " datetime.datetime(1981, 8, 31, 0, 0),\n",
       " datetime.datetime(1981, 9, 30, 0, 0),\n",
       " datetime.datetime(1981, 10, 31, 0, 0),\n",
       " datetime.datetime(1981, 11, 30, 0, 0),\n",
       " datetime.datetime(1981, 12, 31, 0, 0),\n",
       " datetime.datetime(1982, 1, 31, 0, 0),\n",
       " datetime.datetime(1982, 2, 28, 0, 0),\n",
       " datetime.datetime(1982, 3, 31, 0, 0),\n",
       " datetime.datetime(1982, 4, 30, 0, 0),\n",
       " datetime.datetime(1982, 5, 31, 0, 0),\n",
       " datetime.datetime(1982, 6, 30, 0, 0),\n",
       " datetime.datetime(1982, 7, 31, 0, 0),\n",
       " datetime.datetime(1982, 8, 31, 0, 0),\n",
       " datetime.datetime(1982, 9, 30, 0, 0),\n",
       " datetime.datetime(1982, 10, 31, 0, 0),\n",
       " datetime.datetime(1982, 11, 30, 0, 0),\n",
       " datetime.datetime(1982, 12, 31, 0, 0),\n",
       " datetime.datetime(1983, 1, 31, 0, 0),\n",
       " datetime.datetime(1983, 2, 28, 0, 0),\n",
       " datetime.datetime(1983, 3, 31, 0, 0),\n",
       " datetime.datetime(1983, 4, 30, 0, 0),\n",
       " datetime.datetime(1983, 5, 31, 0, 0),\n",
       " datetime.datetime(1983, 6, 30, 0, 0),\n",
       " datetime.datetime(1983, 7, 31, 0, 0),\n",
       " datetime.datetime(1983, 8, 31, 0, 0),\n",
       " datetime.datetime(1983, 9, 30, 0, 0),\n",
       " datetime.datetime(1983, 10, 31, 0, 0),\n",
       " datetime.datetime(1983, 11, 30, 0, 0),\n",
       " datetime.datetime(1983, 12, 31, 0, 0),\n",
       " datetime.datetime(1984, 1, 31, 0, 0),\n",
       " datetime.datetime(1984, 2, 29, 0, 0),\n",
       " datetime.datetime(1984, 3, 31, 0, 0),\n",
       " datetime.datetime(1984, 4, 30, 0, 0),\n",
       " datetime.datetime(1984, 5, 31, 0, 0),\n",
       " datetime.datetime(1984, 6, 30, 0, 0),\n",
       " datetime.datetime(1984, 7, 31, 0, 0),\n",
       " datetime.datetime(1984, 8, 31, 0, 0),\n",
       " datetime.datetime(1984, 9, 30, 0, 0),\n",
       " datetime.datetime(1984, 10, 31, 0, 0),\n",
       " datetime.datetime(1984, 11, 30, 0, 0),\n",
       " datetime.datetime(1984, 12, 31, 0, 0),\n",
       " datetime.datetime(1985, 1, 31, 0, 0),\n",
       " datetime.datetime(1985, 2, 28, 0, 0),\n",
       " datetime.datetime(1985, 3, 31, 0, 0),\n",
       " datetime.datetime(1985, 4, 30, 0, 0),\n",
       " datetime.datetime(1985, 5, 31, 0, 0),\n",
       " datetime.datetime(1985, 6, 30, 0, 0),\n",
       " datetime.datetime(1985, 7, 31, 0, 0),\n",
       " datetime.datetime(1985, 8, 31, 0, 0),\n",
       " datetime.datetime(1985, 9, 30, 0, 0),\n",
       " datetime.datetime(1985, 10, 31, 0, 0),\n",
       " datetime.datetime(1985, 11, 30, 0, 0),\n",
       " datetime.datetime(1985, 12, 31, 0, 0),\n",
       " datetime.datetime(1986, 1, 31, 0, 0),\n",
       " datetime.datetime(1986, 2, 28, 0, 0),\n",
       " datetime.datetime(1986, 3, 31, 0, 0),\n",
       " datetime.datetime(1986, 4, 30, 0, 0),\n",
       " datetime.datetime(1986, 5, 31, 0, 0),\n",
       " datetime.datetime(1986, 6, 30, 0, 0),\n",
       " datetime.datetime(1986, 7, 31, 0, 0),\n",
       " datetime.datetime(1986, 8, 31, 0, 0),\n",
       " datetime.datetime(1986, 9, 30, 0, 0),\n",
       " datetime.datetime(1986, 10, 31, 0, 0),\n",
       " datetime.datetime(1986, 11, 30, 0, 0),\n",
       " datetime.datetime(1986, 12, 31, 0, 0),\n",
       " datetime.datetime(1987, 1, 31, 0, 0),\n",
       " datetime.datetime(1987, 2, 28, 0, 0),\n",
       " datetime.datetime(1987, 3, 31, 0, 0),\n",
       " datetime.datetime(1987, 4, 30, 0, 0),\n",
       " datetime.datetime(1987, 5, 31, 0, 0),\n",
       " datetime.datetime(1987, 6, 30, 0, 0),\n",
       " datetime.datetime(1987, 7, 31, 0, 0),\n",
       " datetime.datetime(1987, 8, 31, 0, 0),\n",
       " datetime.datetime(1987, 9, 30, 0, 0),\n",
       " datetime.datetime(1987, 10, 31, 0, 0),\n",
       " datetime.datetime(1987, 11, 30, 0, 0),\n",
       " datetime.datetime(1987, 12, 31, 0, 0),\n",
       " datetime.datetime(1988, 1, 31, 0, 0),\n",
       " datetime.datetime(1988, 2, 29, 0, 0),\n",
       " datetime.datetime(1988, 3, 31, 0, 0),\n",
       " datetime.datetime(1988, 4, 30, 0, 0),\n",
       " datetime.datetime(1988, 5, 31, 0, 0),\n",
       " datetime.datetime(1988, 6, 30, 0, 0),\n",
       " datetime.datetime(1988, 7, 31, 0, 0),\n",
       " datetime.datetime(1988, 8, 31, 0, 0),\n",
       " datetime.datetime(1988, 9, 30, 0, 0),\n",
       " datetime.datetime(1988, 10, 31, 0, 0),\n",
       " datetime.datetime(1988, 11, 30, 0, 0),\n",
       " datetime.datetime(1988, 12, 31, 0, 0),\n",
       " datetime.datetime(1989, 1, 31, 0, 0),\n",
       " datetime.datetime(1989, 2, 28, 0, 0),\n",
       " datetime.datetime(1989, 3, 31, 0, 0),\n",
       " datetime.datetime(1989, 4, 30, 0, 0),\n",
       " datetime.datetime(1989, 5, 31, 0, 0),\n",
       " datetime.datetime(1989, 6, 30, 0, 0),\n",
       " datetime.datetime(1989, 7, 31, 0, 0),\n",
       " datetime.datetime(1989, 8, 31, 0, 0),\n",
       " datetime.datetime(1989, 9, 30, 0, 0),\n",
       " datetime.datetime(1989, 10, 31, 0, 0),\n",
       " datetime.datetime(1989, 11, 30, 0, 0),\n",
       " datetime.datetime(1989, 12, 31, 0, 0),\n",
       " datetime.datetime(1990, 1, 31, 0, 0),\n",
       " datetime.datetime(1990, 2, 28, 0, 0),\n",
       " datetime.datetime(1990, 3, 31, 0, 0),\n",
       " datetime.datetime(1990, 4, 30, 0, 0),\n",
       " datetime.datetime(1990, 5, 31, 0, 0),\n",
       " datetime.datetime(1990, 6, 30, 0, 0),\n",
       " datetime.datetime(1990, 7, 31, 0, 0),\n",
       " datetime.datetime(1990, 8, 31, 0, 0),\n",
       " datetime.datetime(1990, 9, 30, 0, 0),\n",
       " datetime.datetime(1990, 10, 31, 0, 0),\n",
       " datetime.datetime(1990, 11, 30, 0, 0),\n",
       " datetime.datetime(1990, 12, 31, 0, 0),\n",
       " datetime.datetime(1991, 1, 31, 0, 0),\n",
       " datetime.datetime(1991, 2, 28, 0, 0),\n",
       " datetime.datetime(1991, 3, 31, 0, 0),\n",
       " datetime.datetime(1991, 4, 30, 0, 0),\n",
       " datetime.datetime(1991, 5, 31, 0, 0),\n",
       " datetime.datetime(1991, 6, 30, 0, 0),\n",
       " datetime.datetime(1991, 7, 31, 0, 0),\n",
       " datetime.datetime(1991, 8, 31, 0, 0),\n",
       " datetime.datetime(1991, 9, 30, 0, 0),\n",
       " datetime.datetime(1991, 10, 31, 0, 0),\n",
       " datetime.datetime(1991, 11, 30, 0, 0),\n",
       " datetime.datetime(1991, 12, 31, 0, 0),\n",
       " datetime.datetime(1992, 1, 31, 0, 0),\n",
       " datetime.datetime(1992, 2, 29, 0, 0),\n",
       " datetime.datetime(1992, 3, 31, 0, 0),\n",
       " datetime.datetime(1992, 4, 30, 0, 0),\n",
       " datetime.datetime(1992, 5, 31, 0, 0),\n",
       " datetime.datetime(1992, 6, 30, 0, 0),\n",
       " datetime.datetime(1992, 7, 31, 0, 0),\n",
       " datetime.datetime(1992, 8, 31, 0, 0),\n",
       " datetime.datetime(1992, 9, 30, 0, 0),\n",
       " datetime.datetime(1992, 10, 31, 0, 0),\n",
       " datetime.datetime(1992, 11, 30, 0, 0),\n",
       " datetime.datetime(1992, 12, 31, 0, 0),\n",
       " datetime.datetime(1993, 1, 31, 0, 0),\n",
       " datetime.datetime(1993, 2, 28, 0, 0),\n",
       " datetime.datetime(1993, 3, 31, 0, 0),\n",
       " datetime.datetime(1993, 4, 30, 0, 0),\n",
       " datetime.datetime(1993, 5, 31, 0, 0),\n",
       " datetime.datetime(1993, 6, 30, 0, 0),\n",
       " datetime.datetime(1993, 7, 31, 0, 0),\n",
       " datetime.datetime(1993, 8, 31, 0, 0),\n",
       " datetime.datetime(1993, 9, 30, 0, 0),\n",
       " datetime.datetime(1993, 10, 31, 0, 0),\n",
       " datetime.datetime(1993, 11, 30, 0, 0),\n",
       " datetime.datetime(1993, 12, 31, 0, 0),\n",
       " datetime.datetime(1994, 1, 31, 0, 0),\n",
       " datetime.datetime(1994, 2, 28, 0, 0),\n",
       " datetime.datetime(1994, 3, 31, 0, 0),\n",
       " datetime.datetime(1994, 4, 30, 0, 0),\n",
       " datetime.datetime(1994, 5, 31, 0, 0),\n",
       " datetime.datetime(1994, 6, 30, 0, 0),\n",
       " datetime.datetime(1994, 7, 31, 0, 0),\n",
       " datetime.datetime(1994, 8, 31, 0, 0),\n",
       " datetime.datetime(1994, 9, 30, 0, 0),\n",
       " datetime.datetime(1994, 10, 31, 0, 0),\n",
       " datetime.datetime(1994, 11, 30, 0, 0),\n",
       " datetime.datetime(1994, 12, 31, 0, 0),\n",
       " datetime.datetime(1995, 1, 31, 0, 0),\n",
       " datetime.datetime(1995, 2, 28, 0, 0),\n",
       " datetime.datetime(1995, 3, 31, 0, 0),\n",
       " datetime.datetime(1995, 4, 30, 0, 0),\n",
       " datetime.datetime(1995, 5, 31, 0, 0),\n",
       " datetime.datetime(1995, 6, 30, 0, 0),\n",
       " datetime.datetime(1995, 7, 31, 0, 0),\n",
       " datetime.datetime(1995, 8, 31, 0, 0),\n",
       " datetime.datetime(1995, 9, 30, 0, 0),\n",
       " datetime.datetime(1995, 10, 31, 0, 0),\n",
       " datetime.datetime(1995, 11, 30, 0, 0),\n",
       " datetime.datetime(1995, 12, 31, 0, 0),\n",
       " datetime.datetime(1996, 1, 31, 0, 0),\n",
       " datetime.datetime(1996, 2, 29, 0, 0),\n",
       " datetime.datetime(1996, 3, 31, 0, 0),\n",
       " datetime.datetime(1996, 4, 30, 0, 0),\n",
       " datetime.datetime(1996, 5, 31, 0, 0),\n",
       " datetime.datetime(1996, 6, 30, 0, 0),\n",
       " datetime.datetime(1996, 7, 31, 0, 0),\n",
       " datetime.datetime(1996, 8, 31, 0, 0),\n",
       " datetime.datetime(1996, 9, 30, 0, 0),\n",
       " datetime.datetime(1996, 10, 31, 0, 0),\n",
       " datetime.datetime(1996, 11, 30, 0, 0),\n",
       " datetime.datetime(1996, 12, 31, 0, 0),\n",
       " datetime.datetime(1997, 1, 31, 0, 0),\n",
       " datetime.datetime(1997, 2, 28, 0, 0),\n",
       " datetime.datetime(1997, 3, 31, 0, 0),\n",
       " datetime.datetime(1997, 4, 30, 0, 0),\n",
       " datetime.datetime(1997, 5, 31, 0, 0),\n",
       " datetime.datetime(1997, 6, 30, 0, 0),\n",
       " datetime.datetime(1997, 7, 31, 0, 0),\n",
       " datetime.datetime(1997, 8, 31, 0, 0),\n",
       " datetime.datetime(1997, 9, 30, 0, 0),\n",
       " datetime.datetime(1997, 10, 31, 0, 0),\n",
       " datetime.datetime(1997, 11, 30, 0, 0),\n",
       " datetime.datetime(1997, 12, 31, 0, 0),\n",
       " datetime.datetime(1998, 1, 31, 0, 0),\n",
       " datetime.datetime(1998, 2, 28, 0, 0),\n",
       " datetime.datetime(1998, 3, 31, 0, 0),\n",
       " datetime.datetime(1998, 4, 30, 0, 0),\n",
       " datetime.datetime(1998, 5, 31, 0, 0),\n",
       " datetime.datetime(1998, 6, 30, 0, 0),\n",
       " datetime.datetime(1998, 7, 31, 0, 0),\n",
       " datetime.datetime(1998, 8, 31, 0, 0),\n",
       " datetime.datetime(1998, 9, 30, 0, 0),\n",
       " datetime.datetime(1998, 10, 31, 0, 0),\n",
       " datetime.datetime(1998, 11, 30, 0, 0),\n",
       " datetime.datetime(1998, 12, 31, 0, 0),\n",
       " datetime.datetime(1999, 1, 31, 0, 0),\n",
       " datetime.datetime(1999, 2, 28, 0, 0),\n",
       " datetime.datetime(1999, 3, 31, 0, 0),\n",
       " datetime.datetime(1999, 4, 30, 0, 0),\n",
       " datetime.datetime(1999, 5, 31, 0, 0),\n",
       " datetime.datetime(1999, 6, 30, 0, 0),\n",
       " datetime.datetime(1999, 7, 31, 0, 0),\n",
       " datetime.datetime(1999, 8, 31, 0, 0),\n",
       " datetime.datetime(1999, 9, 30, 0, 0),\n",
       " datetime.datetime(1999, 10, 31, 0, 0),\n",
       " datetime.datetime(1999, 11, 30, 0, 0),\n",
       " datetime.datetime(1999, 12, 31, 0, 0),\n",
       " datetime.datetime(2000, 1, 31, 0, 0),\n",
       " datetime.datetime(2000, 2, 29, 0, 0),\n",
       " datetime.datetime(2000, 3, 31, 0, 0),\n",
       " datetime.datetime(2000, 4, 30, 0, 0),\n",
       " datetime.datetime(2000, 5, 31, 0, 0),\n",
       " datetime.datetime(2000, 6, 30, 0, 0),\n",
       " datetime.datetime(2000, 7, 31, 0, 0),\n",
       " datetime.datetime(2000, 8, 31, 0, 0),\n",
       " datetime.datetime(2000, 9, 30, 0, 0),\n",
       " datetime.datetime(2000, 10, 31, 0, 0),\n",
       " datetime.datetime(2000, 11, 30, 0, 0),\n",
       " datetime.datetime(2000, 12, 31, 0, 0),\n",
       " datetime.datetime(2001, 1, 31, 0, 0),\n",
       " datetime.datetime(2001, 2, 28, 0, 0),\n",
       " datetime.datetime(2001, 3, 31, 0, 0),\n",
       " datetime.datetime(2001, 4, 30, 0, 0),\n",
       " datetime.datetime(2001, 5, 31, 0, 0),\n",
       " datetime.datetime(2001, 6, 30, 0, 0),\n",
       " datetime.datetime(2001, 7, 31, 0, 0),\n",
       " datetime.datetime(2001, 8, 31, 0, 0),\n",
       " datetime.datetime(2001, 9, 30, 0, 0),\n",
       " datetime.datetime(2001, 10, 31, 0, 0),\n",
       " datetime.datetime(2001, 11, 30, 0, 0),\n",
       " datetime.datetime(2001, 12, 31, 0, 0),\n",
       " datetime.datetime(2002, 1, 31, 0, 0),\n",
       " datetime.datetime(2002, 2, 28, 0, 0),\n",
       " datetime.datetime(2002, 3, 31, 0, 0),\n",
       " datetime.datetime(2002, 4, 30, 0, 0),\n",
       " datetime.datetime(2002, 5, 31, 0, 0),\n",
       " datetime.datetime(2002, 6, 30, 0, 0),\n",
       " datetime.datetime(2002, 7, 31, 0, 0),\n",
       " datetime.datetime(2002, 8, 31, 0, 0),\n",
       " datetime.datetime(2002, 9, 30, 0, 0),\n",
       " datetime.datetime(2002, 10, 31, 0, 0),\n",
       " datetime.datetime(2002, 11, 30, 0, 0),\n",
       " datetime.datetime(2002, 12, 31, 0, 0),\n",
       " datetime.datetime(2003, 1, 31, 0, 0),\n",
       " datetime.datetime(2003, 2, 28, 0, 0),\n",
       " datetime.datetime(2003, 3, 31, 0, 0),\n",
       " datetime.datetime(2003, 4, 30, 0, 0),\n",
       " datetime.datetime(2003, 5, 31, 0, 0),\n",
       " datetime.datetime(2003, 6, 30, 0, 0),\n",
       " datetime.datetime(2003, 7, 31, 0, 0),\n",
       " datetime.datetime(2003, 8, 31, 0, 0),\n",
       " datetime.datetime(2003, 9, 30, 0, 0),\n",
       " datetime.datetime(2003, 10, 31, 0, 0),\n",
       " datetime.datetime(2003, 11, 30, 0, 0),\n",
       " datetime.datetime(2003, 12, 31, 0, 0),\n",
       " datetime.datetime(2004, 1, 31, 0, 0),\n",
       " datetime.datetime(2004, 2, 29, 0, 0),\n",
       " datetime.datetime(2004, 3, 31, 0, 0),\n",
       " datetime.datetime(2004, 4, 30, 0, 0),\n",
       " datetime.datetime(2004, 5, 31, 0, 0),\n",
       " datetime.datetime(2004, 6, 30, 0, 0),\n",
       " datetime.datetime(2004, 7, 31, 0, 0),\n",
       " datetime.datetime(2004, 8, 31, 0, 0),\n",
       " datetime.datetime(2004, 9, 30, 0, 0),\n",
       " datetime.datetime(2004, 10, 31, 0, 0),\n",
       " datetime.datetime(2004, 11, 30, 0, 0),\n",
       " datetime.datetime(2004, 12, 31, 0, 0),\n",
       " datetime.datetime(2005, 1, 31, 0, 0),\n",
       " datetime.datetime(2005, 2, 28, 0, 0),\n",
       " datetime.datetime(2005, 3, 31, 0, 0),\n",
       " datetime.datetime(2005, 4, 30, 0, 0),\n",
       " datetime.datetime(2005, 5, 31, 0, 0),\n",
       " datetime.datetime(2005, 6, 30, 0, 0),\n",
       " datetime.datetime(2005, 7, 31, 0, 0),\n",
       " datetime.datetime(2005, 8, 31, 0, 0),\n",
       " datetime.datetime(2005, 9, 30, 0, 0),\n",
       " datetime.datetime(2005, 10, 31, 0, 0),\n",
       " datetime.datetime(2005, 11, 30, 0, 0),\n",
       " datetime.datetime(2005, 12, 31, 0, 0),\n",
       " datetime.datetime(2006, 1, 31, 0, 0),\n",
       " datetime.datetime(2006, 2, 28, 0, 0),\n",
       " datetime.datetime(2006, 3, 31, 0, 0),\n",
       " datetime.datetime(2006, 4, 30, 0, 0),\n",
       " datetime.datetime(2006, 5, 31, 0, 0),\n",
       " datetime.datetime(2006, 6, 30, 0, 0),\n",
       " datetime.datetime(2006, 7, 31, 0, 0),\n",
       " datetime.datetime(2006, 8, 31, 0, 0),\n",
       " datetime.datetime(2006, 9, 30, 0, 0),\n",
       " datetime.datetime(2006, 10, 31, 0, 0),\n",
       " datetime.datetime(2006, 11, 30, 0, 0),\n",
       " datetime.datetime(2006, 12, 31, 0, 0),\n",
       " datetime.datetime(2007, 1, 31, 0, 0),\n",
       " datetime.datetime(2007, 2, 28, 0, 0),\n",
       " datetime.datetime(2007, 3, 31, 0, 0),\n",
       " datetime.datetime(2007, 4, 30, 0, 0),\n",
       " datetime.datetime(2007, 5, 31, 0, 0),\n",
       " datetime.datetime(2007, 6, 30, 0, 0),\n",
       " datetime.datetime(2007, 7, 31, 0, 0),\n",
       " datetime.datetime(2007, 8, 31, 0, 0),\n",
       " datetime.datetime(2007, 9, 30, 0, 0),\n",
       " datetime.datetime(2007, 10, 31, 0, 0),\n",
       " datetime.datetime(2007, 11, 30, 0, 0),\n",
       " datetime.datetime(2007, 12, 31, 0, 0),\n",
       " datetime.datetime(2008, 1, 31, 0, 0),\n",
       " datetime.datetime(2008, 2, 29, 0, 0),\n",
       " datetime.datetime(2008, 3, 31, 0, 0),\n",
       " datetime.datetime(2008, 4, 30, 0, 0),\n",
       " datetime.datetime(2008, 5, 31, 0, 0),\n",
       " datetime.datetime(2008, 6, 30, 0, 0),\n",
       " datetime.datetime(2008, 7, 31, 0, 0),\n",
       " datetime.datetime(2008, 8, 31, 0, 0),\n",
       " datetime.datetime(2008, 9, 30, 0, 0),\n",
       " datetime.datetime(2008, 10, 31, 0, 0),\n",
       " datetime.datetime(2008, 11, 30, 0, 0),\n",
       " datetime.datetime(2008, 12, 31, 0, 0),\n",
       " datetime.datetime(2009, 1, 31, 0, 0),\n",
       " datetime.datetime(2009, 2, 28, 0, 0),\n",
       " datetime.datetime(2009, 3, 31, 0, 0),\n",
       " datetime.datetime(2009, 4, 30, 0, 0),\n",
       " datetime.datetime(2009, 5, 31, 0, 0),\n",
       " datetime.datetime(2009, 6, 30, 0, 0),\n",
       " datetime.datetime(2009, 7, 31, 0, 0),\n",
       " datetime.datetime(2009, 8, 31, 0, 0),\n",
       " datetime.datetime(2009, 9, 30, 0, 0),\n",
       " datetime.datetime(2009, 10, 31, 0, 0),\n",
       " datetime.datetime(2009, 11, 30, 0, 0),\n",
       " datetime.datetime(2009, 12, 31, 0, 0),\n",
       " datetime.datetime(2010, 1, 31, 0, 0),\n",
       " datetime.datetime(2010, 2, 28, 0, 0),\n",
       " datetime.datetime(2010, 3, 31, 0, 0),\n",
       " datetime.datetime(2010, 4, 30, 0, 0),\n",
       " datetime.datetime(2010, 5, 31, 0, 0),\n",
       " datetime.datetime(2010, 6, 30, 0, 0),\n",
       " datetime.datetime(2010, 7, 31, 0, 0),\n",
       " datetime.datetime(2010, 8, 31, 0, 0),\n",
       " datetime.datetime(2010, 9, 30, 0, 0),\n",
       " datetime.datetime(2010, 10, 31, 0, 0),\n",
       " datetime.datetime(2010, 11, 30, 0, 0),\n",
       " datetime.datetime(2010, 12, 31, 0, 0),\n",
       " datetime.datetime(2011, 1, 31, 0, 0),\n",
       " datetime.datetime(2011, 2, 28, 0, 0),\n",
       " datetime.datetime(2011, 3, 31, 0, 0),\n",
       " datetime.datetime(2011, 4, 30, 0, 0),\n",
       " datetime.datetime(2011, 5, 31, 0, 0),\n",
       " datetime.datetime(2011, 6, 30, 0, 0),\n",
       " datetime.datetime(2011, 7, 31, 0, 0),\n",
       " datetime.datetime(2011, 8, 31, 0, 0),\n",
       " datetime.datetime(2011, 9, 30, 0, 0),\n",
       " datetime.datetime(2011, 10, 31, 0, 0),\n",
       " datetime.datetime(2011, 11, 30, 0, 0),\n",
       " datetime.datetime(2011, 12, 31, 0, 0),\n",
       " datetime.datetime(2012, 1, 31, 0, 0),\n",
       " datetime.datetime(2012, 2, 29, 0, 0),\n",
       " datetime.datetime(2012, 3, 31, 0, 0),\n",
       " datetime.datetime(2012, 4, 30, 0, 0),\n",
       " datetime.datetime(2012, 5, 31, 0, 0),\n",
       " datetime.datetime(2012, 6, 30, 0, 0),\n",
       " datetime.datetime(2012, 7, 31, 0, 0),\n",
       " datetime.datetime(2012, 8, 31, 0, 0),\n",
       " datetime.datetime(2012, 9, 30, 0, 0),\n",
       " datetime.datetime(2012, 10, 31, 0, 0),\n",
       " datetime.datetime(2012, 11, 30, 0, 0),\n",
       " datetime.datetime(2012, 12, 31, 0, 0),\n",
       " datetime.datetime(2013, 1, 31, 0, 0),\n",
       " datetime.datetime(2013, 2, 28, 0, 0),\n",
       " datetime.datetime(2013, 3, 31, 0, 0),\n",
       " datetime.datetime(2013, 4, 30, 0, 0),\n",
       " datetime.datetime(2013, 5, 31, 0, 0),\n",
       " datetime.datetime(2013, 6, 30, 0, 0),\n",
       " datetime.datetime(2013, 7, 31, 0, 0),\n",
       " datetime.datetime(2013, 8, 31, 0, 0),\n",
       " datetime.datetime(2013, 9, 30, 0, 0),\n",
       " datetime.datetime(2013, 10, 31, 0, 0),\n",
       " datetime.datetime(2013, 11, 30, 0, 0),\n",
       " datetime.datetime(2013, 12, 31, 0, 0),\n",
       " datetime.datetime(2014, 1, 31, 0, 0),\n",
       " datetime.datetime(2014, 2, 28, 0, 0),\n",
       " datetime.datetime(2014, 3, 31, 0, 0),\n",
       " datetime.datetime(2014, 4, 30, 0, 0),\n",
       " datetime.datetime(2014, 5, 31, 0, 0),\n",
       " datetime.datetime(2014, 6, 30, 0, 0),\n",
       " datetime.datetime(2014, 7, 31, 0, 0),\n",
       " datetime.datetime(2014, 8, 31, 0, 0),\n",
       " datetime.datetime(2014, 9, 30, 0, 0),\n",
       " datetime.datetime(2014, 10, 31, 0, 0),\n",
       " datetime.datetime(2014, 11, 30, 0, 0),\n",
       " datetime.datetime(2014, 12, 31, 0, 0),\n",
       " datetime.datetime(2015, 1, 31, 0, 0),\n",
       " datetime.datetime(2015, 2, 28, 0, 0),\n",
       " datetime.datetime(2015, 3, 31, 0, 0),\n",
       " datetime.datetime(2015, 4, 30, 0, 0),\n",
       " datetime.datetime(2015, 5, 31, 0, 0),\n",
       " datetime.datetime(2015, 6, 30, 0, 0),\n",
       " datetime.datetime(2015, 7, 31, 0, 0),\n",
       " datetime.datetime(2015, 8, 31, 0, 0),\n",
       " datetime.datetime(2015, 9, 30, 0, 0),\n",
       " datetime.datetime(2015, 10, 31, 0, 0),\n",
       " datetime.datetime(2015, 11, 30, 0, 0),\n",
       " datetime.datetime(2015, 12, 31, 0, 0),\n",
       " datetime.datetime(2016, 1, 31, 0, 0),\n",
       " datetime.datetime(2016, 2, 29, 0, 0),\n",
       " datetime.datetime(2016, 3, 31, 0, 0),\n",
       " datetime.datetime(2016, 4, 30, 0, 0),\n",
       " datetime.datetime(2016, 5, 31, 0, 0),\n",
       " datetime.datetime(2016, 6, 30, 0, 0),\n",
       " datetime.datetime(2016, 7, 31, 0, 0),\n",
       " datetime.datetime(2016, 8, 31, 0, 0),\n",
       " datetime.datetime(2016, 9, 30, 0, 0),\n",
       " datetime.datetime(2016, 10, 31, 0, 0),\n",
       " datetime.datetime(2016, 11, 30, 0, 0),\n",
       " datetime.datetime(2016, 12, 31, 0, 0),\n",
       " datetime.datetime(2017, 1, 31, 0, 0),\n",
       " datetime.datetime(2017, 2, 28, 0, 0),\n",
       " datetime.datetime(2017, 3, 31, 0, 0),\n",
       " datetime.datetime(2017, 4, 30, 0, 0),\n",
       " datetime.datetime(2017, 5, 31, 0, 0),\n",
       " datetime.datetime(2017, 6, 30, 0, 0),\n",
       " datetime.datetime(2017, 7, 31, 0, 0),\n",
       " datetime.datetime(2017, 8, 31, 0, 0),\n",
       " datetime.datetime(2017, 9, 30, 0, 0),\n",
       " datetime.datetime(2017, 10, 31, 0, 0),\n",
       " datetime.datetime(2017, 11, 30, 0, 0),\n",
       " datetime.datetime(2017, 12, 31, 0, 0),\n",
       " datetime.datetime(2018, 1, 31, 0, 0),\n",
       " datetime.datetime(2018, 2, 28, 0, 0),\n",
       " datetime.datetime(2018, 3, 31, 0, 0),\n",
       " datetime.datetime(2018, 4, 30, 0, 0),\n",
       " datetime.datetime(2018, 5, 31, 0, 0),\n",
       " datetime.datetime(2018, 6, 30, 0, 0)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['PRC'] = data['PRC'].fillna(-1)\n",
    "data['SHROUT'] = data['SHROUT'].fillna(-1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "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>PERMNO</th>\n",
       "      <th>date</th>\n",
       "      <th>DIVAMT</th>\n",
       "      <th>PRC</th>\n",
       "      <th>SHROUT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.37500</td>\n",
       "      <td>3680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.25000</td>\n",
       "      <td>3680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.43750</td>\n",
       "      <td>3680.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.10938</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.09375</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.84375</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.09375</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.03125</td>\n",
       "      <td>3793.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.78125</td>\n",
       "      <td>3843.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.82813</td>\n",
       "      <td>3843.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.51563</td>\n",
       "      <td>3843.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.25000</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.23438</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.21875</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>3893.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10001</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.25000</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.06250</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.50000</td>\n",
       "      <td>985.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>991.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.62500</td>\n",
       "      <td>991.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098016</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.20000</td>\n",
       "      <td>132056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098017</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.92999</td>\n",
       "      <td>132056.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098018</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>229.77000</td>\n",
       "      <td>133858.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098019</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>240.75999</td>\n",
       "      <td>133945.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098020</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>223.23000</td>\n",
       "      <td>147543.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098021</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.28000</td>\n",
       "      <td>148015.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098022</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>234.78999</td>\n",
       "      <td>148693.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098023</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.00999</td>\n",
       "      <td>149239.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098024</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>204.03000</td>\n",
       "      <td>149825.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098025</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>197.73000</td>\n",
       "      <td>149891.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098026</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>189.39999</td>\n",
       "      <td>149964.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098027</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>213.69000</td>\n",
       "      <td>161561.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098028</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>251.92999</td>\n",
       "      <td>161670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098029</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>249.99001</td>\n",
       "      <td>161670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098030</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>278.29999</td>\n",
       "      <td>164164.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098031</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>314.07001</td>\n",
       "      <td>164260.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098032</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.01001</td>\n",
       "      <td>164260.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098033</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>361.60999</td>\n",
       "      <td>166863.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098034</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>323.47000</td>\n",
       "      <td>166887.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098035</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>355.89999</td>\n",
       "      <td>166887.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098036</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.10001</td>\n",
       "      <td>168017.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098037</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>331.53000</td>\n",
       "      <td>168067.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098038</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>308.85001</td>\n",
       "      <td>168067.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098039</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>311.35001</td>\n",
       "      <td>168797.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098040</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>354.31000</td>\n",
       "      <td>168797.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098041</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>343.06000</td>\n",
       "      <td>168920.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098042</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>266.13000</td>\n",
       "      <td>169750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098043</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>293.89999</td>\n",
       "      <td>169794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098044</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>284.73001</td>\n",
       "      <td>169794.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098045</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>342.95001</td>\n",
       "      <td>170516.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4098046 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         PERMNO       date    DIVAMT        PRC    SHROUT\n",
       "0         10000 1985-12-31  0.000000   -1.00000      -1.0\n",
       "1         10000 1986-01-31  0.000000    4.37500    3680.0\n",
       "2         10000 1986-02-28  0.000000    3.25000    3680.0\n",
       "3         10000 1986-03-31  0.000000    4.43750    3680.0\n",
       "4         10000 1986-04-30  0.000000    4.00000    3793.0\n",
       "5         10000 1986-05-31  0.000000    3.10938    3793.0\n",
       "6         10000 1986-06-30  0.000000    3.09375    3793.0\n",
       "7         10000 1986-07-31  0.000000    2.84375    3793.0\n",
       "8         10000 1986-08-31  0.000000    1.09375    3793.0\n",
       "9         10000 1986-09-30  0.000000    1.03125    3793.0\n",
       "10        10000 1986-10-31  0.000000    0.78125    3843.0\n",
       "11        10000 1986-11-30  0.000000    0.82813    3843.0\n",
       "12        10000 1986-12-31  0.000000    0.51563    3843.0\n",
       "13        10000 1987-01-31  0.000000    0.40625    3893.0\n",
       "14        10000 1987-02-28  0.000000    0.40625    3893.0\n",
       "15        10000 1987-03-31  0.000000    0.25000    3893.0\n",
       "16        10000 1987-04-30  0.000000    0.23438    3893.0\n",
       "17        10000 1987-05-31  0.000000    0.21875    3893.0\n",
       "18        10000 1987-06-30  0.000000   -1.00000    3893.0\n",
       "19        10001 1985-12-31  0.000000   -1.00000      -1.0\n",
       "20        10001 1986-01-31  0.000000    6.12500     985.0\n",
       "21        10001 1986-02-28  0.000000    6.25000     985.0\n",
       "22        10001 1986-03-31  0.031667    6.31250     985.0\n",
       "23        10001 1986-04-30  0.031667    6.37500     985.0\n",
       "24        10001 1986-05-31  0.031667    6.31250     985.0\n",
       "25        10001 1986-06-30  0.035000    6.12500     985.0\n",
       "26        10001 1986-07-31  0.035000    6.06250     985.0\n",
       "27        10001 1986-08-31  0.035000    6.50000     985.0\n",
       "28        10001 1986-09-30  0.035000    6.37500     991.0\n",
       "29        10001 1986-10-31  0.035000    6.62500     991.0\n",
       "...         ...        ...       ...        ...       ...\n",
       "4098016   93436 2016-01-31  0.000000  191.20000  132056.0\n",
       "4098017   93436 2016-02-29  0.000000  191.92999  132056.0\n",
       "4098018   93436 2016-03-31  0.000000  229.77000  133858.0\n",
       "4098019   93436 2016-04-30  0.000000  240.75999  133945.0\n",
       "4098020   93436 2016-05-31  0.000000  223.23000  147543.0\n",
       "4098021   93436 2016-06-30  0.000000  212.28000  148015.0\n",
       "4098022   93436 2016-07-31  0.000000  234.78999  148693.0\n",
       "4098023   93436 2016-08-31  0.000000  212.00999  149239.0\n",
       "4098024   93436 2016-09-30  0.000000  204.03000  149825.0\n",
       "4098025   93436 2016-10-31  0.000000  197.73000  149891.0\n",
       "4098026   93436 2016-11-30  0.000000  189.39999  149964.0\n",
       "4098027   93436 2016-12-31  0.000000  213.69000  161561.0\n",
       "4098028   93436 2017-01-31  0.000000  251.92999  161670.0\n",
       "4098029   93436 2017-02-28  0.000000  249.99001  161670.0\n",
       "4098030   93436 2017-03-31  0.000000  278.29999  164164.0\n",
       "4098031   93436 2017-04-30  0.000000  314.07001  164260.0\n",
       "4098032   93436 2017-05-31  0.000000  341.01001  164260.0\n",
       "4098033   93436 2017-06-30  0.000000  361.60999  166863.0\n",
       "4098034   93436 2017-07-31  0.000000  323.47000  166887.0\n",
       "4098035   93436 2017-08-31  0.000000  355.89999  166887.0\n",
       "4098036   93436 2017-09-30  0.000000  341.10001  168017.0\n",
       "4098037   93436 2017-10-31  0.000000  331.53000  168067.0\n",
       "4098038   93436 2017-11-30  0.000000  308.85001  168067.0\n",
       "4098039   93436 2017-12-31  0.000000  311.35001  168797.0\n",
       "4098040   93436 2018-01-31  0.000000  354.31000  168797.0\n",
       "4098041   93436 2018-02-28  0.000000  343.06000  168920.0\n",
       "4098042   93436 2018-03-31  0.000000  266.13000  169750.0\n",
       "4098043   93436 2018-04-30  0.000000  293.89999  169794.0\n",
       "4098044   93436 2018-05-31  0.000000  284.73001  169794.0\n",
       "4098045   93436 2018-06-30  0.000000  342.95001  170516.0\n",
       "\n",
       "[4098046 rows x 5 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# make sure the dates are aligned to the last day of the month\n",
    "def match_last_day(date):\n",
    "    date = datetime.datetime.strptime(date,'%Y/%m/%d')\n",
    "    if date not in dates_month:\n",
    "        if date.month<=11:\n",
    "            return datetime.datetime(date.year, date.month+1, 1) - datetime.timedelta(days=1)\n",
    "        else:\n",
    "            return datetime.datetime(date.year+1, 1, 1) - datetime.timedelta(days=1)\n",
    "    else:\n",
    "        return date\n",
    "\n",
    "data['date'] = data['date'].apply(match_last_day)\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "243.67380023002625\n",
      "262.35876083374023\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{10000: [1, 17],\n",
       " 10001: [20, 400],\n",
       " 10002: [403, 728],\n",
       " 10003: [731, 849],\n",
       " 10005: [852, 917],\n",
       " 10006: [919, 1173],\n",
       " 10007: [1176, 1232],\n",
       " 10008: [1235, 1268],\n",
       " 10009: [1271, 1448],\n",
       " 10010: [1451, 1565],\n",
       " 10011: [1568, 1683],\n",
       " 10012: [1686, 1920],\n",
       " 10013: [1923, 1936],\n",
       " 10014: [1941, 2105],\n",
       " 10015: [2108, 2141],\n",
       " 10016: [2144, 2327],\n",
       " 10017: [2330, 2361],\n",
       " 10018: [2364, 2481],\n",
       " 10019: [2484, 2680],\n",
       " 10020: [2683, 2772],\n",
       " 10021: [2775, 2894],\n",
       " 10023: [2897, 2902],\n",
       " 10024: [2905, 2980],\n",
       " 10025: [2983, 3355],\n",
       " 10026: [3359, 3747],\n",
       " 10027: [3749, 3797],\n",
       " 10028: [3800, 4188],\n",
       " 10029: [4190, 4243],\n",
       " 10030: [4245, 4309],\n",
       " 10031: [4313, 4380],\n",
       " 10032: [4383, 4771],\n",
       " 10033: [4773, 4782],\n",
       " 10034: [4786, 4852],\n",
       " 10035: [4857, 5070],\n",
       " 10036: [5073, 5133],\n",
       " 10037: [5136, 5357],\n",
       " 10038: [5360, 5418],\n",
       " 10039: [5421, 5604],\n",
       " 10040: [5607, 5646],\n",
       " 10041: [5649, 5699],\n",
       " 10042: [5702, 5966],\n",
       " 10043: [5969, 6128],\n",
       " 10044: [6131, 6520],\n",
       " 10045: [6522, 6535],\n",
       " 10046: [6538, 6646],\n",
       " 10047: [6649, 6755],\n",
       " 10048: [6758, 6921],\n",
       " 10050: [6923, 6989],\n",
       " 10051: [6997, 7356],\n",
       " 10052: [7387, 7411],\n",
       " 10053: [7414, 7460],\n",
       " 10054: [7463, 7473],\n",
       " 10055: [7476, 7589],\n",
       " 10056: [7592, 7839],\n",
       " 10057: [7841, 8240],\n",
       " 10058: [8243, 8384],\n",
       " 10059: [8387, 8484],\n",
       " 10060: [8487, 8634],\n",
       " 10061: [8637, 8689],\n",
       " 10062: [8693, 8855],\n",
       " 10063: [8858, 8947],\n",
       " 10064: [8950, 9083],\n",
       " 10065: [9085, 9865],\n",
       " 10066: [9867, 10094],\n",
       " 10067: [10098, 10110],\n",
       " 10068: [10113, 10121],\n",
       " 10069: [10125, 10135],\n",
       " 10070: [10138, 10194],\n",
       " 10071: [10196, 10336],\n",
       " 10072: [10338, 10417],\n",
       " 10074: [10420, 10590],\n",
       " 10075: [10593, 10752],\n",
       " 10076: [10755, 10805],\n",
       " 10077: [10808, 10900],\n",
       " 10078: [10903, 11188],\n",
       " 10079: [11191, 11298],\n",
       " 10080: [11301, 11512],\n",
       " 10082: [11515, 11664],\n",
       " 10083: [11668, 11757],\n",
       " 10084: [11760, 11855],\n",
       " 10085: [11858, 12098],\n",
       " 10086: [12101, 12200],\n",
       " 10087: [12203, 12377],\n",
       " 10088: [12380, 12405],\n",
       " 10089: [12408, 12635],\n",
       " 10090: [12638, 12750],\n",
       " 10091: [12753, 12787],\n",
       " 10092: [12790, 13080],\n",
       " 10093: [13082, 13131],\n",
       " 10094: [13134, 13274],\n",
       " 10095: [13277, 13381],\n",
       " 10096: [13384, 13431],\n",
       " 10097: [13434, 13554],\n",
       " 10098: [13557, 13593],\n",
       " 10099: [13596, 13611],\n",
       " 10100: [13615, 13938],\n",
       " 10101: [13941, 13968],\n",
       " 10102: [13970, 14148],\n",
       " 10103: [14151, 14303],\n",
       " 10104: [14306, 14693],\n",
       " 10105: [14695, 14711],\n",
       " 10106: [14714, 14819],\n",
       " 10107: [14822, 15211],\n",
       " 10108: [15213, 15445],\n",
       " 10109: [15448, 15502],\n",
       " 10111: [15505, 15507],\n",
       " 10112: [15510, 15536],\n",
       " 10113: [15539, 15634],\n",
       " 10114: [15636, 15829],\n",
       " 10115: [15832, 15858],\n",
       " 10116: [15861, 16231],\n",
       " 10117: [16234, 16252],\n",
       " 10118: [16255, 16286],\n",
       " 10119: [16289, 16546],\n",
       " 10120: [16551, 16696],\n",
       " 10121: [16699, 16768],\n",
       " 10122: [16771, 16976],\n",
       " 10123: [16979, 17148],\n",
       " 10124: [17151, 17171],\n",
       " 10125: [17174, 17239],\n",
       " 10126: [17241, 17414],\n",
       " 10127: [17417, 17609],\n",
       " 10128: [17612, 17790],\n",
       " 10130: [17793, 17952],\n",
       " 10131: [17955, 18020],\n",
       " 10132: [18023, 18066],\n",
       " 10133: [18069, 18123],\n",
       " 10134: [18126, 18156],\n",
       " 10135: [18159, 18192],\n",
       " 10136: [18195, 18313],\n",
       " 10137: [18315, 18886],\n",
       " 10138: [18889, 19278],\n",
       " 10139: [19280, 19294],\n",
       " 10140: [19298, 19444],\n",
       " 10141: [19448, 19570],\n",
       " 10142: [19573, 19740],\n",
       " 10143: [19743, 20032],\n",
       " 10144: [20035, 20063],\n",
       " 10145: [20065, 20733],\n",
       " 10146: [20735, 20922],\n",
       " 10147: [20925, 21289],\n",
       " 10148: [21293, 21352],\n",
       " 10149: [21355, 21550],\n",
       " 10150: [21553, 21694],\n",
       " 10151: [21697, 21757],\n",
       " 10152: [21760, 21808],\n",
       " 10153: [21810, 22114],\n",
       " 10154: [22117, 22394],\n",
       " 10155: [22397, 22578],\n",
       " 10156: [22581, 22640],\n",
       " 10157: [22644, 22772],\n",
       " 10158: [22775, 22870],\n",
       " 10159: [22872, 22921],\n",
       " 10160: [22924, 23129],\n",
       " 10161: [23131, 23495],\n",
       " 10162: [23500, 23597],\n",
       " 10163: [23600, 23912],\n",
       " 10164: [23915, 23955],\n",
       " 10165: [23958, 23979],\n",
       " 10166: [23982, 24059],\n",
       " 10167: [24062, 24075],\n",
       " 10168: [24078, 24113],\n",
       " 10169: [24116, 24132],\n",
       " 10170: [24135, 24339],\n",
       " 10171: [24342, 24390],\n",
       " 10172: [24393, 24461],\n",
       " 10173: [24464, 24582],\n",
       " 10174: [24585, 24672],\n",
       " 10175: [24675, 24810],\n",
       " 10176: [24813, 24886],\n",
       " 10177: [24889, 25095],\n",
       " 10178: [25098, 25251],\n",
       " 10179: [25254, 25391],\n",
       " 10180: [25394, 25757],\n",
       " 10181: [25759, 25805],\n",
       " 10182: [25808, 26194],\n",
       " 10183: [26196, 26201],\n",
       " 10184: [26203, 26252],\n",
       " 10185: [26255, 26417],\n",
       " 10186: [26420, 26467],\n",
       " 10187: [26470, 26612],\n",
       " 10188: [26614, 26793],\n",
       " 10189: [26797, 26978],\n",
       " 10190: [26981, 27049],\n",
       " 10191: [27052, 27098],\n",
       " 10192: [27101, 27283],\n",
       " 10193: [27286, 27385],\n",
       " 10194: [27388, 27563],\n",
       " 10195: [27566, 27777],\n",
       " 10196: [27779, 27832],\n",
       " 10197: [27835, 27836],\n",
       " 10198: [27839, 27904],\n",
       " 10199: [27907, 28053],\n",
       " 10200: [28056, 28442],\n",
       " 10201: [28444, 28539],\n",
       " 10202: [28541, 28568],\n",
       " 10203: [28571, 28659],\n",
       " 10204: [28662, 28673],\n",
       " 10205: [28676, 28970],\n",
       " 10206: [28973, 29020],\n",
       " 10207: [29023, 29387],\n",
       " 10208: [29389, 29460],\n",
       " 10209: [29462, 29464],\n",
       " 10210: [29467, 29532],\n",
       " 10211: [29535, 29775],\n",
       " 10212: [29778, 29898],\n",
       " 10213: [29901, 30027],\n",
       " 10214: [30030, 30169],\n",
       " 10215: [30172, 30186],\n",
       " 10216: [30189, 30368],\n",
       " 10217: [30370, 30447],\n",
       " 10218: [30450, 30670],\n",
       " 10219: [30673, 30684],\n",
       " 10220: [30687, 30781],\n",
       " 10221: [30783, 30807],\n",
       " 10222: [30810, 30926],\n",
       " 10223: [30929, 30972],\n",
       " 10224: [30975, 31058],\n",
       " 10225: [31060, 31673],\n",
       " 10226: [31676, 31685],\n",
       " 10227: [31688, 31717],\n",
       " 10228: [31720, 31768],\n",
       " 10229: [31771, 31792],\n",
       " 10230: [31795, 31981],\n",
       " 10231: [31984, 32119],\n",
       " 10232: [32122, 32526],\n",
       " 10233: [32527, 32797],\n",
       " 10234: [32801, 32927],\n",
       " 10235: [32930, 33112],\n",
       " 10236: [33115, 33167],\n",
       " 10237: [33170, 33197],\n",
       " 10238: [33200, 33460],\n",
       " 10239: [33464, 33864],\n",
       " 10240: [33866, 33889],\n",
       " 10241: [33891, 34196],\n",
       " 10242: [34200, 34347],\n",
       " 10243: [34350, 34458],\n",
       " 10244: [34461, 34653],\n",
       " 10245: [34656, 34733],\n",
       " 10246: [34736, 34853],\n",
       " 10247: [34856, 35018],\n",
       " 10248: [35022, 35138],\n",
       " 10249: [35141, 35169],\n",
       " 10250: [35172, 35310],\n",
       " 10251: [35313, 35390],\n",
       " 10252: [35393, 35778],\n",
       " 10253: [35780, 36165],\n",
       " 10254: [36167, 36181],\n",
       " 10255: [36184, 36211],\n",
       " 10256: [36214, 36395],\n",
       " 10257: [36398, 36783],\n",
       " 10258: [36785, 37170],\n",
       " 10259: [37172, 37557],\n",
       " 10260: [37559, 37609],\n",
       " 10261: [37612, 37704],\n",
       " 10262: [37707, 37761],\n",
       " 10263: [37764, 37819],\n",
       " 10264: [37822, 37855],\n",
       " 10265: [37858, 37929],\n",
       " 10266: [37932, 38030],\n",
       " 10267: [38033, 38107],\n",
       " 10268: [38109, 38236],\n",
       " 10269: [38239, 38252],\n",
       " 10270: [38256, 38274],\n",
       " 10271: [38277, 38545],\n",
       " 10272: [38548, 38855],\n",
       " 10273: [38858, 38930],\n",
       " 10274: [38933, 38972],\n",
       " 10275: [38975, 39196],\n",
       " 10276: [39198, 39312],\n",
       " 10277: [39315, 39380],\n",
       " 10278: [39383, 39525],\n",
       " 10279: [39528, 39595],\n",
       " 10280: [39599, 39612],\n",
       " 10281: [39615, 39760],\n",
       " 10282: [39763, 39890],\n",
       " 10283: [39893, 39911],\n",
       " 10285: [39914, 40036],\n",
       " 10286: [40039, 40169],\n",
       " 10287: [40172, 40244],\n",
       " 10288: [40247, 40274],\n",
       " 10289: [40277, 40303],\n",
       " 10290: [40306, 40620],\n",
       " 10291: [40623, 40653],\n",
       " 10293: [40656, 40683],\n",
       " 10294: [40686, 41072],\n",
       " 10295: [41074, 41100],\n",
       " 10296: [41103, 41206],\n",
       " 10297: [41209, 41577],\n",
       " 10298: [41580, 41654],\n",
       " 10299: [41657, 42027],\n",
       " 10300: [42031, 42105],\n",
       " 10301: [42108, 42156],\n",
       " 10302: [42159, 42544],\n",
       " 10303: [42546, 42714],\n",
       " 10304: [42717, 43008],\n",
       " 10306: [43012, 43033],\n",
       " 10307: [43036, 43209],\n",
       " 10308: [43212, 43608],\n",
       " 10309: [43610, 43766],\n",
       " 10310: [43769, 43793],\n",
       " 10311: [43796, 43820],\n",
       " 10312: [43823, 43825],\n",
       " 10313: [43827, 43898],\n",
       " 10314: [43901, 43994],\n",
       " 10315: [43997, 44016],\n",
       " 10316: [44019, 44102],\n",
       " 10317: [44105, 44134],\n",
       " 10318: [44137, 44521],\n",
       " 10319: [44523, 44620],\n",
       " 10320: [44623, 44675],\n",
       " 10321: [44678, 44967],\n",
       " 10322: [44970, 45027],\n",
       " 10323: [45030, 45043],\n",
       " 10324: [45046, 45343],\n",
       " 10325: [45347, 45527],\n",
       " 10326: [45530, 45652],\n",
       " 10327: [45655, 45699],\n",
       " 10328: [45702, 45833],\n",
       " 10329: [45836, 45905],\n",
       " 10330: [45908, 45949],\n",
       " 10331: [45952, 45975],\n",
       " 10332: [45978, 46007],\n",
       " 10333: [46010, 46394],\n",
       " 10334: [46396, 46439],\n",
       " 10335: [46442, 46476],\n",
       " 10336: [46480, 46510],\n",
       " 10337: [46513, 46635],\n",
       " 10338: [46638, 46694],\n",
       " 10339: [46697, 46805],\n",
       " 10340: [46808, 46822],\n",
       " 10341: [46825, 47040],\n",
       " 10342: [47043, 47279],\n",
       " 10343: [47282, 47408],\n",
       " 10344: [47411, 47508],\n",
       " 10345: [47511, 47583],\n",
       " 10346: [47586, 47783],\n",
       " 10347: [47786, 47806],\n",
       " 10349: [47809, 47938],\n",
       " 10350: [47943, 48024],\n",
       " 10351: [48026, 48141],\n",
       " 10352: [48144, 48260],\n",
       " 10353: [48263, 48620],\n",
       " 10354: [48622, 48682],\n",
       " 10355: [48685, 49069],\n",
       " 10357: [49071, 49078],\n",
       " 10358: [49081, 49191],\n",
       " 10359: [49195, 49206],\n",
       " 10360: [49209, 49345],\n",
       " 10361: [49348, 49466],\n",
       " 10362: [49469, 49706],\n",
       " 10363: [49709, 50093],\n",
       " 10364: [50094, 50537],\n",
       " 10365: [50540, 50575],\n",
       " 10366: [50578, 50673],\n",
       " 10367: [50676, 50732],\n",
       " 10368: [50737, 50762],\n",
       " 10369: [50765, 50821],\n",
       " 10370: [50824, 50856],\n",
       " 10371: [50859, 51141],\n",
       " 10372: [51143, 51442],\n",
       " 10373: [51445, 51456],\n",
       " 10374: [51459, 51524],\n",
       " 10375: [51527, 51911],\n",
       " 10376: [51913, 52048],\n",
       " 10377: [52051, 52077],\n",
       " 10378: [52080, 52105],\n",
       " 10379: [52108, 52148],\n",
       " 10381: [52151, 52171],\n",
       " 10382: [52174, 52558],\n",
       " 10383: [52560, 52822],\n",
       " 10384: [52825, 52962],\n",
       " 10385: [52965, 53035],\n",
       " 10386: [53038, 53196],\n",
       " 10387: [53199, 53253],\n",
       " 10388: [53256, 53456],\n",
       " 10389: [53459, 53505],\n",
       " 10390: [53508, 53634],\n",
       " 10391: [53637, 53676],\n",
       " 10392: [53679, 53769],\n",
       " 10393: [53772, 53876],\n",
       " 10394: [53879, 54008],\n",
       " 10395: [54012, 54396],\n",
       " 10396: [54398, 54427],\n",
       " 10397: [54432, 54819],\n",
       " 10398: [54821, 54986],\n",
       " 10400: [54989, 55101],\n",
       " 10401: [55103, 55619],\n",
       " 10402: [55623, 55746],\n",
       " 10403: [55749, 55771],\n",
       " 10404: [55774, 56069],\n",
       " 10405: [56072, 56105],\n",
       " 10406: [56108, 56261],\n",
       " 10407: [56264, 56407],\n",
       " 10408: [56410, 56429],\n",
       " 10409: [56432, 56459],\n",
       " 10410: [56462, 56636],\n",
       " 10411: [56639, 56768],\n",
       " 10412: [56771, 56934],\n",
       " 10413: [56938, 56998],\n",
       " 10414: [57002, 57094],\n",
       " 10415: [57097, 57187],\n",
       " 10416: [57190, 57231],\n",
       " 10417: [57234, 57299],\n",
       " 10418: [57303, 57523],\n",
       " 10419: [57526, 57651],\n",
       " 10420: [57654, 57726],\n",
       " 10421: [57729, 58115],\n",
       " 10422: [58117, 58299],\n",
       " 10423: [58302, 58483],\n",
       " 10424: [58486, 58498],\n",
       " 10425: [58501, 58661],\n",
       " 10426: [58664, 58769],\n",
       " 10427: [58771, 58790],\n",
       " 10429: [58793, 58947],\n",
       " 10430: [58950, 58978],\n",
       " 10431: [58981, 59058],\n",
       " 10432: [59061, 59149],\n",
       " 10433: [59152, 59198],\n",
       " 10434: [59202, 59338],\n",
       " 10435: [59341, 59389],\n",
       " 10436: [59391, 59489],\n",
       " 10437: [59492, 59524],\n",
       " 10438: [59527, 59647],\n",
       " 10439: [59650, 59811],\n",
       " 10440: [59814, 59842],\n",
       " 10441: [59845, 59901],\n",
       " 10442: [59904, 60071],\n",
       " 10443: [60074, 60457],\n",
       " 10445: [60459, 60553],\n",
       " 10446: [60556, 60709],\n",
       " 10447: [60712, 60785],\n",
       " 10448: [60788, 60808],\n",
       " 10449: [60811, 61007],\n",
       " 10450: [61010, 61054],\n",
       " 10451: [61057, 61183],\n",
       " 10453: [61188, 61395],\n",
       " 10454: [61398, 61484],\n",
       " 10455: [61487, 61626],\n",
       " 10456: [61629, 61795],\n",
       " 10457: [61798, 61905],\n",
       " 10458: [61908, 62051],\n",
       " 10459: [62054, 62073],\n",
       " 10460: [62075, 62493],\n",
       " 10461: [62497, 62641],\n",
       " 10462: [62644, 62773],\n",
       " 10463: [62776, 63159],\n",
       " 10464: [63161, 63202],\n",
       " 10465: [63205, 63249],\n",
       " 10466: [63252, 63276],\n",
       " 10467: [63279, 63481],\n",
       " 10468: [63484, 63512],\n",
       " 10469: [63515, 63537],\n",
       " 10470: [63540, 63567],\n",
       " 10471: [63570, 63640],\n",
       " 10472: [63643, 63661],\n",
       " 10473: [63664, 63676],\n",
       " 10474: [63679, 63736],\n",
       " 10475: [63739, 63766],\n",
       " 10476: [63769, 63835],\n",
       " 10477: [63838, 63912],\n",
       " 10478: [63915, 63985],\n",
       " 10479: [63987, 64234],\n",
       " 10480: [64237, 64248],\n",
       " 10481: [64251, 64349],\n",
       " 10482: [64352, 64394],\n",
       " 10483: [64397, 64433],\n",
       " 10484: [64436, 64614],\n",
       " 10485: [64617, 64667],\n",
       " 10486: [64670, 64729],\n",
       " 10487: [64732, 65006],\n",
       " 10488: [65010, 65409],\n",
       " 10489: [65412, 65436],\n",
       " 10490: [65439, 65463],\n",
       " 10491: [65466, 65515],\n",
       " 10492: [65518, 65593],\n",
       " 10493: [65597, 65707],\n",
       " 10494: [65710, 65858],\n",
       " 10495: [65860, 66030],\n",
       " 10496: [66034, 66218],\n",
       " 10497: [66221, 66237],\n",
       " 10498: [66240, 66295],\n",
       " 10499: [66298, 66334],\n",
       " 10500: [66337, 66422],\n",
       " 10501: [66425, 66809],\n",
       " 10502: [66811, 66846],\n",
       " 10503: [66849, 66902],\n",
       " 10504: [66905, 66971],\n",
       " 10505: [66974, 67036],\n",
       " 10506: [67039, 67178],\n",
       " 10507: [67181, 67519],\n",
       " 10509: [67521, 67575],\n",
       " 10510: [67578, 67598],\n",
       " 10511: [67602, 67634],\n",
       " 10512: [67638, 67672],\n",
       " 10513: [67675, 67840],\n",
       " 10514: [67844, 68227],\n",
       " 10515: [68229, 68378],\n",
       " 10516: [68380, 69061],\n",
       " 10517: [69063, 69492],\n",
       " 10518: [69494, 69504],\n",
       " 10519: [69507, 69586],\n",
       " 10520: [69589, 69689],\n",
       " 10521: [69692, 69716],\n",
       " 10522: [69719, 69748],\n",
       " 10523: [69751, 69780],\n",
       " 10524: [69782, 69870],\n",
       " 10525: [69874, 69967],\n",
       " 10526: [69970, 70007],\n",
       " 10527: [70011, 70168],\n",
       " 10528: [70171, 70231],\n",
       " 10529: [70237, 70254],\n",
       " 10530: [70257, 70645],\n",
       " 10531: [70647, 70818],\n",
       " 10532: [70820, 70845],\n",
       " 10533: [70848, 70864],\n",
       " 10534: [70867, 70932],\n",
       " 10535: [70935, 70971],\n",
       " 10536: [70974, 71150],\n",
       " 10537: [71153, 71264],\n",
       " 10538: [71266, 71326],\n",
       " 10539: [71328, 71346],\n",
       " 10541: [71349, 71397],\n",
       " 10542: [71402, 71480],\n",
       " 10543: [71483, 71537],\n",
       " 10544: [71540, 71649],\n",
       " 10545: [71653, 71819],\n",
       " 10546: [71822, 71901],\n",
       " 10547: [71904, 72286],\n",
       " 10548: [72288, 72311],\n",
       " 10549: [72314, 72343],\n",
       " 10550: [72346, 72728],\n",
       " 10551: [72730, 72856],\n",
       " 10552: [72859, 72890],\n",
       " 10553: [72893, 72941],\n",
       " 10554: [72945, 72958],\n",
       " 10555: [72961, 73068],\n",
       " 10556: [73072, 73127],\n",
       " 10557: [73130, 73222],\n",
       " 10558: [73225, 73244],\n",
       " 10559: [73246, 73524],\n",
       " 10560: [73527, 73769],\n",
       " 10561: [73772, 73880],\n",
       " 10562: [73884, 74162],\n",
       " 10563: [74165, 74450],\n",
       " 10564: [74453, 74642],\n",
       " 10565: [74645, 74700],\n",
       " 10566: [74703, 74837],\n",
       " 10568: [74840, 75194],\n",
       " 10569: [75197, 75319],\n",
       " 10570: [75322, 75391],\n",
       " 10571: [75394, 75594],\n",
       " 10572: [75597, 75737],\n",
       " 10573: [75741, 75865],\n",
       " 10574: [75868, 76112],\n",
       " 10575: [76115, 76184],\n",
       " 10576: [76187, 76405],\n",
       " 10577: [76408, 76428],\n",
       " 10578: [76431, 76538],\n",
       " 10579: [76541, 76577],\n",
       " 10580: [76580, 76686],\n",
       " 10581: [76690, 76782],\n",
       " 10582: [76785, 76817],\n",
       " 10584: [76820, 76852],\n",
       " 10585: [76855, 76890],\n",
       " 10586: [76893, 77154],\n",
       " 10587: [77157, 77195],\n",
       " 10588: [77198, 77396],\n",
       " 10589: [77399, 77417],\n",
       " 10590: [77420, 77435],\n",
       " 10591: [77437, 77487],\n",
       " 10592: [77490, 77578],\n",
       " 10593: [77581, 77648],\n",
       " 10594: [77651, 77835],\n",
       " 10595: [77839, 77850],\n",
       " 10596: [77853, 77984],\n",
       " 10597: [77987, 78268],\n",
       " 10598: [78271, 78407],\n",
       " 10599: [78410, 78424],\n",
       " 10600: [78427, 78434],\n",
       " 10601: [78437, 78455],\n",
       " 10602: [78458, 78499],\n",
       " 10603: [78502, 78658],\n",
       " 10604: [78660, 79102],\n",
       " 10605: [79105, 79393],\n",
       " 10606: [79396, 79778],\n",
       " 10607: [79780, 79825],\n",
       " 10608: [79828, 79956],\n",
       " 10609: [79959, 79972],\n",
       " 10610: [79975, 80088],\n",
       " 10611: [80091, 80235],\n",
       " 10613: [80238, 80321],\n",
       " 10614: [80324, 80347],\n",
       " 10615: [80350, 80377],\n",
       " 10616: [80380, 80441],\n",
       " 10617: [80444, 80471],\n",
       " 10618: [80474, 80543],\n",
       " 10619: [80546, 80604],\n",
       " 10620: [80606, 80702],\n",
       " 10621: [80705, 80783],\n",
       " 10622: [80786, 80996],\n",
       " 10623: [80999, 81251],\n",
       " 10624: [81254, 81426],\n",
       " 10625: [81429, 81469],\n",
       " 10626: [81473, 81506],\n",
       " 10627: [81509, 81554],\n",
       " 10628: [81557, 81745],\n",
       " 10629: [81748, 82132],\n",
       " 10630: [82134, 82233],\n",
       " 10631: [82236, 82296],\n",
       " 10632: [82299, 82414],\n",
       " 10633: [82417, 82529],\n",
       " 10634: [82532, 82653],\n",
       " 10635: [82656, 82686],\n",
       " 10636: [82689, 82789],\n",
       " 10637: [82792, 82982],\n",
       " 10638: [82985, 83293],\n",
       " 10640: [83296, 83466],\n",
       " 10641: [83469, 83489],\n",
       " 10642: [83492, 83508],\n",
       " 10643: [83511, 83546],\n",
       " 10644: [83549, 83873],\n",
       " 10645: [83876, 84257],\n",
       " 10646: [84259, 84392],\n",
       " 10647: [84394, 84463],\n",
       " 10648: [84466, 84471],\n",
       " 10649: [84474, 84855],\n",
       " 10650: [84857, 85089],\n",
       " 10651: [85093, 85182],\n",
       " 10652: [85185, 85265],\n",
       " 10653: [85268, 85320],\n",
       " 10654: [85323, 85451],\n",
       " 10656: [85454, 86012],\n",
       " 10657: [86014, 86260],\n",
       " 10658: [86263, 86432],\n",
       " 10659: [86435, 86781],\n",
       " 10660: [86784, 86860],\n",
       " 10661: [86863, 87244],\n",
       " 10662: [87246, 87303],\n",
       " 10664: [87306, 87578],\n",
       " 10665: [87581, 87624],\n",
       " 10666: [87628, 87656],\n",
       " 10667: [87659, 88015],\n",
       " 10668: [88018, 88041],\n",
       " 10669: [88044, 88157],\n",
       " 10670: [88160, 88259],\n",
       " 10671: [88261, 88284],\n",
       " 10672: [88288, 88437],\n",
       " 10673: [88440, 88462],\n",
       " 10674: [88465, 88477],\n",
       " 10675: [88480, 88626],\n",
       " 10676: [88629, 88731],\n",
       " 10677: [88734, 88765],\n",
       " 10678: [88769, 88790],\n",
       " 10679: [88793, 88807],\n",
       " 10680: [88810, 89014],\n",
       " 10681: [89017, 89044],\n",
       " 10682: [89048, 89293],\n",
       " 10683: [89296, 89408],\n",
       " 10684: [89411, 89500],\n",
       " 10685: [89503, 89683],\n",
       " 10686: [89686, 89757],\n",
       " 10687: [89760, 89848],\n",
       " 10688: [89851, 89980],\n",
       " 10689: [89983, 90058],\n",
       " 10690: [90061, 90126],\n",
       " 10691: [90129, 90352],\n",
       " 10692: [90355, 90360],\n",
       " 10693: [90363, 90744],\n",
       " 10694: [90746, 90758],\n",
       " 10695: [90761, 90818],\n",
       " 10696: [90821, 91202],\n",
       " 10697: [91204, 91295],\n",
       " 10698: [91297, 91511],\n",
       " 10699: [91519, 91575],\n",
       " 10701: [91578, 91671],\n",
       " 10702: [91674, 91784],\n",
       " 10703: [91787, 91807],\n",
       " 10704: [91810, 91943],\n",
       " 10705: [91946, 92044],\n",
       " 10706: [92047, 92199],\n",
       " 10707: [92202, 92267],\n",
       " 10708: [92271, 92296],\n",
       " 10709: [92299, 92557],\n",
       " 10710: [92560, 92656],\n",
       " 10711: [92658, 92705],\n",
       " 10712: [92708, 92730],\n",
       " 10713: [92733, 92880],\n",
       " 10714: [92883, 93012],\n",
       " 10715: [93015, 93121],\n",
       " 10716: [93124, 93270],\n",
       " 10717: [93273, 93285],\n",
       " 10718: [93288, 93372],\n",
       " 10720: [93375, 93442],\n",
       " 10721: [93445, 93567],\n",
       " 10722: [93570, 93643],\n",
       " 10723: [93646, 93690],\n",
       " 10724: [93693, 93820],\n",
       " 10725: [93823, 94023],\n",
       " 10726: [94026, 94179],\n",
       " 10727: [94181, 94408],\n",
       " 10728: [94415, 94456],\n",
       " 10729: [94459, 94473],\n",
       " 10730: [94476, 94598],\n",
       " 10731: [94601, 94869],\n",
       " 10732: [94872, 94887],\n",
       " 10733: [94890, 94998],\n",
       " 10734: [95001, 95048],\n",
       " 10735: [95050, 95103],\n",
       " 10736: [95106, 95117],\n",
       " 10737: [95120, 95173],\n",
       " 10738: [95176, 95311],\n",
       " 10739: [95314, 95412],\n",
       " 10740: [95415, 95452],\n",
       " 10741: [95455, 95459],\n",
       " 10742: [95462, 95494],\n",
       " 10743: [95496, 95860],\n",
       " 10744: [95863, 95875],\n",
       " 10745: [95878, 96052],\n",
       " 10746: [96056, 96079],\n",
       " 10747: [96082, 96107],\n",
       " 10748: [96110, 96137],\n",
       " 10749: [96140, 96314],\n",
       " 10750: [96317, 96487],\n",
       " 10751: [96489, 96725],\n",
       " 10752: [96731, 96983],\n",
       " 10753: [96986, 97162],\n",
       " 10754: [97165, 97241],\n",
       " 10755: [97244, 97427],\n",
       " 10756: [97430, 97444],\n",
       " 10757: [97448, 97589],\n",
       " 10758: [97592, 97666],\n",
       " 10759: [97669, 97738],\n",
       " 10760: [97741, 97742],\n",
       " 10761: [97745, 97952],\n",
       " 10762: [97955, 98049],\n",
       " 10763: [98053, 98126],\n",
       " 10764: [98129, 98140],\n",
       " 10765: [98143, 98252],\n",
       " 10766: [98255, 98283],\n",
       " 10767: [98286, 98351],\n",
       " 10768: [98354, 98464],\n",
       " 10769: [98467, 98779],\n",
       " 10770: [98783, 98909],\n",
       " 10771: [98912, 99001],\n",
       " 10772: [99004, 99328],\n",
       " 10773: [99331, 99349],\n",
       " 10774: [99352, 99444],\n",
       " 10775: [99447, 99591],\n",
       " 10776: [99594, 99644],\n",
       " 10777: [99647, 100027],\n",
       " 10779: [100029, 100505],\n",
       " 10780: [100507, 100691],\n",
       " 10781: [100694, 101080],\n",
       " 10782: [101082, 101102],\n",
       " 10783: [101105, 101190],\n",
       " 10784: [101193, 101201],\n",
       " 10785: [101204, 101482],\n",
       " 10786: [101484, 101954],\n",
       " 10787: [101957, 102139],\n",
       " 10788: [102142, 102228],\n",
       " 10789: [102231, 102266],\n",
       " 10790: [102269, 102430],\n",
       " 10791: [102433, 102661],\n",
       " 10792: [102664, 102746],\n",
       " 10793: [102749, 102801],\n",
       " 10795: [102804, 102819],\n",
       " 10796: [102822, 102870],\n",
       " 10797: [102873, 102924],\n",
       " 10798: [102927, 102985],\n",
       " 10799: [102988, 103110],\n",
       " 10800: [103112, 103290],\n",
       " 10801: [103292, 103357],\n",
       " 10802: [103359, 103392],\n",
       " 10803: [103394, 103552],\n",
       " 10804: [103554, 103588],\n",
       " 10805: [103590, 103633],\n",
       " 10806: [103636, 103808],\n",
       " 10807: [103810, 103836],\n",
       " 10808: [103839, 104048],\n",
       " 10809: [104051, 104183],\n",
       " 10810: [104186, 104241],\n",
       " 10811: [104244, 104274],\n",
       " 10812: [104277, 104658],\n",
       " 10813: [104660, 104701],\n",
       " 10814: [104704, 104835],\n",
       " 10816: [104838, 104857],\n",
       " 10817: [104860, 104877],\n",
       " 10818: [104880, 105035],\n",
       " 10819: [105038, 105054],\n",
       " 10820: [105057, 105155],\n",
       " 10821: [105158, 105309],\n",
       " 10822: [105312, 105609],\n",
       " 10823: [105611, 106045],\n",
       " 10824: [106048, 106083],\n",
       " 10825: [106086, 106375],\n",
       " 10826: [106378, 106391],\n",
       " 10827: [106394, 106497],\n",
       " 10828: [106500, 106713],\n",
       " 10829: [106716, 106764],\n",
       " 10830: [106769, 106892],\n",
       " 10832: [106895, 106902],\n",
       " 10833: [106905, 106995],\n",
       " 10834: [106998, 107119],\n",
       " 10835: [107122, 107228],\n",
       " 10836: [107231, 107250],\n",
       " 10837: [107253, 107395],\n",
       " 10838: [107398, 107777],\n",
       " 10839: [107779, 107858],\n",
       " 10840: [107861, 107924],\n",
       " 10841: [107928, 108024],\n",
       " 10842: [108027, 108134],\n",
       " 10843: [108137, 108385],\n",
       " 10844: [108388, 108416],\n",
       " 10845: [108419, 108525],\n",
       " 10846: [108528, 108535],\n",
       " 10847: [108538, 108684],\n",
       " 10848: [108688, 108712],\n",
       " 10849: [108715, 108801],\n",
       " 10850: [108804, 108868],\n",
       " 10851: [108871, 108969],\n",
       " 10852: [108972, 108989],\n",
       " 10853: [108992, 109342],\n",
       " 10854: [109345, 109483],\n",
       " 10855: [109486, 109527],\n",
       " 10856: [109531, 109561],\n",
       " 10857: [109564, 109723],\n",
       " 10859: [109726, 109795],\n",
       " 10860: [109798, 110177],\n",
       " 10861: [110179, 110218],\n",
       " 10862: [110221, 110332],\n",
       " 10863: [110335, 110504],\n",
       " 10864: [110508, 110553],\n",
       " 10865: [110556, 110586],\n",
       " 10866: [110588, 111248],\n",
       " 10867: [111250, 111427],\n",
       " 10868: [111430, 111495],\n",
       " 10869: [111498, 111578],\n",
       " 10870: [111581, 111724],\n",
       " 10871: [111727, 111765],\n",
       " 10872: [111768, 111857],\n",
       " 10873: [111860, 111989],\n",
       " 10874: [111991, 112650],\n",
       " 10875: [112652, 112928],\n",
       " 10876: [112931, 113136],\n",
       " 10877: [113139, 113186],\n",
       " 10878: [113189, 113314],\n",
       " 10879: [113317, 113444],\n",
       " 10880: [113447, 113556],\n",
       " 10881: [113559, 113803],\n",
       " 10883: [113806, 113838],\n",
       " 10884: [113841, 113872],\n",
       " 10885: [113875, 113915],\n",
       " 10886: [113918, 114297],\n",
       " 10887: [114299, 114313],\n",
       " 10888: [114316, 114404],\n",
       " 10889: [114407, 114552],\n",
       " 10890: [114554, 115213],\n",
       " 10891: [115215, 115318],\n",
       " 10892: [115322, 115702],\n",
       " 10893: [115704, 115836],\n",
       " 10894: [115838, 115898],\n",
       " 10895: [115900, 115911],\n",
       " 10896: [115914, 115956],\n",
       " 10897: [115959, 116039],\n",
       " 10898: [116042, 116098],\n",
       " 10899: [116101, 116127],\n",
       " 10900: [116130, 116187],\n",
       " 10901: [116190, 116570],\n",
       " 10902: [116572, 116733],\n",
       " 10904: [116736, 116768],\n",
       " 10905: [116771, 116906],\n",
       " 10906: [116909, 117128],\n",
       " 10907: [117133, 117148],\n",
       " 10908: [117151, 117361],\n",
       " 10909: [117364, 117742],\n",
       " 10910: [117744, 117818],\n",
       " 10911: [117820, 117971],\n",
       " 10912: [117974, 118196],\n",
       " 10913: [118199, 118580],\n",
       " 10914: [118582, 118708],\n",
       " 10915: [118711, 118721],\n",
       " 10916: [118725, 118962],\n",
       " 10917: [118965, 119011],\n",
       " 10918: [119014, 119042],\n",
       " 10919: [119045, 119051],\n",
       " 10920: [119055, 119072],\n",
       " 10921: [119075, 119090],\n",
       " 10922: [119093, 119133],\n",
       " 10923: [119136, 119164],\n",
       " 10924: [119167, 119432],\n",
       " 10925: [119435, 119452],\n",
       " 10926: [119455, 119695],\n",
       " 10927: [119698, 119728],\n",
       " 10928: [119731, 119918],\n",
       " 10929: [119921, 120069],\n",
       " 10930: [120073, 120110],\n",
       " 10931: [120113, 120219],\n",
       " 10932: [120223, 120603],\n",
       " 10933: [120605, 120983],\n",
       " 10934: [120985, 121016],\n",
       " 10935: [121019, 121242],\n",
       " 10936: [121245, 121256],\n",
       " 10937: [121258, 121476],\n",
       " 10938: [121478, 121575],\n",
       " 10939: [121578, 121587],\n",
       " 10940: [121590, 121599],\n",
       " 10941: [121602, 121711],\n",
       " 10942: [121714, 122027],\n",
       " 10943: [122030, 122071],\n",
       " 10944: [122074, 122135],\n",
       " 10945: [122138, 122186],\n",
       " 10947: [122189, 122273],\n",
       " 10948: [122276, 122294],\n",
       " 10949: [122297, 122320],\n",
       " 10950: [122323, 122411],\n",
       " 10951: [122414, 122442],\n",
       " 10952: [122445, 122823],\n",
       " 10953: [122825, 122882],\n",
       " 10955: [122885, 122911],\n",
       " 10956: [122914, 122979],\n",
       " 10957: [122982, 123109],\n",
       " 10958: [123112, 123149],\n",
       " 10959: [123153, 123253],\n",
       " 10960: [123256, 123297],\n",
       " 10961: [123300, 123492],\n",
       " 10963: [123495, 123661],\n",
       " 10964: [123664, 123742],\n",
       " 10965: [123745, 123829],\n",
       " 10966: [123832, 124210],\n",
       " 10967: [124212, 124305],\n",
       " 10968: [124308, 124381],\n",
       " 10969: [124385, 124493],\n",
       " 10970: [124495, 124694],\n",
       " 10971: [124697, 124796],\n",
       " 10972: [124799, 125007],\n",
       " 10973: [125010, 125027],\n",
       " 10974: [125030, 125283],\n",
       " 10975: [125286, 125355],\n",
       " 10976: [125358, 125436],\n",
       " 10977: [125439, 125648],\n",
       " 10978: [125651, 125790],\n",
       " 10979: [125793, 125799],\n",
       " 10980: [125802, 125872],\n",
       " 10981: [125876, 125967],\n",
       " 10982: [125970, 125980],\n",
       " 10983: [125983, 126087],\n",
       " 10984: [126091, 126321],\n",
       " 10985: [126324, 126354],\n",
       " 10986: [126359, 126459],\n",
       " 10987: [126462, 126651],\n",
       " 10988: [126654, 126704],\n",
       " 10989: [126706, 127154],\n",
       " 10990: [127157, 127304],\n",
       " 10991: [127307, 127383],\n",
       " 10992: [127386, 127459],\n",
       " 10993: [127462, 127551],\n",
       " 10994: [127554, 127827],\n",
       " 10995: [127830, 127924],\n",
       " 10996: [127927, 127936],\n",
       " 10998: [127939, 128091],\n",
       " 10999: [128094, 128301],\n",
       " 11000: [128304, 128444],\n",
       " 11001: [128446, 128543],\n",
       " 11002: [128546, 128557],\n",
       " 11003: [128560, 128656],\n",
       " 11004: [128658, 128742],\n",
       " 11005: [128745, 128930],\n",
       " 11006: [128933, 129312],\n",
       " 11007: [129314, 129374],\n",
       " 11008: [129377, 129418],\n",
       " 11010: [129420, 129637],\n",
       " 11011: [129640, 129732],\n",
       " 11012: [129735, 129915],\n",
       " 11013: [129918, 129936],\n",
       " 11014: [129939, 130053],\n",
       " 11015: [130056, 130085],\n",
       " 11016: [130088, 130132],\n",
       " 11017: [130134, 130487],\n",
       " 11018: [130490, 130868],\n",
       " 11019: [130870, 130953],\n",
       " 11020: [130957, 131110],\n",
       " 11021: [131113, 131159],\n",
       " 11022: [131162, 131256],\n",
       " 11023: [131259, 131301],\n",
       " 11024: [131304, 131322],\n",
       " 11026: [131325, 131408],\n",
       " 11027: [131411, 131426],\n",
       " 11028: [131429, 131559],\n",
       " 11029: [131562, 131574],\n",
       " 11030: [131577, 131591],\n",
       " 11031: [131594, 131702],\n",
       " 11032: [131705, 131759],\n",
       " 11033: [131761, 131818],\n",
       " 11034: [131821, 131912],\n",
       " 11035: [131915, 132019],\n",
       " 11036: [132022, 132161],\n",
       " 11037: [132164, 132277],\n",
       " 11038: [132280, 132586],\n",
       " 11039: [132589, 132645],\n",
       " ...}"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Record dates\n",
    "cur_tic = data['PERMNO'][0]\n",
    "start_ind = 0\n",
    "dates_ind = {}\n",
    "dates = {}\n",
    "start_time = time.time()\n",
    "\n",
    "for i in range(len(data)):\n",
    "    if data['PERMNO'][i]==cur_tic:\n",
    "        pass\n",
    "    else:\n",
    "        dates_ind.update({cur_tic:[start_ind, i-1]})\n",
    "        dates.update({cur_tic:[data['date'][start_ind],data['date'][i-1]]})\n",
    "        start_ind=i\n",
    "        cur_tic = data['PERMNO'][i]\n",
    "        \n",
    "dates_ind.update({cur_tic:[start_ind, i]})\n",
    "dates.update({cur_tic:[data['date'][start_ind],data['date'][i]]})\n",
    "print(time.time()-start_time)\n",
    "\n",
    "# For some stocks, the shares outstanding and the prices may be missing, \n",
    "# if so we need to mark the date when they have both price and shares outstanding\n",
    "\n",
    "for ticker in dates_ind:\n",
    "    # Get the start right\n",
    "    si, ei = dates_ind[ticker][0], dates_ind[ticker][1]\n",
    "    while(data['PRC'].iloc[si]<0 or data['SHROUT'].iloc[si]<0):\n",
    "        si+=1\n",
    "    while(data['PRC'].iloc[ei]<0 or data['SHROUT'].iloc[ei]<0):\n",
    "        ei-=1\n",
    "    dates_ind.update({ticker:[si,ei]})\n",
    "    dates.update({ticker:[data['date'][si],data['date'][ei]]})\n",
    "    \n",
    "print(time.time()-start_time)\n",
    "dates_ind"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[4097949, 4098045]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates_ind[93436]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "scrolled": true
   },
   "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>PERMNO</th>\n",
       "      <th>date</th>\n",
       "      <th>DIVAMT</th>\n",
       "      <th>PRC</th>\n",
       "      <th>SHROUT</th>\n",
       "      <th>Rolling 12m Vol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.37500</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.25000</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.43750</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.10938</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.09375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.84375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.09375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.03125</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.78125</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.82813</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.51563</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.435027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.409594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.25000</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.437770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.23438</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.273181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.21875</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.074227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10001</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.25000</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.06250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.50000</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>991.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.62500</td>\n",
       "      <td>991.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098016</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.20000</td>\n",
       "      <td>132056.0</td>\n",
       "      <td>25.202296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098017</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.92999</td>\n",
       "      <td>132056.0</td>\n",
       "      <td>26.555348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098018</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>229.77000</td>\n",
       "      <td>133858.0</td>\n",
       "      <td>27.700047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098019</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>240.75999</td>\n",
       "      <td>133945.0</td>\n",
       "      <td>24.803335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098020</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>223.23000</td>\n",
       "      <td>147543.0</td>\n",
       "      <td>24.781435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098021</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.28000</td>\n",
       "      <td>148015.0</td>\n",
       "      <td>24.435724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098022</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>234.78999</td>\n",
       "      <td>148693.0</td>\n",
       "      <td>22.354128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098023</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.00999</td>\n",
       "      <td>149239.0</td>\n",
       "      <td>19.308349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098024</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>204.03000</td>\n",
       "      <td>149825.0</td>\n",
       "      <td>18.121559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098025</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>197.73000</td>\n",
       "      <td>149891.0</td>\n",
       "      <td>16.794403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098026</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>189.39999</td>\n",
       "      <td>149964.0</td>\n",
       "      <td>17.482701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098027</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>213.69000</td>\n",
       "      <td>161561.0</td>\n",
       "      <td>18.578174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098028</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>251.92999</td>\n",
       "      <td>161670.0</td>\n",
       "      <td>16.842055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098029</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>249.99001</td>\n",
       "      <td>161670.0</td>\n",
       "      <td>18.908695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098030</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>278.29999</td>\n",
       "      <td>164164.0</td>\n",
       "      <td>19.350054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098031</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>314.07001</td>\n",
       "      <td>164260.0</td>\n",
       "      <td>24.902570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098032</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.01001</td>\n",
       "      <td>164260.0</td>\n",
       "      <td>34.856163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098033</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>361.60999</td>\n",
       "      <td>166863.0</td>\n",
       "      <td>45.898203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098034</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>323.47000</td>\n",
       "      <td>166887.0</td>\n",
       "      <td>55.500332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098035</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>355.89999</td>\n",
       "      <td>166887.0</td>\n",
       "      <td>58.278696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098036</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.10001</td>\n",
       "      <td>168017.0</td>\n",
       "      <td>61.584522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098037</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>331.53000</td>\n",
       "      <td>168067.0</td>\n",
       "      <td>60.352930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098038</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>308.85001</td>\n",
       "      <td>168067.0</td>\n",
       "      <td>55.382738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098039</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>311.35001</td>\n",
       "      <td>168797.0</td>\n",
       "      <td>45.110519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098040</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>354.31000</td>\n",
       "      <td>168797.0</td>\n",
       "      <td>35.521304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098041</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>343.06000</td>\n",
       "      <td>168920.0</td>\n",
       "      <td>31.650373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098042</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>266.13000</td>\n",
       "      <td>169750.0</td>\n",
       "      <td>23.167902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098043</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>293.89999</td>\n",
       "      <td>169794.0</td>\n",
       "      <td>25.567578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098044</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>284.73001</td>\n",
       "      <td>169794.0</td>\n",
       "      <td>27.132966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098045</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>342.95001</td>\n",
       "      <td>170516.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4098046 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         PERMNO       date    DIVAMT        PRC    SHROUT  Rolling 12m Vol\n",
       "0         10000 1985-12-31  0.000000   -1.00000      -1.0              NaN\n",
       "1         10000 1986-01-31  0.000000    4.37500    3680.0              NaN\n",
       "2         10000 1986-02-28  0.000000    3.25000    3680.0              NaN\n",
       "3         10000 1986-03-31  0.000000    4.43750    3680.0              NaN\n",
       "4         10000 1986-04-30  0.000000    4.00000    3793.0              NaN\n",
       "5         10000 1986-05-31  0.000000    3.10938    3793.0              NaN\n",
       "6         10000 1986-06-30  0.000000    3.09375    3793.0              NaN\n",
       "7         10000 1986-07-31  0.000000    2.84375    3793.0              NaN\n",
       "8         10000 1986-08-31  0.000000    1.09375    3793.0              NaN\n",
       "9         10000 1986-09-30  0.000000    1.03125    3793.0              NaN\n",
       "10        10000 1986-10-31  0.000000    0.78125    3843.0              NaN\n",
       "11        10000 1986-11-30  0.000000    0.82813    3843.0              NaN\n",
       "12        10000 1986-12-31  0.000000    0.51563    3843.0              NaN\n",
       "13        10000 1987-01-31  0.000000    0.40625    3893.0         1.435027\n",
       "14        10000 1987-02-28  0.000000    0.40625    3893.0         1.409594\n",
       "15        10000 1987-03-31  0.000000    0.25000    3893.0         1.437770\n",
       "16        10000 1987-04-30  0.000000    0.23438    3893.0         1.273181\n",
       "17        10000 1987-05-31  0.000000    0.21875    3893.0         1.074227\n",
       "18        10000 1987-06-30  0.000000   -1.00000    3893.0              NaN\n",
       "19        10001 1985-12-31  0.000000   -1.00000      -1.0              NaN\n",
       "20        10001 1986-01-31  0.000000    6.12500     985.0              NaN\n",
       "21        10001 1986-02-28  0.000000    6.25000     985.0              NaN\n",
       "22        10001 1986-03-31  0.031667    6.31250     985.0              NaN\n",
       "23        10001 1986-04-30  0.031667    6.37500     985.0              NaN\n",
       "24        10001 1986-05-31  0.031667    6.31250     985.0              NaN\n",
       "25        10001 1986-06-30  0.035000    6.12500     985.0              NaN\n",
       "26        10001 1986-07-31  0.035000    6.06250     985.0              NaN\n",
       "27        10001 1986-08-31  0.035000    6.50000     985.0              NaN\n",
       "28        10001 1986-09-30  0.035000    6.37500     991.0              NaN\n",
       "29        10001 1986-10-31  0.035000    6.62500     991.0              NaN\n",
       "...         ...        ...       ...        ...       ...              ...\n",
       "4098016   93436 2016-01-31  0.000000  191.20000  132056.0        25.202296\n",
       "4098017   93436 2016-02-29  0.000000  191.92999  132056.0        26.555348\n",
       "4098018   93436 2016-03-31  0.000000  229.77000  133858.0        27.700047\n",
       "4098019   93436 2016-04-30  0.000000  240.75999  133945.0        24.803335\n",
       "4098020   93436 2016-05-31  0.000000  223.23000  147543.0        24.781435\n",
       "4098021   93436 2016-06-30  0.000000  212.28000  148015.0        24.435724\n",
       "4098022   93436 2016-07-31  0.000000  234.78999  148693.0        22.354128\n",
       "4098023   93436 2016-08-31  0.000000  212.00999  149239.0        19.308349\n",
       "4098024   93436 2016-09-30  0.000000  204.03000  149825.0        18.121559\n",
       "4098025   93436 2016-10-31  0.000000  197.73000  149891.0        16.794403\n",
       "4098026   93436 2016-11-30  0.000000  189.39999  149964.0        17.482701\n",
       "4098027   93436 2016-12-31  0.000000  213.69000  161561.0        18.578174\n",
       "4098028   93436 2017-01-31  0.000000  251.92999  161670.0        16.842055\n",
       "4098029   93436 2017-02-28  0.000000  249.99001  161670.0        18.908695\n",
       "4098030   93436 2017-03-31  0.000000  278.29999  164164.0        19.350054\n",
       "4098031   93436 2017-04-30  0.000000  314.07001  164260.0        24.902570\n",
       "4098032   93436 2017-05-31  0.000000  341.01001  164260.0        34.856163\n",
       "4098033   93436 2017-06-30  0.000000  361.60999  166863.0        45.898203\n",
       "4098034   93436 2017-07-31  0.000000  323.47000  166887.0        55.500332\n",
       "4098035   93436 2017-08-31  0.000000  355.89999  166887.0        58.278696\n",
       "4098036   93436 2017-09-30  0.000000  341.10001  168017.0        61.584522\n",
       "4098037   93436 2017-10-31  0.000000  331.53000  168067.0        60.352930\n",
       "4098038   93436 2017-11-30  0.000000  308.85001  168067.0        55.382738\n",
       "4098039   93436 2017-12-31  0.000000  311.35001  168797.0        45.110519\n",
       "4098040   93436 2018-01-31  0.000000  354.31000  168797.0        35.521304\n",
       "4098041   93436 2018-02-28  0.000000  343.06000  168920.0        31.650373\n",
       "4098042   93436 2018-03-31  0.000000  266.13000  169750.0        23.167902\n",
       "4098043   93436 2018-04-30  0.000000  293.89999  169794.0        25.567578\n",
       "4098044   93436 2018-05-31  0.000000  284.73001  169794.0        27.132966\n",
       "4098045   93436 2018-06-30  0.000000  342.95001  170516.0              NaN\n",
       "\n",
       "[4098046 rows x 6 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "32.13521480560303\n",
      "2082\n",
      "2085\n",
      "2088\n",
      "2087\n",
      "2095\n",
      "2095\n",
      "2100\n",
      "2109\n",
      "2106\n",
      "2117\n",
      "2122\n",
      "2129\n",
      "2134\n",
      "2138\n",
      "2142\n",
      "2142\n",
      "2146\n",
      "2149\n",
      "2151\n",
      "2157\n",
      "2156\n",
      "2164\n",
      "2167\n",
      "2178\n",
      "2182\n",
      "2189\n",
      "2185\n",
      "2186\n",
      "2189\n",
      "2194\n",
      "2192\n",
      "2188\n",
      "2189\n",
      "2188\n",
      "2195\n",
      "2196\n",
      "2203\n",
      "2210\n",
      "2216\n",
      "2215\n",
      "2216\n",
      "2212\n",
      "2215\n",
      "2218\n",
      "2215\n",
      "2217\n",
      "2212\n",
      "2217\n",
      "2209\n",
      "2215\n",
      "2216\n",
      "2221\n",
      "2224\n",
      "2225\n",
      "2217\n",
      "2218\n",
      "2228\n",
      "2233\n",
      "2235\n",
      "2228\n",
      "2216\n",
      "2225\n",
      "2229\n",
      "2232\n",
      "2239\n",
      "2243\n",
      "2248\n",
      "2262\n",
      "2270\n",
      "2283\n",
      "2290\n",
      "2299\n",
      "2313\n",
      "2330\n",
      "2340\n",
      "2349\n",
      "2354\n",
      "2358\n",
      "2370\n",
      "2391\n",
      "2402\n",
      "2410\n",
      "2417\n",
      "2422\n",
      "2428\n",
      "2430\n",
      "2441\n",
      "2455\n",
      "2467\n",
      "2466\n",
      "2477\n",
      "2482\n",
      "2486\n",
      "2499\n",
      "2512\n",
      "2518\n",
      "2535\n",
      "2548\n",
      "2562\n",
      "2573\n",
      "2588\n",
      "2604\n",
      "2618\n",
      "2636\n",
      "2651\n",
      "2664\n",
      "2678\n",
      "2684\n",
      "2695\n",
      "2707\n",
      "2725\n",
      "2735\n",
      "2750\n",
      "5755\n",
      "5794\n",
      "5806\n",
      "5786\n",
      "5775\n",
      "5731\n",
      "5707\n",
      "5685\n",
      "5659\n",
      "5643\n",
      "5636\n",
      "5585\n",
      "5533\n",
      "5512\n",
      "5499\n",
      "5489\n",
      "5477\n",
      "5451\n",
      "5427\n",
      "5412\n",
      "5378\n",
      "5360\n",
      "5337\n",
      "5322\n",
      "5303\n",
      "5283\n",
      "5282\n",
      "5275\n",
      "5273\n",
      "5278\n",
      "5277\n",
      "5280\n",
      "5276\n",
      "5279\n",
      "5278\n",
      "5270\n",
      "5262\n",
      "5262\n",
      "5260\n",
      "5259\n",
      "5259\n",
      "5257\n",
      "5265\n",
      "5278\n",
      "5270\n",
      "5278\n",
      "5291\n",
      "5289\n",
      "5277\n",
      "5270\n",
      "5260\n",
      "5258\n",
      "5266\n",
      "5253\n",
      "5260\n",
      "5244\n",
      "5225\n",
      "5218\n",
      "5210\n",
      "5208\n",
      "5186\n",
      "5160\n",
      "5151\n",
      "5142\n",
      "5141\n",
      "5126\n",
      "5107\n",
      "5098\n",
      "5097\n",
      "5105\n",
      "5101\n",
      "5093\n",
      "5090\n",
      "5071\n",
      "5054\n",
      "5062\n",
      "5051\n",
      "5056\n",
      "5040\n",
      "5020\n",
      "5012\n",
      "5008\n",
      "5012\n",
      "5009\n",
      "5016\n",
      "5012\n",
      "5026\n",
      "5034\n",
      "5026\n",
      "5028\n",
      "5053\n",
      "5079\n",
      "5087\n",
      "5110\n",
      "5138\n",
      "5160\n",
      "5193\n",
      "5219\n",
      "5232\n",
      "5271\n",
      "5324\n",
      "5377\n",
      "5421\n",
      "5476\n",
      "5505\n",
      "5517\n",
      "5536\n",
      "5585\n",
      "5603\n",
      "5605\n",
      "5609\n",
      "5599\n",
      "5578\n",
      "5575\n",
      "5570\n",
      "5568\n",
      "5559\n",
      "5535\n",
      "5516\n",
      "5716\n",
      "5726\n",
      "5709\n",
      "5720\n",
      "5761\n",
      "5792\n",
      "5838\n",
      "5904\n",
      "5981\n",
      "6050\n",
      "6103\n",
      "6186\n",
      "6261\n",
      "6350\n",
      "6380\n",
      "6415\n",
      "6457\n",
      "6481\n",
      "6494\n",
      "6502\n",
      "6507\n",
      "6528\n",
      "6495\n",
      "6487\n",
      "6486\n",
      "6491\n",
      "6475\n",
      "6469\n",
      "6464\n",
      "6467\n",
      "6467\n",
      "6467\n",
      "6473\n",
      "6462\n",
      "6472\n",
      "6492\n",
      "6497\n",
      "6510\n",
      "6473\n",
      "6482\n",
      "6493\n",
      "6507\n",
      "6557\n",
      "6636\n",
      "6686\n",
      "6722\n",
      "6753\n",
      "6799\n",
      "6838\n",
      "6847\n",
      "6863\n",
      "6934\n",
      "6992\n",
      "7034\n",
      "7103\n",
      "7180\n",
      "7256\n",
      "7306\n",
      "7369\n",
      "7378\n",
      "7347\n",
      "7322\n",
      "7303\n",
      "7283\n",
      "7269\n",
      "7258\n",
      "7226\n",
      "7222\n",
      "7223\n",
      "7209\n",
      "7175\n",
      "7195\n",
      "7168\n",
      "7123\n",
      "7115\n",
      "7096\n",
      "7073\n",
      "7066\n",
      "7049\n",
      "7023\n",
      "7029\n",
      "6996\n",
      "6969\n",
      "6962\n",
      "6957\n",
      "6959\n",
      "6944\n",
      "6939\n",
      "6943\n",
      "6946\n",
      "6950\n",
      "6937\n",
      "6940\n",
      "6925\n",
      "6904\n",
      "6879\n",
      "6853\n",
      "6843\n",
      "6817\n",
      "6803\n",
      "6804\n",
      "6789\n",
      "6801\n",
      "6830\n",
      "6835\n",
      "6855\n",
      "6876\n",
      "6906\n",
      "6940\n",
      "6943\n",
      "6958\n",
      "6976\n",
      "7027\n",
      "7037\n",
      "7036\n",
      "7042\n",
      "7015\n",
      "7008\n",
      "7018\n",
      "7043\n",
      "7075\n",
      "7127\n",
      "7138\n",
      "7180\n",
      "7242\n",
      "7296\n",
      "7364\n",
      "7438\n",
      "7491\n",
      "7549\n",
      "7623\n",
      "7696\n",
      "7798\n",
      "7912\n",
      "7951\n",
      "8011\n",
      "8092\n",
      "8129\n",
      "8188\n",
      "8240\n",
      "8273\n",
      "8295\n",
      "8296\n",
      "8331\n",
      "8354\n",
      "8352\n",
      "8332\n",
      "8341\n",
      "8343\n",
      "8369\n",
      "8372\n",
      "8404\n",
      "8429\n",
      "8461\n",
      "8476\n",
      "8518\n",
      "8559\n",
      "8621\n",
      "8602\n",
      "8632\n",
      "8714\n",
      "8762\n",
      "8843\n",
      "8917\n",
      "8949\n",
      "8993\n",
      "9017\n",
      "9091\n",
      "9163\n",
      "9185\n",
      "9180\n",
      "9200\n",
      "9192\n",
      "9198\n",
      "9216\n",
      "9217\n",
      "9227\n",
      "9212\n",
      "9205\n",
      "9246\n",
      "9288\n",
      "9252\n",
      "9231\n",
      "9226\n",
      "9198\n",
      "9197\n",
      "9206\n",
      "9190\n",
      "9169\n",
      "9116\n",
      "9049\n",
      "8945\n",
      "8908\n",
      "8860\n",
      "8809\n",
      "8781\n",
      "8709\n",
      "8677\n",
      "8668\n",
      "8658\n",
      "8651\n",
      "8623\n",
      "8598\n",
      "8605\n",
      "8586\n",
      "8555\n",
      "8518\n",
      "8521\n",
      "8505\n",
      "8514\n",
      "8515\n",
      "8508\n",
      "8517\n",
      "8538\n",
      "8517\n",
      "8465\n",
      "8399\n",
      "8319\n",
      "8238\n",
      "8169\n",
      "8117\n",
      "8034\n",
      "7962\n",
      "7909\n",
      "7865\n",
      "7775\n",
      "7739\n",
      "7713\n",
      "7657\n",
      "7622\n",
      "7574\n",
      "7533\n",
      "7505\n",
      "7450\n",
      "7422\n",
      "7395\n",
      "7371\n",
      "7312\n",
      "7282\n",
      "7246\n",
      "7250\n",
      "7219\n",
      "7174\n",
      "7138\n",
      "7077\n",
      "7044\n",
      "7009\n",
      "6984\n",
      "6952\n",
      "6923\n",
      "6918\n",
      "6910\n",
      "6895\n",
      "6893\n",
      "6895\n",
      "6895\n",
      "6904\n",
      "6886\n",
      "6884\n",
      "6887\n",
      "6891\n",
      "6880\n",
      "6886\n",
      "6900\n",
      "6897\n",
      "6906\n",
      "6892\n",
      "6899\n",
      "6900\n",
      "6890\n",
      "6889\n",
      "6911\n",
      "6906\n",
      "6903\n",
      "6900\n",
      "6893\n",
      "6899\n",
      "6897\n",
      "6872\n",
      "6869\n",
      "6877\n",
      "6874\n",
      "6880\n",
      "6931\n",
      "6921\n",
      "6920\n",
      "6944\n",
      "6960\n",
      "6972\n",
      "6981\n",
      "6982\n",
      "7023\n",
      "7046\n",
      "7042\n",
      "7080\n",
      "7078\n",
      "7080\n",
      "7072\n",
      "7060\n",
      "7076\n",
      "7093\n",
      "7104\n",
      "7105\n",
      "7081\n",
      "7064\n",
      "7051\n",
      "7052\n",
      "7047\n",
      "7029\n",
      "7007\n",
      "6970\n",
      "6926\n",
      "6920\n",
      "6883\n",
      "6856\n",
      "6812\n",
      "6771\n",
      "6744\n",
      "6706\n",
      "6686\n",
      "6670\n",
      "6652\n",
      "6657\n",
      "6654\n",
      "6666\n",
      "6662\n",
      "6667\n",
      "6666\n",
      "6658\n",
      "6683\n",
      "6674\n",
      "6685\n",
      "6682\n",
      "6679\n",
      "6686\n",
      "6681\n",
      "6686\n",
      "6682\n",
      "6699\n",
      "6713\n",
      "6719\n",
      "6728\n",
      "6768\n",
      "6775\n",
      "6771\n",
      "6778\n",
      "6787\n",
      "6779\n",
      "6782\n",
      "6758\n",
      "6764\n",
      "6794\n",
      "6809\n",
      "6823\n",
      "6828\n",
      "6823\n",
      "6815\n",
      "6777\n",
      "6763\n",
      "6735\n",
      "6731\n",
      "6717\n",
      "6715\n",
      "6710\n",
      "6696\n",
      "6704\n",
      "6726\n",
      "6743\n",
      "6758\n",
      "6777\n",
      "6802\n",
      "6831\n",
      "6845\n",
      "6849\n",
      "6882\n",
      "6892\n",
      "6916\n",
      "6944\n",
      "6972\n",
      "7012\n",
      "7045\n",
      "7062\n",
      "7069\n",
      "7096\n",
      "7119\n",
      "7111\n",
      "7097\n",
      "7107\n",
      "7114\n",
      "7132\n",
      "7158\n",
      "7207\n",
      "7259\n",
      "7258\n",
      "7262\n",
      "7261\n",
      "7290\n",
      "7287\n",
      "7265\n",
      "7245\n",
      "7229\n",
      "7228\n",
      "7224\n",
      "7240\n",
      "7240\n",
      "7195\n",
      "7203\n",
      "7217\n",
      "7210\n",
      "7213\n",
      "7225\n",
      "7226\n",
      "7223\n",
      "7236\n",
      "7248\n",
      "7269\n",
      "7262\n",
      "7266\n",
      "7240\n",
      "7257\n",
      "7312\n",
      "7310\n",
      "7340\n",
      "7366\n",
      "7374\n",
      "7360\n",
      "7384\n",
      "7413\n",
      "32.180094957351685\n"
     ]
    }
   ],
   "source": [
    "# For a given date, find the stocks that have prices on that day\n",
    "start_time = time.time()\n",
    "store = {}\n",
    "\n",
    "# find_stocks will save the tickers of the stocks available at given date and return a list of them\n",
    "def find_stocks(target_date):\n",
    "    stock_lst = []\n",
    "    for ticker in dates:\n",
    "        if dates[ticker][0] <= target_date and dates[ticker][1] >= target_date:\n",
    "            stock_lst.append(ticker)\n",
    "    return stock_lst\n",
    "\n",
    "\n",
    "for i in dates_month:\n",
    "    store.update({i:find_stocks(i)})\n",
    "    \n",
    "print(time.time()-start_time)\n",
    "\n",
    "# The size of each portfolio\n",
    "for date in store.keys():\n",
    "    print(len(store[date]))\n",
    "    \n",
    "print(time.time()-start_time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Find the record of the company of a certain month\n",
    "start_time = time.time()\n",
    "\n",
    "def find_component(date,\n",
    "                   start_time):\n",
    "    date_df = []\n",
    "    for stock in store[date]:\n",
    "        data_slice = pd.DataFrame(data[dates_ind[stock][0]:dates_ind[stock][1]+1])\n",
    "        n = np.where(data_slice['date']==date)\n",
    "        date_df.append(data_slice.iloc[n])\n",
    "    print(time.time()-start_time)\n",
    "    return pd.concat(date_df)\n",
    "#frame = find_component(datetime.datetime(2018, 6, 30, 0, 0), start_time)7\n",
    "#frame.sort_values(['DIVAMT'],ascending=False)[0:1000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1157.5029287338257\n"
     ]
    }
   ],
   "source": [
    "# Record the rolling average of vol of the stock prices in a new column\n",
    "start_time = time.time()\n",
    "def get_rolling_vol(rolling_vol_length): \n",
    "    volw = rolling_vol_length # rolling vol window\n",
    "    rvols = [np.nan for i in range(len(data))] # the list of rolling vols, the first volw of the vols should are used as np.nan\n",
    "    \n",
    "    for ticker in dates_ind:\n",
    "        si, ei = dates_ind[ticker][0], dates_ind[ticker][1]\n",
    "        if ei-si>volw:\n",
    "            for index in range(si+volw, ei+1):\n",
    "                rvols[index] = np.std(data['PRC'][index-volw:index])\n",
    "    return rvols\n",
    "rolling_vol = get_rolling_vol(12)\n",
    "print(time.time()-start_time) # About 9 min"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "data['Rolling 12m Vol'] = rolling_vol\n",
    "data.to_csv('with_vols.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>PERMNO</th>\n",
       "      <th>date</th>\n",
       "      <th>DIVAMT</th>\n",
       "      <th>PRC</th>\n",
       "      <th>SHROUT</th>\n",
       "      <th>Rolling 12m Vol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.37500</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.25000</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.43750</td>\n",
       "      <td>3680.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>4.00000</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.10938</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.09375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.84375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.09375</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.03125</td>\n",
       "      <td>3793.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.78125</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.82813</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>10000</td>\n",
       "      <td>1986-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.51563</td>\n",
       "      <td>3843.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.435027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.40625</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.409594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.25000</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.437770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.23438</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.273181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.21875</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>1.074227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10000</td>\n",
       "      <td>1987-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>3893.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10001</td>\n",
       "      <td>1985-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.00000</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.25000</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-03-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-04-30</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-05-31</td>\n",
       "      <td>0.031667</td>\n",
       "      <td>6.31250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-06-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.12500</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-07-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.06250</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-08-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.50000</td>\n",
       "      <td>985.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-09-30</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.37500</td>\n",
       "      <td>991.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10001</td>\n",
       "      <td>1986-10-31</td>\n",
       "      <td>0.035000</td>\n",
       "      <td>6.62500</td>\n",
       "      <td>991.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098016</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.20000</td>\n",
       "      <td>132056.0</td>\n",
       "      <td>25.202296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098017</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>191.92999</td>\n",
       "      <td>132056.0</td>\n",
       "      <td>26.555348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098018</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>229.77000</td>\n",
       "      <td>133858.0</td>\n",
       "      <td>27.700047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098019</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>240.75999</td>\n",
       "      <td>133945.0</td>\n",
       "      <td>24.803335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098020</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>223.23000</td>\n",
       "      <td>147543.0</td>\n",
       "      <td>24.781435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098021</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.28000</td>\n",
       "      <td>148015.0</td>\n",
       "      <td>24.435724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098022</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>234.78999</td>\n",
       "      <td>148693.0</td>\n",
       "      <td>22.354128</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098023</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>212.00999</td>\n",
       "      <td>149239.0</td>\n",
       "      <td>19.308349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098024</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>204.03000</td>\n",
       "      <td>149825.0</td>\n",
       "      <td>18.121559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098025</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>197.73000</td>\n",
       "      <td>149891.0</td>\n",
       "      <td>16.794403</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098026</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>189.39999</td>\n",
       "      <td>149964.0</td>\n",
       "      <td>17.482701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098027</th>\n",
       "      <td>93436</td>\n",
       "      <td>2016-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>213.69000</td>\n",
       "      <td>161561.0</td>\n",
       "      <td>18.578174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098028</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>251.92999</td>\n",
       "      <td>161670.0</td>\n",
       "      <td>16.842055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098029</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>249.99001</td>\n",
       "      <td>161670.0</td>\n",
       "      <td>18.908695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098030</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>278.29999</td>\n",
       "      <td>164164.0</td>\n",
       "      <td>19.350054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098031</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>314.07001</td>\n",
       "      <td>164260.0</td>\n",
       "      <td>24.902570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098032</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.01001</td>\n",
       "      <td>164260.0</td>\n",
       "      <td>34.856163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098033</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>361.60999</td>\n",
       "      <td>166863.0</td>\n",
       "      <td>45.898203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098034</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>323.47000</td>\n",
       "      <td>166887.0</td>\n",
       "      <td>55.500332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098035</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>355.89999</td>\n",
       "      <td>166887.0</td>\n",
       "      <td>58.278696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098036</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-09-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>341.10001</td>\n",
       "      <td>168017.0</td>\n",
       "      <td>61.584522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098037</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>331.53000</td>\n",
       "      <td>168067.0</td>\n",
       "      <td>60.352930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098038</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-11-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>308.85001</td>\n",
       "      <td>168067.0</td>\n",
       "      <td>55.382738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098039</th>\n",
       "      <td>93436</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>311.35001</td>\n",
       "      <td>168797.0</td>\n",
       "      <td>45.110519</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098040</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-01-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>354.31000</td>\n",
       "      <td>168797.0</td>\n",
       "      <td>35.521304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098041</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-02-28</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>343.06000</td>\n",
       "      <td>168920.0</td>\n",
       "      <td>31.650373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098042</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-03-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>266.13000</td>\n",
       "      <td>169750.0</td>\n",
       "      <td>23.167902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098043</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-04-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>293.89999</td>\n",
       "      <td>169794.0</td>\n",
       "      <td>25.567578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098044</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>284.73001</td>\n",
       "      <td>169794.0</td>\n",
       "      <td>27.132966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4098045</th>\n",
       "      <td>93436</td>\n",
       "      <td>2018-06-30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>342.95001</td>\n",
       "      <td>170516.0</td>\n",
       "      <td>29.209012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4098046 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         PERMNO       date    DIVAMT        PRC    SHROUT  Rolling 12m Vol\n",
       "0         10000 1985-12-31  0.000000   -1.00000      -1.0              NaN\n",
       "1         10000 1986-01-31  0.000000    4.37500    3680.0              NaN\n",
       "2         10000 1986-02-28  0.000000    3.25000    3680.0              NaN\n",
       "3         10000 1986-03-31  0.000000    4.43750    3680.0              NaN\n",
       "4         10000 1986-04-30  0.000000    4.00000    3793.0              NaN\n",
       "5         10000 1986-05-31  0.000000    3.10938    3793.0              NaN\n",
       "6         10000 1986-06-30  0.000000    3.09375    3793.0              NaN\n",
       "7         10000 1986-07-31  0.000000    2.84375    3793.0              NaN\n",
       "8         10000 1986-08-31  0.000000    1.09375    3793.0              NaN\n",
       "9         10000 1986-09-30  0.000000    1.03125    3793.0              NaN\n",
       "10        10000 1986-10-31  0.000000    0.78125    3843.0              NaN\n",
       "11        10000 1986-11-30  0.000000    0.82813    3843.0              NaN\n",
       "12        10000 1986-12-31  0.000000    0.51563    3843.0              NaN\n",
       "13        10000 1987-01-31  0.000000    0.40625    3893.0         1.435027\n",
       "14        10000 1987-02-28  0.000000    0.40625    3893.0         1.409594\n",
       "15        10000 1987-03-31  0.000000    0.25000    3893.0         1.437770\n",
       "16        10000 1987-04-30  0.000000    0.23438    3893.0         1.273181\n",
       "17        10000 1987-05-31  0.000000    0.21875    3893.0         1.074227\n",
       "18        10000 1987-06-30  0.000000   -1.00000    3893.0              NaN\n",
       "19        10001 1985-12-31  0.000000   -1.00000      -1.0              NaN\n",
       "20        10001 1986-01-31  0.000000    6.12500     985.0              NaN\n",
       "21        10001 1986-02-28  0.000000    6.25000     985.0              NaN\n",
       "22        10001 1986-03-31  0.031667    6.31250     985.0              NaN\n",
       "23        10001 1986-04-30  0.031667    6.37500     985.0              NaN\n",
       "24        10001 1986-05-31  0.031667    6.31250     985.0              NaN\n",
       "25        10001 1986-06-30  0.035000    6.12500     985.0              NaN\n",
       "26        10001 1986-07-31  0.035000    6.06250     985.0              NaN\n",
       "27        10001 1986-08-31  0.035000    6.50000     985.0              NaN\n",
       "28        10001 1986-09-30  0.035000    6.37500     991.0              NaN\n",
       "29        10001 1986-10-31  0.035000    6.62500     991.0              NaN\n",
       "...         ...        ...       ...        ...       ...              ...\n",
       "4098016   93436 2016-01-31  0.000000  191.20000  132056.0        25.202296\n",
       "4098017   93436 2016-02-29  0.000000  191.92999  132056.0        26.555348\n",
       "4098018   93436 2016-03-31  0.000000  229.77000  133858.0        27.700047\n",
       "4098019   93436 2016-04-30  0.000000  240.75999  133945.0        24.803335\n",
       "4098020   93436 2016-05-31  0.000000  223.23000  147543.0        24.781435\n",
       "4098021   93436 2016-06-30  0.000000  212.28000  148015.0        24.435724\n",
       "4098022   93436 2016-07-31  0.000000  234.78999  148693.0        22.354128\n",
       "4098023   93436 2016-08-31  0.000000  212.00999  149239.0        19.308349\n",
       "4098024   93436 2016-09-30  0.000000  204.03000  149825.0        18.121559\n",
       "4098025   93436 2016-10-31  0.000000  197.73000  149891.0        16.794403\n",
       "4098026   93436 2016-11-30  0.000000  189.39999  149964.0        17.482701\n",
       "4098027   93436 2016-12-31  0.000000  213.69000  161561.0        18.578174\n",
       "4098028   93436 2017-01-31  0.000000  251.92999  161670.0        16.842055\n",
       "4098029   93436 2017-02-28  0.000000  249.99001  161670.0        18.908695\n",
       "4098030   93436 2017-03-31  0.000000  278.29999  164164.0        19.350054\n",
       "4098031   93436 2017-04-30  0.000000  314.07001  164260.0        24.902570\n",
       "4098032   93436 2017-05-31  0.000000  341.01001  164260.0        34.856163\n",
       "4098033   93436 2017-06-30  0.000000  361.60999  166863.0        45.898203\n",
       "4098034   93436 2017-07-31  0.000000  323.47000  166887.0        55.500332\n",
       "4098035   93436 2017-08-31  0.000000  355.89999  166887.0        58.278696\n",
       "4098036   93436 2017-09-30  0.000000  341.10001  168017.0        61.584522\n",
       "4098037   93436 2017-10-31  0.000000  331.53000  168067.0        60.352930\n",
       "4098038   93436 2017-11-30  0.000000  308.85001  168067.0        55.382738\n",
       "4098039   93436 2017-12-31  0.000000  311.35001  168797.0        45.110519\n",
       "4098040   93436 2018-01-31  0.000000  354.31000  168797.0        35.521304\n",
       "4098041   93436 2018-02-28  0.000000  343.06000  168920.0        31.650373\n",
       "4098042   93436 2018-03-31  0.000000  266.13000  169750.0        23.167902\n",
       "4098043   93436 2018-04-30  0.000000  293.89999  169794.0        25.567578\n",
       "4098044   93436 2018-05-31  0.000000  284.73001  169794.0        27.132966\n",
       "4098045   93436 2018-06-30  0.000000  342.95001  170516.0        29.209012\n",
       "\n",
       "[4098046 rows x 6 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}