hdf5-pytables-pycon-slovakia.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting started with HDF5 and PyTables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*11/03/2018 - Giacomo Debidda @PyCon Slovakia*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Repo: https://github.com/jackdbd/hdf5-pycon-slovakia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import tables as tb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [],
   "source": [
    "np.set_printoptions(precision=2, suppress=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/jack/Repos/hdf5-pycon-slovakia/data\n"
     ]
    }
   ],
   "source": [
    "data_dir = os.path.join(os.getcwd(), 'data')\n",
    "print(data_dir)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### HDF5: a filesystem in a file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HDF5 is a data model and an open file format for storing and managing big and complex data.\n",
    "\n",
    "An HDF5 file can be thought of as a container (or group) that holds a variety of heterogeneous data objects (or datasets).\n",
    "\n",
    "HDF5 datasets can be almost anything: images, tables, graphs, or even documents, such as PDF or Excel.\n",
    "\n",
    "- Datasets (i.e. files in a filesystem)\n",
    "- Groups (i.e. directories in a filesystem)\n",
    "- Attributes (i.e. metadata of file/directory)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![HDF5 structure](img/hdf5_structure.jpg)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Working with groups and group members is similar to working with directories and files in UNIX.\n",
    "\n",
    "**/** root group (every HDF5 file has a root group)\n",
    "\n",
    "**/foo** member of the root group called foo\n",
    "\n",
    "**/foo/bar** member of the group foo called bar"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### HDF5: the C library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since HDF5 is an open file format, you could read the specifications (150 pages) and implement it in any language.\n",
    "\n",
    "As far as I know, there is only a C implementation developed by the HDF Group."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### HDF5 in the Python data stack"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![h5py - PyTables refactor](img/h5py-pytables-refactor.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![PyTables logo](img/pytables-logo.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Features"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Does not want to be a complete wrapper for the entire HDF5 C API\n",
    "- High level abstraction over HDF5 (it's more \"battery included\" than h5py)\n",
    "- Database-like approach to data storage\n",
    "- Does not depend on h5py (at the moment)\n",
    "- Natural naming\n",
    "- Fast searches (indexing)\n",
    "- Built-in compression\n",
    "- Undo/redo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Groups, Attributes, ~~Datasets~~"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's forget HDF5 Datasets for a moment...\n",
    "\n",
    "The simplest PyTables datatype that you can use to store some data is the `Array`.\n",
    "\n",
    "- how to create groups, attributes and arrays\n",
    "- how to access nodes in a HDF5 file\n",
    "- how to access data in a node"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Natural naming"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PyTables nodes (i.e. any element in the hierarchy of the HDF5 file) can be accessed with the **dot notation** if they follow a **natural naming** schema."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    f.create_group(where='/', name='my_group')    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jack/.virtualenvs/hdf5-pycon-slovakia/lib/python3.6/site-packages/tables/path.py:112: NaturalNameWarning: object name is not a valid Python identifier: 'my group'; it does not match the pattern ``^[a-zA-Z_][a-zA-Z0-9_]*$``; you will not be able to use natural naming to access this object; using ``getattr()`` will still work, though\n",
      "  NaturalNameWarning)\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    f.create_group(where='/', name='my group')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When a node which does not follow the natural naming schema you can still use `get_node` to access it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/my group (Group) ''\n",
      "/my group (Group) ''\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='r') as f:\n",
    "    my_group = f.get_node(where='/my group')\n",
    "    print(my_group)\n",
    "    my_group = f.get_node(where='/', name='my group')\n",
    "    print(my_group)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's create some data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([  0,  11,  22,  33,  44,  55,  66,  77,  88, 100], dtype=int8)"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arr1d = np.linspace(start=0, stop=100, num=10, dtype=np.int8)\n",
    "arr1d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 0.,  1.,  2.],\n",
       "       [ 3.,  4.,  5.],\n",
       "       [ 6.,  7.,  8.],\n",
       "       [ 9., 10., 11.],\n",
       "       [12., 13., 14.],\n",
       "       [15., 16., 17.],\n",
       "       [18., 19., 20.],\n",
       "       [21., 22., 23.],\n",
       "       [24., 25., 26.],\n",
       "       [27., 28., 29.]], dtype=float32)"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arr2d = np.arange(30, dtype=np.float32).reshape(10, 3)\n",
    "arr2d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[[0.33, 0.21, 0.67, 0.4 , 0.93],\n",
       "        [0.31, 0.24, 0.92, 0.86, 0.8 ],\n",
       "        [0.4 , 0.74, 0.11, 0.76, 0.95]],\n",
       "\n",
       "       [[0.28, 0.35, 0.1 , 0.77, 0.3 ],\n",
       "        [0.67, 0.85, 0.98, 0.72, 0.69],\n",
       "        [0.2 , 0.21, 0.98, 0.4 , 0.15]],\n",
       "\n",
       "       [[0.02, 0.35, 0.42, 0.61, 1.  ],\n",
       "        [0.03, 0.01, 0.93, 0.26, 0.94],\n",
       "        [0.6 , 0.74, 0.38, 0.13, 0.44]],\n",
       "\n",
       "       [[0.27, 0.85, 1.  , 0.28, 0.06],\n",
       "        [0.13, 0.23, 0.32, 0.81, 0.13],\n",
       "        [0.64, 0.35, 0.22, 0.88, 0.81]],\n",
       "\n",
       "       [[0.32, 0.53, 0.38, 0.63, 0.14],\n",
       "        [0.61, 0.56, 0.45, 0.01, 0.53],\n",
       "        [0.41, 0.1 , 0.36, 0.37, 0.89]],\n",
       "\n",
       "       [[0.07, 0.63, 0.59, 0.89, 0.22],\n",
       "        [0.11, 0.79, 0.43, 0.74, 0.06],\n",
       "        [0.05, 0.56, 0.55, 0.07, 0.83]],\n",
       "\n",
       "       [[0.89, 0.5 , 0.02, 0.69, 0.21],\n",
       "        [0.66, 0.74, 0.06, 0.34, 0.62],\n",
       "        [0.95, 0.84, 0.22, 0.87, 0.22]],\n",
       "\n",
       "       [[0.69, 0.48, 0.71, 0.02, 0.11],\n",
       "        [0.36, 0.79, 0.81, 0.55, 0.3 ],\n",
       "        [0.6 , 0.59, 0.55, 0.  , 0.05]],\n",
       "\n",
       "       [[0.2 , 0.63, 0.54, 0.8 , 0.4 ],\n",
       "        [0.48, 0.16, 0.07, 0.68, 0.96],\n",
       "        [0.03, 0.47, 1.  , 0.74, 0.6 ]],\n",
       "\n",
       "       [[0.92, 0.1 , 0.12, 0.21, 0.66],\n",
       "        [0.27, 0.47, 0.18, 0.02, 0.47],\n",
       "        [0.4 , 0.67, 0.09, 0.05, 0.85]]], dtype=float32)"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arr3d = np.random.random((10, 3, 5)).astype('float32')\n",
    "arr3d"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create groups, attributes, arrays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    f.create_array(where='/', \n",
    "                   name='array_1d',\n",
    "                   title='A one-dimensional Array',\n",
    "                   obj=arr1d)\n",
    "    f.set_node_attr(where='/array_1d', attrname='SomeAttribute', attrvalue='SomeValue')\n",
    "    \n",
    "    f.create_group(where='/', name='multidimensional_data', title='Multi dimensional data')\n",
    "    f.set_node_attr(where='/multidimensional_data', attrname='SomeOtherAttribute', attrvalue=123)\n",
    "    \n",
    "    f.create_array(where='/multidimensional_data', \n",
    "                   name='array_2d',\n",
    "                   title='A two-dimensional Array',\n",
    "                   obj=arr2d)\n",
    "    \n",
    "    f.create_array(where=f.root.multidimensional_data, \n",
    "                   name='array_3d',\n",
    "                   title='A three-dimensional Array',\n",
    "                   obj=arr3d)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Traverse a HDF5 file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/ (RootGroup) ''\n",
      "/array_1d (Array(10,)) 'A one-dimensional Array'\n",
      "/multidimensional_data (Group) 'Multi dimensional data'\n",
      "/multidimensional_data/array_2d (Array(10, 3)) 'A two-dimensional Array'\n",
      "/multidimensional_data/array_3d (Array(10, 3, 5)) 'A three-dimensional Array'\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file('data/my_pytables_file.h5', 'r') as f:\n",
    "    for node in f:\n",
    "        print(node)\n",
    "        \n",
    "#     for node in f.walk_groups():\n",
    "#         print(node)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select a hyperslab of data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Hyperslabs are portions of datasets.\n",
    "\n",
    "A hyperslab selection can be a logically contiguous collection of points in a dataspace, or it can be regular pattern of points or blocks in a dataspace."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "array([[0.03, 0.01, 0.93, 0.26, 0.94],\n",
      "       [0.13, 0.23, 0.32, 0.81, 0.13],\n",
      "       [0.61, 0.56, 0.45, 0.01, 0.53],\n",
      "       [0.11, 0.79, 0.43, 0.74, 0.06],\n",
      "       [0.66, 0.74, 0.06, 0.34, 0.62]], dtype=float32)\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='r') as f:\n",
    "    print(repr(f.root.multidimensional_data.array_3d[2:7, 1, :]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### HDF5 CLI utils"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Here](https://support.hdfgroup.org/products/hdf5_tools/#h5dist) you can find the command line tools developed by the HDF Group. You don't need h5py or PyTables to use them.\n",
    "\n",
    "If you are on Ubuntu, you can install them with `sudo apt install hdf5-tools`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/                        Group\r\n",
      "/array_1d                Dataset {10}\r\n",
      "/multidimensional_data   Group\r\n",
      "/multidimensional_data/array_2d Dataset {10, 3}\r\n",
      "/multidimensional_data/array_3d Dataset {10, 3, 5}\r\n"
     ]
    }
   ],
   "source": [
    "# -r stands for 'recursive'\n",
    "!h5ls -r 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "HDF5 \"data/my_pytables_file.h5\" {\r\n",
      "GROUP \"/\" {\r\n",
      "   ATTRIBUTE \"CLASS\" {\r\n",
      "      DATATYPE  H5T_STRING {\r\n",
      "         STRSIZE 5;\r\n",
      "         STRPAD H5T_STR_NULLTERM;\r\n",
      "         CSET H5T_CSET_UTF8;\r\n",
      "         CTYPE H5T_C_S1;\r\n",
      "      }\r\n",
      "      DATASPACE  SCALAR\r\n",
      "      DATA {\r\n",
      "      (0): \"GROUP\"\r\n",
      "      }\r\n",
      "   }\r\n",
      "   ATTRIBUTE \"PYTABLES_FORMAT_VERSION\" {\r\n",
      "      DATATYPE  H5T_STRING {\r\n",
      "         STRSIZE 3;\r\n",
      "         STRPAD H5T_STR_NULLTERM;\r\n",
      "         CSET H5T_CSET_UTF8;\r\n",
      "         CTYPE H5T_C_S1;\r\n",
      "      }\r\n",
      "      DATASPACE  SCALAR\r\n",
      "      DATA {\r\n",
      "      (0): \"2.1\"\r\n",
      "      }\r\n",
      "   }\r\n",
      "   ATTRIBUTE \"TITLE\" {\r\n",
      "      DATATYPE  H5T_STRING {\r\n",
      "         STRSIZE 1;\r\n",
      "         STRPAD H5T_STR_NULLTERM;\r\n",
      "         CSET H5T_CSET_UTF8;\r\n",
      "         CTYPE H5T_C_S1;\r\n",
      "      }\r\n",
      "      DATASPACE  NULL\r\n",
      "      DATA {\r\n",
      "      }\r\n",
      "   }\r\n",
      "   ATTRIBUTE \"VERSION\" {\r\n",
      "      DATATYPE  H5T_STRING {\r\n",
      "         STRSIZE 3;\r\n",
      "         STRPAD H5T_STR_NULLTERM;\r\n",
      "         CSET H5T_CSET_UTF8;\r\n",
      "         CTYPE H5T_C_S1;\r\n",
      "      }\r\n",
      "      DATASPACE  SCALAR\r\n",
      "      DATA {\r\n",
      "      (0): \"1.0\"\r\n",
      "      }\r\n",
      "   }\r\n",
      "   DATASET \"array_1d\" {\r\n",
      "      DATATYPE  H5T_STD_I8LE\r\n",
      "      DATASPACE  SIMPLE { ( 10 ) / ( 10 ) }\r\n",
      "      DATA {\r\n",
      "      (0): 0, 11, 22, 33, 44, 55, 66, 77, 88, 100\r\n",
      "      }\r\n",
      "      ATTRIBUTE \"CLASS\" {\r\n",
      "         DATATYPE  H5T_STRING {\r\n",
      "            STRSIZE 5;\r\n",
      "            STRPAD H5T_STR_NULLTERM;\r\n",
      "            CSET H5T_CSET_UTF8;\r\n",
      "            CTYPE H5T_C_S1;\r\n",
      "         }\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): \"ARRAY\"\r\n",
      "         }\r\n",
      "      }\r\n",
      "      ATTRIBUTE \"FLAVOR\" {\r\n",
      "         DATATYPE  H5T_STRING {\r\n",
      "            STRSIZE 5;\r\n",
      "            STRPAD H5T_STR_NULLTERM;\r\n",
      "            CSET H5T_CSET_UTF8;\r\n",
      "            CTYPE H5T_C_S1;\r\n",
      "         }\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): \"numpy\"\r\n",
      "         }\r\n",
      "      }\r\n",
      "      ATTRIBUTE \"SomeAttribute\" {\r\n",
      "         DATATYPE  H5T_STRING {\r\n",
      "            STRSIZE 9;\r\n",
      "            STRPAD H5T_STR_NULLTERM;\r\n",
      "            CSET H5T_CSET_UTF8;\r\n",
      "            CTYPE H5T_C_S1;\r\n",
      "         }\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): \"SomeValue\"\r\n",
      "         }\r\n",
      "      }\r\n",
      "      ATTRIBUTE \"TITLE\" {\r\n",
      "         DATATYPE  H5T_STRING {\r\n",
      "            STRSIZE 23;\r\n",
      "            STRPAD H5T_STR_NULLTERM;\r\n",
      "            CSET H5T_CSET_UTF8;\r\n",
      "            CTYPE H5T_C_S1;\r\n",
      "         }\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): \"A one-dimensional Array\"\r\n",
      "         }\r\n",
      "      }\r\n",
      "      ATTRIBUTE \"VERSION\" {\r\n",
      "         DATATYPE  H5T_STRING {\r\n",
      "            STRSIZE 3;\r\n",
      "            STRPAD H5T_STR_NULLTERM;\r\n",
      "            CSET H5T_CSET_UTF8;\r\n",
      "            CTYPE H5T_C_S1;\r\n",
      "         }\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): \"2.4\"\r\n",
      "         }\r\n",
      "      }\r\n",
      "   }\r\n",
      "   GROUP \"multidimensional_data\" {\r\n",
      "      ATTRIBUTE \"CLASS\" {\r\n",
      "         DATATYPE  H5T_STRING {\r\n",
      "            STRSIZE 5;\r\n",
      "            STRPAD H5T_STR_NULLTERM;\r\n",
      "            CSET H5T_CSET_UTF8;\r\n",
      "            CTYPE H5T_C_S1;\r\n",
      "         }\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): \"GROUP\"\r\n",
      "         }\r\n",
      "      }\r\n",
      "      ATTRIBUTE \"SomeOtherAttribute\" {\r\n",
      "         DATATYPE  H5T_STD_I64LE\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): 123\r\n",
      "         }\r\n",
      "      }\r\n",
      "      ATTRIBUTE \"TITLE\" {\r\n",
      "         DATATYPE  H5T_STRING {\r\n",
      "            STRSIZE 22;\r\n",
      "            STRPAD H5T_STR_NULLTERM;\r\n",
      "            CSET H5T_CSET_UTF8;\r\n",
      "            CTYPE H5T_C_S1;\r\n",
      "         }\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): \"Multi dimensional data\"\r\n",
      "         }\r\n",
      "      }\r\n",
      "      ATTRIBUTE \"VERSION\" {\r\n",
      "         DATATYPE  H5T_STRING {\r\n",
      "            STRSIZE 3;\r\n",
      "            STRPAD H5T_STR_NULLTERM;\r\n",
      "            CSET H5T_CSET_UTF8;\r\n",
      "            CTYPE H5T_C_S1;\r\n",
      "         }\r\n",
      "         DATASPACE  SCALAR\r\n",
      "         DATA {\r\n",
      "         (0): \"1.0\"\r\n",
      "         }\r\n",
      "      }\r\n",
      "      DATASET \"array_2d\" {\r\n",
      "         DATATYPE  H5T_IEEE_F32LE\r\n",
      "         DATASPACE  SIMPLE { ( 10, 3 ) / ( 10, 3 ) }\r\n",
      "         DATA {\r\n",
      "         (0,0): 0, 1, 2,\r\n",
      "         (1,0): 3, 4, 5,\r\n",
      "         (2,0): 6, 7, 8,\r\n",
      "         (3,0): 9, 10, 11,\r\n",
      "         (4,0): 12, 13, 14,\r\n",
      "         (5,0): 15, 16, 17,\r\n",
      "         (6,0): 18, 19, 20,\r\n",
      "         (7,0): 21, 22, 23,\r\n",
      "         (8,0): 24, 25, 26,\r\n",
      "         (9,0): 27, 28, 29\r\n",
      "         }\r\n",
      "         ATTRIBUTE \"CLASS\" {\r\n",
      "            DATATYPE  H5T_STRING {\r\n",
      "               STRSIZE 5;\r\n",
      "               STRPAD H5T_STR_NULLTERM;\r\n",
      "               CSET H5T_CSET_UTF8;\r\n",
      "               CTYPE H5T_C_S1;\r\n",
      "            }\r\n",
      "            DATASPACE  SCALAR\r\n",
      "            DATA {\r\n",
      "            (0): \"ARRAY\"\r\n",
      "            }\r\n",
      "         }\r\n",
      "         ATTRIBUTE \"FLAVOR\" {\r\n",
      "            DATATYPE  H5T_STRING {\r\n",
      "               STRSIZE 5;\r\n",
      "               STRPAD H5T_STR_NULLTERM;\r\n",
      "               CSET H5T_CSET_UTF8;\r\n",
      "               CTYPE H5T_C_S1;\r\n",
      "            }\r\n",
      "            DATASPACE  SCALAR\r\n",
      "            DATA {\r\n",
      "            (0): \"numpy\"\r\n",
      "            }\r\n",
      "         }\r\n",
      "         ATTRIBUTE \"TITLE\" {\r\n",
      "            DATATYPE  H5T_STRING {\r\n",
      "               STRSIZE 23;\r\n",
      "               STRPAD H5T_STR_NULLTERM;\r\n",
      "               CSET H5T_CSET_UTF8;\r\n",
      "               CTYPE H5T_C_S1;\r\n",
      "            }\r\n",
      "            DATASPACE  SCALAR\r\n",
      "            DATA {\r\n",
      "            (0): \"A two-dimensional Array\"\r\n",
      "            }\r\n",
      "         }\r\n",
      "         ATTRIBUTE \"VERSION\" {\r\n",
      "            DATATYPE  H5T_STRING {\r\n",
      "               STRSIZE 3;\r\n",
      "               STRPAD H5T_STR_NULLTERM;\r\n",
      "               CSET H5T_CSET_UTF8;\r\n",
      "               CTYPE H5T_C_S1;\r\n",
      "            }\r\n",
      "            DATASPACE  SCALAR\r\n",
      "            DATA {\r\n",
      "            (0): \"2.4\"\r\n",
      "            }\r\n",
      "         }\r\n",
      "      }\r\n",
      "      DATASET \"array_3d\" {\r\n",
      "         DATATYPE  H5T_IEEE_F32LE\r\n",
      "         DATASPACE  SIMPLE { ( 10, 3, 5 ) / ( 10, 3, 5 ) }\r\n",
      "         DATA {\r\n",
      "         (0,0,0): 0.330382, 0.211382, 0.674004, 0.401573, 0.926867,\r\n",
      "         (0,1,0): 0.312208, 0.235216, 0.921059, 0.861037, 0.802998,\r\n",
      "         (0,2,0): 0.398372, 0.737166, 0.110497, 0.756429, 0.947827,\r\n",
      "         (1,0,0): 0.280923, 0.347971, 0.0991416, 0.76773, 0.304157,\r\n",
      "         (1,1,0): 0.666646, 0.849077, 0.984168, 0.722149, 0.694381,\r\n",
      "         (1,2,0): 0.199767, 0.207886, 0.976607, 0.403246, 0.148616,\r\n",
      "         (2,0,0): 0.0207459, 0.350185, 0.423948, 0.610295, 0.996087,\r\n",
      "         (2,1,0): 0.0258948, 0.0099808, 0.932964, 0.260481, 0.937791,\r\n",
      "         (2,2,0): 0.595337, 0.744023, 0.382078, 0.126179, 0.442605,\r\n",
      "         (3,0,0): 0.267967, 0.853036, 0.999187, 0.280909, 0.0628747,\r\n",
      "         (3,1,0): 0.128449, 0.225405, 0.32431, 0.81417, 0.127319,\r\n",
      "         (3,2,0): 0.637449, 0.350392, 0.219409, 0.884071, 0.808244,\r\n",
      "         (4,0,0): 0.323861, 0.529327, 0.378161, 0.634582, 0.139873,\r\n",
      "         (4,1,0): 0.614895, 0.562731, 0.453598, 0.0116401, 0.534773,\r\n",
      "         (4,2,0): 0.412879, 0.0981646, 0.362896, 0.373975, 0.886911,\r\n",
      "         (5,0,0): 0.0672617, 0.630071, 0.593364, 0.89216, 0.219147,\r\n",
      "         (5,1,0): 0.10746, 0.786636, 0.4325, 0.742289, 0.0568314,\r\n",
      "         (5,2,0): 0.0521352, 0.561437, 0.546454, 0.0675838, 0.829415,\r\n",
      "         (6,0,0): 0.889657, 0.500545, 0.0249795, 0.689666, 0.211257,\r\n",
      "         (6,1,0): 0.655427, 0.739981, 0.0592963, 0.336242, 0.619688,\r\n",
      "         (6,2,0): 0.946408, 0.839812, 0.22084, 0.872006, 0.222713,\r\n",
      "         (7,0,0): 0.691615, 0.483764, 0.706199, 0.0245262, 0.106516,\r\n",
      "         (7,1,0): 0.364657, 0.787485, 0.810447, 0.549754, 0.303941,\r\n",
      "         (7,2,0): 0.599431, 0.591272, 0.547184, 0.00432997, 0.0485105,\r\n",
      "         (8,0,0): 0.20292, 0.626001, 0.538863, 0.80267, 0.396131,\r\n",
      "         (8,1,0): 0.484153, 0.158277, 0.0683405, 0.681269, 0.960366,\r\n",
      "         (8,2,0): 0.0328671, 0.46648, 0.999792, 0.741555, 0.598544,\r\n",
      "         (9,0,0): 0.924052, 0.0989748, 0.124454, 0.209519, 0.657576,\r\n",
      "         (9,1,0): 0.26586, 0.467258, 0.178674, 0.0219479, 0.472034,\r\n",
      "         (9,2,0): 0.397102, 0.670981, 0.0865746, 0.0518581, 0.850333\r\n",
      "         }\r\n",
      "         ATTRIBUTE \"CLASS\" {\r\n",
      "            DATATYPE  H5T_STRING {\r\n",
      "               STRSIZE 5;\r\n",
      "               STRPAD H5T_STR_NULLTERM;\r\n",
      "               CSET H5T_CSET_UTF8;\r\n",
      "               CTYPE H5T_C_S1;\r\n",
      "            }\r\n",
      "            DATASPACE  SCALAR\r\n",
      "            DATA {\r\n",
      "            (0): \"ARRAY\"\r\n",
      "            }\r\n",
      "         }\r\n",
      "         ATTRIBUTE \"FLAVOR\" {\r\n",
      "            DATATYPE  H5T_STRING {\r\n",
      "               STRSIZE 5;\r\n",
      "               STRPAD H5T_STR_NULLTERM;\r\n",
      "               CSET H5T_CSET_UTF8;\r\n",
      "               CTYPE H5T_C_S1;\r\n",
      "            }\r\n",
      "            DATASPACE  SCALAR\r\n",
      "            DATA {\r\n",
      "            (0): \"numpy\"\r\n",
      "            }\r\n",
      "         }\r\n",
      "         ATTRIBUTE \"TITLE\" {\r\n",
      "            DATATYPE  H5T_STRING {\r\n",
      "               STRSIZE 25;\r\n",
      "               STRPAD H5T_STR_NULLTERM;\r\n",
      "               CSET H5T_CSET_UTF8;\r\n",
      "               CTYPE H5T_C_S1;\r\n",
      "            }\r\n",
      "            DATASPACE  SCALAR\r\n",
      "            DATA {\r\n",
      "            (0): \"A three-dimensional Array\"\r\n",
      "            }\r\n",
      "         }\r\n",
      "         ATTRIBUTE \"VERSION\" {\r\n",
      "            DATATYPE  H5T_STRING {\r\n",
      "               STRSIZE 3;\r\n",
      "               STRPAD H5T_STR_NULLTERM;\r\n",
      "               CSET H5T_CSET_UTF8;\r\n",
      "               CTYPE H5T_C_S1;\r\n",
      "            }\r\n",
      "            DATASPACE  SCALAR\r\n",
      "            DATA {\r\n",
      "            (0): \"2.4\"\r\n",
      "            }\r\n",
      "         }\r\n",
      "      }\r\n",
      "   }\r\n",
      "}\r\n",
      "}\r\n"
     ]
    }
   ],
   "source": [
    "!h5dump 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PyTables CLI utils"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some very useful tools are shipped with PyTables. These are just python scripts that can be used from the command line."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/ (RootGroup) ''\r\n",
      "  /._v_attrs (AttributeSet), 4 attributes:\r\n",
      "   [CLASS := 'GROUP',\r\n",
      "    PYTABLES_FORMAT_VERSION := '2.1',\r\n",
      "    TITLE := '',\r\n",
      "    VERSION := '1.0']\r\n",
      "/array_1d (Array(10,)) 'A one-dimensional Array'\r\n",
      "  atom := Int8Atom(shape=(), dflt=0)\r\n",
      "  maindim := 0\r\n",
      "  flavor := 'numpy'\r\n",
      "  byteorder := 'irrelevant'\r\n",
      "  chunkshape := None\r\n",
      "  /array_1d._v_attrs (AttributeSet), 5 attributes:\r\n",
      "   [CLASS := 'ARRAY',\r\n",
      "    FLAVOR := 'numpy',\r\n",
      "    SomeAttribute := 'SomeValue',\r\n",
      "    TITLE := 'A one-dimensional Array',\r\n",
      "    VERSION := '2.4']\r\n",
      "/multidimensional_data (Group) 'Multi dimensional data'\r\n",
      "  /multidimensional_data._v_attrs (AttributeSet), 4 attributes:\r\n",
      "   [CLASS := 'GROUP',\r\n",
      "    SomeOtherAttribute := 123,\r\n",
      "    TITLE := 'Multi dimensional data',\r\n",
      "    VERSION := '1.0']\r\n",
      "/multidimensional_data/array_2d (Array(10, 3)) 'A two-dimensional Array'\r\n",
      "  atom := Float32Atom(shape=(), dflt=0.0)\r\n",
      "  maindim := 0\r\n",
      "  flavor := 'numpy'\r\n",
      "  byteorder := 'little'\r\n",
      "  chunkshape := None\r\n",
      "  /multidimensional_data/array_2d._v_attrs (AttributeSet), 4 attributes:\r\n",
      "   [CLASS := 'ARRAY',\r\n",
      "    FLAVOR := 'numpy',\r\n",
      "    TITLE := 'A two-dimensional Array',\r\n",
      "    VERSION := '2.4']\r\n",
      "/multidimensional_data/array_3d (Array(10, 3, 5)) 'A three-dimensional Array'\r\n",
      "  atom := Float32Atom(shape=(), dflt=0.0)\r\n",
      "  maindim := 0\r\n",
      "  flavor := 'numpy'\r\n",
      "  byteorder := 'little'\r\n",
      "  chunkshape := None\r\n",
      "  /multidimensional_data/array_3d._v_attrs (AttributeSet), 4 attributes:\r\n",
      "   [CLASS := 'ARRAY',\r\n",
      "    FLAVOR := 'numpy',\r\n",
      "    TITLE := 'A three-dimensional Array',\r\n",
      "    VERSION := '2.4']\r\n"
     ]
    }
   ],
   "source": [
    "# -a show attributes, -v verbose\n",
    "!ptdump -av 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r\n",
      "------------------------------------------------------------\r\n",
      "\r\n",
      "/ (RootGroup)\r\n",
      "+--multidimensional_data (Group)\r\n",
      "|  +--array_3d (Array)\r\n",
      "|  |     mem=600.0B, disk=600.0B [82.2%]\r\n",
      "|  `--array_2d (Array)\r\n",
      "|        mem=120.0B, disk=120.0B [16.4%]\r\n",
      "`--array_1d (Array)\r\n",
      "      mem=10.0B, disk=10.0B [ 1.4%]\r\n",
      "\r\n",
      "------------------------------------------------------------\r\n",
      "Total branch leaves:    3\r\n",
      "Total branch size:      730.0B in memory, 730.0B on disk\r\n",
      "Mean compression ratio: 1.00\r\n",
      "HDF5 file size:         6.0kB\r\n",
      "------------------------------------------------------------\r\n",
      "\r\n"
     ]
    }
   ],
   "source": [
    "!pttree -L 2 --use-si-units --sort-by 'size' 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### HDF5 Viewers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [HDFView](https://support.hdfgroup.org/products/java/hdfview/): I like it!\n",
    "- [HDF Compass](https://support.hdfgroup.org/projects/compass/): seems to be the go-to choice for Mac users\n",
    "- [ViTables](http://vitables.org/): a bit weird... indexing starts at 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remember when I said \"let's forget HDF5 datasets for a moment\"?\n",
    "\n",
    "Here is why..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### PyTables provides high-level abstractions over the HDF5 Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Homogenous dataset:\n",
    "\n",
    "- **Array**\n",
    "- **CArray**\n",
    "- **EArray**\n",
    "- **VLArray**\n",
    "\n",
    "Heterogenous dataset:\n",
    "\n",
    "- **Table**\n",
    "\n",
    "*Note*: some HDF5 libraries/tools could create a HDF5 dataset currently not supported by PyTables. In this case the dataset will be mapped into an [UnImplemented](http://www.pytables.org/usersguide/libref/helper_classes.html#tables.UnImplemented) class instance."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Wait! What is a *Homogeneous* dataset?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A homogeneous dataset is a dataset where all its elements have the same [Atom](http://www.pytables.org/usersguide/libref/declarative_classes.html#atomclassdescr).\n",
    "\n",
    "An Atom represents the **type and shape** of the atomic objects to be saved.\n",
    "\n",
    "It's the most basic, indivisible element that can be stored in a given dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UInt8Atom(shape=(2,), dflt=0)"
      ]
     },
     "execution_count": 221,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atom = tb.UInt8Atom(shape=(2,))\n",
    "atom"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Array"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Docs](http://www.pytables.org/usersguide/libref/homogenous_storage.html#the-array-class)\n",
    "\n",
    "An Array contains homogeneous data. Every atomic object (i.e. every single element) has the same type and shape.\n",
    "\n",
    "- Fastest I/O speed\n",
    "- Must fit in memory\n",
    "- Not compressible\n",
    "- Not enlargeable (i.e. no append)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    f.create_array(where='/', \n",
    "                   name='array_1d',\n",
    "                   title='A one-dimensional Array',\n",
    "                   obj=[0, 1, 2, 3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CArray"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Docs](http://www.pytables.org/usersguide/libref/homogenous_storage.html#carrayclassdescr)\n",
    "\n",
    "- Chunked layout, compressible\n",
    "- Not enlargeable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {},
   "outputs": [],
   "source": [
    "filters = tb.Filters(complevel=5, complib='zlib')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Tips on how to use compression (from the PyTables docs)\n",
    "\n",
    "- A mid-level (5) compression is sufficient. No need to go all the way up (9)\n",
    "- Use zlib if you must guarantee complete portability\n",
    "- Use blosc all other times (it is optimized for HDF5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    f.create_carray(\n",
    "        where='/',\n",
    "        name='my_carray',\n",
    "        title='My PyTables CArray',\n",
    "        obj=arr2d,\n",
    "        filters=filters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### EArray"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Docs](http://www.pytables.org/usersguide/libref/homogenous_storage.html#earrayclassdescr)\n",
    "\n",
    "- Enlargeable on **one** dimension (append)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 225,
   "metadata": {},
   "outputs": [],
   "source": [
    "# One (and only one) of the shape dimensions *must* be 0.\n",
    "# The dimension being 0 means that the resulting EArray object can be extended along it.\n",
    "# Multiple enlargeable dimensions are not supported (at the moment).\n",
    "num_columns = 5\n",
    "shape = (0, num_columns)\n",
    "\n",
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    # you can create an EArray and fill it later, but you need to specify atom and shape\n",
    "    f.create_earray(\n",
    "        where='/',\n",
    "        name='my_earray',\n",
    "        title='My PyTables EArray',\n",
    "        atom=tb.Float32Atom(),\n",
    "        shape=shape,\n",
    "        filters=filters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 226,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_rows = 1000000  # 1 million\n",
    "matrix = np.random.random((num_rows, num_columns)).astype('float32')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Append some data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 227,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    earray = f.root.my_earray\n",
    "    earray.append(sequence=matrix[0:1000, :])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Append some more data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 228,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    earray = f.root.my_earray\n",
    "    earray.append(sequence=matrix[1001:5000, :])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### VLArray"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Docs](http://www.pytables.org/usersguide/libref/homogenous_storage.html#the-vlarray-class)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Each row has a variable number of homogeneous elements (atoms)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "UInt8Atom(shape=(2,), dflt=0)"
      ]
     },
     "execution_count": 229,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "atom = tb.UInt8Atom(shape=(2,))\n",
    "atom"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    vlarray = f.create_vlarray(where='/',\n",
    "                               name='my_vlarray',\n",
    "                               atom=atom,\n",
    "                               title='Variable length array of vectors')\n",
    "    vlarray.append([[0, 1]])\n",
    "    vlarray.append([[2, 3], [4, 5], [6, 7]])\n",
    "    vlarray.append([[8, 9]])\n",
    "    vlarray.append([[1, 1], [2, 2]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[array([[0, 1]], dtype=uint8), array([[2, 3],\n",
      "       [4, 5],\n",
      "       [6, 7]], dtype=uint8), array([[8, 9]], dtype=uint8), array([[1, 1],\n",
      "       [2, 2]], dtype=uint8)]\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='r') as f:\n",
    "    print(f.root.my_vlarray[:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[Docs](http://www.pytables.org/usersguide/libref/structured_storage.html?highlight=table#tableclassdescr)\n",
    "\n",
    "- Data can be heterogeneous (i.e. different shapes and different dtypes)\n",
    "- The structure of a table is declared by its [description](http://www.pytables.org/usersguide/libref/declarative_classes.html#tables.IsDescription)\n",
    "- multi-column searches"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to emulate in Python records mapped to HDF5 C structs PyTables implements a special class so as to easily define all its fields and other properties. It's called `IsDescription`.\n",
    "\n",
    "A *description* defines the table structure (basically, the *schema* of your table)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Example: NYC yellow taxi dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data source: [Taxi & Limousine Commission Trip Record Data](http://www.nyc.gov/html/tlc/html/about/trip_record_data.shtml)\n",
    "\n",
    "Download the CSV for **yellow** taxi rides from **December 2017**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"LocationID\",\"Borough\",\"Zone\",\"service_zone\"\n",
      "1,\"EWR\",\"Newark Airport\",\"EWR\"\n",
      "2,\"Queens\",\"Jamaica Bay\",\"Boro Zone\"\n",
      "3,\"Bronx\",\"Allerton/Pelham Gardens\",\"Boro Zone\"\n",
      "4,\"Manhattan\",\"Alphabet City\",\"Yellow Zone\"\n",
      "5,\"Staten Island\",\"Arden Heights\",\"Boro Zone\"\n",
      "6,\"Staten Island\",\"Arrochar/Fort Wadsworth\",\"Boro Zone\"\n",
      "7,\"Queens\",\"Astoria\",\"Boro Zone\"\n",
      "8,\"Queens\",\"Astoria Park\",\"Boro Zone\"\n",
      "9,\"Queens\",\"Auburndale\",\"Boro Zone\"\n",
      "10,\"Queens\",\"Baisley Park\",\"Boro Zone\"\n",
      "11,\"Brooklyn\",\"Bath Beach\",\"Boro Zone\"\n",
      "12,\"Manhattan\",\"Battery Park\",\"Yellow Zone\"\n",
      "13,\"Manhattan\",\"Battery Park City\",\"Yellow Zone\"\n",
      "14,\"Brooklyn\",\"Bay Ridge\",\"Boro Zone\"\n",
      "15,\"Queens\",\"Bay Terrace/Fort Totten\",\"Boro Zone\"\n",
      "16,\"Queens\",\"Bayside\",\"Boro Zone\"\n",
      "17,\"Brooklyn\",\"Bedford\",\"Boro Zone\"\n",
      "18,\"Bronx\",\"Bedford Park\",\"Boro Zone\"\n",
      "19,\"Queens\",\"Bellerose\",\"Boro Zone\"\n",
      "20,\"Bronx\",\"Belmont\",\"Boro Zone\"\n",
      "21,\"Brooklyn\",\"Bensonhurst East\",\"Boro Zone\"\n",
      "22,\"Brooklyn\",\"Bensonhurst West\",\"Boro Zone\"\n",
      "\u001b[K:\u001b[Ka/taxi+_zone_lookup.csv\u001b[m\u001b[K\u0007"
     ]
    }
   ],
   "source": [
    "!less 'data/taxi+_zone_lookup.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "VendorID,tpep_pickup_datetime,tpep_dropoff_datetime,passenger_count,trip_distance,RatecodeID,store_and_fwd_flag,PULocationID,DOLocationID,payment_type,fare_amount,extra,mta_tax,tip_amount,tolls_amount,improvement_surcharge,total_amount\n",
      "\n",
      "1,2017-12-01 00:12:00,2017-12-01 00:12:51,1,.00,1,N,226,226,3,2.5,0.5,0.5,0,0,0.3,3.8\n",
      "1,2017-12-01 00:13:37,2017-12-01 00:13:47,1,.00,1,N,226,226,3,2.5,0.5,0.5,0,0,0.3,3.8\n",
      "1,2017-12-01 00:14:15,2017-12-01 00:15:05,1,.00,1,N,226,226,3,2.5,0.5,0.5,0,0,0.3,3.8\n",
      "1,2017-12-01 00:15:33,2017-12-01 00:15:37,1,.00,1,N,226,226,3,2.5,0.5,0.5,0,0,0.3,3.8\n",
      "1,2017-12-01 00:50:03,2017-12-01 00:53:35,1,.00,1,N,145,145,2,4,0.5,0.5,0,0,0.3,5.3\n",
      "1,2017-12-01 00:14:20,2017-12-01 00:28:35,1,4.20,1,N,82,258,2,15,0.5,0.5,0,0,0.3,16.3\n",
      "1,2017-12-01 00:20:32,2017-12-01 00:31:24,1,5.40,1,N,50,116,2,17,0.5,0.5,0,0,0.3,18.3\n",
      "1,2017-12-01 00:01:46,2017-12-01 00:12:19,1,1.90,1,N,161,107,1,9,0.5,0.5,2.05,0,0.3,12.35\n",
      "1,2017-12-01 00:17:52,2017-12-01 00:32:35,1,3.30,1,N,107,263,1,12.5,0.5,0.5,2.07,0,0.3,15.87\n",
      "\u001b[K:\u001b[K12-01 00:10:00,2017-12-01 00:24:35,1,2.80,1,N,264,87,1,12,0.5,0.5,2.65,0,\u001b[7mdata/yellow_tripdata_2017-12.csv\u001b[m\u001b[K\u0007"
     ]
    }
   ],
   "source": [
    "!less 'data/yellow_tripdata_2017-12.csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "HDF5 is *self-describing*: you can store the data dictionary as metadata. No need for external files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [],
   "source": [
    "# data dictionary for NY yellow taxi CSV files\n",
    "# http://www.nyc.gov/html/tlc/downloads/pdf/data_dictionary_trip_records_yellow.pdf\n",
    "data_dictionary = {\n",
    "    'VendorID': 'A code indicating the TPEP provider that provided the record',\n",
    "    'tpep_pickup_datetime': 'The date and time when the meter was engaged ',\n",
    "    'tpep_dropoff_datetime': 'The date and time when the meter was disengaged ',\n",
    "    'passenger_count': 'The number of passengers in the vehicle',\n",
    "    'trip_distance': 'The elapsed trip distance in miles reported by the taximeter',\n",
    "    'PULocationID': 'A code indicating the zone + borough of th pickup location',\n",
    "    'DOLocationID': 'A code indicating the zone + borough of th dropoff location',\n",
    "    'payment_type': 'A numeric code signifying how the passenger paid for the trip',\n",
    "    'fare_amount': 'The time-and-distance fare calculated by the meter',\n",
    "    'tip_amount': 'Tip amount – This field is automatically populated for credit card tips. Cash tips are not included',\n",
    "    'total_amount': 'The total amount charged to passengers. Does not include cash tips',\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define a description object to pass to the `Table` constructor. The information it contains will be used to define the table structure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 238,
   "metadata": {},
   "outputs": [],
   "source": [
    "class TaxiTableDescription(tb.IsDescription):\n",
    "    vendor_id = tb.UInt8Col(pos=0)\n",
    "    pickup_timestamp_ms = tb.Int64Col()\n",
    "    dropoff_timestamp_ms = tb.Int64Col()\n",
    "    passenger_count = tb.UInt8Col()\n",
    "    trip_distance = tb.Float32Col()\n",
    "    pickup_location_id = tb.UInt16Col()\n",
    "    dropoff_location_id = tb.UInt16Col()\n",
    "    payment_type = tb.UInt8Col()\n",
    "    fare_amount = tb.Float32Col()\n",
    "    tip_amount = tb.Float32Col()\n",
    "    total_amount = tb.Float32Col()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/jack/Repos/hdf5-pycon-slovakia/data/NYC-yellow-taxis-100k.h5\n"
     ]
    }
   ],
   "source": [
    "h5_file_path = os.path.join(data_dir, 'NYC-yellow-taxis-100k.h5')\n",
    "print(h5_file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [],
   "source": [
    "filters = tb.Filters(complevel=5, complib='zlib')\n",
    "\n",
    "with tb.open_file(filename=h5_file_path, mode='w') as f:\n",
    "    # create table with pre-defined schema\n",
    "    f.create_table(\n",
    "        where='/',\n",
    "        name='yellow_taxis_2017_12',\n",
    "        description=TaxiTableDescription,\n",
    "        title='NYC Yellow Taxi data December 2017',\n",
    "        filters=filters)\n",
    "    # add metadata\n",
    "    table_where = '/yellow_taxis_2017_12'\n",
    "    for key, val in data_dictionary.items():\n",
    "        f.set_node_attr(where=table_where, attrname=key, attrvalue=val)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 241,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/ (RootGroup) ''\r\n",
      "/yellow_taxis_2017_12 (Table(0,), shuffle, zlib(5)) 'NYC Yellow Taxi data December 2017'\r\n"
     ]
    }
   ],
   "source": [
    "!ptdump 'data/NYC-yellow-taxis-100k.h5'  # try also h5dump"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 242,
   "metadata": {},
   "outputs": [],
   "source": [
    "def date_to_timestamp_ms(date_obj):\n",
    "    timestamp_in_nanoseconds = date_obj.astype('int64')\n",
    "    timestamp_in_ms = (timestamp_in_nanoseconds / 1000000).astype('int64')\n",
    "    return timestamp_in_ms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fill_table(table, mapping, df):\n",
    "    num_records = df.shape[0]  # it's equal to the chunksize used in read_csv\n",
    "    row = table.row\n",
    "    for i in range(num_records):\n",
    "        row['vendor_id'] = df[mapping['vendor_id']].values[i]\n",
    "\n",
    "        pickup_ms = date_to_timestamp_ms(df[mapping['pickup_datetime']].values[i])\n",
    "        row['pickup_timestamp_ms'] = pickup_ms\n",
    "        dropoff_ms = date_to_timestamp_ms(df[mapping['dropoff_datetime']].values[i])\n",
    "        row['dropoff_timestamp_ms'] = dropoff_ms\n",
    "\n",
    "        row['passenger_count'] = df['passenger_count'].values[i]\n",
    "        row['trip_distance'] = df['trip_distance'].values[i]\n",
    "\n",
    "        row['pickup_location_id'] = df['PULocationID'].values[i]\n",
    "        row['dropoff_location_id'] = df['DOLocationID'].values[i]\n",
    "\n",
    "        row['fare_amount'] = df['fare_amount'].values[i]\n",
    "        row['tip_amount'] = df['tip_amount'].values[i]\n",
    "        row['total_amount'] = df['total_amount'].values[i]\n",
    "\n",
    "        row['payment_type'] = df['payment_type'].values[i]\n",
    "        row.append()\n",
    "    table.flush()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Remember to flush:* Remember, flushing a table is a very important step as it will not only help to maintain the integrity of your file, but also will free valuable memory resources (i.e. internal buffers) that your program may need for other things."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 244,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 10.1 s, sys: 64 ms, total: 10.1 s\n",
      "Wall time: 10.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Open the HDF5 file in 'a'ppend mode and populate the table with CSV data\n",
    "with tb.open_file(filename=h5_file_path, mode='a') as f:\n",
    "    # Left, the key we want to use. Right, the key in the CSV file\n",
    "    mapping = {\n",
    "        'vendor_id': 'VendorID',\n",
    "        'pickup_datetime': 'tpep_pickup_datetime',\n",
    "        'dropoff_datetime': 'tpep_dropoff_datetime',\n",
    "        'pickup_location_id': 'PULocationID',\n",
    "        'dropoff_location_id': 'DOLocationID'\n",
    "    }\n",
    "\n",
    "    # define the dtype to use when reading the CSV with pandas (this has nothing to do with the HDF5 table)\n",
    "    dtype = {'VendorID': 'category', 'payment_type': 'category'}\n",
    "    parse_dates = ['tpep_pickup_datetime', 'tpep_dropoff_datetime']\n",
    "\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    " \n",
    "    csv_file_path = os.path.join(data_dir, 'yellow_tripdata_2017-12.csv')\n",
    "\n",
    "    # read in chunks because these CSV files are too big\n",
    "    chunksize = 100000\n",
    "    for chunk in pd.read_csv(\n",
    "        csv_file_path, chunksize=chunksize, dtype=dtype,\n",
    "        skipinitialspace=True, parse_dates=parse_dates):\n",
    "        df = chunk.reset_index(drop=True)\n",
    "        fill_table(table, mapping, df)\n",
    "        # remove the break statement to process all chunks (it will take ~20 minutes)\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r\n",
      "------------------------------------------------------------\r\n",
      "\r\n",
      "/ (RootGroup)\r\n",
      "`--yellow_taxis_2017_12 (Table)\r\n",
      "      mem=3.9MB, disk=1.8MB [100.0%]\r\n",
      "\r\n",
      "------------------------------------------------------------\r\n",
      "Total branch leaves:    1\r\n",
      "Total branch size:      3.9MB in memory, 1.8MB on disk\r\n",
      "Mean compression ratio: 0.45\r\n",
      "HDF5 file size:         1.8MB\r\n",
      "------------------------------------------------------------\r\n",
      "\r\n"
     ]
    }
   ],
   "source": [
    "!pttree --use-si-units --sort-by 'size' 'data/NYC-yellow-taxis-100k.h5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Expressions with NumExpr"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[NumExpr](https://github.com/pydata/numexpr) is an expression evaluator for NumPy.\n",
    "\n",
    "- Parses expressions into its own bytecode that is then used by an integrated computing virtual machine written in C\n",
    "- Processes chunks of elements at a time.\n",
    "- The compilation step has some overhead, so NumExpr achieves better speedups with large arrays."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_elements = 1000000  # 1 million\n",
    "x = np.random.uniform(low=1, high=5, size=num_elements).astype('float32')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 247,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 144 ms, sys: 16 ms, total: 160 ms\n",
      "Wall time: 162 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    carray = f.create_carray(where='/', name='carray_without_numexpr', atom=tb.Float32Atom(), shape=x.shape)\n",
    "    carray[:] = x**3 + 0.5*x**2 - x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 24 ms, sys: 24 ms, total: 48 ms\n",
      "Wall time: 43.2 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    carray = f.create_carray(where='/', name='carray_with_numexpr', atom=tb.Float32Atom(), shape=x.shape)\n",
    "    ex = tb.Expr('x**3 + 0.5*x**2 - x')\n",
    "    ex.set_output(carray) # output will got to the CArray on disk\n",
    "    ex.eval()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Searches"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`tables.Table.read` reads the **entire table** (it must fit into memory) and queries it with NumPy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "74411\n",
      "CPU times: user 564 ms, sys: 0 ns, total: 564 ms\n",
      "Wall time: 562 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='r') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    rows = table.read()\n",
    "    amounts = [row['total_amount'] for row in rows if row['passenger_count'] == 1]\n",
    "    \n",
    "print(len(amounts))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`tables.Table.iterrows` returns an **iterator** that iterates over all rows (so no need to load the entire table into memory)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "74411\n",
      "CPU times: user 76 ms, sys: 4 ms, total: 80 ms\n",
      "Wall time: 79.5 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='r') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    rows = table.iterrows()\n",
    "    amounts = [row['total_amount'] for row in rows if row['passenger_count'] == 1]\n",
    "    \n",
    "print(len(amounts))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`tables.Table.read_where` uses **NumExpr** to make a **in-kernel** query.\n",
    "\n",
    "It gets all the rows which fulfill the given condition and packs them in a single container, like `tables.Table.read`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "74411\n",
      "CPU times: user 460 ms, sys: 4 ms, total: 464 ms\n",
      "Wall time: 462 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='r') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    rows = table.read_where('passenger_count == 1')\n",
    "    amounts = [x['total_amount'] for x in rows]\n",
    "    \n",
    "print(len(amounts))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`tables.Table.where` uses **NumExpr** to make a **in-kernel** query.\n",
    "\n",
    "It iterates over the rows in a table which fulfill the given condition, like `tables.Table.iterrows`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "74411\n",
      "CPU times: user 52 ms, sys: 0 ns, total: 52 ms\n",
      "Wall time: 51.8 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='r') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    rows = table.where('passenger_count == 1')\n",
    "    amounts = [x['total_amount'] for x in rows]\n",
    "    \n",
    "print(len(amounts))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Note*: you might think that `tables.Table.where` is always the fastest solution. This is not necessarily true."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Indexes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Indexing affects only `tables.Table.where` and `tables.Table.read_where`.\n",
    "\n",
    "Here is why:\n",
    "\n",
    "- With `tables.Table.read` and `tables.Table.iterrows` the condition is evaluated in Python.\n",
    "\n",
    "- With `tables.Table.where` and `tables.Table.read_where` the condition is passed to the PyTables kernel (hence the name), written in C, and evaluated there at full C speed (with NumExpr). The only values that are brought to the Python space are the rows that fulfilled the condition."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 156 ms, sys: 8 ms, total: 164 ms\n",
      "Wall time: 164 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='a') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    table.cols.trip_distance.create_index()\n",
    "    table.cols.passenger_count.create_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 254,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/                        Group\r\n",
      "/_i_yellow_taxis_2017_12 Group\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count Group\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/abounds Dataset {0/Inf}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/bounds Dataset {0/Inf, 127}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/indices Dataset {0/Inf, 131072}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/indicesLR Dataset {131072}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/mbounds Dataset {0/Inf}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/mranges Dataset {0/Inf}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/ranges Dataset {0/Inf, 2}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/sorted Dataset {0/Inf, 131072}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/sortedLR Dataset {131201}\r\n",
      "/_i_yellow_taxis_2017_12/passenger_count/zbounds Dataset {0/Inf}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance Group\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/abounds Dataset {0/Inf}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/bounds Dataset {0/Inf, 127}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/indices Dataset {0/Inf, 131072}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/indicesLR Dataset {131072}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/mbounds Dataset {0/Inf}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/mranges Dataset {0/Inf}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/ranges Dataset {0/Inf, 2}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/sorted Dataset {0/Inf, 131072}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/sortedLR Dataset {131201}\r\n",
      "/_i_yellow_taxis_2017_12/trip_distance/zbounds Dataset {0/Inf}\r\n",
      "/yellow_taxis_2017_12    Dataset {100000/Inf}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls -r 'data/NYC-yellow-taxis-100k.h5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Indices created with PyTables](img/indices.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Index performance"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Indexing works best when there is only a **small subset of the total data set** that you are querying for (see [this answer](https://stackoverflow.com/questions/20769818/search-with-index-is-slower-than-without-index-in-pytables-when-the-result-is-la/20789807#20789807))."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 255,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "76 ms ± 8.89 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "# with index\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='r') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    rows = table.where('(passenger_count == 1) | (passenger_count == 3)')\n",
    "    amounts = [x['total_amount'] for x in rows]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='a') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    table.cols.trip_distance.remove_index()\n",
    "    table.cols.passenger_count.remove_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/                        Group\r\n",
      "/_i_yellow_taxis_2017_12 Group\r\n",
      "/yellow_taxis_2017_12    Dataset {100000/Inf}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls -r 'data/NYC-yellow-taxis-100k.h5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "55.7 ms ± 6.91 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)\n"
     ]
    }
   ],
   "source": [
    "%%timeit\n",
    "# without index\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='r') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    rows = table.where('(passenger_count == 1) | (passenger_count == 3)')\n",
    "    amounts = [x['total_amount'] for x in rows]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Index performance on a big dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It's hard to see any meaningful difference with such a small table (100k records). Let's see a bigger one (~10M records).\n",
    "\n",
    "*Note*: this dataset already contains indexes for the searches I am about to perform."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "8577704\n",
      "CPU times: user 6.07 s, sys: 264 ms, total: 6.33 s\n",
      "Wall time: 6.18 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "broad_condition = 'passenger_count < 4'\n",
    "\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-2017-12.h5', mode='r') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    rows = table.where(broad_condition)\n",
    "    amounts = [x['total_amount'] for x in rows]\n",
    "    \n",
    "print(len(amounts))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "163\n",
      "CPU times: user 1.78 s, sys: 60 ms, total: 1.84 s\n",
      "Wall time: 1.78 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "strict_condition = '(passenger_count == 3) & (trip_distance > 30)'\n",
    "\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-2017-12.h5', mode='r') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    rows = table.where(strict_condition)\n",
    "    amounts = [x['total_amount'] for x in rows]\n",
    "    \n",
    "print(len(amounts))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Deleting an index takes almost no time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 60 ms, sys: 4 ms, total: 64 ms\n",
      "Wall time: 66.1 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-2017-12.h5', mode='a') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    table.cols.trip_distance.remove_index()\n",
    "    table.cols.passenger_count.remove_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Note*: re-execute the cells with the `broad_condition` and the `strict_condition` and compare the times."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating an index takes time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 262,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 16.6 s, sys: 1.62 s, total: 18.2 s\n",
      "Wall time: 18.2 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-2017-12.h5', mode='a') as f:\n",
    "    table = f.get_node(where='/yellow_taxis_2017_12')\n",
    "    table.cols.trip_distance.create_index()\n",
    "    table.cols.passenger_count.create_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Links"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [hard link](http://www.pytables.org/usersguide/libref/file_class.html#tables.File.create_hard_link)\n",
    "- symbolic links\n",
    "  - [soft link](http://www.pytables.org/usersguide/libref/file_class.html#tables.File.create_soft_link)\n",
    "  - [external link](http://www.pytables.org/usersguide/libref/file_class.html#tables.File.create_external_link)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can perform several [operations](http://www.pytables.org/usersguide/libref/link_classes.html#link-methods) on a link:\n",
    "\n",
    "- copy\n",
    "- move\n",
    "- remove\n",
    "- rename"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Hard links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 263,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    gr1 = f.create_group(where='/', name='gr1') \n",
    "    gr2 = f.create_group(where=f.root.gr1, name='gr2')\n",
    "    f.create_array(where='/gr1/gr2',  name='some_array', obj=[0, 1, 2, 3])\n",
    "    \n",
    "    f.create_hard_link(where='/', name='hard_link', target='/gr1/gr2/some_array')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 264,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/                        Group\r\n",
      "/gr1                     Group\r\n",
      "/gr1/gr2                 Group\r\n",
      "/gr1/gr2/some_array      Dataset {4}\r\n",
      "/hard_link               Dataset, same as /gr1/gr2/some_array\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls -r 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 265,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    f.remove_node(where=f.root.gr1.gr2, name='some_array')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 266,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/                        Group\r\n",
      "/gr1                     Group\r\n",
      "/gr1/gr2                 Group\r\n",
      "/hard_link               Dataset {4}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls -r 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Even if the original array is gone, the data is still accessible."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 267,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0, 1, 2, 3]\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='r') as f:\n",
    "    print(f.root.hard_link[:])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Soft links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 268,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/gr1/gr2/some_array\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    gr1 = f.create_group(where='/', name='gr1') \n",
    "    gr2 = f.create_group(where=f.root.gr1, name='gr2')\n",
    "    f.create_array(where='/gr1/gr2',  name='some_array', obj=[0, 1, 2, 3])\n",
    "    \n",
    "    soft_link = f.create_soft_link(where='/', name='soft_link', target='/gr1/gr2/some_array')\n",
    "    \n",
    "    # 'soft_link' points to 'some_array'\n",
    "    print(soft_link.target)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can create soft links to nodes which do not exist (dangling links)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 269,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/gr1/gr2/gr3/some_not_yet_existing_array\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    dangling_link = f.create_soft_link(where='/',\n",
    "                                       name='dangling_link',\n",
    "                                       target='/gr1/gr2/gr3/some_not_yet_existing_array')\n",
    "    print(dangling_link.target)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Obviously if you try to access some node which does not exist you get an error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "metadata": {},
   "outputs": [
    {
     "ename": "NoSuchNodeError",
     "evalue": "group ``/`` does not have a child named ``/gr1/gr2/gr3/some_not_yet_existing_array``",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNoSuchNodeError\u001b[0m                           Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-270-c5caa5e3b8c2>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;32mwith\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mopen_file\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilename\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'data/my_pytables_file.h5'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmode\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'r'\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m     \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mroot\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdangling_link\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m...\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/.virtualenvs/hdf5-pycon-slovakia/lib/python3.6/site-packages/tables/link.py\u001b[0m in \u001b[0;36m__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m    275\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_v_isopen\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    276\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mtables\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mClosedNodeError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'the node object is closed'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 277\u001b[0;31m         \u001b[0;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mis_dangling\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    278\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"softlink target does not exist\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    279\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/hdf5-pycon-slovakia/lib/python3.6/site-packages/tables/link.py\u001b[0m in \u001b[0;36mis_dangling\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    292\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    293\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mis_dangling\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 294\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdereference\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_v_file\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    295\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    296\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/hdf5-pycon-slovakia/lib/python3.6/site-packages/tables/link.py\u001b[0m in \u001b[0;36mdereference\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    229\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtarget\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstartswith\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'/'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    230\u001b[0m                 \u001b[0mtarget\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_v_parent\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_g_join\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtarget\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 231\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_v_file\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_node\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtarget\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    232\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    233\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/hdf5-pycon-slovakia/lib/python3.6/site-packages/tables/file.py\u001b[0m in \u001b[0;36m_get_node\u001b[0;34m(self, nodepath)\u001b[0m\n\u001b[1;32m   1540\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mroot\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1541\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1542\u001b[0;31m         \u001b[0mnode\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_node_manager\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_node\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnodepath\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1543\u001b[0m         \u001b[0;32massert\u001b[0m \u001b[0mnode\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"unable to instantiate node ``%s``\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mnodepath\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1544\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/hdf5-pycon-slovakia/lib/python3.6/site-packages/tables/file.py\u001b[0m in \u001b[0;36mget_node\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m    435\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    436\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnode_factory\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 437\u001b[0;31m             \u001b[0mnode\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mnode_factory\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    438\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcache_node\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnode\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    439\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/hdf5-pycon-slovakia/lib/python3.6/site-packages/tables/group.py\u001b[0m in \u001b[0;36m_g_load_child\u001b[0;34m(self, childname)\u001b[0m\n\u001b[1;32m   1158\u001b[0m             \u001b[0mchildname\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mjoin_path\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_v_file\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mroot_uep\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mchildname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1159\u001b[0m         \u001b[0;31m# Is the node a group or a leaf?\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1160\u001b[0;31m         \u001b[0mnode_type\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_g_check_has_child\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mchildname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1161\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1162\u001b[0m         \u001b[0;31m# Nodes that HDF5 report as H5G_UNKNOWN\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.virtualenvs/hdf5-pycon-slovakia/lib/python3.6/site-packages/tables/group.py\u001b[0m in \u001b[0;36m_g_check_has_child\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m    393\u001b[0m             raise NoSuchNodeError(\n\u001b[1;32m    394\u001b[0m                 \u001b[0;34m\"group ``%s`` does not have a child named ``%s``\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 395\u001b[0;31m                 % (self._v_pathname, name))\n\u001b[0m\u001b[1;32m    396\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mnode_type\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    397\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mNoSuchNodeError\u001b[0m: group ``/`` does not have a child named ``/gr1/gr2/gr3/some_not_yet_existing_array``"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='r') as f:\n",
    "    print(f.root.dangling_link[...])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    f.create_array(where='/gr1/gr2/gr3',\n",
    "                   name='some_not_yet_existing_array',\n",
    "                   obj=[4, 5, 6, 7],\n",
    "                   createparents=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now the path to the node exists, so the soft link is no longer dangling."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[4, 5, 6, 7]\n",
      "False\n"
     ]
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='r') as f:\n",
    "    print(f.root.dangling_link[...])\n",
    "    print(f.root.dangling_link.is_dangling())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### External links"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/file1.h5', mode='w') as f:\n",
    "    f.create_array(where='/',  name='array1', obj=[0, 1, 2, 3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "array1                   Dataset {4}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls 'data/file1.h5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/file2.h5', mode='w') as f:\n",
    "    f.create_array(where='/',  name='array2', obj=[4, 5, 6, 7])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 276,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/home/jack/Repos/hdf5-pycon-slovakia/data/file1.h5:/array1\n",
      "/home/jack/Repos/hdf5-pycon-slovakia/data/file2.h5:/array2\n"
     ]
    }
   ],
   "source": [
    "path_to_array1 = '{f1path}:/array1'.format(f1path=os.path.abspath(os.path.join(data_dir, 'file1.h5')))\n",
    "path_to_array2 = '{f2path}:/array2'.format(f2path=os.path.abspath(os.path.join(data_dir, 'file2.h5')))\n",
    "print(path_to_array1)\n",
    "print(path_to_array2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file('data/file1.h5', mode='a') as f1, tb.open_file('data/file2.h5', mode='a') as f2:\n",
    "    f1.create_external_link(where='/', name='external_link_to_array2', target=path_to_array1)\n",
    "    f2.create_external_link(where='/', name='external_link_to_array1', target=path_to_array2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 278,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "array1                   Dataset {4}\r\n",
      "external_link_to_array2  External Link {/home/jack/Repos/hdf5-pycon-slovakia/data/file1.h5//array1}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls 'data/file1.h5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Transactions: mark, undo, redo, goto"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set a snaphot of the current state of the HDF5 file with `File.mark()`.\n",
    "\n",
    "There are two implicit marks which are always available: the initial mark (0) and the final mark (-1).\n",
    "\n",
    "*Note*: I personally find the method name `undo` a bit misleading:\n",
    "\n",
    "- when a mark `name` is specified, `undo` returns to the snapshot marked as `'after b'`)\n",
    "- when a mark `name` is not specified, undo rollbacks the last transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='w') as f:\n",
    "    f.enable_undo()\n",
    "    \n",
    "    f.create_array(where='/', name='array_a', title='Array A', obj=[1, 2, 3])\n",
    "    f.mark(name='after a')\n",
    "    \n",
    "    f.create_array(where='/', name='array_b', title='Array B', obj=[4, 5, 6])\n",
    "    f.mark(name='after b')\n",
    "    \n",
    "    f.create_array(where='/', name='array_c', title='Array C', obj=[7, 8, 9])\n",
    "    f.mark(name='after c')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Transaction action log](img/action_log.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_p_transactions          Group\r\n",
      "array_a                  Dataset {3}\r\n",
      "array_b                  Dataset {3}\r\n",
      "array_c                  Dataset {3}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 281,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    current_mark = f.get_current_mark()\n",
    "    \n",
    "current_mark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    f.undo(mark='after b')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_p_transactions          Group\r\n",
      "array_a                  Dataset {3}\r\n",
      "array_b                  Dataset {3}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    f.redo()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 285,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_p_transactions          Group\r\n",
      "array_a                  Dataset {3}\r\n",
      "array_b                  Dataset {3}\r\n",
      "array_c                  Dataset {3}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {},
   "outputs": [],
   "source": [
    "with tb.open_file(filename='data/my_pytables_file.h5', mode='a') as f:\n",
    "    f.goto(mark='after a')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "_p_transactions          Group\r\n",
      "array_a                  Dataset {3}\r\n"
     ]
    }
   ],
   "source": [
    "!h5ls 'data/my_pytables_file.h5'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Migrations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You have to create a new table and copy the original data. See [this answer](https://stackoverflow.com/a/19470951)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 288,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 444 ms, sys: 212 ms, total: 656 ms\n",
      "Wall time: 655 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "with tb.open_file(filename='data/NYC-yellow-taxis-100k.h5', mode='a') as f:\n",
    "    table = f.get_node('/yellow_taxis_2017_12')\n",
    "    \n",
    "    # Prepare the new schema\n",
    "    d1 = table.description._v_colobjects\n",
    "    d2 = d1.copy()\n",
    "    d2['new_field'] = tb.UInt64Col(dflt=123)\n",
    "    \n",
    "    # Create a new table    \n",
    "    table2 = f.create_table(where='/', name='table2', description=d2, title='New Table')\n",
    "    \n",
    "    # Copy the attributes of the original table\n",
    "    table.attrs._f_copy(table2)\n",
    "    \n",
    "    # Fill the rows of the new table with default values\n",
    "    for i in range(table.nrows):\n",
    "        table2.row.append()\n",
    "\n",
    "    # Commit changes to disk\n",
    "    table2.flush()\n",
    "\n",
    "    # Copy the columns of the original table to the new table\n",
    "    for col in d1:\n",
    "        getattr(table2.cols, col)[:] = getattr(table.cols, col)[:]\n",
    "        \n",
    "    table2.flush()\n",
    "\n",
    "    # Fill the new column\n",
    "    table2.cols.new_field[:] = [x for x in range(table2.nrows)]\n",
    "    \n",
    "    # Cleanup\n",
    "    table.remove()\n",
    "    table2.move(newparent='/', newname='yellow_taxis_2017_12')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Resources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [Introduction to HDF5](https://www.youtube.com/watch?v=BAjsCldRMMc) by Quincey Koziol\n",
    "- [HDF5 take 2 - h5py & PyTables](https://www.youtube.com/watch?v=ofLFhQ9yxCw) by Tom Kooij\n",
    "- [PyTables documentation](http://www.pytables.org/usersguide/index.html)\n",
    "- [The starving CPU problem](https://python.g-node.org/python-summerschool-2013/_media/starving_cpu/starvingcpus.pdf) by Francesc Alted\n",
    "- [2012 PyData Workshop: Boosting NumPy with Numexpr and Cython](https://www.youtube.com/watch?v=J3-oN_TulTg) by Francesc Alted\n",
    "- [Moving away from HDF5](http://cyrille.rossant.net/moving-away-hdf5/) and the follow-up [Should you use HDF5](http://cyrille.rossant.net/should-you-use-hdf5/) by Cyrille Rossant\n",
    "- [On HDF5 and the future of data management](http://blog.khinsen.net/posts/2016/01/07/on-hdf5-and-the-future-of-data-management/) by Konrad Hinsen\n",
    "- [To HDF5 and beyond](http://alimanfoo.github.io/2016/04/14/to-hdf5-and-beyond.html) by Alistair Miles\n",
    "- [Retrieval of Genomic Data using PyTables](https://www.duo.uio.no/handle/10852/41542) Master's thesis by Henrik Glasø Skifjeld"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Special thanks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "[Julien Guillaumin](https://www.linkedin.com/in/julienguillaumin): HDF5 for image augmentation for deep learning applications"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}