09_blaze.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Blaze\n",
    "\n",
    "There's a very interesting set of libraries under [The Blaze Ecosystem](http://blaze.pydata.org/) project.\n",
    "\n",
    "[Blaze](http://blaze.readthedocs.io/en/latest/index.html) is a library to do _computation_ on large data sets, providing a syntax very similar to Pandas or NumPy, but allowing us to access to different storages: database, in-memory, distributed systems, etc.\n",
    "\n",
    "It's interesting to read the documentation on [what Blaze doesn't do](http://blaze.readthedocs.io/en/latest/what-blaze-isnt.html):\n",
    "\n",
    "> Blaze is a query system that looks like NumPy/Pandas. You write Blaze queries, Blaze translates those queries to something else (like SQL), and ships those queries to various database to run on other people’s fast code. It smoothes out this process to make interacting with foreign data as accessible as using Pandas. This is actually quite difficult.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/luis/anaconda/lib/python3.6/site-packages/odo/backends/pandas.py:94: FutureWarning: pandas.tslib is deprecated and will be removed in a future version.\n",
      "You can access NaTType as type(pandas.NaT)\n",
      "  @convert.register((pd.Timestamp, pd.Timedelta), (pd.tslib.NaTType, type(None)))\n",
      "/Users/luis/anaconda/lib/python3.6/site-packages/blaze/server/server.py:17: ExtDeprecationWarning: Importing flask.ext.cors is deprecated, use flask_cors instead.\n",
      "  from flask.ext.cors import cross_origin\n"
     ]
    }
   ],
   "source": [
    "from blaze import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     name\n",
       "0   1    Alice\n",
       "1   2      Bob\n",
       "2   3  Charlie"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = data([\n",
    "    (1, \"Alice\"), \n",
    "    (2, \"Bob\"),\n",
    "    (3, \"Charlie\")], \n",
    "    fields=['id', 'name'])\n",
    "t.peek()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading Data\n",
    "\n",
    "Our data set has _Date_ columns not properly formated. We have to create a dshape with `date` instead of `datetime` to make sure python handles the column properly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Adj Close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-07-01</td>\n",
       "      <td>692.200012</td>\n",
       "      <td>700.650024</td>\n",
       "      <td>692.130005</td>\n",
       "      <td>699.210022</td>\n",
       "      <td>1342700</td>\n",
       "      <td>699.210022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-06-30</td>\n",
       "      <td>685.469971</td>\n",
       "      <td>692.320007</td>\n",
       "      <td>683.650024</td>\n",
       "      <td>692.099976</td>\n",
       "      <td>1590500</td>\n",
       "      <td>692.099976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-06-29</td>\n",
       "      <td>683.000000</td>\n",
       "      <td>687.429016</td>\n",
       "      <td>681.409973</td>\n",
       "      <td>684.109985</td>\n",
       "      <td>1928500</td>\n",
       "      <td>684.109985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-06-28</td>\n",
       "      <td>678.969971</td>\n",
       "      <td>680.330017</td>\n",
       "      <td>673.000000</td>\n",
       "      <td>680.039978</td>\n",
       "      <td>2116600</td>\n",
       "      <td>680.039978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-06-27</td>\n",
       "      <td>671.000000</td>\n",
       "      <td>672.299988</td>\n",
       "      <td>663.283997</td>\n",
       "      <td>668.260010</td>\n",
       "      <td>2629000</td>\n",
       "      <td>668.260010</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "        Date        Open        High         Low       Close   Volume  \\\n",
       "0 2016-07-01  692.200012  700.650024  692.130005  699.210022  1342700   \n",
       "1 2016-06-30  685.469971  692.320007  683.650024  692.099976  1590500   \n",
       "2 2016-06-29  683.000000  687.429016  681.409973  684.109985  1928500   \n",
       "3 2016-06-28  678.969971  680.330017  673.000000  680.039978  2116600   \n",
       "4 2016-06-27  671.000000  672.299988  663.283997  668.260010  2629000   \n",
       "\n",
       "    Adj Close  \n",
       "0  699.210022  \n",
       "1  692.099976  \n",
       "2  684.109985  \n",
       "3  680.039978  \n",
       "4  668.260010  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goog = data('goog.csv.gz')\n",
    "goog[:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Adj Close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>585</th>\n",
       "      <td>2014-03-07</td>\n",
       "      <td>1226.802152</td>\n",
       "      <td>1226.992071</td>\n",
       "      <td>1211.442033</td>\n",
       "      <td>1214.792073</td>\n",
       "      <td>3041500</td>\n",
       "      <td>606.789247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>586</th>\n",
       "      <td>2014-03-06</td>\n",
       "      <td>1222.282091</td>\n",
       "      <td>1226.152141</td>\n",
       "      <td>1218.602083</td>\n",
       "      <td>1219.612082</td>\n",
       "      <td>2545600</td>\n",
       "      <td>609.196844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>590</th>\n",
       "      <td>2014-02-28</td>\n",
       "      <td>1220.342104</td>\n",
       "      <td>1224.192059</td>\n",
       "      <td>1206.222092</td>\n",
       "      <td>1215.652097</td>\n",
       "      <td>4644500</td>\n",
       "      <td>607.218830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>592</th>\n",
       "      <td>2014-02-26</td>\n",
       "      <td>1224.002141</td>\n",
       "      <td>1228.882066</td>\n",
       "      <td>1213.762102</td>\n",
       "      <td>1220.172036</td>\n",
       "      <td>3979100</td>\n",
       "      <td>609.476541</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "          Date         Open         High          Low        Close   Volume  \\\n",
       "585 2014-03-07  1226.802152  1226.992071  1211.442033  1214.792073  3041500   \n",
       "586 2014-03-06  1222.282091  1226.152141  1218.602083  1219.612082  2545600   \n",
       "590 2014-02-28  1220.342104  1224.192059  1206.222092  1215.652097  4644500   \n",
       "592 2014-02-26  1224.002141  1228.882066  1213.762102  1220.172036  3979100   \n",
       "\n",
       "      Adj Close  \n",
       "585  606.789247  \n",
       "586  609.196844  \n",
       "590  607.218830  \n",
       "592  609.476541  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goog[goog.Open > 1220]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>Close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>692.200012</td>\n",
       "      <td>699.210022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>685.469971</td>\n",
       "      <td>692.099976</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "         Open       Close\n",
       "0  692.200012  699.210022\n",
       "1  685.469971  692.099976"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goog[[\"Open\", \"Close\"]][:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "545.5555842261632"
      ],
      "text/plain": [
       "545.5555842261632"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goog.Close.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Grouping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date_year</th>\n",
       "      <th>Date_month</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>8</td>\n",
       "      <td>105.262402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004</td>\n",
       "      <td>9</td>\n",
       "      <td>113.227337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004</td>\n",
       "      <td>10</td>\n",
       "      <td>153.231214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004</td>\n",
       "      <td>11</td>\n",
       "      <td>177.495544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004</td>\n",
       "      <td>12</td>\n",
       "      <td>181.770309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2005</td>\n",
       "      <td>1</td>\n",
       "      <td>192.846331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2005</td>\n",
       "      <td>2</td>\n",
       "      <td>195.014017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2005</td>\n",
       "      <td>3</td>\n",
       "      <td>181.158493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2005</td>\n",
       "      <td>4</td>\n",
       "      <td>199.215105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2005</td>\n",
       "      <td>5</td>\n",
       "      <td>239.710411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2005</td>\n",
       "      <td>6</td>\n",
       "      <td>287.545949</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    Date_year  Date_month        mean\n",
       "0        2004           8  105.262402\n",
       "1        2004           9  113.227337\n",
       "2        2004          10  153.231214\n",
       "3        2004          11  177.495544\n",
       "4        2004          12  181.770309\n",
       "5        2005           1  192.846331\n",
       "6        2005           2  195.014017\n",
       "7        2005           3  181.158493\n",
       "8        2005           4  199.215105\n",
       "9        2005           5  239.710411\n",
       "..."
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by(merge(goog.Date.year, goog.Date.month), mean=goog.Close.mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Date_year</th>\n",
       "      <th>max</th>\n",
       "      <th>min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004</td>\n",
       "      <td>197.600333</td>\n",
       "      <td>100.010169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2005</td>\n",
       "      <td>432.040752</td>\n",
       "      <td>174.990304</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2006</td>\n",
       "      <td>509.650860</td>\n",
       "      <td>337.060574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007</td>\n",
       "      <td>741.791259</td>\n",
       "      <td>438.680763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008</td>\n",
       "      <td>685.331181</td>\n",
       "      <td>257.440455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2009</td>\n",
       "      <td>622.871087</td>\n",
       "      <td>282.750497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2010</td>\n",
       "      <td>626.771094</td>\n",
       "      <td>436.070761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011</td>\n",
       "      <td>645.901096</td>\n",
       "      <td>474.880824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2012</td>\n",
       "      <td>768.051291</td>\n",
       "      <td>559.050931</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013</td>\n",
       "      <td>1120.711956</td>\n",
       "      <td>702.871197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2014</td>\n",
       "      <td>1220.172036</td>\n",
       "      <td>495.392242</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "    Date_year          max         min\n",
       "0        2004   197.600333  100.010169\n",
       "1        2005   432.040752  174.990304\n",
       "2        2006   509.650860  337.060574\n",
       "3        2007   741.791259  438.680763\n",
       "4        2008   685.331181  257.440455\n",
       "5        2009   622.871087  282.750497\n",
       "6        2010   626.771094  436.070761\n",
       "7        2011   645.901096  474.880824\n",
       "8        2012   768.051291  559.050931\n",
       "9        2013  1120.711956  702.871197\n",
       "..."
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by(goog.Date.year, min=goog.Close.min(), max=goog.Close.max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CSV to SQLite\n",
    "\n",
    "Conversion is done using [Odo package](http://odo.readthedocs.io/en/latest/).\n",
    "\n",
    "More examples can be found [here](http://blaze.readthedocs.io/en/latest/csv.html#migrate-to-binary-storage-formats)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 35.3 ms, sys: 15.6 ms, total: 50.9 ms\n",
      "Wall time: 109 ms\n"
     ]
    }
   ],
   "source": [
    "from odo import odo\n",
    "%time table = odo(\"goog.csv\", \"sqlite:///goog.sqlite::prices\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dshape(\"\"\"{\n",
       "  prices: var * {\n",
       "    Date: ?datetime,\n",
       "    Open: float64,\n",
       "    High: float64,\n",
       "    Low: float64,\n",
       "    Close: float64,\n",
       "    Volume: int64,\n",
       "    'Adj Close': float64\n",
       "    }\n",
       "  }\"\"\")"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import Table, Column, MetaData, Integer, String, create_engine\n",
    "engine = create_engine(\"sqlite:///goog.sqlite\")\n",
    "goog = data(engine)\n",
    "goog.dshape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "      <th>Close</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>692.200012</td>\n",
       "      <td>699.210022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>685.469971</td>\n",
       "      <td>692.099976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>683.000000</td>\n",
       "      <td>684.109985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>678.969971</td>\n",
       "      <td>680.039978</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>671.000000</td>\n",
       "      <td>668.260010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>675.169983</td>\n",
       "      <td>675.219971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>697.450012</td>\n",
       "      <td>701.869995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>699.059998</td>\n",
       "      <td>697.460022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>698.400024</td>\n",
       "      <td>695.940002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>698.770020</td>\n",
       "      <td>693.710022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>708.650024</td>\n",
       "      <td>691.719971</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "          Open       Close\n",
       "0   692.200012  699.210022\n",
       "1   685.469971  692.099976\n",
       "2   683.000000  684.109985\n",
       "3   678.969971  680.039978\n",
       "4   671.000000  668.260010\n",
       "5   675.169983  675.219971\n",
       "6   697.450012  701.869995\n",
       "7   699.059998  697.460022\n",
       "8   698.400024  695.940002\n",
       "9   698.770020  693.710022\n",
       "..."
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "goog.prices[[\"Open\", \"Close\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manually defining input format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Open</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>692.200012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>685.469971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>683.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>678.969971</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>671.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>675.169983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>697.450012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>699.059998</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>698.400024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>698.770020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>708.650024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "          Open\n",
       "0   692.200012\n",
       "1   685.469971\n",
       "2   683.000000\n",
       "3   678.969971\n",
       "4   671.000000\n",
       "5   675.169983\n",
       "6   697.450012\n",
       "7   699.059998\n",
       "8   698.400024\n",
       "9   698.770020\n",
       "..."
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schema = dshape(\"\"\"{\n",
    "  prices: var * {\n",
    "    Date: ?datetime,\n",
    "    Open: float64,\n",
    "    High: float64,\n",
    "    Low: float64,\n",
    "    Close: float64,\n",
    "    Volume: int64,\n",
    "    'Adj Close': float64\n",
    "    }\n",
    "  }\"\"\")\n",
    "goog = data('sqlite:///goog.sqlite::prices', dshape=schema)\n",
    "goog.Open"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And kids, this is how one gets bitten by the `datetime` monster:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DO NOT TRY THIS AT HOME\n",
    "# goog.Date"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}