lessons/Data management.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:7144d40b448e5eefe830bed388c14e9e07bee9084aeefed67448abe0329fe191"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%matplotlib inline\n",
      "import matplotlib.pyplot as plt\n",
      "import numpy as np\n",
      "%precision 4\n",
      "import os, sys, glob"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Using SQLite3\n",
      "\n",
      "Willl change this to use the same example for queries and schema design\n",
      "\n",
      "* Subjects - Ann, Bob, Charlie\n",
      "* Tests - Liver function, Complete blood count\n",
      "* Test parameters - AST, ALT, RBC, platelets, WBC (may perform all or only subset of parameters)\n",
      "* Diffrent number of visits, different number of tests per visit"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Working example dataset\n",
      "\n",
      "This data contains the survival time after receiving a heart transplant, the age of the patient and whether or not the survival time was censored \n",
      "\n",
      "* Number of Observations - 69\n",
      "* Number of Variables - 3\n",
      "\n",
      "Variable name definitions::\n",
      "*    death - Days after surgery until death\n",
      "*    age - age at the time of surgery\n",
      "*    censored - indicates if an observation is censored.  1 is uncensored"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import statsmodels.api as sm\n",
      "heart = sm.datasets.heart.load_pandas().data\n",
      "heart.take(np.random.choice(len(heart), 6))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>survival</th>\n",
        "      <th>censors</th>\n",
        "      <th>age</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>32</th>\n",
        "      <td>  147</td>\n",
        "      <td> 1</td>\n",
        "      <td> 47.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>67</th>\n",
        "      <td>   13</td>\n",
        "      <td> 0</td>\n",
        "      <td> 28.9</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>49</th>\n",
        "      <td>  499</td>\n",
        "      <td> 0</td>\n",
        "      <td> 52.2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>24</th>\n",
        "      <td> 1367</td>\n",
        "      <td> 0</td>\n",
        "      <td> 48.6</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>20</th>\n",
        "      <td>   54</td>\n",
        "      <td> 1</td>\n",
        "      <td> 49.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1 </th>\n",
        "      <td>    3</td>\n",
        "      <td> 1</td>\n",
        "      <td> 40.4</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 2,
       "text": [
        "    survival  censors   age\n",
        "32       147        1  47.5\n",
        "67        13        0  28.9\n",
        "49       499        0  52.2\n",
        "24      1367        0  48.6\n",
        "20        54        1  49.0\n",
        "1          3        1  40.4"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import sqlite3\n",
      "conn = sqlite3.connect('heart.db')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Creating and populating a table"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c = conn.cursor()\n",
      "\n",
      "c.execute('''CREATE TABLE IF NOT EXISTS transplant\n",
      "             (survival integer, censors integer, age real)''')\n",
      "\n",
      "c.executemany(\"insert into transplant(survival, censors, age) values (?, ?, ?)\", heart.values);"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### SQL queries\n",
      "\n",
      "SQL Queries take the form\n",
      "```sql\n",
      "select (distinct) ... from ... (limit ...)\n",
      "where ...\n",
      "groupby ..\n",
      "order by ...\n",
      "```\n",
      "where most of the query apart from the ```select ... from ...``` are optional."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Selecting all columns, first 10 rows"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for row in c.execute('''select * from transplant limit 5;'''):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(15, 1, 54.3)\n",
        "(3, 1, 40.4)\n",
        "(624, 1, 51.0)\n",
        "(46, 1, 42.5)\n",
        "(127, 1, 48.0)\n"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Using where to filter rows"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# only find censored data for subjects < 40 years old\n",
      "for row in c.execute('''\n",
      "select * from transplant \n",
      "where censors=0 and age < 40 limit 5;'''):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(1775, 0, 33.3)\n",
        "(1106, 0, 36.8)\n",
        "(875, 0, 38.9)\n",
        "(815, 0, 32.7)\n",
        "(592, 0, 26.7)\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Using SQL functions"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for row in c.execute('''select count(*), avg(age) from transplant where censors=0 and age < 40;'''):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(9, 31.43333333333333)\n"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Using groupby to find number of cnesored and uncensored subjects and thier average age"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "query = '''\n",
      "select censors, count(*), avg(age) from transplant \n",
      "group by censors;\n",
      "'''\n",
      "for row in c.execute(query):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(0, 24, 41.729166666666664)\n",
        "(1, 45, 48.484444444444456)\n"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Using having to filter grouped results"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "query = '''\n",
      "select censors, count(*), avg(age) from transplant \n",
      "group by censors\n",
      "having avg(age) < 45;\n",
      "'''\n",
      "for row in c.execute(query):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(0, 24, 41.729166666666664)\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Using order by to sort results"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "query = '''\n",
      "select * from transplant \n",
      "where age < 40\n",
      "order by age desc;\n",
      "'''\n",
      "for row in c.execute(query):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(875, 0, 38.9)\n",
        "(1106, 0, 36.8)\n",
        "(44, 1, 36.2)\n",
        "(1, 0, 35.2)\n",
        "(1775, 0, 33.3)\n",
        "(815, 0, 32.7)\n",
        "(12, 1, 29.2)\n",
        "(13, 0, 28.9)\n",
        "(592, 0, 26.7)\n",
        "(167, 0, 26.7)\n",
        "(110, 0, 23.7)\n",
        "(228, 1, 19.7)\n"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Reading into a numpy structured array"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "result = c.execute(query).fetchall()\n",
      "arr = np.fromiter(result, dtype='i4,i4,f4')\n",
      "arr.dtype.names = ['survival', 'censors', 'age']\n",
      "print '\\n'.join(map(str, arr))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(875, 0, 38.900001525878906)\n",
        "(1106, 0, 36.79999923706055)\n",
        "(44, 1, 36.20000076293945)\n",
        "(1, 0, 35.20000076293945)\n",
        "(1775, 0, 33.29999923706055)\n",
        "(815, 0, 32.70000076293945)\n",
        "(12, 1, 29.200000762939453)\n",
        "(13, 0, 28.899999618530273)\n",
        "(592, 0, 26.700000762939453)\n",
        "(167, 0, 26.700000762939453)\n",
        "(110, 0, 23.700000762939453)\n",
        "(228, 1, 19.700000762939453)\n"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Reading into a numpy regular array"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from itertools import chain\n",
      "result = c.execute(query).fetchall()\n",
      "arr = np.fromiter(chain.from_iterable(result), dtype=np.float)\n",
      "print arr.reshape(-1,3)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[[  8.7500e+02   0.0000e+00   3.8900e+01]\n",
        " [  1.1060e+03   0.0000e+00   3.6800e+01]\n",
        " [  4.4000e+01   1.0000e+00   3.6200e+01]\n",
        " [  1.0000e+00   0.0000e+00   3.5200e+01]\n",
        " [  1.7750e+03   0.0000e+00   3.3300e+01]\n",
        " [  8.1500e+02   0.0000e+00   3.2700e+01]\n",
        " [  1.2000e+01   1.0000e+00   2.9200e+01]\n",
        " [  1.3000e+01   0.0000e+00   2.8900e+01]\n",
        " [  5.9200e+02   0.0000e+00   2.6700e+01]\n",
        " [  1.6700e+02   0.0000e+00   2.6700e+01]\n",
        " [  1.1000e+02   0.0000e+00   2.3700e+01]\n",
        " [  2.2800e+02   1.0000e+00   1.9700e+01]]\n"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Working wiht multiple tables in SQL"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We will consturct a new database with 2 tables to illustrate the concept of joins."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "conn1 = sqlite3.connect('samples.db')\n",
      "c1 = conn1.cursor()\n",
      "\n",
      "c1.execute(\n",
      "'''\n",
      "CREATE TABLE IF NOT EXISTS t1(\n",
      "  ID TEXT,\n",
      "  Name TEXT,\n",
      "  Value Real);\n",
      "''')\n",
      "\n",
      "c1.execute('''\n",
      "CREATE TABLE IF NOT EXISTS t2(\n",
      "  ID TEXT,\n",
      "  Name TEXT,\n",
      "  Value Real,\n",
      "  Age INTEGER);\n",
      "''');\n",
      "\n",
      "from string import ascii_lowercase\n",
      "for i in range(5):\n",
      "    c1.execute('''insert into t1(ID, Name, Value) values (%d, '%s', %.2f)''' % (i, ascii_lowercase[i], i*i));\n",
      "    c1.execute('''insert into t2(ID, Name, Value, Age) values (%d, '%s', %.2f, %d)''' % (i*2, ascii_lowercase[i*2], i*i+5, 10*i));"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 13
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Cartesian product"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Without specifiying a join, the result is all possible combinations\n",
      "query = '''\n",
      "select t1.ID, t2.ID from t1, t2; \n",
      "'''\n",
      "for row in c1.execute(query):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(u'0', u'0')\n",
        "(u'0', u'2')\n",
        "(u'0', u'4')\n",
        "(u'0', u'6')\n",
        "(u'0', u'8')\n",
        "(u'1', u'0')\n",
        "(u'1', u'2')\n",
        "(u'1', u'4')\n",
        "(u'1', u'6')\n",
        "(u'1', u'8')\n",
        "(u'2', u'0')\n",
        "(u'2', u'2')\n",
        "(u'2', u'4')\n",
        "(u'2', u'6')\n",
        "(u'2', u'8')\n",
        "(u'3', u'0')\n",
        "(u'3', u'2')\n",
        "(u'3', u'4')\n",
        "(u'3', u'6')\n",
        "(u'3', u'8')\n",
        "(u'4', u'0')\n",
        "(u'4', u'2')\n",
        "(u'4', u'4')\n",
        "(u'4', u'6')\n",
        "(u'4', u'8')\n"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Inner joins"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Inner join (intersection)\n",
      "query = '''\n",
      "select t1.ID, t2.ID, t1.value, t2.value, t1.value * t2.value from t1, t2\n",
      "where t1.ID = t2.ID;\n",
      "'''\n",
      "for row in c1.execute(query):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(u'0', u'0', 0.0, 5.0, 0.0)\n",
        "(u'2', u'2', 4.0, 6.0, 24.0)\n",
        "(u'4', u'4', 16.0, 9.0, 144.0)\n"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# left join keeps all values from the left table (t2) \n",
      "# and values from the right (t1) where there is a match\n",
      "query = '''\n",
      "select t1.id, t2.ID, t1.value, t2.value from t2 left join t1 on t1.ID = t2.ID\n",
      "'''\n",
      "for row in c1.execute(query):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(u'0', u'0', 0.0, 5.0)\n",
        "(u'2', u'2', 4.0, 6.0)\n",
        "(u'4', u'4', 16.0, 9.0)\n",
        "(None, u'6', None, 14.0)\n",
        "(None, u'8', None, 21.0)\n"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# same join but we swtich left and right tables\n",
      "query = '''\n",
      "select t1.ID, t2.ID, t1.value, t2.value from t1 left join t2 on t1.ID = t2.ID\n",
      "'''\n",
      "for row in c1.execute(query):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(u'0', u'0', 0.0, 5.0)\n",
        "(u'1', None, 1.0, None)\n",
        "(u'2', u'2', 4.0, 6.0)\n",
        "(u'3', None, 9.0, None)\n",
        "(u'4', u'4', 16.0, 9.0)\n"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "#### Self-joins"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# we can join a table to itself by using aliases \n",
      "# lets add a few more rows to t1 which may have the same id and name but different values\n",
      "\n",
      "for i in range(5):\n",
      "    c1.execute('''insert into t1(ID, Name, Value) values (%d, '%s', %.2f)''' % (i, ascii_lowercase[i], i*i*i));\n",
      "\n",
      "for row in c1.execute('select * from t1;'):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(u'0', u'a', 0.0)\n",
        "(u'1', u'b', 1.0)\n",
        "(u'2', u'c', 4.0)\n",
        "(u'3', u'd', 9.0)\n",
        "(u'4', u'e', 16.0)\n",
        "(u'0', u'a', 0.0)\n",
        "(u'1', u'b', 1.0)\n",
        "(u'2', u'c', 8.0)\n",
        "(u'3', u'd', 27.0)\n",
        "(u'4', u'e', 64.0)\n"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# Now use a self-join to find paired values for the same ID and name\n",
      "\n",
      "query = '''\n",
      "select t1a.ID, t1a.Name, t1a.value, t1b.value from t1 as t1a, t1 as t1b\n",
      "where t1a.Name = t1b.Name and t1a.Value < t1b.Value\n",
      "order by t1a.ID ASC;\n",
      "'''\n",
      "for row in c1.execute(query):\n",
      "    print row"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(u'2', u'c', 4.0, 8.0)\n",
        "(u'3', u'd', 9.0, 27.0)\n",
        "(u'4', u'e', 16.0, 64.0)\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Basic concepts of database normalization\n",
      "\n",
      "In which we convert a dataframe into a normalized database."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "names = ['ann', 'bob', 'ann', 'bob', 'carl', 'delia', 'ann']\n",
      "tests = ['wbc', 'wbc', 'rbc', 'rbc', 'wbc', 'rbc', 'platelets']\n",
      "values1 = [10, 11.2, 300, 204, 9.8, 340, 125]\n",
      "values2 = [10.6, 13.2, 322, 214, 10.3, 343, 145]\n",
      "df = pd.DataFrame([names, tests, values1, values2]).T\n",
      "df.columns = ['names', 'tests', 'values1', 'values2']\n",
      "df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>names</th>\n",
        "      <th>tests</th>\n",
        "      <th>values1</th>\n",
        "      <th>values2</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>   ann</td>\n",
        "      <td>       wbc</td>\n",
        "      <td>   10</td>\n",
        "      <td> 10.6</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td>   bob</td>\n",
        "      <td>       wbc</td>\n",
        "      <td> 11.2</td>\n",
        "      <td> 13.2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td>   ann</td>\n",
        "      <td>       rbc</td>\n",
        "      <td>  300</td>\n",
        "      <td>  322</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td>   bob</td>\n",
        "      <td>       rbc</td>\n",
        "      <td>  204</td>\n",
        "      <td>  214</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td>  carl</td>\n",
        "      <td>       wbc</td>\n",
        "      <td>  9.8</td>\n",
        "      <td> 10.3</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5</th>\n",
        "      <td> delia</td>\n",
        "      <td>       rbc</td>\n",
        "      <td>  340</td>\n",
        "      <td>  343</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>6</th>\n",
        "      <td>   ann</td>\n",
        "      <td> platelets</td>\n",
        "      <td>  125</td>\n",
        "      <td>  145</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 127,
       "text": [
        "   names      tests values1 values2\n",
        "0    ann        wbc      10    10.6\n",
        "1    bob        wbc    11.2    13.2\n",
        "2    ann        rbc     300     322\n",
        "3    bob        rbc     204     214\n",
        "4   carl        wbc     9.8    10.3\n",
        "5  delia        rbc     340     343\n",
        "6    ann  platelets     125     145"
       ]
      }
     ],
     "prompt_number": 127
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# names are put into their own table so there is no dubplication\n",
      "\n",
      "name_table = pd.DataFrame(df['names'].unique(), columns=['name'])\n",
      "name_table['name_id'] = name_table.index\n",
      "columns = ['name_id', 'name']\n",
      "name_table[columns]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>name_id</th>\n",
        "      <th>name</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td>   ann</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td>   bob</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2</td>\n",
        "      <td>  carl</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> 3</td>\n",
        "      <td> delia</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 129,
       "text": [
        "   name_id   name\n",
        "0        0    ann\n",
        "1        1    bob\n",
        "2        2   carl\n",
        "3        3  delia"
       ]
      }
     ],
     "prompt_number": 129
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# tests are put inot their own table so there is no duplication\n",
      "\n",
      "test_table = pd.DataFrame(df['tests'].unique(), columns=['test'])\n",
      "test_table['test_id'] = test_table.index\n",
      "columns = ['test_id', 'test']\n",
      "test_table[columns]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>test_id</th>\n",
        "      <th>test</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td>       wbc</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td>       rbc</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 2</td>\n",
        "      <td> platelets</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 130,
       "text": [
        "   test_id       test\n",
        "0        0        wbc\n",
        "1        1        rbc\n",
        "2        2  platelets"
       ]
      }
     ],
     "prompt_number": 130
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# the values1 and values2 correspond to visit 1 and 2, so\n",
      "# we create a visits table\n",
      "\n",
      "visit_table = pd.DataFrame([1,2], columns=['visit'])\n",
      "visit_table['visit_id'] = visit_table.index\n",
      "columns = ['visit_id', 'visit']\n",
      "visit_table[columns]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>visit_id</th>\n",
        "      <th>visit</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 1</td>\n",
        "      <td> 2</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 132,
       "text": [
        "   visit_id  visit\n",
        "0         0      1\n",
        "1         1      2"
       ]
      }
     ],
     "prompt_number": 132
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# finally, we link each value to a triple(name_id, test_id, visit_id)\n",
      "\n",
      "value_table = pd.DataFrame([\n",
      "    [0,0,0,10], [1,0,0,11.2], [0,1,0,300], [1,1,0,204], [2,0,0,9.8], [3,1,0,340], [0,2,0,125],\n",
      "   [0,0,1,10.6], [1,0,1,13.2], [0,1,1,322], [1,1,1,214], [2,0,1,10.3], [3,1,1,343], [0,2,1,145]\n",
      "], columns=['name_id', 'test_id', 'visit_id', 'value'])\n",
      "value_table"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>name_id</th>\n",
        "      <th>test_id</th>\n",
        "      <th>visit_id</th>\n",
        "      <th>value</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0 </th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td>  10.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1 </th>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td>  11.2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2 </th>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 300.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3 </th>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 204.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4 </th>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td>   9.8</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5 </th>\n",
        "      <td> 3</td>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 340.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>6 </th>\n",
        "      <td> 0</td>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td> 125.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>7 </th>\n",
        "      <td> 0</td>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "      <td>  10.6</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>8 </th>\n",
        "      <td> 1</td>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "      <td>  13.2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>9 </th>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> 322.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>10</th>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> 214.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>11</th>\n",
        "      <td> 2</td>\n",
        "      <td> 0</td>\n",
        "      <td> 1</td>\n",
        "      <td>  10.3</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>12</th>\n",
        "      <td> 3</td>\n",
        "      <td> 1</td>\n",
        "      <td> 1</td>\n",
        "      <td> 343.0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>13</th>\n",
        "      <td> 0</td>\n",
        "      <td> 2</td>\n",
        "      <td> 1</td>\n",
        "      <td> 145.0</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 97,
       "text": [
        "    name_id  test_id  visit_id  value\n",
        "0         0        0         0   10.0\n",
        "1         1        0         0   11.2\n",
        "2         0        1         0  300.0\n",
        "3         1        1         0  204.0\n",
        "4         2        0         0    9.8\n",
        "5         3        1         0  340.0\n",
        "6         0        2         0  125.0\n",
        "7         0        0         1   10.6\n",
        "8         1        0         1   13.2\n",
        "9         0        1         1  322.0\n",
        "10        1        1         1  214.0\n",
        "11        2        0         1   10.3\n",
        "12        3        1         1  343.0\n",
        "13        0        2         1  145.0"
       ]
      }
     ],
     "prompt_number": 97
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "At the end of the normalizaiton, we have gone from 1 dataframe with multiple redundancies to 4 tables with unique entries in each row. This organization helps maintain data integrity and is necesssary for effficeincy as the number of test values grows, possibly into millions of rows. As we have seen, we can use SQL queries to recreate the origianl dataformat if that is more convenient for analysis."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Using HDF5\n",
      "\n",
      "When your data consists of many numerical and matrices, each of which is relatively independent, relational databases offer little benefit, and it is more efficient to use HDF5 (Hierarchical Data Format) for storage.  For example, your data may come from a simulation which generates a 3D matrix and a list of count data at every iteration."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import h5py\n",
      "\n",
      "f = h5py.File('simulation.h5')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 44
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "for i in range(10): # iterations in simulation\n",
      "    xs = np.random.random((100,100,100))\n",
      "    ys = np.random.randint(0,100,(i+1)*10)\n",
      "    group = f.create_group('Iteration%03d' % i)\n",
      "    group.create_dataset('xs', data=xs)\n",
      "    group.create_dataset('ys', data=ys)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 45
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "f.keys()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 46,
       "text": [
        "[u'Iteration000',\n",
        " u'Iteration001',\n",
        " u'Iteration002',\n",
        " u'Iteration003',\n",
        " u'Iteration004',\n",
        " u'Iteration005',\n",
        " u'Iteration006',\n",
        " u'Iteration007',\n",
        " u'Iteration008',\n",
        " u'Iteration009']"
       ]
      }
     ],
     "prompt_number": 46
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "f['Iteration008'].keys()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 47,
       "text": [
        "[u'xs', u'ys']"
       ]
      }
     ],
     "prompt_number": 47
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "g8 = f['Iteration008']\n",
      "print g8['xs'][2:5,2:5,2:5]\n",
      "print g8['ys'][-10:]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[[[ 0.0367  0.2883  0.5562]\n",
        "  [ 0.9494  0.5614  0.1159]\n",
        "  [ 0.8887  0.7396  0.891 ]]\n",
        "\n",
        " [[ 0.7552  0.1539  0.216 ]\n",
        "  [ 0.6671  0.4682  0.9107]\n",
        "  [ 0.5565  0.5443  0.1665]]\n",
        "\n",
        " [[ 0.3972  0.1205  0.9487]\n",
        "  [ 0.7874  0.3466  0.2818]\n",
        "  [ 0.1248  0.0161  0.6898]]]\n",
        "[37 69  5 15 10 44 20 73 74 24]\n"
       ]
      }
     ],
     "prompt_number": 48
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "### Interfacing withPandas"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas as pd"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 26
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df = pd.read_sql('select * from transplant;', conn)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 27
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df.take(np.random.randint(0, len(df), 6))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>survival</th>\n",
        "      <th>censors</th>\n",
        "      <th>age</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>8 </th>\n",
        "      <td>  23</td>\n",
        "      <td> 1</td>\n",
        "      <td> 56.9</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>38</th>\n",
        "      <td> 815</td>\n",
        "      <td> 0</td>\n",
        "      <td> 32.7</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>12</th>\n",
        "      <td> 730</td>\n",
        "      <td> 1</td>\n",
        "      <td> 58.4</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>58</th>\n",
        "      <td> 339</td>\n",
        "      <td> 0</td>\n",
        "      <td> 54.4</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>53</th>\n",
        "      <td> 439</td>\n",
        "      <td> 0</td>\n",
        "      <td> 52.9</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>27</th>\n",
        "      <td> 994</td>\n",
        "      <td> 1</td>\n",
        "      <td> 48.6</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 28,
       "text": [
        "    survival  censors   age\n",
        "8         23        1  56.9\n",
        "38       815        0  32.7\n",
        "12       730        1  58.4\n",
        "58       339        0  54.4\n",
        "53       439        0  52.9\n",
        "27       994        1  48.6"
       ]
      }
     ],
     "prompt_number": 28
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1 = pd.read_sql('select t1.name, t2.value, t2.age from t1, t2 where t1.name = t2.name;', conn1)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 29
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "df1"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>Name</th>\n",
        "      <th>Value</th>\n",
        "      <th>Age</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> a</td>\n",
        "      <td> 5</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> c</td>\n",
        "      <td> 6</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> e</td>\n",
        "      <td> 9</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> a</td>\n",
        "      <td> 5</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> c</td>\n",
        "      <td> 6</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5</th>\n",
        "      <td> e</td>\n",
        "      <td> 9</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 30,
       "text": [
        "  Name  Value  Age\n",
        "0    a      5    0\n",
        "1    c      6   10\n",
        "2    e      9   20\n",
        "3    a      5    0\n",
        "4    c      6   10\n",
        "5    e      9   20"
       ]
      }
     ],
     "prompt_number": 30
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "c.close()\n",
      "c1.close()\n",
      "conn.close()\n",
      "conn1.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 31
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store = pd.HDFStore('dump.h5')\n",
      "store['transplant'] = df\n",
      "store['tables'] = df1\n",
      "store.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "/Users/cliburn/anaconda/lib/python2.7/site-packages/pandas/io/pytables.py:2453: PerformanceWarning: \n",
        "your performance may suffer as PyTables will pickle object types that it cannot\n",
        "map directly to c-types [inferred_type->unicode,key->block2_values] [items->['Name']]\n",
        "\n",
        "  warnings.warn(ws, PerformanceWarning)\n"
       ]
      }
     ],
     "prompt_number": 60
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "transplant_df = pd.read_hdf('dump.h5', 'transplant')\n",
      "transplant_df.take(np.random.randint(0, len(df), 6))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>survival</th>\n",
        "      <th>censors</th>\n",
        "      <th>age</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>50</th>\n",
        "      <td>  305</td>\n",
        "      <td> 0</td>\n",
        "      <td> 49.3</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3 </th>\n",
        "      <td>   46</td>\n",
        "      <td> 1</td>\n",
        "      <td> 42.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>0 </th>\n",
        "      <td>   15</td>\n",
        "      <td> 1</td>\n",
        "      <td> 54.3</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>22</th>\n",
        "      <td>    1</td>\n",
        "      <td> 1</td>\n",
        "      <td> 41.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>47</th>\n",
        "      <td>   63</td>\n",
        "      <td> 1</td>\n",
        "      <td> 56.4</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>19</th>\n",
        "      <td> 1549</td>\n",
        "      <td> 0</td>\n",
        "      <td> 40.6</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 62,
       "text": [
        "    survival  censors   age\n",
        "50       305        0  49.3\n",
        "3         46        1  42.5\n",
        "0         15        1  54.3\n",
        "22         1        1  41.5\n",
        "47        63        1  56.4\n",
        "19      1549        0  40.6"
       ]
      }
     ],
     "prompt_number": 62
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "table_df = pd.read_hdf('dump.h5', 'tables')\n",
      "table_df"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>Name</th>\n",
        "      <th>Value</th>\n",
        "      <th>Age</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> a</td>\n",
        "      <td> 5</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> c</td>\n",
        "      <td> 6</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> e</td>\n",
        "      <td> 9</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td> a</td>\n",
        "      <td> 5</td>\n",
        "      <td>  0</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td> c</td>\n",
        "      <td> 6</td>\n",
        "      <td> 10</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5</th>\n",
        "      <td> e</td>\n",
        "      <td> 9</td>\n",
        "      <td> 20</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 64,
       "text": [
        "  Name  Value  Age\n",
        "0    a      5    0\n",
        "1    c      6   10\n",
        "2    e      9   20\n",
        "3    a      5    0\n",
        "4    c      6   10\n",
        "5    e      9   20"
       ]
      }
     ],
     "prompt_number": 64
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 65,
       "text": [
        "<class 'pandas.io.pytables.HDFStore'>\n",
        "File path: dump.h5\n",
        "File is CLOSED"
       ]
      }
     ],
     "prompt_number": 65
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store = pd.HDFStore('dump.h5')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 66
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store.keys()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 67,
       "text": [
        "['/tables', '/transplant']"
       ]
      }
     ],
     "prompt_number": 67
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "store.close()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 68
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}