ETL_with_Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ETL with Python Tutorial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# pETL package\n",
    "pETL is a general purpose Python package for extracting, transforming and loading tables of data.\n",
    "<br>\n",
    "Extract & Load : http://petl.readthedocs.io/en/latest/io.html\n",
    "<br>\n",
    "transform: http://petl.readthedocs.io/en/latest/transform.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "special thanks for https://github.com/justmarkham and https://github.com/guipsamora for the dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "install petl by running ```pip install petl``` in Command-Line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import petl as etl"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Extract"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Extract JSON file and display"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "filename= 'drinks.json'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>total_pure_alcohol</th>\n",
       "<th>continent</th>\n",
       "<th>servings</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td>0.0 Litres</td>\n",
       "<td>AS</td>\n",
       "<td>[beer:0, spirit:0, wine:0]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>4.9 Litres</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:89, spirit:132, wine:54]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td>0.7 Litres</td>\n",
       "<td>AF</td>\n",
       "<td>[beer:25, spirit:0, wine:14]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andorra</td>\n",
       "<td>12.4 Litres</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:245, spirit:138, wine:312]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Angola</td>\n",
       "<td>5.9 Litres</td>\n",
       "<td>AF</td>\n",
       "<td>[beer:217, spirit:57, wine:45]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Antigua & Barbuda</td>\n",
       "<td>4.9 Litres</td>\n",
       "<td>None</td>\n",
       "<td>[beer:102, spirit:128, wine:45]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Argentina</td>\n",
       "<td>8.3 Litres</td>\n",
       "<td>SA</td>\n",
       "<td>[beer:193, spirit:25, wine:221]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Armenia</td>\n",
       "<td>3.8 Litres</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:21, spirit:179, wine:11]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Australia</td>\n",
       "<td>10.4 Litres</td>\n",
       "<td>OC</td>\n",
       "<td>[beer:261, spirit:72, wine:212]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Austria</td>\n",
       "<td>9.7 Litres</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:279, spirit:75, wine:191]</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "t1 = etl.fromjson(filename)\n",
    "t1.display(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "one of the key benefits from using Petl is low memory consumption, since the code is not reading the whole file, until we execute this command directy.  \n",
    "\n",
    "Moreover - petl can the the data directly from zip files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>total_pure_alcohol</th>\n",
       "<th>continent</th>\n",
       "<th>servings</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td>0.0 Litres</td>\n",
       "<td>AS</td>\n",
       "<td>[beer:0, spirit:0, wine:0]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>4.9 Litres</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:89, spirit:132, wine:54]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td>0.7 Litres</td>\n",
       "<td>AF</td>\n",
       "<td>[beer:25, spirit:0, wine:14]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andorra</td>\n",
       "<td>12.4 Litres</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:245, spirit:138, wine:312]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Angola</td>\n",
       "<td>5.9 Litres</td>\n",
       "<td>AF</td>\n",
       "<td>[beer:217, spirit:57, wine:45]</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "source = etl.sources.ZipSource('drinks.zip','drinks.json') # where is the data\n",
    "etl.fromjson(source).display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### See the number of records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "193"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t1.nrows()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Retrieving fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'country', u'total_pure_alcohol', u'continent', u'servings')\n"
     ]
    }
   ],
   "source": [
    "fields = t1.fieldnames()\n",
    "print fields"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing specific value\n",
    "our table is an iterable object of rows of cells:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.9 Litres\n"
     ]
    }
   ],
   "source": [
    "print t1[2][1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "but we can slice the tables by columns too:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.9 Litres\n"
     ]
    }
   ],
   "source": [
    "print t1['total_pure_alcohol'][1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Learning types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "country \tCounter({'unicode': 193})\n",
      "total_pure_alcohol \tCounter({'unicode': 193})\n",
      "continent \tCounter({'unicode': 170, 'NoneType': 23})\n",
      "servings \tCounter({'unicode': 193})\n"
     ]
    }
   ],
   "source": [
    "for f in fields:\n",
    "    print f,'\\t', t1.typecounter(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see few problems in our data, that need to be solved before loading to DB:\n",
    "- total alcohol is a string and not a number\n",
    "- servings is a unicode string and not a list or dictionary, and it is not normalized 1NF\n",
    "- continent can contain empty values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets convert our data into 2 tables:\n",
    "<table bordercolor=\"white\"><tr><td>\n",
    "<table>\n",
    "<tr><td colspan=2 bgcolor='bbbbbb'>Countries</td></tr>\n",
    "<tr><td><u>Country</td><td>String</td></tr>\n",
    "<tr><td>Continent</td><td>String</td></tr>\n",
    "<tr><td>Total_alcohol</td><td>Float</td></tr>\n",
    "<tr><td>Total_serving</td><td>Integer</td></tr>\n",
    "</table>\n",
    "</td><td>\n",
    "<table>\n",
    "<tr><td colspan=2 bgcolor='bbbbbb'>Servings</td></tr>\n",
    "<tr><td><u>S_id</td><td>Integer</td></tr>\n",
    "<tr><td>Country</td><td>String</td></tr>\n",
    "<tr><td>Type</td><td>String</td></tr>\n",
    "<tr><td>servings</td><td>Integer</td></tr>\n",
    "</table>\n",
    "</td></tr>\n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_______________________________"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transform"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lets define a function to converttotal_pure_alcohol to float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.9\n"
     ]
    }
   ],
   "source": [
    "def to_float(text):\n",
    "    return float(text[:-7])\n",
    "\n",
    "print to_float('4.9 Litres')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Now we will apply this function to each of the rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "t2 = t1.convert('total_pure_alcohol',to_float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Counter({'float': 193})\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>total_pure_alcohol</th>\n",
       "<th>continent</th>\n",
       "<th>servings</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td style='text-align: right'>0.0</td>\n",
       "<td>AS</td>\n",
       "<td>[beer:0, spirit:0, wine:0]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:89, spirit:132, wine:54]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td style='text-align: right'>0.7</td>\n",
       "<td>AF</td>\n",
       "<td>[beer:25, spirit:0, wine:14]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andorra</td>\n",
       "<td style='text-align: right'>12.4</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:245, spirit:138, wine:312]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Angola</td>\n",
       "<td style='text-align: right'>5.9</td>\n",
       "<td>AF</td>\n",
       "<td>[beer:217, spirit:57, wine:45]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Antigua & Barbuda</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td>None</td>\n",
       "<td>[beer:102, spirit:128, wine:45]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Argentina</td>\n",
       "<td style='text-align: right'>8.3</td>\n",
       "<td>SA</td>\n",
       "<td>[beer:193, spirit:25, wine:221]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Armenia</td>\n",
       "<td style='text-align: right'>3.8</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:21, spirit:179, wine:11]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Australia</td>\n",
       "<td style='text-align: right'>10.4</td>\n",
       "<td>OC</td>\n",
       "<td>[beer:261, spirit:72, wine:212]</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Austria</td>\n",
       "<td style='text-align: right'>9.7</td>\n",
       "<td>EU</td>\n",
       "<td>[beer:279, spirit:75, wine:191]</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print t2.typecounter('total_pure_alcohol')\n",
    "t2.display(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lets edit `servings` field\n",
    "#### first we will convert the string to dictionary\n",
    "dictionary is a useful data structure for our following steps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[beer:102, spirit:128, wine:45]\n",
      "<type 'unicode'>\n"
     ]
    }
   ],
   "source": [
    "# lets check servings content and type on  a sample value\n",
    "sample = t2['servings'][5]\n",
    "print sample\n",
    "print type(sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{u'beer': 102, u'spirit': 128, u'wine': 45}\n",
      "<type 'dict'>\n"
     ]
    }
   ],
   "source": [
    "def to_dict(text):\n",
    "    if text[0]=='[' and text[-1]==']':\n",
    "        items =  text[1:-1].split(', ') # turn values to list\n",
    "    else:\n",
    "        return None\n",
    "    output = {}\n",
    "    for i in items:\n",
    "        key, value = i.split(':') # splitting to key and value\n",
    "        output[key]=int(value)\n",
    "    return output\n",
    "\n",
    "    \n",
    "print to_dict(sample)\n",
    "print type(to_dict(sample))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Counter({'dict': 193})"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t3 = t2.convert('servings',to_dict)\n",
    "t3.typecounter('servings')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now we will unpack `servings` (convert iterable field to number of fields)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>total_pure_alcohol</th>\n",
       "<th>continent</th>\n",
       "<th>beer</th>\n",
       "<th>spirit</th>\n",
       "<th>wine</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td style='text-align: right'>0.0</td>\n",
       "<td>AS</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>89</td>\n",
       "<td style='text-align: right'>132</td>\n",
       "<td style='text-align: right'>54</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td style='text-align: right'>0.7</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>25</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>14</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andorra</td>\n",
       "<td style='text-align: right'>12.4</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>245</td>\n",
       "<td style='text-align: right'>138</td>\n",
       "<td style='text-align: right'>312</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Angola</td>\n",
       "<td style='text-align: right'>5.9</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>217</td>\n",
       "<td style='text-align: right'>57</td>\n",
       "<td style='text-align: right'>45</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "t4 = t3.unpackdict('servings')\n",
    "t4.display(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "learn more about unpacking: http://petl.readthedocs.io/en/latest/transform.html#unpacking-compound-values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We can add a new field based on row calculation - for example total price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def total_serving(row):\n",
    "    return row['beer']+row['spirit']+row['wine']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>total_pure_alcohol</th>\n",
       "<th>continent</th>\n",
       "<th>beer</th>\n",
       "<th>spirit</th>\n",
       "<th>wine</th>\n",
       "<th>total_serving</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td style='text-align: right'>0.0</td>\n",
       "<td>AS</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>89</td>\n",
       "<td style='text-align: right'>132</td>\n",
       "<td style='text-align: right'>54</td>\n",
       "<td style='text-align: right'>275</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td style='text-align: right'>0.7</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>25</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>14</td>\n",
       "<td style='text-align: right'>39</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andorra</td>\n",
       "<td style='text-align: right'>12.4</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>245</td>\n",
       "<td style='text-align: right'>138</td>\n",
       "<td style='text-align: right'>312</td>\n",
       "<td style='text-align: right'>695</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Angola</td>\n",
       "<td style='text-align: right'>5.9</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>217</td>\n",
       "<td style='text-align: right'>57</td>\n",
       "<td style='text-align: right'>45</td>\n",
       "<td style='text-align: right'>319</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Antigua & Barbuda</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td>None</td>\n",
       "<td style='text-align: right'>102</td>\n",
       "<td style='text-align: right'>128</td>\n",
       "<td style='text-align: right'>45</td>\n",
       "<td style='text-align: right'>275</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Argentina</td>\n",
       "<td style='text-align: right'>8.3</td>\n",
       "<td>SA</td>\n",
       "<td style='text-align: right'>193</td>\n",
       "<td style='text-align: right'>25</td>\n",
       "<td style='text-align: right'>221</td>\n",
       "<td style='text-align: right'>439</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Armenia</td>\n",
       "<td style='text-align: right'>3.8</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>21</td>\n",
       "<td style='text-align: right'>179</td>\n",
       "<td style='text-align: right'>11</td>\n",
       "<td style='text-align: right'>211</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Australia</td>\n",
       "<td style='text-align: right'>10.4</td>\n",
       "<td>OC</td>\n",
       "<td style='text-align: right'>261</td>\n",
       "<td style='text-align: right'>72</td>\n",
       "<td style='text-align: right'>212</td>\n",
       "<td style='text-align: right'>545</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Austria</td>\n",
       "<td style='text-align: right'>9.7</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>279</td>\n",
       "<td style='text-align: right'>75</td>\n",
       "<td style='text-align: right'>191</td>\n",
       "<td style='text-align: right'>545</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "t5 = t4.addfield('total_serving',total_serving)\n",
    "t5.display(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lets cut out the relevant table for `countries` table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>continent</th>\n",
       "<th>total_pure_alcohol</th>\n",
       "<th>total_serving</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td>AS</td>\n",
       "<td style='text-align: right'>0.0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td style='text-align: right'>275</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>0.7</td>\n",
       "<td style='text-align: right'>39</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andorra</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>12.4</td>\n",
       "<td style='text-align: right'>695</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Angola</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>5.9</td>\n",
       "<td style='text-align: right'>319</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "out_countries = t5.cut(['country','continent','total_pure_alcohol','total_serving'])\n",
    "out_countries.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Our next step will be to convert each row into multiple rows (by serving type)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lets cut the needed columns: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>beer</th>\n",
       "<th>wine</th>\n",
       "<th>spirit</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td style='text-align: right'>89</td>\n",
       "<td style='text-align: right'>54</td>\n",
       "<td style='text-align: right'>132</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td style='text-align: right'>25</td>\n",
       "<td style='text-align: right'>14</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andorra</td>\n",
       "<td style='text-align: right'>245</td>\n",
       "<td style='text-align: right'>312</td>\n",
       "<td style='text-align: right'>138</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Angola</td>\n",
       "<td style='text-align: right'>217</td>\n",
       "<td style='text-align: right'>45</td>\n",
       "<td style='text-align: right'>57</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "t6 = t5.cut(['country','beer','wine','spirit'])\n",
    "t6.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### And perform Melting (row -> rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>variable</th>\n",
       "<th>value</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td>beer</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td>wine</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td>spirit</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>beer</td>\n",
       "<td style='text-align: right'>89</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>wine</td>\n",
       "<td style='text-align: right'>54</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>spirit</td>\n",
       "<td style='text-align: right'>132</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "t7 = t6.melt(key=['country'])\n",
    "t7.display(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's filter out zero values and add row_numbers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>row</th>\n",
       "<th>country</th>\n",
       "<th>variable</th>\n",
       "<th>value</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td style='text-align: right'>1</td>\n",
       "<td>Albania</td>\n",
       "<td>beer</td>\n",
       "<td style='text-align: right'>89</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>2</td>\n",
       "<td>Albania</td>\n",
       "<td>wine</td>\n",
       "<td style='text-align: right'>54</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>3</td>\n",
       "<td>Albania</td>\n",
       "<td>spirit</td>\n",
       "<td style='text-align: right'>132</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>4</td>\n",
       "<td>Algeria</td>\n",
       "<td>beer</td>\n",
       "<td style='text-align: right'>25</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>5</td>\n",
       "<td>Algeria</td>\n",
       "<td>wine</td>\n",
       "<td style='text-align: right'>14</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "t8 = t7.selectisnot('value',0) # selecting rows where serving !=0\n",
    "t9 = t8.addrownumbers()\n",
    "t9.display()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>s_id</th>\n",
       "<th>country</th>\n",
       "<th>type</th>\n",
       "<th>servings</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td style='text-align: right'>1</td>\n",
       "<td>Albania</td>\n",
       "<td>beer</td>\n",
       "<td style='text-align: right'>89</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>2</td>\n",
       "<td>Albania</td>\n",
       "<td>wine</td>\n",
       "<td style='text-align: right'>54</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>3</td>\n",
       "<td>Albania</td>\n",
       "<td>spirit</td>\n",
       "<td style='text-align: right'>132</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>4</td>\n",
       "<td>Algeria</td>\n",
       "<td>beer</td>\n",
       "<td style='text-align: right'>25</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td style='text-align: right'>5</td>\n",
       "<td>Algeria</td>\n",
       "<td>wine</td>\n",
       "<td style='text-align: right'>14</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "out_servings = t9.rename({'row':'s_id','variable':'type','value':'servings'}) # renaming columns\n",
    "out_servings.display()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Lets check if joining our two tables results in logical output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>c_country</th>\n",
       "<th>c_continent</th>\n",
       "<th>c_total_pure_alcohol</th>\n",
       "<th>c_total_serving</th>\n",
       "<th>serv_s_id</th>\n",
       "<th>serv_type</th>\n",
       "<th>serv_servings</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td style='text-align: right'>275</td>\n",
       "<td style='text-align: right'>1</td>\n",
       "<td>beer</td>\n",
       "<td style='text-align: right'>89</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td style='text-align: right'>275</td>\n",
       "<td style='text-align: right'>2</td>\n",
       "<td>wine</td>\n",
       "<td style='text-align: right'>54</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td style='text-align: right'>275</td>\n",
       "<td style='text-align: right'>3</td>\n",
       "<td>spirit</td>\n",
       "<td style='text-align: right'>132</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>0.7</td>\n",
       "<td style='text-align: right'>39</td>\n",
       "<td style='text-align: right'>4</td>\n",
       "<td>beer</td>\n",
       "<td style='text-align: right'>25</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>0.7</td>\n",
       "<td style='text-align: right'>39</td>\n",
       "<td style='text-align: right'>5</td>\n",
       "<td>wine</td>\n",
       "<td style='text-align: right'>14</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ],
      "text/plain": [
       "+------------+-------------+----------------------+-----------------+-----------+-----------+---------------+\n",
       "| c_country  | c_continent | c_total_pure_alcohol | c_total_serving | serv_s_id | serv_type | serv_servings |\n",
       "+============+=============+======================+=================+===========+===========+===============+\n",
       "| u'Albania' | u'EU'       |                  4.9 |             275 |         1 | u'beer'   |            89 |\n",
       "+------------+-------------+----------------------+-----------------+-----------+-----------+---------------+\n",
       "| u'Albania' | u'EU'       |                  4.9 |             275 |         2 | u'wine'   |            54 |\n",
       "+------------+-------------+----------------------+-----------------+-----------+-----------+---------------+\n",
       "| u'Albania' | u'EU'       |                  4.9 |             275 |         3 | u'spirit' |           132 |\n",
       "+------------+-------------+----------------------+-----------------+-----------+-----------+---------------+\n",
       "| u'Algeria' | u'AF'       |                  0.7 |              39 |         4 | u'beer'   |            25 |\n",
       "+------------+-------------+----------------------+-----------------+-----------+-----------+---------------+\n",
       "| u'Algeria' | u'AF'       |                  0.7 |              39 |         5 | u'wine'   |            14 |\n",
       "+------------+-------------+----------------------+-----------------+-----------+-----------+---------------+\n",
       "..."
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out_countries.join(out_servings, # right table\n",
    "                   lkey='country',rkey='country', #join equality columns\n",
    "                   lprefix = 'c_', rprefix='serv_') # prefixes of columns from each table (not mandatory)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Transform</b> is a very rich library that can be combined with more internal and external functions.  \n",
    "It's recommended to check the documentation of the following actions as well:\n",
    "- Cat / Stack  - Union like operations\n",
    "- Select family - Picking a subset of rows by condition / conditions\n",
    "- Sort - Sorting the values\n",
    "- Join / Leftjoin / Rightjoin / Outerjoin / Crossjoin - various join functionality\n",
    "- Unjoin - Reversing Inner Join\n",
    "- Unique / Duplicates / Distinct - Working with duplicated values\n",
    "- Aggregate - Makes Aggregation calculation based on key (Group by)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Let's try the aggregate function to find the maximal total serving at each continent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>total_pure_alcohol</th>\n",
       "<th>continent</th>\n",
       "<th>beer</th>\n",
       "<th>spirit</th>\n",
       "<th>wine</th>\n",
       "<th>total_serving</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Afghanistan</td>\n",
       "<td style='text-align: right'>0.0</td>\n",
       "<td>AS</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Albania</td>\n",
       "<td style='text-align: right'>4.9</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>89</td>\n",
       "<td style='text-align: right'>132</td>\n",
       "<td style='text-align: right'>54</td>\n",
       "<td style='text-align: right'>275</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Algeria</td>\n",
       "<td style='text-align: right'>0.7</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>25</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td style='text-align: right'>14</td>\n",
       "<td style='text-align: right'>39</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Andorra</td>\n",
       "<td style='text-align: right'>12.4</td>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>245</td>\n",
       "<td style='text-align: right'>138</td>\n",
       "<td style='text-align: right'>312</td>\n",
       "<td style='text-align: right'>695</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>Angola</td>\n",
       "<td style='text-align: right'>5.9</td>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>217</td>\n",
       "<td style='text-align: right'>57</td>\n",
       "<td style='text-align: right'>45</td>\n",
       "<td style='text-align: right'>319</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n",
       "<p><strong>...</strong></p>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "t5.display(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>continent</th>\n",
       "<th>value</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>None</td>\n",
       "<td style='text-align: right'>665</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>AF</td>\n",
       "<td style='text-align: right'>504</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>AS</td>\n",
       "<td style='text-align: right'>646</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>EU</td>\n",
       "<td style='text-align: right'>695</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>OC</td>\n",
       "<td style='text-align: right'>545</td>\n",
       "</tr>\n",
       "<tr>\n",
       "<td>SA</td>\n",
       "<td style='text-align: right'>439</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "t5.aggregate('continent',max,'total_serving').displayall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "_________________________________"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Load"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can save the ouput in multiple ways. First - Let's try csv (that we already know how to load to MySQL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "out_countries.tocsv('out_countries.csv')\n",
    "out_servings.tocsv('out_servings.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we will work with MySQL cursor to load the data - First we'll create the schema and tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "note - SqlAlchemy package is required"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import MySQLdb as mdb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "user = 'YOURUSERNAME'\n",
    "passwd = 'YOURPASSWORD'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import MySQLdb as mdb\n",
    "con = mdb.connect(\n",
    "                host = '127.0.0.1', user = user, passwd = passwd) #optional - db=\"schema_name\"  \n",
    "# setting a cursor\n",
    "cur = con.cursor()     # get the cursor"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create and use the rellevant schema:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0L"
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cur.execute('DROP SCHEMA IF EXISTS drinks')\n",
    "cur.execute('CREATE SCHEMA IF NOT EXISTS drinks')\n",
    "cur.execute('USE drinks')\n",
    "cur.execute('SET SQL_MODE=ANSI_QUOTES') #important for petl operation - use this kind of quotes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "out_countries.todb(cur,'countries',schema='drinks', create='True')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "out_servings.todb(cur,'serving',schema='drinks', create='True')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### We can also append data to existing tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "addition = [{'country':'Neverland','continent':None,'total_pure_alcohol':0,'total_serving':0}]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class='petl'>\n",
       "<thead>\n",
       "<tr>\n",
       "<th>country</th>\n",
       "<th>total_pure_alcohol</th>\n",
       "<th>continent</th>\n",
       "<th>total_serving</th>\n",
       "</tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "<tr>\n",
       "<td>Neverland</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "<td>None</td>\n",
       "<td style='text-align: right'>0</td>\n",
       "</tr>\n",
       "</tbody>\n",
       "</table>\n"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "add_table = etl.fromdicts(addition) # create table object from dictionary\n",
    "add_table.display(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "add_table.appenddb(cur,'countries',schema='drinks',commit=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Now we can find all our data in MySQL server:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(('North Korea', 'AS', 0.0, 0L), ('Namibia', 'AF', 6.8, 380L), ('Nauru', 'OC', 1.0, 57L), ('Nepal', 'AS', 0.2, 11L), ('Netherlands', 'EU', 9.4, 529L), ('New Zealand', 'OC', 9.3, 457L), ('Nicaragua', None, 3.5, 197L), ('Niger', 'AF', 0.1, 6L), ('Nigeria', 'AF', 9.1, 49L), ('Niue', 'OC', 7.0, 395L), ('Norway', 'EU', 6.7, 369L), ('Neverland', None, 0.0, 0L), ('Neverland', None, 0.0, 0L))\n"
     ]
    }
   ],
   "source": [
    "cur.execute(\"\"\" SELECT * \n",
    "                FROM drinks.countries\n",
    "                WHERE country like 'N%' ;\"\"\")\n",
    "\n",
    "print cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## P.S. - Pandas\n",
    "\n",
    "Another very commonly used package for data manipulation is Pandas (http://pandas.pydata.org/), used not only for ETL but for data analysis, visuialization and data mining. You're welcome to check out this package and use it in your projects."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}