2. Blaze (solutions).ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <img src=images/continuum_analytics_b&w.png align=\"left\" width=\"15%\" style=\"margin-right:15%\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1 align='center'>Introduction to Blaze</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this tutorial we'll learn how to use Blaze to discover, migrate, and query data living in other databases.  Generally this tutorial will have the following format\n",
    "\n",
    "1. `odo` - Move data to database\n",
    "2. `blaze` - Query data in database\n",
    "\n",
    "\n",
    "Goal: Accessible, Interactive, Analytic Queries\n",
    "-----------------------------------------------\n",
    "\n",
    "NumPy and Pandas provide accessible, interactive, analytic queries; this is valuable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>SepalLength</th>\n",
       "      <th>SepalWidth</th>\n",
       "      <th>PetalLength</th>\n",
       "      <th>PetalWidth</th>\n",
       "      <th>Species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  SepalLength  SepalWidth  PetalLength  PetalWidth Species\n",
       "0           1          5.1         3.5          1.4         0.2  setosa\n",
       "1           2          4.9         3.0          1.4         0.2  setosa\n",
       "2           3          4.7         3.2          1.3         0.2  setosa\n",
       "3           4          4.6         3.1          1.5         0.2  setosa\n",
       "4           5          5.0         3.6          1.4         0.2  setosa"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_csv('data/iris.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Species\n",
       "setosa        1.462\n",
       "versicolor    4.260\n",
       "virginica     5.552\n",
       "Name: PetalLength, dtype: float64"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(df.Species).PetalLength.mean()  # Average petal length per species"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr/>\n",
    "\n",
    "But as data grows and systems become more complex, moving data and querying data become more difficult. Python already has excellent tools for data that fits in memory, but we want to hook up to data that is inconvenient.\n",
    "\n",
    "From now on, we're going to assume one of the following:\n",
    "\n",
    "1.  You have an inconvenient amount of data\n",
    "2.  That data should live someplace other than your computer\n",
    "\n",
    "<hr/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Databases and Python\n",
    "--------------------\n",
    "\n",
    "When in-memory arrays/dataframes cease to be an option, we turn to databases.  These live outside of the Python process and so might be less convenient.  The open source Python ecosystem includes libraries to interact with these databases and with foreign data in general.  \n",
    "\n",
    "Examples:\n",
    "\n",
    "* SQL - [`sqlalchemy`](http://sqlalchemy.org) \n",
    "    * Hive/Cassandra - [`pyhive`](https://github.com/dropbox/PyHive)\n",
    "    * Impala  - [`impyla`](https://github.com/cloudera/impyla)\n",
    "    * RedShift - [`redshift-sqlalchemy`](https://pypi.python.org/pypi/redshift-sqlalchemy)\n",
    "    * ...\n",
    "* MongoDB - [`pymongo`](http://api.mongodb.org/python/current/)\n",
    "* HBase - [`happybase`](http://happybase.readthedocs.org/en/latest/)\n",
    "* Spark - [`pyspark`](http://spark.apache.org/docs/latest/api/python/)\n",
    "* SSH - [`paramiko`](http://www.paramiko.org/)\n",
    "* HDFS - [`pywebhdfs`](https://pypi.python.org/pypi/pywebhdfs)\n",
    "* Amazon S3 - [`boto`](https://boto.readthedocs.org/en/latest/)\n",
    "\n",
    "Today we're going to use some of these indirectly with `odo` (was `into`) and Blaze.  We'll try to point out these libraries as we automate them so that, if you'd like, you can use them independently.\n",
    "\n",
    "<hr />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`odo` (formerly `into`)\n",
    "=======================\n",
    "\n",
    "Odo migrates data between formats and locations.\n",
    "\n",
    "Before we can use a database we need to move data into it.  The `odo` project provides a single consistent interface to move data between formats and between locations.\n",
    "\n",
    "We'll start with local data and eventually move out to remote data.\n",
    "\n",
    "[*odo docs*](http://odo.readthedocs.org/en/latest/index.html)\n",
    "\n",
    "\n",
    "<hr/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Examples\n",
    "\n",
    "\n",
    "Odo moves data into a target from a source\n",
    "\n",
    "```python\n",
    ">>> odo(source, target)\n",
    "```\n",
    "\n",
    "The target and source can be either a Python object or a string URI.  The following are all valid calls to `into`\n",
    "\n",
    "```python\n",
    ">>> odo('iris.csv', pd.DataFrame)  # Load CSV file into new DataFrame\n",
    ">>> odo(my_df, 'iris.json')        # Write DataFrame into JSON file\n",
    ">>> odo('iris.csv', 'iris.json')   # Migrate data from CSV to JSON\n",
    "```\n",
    "\n",
    "<hr/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "Use `odo` to load the `iris.csv` file into a Python `list`, a `np.ndarray`, and a `pd.DataFrame`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from odo import odo\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SepalLength</th>\n",
       "      <th>SepalWidth</th>\n",
       "      <th>PetalLength</th>\n",
       "      <th>PetalWidth</th>\n",
       "      <th>Species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5.4</td>\n",
       "      <td>3.9</td>\n",
       "      <td>1.7</td>\n",
       "      <td>0.4</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.3</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4.4</td>\n",
       "      <td>2.9</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4.9</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.1</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>5.4</td>\n",
       "      <td>3.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>4.8</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>4.8</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.1</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>4.3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>5.8</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.2</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>5.7</td>\n",
       "      <td>4.4</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.4</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>5.4</td>\n",
       "      <td>3.9</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.4</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.3</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>5.7</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1.7</td>\n",
       "      <td>0.3</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.3</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5.4</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1.7</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.7</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.4</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>4.6</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>5.1</td>\n",
       "      <td>3.3</td>\n",
       "      <td>1.7</td>\n",
       "      <td>0.5</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>4.8</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1.9</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>5.0</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.4</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>5.2</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>5.2</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</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>120</th>\n",
       "      <td>6.9</td>\n",
       "      <td>3.2</td>\n",
       "      <td>5.7</td>\n",
       "      <td>2.3</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>5.6</td>\n",
       "      <td>2.8</td>\n",
       "      <td>4.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>7.7</td>\n",
       "      <td>2.8</td>\n",
       "      <td>6.7</td>\n",
       "      <td>2.0</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>6.3</td>\n",
       "      <td>2.7</td>\n",
       "      <td>4.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>6.7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>5.7</td>\n",
       "      <td>2.1</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>125</th>\n",
       "      <td>7.2</td>\n",
       "      <td>3.2</td>\n",
       "      <td>6.0</td>\n",
       "      <td>1.8</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>6.2</td>\n",
       "      <td>2.8</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1.8</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>6.1</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.9</td>\n",
       "      <td>1.8</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>6.4</td>\n",
       "      <td>2.8</td>\n",
       "      <td>5.6</td>\n",
       "      <td>2.1</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>7.2</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.8</td>\n",
       "      <td>1.6</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>7.4</td>\n",
       "      <td>2.8</td>\n",
       "      <td>6.1</td>\n",
       "      <td>1.9</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>7.9</td>\n",
       "      <td>3.8</td>\n",
       "      <td>6.4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>6.4</td>\n",
       "      <td>2.8</td>\n",
       "      <td>5.6</td>\n",
       "      <td>2.2</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>6.3</td>\n",
       "      <td>2.8</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>6.1</td>\n",
       "      <td>2.6</td>\n",
       "      <td>5.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>7.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>6.1</td>\n",
       "      <td>2.3</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>6.3</td>\n",
       "      <td>3.4</td>\n",
       "      <td>5.6</td>\n",
       "      <td>2.4</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>6.4</td>\n",
       "      <td>3.1</td>\n",
       "      <td>5.5</td>\n",
       "      <td>1.8</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>6.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.8</td>\n",
       "      <td>1.8</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>6.9</td>\n",
       "      <td>3.1</td>\n",
       "      <td>5.4</td>\n",
       "      <td>2.1</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>6.7</td>\n",
       "      <td>3.1</td>\n",
       "      <td>5.6</td>\n",
       "      <td>2.4</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>6.9</td>\n",
       "      <td>3.1</td>\n",
       "      <td>5.1</td>\n",
       "      <td>2.3</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>5.8</td>\n",
       "      <td>2.7</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1.9</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>6.8</td>\n",
       "      <td>3.2</td>\n",
       "      <td>5.9</td>\n",
       "      <td>2.3</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>6.7</td>\n",
       "      <td>3.3</td>\n",
       "      <td>5.7</td>\n",
       "      <td>2.5</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>6.7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.2</td>\n",
       "      <td>2.3</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>6.3</td>\n",
       "      <td>2.5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>1.9</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>6.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>6.2</td>\n",
       "      <td>3.4</td>\n",
       "      <td>5.4</td>\n",
       "      <td>2.3</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>5.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.1</td>\n",
       "      <td>1.8</td>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>150 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     SepalLength  SepalWidth  PetalLength  PetalWidth    Species\n",
       "0            5.1         3.5          1.4         0.2     setosa\n",
       "1            4.9         3.0          1.4         0.2     setosa\n",
       "2            4.7         3.2          1.3         0.2     setosa\n",
       "3            4.6         3.1          1.5         0.2     setosa\n",
       "4            5.0         3.6          1.4         0.2     setosa\n",
       "5            5.4         3.9          1.7         0.4     setosa\n",
       "6            4.6         3.4          1.4         0.3     setosa\n",
       "7            5.0         3.4          1.5         0.2     setosa\n",
       "8            4.4         2.9          1.4         0.2     setosa\n",
       "9            4.9         3.1          1.5         0.1     setosa\n",
       "10           5.4         3.7          1.5         0.2     setosa\n",
       "11           4.8         3.4          1.6         0.2     setosa\n",
       "12           4.8         3.0          1.4         0.1     setosa\n",
       "13           4.3         3.0          1.1         0.1     setosa\n",
       "14           5.8         4.0          1.2         0.2     setosa\n",
       "15           5.7         4.4          1.5         0.4     setosa\n",
       "16           5.4         3.9          1.3         0.4     setosa\n",
       "17           5.1         3.5          1.4         0.3     setosa\n",
       "18           5.7         3.8          1.7         0.3     setosa\n",
       "19           5.1         3.8          1.5         0.3     setosa\n",
       "20           5.4         3.4          1.7         0.2     setosa\n",
       "21           5.1         3.7          1.5         0.4     setosa\n",
       "22           4.6         3.6          1.0         0.2     setosa\n",
       "23           5.1         3.3          1.7         0.5     setosa\n",
       "24           4.8         3.4          1.9         0.2     setosa\n",
       "25           5.0         3.0          1.6         0.2     setosa\n",
       "26           5.0         3.4          1.6         0.4     setosa\n",
       "27           5.2         3.5          1.5         0.2     setosa\n",
       "28           5.2         3.4          1.4         0.2     setosa\n",
       "29           4.7         3.2          1.6         0.2     setosa\n",
       "..           ...         ...          ...         ...        ...\n",
       "120          6.9         3.2          5.7         2.3  virginica\n",
       "121          5.6         2.8          4.9         2.0  virginica\n",
       "122          7.7         2.8          6.7         2.0  virginica\n",
       "123          6.3         2.7          4.9         1.8  virginica\n",
       "124          6.7         3.3          5.7         2.1  virginica\n",
       "125          7.2         3.2          6.0         1.8  virginica\n",
       "126          6.2         2.8          4.8         1.8  virginica\n",
       "127          6.1         3.0          4.9         1.8  virginica\n",
       "128          6.4         2.8          5.6         2.1  virginica\n",
       "129          7.2         3.0          5.8         1.6  virginica\n",
       "130          7.4         2.8          6.1         1.9  virginica\n",
       "131          7.9         3.8          6.4         2.0  virginica\n",
       "132          6.4         2.8          5.6         2.2  virginica\n",
       "133          6.3         2.8          5.1         1.5  virginica\n",
       "134          6.1         2.6          5.6         1.4  virginica\n",
       "135          7.7         3.0          6.1         2.3  virginica\n",
       "136          6.3         3.4          5.6         2.4  virginica\n",
       "137          6.4         3.1          5.5         1.8  virginica\n",
       "138          6.0         3.0          4.8         1.8  virginica\n",
       "139          6.9         3.1          5.4         2.1  virginica\n",
       "140          6.7         3.1          5.6         2.4  virginica\n",
       "141          6.9         3.1          5.1         2.3  virginica\n",
       "142          5.8         2.7          5.1         1.9  virginica\n",
       "143          6.8         3.2          5.9         2.3  virginica\n",
       "144          6.7         3.3          5.7         2.5  virginica\n",
       "145          6.7         3.0          5.2         2.3  virginica\n",
       "146          6.3         2.5          5.0         1.9  virginica\n",
       "147          6.5         3.0          5.2         2.0  virginica\n",
       "148          6.2         3.4          5.4         2.3  virginica\n",
       "149          5.9         3.0          5.1         1.8  virginica\n",
       "\n",
       "[150 rows x 5 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo(\"data/iris.csv\", pd.DataFrame, index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 5.1, 3.5, 1.4, 0.2, u'setosa'),\n",
       " (2, 4.9, 3.0, 1.4, 0.2, u'setosa'),\n",
       " (3, 4.7, 3.2, 1.3, 0.2, u'setosa'),\n",
       " (4, 4.6, 3.1, 1.5, 0.2, u'setosa'),\n",
       " (5, 5.0, 3.6, 1.4, 0.2, u'setosa'),\n",
       " (6, 5.4, 3.9, 1.7, 0.4, u'setosa'),\n",
       " (7, 4.6, 3.4, 1.4, 0.3, u'setosa'),\n",
       " (8, 5.0, 3.4, 1.5, 0.2, u'setosa'),\n",
       " (9, 4.4, 2.9, 1.4, 0.2, u'setosa'),\n",
       " (10, 4.9, 3.1, 1.5, 0.1, u'setosa'),\n",
       " (11, 5.4, 3.7, 1.5, 0.2, u'setosa'),\n",
       " (12, 4.8, 3.4, 1.6, 0.2, u'setosa'),\n",
       " (13, 4.8, 3.0, 1.4, 0.1, u'setosa'),\n",
       " (14, 4.3, 3.0, 1.1, 0.1, u'setosa'),\n",
       " (15, 5.8, 4.0, 1.2, 0.2, u'setosa'),\n",
       " (16, 5.7, 4.4, 1.5, 0.4, u'setosa'),\n",
       " (17, 5.4, 3.9, 1.3, 0.4, u'setosa'),\n",
       " (18, 5.1, 3.5, 1.4, 0.3, u'setosa'),\n",
       " (19, 5.7, 3.8, 1.7, 0.3, u'setosa'),\n",
       " (20, 5.1, 3.8, 1.5, 0.3, u'setosa'),\n",
       " (21, 5.4, 3.4, 1.7, 0.2, u'setosa'),\n",
       " (22, 5.1, 3.7, 1.5, 0.4, u'setosa'),\n",
       " (23, 4.6, 3.6, 1.0, 0.2, u'setosa'),\n",
       " (24, 5.1, 3.3, 1.7, 0.5, u'setosa'),\n",
       " (25, 4.8, 3.4, 1.9, 0.2, u'setosa'),\n",
       " (26, 5.0, 3.0, 1.6, 0.2, u'setosa'),\n",
       " (27, 5.0, 3.4, 1.6, 0.4, u'setosa'),\n",
       " (28, 5.2, 3.5, 1.5, 0.2, u'setosa'),\n",
       " (29, 5.2, 3.4, 1.4, 0.2, u'setosa'),\n",
       " (30, 4.7, 3.2, 1.6, 0.2, u'setosa'),\n",
       " (31, 4.8, 3.1, 1.6, 0.2, u'setosa'),\n",
       " (32, 5.4, 3.4, 1.5, 0.4, u'setosa'),\n",
       " (33, 5.2, 4.1, 1.5, 0.1, u'setosa'),\n",
       " (34, 5.5, 4.2, 1.4, 0.2, u'setosa'),\n",
       " (35, 4.9, 3.1, 1.5, 0.2, u'setosa'),\n",
       " (36, 5.0, 3.2, 1.2, 0.2, u'setosa'),\n",
       " (37, 5.5, 3.5, 1.3, 0.2, u'setosa'),\n",
       " (38, 4.9, 3.6, 1.4, 0.1, u'setosa'),\n",
       " (39, 4.4, 3.0, 1.3, 0.2, u'setosa'),\n",
       " (40, 5.1, 3.4, 1.5, 0.2, u'setosa'),\n",
       " (41, 5.0, 3.5, 1.3, 0.3, u'setosa'),\n",
       " (42, 4.5, 2.3, 1.3, 0.3, u'setosa'),\n",
       " (43, 4.4, 3.2, 1.3, 0.2, u'setosa'),\n",
       " (44, 5.0, 3.5, 1.6, 0.6, u'setosa'),\n",
       " (45, 5.1, 3.8, 1.9, 0.4, u'setosa'),\n",
       " (46, 4.8, 3.0, 1.4, 0.3, u'setosa'),\n",
       " (47, 5.1, 3.8, 1.6, 0.2, u'setosa'),\n",
       " (48, 4.6, 3.2, 1.4, 0.2, u'setosa'),\n",
       " (49, 5.3, 3.7, 1.5, 0.2, u'setosa'),\n",
       " (50, 5.0, 3.3, 1.4, 0.2, u'setosa'),\n",
       " (51, 7.0, 3.2, 4.7, 1.4, u'versicolor'),\n",
       " (52, 6.4, 3.2, 4.5, 1.5, u'versicolor'),\n",
       " (53, 6.9, 3.1, 4.9, 1.5, u'versicolor'),\n",
       " (54, 5.5, 2.3, 4.0, 1.3, u'versicolor'),\n",
       " (55, 6.5, 2.8, 4.6, 1.5, u'versicolor'),\n",
       " (56, 5.7, 2.8, 4.5, 1.3, u'versicolor'),\n",
       " (57, 6.3, 3.3, 4.7, 1.6, u'versicolor'),\n",
       " (58, 4.9, 2.4, 3.3, 1.0, u'versicolor'),\n",
       " (59, 6.6, 2.9, 4.6, 1.3, u'versicolor'),\n",
       " (60, 5.2, 2.7, 3.9, 1.4, u'versicolor'),\n",
       " (61, 5.0, 2.0, 3.5, 1.0, u'versicolor'),\n",
       " (62, 5.9, 3.0, 4.2, 1.5, u'versicolor'),\n",
       " (63, 6.0, 2.2, 4.0, 1.0, u'versicolor'),\n",
       " (64, 6.1, 2.9, 4.7, 1.4, u'versicolor'),\n",
       " (65, 5.6, 2.9, 3.6, 1.3, u'versicolor'),\n",
       " (66, 6.7, 3.1, 4.4, 1.4, u'versicolor'),\n",
       " (67, 5.6, 3.0, 4.5, 1.5, u'versicolor'),\n",
       " (68, 5.8, 2.7, 4.1, 1.0, u'versicolor'),\n",
       " (69, 6.2, 2.2, 4.5, 1.5, u'versicolor'),\n",
       " (70, 5.6, 2.5, 3.9, 1.1, u'versicolor'),\n",
       " (71, 5.9, 3.2, 4.8, 1.8, u'versicolor'),\n",
       " (72, 6.1, 2.8, 4.0, 1.3, u'versicolor'),\n",
       " (73, 6.3, 2.5, 4.9, 1.5, u'versicolor'),\n",
       " (74, 6.1, 2.8, 4.7, 1.2, u'versicolor'),\n",
       " (75, 6.4, 2.9, 4.3, 1.3, u'versicolor'),\n",
       " (76, 6.6, 3.0, 4.4, 1.4, u'versicolor'),\n",
       " (77, 6.8, 2.8, 4.8, 1.4, u'versicolor'),\n",
       " (78, 6.7, 3.0, 5.0, 1.7, u'versicolor'),\n",
       " (79, 6.0, 2.9, 4.5, 1.5, u'versicolor'),\n",
       " (80, 5.7, 2.6, 3.5, 1.0, u'versicolor'),\n",
       " (81, 5.5, 2.4, 3.8, 1.1, u'versicolor'),\n",
       " (82, 5.5, 2.4, 3.7, 1.0, u'versicolor'),\n",
       " (83, 5.8, 2.7, 3.9, 1.2, u'versicolor'),\n",
       " (84, 6.0, 2.7, 5.1, 1.6, u'versicolor'),\n",
       " (85, 5.4, 3.0, 4.5, 1.5, u'versicolor'),\n",
       " (86, 6.0, 3.4, 4.5, 1.6, u'versicolor'),\n",
       " (87, 6.7, 3.1, 4.7, 1.5, u'versicolor'),\n",
       " (88, 6.3, 2.3, 4.4, 1.3, u'versicolor'),\n",
       " (89, 5.6, 3.0, 4.1, 1.3, u'versicolor'),\n",
       " (90, 5.5, 2.5, 4.0, 1.3, u'versicolor'),\n",
       " (91, 5.5, 2.6, 4.4, 1.2, u'versicolor'),\n",
       " (92, 6.1, 3.0, 4.6, 1.4, u'versicolor'),\n",
       " (93, 5.8, 2.6, 4.0, 1.2, u'versicolor'),\n",
       " (94, 5.0, 2.3, 3.3, 1.0, u'versicolor'),\n",
       " (95, 5.6, 2.7, 4.2, 1.3, u'versicolor'),\n",
       " (96, 5.7, 3.0, 4.2, 1.2, u'versicolor'),\n",
       " (97, 5.7, 2.9, 4.2, 1.3, u'versicolor'),\n",
       " (98, 6.2, 2.9, 4.3, 1.3, u'versicolor'),\n",
       " (99, 5.1, 2.5, 3.0, 1.1, u'versicolor'),\n",
       " (100, 5.7, 2.8, 4.1, 1.3, u'versicolor'),\n",
       " (101, 6.3, 3.3, 6.0, 2.5, u'virginica'),\n",
       " (102, 5.8, 2.7, 5.1, 1.9, u'virginica'),\n",
       " (103, 7.1, 3.0, 5.9, 2.1, u'virginica'),\n",
       " (104, 6.3, 2.9, 5.6, 1.8, u'virginica'),\n",
       " (105, 6.5, 3.0, 5.8, 2.2, u'virginica'),\n",
       " (106, 7.6, 3.0, 6.6, 2.1, u'virginica'),\n",
       " (107, 4.9, 2.5, 4.5, 1.7, u'virginica'),\n",
       " (108, 7.3, 2.9, 6.3, 1.8, u'virginica'),\n",
       " (109, 6.7, 2.5, 5.8, 1.8, u'virginica'),\n",
       " (110, 7.2, 3.6, 6.1, 2.5, u'virginica'),\n",
       " (111, 6.5, 3.2, 5.1, 2.0, u'virginica'),\n",
       " (112, 6.4, 2.7, 5.3, 1.9, u'virginica'),\n",
       " (113, 6.8, 3.0, 5.5, 2.1, u'virginica'),\n",
       " (114, 5.7, 2.5, 5.0, 2.0, u'virginica'),\n",
       " (115, 5.8, 2.8, 5.1, 2.4, u'virginica'),\n",
       " (116, 6.4, 3.2, 5.3, 2.3, u'virginica'),\n",
       " (117, 6.5, 3.0, 5.5, 1.8, u'virginica'),\n",
       " (118, 7.7, 3.8, 6.7, 2.2, u'virginica'),\n",
       " (119, 7.7, 2.6, 6.9, 2.3, u'virginica'),\n",
       " (120, 6.0, 2.2, 5.0, 1.5, u'virginica'),\n",
       " (121, 6.9, 3.2, 5.7, 2.3, u'virginica'),\n",
       " (122, 5.6, 2.8, 4.9, 2.0, u'virginica'),\n",
       " (123, 7.7, 2.8, 6.7, 2.0, u'virginica'),\n",
       " (124, 6.3, 2.7, 4.9, 1.8, u'virginica'),\n",
       " (125, 6.7, 3.3, 5.7, 2.1, u'virginica'),\n",
       " (126, 7.2, 3.2, 6.0, 1.8, u'virginica'),\n",
       " (127, 6.2, 2.8, 4.8, 1.8, u'virginica'),\n",
       " (128, 6.1, 3.0, 4.9, 1.8, u'virginica'),\n",
       " (129, 6.4, 2.8, 5.6, 2.1, u'virginica'),\n",
       " (130, 7.2, 3.0, 5.8, 1.6, u'virginica'),\n",
       " (131, 7.4, 2.8, 6.1, 1.9, u'virginica'),\n",
       " (132, 7.9, 3.8, 6.4, 2.0, u'virginica'),\n",
       " (133, 6.4, 2.8, 5.6, 2.2, u'virginica'),\n",
       " (134, 6.3, 2.8, 5.1, 1.5, u'virginica'),\n",
       " (135, 6.1, 2.6, 5.6, 1.4, u'virginica'),\n",
       " (136, 7.7, 3.0, 6.1, 2.3, u'virginica'),\n",
       " (137, 6.3, 3.4, 5.6, 2.4, u'virginica'),\n",
       " (138, 6.4, 3.1, 5.5, 1.8, u'virginica'),\n",
       " (139, 6.0, 3.0, 4.8, 1.8, u'virginica'),\n",
       " (140, 6.9, 3.1, 5.4, 2.1, u'virginica'),\n",
       " (141, 6.7, 3.1, 5.6, 2.4, u'virginica'),\n",
       " (142, 6.9, 3.1, 5.1, 2.3, u'virginica'),\n",
       " (143, 5.8, 2.7, 5.1, 1.9, u'virginica'),\n",
       " (144, 6.8, 3.2, 5.9, 2.3, u'virginica'),\n",
       " (145, 6.7, 3.3, 5.7, 2.5, u'virginica'),\n",
       " (146, 6.7, 3.0, 5.2, 2.3, u'virginica'),\n",
       " (147, 6.3, 2.5, 5.0, 1.9, u'virginica'),\n",
       " (148, 6.5, 3.0, 5.2, 2.0, u'virginica'),\n",
       " (149, 6.2, 3.4, 5.4, 2.3, u'virginica'),\n",
       " (150, 5.9, 3.0, 5.1, 1.8, u'virginica')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo(\"data/iris.csv\", list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rec.array([(1, 5.1, 3.5, 1.4, 0.2, u'setosa'),\n",
       "       (2, 4.9, 3.0, 1.4, 0.2, u'setosa'),\n",
       "       (3, 4.7, 3.2, 1.3, 0.2, u'setosa'),\n",
       "       (4, 4.6, 3.1, 1.5, 0.2, u'setosa'),\n",
       "       (5, 5.0, 3.6, 1.4, 0.2, u'setosa'),\n",
       "       (6, 5.4, 3.9, 1.7, 0.4, u'setosa'),\n",
       "       (7, 4.6, 3.4, 1.4, 0.3, u'setosa'),\n",
       "       (8, 5.0, 3.4, 1.5, 0.2, u'setosa'),\n",
       "       (9, 4.4, 2.9, 1.4, 0.2, u'setosa'),\n",
       "       (10, 4.9, 3.1, 1.5, 0.1, u'setosa'),\n",
       "       (11, 5.4, 3.7, 1.5, 0.2, u'setosa'),\n",
       "       (12, 4.8, 3.4, 1.6, 0.2, u'setosa'),\n",
       "       (13, 4.8, 3.0, 1.4, 0.1, u'setosa'),\n",
       "       (14, 4.3, 3.0, 1.1, 0.1, u'setosa'),\n",
       "       (15, 5.8, 4.0, 1.2, 0.2, u'setosa'),\n",
       "       (16, 5.7, 4.4, 1.5, 0.4, u'setosa'),\n",
       "       (17, 5.4, 3.9, 1.3, 0.4, u'setosa'),\n",
       "       (18, 5.1, 3.5, 1.4, 0.3, u'setosa'),\n",
       "       (19, 5.7, 3.8, 1.7, 0.3, u'setosa'),\n",
       "       (20, 5.1, 3.8, 1.5, 0.3, u'setosa'),\n",
       "       (21, 5.4, 3.4, 1.7, 0.2, u'setosa'),\n",
       "       (22, 5.1, 3.7, 1.5, 0.4, u'setosa'),\n",
       "       (23, 4.6, 3.6, 1.0, 0.2, u'setosa'),\n",
       "       (24, 5.1, 3.3, 1.7, 0.5, u'setosa'),\n",
       "       (25, 4.8, 3.4, 1.9, 0.2, u'setosa'),\n",
       "       (26, 5.0, 3.0, 1.6, 0.2, u'setosa'),\n",
       "       (27, 5.0, 3.4, 1.6, 0.4, u'setosa'),\n",
       "       (28, 5.2, 3.5, 1.5, 0.2, u'setosa'),\n",
       "       (29, 5.2, 3.4, 1.4, 0.2, u'setosa'),\n",
       "       (30, 4.7, 3.2, 1.6, 0.2, u'setosa'),\n",
       "       (31, 4.8, 3.1, 1.6, 0.2, u'setosa'),\n",
       "       (32, 5.4, 3.4, 1.5, 0.4, u'setosa'),\n",
       "       (33, 5.2, 4.1, 1.5, 0.1, u'setosa'),\n",
       "       (34, 5.5, 4.2, 1.4, 0.2, u'setosa'),\n",
       "       (35, 4.9, 3.1, 1.5, 0.2, u'setosa'),\n",
       "       (36, 5.0, 3.2, 1.2, 0.2, u'setosa'),\n",
       "       (37, 5.5, 3.5, 1.3, 0.2, u'setosa'),\n",
       "       (38, 4.9, 3.6, 1.4, 0.1, u'setosa'),\n",
       "       (39, 4.4, 3.0, 1.3, 0.2, u'setosa'),\n",
       "       (40, 5.1, 3.4, 1.5, 0.2, u'setosa'),\n",
       "       (41, 5.0, 3.5, 1.3, 0.3, u'setosa'),\n",
       "       (42, 4.5, 2.3, 1.3, 0.3, u'setosa'),\n",
       "       (43, 4.4, 3.2, 1.3, 0.2, u'setosa'),\n",
       "       (44, 5.0, 3.5, 1.6, 0.6, u'setosa'),\n",
       "       (45, 5.1, 3.8, 1.9, 0.4, u'setosa'),\n",
       "       (46, 4.8, 3.0, 1.4, 0.3, u'setosa'),\n",
       "       (47, 5.1, 3.8, 1.6, 0.2, u'setosa'),\n",
       "       (48, 4.6, 3.2, 1.4, 0.2, u'setosa'),\n",
       "       (49, 5.3, 3.7, 1.5, 0.2, u'setosa'),\n",
       "       (50, 5.0, 3.3, 1.4, 0.2, u'setosa'),\n",
       "       (51, 7.0, 3.2, 4.7, 1.4, u'versicolor'),\n",
       "       (52, 6.4, 3.2, 4.5, 1.5, u'versicolor'),\n",
       "       (53, 6.9, 3.1, 4.9, 1.5, u'versicolor'),\n",
       "       (54, 5.5, 2.3, 4.0, 1.3, u'versicolor'),\n",
       "       (55, 6.5, 2.8, 4.6, 1.5, u'versicolor'),\n",
       "       (56, 5.7, 2.8, 4.5, 1.3, u'versicolor'),\n",
       "       (57, 6.3, 3.3, 4.7, 1.6, u'versicolor'),\n",
       "       (58, 4.9, 2.4, 3.3, 1.0, u'versicolor'),\n",
       "       (59, 6.6, 2.9, 4.6, 1.3, u'versicolor'),\n",
       "       (60, 5.2, 2.7, 3.9, 1.4, u'versicolor'),\n",
       "       (61, 5.0, 2.0, 3.5, 1.0, u'versicolor'),\n",
       "       (62, 5.9, 3.0, 4.2, 1.5, u'versicolor'),\n",
       "       (63, 6.0, 2.2, 4.0, 1.0, u'versicolor'),\n",
       "       (64, 6.1, 2.9, 4.7, 1.4, u'versicolor'),\n",
       "       (65, 5.6, 2.9, 3.6, 1.3, u'versicolor'),\n",
       "       (66, 6.7, 3.1, 4.4, 1.4, u'versicolor'),\n",
       "       (67, 5.6, 3.0, 4.5, 1.5, u'versicolor'),\n",
       "       (68, 5.8, 2.7, 4.1, 1.0, u'versicolor'),\n",
       "       (69, 6.2, 2.2, 4.5, 1.5, u'versicolor'),\n",
       "       (70, 5.6, 2.5, 3.9, 1.1, u'versicolor'),\n",
       "       (71, 5.9, 3.2, 4.8, 1.8, u'versicolor'),\n",
       "       (72, 6.1, 2.8, 4.0, 1.3, u'versicolor'),\n",
       "       (73, 6.3, 2.5, 4.9, 1.5, u'versicolor'),\n",
       "       (74, 6.1, 2.8, 4.7, 1.2, u'versicolor'),\n",
       "       (75, 6.4, 2.9, 4.3, 1.3, u'versicolor'),\n",
       "       (76, 6.6, 3.0, 4.4, 1.4, u'versicolor'),\n",
       "       (77, 6.8, 2.8, 4.8, 1.4, u'versicolor'),\n",
       "       (78, 6.7, 3.0, 5.0, 1.7, u'versicolor'),\n",
       "       (79, 6.0, 2.9, 4.5, 1.5, u'versicolor'),\n",
       "       (80, 5.7, 2.6, 3.5, 1.0, u'versicolor'),\n",
       "       (81, 5.5, 2.4, 3.8, 1.1, u'versicolor'),\n",
       "       (82, 5.5, 2.4, 3.7, 1.0, u'versicolor'),\n",
       "       (83, 5.8, 2.7, 3.9, 1.2, u'versicolor'),\n",
       "       (84, 6.0, 2.7, 5.1, 1.6, u'versicolor'),\n",
       "       (85, 5.4, 3.0, 4.5, 1.5, u'versicolor'),\n",
       "       (86, 6.0, 3.4, 4.5, 1.6, u'versicolor'),\n",
       "       (87, 6.7, 3.1, 4.7, 1.5, u'versicolor'),\n",
       "       (88, 6.3, 2.3, 4.4, 1.3, u'versicolor'),\n",
       "       (89, 5.6, 3.0, 4.1, 1.3, u'versicolor'),\n",
       "       (90, 5.5, 2.5, 4.0, 1.3, u'versicolor'),\n",
       "       (91, 5.5, 2.6, 4.4, 1.2, u'versicolor'),\n",
       "       (92, 6.1, 3.0, 4.6, 1.4, u'versicolor'),\n",
       "       (93, 5.8, 2.6, 4.0, 1.2, u'versicolor'),\n",
       "       (94, 5.0, 2.3, 3.3, 1.0, u'versicolor'),\n",
       "       (95, 5.6, 2.7, 4.2, 1.3, u'versicolor'),\n",
       "       (96, 5.7, 3.0, 4.2, 1.2, u'versicolor'),\n",
       "       (97, 5.7, 2.9, 4.2, 1.3, u'versicolor'),\n",
       "       (98, 6.2, 2.9, 4.3, 1.3, u'versicolor'),\n",
       "       (99, 5.1, 2.5, 3.0, 1.1, u'versicolor'),\n",
       "       (100, 5.7, 2.8, 4.1, 1.3, u'versicolor'),\n",
       "       (101, 6.3, 3.3, 6.0, 2.5, u'virginica'),\n",
       "       (102, 5.8, 2.7, 5.1, 1.9, u'virginica'),\n",
       "       (103, 7.1, 3.0, 5.9, 2.1, u'virginica'),\n",
       "       (104, 6.3, 2.9, 5.6, 1.8, u'virginica'),\n",
       "       (105, 6.5, 3.0, 5.8, 2.2, u'virginica'),\n",
       "       (106, 7.6, 3.0, 6.6, 2.1, u'virginica'),\n",
       "       (107, 4.9, 2.5, 4.5, 1.7, u'virginica'),\n",
       "       (108, 7.3, 2.9, 6.3, 1.8, u'virginica'),\n",
       "       (109, 6.7, 2.5, 5.8, 1.8, u'virginica'),\n",
       "       (110, 7.2, 3.6, 6.1, 2.5, u'virginica'),\n",
       "       (111, 6.5, 3.2, 5.1, 2.0, u'virginica'),\n",
       "       (112, 6.4, 2.7, 5.3, 1.9, u'virginica'),\n",
       "       (113, 6.8, 3.0, 5.5, 2.1, u'virginica'),\n",
       "       (114, 5.7, 2.5, 5.0, 2.0, u'virginica'),\n",
       "       (115, 5.8, 2.8, 5.1, 2.4, u'virginica'),\n",
       "       (116, 6.4, 3.2, 5.3, 2.3, u'virginica'),\n",
       "       (117, 6.5, 3.0, 5.5, 1.8, u'virginica'),\n",
       "       (118, 7.7, 3.8, 6.7, 2.2, u'virginica'),\n",
       "       (119, 7.7, 2.6, 6.9, 2.3, u'virginica'),\n",
       "       (120, 6.0, 2.2, 5.0, 1.5, u'virginica'),\n",
       "       (121, 6.9, 3.2, 5.7, 2.3, u'virginica'),\n",
       "       (122, 5.6, 2.8, 4.9, 2.0, u'virginica'),\n",
       "       (123, 7.7, 2.8, 6.7, 2.0, u'virginica'),\n",
       "       (124, 6.3, 2.7, 4.9, 1.8, u'virginica'),\n",
       "       (125, 6.7, 3.3, 5.7, 2.1, u'virginica'),\n",
       "       (126, 7.2, 3.2, 6.0, 1.8, u'virginica'),\n",
       "       (127, 6.2, 2.8, 4.8, 1.8, u'virginica'),\n",
       "       (128, 6.1, 3.0, 4.9, 1.8, u'virginica'),\n",
       "       (129, 6.4, 2.8, 5.6, 2.1, u'virginica'),\n",
       "       (130, 7.2, 3.0, 5.8, 1.6, u'virginica'),\n",
       "       (131, 7.4, 2.8, 6.1, 1.9, u'virginica'),\n",
       "       (132, 7.9, 3.8, 6.4, 2.0, u'virginica'),\n",
       "       (133, 6.4, 2.8, 5.6, 2.2, u'virginica'),\n",
       "       (134, 6.3, 2.8, 5.1, 1.5, u'virginica'),\n",
       "       (135, 6.1, 2.6, 5.6, 1.4, u'virginica'),\n",
       "       (136, 7.7, 3.0, 6.1, 2.3, u'virginica'),\n",
       "       (137, 6.3, 3.4, 5.6, 2.4, u'virginica'),\n",
       "       (138, 6.4, 3.1, 5.5, 1.8, u'virginica'),\n",
       "       (139, 6.0, 3.0, 4.8, 1.8, u'virginica'),\n",
       "       (140, 6.9, 3.1, 5.4, 2.1, u'virginica'),\n",
       "       (141, 6.7, 3.1, 5.6, 2.4, u'virginica'),\n",
       "       (142, 6.9, 3.1, 5.1, 2.3, u'virginica'),\n",
       "       (143, 5.8, 2.7, 5.1, 1.9, u'virginica'),\n",
       "       (144, 6.8, 3.2, 5.9, 2.3, u'virginica'),\n",
       "       (145, 6.7, 3.3, 5.7, 2.5, u'virginica'),\n",
       "       (146, 6.7, 3.0, 5.2, 2.3, u'virginica'),\n",
       "       (147, 6.3, 2.5, 5.0, 1.9, u'virginica'),\n",
       "       (148, 6.5, 3.0, 5.2, 2.0, u'virginica'),\n",
       "       (149, 6.2, 3.4, 5.4, 2.3, u'virginica'),\n",
       "       (150, 5.9, 3.0, 5.1, 1.8, u'virginica')], \n",
       "      dtype=[('Unnamed: 0', '<i8'), ('SepalLength', '<f8'), ('SepalWidth', '<f8'), ('PetalLength', '<f8'), ('PetalWidth', '<f8'), ('Species', 'O')])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo(\"data/iris.csv\", np.ndarray)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr/>\n",
    "\n",
    "\n",
    "URI Strings\n",
    "-----------\n",
    "\n",
    "Odo refers to foreign data either with a Python object like a `sqlalchemy.Table` object for a SQL table, or with a string URI, like `postgresql://hostname::tablename`.\n",
    "\n",
    "URI's often take on the following form\n",
    "\n",
    "    protocol://path-to-resource::path-within-resource\n",
    "    \n",
    "Where `path-to-resource` might point to a file, a database hostname, etc. while `path-within-resource` might refer to a datapath or table name.  Note the two main separators\n",
    "\n",
    "*   `://` separates the protocol on the left (`sqlite`, `mongodb`, `ssh`, `hdfs`, `hive`, ...)\n",
    "*   `::` separates the path within the database on the right (e.g. tablename)\n",
    "\n",
    "[*odo docs on uri strings*](http://odo.readthedocs.org/en/latest/uri.html)\n",
    "\n",
    "<hr/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Examples\n",
    "\n",
    "Here are some example URIs\n",
    "\n",
    "```\n",
    "myfile.json\n",
    "myfiles.*.csv'\n",
    "postgresql://hostname::tablename\n",
    "mongodb://hostname/db::collection\n",
    "ssh://user@host:/path/to/myfile.csv\n",
    "hdfs://user@host:/path/to/*.csv\n",
    "```\n",
    "\n",
    "<hr />"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Exercise\n",
    "\n",
    "Migrate your CSV file into a table named `iris` in a new SQLite database at `sqlite:///my.db`.  Remember to use the `::` separator and to separate your database name from your table name.\n",
    "\n",
    "[*odo docs on SQL*](http://odo.readthedocs.org/en/latest/sql.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('iris', MetaData(bind=Engine(sqlite:///my.db)), Column('Unnamed: 0', BigInteger(), table=<iris>, nullable=False), Column('SepalLength', Float(precision=53), table=<iris>), Column('SepalWidth', Float(precision=53), table=<iris>), Column('PetalLength', Float(precision=53), table=<iris>), Column('PetalWidth', Float(precision=53), table=<iris>), Column('Species', Text(), table=<iris>), schema=None)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo(\"data/iris.csv\", \"sqlite:///my.db::iris\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What kind of object did you get receive as output?  Call `type` on your result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sqlalchemy.sql.schema.Table"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(_)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr/>\n",
    "\n",
    "How it works\n",
    "------------\n",
    "\n",
    "Odo is a network of fast pairwise conversions between pairs of formats.  We when we migrate between two formats we traverse a path of pairwise conversions.\n",
    "\n",
    "We visualize that network below:\n",
    "\n",
    "![](images/conversions.png)\n",
    "\n",
    "Each node represents a data format. Each directed edge represents a function to transform data between two formats. A single call to into may traverse multiple edges and multiple intermediate formats. Red nodes support larger-than-memory data.\n",
    "\n",
    "A single call to into may traverse several intermediate formats calling on several conversion functions.   For example, we when migrate a CSV file to a Mongo database we might take the following route:\n",
    "\n",
    "* Load in to a `DataFrame` (`pandas.read_csv`)\n",
    "* Convert to `np.recarray` (`DataFrame.to_records`)\n",
    "* Then to a Python `Iterator` (`np.ndarray.tolist`)\n",
    "* Finally to Mongo (`pymongo.Collection.insert`)\n",
    "\n",
    "Alternatively we could write a special function that uses MongoDB's native CSV\n",
    "loader and shortcut this entire process with a direct edge `CSV -> Mongo`.\n",
    "\n",
    "These functions are chosen because they are fast, often far faster than converting through a central serialization format.\n",
    "\n",
    "This picture is actually from an older version of `odo`, when the graph was still small enough to visualize pleasantly.  See [*odo docs*](http://odo.readthedocs.org/en/latest/overview.html) for a more updated version.\n",
    "\n",
    "<hr/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remote Data\n",
    "-----------\n",
    "\n",
    "We can interact with remote data in three locations\n",
    "\n",
    "1.  On Amazon's S3 (this will be quick)\n",
    "2.  On a remote machine via `ssh`\n",
    "3.  On the Hadoop File System (HDFS)\n",
    "\n",
    "For most of this we'll wait until we've seen Blaze, briefly we'll use S3.\n",
    "\n",
    "### S3\n",
    "\n",
    "For now, we quickly grab a file from Amazon's `S3`.\n",
    "\n",
    "This example depends on  [`boto`](https://boto.readthedocs.org/en/latest/) to interact with S3.\n",
    "\n",
    "    conda install boto\n",
    "\n",
    "[*odo docs on aws*](http://odo.readthedocs.org/en/latest/aws.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_bill</th>\n",
       "      <th>tip</th>\n",
       "      <th>sex</th>\n",
       "      <th>smoker</th>\n",
       "      <th>day</th>\n",
       "      <th>time</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16.99</td>\n",
       "      <td>1.01</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.34</td>\n",
       "      <td>1.66</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>21.01</td>\n",
       "      <td>3.50</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>23.68</td>\n",
       "      <td>3.31</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24.59</td>\n",
       "      <td>3.61</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>25.29</td>\n",
       "      <td>4.71</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8.77</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>26.88</td>\n",
       "      <td>3.12</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>15.04</td>\n",
       "      <td>1.96</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>14.78</td>\n",
       "      <td>3.23</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10.27</td>\n",
       "      <td>1.71</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>35.26</td>\n",
       "      <td>5.00</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>15.42</td>\n",
       "      <td>1.57</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>18.43</td>\n",
       "      <td>3.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14.83</td>\n",
       "      <td>3.02</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>21.58</td>\n",
       "      <td>3.92</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10.33</td>\n",
       "      <td>1.67</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>16.29</td>\n",
       "      <td>3.71</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>16.97</td>\n",
       "      <td>3.50</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sun</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>20.65</td>\n",
       "      <td>3.35</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>17.92</td>\n",
       "      <td>4.08</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>20.29</td>\n",
       "      <td>2.75</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>15.77</td>\n",
       "      <td>2.23</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>39.42</td>\n",
       "      <td>7.58</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>19.82</td>\n",
       "      <td>3.18</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>17.81</td>\n",
       "      <td>2.34</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>13.37</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>12.69</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>21.70</td>\n",
       "      <td>4.30</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>19.65</td>\n",
       "      <td>3.00</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>28.17</td>\n",
       "      <td>6.50</td>\n",
       "      <td>Female</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>12.90</td>\n",
       "      <td>1.10</td>\n",
       "      <td>Female</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>28.15</td>\n",
       "      <td>3.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>11.59</td>\n",
       "      <td>1.50</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>7.74</td>\n",
       "      <td>1.44</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>30.14</td>\n",
       "      <td>3.09</td>\n",
       "      <td>Female</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>12.16</td>\n",
       "      <td>2.20</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>13.42</td>\n",
       "      <td>3.48</td>\n",
       "      <td>Female</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>8.58</td>\n",
       "      <td>1.92</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>15.98</td>\n",
       "      <td>3.00</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>13.42</td>\n",
       "      <td>1.58</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>16.27</td>\n",
       "      <td>2.50</td>\n",
       "      <td>Female</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>10.09</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Female</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Fri</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>20.45</td>\n",
       "      <td>3.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>13.28</td>\n",
       "      <td>2.72</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>22.12</td>\n",
       "      <td>2.88</td>\n",
       "      <td>Female</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>24.01</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>15.69</td>\n",
       "      <td>3.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>11.61</td>\n",
       "      <td>3.39</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>10.77</td>\n",
       "      <td>1.47</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>15.53</td>\n",
       "      <td>3.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>10.07</td>\n",
       "      <td>1.25</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>236</th>\n",
       "      <td>12.60</td>\n",
       "      <td>1.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>32.83</td>\n",
       "      <td>1.17</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>35.83</td>\n",
       "      <td>4.67</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>29.03</td>\n",
       "      <td>5.92</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240</th>\n",
       "      <td>27.18</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Female</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>22.67</td>\n",
       "      <td>2.00</td>\n",
       "      <td>Male</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>17.82</td>\n",
       "      <td>1.75</td>\n",
       "      <td>Male</td>\n",
       "      <td>No</td>\n",
       "      <td>Sat</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>18.78</td>\n",
       "      <td>3.00</td>\n",
       "      <td>Female</td>\n",
       "      <td>No</td>\n",
       "      <td>Thur</td>\n",
       "      <td>Dinner</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>244 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     total_bill   tip     sex smoker   day    time  size\n",
       "0         16.99  1.01  Female     No   Sun  Dinner     2\n",
       "1         10.34  1.66    Male     No   Sun  Dinner     3\n",
       "2         21.01  3.50    Male     No   Sun  Dinner     3\n",
       "3         23.68  3.31    Male     No   Sun  Dinner     2\n",
       "4         24.59  3.61  Female     No   Sun  Dinner     4\n",
       "5         25.29  4.71    Male     No   Sun  Dinner     4\n",
       "6          8.77  2.00    Male     No   Sun  Dinner     2\n",
       "7         26.88  3.12    Male     No   Sun  Dinner     4\n",
       "8         15.04  1.96    Male     No   Sun  Dinner     2\n",
       "9         14.78  3.23    Male     No   Sun  Dinner     2\n",
       "10        10.27  1.71    Male     No   Sun  Dinner     2\n",
       "11        35.26  5.00  Female     No   Sun  Dinner     4\n",
       "12        15.42  1.57    Male     No   Sun  Dinner     2\n",
       "13        18.43  3.00    Male     No   Sun  Dinner     4\n",
       "14        14.83  3.02  Female     No   Sun  Dinner     2\n",
       "15        21.58  3.92    Male     No   Sun  Dinner     2\n",
       "16        10.33  1.67  Female     No   Sun  Dinner     3\n",
       "17        16.29  3.71    Male     No   Sun  Dinner     3\n",
       "18        16.97  3.50  Female     No   Sun  Dinner     3\n",
       "19        20.65  3.35    Male     No   Sat  Dinner     3\n",
       "20        17.92  4.08    Male     No   Sat  Dinner     2\n",
       "21        20.29  2.75  Female     No   Sat  Dinner     2\n",
       "22        15.77  2.23  Female     No   Sat  Dinner     2\n",
       "23        39.42  7.58    Male     No   Sat  Dinner     4\n",
       "24        19.82  3.18    Male     No   Sat  Dinner     2\n",
       "25        17.81  2.34    Male     No   Sat  Dinner     4\n",
       "26        13.37  2.00    Male     No   Sat  Dinner     2\n",
       "27        12.69  2.00    Male     No   Sat  Dinner     2\n",
       "28        21.70  4.30    Male     No   Sat  Dinner     2\n",
       "29        19.65  3.00  Female     No   Sat  Dinner     2\n",
       "..          ...   ...     ...    ...   ...     ...   ...\n",
       "214       28.17  6.50  Female    Yes   Sat  Dinner     3\n",
       "215       12.90  1.10  Female    Yes   Sat  Dinner     2\n",
       "216       28.15  3.00    Male    Yes   Sat  Dinner     5\n",
       "217       11.59  1.50    Male    Yes   Sat  Dinner     2\n",
       "218        7.74  1.44    Male    Yes   Sat  Dinner     2\n",
       "219       30.14  3.09  Female    Yes   Sat  Dinner     4\n",
       "220       12.16  2.20    Male    Yes   Fri   Lunch     2\n",
       "221       13.42  3.48  Female    Yes   Fri   Lunch     2\n",
       "222        8.58  1.92    Male    Yes   Fri   Lunch     1\n",
       "223       15.98  3.00  Female     No   Fri   Lunch     3\n",
       "224       13.42  1.58    Male    Yes   Fri   Lunch     2\n",
       "225       16.27  2.50  Female    Yes   Fri   Lunch     2\n",
       "226       10.09  2.00  Female    Yes   Fri   Lunch     2\n",
       "227       20.45  3.00    Male     No   Sat  Dinner     4\n",
       "228       13.28  2.72    Male     No   Sat  Dinner     2\n",
       "229       22.12  2.88  Female    Yes   Sat  Dinner     2\n",
       "230       24.01  2.00    Male    Yes   Sat  Dinner     4\n",
       "231       15.69  3.00    Male    Yes   Sat  Dinner     3\n",
       "232       11.61  3.39    Male     No   Sat  Dinner     2\n",
       "233       10.77  1.47    Male     No   Sat  Dinner     2\n",
       "234       15.53  3.00    Male    Yes   Sat  Dinner     2\n",
       "235       10.07  1.25    Male     No   Sat  Dinner     2\n",
       "236       12.60  1.00    Male    Yes   Sat  Dinner     2\n",
       "237       32.83  1.17    Male    Yes   Sat  Dinner     2\n",
       "238       35.83  4.67  Female     No   Sat  Dinner     3\n",
       "239       29.03  5.92    Male     No   Sat  Dinner     3\n",
       "240       27.18  2.00  Female    Yes   Sat  Dinner     2\n",
       "241       22.67  2.00    Male    Yes   Sat  Dinner     2\n",
       "242       17.82  1.75    Male     No   Sat  Dinner     2\n",
       "243       18.78  3.00  Female     No  Thur  Dinner     2\n",
       "\n",
       "[244 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo('s3://nyqpug/tips.csv', pd.DataFrame)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Blaze\n",
    "=====\n",
    "\n",
    "Blaze translates a subset of numpy/pandas syntax into database queries.  It hides away the database.\n",
    "\n",
    "On simple datasets, like CSV files, Blaze acts like Pandas with slightly different syntax.  In this case Blaze is just using Pandas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr/>\n",
    "\n",
    "### Pandas example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>SepalLength</th>\n",
       "      <th>SepalWidth</th>\n",
       "      <th>PetalLength</th>\n",
       "      <th>PetalWidth</th>\n",
       "      <th>Species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  SepalLength  SepalWidth  PetalLength  PetalWidth Species\n",
       "0           1          5.1         3.5          1.4         0.2  setosa\n",
       "1           2          4.9         3.0          1.4         0.2  setosa\n",
       "2           3          4.7         3.2          1.3         0.2  setosa\n",
       "3           4          4.6         3.1          1.5         0.2  setosa\n",
       "4           5          5.0         3.6          1.4         0.2  setosa"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('data/iris.csv')\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['setosa', 'versicolor', 'virginica'], dtype=object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Species.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          setosa\n",
       "50     versicolor\n",
       "100     virginica\n",
       "Name: Species, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.Species.drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr/>\n",
    "\n",
    "### Blaze example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>SepalLength</th>\n",
       "      <th>SepalWidth</th>\n",
       "      <th>PetalLength</th>\n",
       "      <th>PetalWidth</th>\n",
       "      <th>Species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5.1</td>\n",
       "      <td>3.5</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>4.9</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4.7</td>\n",
       "      <td>3.2</td>\n",
       "      <td>1.3</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>4.6</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1.5</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>5.0</td>\n",
       "      <td>3.6</td>\n",
       "      <td>1.4</td>\n",
       "      <td>0.2</td>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   Unnamed: 0  SepalLength  SepalWidth  PetalLength  PetalWidth Species\n",
       "0           1          5.1         3.5          1.4         0.2  setosa\n",
       "1           2          4.9         3.0          1.4         0.2  setosa\n",
       "2           3          4.7         3.2          1.3         0.2  setosa\n",
       "3           4          4.6         3.1          1.5         0.2  setosa\n",
       "4           5          5.0         3.6          1.4         0.2  setosa"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import blaze as bz\n",
    "\n",
    "d = bz.Data('data/iris.csv')\n",
    "d.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>versicolor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "      Species\n",
       "0      setosa\n",
       "1  versicolor\n",
       "2   virginica"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d.Species.distinct()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr/>\n",
    "\n",
    "Foreign Data\n",
    "------------\n",
    "\n",
    "Blaze does different things under-the-hood on different kinds of data\n",
    "\n",
    "*  CSV files: Pandas DataFrames (or iterators of DataFrames)\n",
    "*  SQL tables: [SQLAlchemy](http://sqlalchemy.org).\n",
    "*  Mongo collections: [PyMongo](http://api.mongodb.org/python/current/)\n",
    "*  ...\n",
    "\n",
    "SQL\n",
    "---\n",
    "\n",
    "We'll play with SQL a lot during this tutorial.  Blaze translates your query to SQLAlchemy.  SQLAlchemy then translates to the SQL dialect of your database, your database then executes that query intelligently.\n",
    "\n",
    "* Blaze $\\rightarrow$ SQLAlchemy $\\rightarrow$ SQL $\\rightarrow$ Database computation\n",
    "\n",
    "This translation process lets analysts interact with a familiar interface while leveraging a potentially powerful database.\n",
    "\n",
    "To keep things local we'll use SQLite, but this works with any database with a SQLAlchemy dialect.  Examples in this section use the iris dataset.  Exercises use the Lahman Baseball statistics database, year 2013.\n",
    "\n",
    "If you have not downloaded this dataset you could do so here - https://github.com/jknecht/baseball-archive-sqlite/raw/master/lahman2013.sqlite.  \n",
    "\n",
    "<hr/>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Examples\n",
    "\n",
    "Lets dive into Blaze Syntax.  For simple queries it looks and feels similar to Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "db = bz.Data('sqlite:///my.db')\n",
    "#db.iris\n",
    "#db.iris.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Species</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>setosa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>versicolor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>virginica</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "      Species\n",
       "0     Species\n",
       "1      setosa\n",
       "2  versicolor\n",
       "3   virginica"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.iris.Species.distinct()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Species</th>\n",
       "      <th>SepalLength</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>6.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>6.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>6.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>5.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>6.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>4.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>6.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>5.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>versicolor</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "       Species  SepalLength\n",
       "0   versicolor          7.0\n",
       "1   versicolor          6.4\n",
       "2   versicolor          6.9\n",
       "3   versicolor          5.5\n",
       "4   versicolor          6.5\n",
       "5   versicolor          5.7\n",
       "6   versicolor          6.3\n",
       "7   versicolor          4.9\n",
       "8   versicolor          6.6\n",
       "9   versicolor          5.2\n",
       "..."
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db.iris[db.iris.Species == 'versicolor'][['Species', 'SepalLength']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr />\n",
    "\n",
    "### Work happens on the database\n",
    "\n",
    "If we were using pandas we would read the table into pandas, then use pandas' fast in-memory algorithms for computation.  Here we translate your query into SQL and then send that query to the database to do the work.\n",
    "\n",
    "* Pandas $\\leftarrow_\\textrm{data}$ SQL, then Pandas computes\n",
    "* Blaze $\\rightarrow_\\textrm{query}$ SQL, then database computes\n",
    "\n",
    "If we want to dive into the internal API we can inspect the query that Blaze transmits.\n",
    "\n",
    "<hr />"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT iris.\"Species\", iris.\"SepalLength\" \n",
      "FROM iris \n",
      "WHERE iris.\"Species\" = ?\n"
     ]
    }
   ],
   "source": [
    "# Inspect SQL query\n",
    "query = db.iris[db.iris.Species == 'versicolor'][['Species', 'SepalLength']]\n",
    "print bz.compute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT iris.\"Species\", max(iris.\"PetalLength\") AS longest, min(iris.\"PetalLength\") AS shortest \n",
      "FROM iris GROUP BY iris.\"Species\"\n"
     ]
    }
   ],
   "source": [
    "query = bz.by(db.iris.Species, longest=db.iris.PetalLength.max(),\n",
    "                              shortest=db.iris.PetalLength.min())\n",
    "print bz.compute(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr />\n",
    "\n",
    "### Exercises\n",
    "\n",
    "Now we load the Lahman baseball database and perform similar queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dshape(\"\"\"{\n",
       "  AllstarFull: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    gameNum: ?int32,\n",
       "    gameID: ?string,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    GP: ?int32,\n",
       "    startingPos: ?int32\n",
       "    },\n",
       "  Appearances: var * {\n",
       "    yearID: ?int32,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    playerID: ?string,\n",
       "    G_all: ?int32,\n",
       "    GS: ?int32,\n",
       "    G_batting: ?int32,\n",
       "    G_defense: ?int32,\n",
       "    G_p: ?int32,\n",
       "    G_c: ?int32,\n",
       "    G_1b: ?int32,\n",
       "    G_2b: ?int32,\n",
       "    G_3b: ?int32,\n",
       "    G_ss: ?int32,\n",
       "    G_lf: ?int32,\n",
       "    G_cf: ?int32,\n",
       "    G_rf: ?int32,\n",
       "    G_of: ?int32,\n",
       "    G_dh: ?int32,\n",
       "    G_ph: ?int32,\n",
       "    G_pr: ?int32\n",
       "    },\n",
       "  AwardsManagers: var * {\n",
       "    playerID: ?string,\n",
       "    awardID: ?string,\n",
       "    yearID: ?int32,\n",
       "    lgID: ?string,\n",
       "    tie: ?string,\n",
       "    notes: ?string\n",
       "    },\n",
       "  AwardsPlayers: var * {\n",
       "    playerID: ?string,\n",
       "    awardID: ?string,\n",
       "    yearID: ?int32,\n",
       "    lgID: ?string,\n",
       "    tie: ?string,\n",
       "    notes: ?string\n",
       "    },\n",
       "  AwardsShareManagers: var * {\n",
       "    awardID: ?string,\n",
       "    yearID: ?int32,\n",
       "    lgID: ?string,\n",
       "    playerID: ?string,\n",
       "    pointsWon: ?int32,\n",
       "    pointsMax: ?int32,\n",
       "    votesFirst: ?int32\n",
       "    },\n",
       "  AwardsSharePlayers: var * {\n",
       "    awardID: ?string,\n",
       "    yearID: ?int32,\n",
       "    lgID: ?string,\n",
       "    playerID: ?string,\n",
       "    pointsWon: ?float64,\n",
       "    pointsMax: ?int32,\n",
       "    votesFirst: ?float64\n",
       "    },\n",
       "  Batting: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    stint: ?int32,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    G: ?int32,\n",
       "    G_batting: ?int32,\n",
       "    AB: ?int32,\n",
       "    R: ?int32,\n",
       "    H: ?int32,\n",
       "    2B: ?int32,\n",
       "    3B: ?int32,\n",
       "    HR: ?int32,\n",
       "    RBI: ?int32,\n",
       "    SB: ?int32,\n",
       "    CS: ?int32,\n",
       "    BB: ?int32,\n",
       "    SO: ?int32,\n",
       "    IBB: ?int32,\n",
       "    HBP: ?int32,\n",
       "    SH: ?int32,\n",
       "    SF: ?int32,\n",
       "    GIDP: ?int32,\n",
       "    G_old: ?int32\n",
       "    },\n",
       "  BattingPost: var * {\n",
       "    yearID: ?int32,\n",
       "    round: ?string,\n",
       "    playerID: ?string,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    G: ?int32,\n",
       "    AB: ?int32,\n",
       "    R: ?int32,\n",
       "    H: ?int32,\n",
       "    2B: ?int32,\n",
       "    3B: ?int32,\n",
       "    HR: ?int32,\n",
       "    RBI: ?int32,\n",
       "    SB: ?int32,\n",
       "    CS: ?int32,\n",
       "    BB: ?int32,\n",
       "    SO: ?int32,\n",
       "    IBB: ?int32,\n",
       "    HBP: ?int32,\n",
       "    SH: ?int32,\n",
       "    SF: ?int32,\n",
       "    GIDP: ?int32\n",
       "    },\n",
       "  Fielding: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    stint: ?int32,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    POS: ?string,\n",
       "    G: ?int32,\n",
       "    GS: ?int32,\n",
       "    InnOuts: ?int32,\n",
       "    PO: ?int32,\n",
       "    A: ?int32,\n",
       "    E: ?int32,\n",
       "    DP: ?int32,\n",
       "    PB: ?int32,\n",
       "    WP: ?int32,\n",
       "    SB: ?int32,\n",
       "    CS: ?int32,\n",
       "    ZR: ?float64\n",
       "    },\n",
       "  FieldingOF: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    stint: ?int32,\n",
       "    Glf: ?int32,\n",
       "    Gcf: ?int32,\n",
       "    Grf: ?int32\n",
       "    },\n",
       "  FieldingPost: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    round: ?string,\n",
       "    POS: ?string,\n",
       "    G: ?int32,\n",
       "    GS: ?int32,\n",
       "    InnOuts: ?int32,\n",
       "    PO: ?int32,\n",
       "    A: ?int32,\n",
       "    E: ?int32,\n",
       "    DP: ?int32,\n",
       "    TP: ?int32,\n",
       "    PB: ?int32,\n",
       "    SB: ?int32,\n",
       "    CS: ?int32\n",
       "    },\n",
       "  HallOfFame: var * {\n",
       "    playerID: ?string,\n",
       "    yearid: ?int32,\n",
       "    votedBy: ?string,\n",
       "    ballots: ?int32,\n",
       "    needed: ?int32,\n",
       "    votes: ?int32,\n",
       "    inducted: ?string,\n",
       "    category: ?string,\n",
       "    needed_note: ?string\n",
       "    },\n",
       "  Managers: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    inseason: ?int32,\n",
       "    G: ?int32,\n",
       "    W: ?int32,\n",
       "    L: ?int32,\n",
       "    rank: ?int32,\n",
       "    plyrMgr: ?string\n",
       "    },\n",
       "  ManagersHalf: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    inseason: ?int32,\n",
       "    half: ?int32,\n",
       "    G: ?int32,\n",
       "    W: ?int32,\n",
       "    L: ?int32,\n",
       "    rank: ?int32\n",
       "    },\n",
       "  Master: var * {\n",
       "    playerID: ?string,\n",
       "    birthYear: ?int32,\n",
       "    birthMonth: ?int32,\n",
       "    birthDay: ?int32,\n",
       "    birthCountry: ?string,\n",
       "    birthState: ?string,\n",
       "    birthCity: ?string,\n",
       "    deathYear: ?int32,\n",
       "    deathMonth: ?int32,\n",
       "    deathDay: ?int32,\n",
       "    deathCountry: ?string,\n",
       "    deathState: ?string,\n",
       "    deathCity: ?string,\n",
       "    nameFirst: ?string,\n",
       "    nameLast: ?string,\n",
       "    nameGiven: ?string,\n",
       "    weight: ?int32,\n",
       "    height: ?float64,\n",
       "    bats: ?string,\n",
       "    throws: ?string,\n",
       "    debut: ?float64,\n",
       "    finalGame: ?float64,\n",
       "    retroID: ?string,\n",
       "    bbrefID: ?string\n",
       "    },\n",
       "  Pitching: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    stint: ?int32,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    W: ?int32,\n",
       "    L: ?int32,\n",
       "    G: ?int32,\n",
       "    GS: ?int32,\n",
       "    CG: ?int32,\n",
       "    SHO: ?int32,\n",
       "    SV: ?int32,\n",
       "    IPouts: ?int32,\n",
       "    H: ?int32,\n",
       "    ER: ?int32,\n",
       "    HR: ?int32,\n",
       "    BB: ?int32,\n",
       "    SO: ?int32,\n",
       "    BAOpp: ?float64,\n",
       "    ERA: ?float64,\n",
       "    IBB: ?int32,\n",
       "    WP: ?int32,\n",
       "    HBP: ?int32,\n",
       "    BK: ?int32,\n",
       "    BFP: ?int32,\n",
       "    GF: ?int32,\n",
       "    R: ?int32,\n",
       "    SH: ?int32,\n",
       "    SF: ?int32,\n",
       "    GIDP: ?int32\n",
       "    },\n",
       "  PitchingPost: var * {\n",
       "    playerID: ?string,\n",
       "    yearID: ?int32,\n",
       "    round: ?string,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    W: ?int32,\n",
       "    L: ?int32,\n",
       "    G: ?int32,\n",
       "    GS: ?int32,\n",
       "    CG: ?int32,\n",
       "    SHO: ?int32,\n",
       "    SV: ?int32,\n",
       "    IPouts: ?int32,\n",
       "    H: ?int32,\n",
       "    ER: ?int32,\n",
       "    HR: ?int32,\n",
       "    BB: ?int32,\n",
       "    SO: ?int32,\n",
       "    BAOpp: ?float64,\n",
       "    ERA: ?float64,\n",
       "    IBB: ?int32,\n",
       "    WP: ?int32,\n",
       "    HBP: ?int32,\n",
       "    BK: ?int32,\n",
       "    BFP: ?int32,\n",
       "    GF: ?int32,\n",
       "    R: ?int32,\n",
       "    SH: ?int32,\n",
       "    SF: ?int32,\n",
       "    GIDP: ?int32\n",
       "    },\n",
       "  Salaries: var * {\n",
       "    yearID: ?int32,\n",
       "    teamID: ?string,\n",
       "    lgID: ?string,\n",
       "    playerID: ?string,\n",
       "    salary: ?float64\n",
       "    },\n",
       "  Schools: var * {\n",
       "    schoolID: ?string,\n",
       "    schoolName: ?string,\n",
       "    schoolCity: ?string,\n",
       "    schoolState: ?string,\n",
       "    schoolNick: ?string\n",
       "    },\n",
       "  SchoolsPlayers: var * {\n",
       "    playerID: ?string,\n",
       "    schoolID: ?string,\n",
       "    yearMin: ?int32,\n",
       "    yearMax: ?int32\n",
       "    },\n",
       "  SeriesPost: var * {\n",
       "    yearID: ?int32,\n",
       "    round: ?string,\n",
       "    teamIDwinner: ?string,\n",
       "    lgIDwinner: ?string,\n",
       "    teamIDloser: ?string,\n",
       "    lgIDloser: ?string,\n",
       "    wins: ?int32,\n",
       "    losses: ?int32,\n",
       "    ties: ?int32\n",
       "    },\n",
       "  Teams: var * {\n",
       "    yearID: ?int32,\n",
       "    lgID: ?string,\n",
       "    teamID: ?string,\n",
       "    franchID: ?string,\n",
       "    divID: ?string,\n",
       "    Rank: ?int32,\n",
       "    G: ?int32,\n",
       "    Ghome: ?int32,\n",
       "    W: ?int32,\n",
       "    L: ?int32,\n",
       "    DivWin: ?string,\n",
       "    WCWin: ?string,\n",
       "    LgWin: ?string,\n",
       "    WSWin: ?string,\n",
       "    R: ?int32,\n",
       "    AB: ?int32,\n",
       "    H: ?int32,\n",
       "    2B: ?int32,\n",
       "    3B: ?int32,\n",
       "    HR: ?int32,\n",
       "    BB: ?int32,\n",
       "    SO: ?int32,\n",
       "    SB: ?int32,\n",
       "    CS: ?int32,\n",
       "    HBP: ?int32,\n",
       "    SF: ?int32,\n",
       "    RA: ?int32,\n",
       "    ER: ?int32,\n",
       "    ERA: ?float64,\n",
       "    CG: ?int32,\n",
       "    SHO: ?int32,\n",
       "    SV: ?int32,\n",
       "    IPouts: ?int32,\n",
       "    HA: ?int32,\n",
       "    HRA: ?int32,\n",
       "    BBA: ?int32,\n",
       "    SOA: ?int32,\n",
       "    E: ?int32,\n",
       "    DP: ?int32,\n",
       "    FP: ?float64,\n",
       "    name: ?string,\n",
       "    park: ?string,\n",
       "    attendance: ?int32,\n",
       "    BPF: ?int32,\n",
       "    PPF: ?int32,\n",
       "    teamIDBR: ?string,\n",
       "    teamIDlahman45: ?string,\n",
       "    teamIDretro: ?string\n",
       "    },\n",
       "  TeamsFranchises: var * {\n",
       "    franchID: ?string,\n",
       "    franchName: ?string,\n",
       "    active: ?string,\n",
       "    NAassoc: ?string\n",
       "    },\n",
       "  TeamsHalf: var * {\n",
       "    yearID: ?int32,\n",
       "    lgID: ?string,\n",
       "    teamID: ?string,\n",
       "    Half: ?string,\n",
       "    divID: ?string,\n",
       "    DivWin: ?string,\n",
       "    Rank: ?int32,\n",
       "    G: ?int32,\n",
       "    W: ?int32,\n",
       "    L: ?int32\n",
       "    },\n",
       "  temp: var * {ID: ?int32, namefull: ?string, born: ?float64}\n",
       "  }\"\"\")"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# db = bz.Data('postgresql://postgres:postgres@ec2-54-159-160-163.compute-1.amazonaws.com')  # Use Postgres if you don't have the sqlite file\n",
    "db = bz.Data('sqlite:///data/lahman2013.sqlite')\n",
    "db.dshape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dshape(\"\"\"var * {\n",
       "  yearID: ?int32,\n",
       "  teamID: ?string,\n",
       "  lgID: ?string,\n",
       "  playerID: ?string,\n",
       "  salary: ?float64\n",
       "  }\"\"\")"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# View the Salaries table\n",
    "t = bz.Data('sqlite:///data/lahman2013.sqlite::Salaries')\n",
    "t.dshape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>teamID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ATL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BOS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CHA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CLE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>HOU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>KCA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "   teamID\n",
       "0     ATL\n",
       "1     BAL\n",
       "2     BOS\n",
       "3     CAL\n",
       "4     CHA\n",
       "5     CHN\n",
       "6     CIN\n",
       "7     CLE\n",
       "8     DET\n",
       "9     HOU\n",
       "..."
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What are the distinct teamIDs in the Salaries table?\n",
    "t.teamID.distinct()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(u'ATL',),\n",
       " (u'BAL',),\n",
       " (u'BOS',),\n",
       " (u'CAL',),\n",
       " (u'CHA',),\n",
       " (u'CHN',),\n",
       " (u'CIN',),\n",
       " (u'CLE',),\n",
       " (u'DET',),\n",
       " (u'HOU',),\n",
       " (u'KCA',),\n",
       " (u'LAN',),\n",
       " (u'MIN',),\n",
       " (u'ML4',),\n",
       " (u'MON',),\n",
       " (u'NYA',),\n",
       " (u'NYN',),\n",
       " (u'OAK',),\n",
       " (u'PHI',),\n",
       " (u'PIT',),\n",
       " (u'SDN',),\n",
       " (u'SEA',),\n",
       " (u'SFN',),\n",
       " (u'SLN',),\n",
       " (u'TEX',),\n",
       " (u'TOR',),\n",
       " (u'COL',),\n",
       " (u'FLO',),\n",
       " (u'ANA',),\n",
       " (u'ARI',),\n",
       " (u'MIL',),\n",
       " (u'TBA',),\n",
       " (u'LAA',),\n",
       " (u'WAS',),\n",
       " (u'MIA',)]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from odo import odo\n",
    "\n",
    "odo(t.teamID.distinct(), list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT DISTINCT \"Salaries\".\"teamID\" AS \"teamID\" \n",
      "FROM \"Salaries\"\n"
     ]
    }
   ],
   "source": [
    "query = t.teamID.distinct()\n",
    "print bz.compute(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "1985"
      ],
      "text/plain": [
       "1985"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# What is the minimum and maximum yearID in the Salaries table?  \n",
    "t.yearID.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "2013"
      ],
      "text/plain": [
       "2013"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t.yearID.max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>playerID</th>\n",
       "      <th>salary</th>\n",
       "      <th>yearID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>atherke01</td>\n",
       "      <td>107333</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bakerdu01</td>\n",
       "      <td>575000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>birtsti01</td>\n",
       "      <td>60000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bochtbr01</td>\n",
       "      <td>350000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>codirch01</td>\n",
       "      <td>141500</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>collida02</td>\n",
       "      <td>700000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>davismi02</td>\n",
       "      <td>305000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>griffal01</td>\n",
       "      <td>610000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>heathmi02</td>\n",
       "      <td>425000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>hendest01</td>\n",
       "      <td>150000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>howelja01</td>\n",
       "      <td>262500</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "     playerID  salary  yearID\n",
       "0   atherke01  107333    1985\n",
       "1   bakerdu01  575000    1985\n",
       "2   birtsti01   60000    1985\n",
       "3   bochtbr01  350000    1985\n",
       "4   codirch01  141500    1985\n",
       "5   collida02  700000    1985\n",
       "6   davismi02  305000    1985\n",
       "7   griffal01  610000    1985\n",
       "8   heathmi02  425000    1985\n",
       "9   hendest01  150000    1985\n",
       "..."
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# For the Oakland Athletics (teamID OAK), pick out the playerID, salary, and yearID columns\n",
    "\n",
    "t[t.teamID=='OAK'][['playerID', 'salary', 'yearID']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "oak = t[t.teamID=='OAK'][['playerID', 'salary', 'yearID']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>playerID</th>\n",
       "      <th>salary</th>\n",
       "      <th>yearID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>atherke01</td>\n",
       "      <td>107333</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bakerdu01</td>\n",
       "      <td>575000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>birtsti01</td>\n",
       "      <td>60000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>bochtbr01</td>\n",
       "      <td>350000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>codirch01</td>\n",
       "      <td>141500</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>collida02</td>\n",
       "      <td>700000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>davismi02</td>\n",
       "      <td>305000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>griffal01</td>\n",
       "      <td>610000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>heathmi02</td>\n",
       "      <td>425000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>hendest01</td>\n",
       "      <td>150000</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>howelja01</td>\n",
       "      <td>262500</td>\n",
       "      <td>1985</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "     playerID  salary  yearID\n",
       "0   atherke01  107333    1985\n",
       "1   bakerdu01  575000    1985\n",
       "2   birtsti01   60000    1985\n",
       "3   bochtbr01  350000    1985\n",
       "4   codirch01  141500    1985\n",
       "5   collida02  700000    1985\n",
       "6   davismi02  305000    1985\n",
       "7   griffal01  610000    1985\n",
       "8   heathmi02  425000    1985\n",
       "9   hendest01  150000    1985\n",
       "..."
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oak"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<odo.backends.csv.CSV at 0x10f7eec90>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo(oak, 'oak.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>playerID</th>\n",
       "      <th>salary</th>\n",
       "      <th>yearID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>hollima01</td>\n",
       "      <td>13500000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>kendaja01</td>\n",
       "      <td>12858194</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>chaveer01</td>\n",
       "      <td>12500000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dyeje01</td>\n",
       "      <td>11666667</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>dyeje01</td>\n",
       "      <td>11666667</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>chaveer01</td>\n",
       "      <td>11500000</td>\n",
       "      <td>2008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>chaveer01</td>\n",
       "      <td>11500000</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>kendaja01</td>\n",
       "      <td>11492454</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>kendaja01</td>\n",
       "      <td>10571429</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>sheetbe01</td>\n",
       "      <td>10000000</td>\n",
       "      <td>2010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>chaveer01</td>\n",
       "      <td>9500000</td>\n",
       "      <td>2006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "     playerID    salary  yearID\n",
       "0   hollima01  13500000    2009\n",
       "1   kendaja01  12858194    2007\n",
       "2   chaveer01  12500000    2010\n",
       "3     dyeje01  11666667    2003\n",
       "4     dyeje01  11666667    2004\n",
       "5   chaveer01  11500000    2008\n",
       "6   chaveer01  11500000    2009\n",
       "7   kendaja01  11492454    2006\n",
       "8   kendaja01  10571429    2005\n",
       "9   sheetbe01  10000000    2010\n",
       "..."
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort that result by salary.  \n",
    "# Use the ascending=False keyword argument to the sort function to find the highest paid players\n",
    "\n",
    "oak.sort('salary',ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr />\n",
    "\n",
    "### Example: Split-apply-combine\n",
    "\n",
    "In Pandas we perform computations on a *per-group* basis with the `groupby` operator.  In Blaze our syntax is slightly different, using instead the `by` function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Species\n",
       "setosa        1.0\n",
       "versicolor    3.0\n",
       "virginica     4.5\n",
       "Name: PetalLength, dtype: float64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "iris = pd.read_csv('data/iris.csv')\n",
    "iris.groupby('Species').PetalLength.min()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Species\n",
      "setosa        1.0\n",
      "versicolor    3.0\n",
      "virginica     4.5\n",
      "Name: PetalLength, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "iris = bz.Data('sqlite:///my.db::iris')\n",
    "bz.by(iris.Species, largest=iris.PetalLength.max(), \n",
    "                    smallest=iris.PetalLength.min())\n",
    "print(_)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr/>\n",
    "\n",
    "Store Results\n",
    "-------------\n",
    "\n",
    "By default Blaze only shows us the first ten lines of a result.  This provides a more interactive feel and stops us from accidentally crushing our system.  Sometimes we do want to compute all of the results and store them someplace.\n",
    "\n",
    "Blaze expressions are valid sources for `odo`.  So we can store our results in any format."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<hr/>\n",
    "\n",
    "### Exercise: Storage\n",
    "\n",
    "The solution to the first split-apply-combine problem is below.  Store that result in a list, a CSV file, and in a new SQL table in our database (use a uri like `sqlite://...` to specify the SQL table.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "result = bz.by(db.Salaries.teamID, avg=db.Salaries.salary.mean(), \n",
    "                                   max=db.Salaries.salary.max(), \n",
    "                                   ratio=db.Salaries.salary.max() / db.Salaries.salary.min()\n",
    "                ).sort('ratio', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(u'PHI', 2092230.9326364691, 25000000.0, 416.6666666666667),\n",
       " (u'LAN', 2346982.6980255516, 23854494.0, 397.5749),\n",
       " (u'NYN', 2317349.977245509, 23145011.0, 385.7501833333333),\n",
       " (u'DET', 1980834.9902080784, 23000000.0, 383.3333333333333),\n",
       " (u'MIN', 1525031.650385604, 23000000.0, 383.3333333333333),\n",
       " (u'BOS', 2692113.8568075118, 22500000.0, 375.0),\n",
       " (u'SFN', 2044198.6841463414, 22250000.0, 370.8333333333333),\n",
       " (u'TEX', 1874651.551143201, 22000000.0, 366.6666666666667),\n",
       " (u'SEA', 1932288.913202934, 20557143.0, 342.61905),\n",
       " (u'CHA', 1992653.5012315272, 17000000.0, 340.0)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo(result, list)[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('result', MetaData(bind=Engine(sqlite:///my.db)), Column('teamID', Text(), table=<result>), Column('avg', Float(precision=53), table=<result>, nullable=False), Column('max', Float(precision=53), table=<result>), Column('ratio', Float(precision=53), table=<result>), schema=None)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "odo(result, 'sqlite:///my.db::result')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}