Stocks Analyser.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "http://192.168.1.4:4040\n"
     ]
    }
   ],
   "source": [
    "import sys, glob, os\n",
    "SPARK_HOME=os.environ['SPARK_HOME']\n",
    "sys.path.append(SPARK_HOME + \"/python\")\n",
    "sys.path.append(glob.glob(SPARK_HOME + \"/python/lib/py4j*.zip\")[0])\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# Create spark session\n",
    "spark = SparkSession.builder.enableHiveSupport().getOrCreate()\n",
    "sc = spark.sparkContext\n",
    "sql = spark.sql\n",
    "print(sc.uiWebUrl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql.window import Window, WindowSpec"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks = spark.read.format(\"csv\").options(header = True, inferSchema = True).load(\"/data/stocks.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+---------+---------+---------+---------+---------+---------+------+\n",
      "|               date|     open|     high|      low|    close|   volume| adjclose|symbol|\n",
      "+-------------------+---------+---------+---------+---------+---------+---------+------+\n",
      "|2000-07-17 00:00:00|  95.4375|     97.5|    92.75|   96.625|3508100.0|74.269199|  XLNX|\n",
      "|2000-07-17 00:00:00|   22.625|    22.75|  22.4375|  22.5625| 201600.0| 13.48614|    ES|\n",
      "|2000-07-17 00:00:00| 6.750002| 6.937503|    6.375|      6.5|1235700.0| 5.241649|   CHK|\n",
      "|2000-07-17 00:00:00|19.812501|  20.1875|19.500001|  20.1875|1434100.0| 3.806147|    NI|\n",
      "|2000-07-17 00:00:00|     30.5|  30.6875|     30.0| 30.03125| 254600.0| 19.81183|   SNA|\n",
      "|2000-07-17 00:00:00|44.749996|45.062498|44.500004|45.000009| 535200.0|17.400773|  FOXA|\n",
      "|2000-07-17 00:00:00|   19.625|   19.625|    19.25|   19.375| 309500.0|13.768835|     R|\n",
      "|2000-07-17 00:00:00|  16.6562|  16.6875|   16.125|    16.25|5507200.0| 1.755466|  ROST|\n",
      "|2000-07-17 00:00:00|    56.25|    57.25|  56.0625|   56.125|7941200.0| 18.31076|    PG|\n",
      "|2000-07-17 00:00:00|54.000326|54.000326|52.500318|53.375325|3725000.0|71.068871|   TYC|\n",
      "|2000-07-17 00:00:00|    58.75|   58.875|  57.8125|     58.0| 182700.0|37.544123|    XL|\n",
      "|2000-07-17 00:00:00|47.500132|47.500132|45.750135|46.343886|4898700.0|17.662922|     F|\n",
      "|2000-07-17 00:00:00|     84.0|     84.5|   82.625|82.671883|2861800.0| 23.88973|   CVX|\n",
      "|2000-07-17 00:00:00|     22.5|    22.75|   22.375|    22.75| 423600.0| 5.942444|   PPL|\n",
      "|2000-07-17 00:00:00|  37.4375|  37.5625|  36.5625|  37.4375| 738800.0|24.832407|   TRV|\n",
      "|2000-07-17 00:00:00|76.874999|80.937504|76.249993|78.250003|6166200.0| 50.37851|     A|\n",
      "|2000-07-17 00:00:00|     26.5|  26.6875|  26.3125|     26.5| 335200.0| 6.240835|   LNT|\n",
      "|2000-07-17 00:00:00|  23.9375|  24.0625|     23.5|  23.9375| 648400.0|  23.9375|   AZO|\n",
      "|2000-07-17 00:00:00|   60.875|  60.9375|    60.25|60.531239|1464800.0|22.017028|   UTX|\n",
      "|2000-07-17 00:00:00|   3.5625| 3.625005| 3.312495| 3.437505| 340000.0|  2.20411|   RRC|\n",
      "+-------------------+---------+---------+---------+---------+---------+---------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "stocks.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks.createOrReplaceTempView(\"stocks\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks_agg = spark.sql(\"\"\"\n",
    "select symbol, avg(volume) avg_vol \n",
    "    from stocks \n",
    "where year(date) = 2016 group by symbol \n",
    "order by avg_vol desc\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+--------------------+\n",
      "|symbol|             avg_vol|\n",
      "+------+--------------------+\n",
      "|   BAC|1.0995368974358974E8|\n",
      "|   FCX|4.7979558333333336E7|\n",
      "|   CHK|4.1622735256410256E7|\n",
      "|  AAPL|4.0944183974358976E7|\n",
      "|    GE|3.7751663461538464E7|\n",
      "|     F| 3.743219743589743E7|\n",
      "|   PFE|3.5777183974358976E7|\n",
      "|  MSFT| 3.419444807692308E7|\n",
      "|    FB|2.8902566025641024E7|\n",
      "|    MU|2.7260807692307692E7|\n",
      "|    AA|2.6758177564102564E7|\n",
      "|   MRO| 2.649072564102564E7|\n",
      "|  CSCO|2.6301159615384616E7|\n",
      "|  INTC|2.4185176282051284E7|\n",
      "|     T| 2.371588846153846E7|\n",
      "|     C| 2.338251282051282E7|\n",
      "|   KMI|2.1680207692307692E7|\n",
      "|    RF|2.0861117307692308E7|\n",
      "|   WFC|1.9612389743589744E7|\n",
      "|   SWN|1.8398848717948716E7|\n",
      "+------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "stocks_agg.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "stocks_agg.coalesce(1).write.format(\"json\").mode(\"overwrite\").save(\"/tmp/stocks-summary\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>summary</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>adjclose</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>54.39150019132181</td>\n",
       "      <td>55.03561088277836</td>\n",
       "      <td>53.73182845876957</td>\n",
       "      <td>54.40520389624489</td>\n",
       "      <td>5438876.668468767</td>\n",
       "      <td>43.02996494097698</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>60.84195032737054</td>\n",
       "      <td>61.436667351177796</td>\n",
       "      <td>60.21132386902276</td>\n",
       "      <td>60.839881015722526</td>\n",
       "      <td>1.40008932519909E7</td>\n",
       "      <td>63.518893483888206</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>0.21</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.18312</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>1475.0</td>\n",
       "      <td>1476.52002</td>\n",
       "      <td>1462.75</td>\n",
       "      <td>1469.560059</td>\n",
       "      <td>1.8554102E9</td>\n",
       "      <td>1553.982779</td>\n",
       "      <td>ZION</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary               open                high                low  \\\n",
       "0   count            1857092             1857092            1857092   \n",
       "1    mean  54.39150019132181   55.03561088277836  53.73182845876957   \n",
       "2  stddev  60.84195032737054  61.436667351177796  60.21132386902276   \n",
       "3     min               0.21                0.22               0.19   \n",
       "4     max             1475.0          1476.52002            1462.75   \n",
       "\n",
       "                close              volume            adjclose   symbol  \n",
       "0             1857092             1857092             1857092  1857092  \n",
       "1   54.40520389624489   5438876.668468767   43.02996494097698     None  \n",
       "2  60.839881015722526  1.40008932519909E7  63.518893483888206     None  \n",
       "3                0.21                 0.0             0.18312        A  \n",
       "4         1469.560059         1.8554102E9         1553.982779     ZION  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks.describe().toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('date', 'timestamp'),\n",
       " ('open', 'double'),\n",
       " ('high', 'double'),\n",
       " ('low', 'double'),\n",
       " ('close', 'double'),\n",
       " ('volume', 'double'),\n",
       " ('adjclose', 'double'),\n",
       " ('symbol', 'string')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "      <th>2016-08-03</th>\n",
       "      <th>2016-08-04</th>\n",
       "      <th>2016-08-05</th>\n",
       "      <th>2016-08-08</th>\n",
       "      <th>2016-08-09</th>\n",
       "      <th>2016-08-10</th>\n",
       "      <th>2016-08-11</th>\n",
       "      <th>2016-08-12</th>\n",
       "      <th>2016-08-15</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LEN</td>\n",
       "      <td>0.007489</td>\n",
       "      <td>0.000874</td>\n",
       "      <td>0.014524</td>\n",
       "      <td>-0.007452</td>\n",
       "      <td>0.006880</td>\n",
       "      <td>-0.002132</td>\n",
       "      <td>0.004045</td>\n",
       "      <td>-0.008063</td>\n",
       "      <td>0.008744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GIS</td>\n",
       "      <td>-0.018841</td>\n",
       "      <td>0.007000</td>\n",
       "      <td>0.000709</td>\n",
       "      <td>0.001986</td>\n",
       "      <td>0.002544</td>\n",
       "      <td>0.001409</td>\n",
       "      <td>-0.000422</td>\n",
       "      <td>0.000140</td>\n",
       "      <td>0.000141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K</td>\n",
       "      <td>-0.009891</td>\n",
       "      <td>-0.000243</td>\n",
       "      <td>-0.001569</td>\n",
       "      <td>0.000362</td>\n",
       "      <td>0.000724</td>\n",
       "      <td>-0.000843</td>\n",
       "      <td>0.003487</td>\n",
       "      <td>0.000359</td>\n",
       "      <td>0.000120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALXN</td>\n",
       "      <td>0.033251</td>\n",
       "      <td>-0.019902</td>\n",
       "      <td>0.009349</td>\n",
       "      <td>-0.007729</td>\n",
       "      <td>-0.004058</td>\n",
       "      <td>-0.019511</td>\n",
       "      <td>0.006097</td>\n",
       "      <td>0.003761</td>\n",
       "      <td>0.014086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SPGI</td>\n",
       "      <td>0.004083</td>\n",
       "      <td>-0.003582</td>\n",
       "      <td>0.000828</td>\n",
       "      <td>-0.005216</td>\n",
       "      <td>0.001162</td>\n",
       "      <td>-0.006550</td>\n",
       "      <td>0.002414</td>\n",
       "      <td>-0.002413</td>\n",
       "      <td>-0.000831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AVY</td>\n",
       "      <td>-0.006304</td>\n",
       "      <td>-0.000517</td>\n",
       "      <td>0.007965</td>\n",
       "      <td>0.004979</td>\n",
       "      <td>-0.001395</td>\n",
       "      <td>-0.000508</td>\n",
       "      <td>-0.006219</td>\n",
       "      <td>0.001665</td>\n",
       "      <td>-0.002934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AIV</td>\n",
       "      <td>-0.004200</td>\n",
       "      <td>-0.003774</td>\n",
       "      <td>0.008897</td>\n",
       "      <td>0.011018</td>\n",
       "      <td>0.002180</td>\n",
       "      <td>-0.014115</td>\n",
       "      <td>-0.013413</td>\n",
       "      <td>0.004012</td>\n",
       "      <td>0.009756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>RF</td>\n",
       "      <td>0.014590</td>\n",
       "      <td>-0.002208</td>\n",
       "      <td>0.018438</td>\n",
       "      <td>-0.001064</td>\n",
       "      <td>-0.003191</td>\n",
       "      <td>-0.011740</td>\n",
       "      <td>0.004320</td>\n",
       "      <td>0.002172</td>\n",
       "      <td>0.029064</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MMM</td>\n",
       "      <td>-0.001902</td>\n",
       "      <td>-0.006426</td>\n",
       "      <td>0.001795</td>\n",
       "      <td>-0.001789</td>\n",
       "      <td>0.000112</td>\n",
       "      <td>0.003930</td>\n",
       "      <td>0.010721</td>\n",
       "      <td>-0.006613</td>\n",
       "      <td>-0.002431</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>PPG</td>\n",
       "      <td>0.001921</td>\n",
       "      <td>-0.001918</td>\n",
       "      <td>-0.001721</td>\n",
       "      <td>0.000287</td>\n",
       "      <td>0.000192</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.000763</td>\n",
       "      <td>-0.005927</td>\n",
       "      <td>0.003169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>PKI</td>\n",
       "      <td>-0.000896</td>\n",
       "      <td>-0.002328</td>\n",
       "      <td>-0.014553</td>\n",
       "      <td>-0.003832</td>\n",
       "      <td>0.001831</td>\n",
       "      <td>-0.005289</td>\n",
       "      <td>0.001281</td>\n",
       "      <td>-0.003845</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CI</td>\n",
       "      <td>0.003301</td>\n",
       "      <td>0.028874</td>\n",
       "      <td>-0.004416</td>\n",
       "      <td>0.001951</td>\n",
       "      <td>-0.011888</td>\n",
       "      <td>0.004724</td>\n",
       "      <td>-0.012247</td>\n",
       "      <td>0.050843</td>\n",
       "      <td>-0.000452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AXP</td>\n",
       "      <td>0.005197</td>\n",
       "      <td>0.000939</td>\n",
       "      <td>0.015184</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.006684</td>\n",
       "      <td>-0.010546</td>\n",
       "      <td>0.007385</td>\n",
       "      <td>0.003376</td>\n",
       "      <td>0.005516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>WEC</td>\n",
       "      <td>-0.014695</td>\n",
       "      <td>0.002827</td>\n",
       "      <td>-0.016965</td>\n",
       "      <td>-0.005104</td>\n",
       "      <td>0.000641</td>\n",
       "      <td>-0.000321</td>\n",
       "      <td>0.003054</td>\n",
       "      <td>-0.003192</td>\n",
       "      <td>-0.021750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>IRM</td>\n",
       "      <td>-0.006420</td>\n",
       "      <td>-0.056205</td>\n",
       "      <td>-0.007618</td>\n",
       "      <td>-0.026462</td>\n",
       "      <td>-0.001900</td>\n",
       "      <td>-0.004341</td>\n",
       "      <td>0.007343</td>\n",
       "      <td>0.003240</td>\n",
       "      <td>-0.001884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>PFG</td>\n",
       "      <td>0.009916</td>\n",
       "      <td>0.003719</td>\n",
       "      <td>0.016153</td>\n",
       "      <td>0.006329</td>\n",
       "      <td>-0.000629</td>\n",
       "      <td>-0.013854</td>\n",
       "      <td>0.001912</td>\n",
       "      <td>0.000856</td>\n",
       "      <td>0.000637</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>SNA</td>\n",
       "      <td>-0.001230</td>\n",
       "      <td>-0.007567</td>\n",
       "      <td>0.015980</td>\n",
       "      <td>-0.011596</td>\n",
       "      <td>0.007423</td>\n",
       "      <td>-0.001346</td>\n",
       "      <td>0.001533</td>\n",
       "      <td>-0.016969</td>\n",
       "      <td>-0.001300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>PM</td>\n",
       "      <td>-0.005923</td>\n",
       "      <td>-0.003623</td>\n",
       "      <td>-0.000707</td>\n",
       "      <td>-0.002328</td>\n",
       "      <td>-0.002736</td>\n",
       "      <td>0.002730</td>\n",
       "      <td>-0.006332</td>\n",
       "      <td>-0.005325</td>\n",
       "      <td>-0.005346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>EA</td>\n",
       "      <td>0.025698</td>\n",
       "      <td>-0.001659</td>\n",
       "      <td>0.011401</td>\n",
       "      <td>-0.007264</td>\n",
       "      <td>-0.004050</td>\n",
       "      <td>0.000382</td>\n",
       "      <td>-0.012682</td>\n",
       "      <td>-0.007308</td>\n",
       "      <td>0.024359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>OXY</td>\n",
       "      <td>0.011560</td>\n",
       "      <td>0.003243</td>\n",
       "      <td>-0.007008</td>\n",
       "      <td>0.002291</td>\n",
       "      <td>-0.013390</td>\n",
       "      <td>-0.009865</td>\n",
       "      <td>0.005553</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.002003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>ESRX</td>\n",
       "      <td>0.007692</td>\n",
       "      <td>-0.003152</td>\n",
       "      <td>0.003547</td>\n",
       "      <td>-0.006153</td>\n",
       "      <td>0.006849</td>\n",
       "      <td>-0.008633</td>\n",
       "      <td>0.004484</td>\n",
       "      <td>0.003556</td>\n",
       "      <td>0.008631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>BLK</td>\n",
       "      <td>0.002942</td>\n",
       "      <td>-0.003862</td>\n",
       "      <td>0.012072</td>\n",
       "      <td>-0.007609</td>\n",
       "      <td>-0.010336</td>\n",
       "      <td>-0.008335</td>\n",
       "      <td>0.003003</td>\n",
       "      <td>0.004384</td>\n",
       "      <td>0.007446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>UA</td>\n",
       "      <td>0.040931</td>\n",
       "      <td>-0.000770</td>\n",
       "      <td>0.018316</td>\n",
       "      <td>-0.013896</td>\n",
       "      <td>-0.015280</td>\n",
       "      <td>-0.012629</td>\n",
       "      <td>0.033838</td>\n",
       "      <td>0.002684</td>\n",
       "      <td>0.022513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>DUK</td>\n",
       "      <td>-0.003278</td>\n",
       "      <td>0.002824</td>\n",
       "      <td>-0.009059</td>\n",
       "      <td>0.004862</td>\n",
       "      <td>0.000827</td>\n",
       "      <td>-0.002856</td>\n",
       "      <td>-0.000119</td>\n",
       "      <td>-0.007725</td>\n",
       "      <td>-0.023323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ULTA</td>\n",
       "      <td>0.008374</td>\n",
       "      <td>0.009097</td>\n",
       "      <td>0.003865</td>\n",
       "      <td>-0.006954</td>\n",
       "      <td>0.001798</td>\n",
       "      <td>0.002668</td>\n",
       "      <td>0.007386</td>\n",
       "      <td>0.006603</td>\n",
       "      <td>-0.006660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>HAS</td>\n",
       "      <td>0.007480</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.011112</td>\n",
       "      <td>0.000980</td>\n",
       "      <td>-0.003545</td>\n",
       "      <td>0.005880</td>\n",
       "      <td>0.007785</td>\n",
       "      <td>-0.004469</td>\n",
       "      <td>-0.007523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>TROW</td>\n",
       "      <td>-0.006613</td>\n",
       "      <td>0.000580</td>\n",
       "      <td>0.001434</td>\n",
       "      <td>-0.005424</td>\n",
       "      <td>-0.014187</td>\n",
       "      <td>-0.003477</td>\n",
       "      <td>0.001597</td>\n",
       "      <td>-0.002033</td>\n",
       "      <td>-0.001890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>FLIR</td>\n",
       "      <td>-0.009094</td>\n",
       "      <td>-0.000947</td>\n",
       "      <td>-0.000629</td>\n",
       "      <td>-0.012881</td>\n",
       "      <td>0.001588</td>\n",
       "      <td>0.001903</td>\n",
       "      <td>-0.008536</td>\n",
       "      <td>-0.010243</td>\n",
       "      <td>0.001940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>MAT</td>\n",
       "      <td>0.009251</td>\n",
       "      <td>0.009466</td>\n",
       "      <td>0.023207</td>\n",
       "      <td>0.003831</td>\n",
       "      <td>-0.017024</td>\n",
       "      <td>0.003567</td>\n",
       "      <td>0.007084</td>\n",
       "      <td>0.002059</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>OI</td>\n",
       "      <td>0.019737</td>\n",
       "      <td>0.013477</td>\n",
       "      <td>0.006846</td>\n",
       "      <td>-0.001046</td>\n",
       "      <td>-0.024530</td>\n",
       "      <td>-0.000533</td>\n",
       "      <td>-0.000529</td>\n",
       "      <td>-0.015940</td>\n",
       "      <td>0.013426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>471</th>\n",
       "      <td>CTXS</td>\n",
       "      <td>0.016297</td>\n",
       "      <td>-0.009906</td>\n",
       "      <td>0.007587</td>\n",
       "      <td>-0.003168</td>\n",
       "      <td>0.003418</td>\n",
       "      <td>0.002342</td>\n",
       "      <td>0.017745</td>\n",
       "      <td>0.004379</td>\n",
       "      <td>0.003442</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>CFG</td>\n",
       "      <td>0.017671</td>\n",
       "      <td>0.007127</td>\n",
       "      <td>0.019172</td>\n",
       "      <td>-0.001703</td>\n",
       "      <td>0.004705</td>\n",
       "      <td>-0.008529</td>\n",
       "      <td>0.004723</td>\n",
       "      <td>0.002153</td>\n",
       "      <td>0.005124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>JWN</td>\n",
       "      <td>0.043380</td>\n",
       "      <td>0.012048</td>\n",
       "      <td>0.027850</td>\n",
       "      <td>0.025424</td>\n",
       "      <td>-0.024434</td>\n",
       "      <td>0.009587</td>\n",
       "      <td>0.011915</td>\n",
       "      <td>0.007451</td>\n",
       "      <td>0.023777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>474</th>\n",
       "      <td>GRMN</td>\n",
       "      <td>0.013153</td>\n",
       "      <td>0.012813</td>\n",
       "      <td>0.000182</td>\n",
       "      <td>-0.005979</td>\n",
       "      <td>-0.012175</td>\n",
       "      <td>0.008422</td>\n",
       "      <td>-0.000903</td>\n",
       "      <td>-0.007207</td>\n",
       "      <td>0.003793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>475</th>\n",
       "      <td>MCO</td>\n",
       "      <td>0.003165</td>\n",
       "      <td>-0.007640</td>\n",
       "      <td>0.005167</td>\n",
       "      <td>-0.002482</td>\n",
       "      <td>-0.002297</td>\n",
       "      <td>-0.007560</td>\n",
       "      <td>0.002879</td>\n",
       "      <td>-0.004235</td>\n",
       "      <td>0.011066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>NAVI</td>\n",
       "      <td>0.021418</td>\n",
       "      <td>0.031884</td>\n",
       "      <td>0.011134</td>\n",
       "      <td>-0.014413</td>\n",
       "      <td>-0.012509</td>\n",
       "      <td>-0.042224</td>\n",
       "      <td>-0.004380</td>\n",
       "      <td>-0.008817</td>\n",
       "      <td>0.018423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>GPC</td>\n",
       "      <td>0.002688</td>\n",
       "      <td>0.017873</td>\n",
       "      <td>0.004742</td>\n",
       "      <td>-0.006852</td>\n",
       "      <td>-0.004348</td>\n",
       "      <td>0.002576</td>\n",
       "      <td>0.003443</td>\n",
       "      <td>-0.003828</td>\n",
       "      <td>0.010149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>478</th>\n",
       "      <td>PWR</td>\n",
       "      <td>0.010334</td>\n",
       "      <td>-0.039721</td>\n",
       "      <td>0.052966</td>\n",
       "      <td>-0.012029</td>\n",
       "      <td>0.003238</td>\n",
       "      <td>-0.006029</td>\n",
       "      <td>0.012111</td>\n",
       "      <td>-0.007573</td>\n",
       "      <td>0.018051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479</th>\n",
       "      <td>UPS</td>\n",
       "      <td>0.008287</td>\n",
       "      <td>-0.005074</td>\n",
       "      <td>0.006662</td>\n",
       "      <td>-0.001192</td>\n",
       "      <td>0.005318</td>\n",
       "      <td>0.002372</td>\n",
       "      <td>0.008732</td>\n",
       "      <td>-0.000546</td>\n",
       "      <td>-0.001727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>480</th>\n",
       "      <td>KO</td>\n",
       "      <td>-0.000458</td>\n",
       "      <td>-0.001148</td>\n",
       "      <td>-0.003895</td>\n",
       "      <td>0.000230</td>\n",
       "      <td>-0.000690</td>\n",
       "      <td>-0.000687</td>\n",
       "      <td>0.000686</td>\n",
       "      <td>0.002733</td>\n",
       "      <td>0.003175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>481</th>\n",
       "      <td>WHR</td>\n",
       "      <td>-0.006138</td>\n",
       "      <td>0.004619</td>\n",
       "      <td>-0.002316</td>\n",
       "      <td>-0.005717</td>\n",
       "      <td>-0.003580</td>\n",
       "      <td>-0.016015</td>\n",
       "      <td>-0.002612</td>\n",
       "      <td>-0.000484</td>\n",
       "      <td>-0.008102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>482</th>\n",
       "      <td>PEP</td>\n",
       "      <td>-0.008170</td>\n",
       "      <td>0.002311</td>\n",
       "      <td>-0.000736</td>\n",
       "      <td>-0.002583</td>\n",
       "      <td>0.000277</td>\n",
       "      <td>0.003412</td>\n",
       "      <td>-0.004862</td>\n",
       "      <td>-0.000460</td>\n",
       "      <td>0.000092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>483</th>\n",
       "      <td>CSRA</td>\n",
       "      <td>0.009671</td>\n",
       "      <td>-0.008058</td>\n",
       "      <td>0.013519</td>\n",
       "      <td>-0.017490</td>\n",
       "      <td>-0.002704</td>\n",
       "      <td>0.018605</td>\n",
       "      <td>-0.018519</td>\n",
       "      <td>-0.012186</td>\n",
       "      <td>0.002301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>484</th>\n",
       "      <td>NVDA</td>\n",
       "      <td>0.003931</td>\n",
       "      <td>0.013638</td>\n",
       "      <td>0.005355</td>\n",
       "      <td>-0.004407</td>\n",
       "      <td>-0.002372</td>\n",
       "      <td>-0.015149</td>\n",
       "      <td>0.014961</td>\n",
       "      <td>0.018417</td>\n",
       "      <td>-0.002060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>485</th>\n",
       "      <td>TSS</td>\n",
       "      <td>0.018534</td>\n",
       "      <td>-0.010505</td>\n",
       "      <td>0.002643</td>\n",
       "      <td>-0.014807</td>\n",
       "      <td>-0.005765</td>\n",
       "      <td>0.004751</td>\n",
       "      <td>-0.003285</td>\n",
       "      <td>0.002875</td>\n",
       "      <td>-0.013516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>486</th>\n",
       "      <td>GWW</td>\n",
       "      <td>0.004390</td>\n",
       "      <td>0.000275</td>\n",
       "      <td>0.005780</td>\n",
       "      <td>-0.005453</td>\n",
       "      <td>-0.006336</td>\n",
       "      <td>0.000455</td>\n",
       "      <td>0.030458</td>\n",
       "      <td>-0.001134</td>\n",
       "      <td>0.009881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>487</th>\n",
       "      <td>ILMN</td>\n",
       "      <td>-0.011321</td>\n",
       "      <td>0.006256</td>\n",
       "      <td>0.000841</td>\n",
       "      <td>-0.004247</td>\n",
       "      <td>-0.005227</td>\n",
       "      <td>0.000663</td>\n",
       "      <td>0.008523</td>\n",
       "      <td>-0.001674</td>\n",
       "      <td>0.035603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>HCA</td>\n",
       "      <td>0.019663</td>\n",
       "      <td>-0.005156</td>\n",
       "      <td>0.003175</td>\n",
       "      <td>0.003026</td>\n",
       "      <td>-0.000919</td>\n",
       "      <td>0.006448</td>\n",
       "      <td>0.007578</td>\n",
       "      <td>-0.004934</td>\n",
       "      <td>0.009124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>489</th>\n",
       "      <td>XEL</td>\n",
       "      <td>-0.008272</td>\n",
       "      <td>0.003475</td>\n",
       "      <td>-0.012497</td>\n",
       "      <td>-0.004676</td>\n",
       "      <td>0.001879</td>\n",
       "      <td>-0.001170</td>\n",
       "      <td>0.000468</td>\n",
       "      <td>0.000935</td>\n",
       "      <td>-0.017266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>CME</td>\n",
       "      <td>0.002135</td>\n",
       "      <td>-0.001353</td>\n",
       "      <td>0.007711</td>\n",
       "      <td>-0.003539</td>\n",
       "      <td>-0.004222</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.003760</td>\n",
       "      <td>0.008451</td>\n",
       "      <td>0.009818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>URBN</td>\n",
       "      <td>0.027866</td>\n",
       "      <td>0.001374</td>\n",
       "      <td>0.024117</td>\n",
       "      <td>-0.003309</td>\n",
       "      <td>-0.022659</td>\n",
       "      <td>0.012216</td>\n",
       "      <td>-0.004569</td>\n",
       "      <td>0.004918</td>\n",
       "      <td>-0.002236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492</th>\n",
       "      <td>ENDP</td>\n",
       "      <td>0.069289</td>\n",
       "      <td>-0.014714</td>\n",
       "      <td>-0.008233</td>\n",
       "      <td>-0.001098</td>\n",
       "      <td>0.093241</td>\n",
       "      <td>0.025771</td>\n",
       "      <td>0.045909</td>\n",
       "      <td>0.028936</td>\n",
       "      <td>-0.034497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>CBS</td>\n",
       "      <td>0.003468</td>\n",
       "      <td>-0.003269</td>\n",
       "      <td>0.003076</td>\n",
       "      <td>-0.005735</td>\n",
       "      <td>-0.000963</td>\n",
       "      <td>0.001154</td>\n",
       "      <td>0.013793</td>\n",
       "      <td>-0.005698</td>\n",
       "      <td>-0.003241</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>CBG</td>\n",
       "      <td>0.016249</td>\n",
       "      <td>-0.004164</td>\n",
       "      <td>0.020331</td>\n",
       "      <td>-0.002353</td>\n",
       "      <td>0.003353</td>\n",
       "      <td>0.001003</td>\n",
       "      <td>-0.002671</td>\n",
       "      <td>-0.002692</td>\n",
       "      <td>-0.004696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>BEN</td>\n",
       "      <td>0.015674</td>\n",
       "      <td>-0.009796</td>\n",
       "      <td>0.017030</td>\n",
       "      <td>-0.002464</td>\n",
       "      <td>-0.015690</td>\n",
       "      <td>-0.008366</td>\n",
       "      <td>-0.003633</td>\n",
       "      <td>0.003672</td>\n",
       "      <td>0.006717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>LUV</td>\n",
       "      <td>0.010704</td>\n",
       "      <td>0.005799</td>\n",
       "      <td>0.028533</td>\n",
       "      <td>-0.008472</td>\n",
       "      <td>-0.007682</td>\n",
       "      <td>-0.005110</td>\n",
       "      <td>0.007576</td>\n",
       "      <td>-0.014837</td>\n",
       "      <td>0.010411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>CVS</td>\n",
       "      <td>-0.002764</td>\n",
       "      <td>-0.002982</td>\n",
       "      <td>-0.000103</td>\n",
       "      <td>-0.001951</td>\n",
       "      <td>-0.000103</td>\n",
       "      <td>-0.004102</td>\n",
       "      <td>-0.003902</td>\n",
       "      <td>0.001130</td>\n",
       "      <td>0.001848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>TMO</td>\n",
       "      <td>-0.005292</td>\n",
       "      <td>-0.001277</td>\n",
       "      <td>0.001208</td>\n",
       "      <td>-0.005843</td>\n",
       "      <td>0.000894</td>\n",
       "      <td>-0.004975</td>\n",
       "      <td>0.007809</td>\n",
       "      <td>-0.008165</td>\n",
       "      <td>0.001798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>STT</td>\n",
       "      <td>0.007343</td>\n",
       "      <td>0.000304</td>\n",
       "      <td>0.022727</td>\n",
       "      <td>0.002647</td>\n",
       "      <td>-0.005427</td>\n",
       "      <td>-0.003240</td>\n",
       "      <td>0.008985</td>\n",
       "      <td>0.006787</td>\n",
       "      <td>0.009186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>MRO</td>\n",
       "      <td>0.034695</td>\n",
       "      <td>-0.013971</td>\n",
       "      <td>0.029696</td>\n",
       "      <td>0.010638</td>\n",
       "      <td>0.004878</td>\n",
       "      <td>-0.034530</td>\n",
       "      <td>0.021201</td>\n",
       "      <td>0.008219</td>\n",
       "      <td>-0.002677</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>501 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    symbol  2016-08-03  2016-08-04  2016-08-05  2016-08-08  2016-08-09  \\\n",
       "0      LEN    0.007489    0.000874    0.014524   -0.007452    0.006880   \n",
       "1      GIS   -0.018841    0.007000    0.000709    0.001986    0.002544   \n",
       "2        K   -0.009891   -0.000243   -0.001569    0.000362    0.000724   \n",
       "3     ALXN    0.033251   -0.019902    0.009349   -0.007729   -0.004058   \n",
       "4     SPGI    0.004083   -0.003582    0.000828   -0.005216    0.001162   \n",
       "5      AVY   -0.006304   -0.000517    0.007965    0.004979   -0.001395   \n",
       "6      AIV   -0.004200   -0.003774    0.008897    0.011018    0.002180   \n",
       "7       RF    0.014590   -0.002208    0.018438   -0.001064   -0.003191   \n",
       "8      MMM   -0.001902   -0.006426    0.001795   -0.001789    0.000112   \n",
       "9      PPG    0.001921   -0.001918   -0.001721    0.000287    0.000192   \n",
       "10     PKI   -0.000896   -0.002328   -0.014553   -0.003832    0.001831   \n",
       "11      CI    0.003301    0.028874   -0.004416    0.001951   -0.011888   \n",
       "12     AXP    0.005197    0.000939    0.015184    0.000000   -0.006684   \n",
       "13     WEC   -0.014695    0.002827   -0.016965   -0.005104    0.000641   \n",
       "14     IRM   -0.006420   -0.056205   -0.007618   -0.026462   -0.001900   \n",
       "15     PFG    0.009916    0.003719    0.016153    0.006329   -0.000629   \n",
       "16     SNA   -0.001230   -0.007567    0.015980   -0.011596    0.007423   \n",
       "17      PM   -0.005923   -0.003623   -0.000707   -0.002328   -0.002736   \n",
       "18      EA    0.025698   -0.001659    0.011401   -0.007264   -0.004050   \n",
       "19     OXY    0.011560    0.003243   -0.007008    0.002291   -0.013390   \n",
       "20    ESRX    0.007692   -0.003152    0.003547   -0.006153    0.006849   \n",
       "21     BLK    0.002942   -0.003862    0.012072   -0.007609   -0.010336   \n",
       "22      UA    0.040931   -0.000770    0.018316   -0.013896   -0.015280   \n",
       "23     DUK   -0.003278    0.002824   -0.009059    0.004862    0.000827   \n",
       "24    ULTA    0.008374    0.009097    0.003865   -0.006954    0.001798   \n",
       "25     HAS    0.007480    0.000000    0.011112    0.000980   -0.003545   \n",
       "26    TROW   -0.006613    0.000580    0.001434   -0.005424   -0.014187   \n",
       "27    FLIR   -0.009094   -0.000947   -0.000629   -0.012881    0.001588   \n",
       "28     MAT    0.009251    0.009466    0.023207    0.003831   -0.017024   \n",
       "29      OI    0.019737    0.013477    0.006846   -0.001046   -0.024530   \n",
       "..     ...         ...         ...         ...         ...         ...   \n",
       "471   CTXS    0.016297   -0.009906    0.007587   -0.003168    0.003418   \n",
       "472    CFG    0.017671    0.007127    0.019172   -0.001703    0.004705   \n",
       "473    JWN    0.043380    0.012048    0.027850    0.025424   -0.024434   \n",
       "474   GRMN    0.013153    0.012813    0.000182   -0.005979   -0.012175   \n",
       "475    MCO    0.003165   -0.007640    0.005167   -0.002482   -0.002297   \n",
       "476   NAVI    0.021418    0.031884    0.011134   -0.014413   -0.012509   \n",
       "477    GPC    0.002688    0.017873    0.004742   -0.006852   -0.004348   \n",
       "478    PWR    0.010334   -0.039721    0.052966   -0.012029    0.003238   \n",
       "479    UPS    0.008287   -0.005074    0.006662   -0.001192    0.005318   \n",
       "480     KO   -0.000458   -0.001148   -0.003895    0.000230   -0.000690   \n",
       "481    WHR   -0.006138    0.004619   -0.002316   -0.005717   -0.003580   \n",
       "482    PEP   -0.008170    0.002311   -0.000736   -0.002583    0.000277   \n",
       "483   CSRA    0.009671   -0.008058    0.013519   -0.017490   -0.002704   \n",
       "484   NVDA    0.003931    0.013638    0.005355   -0.004407   -0.002372   \n",
       "485    TSS    0.018534   -0.010505    0.002643   -0.014807   -0.005765   \n",
       "486    GWW    0.004390    0.000275    0.005780   -0.005453   -0.006336   \n",
       "487   ILMN   -0.011321    0.006256    0.000841   -0.004247   -0.005227   \n",
       "488    HCA    0.019663   -0.005156    0.003175    0.003026   -0.000919   \n",
       "489    XEL   -0.008272    0.003475   -0.012497   -0.004676    0.001879   \n",
       "490    CME    0.002135   -0.001353    0.007711   -0.003539   -0.004222   \n",
       "491   URBN    0.027866    0.001374    0.024117   -0.003309   -0.022659   \n",
       "492   ENDP    0.069289   -0.014714   -0.008233   -0.001098    0.093241   \n",
       "493    CBS    0.003468   -0.003269    0.003076   -0.005735   -0.000963   \n",
       "494    CBG    0.016249   -0.004164    0.020331   -0.002353    0.003353   \n",
       "495    BEN    0.015674   -0.009796    0.017030   -0.002464   -0.015690   \n",
       "496    LUV    0.010704    0.005799    0.028533   -0.008472   -0.007682   \n",
       "497    CVS   -0.002764   -0.002982   -0.000103   -0.001951   -0.000103   \n",
       "498    TMO   -0.005292   -0.001277    0.001208   -0.005843    0.000894   \n",
       "499    STT    0.007343    0.000304    0.022727    0.002647   -0.005427   \n",
       "500    MRO    0.034695   -0.013971    0.029696    0.010638    0.004878   \n",
       "\n",
       "     2016-08-10  2016-08-11  2016-08-12  2016-08-15  \n",
       "0     -0.002132    0.004045   -0.008063    0.008744  \n",
       "1      0.001409   -0.000422    0.000140    0.000141  \n",
       "2     -0.000843    0.003487    0.000359    0.000120  \n",
       "3     -0.019511    0.006097    0.003761    0.014086  \n",
       "4     -0.006550    0.002414   -0.002413   -0.000831  \n",
       "5     -0.000508   -0.006219    0.001665   -0.002934  \n",
       "6     -0.014115   -0.013413    0.004012    0.009756  \n",
       "7     -0.011740    0.004320    0.002172    0.029064  \n",
       "8      0.003930    0.010721   -0.006613   -0.002431  \n",
       "9      0.000000   -0.000763   -0.005927    0.003169  \n",
       "10    -0.005289    0.001281   -0.003845    0.000000  \n",
       "11     0.004724   -0.012247    0.050843   -0.000452  \n",
       "12    -0.010546    0.007385    0.003376    0.005516  \n",
       "13    -0.000321    0.003054   -0.003192   -0.021750  \n",
       "14    -0.004341    0.007343    0.003240   -0.001884  \n",
       "15    -0.013854    0.001912    0.000856    0.000637  \n",
       "16    -0.001346    0.001533   -0.016969   -0.001300  \n",
       "17     0.002730   -0.006332   -0.005325   -0.005346  \n",
       "18     0.000382   -0.012682   -0.007308    0.024359  \n",
       "19    -0.009865    0.005553    0.000000    0.002003  \n",
       "20    -0.008633    0.004484    0.003556    0.008631  \n",
       "21    -0.008335    0.003003    0.004384    0.007446  \n",
       "22    -0.012629    0.033838    0.002684    0.022513  \n",
       "23    -0.002856   -0.000119   -0.007725   -0.023323  \n",
       "24     0.002668    0.007386    0.006603   -0.006660  \n",
       "25     0.005880    0.007785   -0.004469   -0.007523  \n",
       "26    -0.003477    0.001597   -0.002033   -0.001890  \n",
       "27     0.001903   -0.008536   -0.010243    0.001940  \n",
       "28     0.003567    0.007084    0.002059    0.000000  \n",
       "29    -0.000533   -0.000529   -0.015940    0.013426  \n",
       "..          ...         ...         ...         ...  \n",
       "471    0.002342    0.017745    0.004379    0.003442  \n",
       "472   -0.008529    0.004723    0.002153    0.005124  \n",
       "473    0.009587    0.011915    0.007451    0.023777  \n",
       "474    0.008422   -0.000903   -0.007207    0.003793  \n",
       "475   -0.007560    0.002879   -0.004235    0.011066  \n",
       "476   -0.042224   -0.004380   -0.008817    0.018423  \n",
       "477    0.002576    0.003443   -0.003828    0.010149  \n",
       "478   -0.006029    0.012111   -0.007573    0.018051  \n",
       "479    0.002372    0.008732   -0.000546   -0.001727  \n",
       "480   -0.000687    0.000686    0.002733    0.003175  \n",
       "481   -0.016015   -0.002612   -0.000484   -0.008102  \n",
       "482    0.003412   -0.004862   -0.000460    0.000092  \n",
       "483    0.018605   -0.018519   -0.012186    0.002301  \n",
       "484   -0.015149    0.014961    0.018417   -0.002060  \n",
       "485    0.004751   -0.003285    0.002875   -0.013516  \n",
       "486    0.000455    0.030458   -0.001134    0.009881  \n",
       "487    0.000663    0.008523   -0.001674    0.035603  \n",
       "488    0.006448    0.007578   -0.004934    0.009124  \n",
       "489   -0.001170    0.000468    0.000935   -0.017266  \n",
       "490    0.000000   -0.003760    0.008451    0.009818  \n",
       "491    0.012216   -0.004569    0.004918   -0.002236  \n",
       "492    0.025771    0.045909    0.028936   -0.034497  \n",
       "493    0.001154    0.013793   -0.005698   -0.003241  \n",
       "494    0.001003   -0.002671   -0.002692   -0.004696  \n",
       "495   -0.008366   -0.003633    0.003672    0.006717  \n",
       "496   -0.005110    0.007576   -0.014837    0.010411  \n",
       "497   -0.004102   -0.003902    0.001130    0.001848  \n",
       "498   -0.004975    0.007809   -0.008165    0.001798  \n",
       "499   -0.003240    0.008985    0.006787    0.009186  \n",
       "500   -0.034530    0.021201    0.008219   -0.002677  \n",
       "\n",
       "[501 rows x 10 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(stocks\n",
    " .withColumn(\"date\", expr(\"cast(date as date) date\"))\n",
    ".withColumn(\"pct\", expr(\"(close-open)/open\"))\n",
    " .filter(\"date>'2016-08-02'\")\n",
    " .groupBy(\"symbol\")\n",
    " .pivot(\"date\")\n",
    " .agg(expr(\"max(pct)\"))\n",
    ").toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>summary</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>volume</th>\n",
       "      <th>adjclose</th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>count</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "      <td>1857092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mean</td>\n",
       "      <td>54.39150019132181</td>\n",
       "      <td>55.03561088277836</td>\n",
       "      <td>53.73182845876957</td>\n",
       "      <td>54.40520389624489</td>\n",
       "      <td>5438876.668468767</td>\n",
       "      <td>43.02996494097698</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stddev</td>\n",
       "      <td>60.84195032737054</td>\n",
       "      <td>61.436667351177796</td>\n",
       "      <td>60.21132386902276</td>\n",
       "      <td>60.839881015722526</td>\n",
       "      <td>1.40008932519909E7</td>\n",
       "      <td>63.518893483888206</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>min</td>\n",
       "      <td>0.21</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.19</td>\n",
       "      <td>0.21</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.18312</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>max</td>\n",
       "      <td>1475.0</td>\n",
       "      <td>1476.52002</td>\n",
       "      <td>1462.75</td>\n",
       "      <td>1469.560059</td>\n",
       "      <td>1.8554102E9</td>\n",
       "      <td>1553.982779</td>\n",
       "      <td>ZION</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  summary               open                high                low  \\\n",
       "0   count            1857092             1857092            1857092   \n",
       "1    mean  54.39150019132181   55.03561088277836  53.73182845876957   \n",
       "2  stddev  60.84195032737054  61.436667351177796  60.21132386902276   \n",
       "3     min               0.21                0.22               0.19   \n",
       "4     max             1475.0          1476.52002            1462.75   \n",
       "\n",
       "                close              volume            adjclose   symbol  \n",
       "0             1857092             1857092             1857092  1857092  \n",
       "1   54.40520389624489   5438876.668468767   43.02996494097698     None  \n",
       "2  60.839881015722526  1.40008932519909E7  63.518893483888206     None  \n",
       "3                0.21                 0.0             0.18312        A  \n",
       "4         1469.560059         1.8554102E9         1553.982779     ZION  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks.describe().toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- summary: string (nullable = true)\n",
      " |-- open: string (nullable = true)\n",
      " |-- high: string (nullable = true)\n",
      " |-- low: string (nullable = true)\n",
      " |-- close: string (nullable = true)\n",
      " |-- volume: string (nullable = true)\n",
      " |-- adjclose: string (nullable = true)\n",
      " |-- symbol: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "stocks.describe().printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['{\"summary\":\"count\",\"open\":\"1857092\",\"high\":\"1857092\",\"low\":\"1857092\",\"close\":\"1857092\",\"volume\":\"1857092\",\"adjclose\":\"1857092\",\"symbol\":\"1857092\"}',\n",
       " '{\"summary\":\"mean\",\"open\":\"54.39150019132181\",\"high\":\"55.03561088277836\",\"low\":\"53.73182845876957\",\"close\":\"54.40520389624489\",\"volume\":\"5438876.668468767\",\"adjclose\":\"43.02996494097698\"}',\n",
       " '{\"summary\":\"stddev\",\"open\":\"60.84195032737054\",\"high\":\"61.436667351177796\",\"low\":\"60.21132386902276\",\"close\":\"60.839881015722526\",\"volume\":\"1.40008932519909E7\",\"adjclose\":\"63.518893483888206\"}',\n",
       " '{\"summary\":\"min\",\"open\":\"0.21\",\"high\":\"0.22\",\"low\":\"0.19\",\"close\":\"0.21\",\"volume\":\"0.0\",\"adjclose\":\"0.18312\",\"symbol\":\"A\"}',\n",
       " '{\"summary\":\"max\",\"open\":\"1475.0\",\"high\":\"1476.52002\",\"low\":\"1462.75\",\"close\":\"1469.560059\",\"volume\":\"1.8554102E9\",\"adjclose\":\"1553.982779\",\"symbol\":\"ZION\"}']"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stocks.describe().toJSON().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}