sliderule_dsi_xml_exercise.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "# XML example and exercise\n",
    "****\n",
    "+ study examples of accessing nodes in XML tree structure  \n",
    "+ work on exercise to be completed and submitted\n",
    "****\n",
    "+ reference: https://docs.python.org/2.7/library/xml.etree.elementtree.html\n",
    "+ data source: http://www.dbis.informatik.uni-goettingen.de/Mondial\n",
    "****"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from xml.etree import ElementTree as ET\n",
    "from lxml import etree as ET\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## XML example\n",
    "\n",
    "+ for details about tree traversal and iterators, see https://docs.python.org/2.7/library/xml.etree.elementtree.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "document_tree = ET.parse( './data/mondial_database_less.xml' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Albania\n",
      "Greece\n",
      "Macedonia\n",
      "Serbia\n",
      "Montenegro\n",
      "Kosovo\n",
      "Andorra\n"
     ]
    }
   ],
   "source": [
    "# print names of all countries\n",
    "for child in document_tree.getroot():\n",
    "    print child.find('name').text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "* Albania: Tirana, Shkodër, Durrës, Vlorë, Elbasan, Korçë\n",
      "* Greece: Komotini, Kavala, Athina, Peiraias, Peristeri, Acharnes, Patra, Kozani, Kerkyra, Ioannina, Thessaloniki, Iraklio, Chania, Ermoupoli, Rhodes, Tripoli, Lamia, Chalkida, Larissa, Volos, Mytilini, Karyes\n",
      "* Macedonia: Skopje, Kumanovo\n",
      "* Serbia: Beograd, Novi Sad, Niš\n",
      "* Montenegro: Podgorica\n",
      "* Kosovo: Prishtine\n",
      "* Andorra: Andorra la Vella\n"
     ]
    }
   ],
   "source": [
    "# print names of all countries and their cities\n",
    "for element in document_tree.iter('country'):\n",
    "    print '* ' + element.find('name').text + ':',\n",
    "    capitals_string = ''\n",
    "    for subelement in element.getiterator('city'):\n",
    "        capitals_string += subelement.find('name').text + ', '\n",
    "    print capitals_string[:-2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>im</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>Central African Republic</td>\n",
       "      <td>92.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Guinea-Bissau</td>\n",
       "      <td>90.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>Chad</td>\n",
       "      <td>90.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>9.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>Thailand</td>\n",
       "      <td>9.86</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      country     im\n",
       "197  Central African Republic  92.86\n",
       "214             Guinea-Bissau  90.92\n",
       "198                      Chad   90.3\n",
       "159                 Argentina   9.96\n",
       "66                   Thailand   9.86"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document = ET.parse( './data/mondial_database.xml' )\n",
    "ims = pd.DataFrame()\n",
    "n = []\n",
    "im = []\n",
    "for country in document.xpath('//country'):\n",
    "    if country.find('infant_mortality') is None:\n",
    "        continue\n",
    "    n.append(country.find('name').text)\n",
    "    im.append(country.find('infant_mortality').text)\n",
    "ims['country'] = n\n",
    "ims['im'] = im\n",
    "ims = ims.sort_values('im', ascending=False)\n",
    "ims.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "## XML exercise\n",
    "\n",
    "Using data in 'data/mondial_database.xml', the examples above, and refering to https://docs.python.org/2.7/library/xml.etree.elementtree.html, find\n",
    "\n",
    "1. 10 countries with the lowest infant mortality rates\n",
    "2. 10 cities with the largest population\n",
    "3. 10 ethnic groups with the largest overall populations (sum of best/latest estimates over all countries)\n",
    "4. name and country of a) longest river, b) largest lake and c) airport at highest elevation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>im</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Monaco</td>\n",
       "      <td>1.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Japan</td>\n",
       "      <td>2.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Bermuda</td>\n",
       "      <td>2.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Norway</td>\n",
       "      <td>2.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>2.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>2.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>2.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>2.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Macao</td>\n",
       "      <td>3.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>Iceland</td>\n",
       "      <td>3.15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            country    im\n",
       "36           Monaco  1.81\n",
       "90            Japan  2.13\n",
       "109         Bermuda  2.48\n",
       "34           Norway  2.48\n",
       "98        Singapore  2.53\n",
       "35           Sweden  2.60\n",
       "8    Czech Republic  2.63\n",
       "72        Hong Kong  2.73\n",
       "73            Macao  3.13\n",
       "39          Iceland  3.15"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document = ET.parse( './data/mondial_database.xml' )\n",
    "ims = pd.DataFrame()\n",
    "n = []\n",
    "im = []\n",
    "for country in document.iterfind('country'):\n",
    "    if country.find('infant_mortality') is None:\n",
    "        continue\n",
    "    n.append(country.find('name').text)\n",
    "    im.append(country.find('infant_mortality').text)\n",
    "ims['country'] = n\n",
    "im = pd.to_numeric(im)\n",
    "ims['im'] = im\n",
    "ims.sort_values('im', ascending=True).head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1251</th>\n",
       "      <td>Shanghai</td>\n",
       "      <td>22315474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>707</th>\n",
       "      <td>Istanbul</td>\n",
       "      <td>13710512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1421</th>\n",
       "      <td>Mumbai</td>\n",
       "      <td>12442373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>443</th>\n",
       "      <td>Moskva</td>\n",
       "      <td>11979529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1250</th>\n",
       "      <td>Beijing</td>\n",
       "      <td>11716620</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2594</th>\n",
       "      <td>São Paulo</td>\n",
       "      <td>11152344</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1252</th>\n",
       "      <td>Tianjin</td>\n",
       "      <td>11090314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974</th>\n",
       "      <td>Guangzhou</td>\n",
       "      <td>11071424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1467</th>\n",
       "      <td>Delhi</td>\n",
       "      <td>11034555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>977</th>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>10358381</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           City  population\n",
       "1251   Shanghai    22315474\n",
       "707    Istanbul    13710512\n",
       "1421     Mumbai    12442373\n",
       "443      Moskva    11979529\n",
       "1250    Beijing    11716620\n",
       "2594  São Paulo    11152344\n",
       "1252    Tianjin    11090314\n",
       "974   Guangzhou    11071424\n",
       "1467      Delhi    11034555\n",
       "977    Shenzhen    10358381"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#10 cities with the largest population\n",
    "document = ET.parse( './data/mondial_database.xml' )\n",
    "document = document.getroot()\n",
    "ims = pd.DataFrame()\n",
    "n = []\n",
    "pop = []\n",
    "for city in document.findall('.//city'):\n",
    "    if city.find('population') is None:\n",
    "        continue\n",
    "    n.append(city.find('name').text)\n",
    "    pop.append(city.findall('population')[-1].text)\n",
    "ims['City'] = n\n",
    "pop = pd.to_numeric(pop, errors='ignore')\n",
    "ims['population'] = pop\n",
    "ims.sort_values('population', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>pop_of_ethnic_group</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ethnicity</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Han Chinese</th>\n",
       "      <td>1.245059e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indo-Aryan</th>\n",
       "      <td>8.718156e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>European</th>\n",
       "      <td>4.948722e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>African</th>\n",
       "      <td>3.183251e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dravidian</th>\n",
       "      <td>3.027137e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mestizo</th>\n",
       "      <td>1.577344e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bengali</th>\n",
       "      <td>1.467769e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russian</th>\n",
       "      <td>1.318570e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japanese</th>\n",
       "      <td>1.265342e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Malay</th>\n",
       "      <td>1.219936e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             pop_of_ethnic_group\n",
       "ethnicity                       \n",
       "Han Chinese         1.245059e+09\n",
       "Indo-Aryan          8.718156e+08\n",
       "European            4.948722e+08\n",
       "African             3.183251e+08\n",
       "Dravidian           3.027137e+08\n",
       "Mestizo             1.577344e+08\n",
       "Bengali             1.467769e+08\n",
       "Russian             1.318570e+08\n",
       "Japanese            1.265342e+08\n",
       "Malay               1.219936e+08"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#10 ethnic groups with the largest overall populations (sum of best/latest estimates over all countries)\n",
    "\n",
    "#\n",
    "document = ET.parse( './data/mondial_database.xml' )\n",
    "egp = pd.DataFrame()\n",
    "for country in document.xpath('//country'):\n",
    "    for e in country.findall('ethnicgroup'):\n",
    "        egp = egp.append([[float(e.attrib['percentage']), e.text, e.getparent().findall('population')[-1].text]])\n",
    "\n",
    "egp.columns = ['percent', 'ethnicity', 'population']\n",
    "egp = egp.sort_values(['ethnicity'])\n",
    "egp['population'] = pd.to_numeric(egp['population'])\n",
    "egp['pop_of_ethnic_group'] = (egp['percent'] / 100) * egp['population'] \n",
    "egp = egp.groupby('ethnicity').sum()\n",
    "egp[['pop_of_ethnic_group']].sort_values('pop_of_ethnic_group', ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>name</th>\n",
       "      <th>length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>Peru</td>\n",
       "      <td>Amazonas</td>\n",
       "      <td>6448.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    country      name  length\n",
       "174    Peru  Amazonas  6448.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#name and country of a) longest river, b) \n",
    "document = ET.parse( './data/mondial_database.xml' )\n",
    "csn = []\n",
    "csc = []\n",
    "rivers = pd.DataFrame()\n",
    "rn = []\n",
    "l = []\n",
    "cc = []\n",
    "#Replace country code with Country name\n",
    "for country in document.iterfind('country'):\n",
    "    csn.append(country.find('name').text)\n",
    "    csc.append(country.attrib['car_code'])\n",
    "\n",
    "for river in document.iterfind('river'):\n",
    "    if river.find('length') is None:\n",
    "        continue\n",
    "    r = str()\n",
    "    rn.append(river.find('name').text)\n",
    "    l.append(river.find('length').text)\n",
    "    cc.append(river.find('source').attrib['country'])\n",
    "\n",
    "l = pd.to_numeric(l)\n",
    "rivers['country'] = cc\n",
    "rivers['name'] = rn\n",
    "rivers['length'] = l\n",
    "rivers = rivers.sort_values(['length'], ascending=False)\n",
    "rivers['country'] = rivers['country'].replace(csc, csn)\n",
    "rivers.head(1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>name</th>\n",
       "      <th>area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Russia</td>\n",
       "      <td>Caspian Sea</td>\n",
       "      <td>386400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Canada</td>\n",
       "      <td>Lake Superior</td>\n",
       "      <td>82103.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Tanzania</td>\n",
       "      <td>Lake Victoria</td>\n",
       "      <td>68870.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Canada</td>\n",
       "      <td>Lake Huron</td>\n",
       "      <td>59600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>United States</td>\n",
       "      <td>Lake Michigan</td>\n",
       "      <td>57800.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           country           name      area\n",
       "54          Russia    Caspian Sea  386400.0\n",
       "109         Canada  Lake Superior   82103.0\n",
       "81        Tanzania  Lake Victoria   68870.0\n",
       "106         Canada     Lake Huron   59600.0\n",
       "108  United States  Lake Michigan   57800.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#largest lake\n",
    "lakes = pd.DataFrame()\n",
    "csn = []\n",
    "csc = []\n",
    "ln = []\n",
    "a = []\n",
    "cc = []\n",
    "#Replace country code with Country name\n",
    "for country in document.iterfind('country'):\n",
    "    csn.append(country.find('name').text)\n",
    "    csc.append(country.attrib['car_code'])\n",
    "\n",
    "for lake in document.iterfind('lake'):\n",
    "    if lake.find('name') is None:\n",
    "        ln.append('')\n",
    "    else:\n",
    "        ln.append(lake.find('name').text)\n",
    "    if lake.find('area') is None:\n",
    "        a.append(0)\n",
    "    else:\n",
    "        a.append(lake.find('area').text)\n",
    "    if lake.find('located') is None:\n",
    "        cc.append(lake.attrib['country'])\n",
    "    else:\n",
    "        cc.append(lake.find('located').attrib['country'])\n",
    "a = pd.to_numeric(a)\n",
    "lakes['country'] = cc\n",
    "lakes['name'] = ln\n",
    "lakes['area'] = a\n",
    "lakes = lakes.sort_values(['area'], ascending=False)\n",
    "lakes['country'] = lakes['country'].replace(csc, csn)\n",
    "lakes.head(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>name</th>\n",
       "      <th>elevation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Bolivia</td>\n",
       "      <td>El Alto Intl</td>\n",
       "      <td>4063.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>China</td>\n",
       "      <td>Lhasa-Gonggar</td>\n",
       "      <td>4005.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>China</td>\n",
       "      <td>Yushu Batang</td>\n",
       "      <td>3963.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>813</th>\n",
       "      <td>Peru</td>\n",
       "      <td>Juliaca</td>\n",
       "      <td>3827.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>815</th>\n",
       "      <td>Peru</td>\n",
       "      <td>Teniente Alejandro Velasco Astete Intl</td>\n",
       "      <td>3311.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     country                                    name  elevation\n",
       "80   Bolivia                            El Alto Intl     4063.0\n",
       "219    China                           Lhasa-Gonggar     4005.0\n",
       "241    China                            Yushu Batang     3963.0\n",
       "813     Peru                                 Juliaca     3827.0\n",
       "815     Peru  Teniente Alejandro Velasco Astete Intl     3311.0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#airport at highest elevation\n",
    "airport = pd.DataFrame()\n",
    "csn = []\n",
    "csc = []\n",
    "an = []\n",
    "elev = []\n",
    "cc = []\n",
    "#Replace country code with Country name\n",
    "for country in document.iterfind('country'):\n",
    "    csn.append(country.find('name').text)\n",
    "    csc.append(country.attrib['car_code'])\n",
    "\n",
    "for ap in document.iterfind('airport'):\n",
    "    if ap.find('name') is None:\n",
    "        an.append('')\n",
    "    else:\n",
    "        an.append(ap.find('name').text)\n",
    "    if ap.find('elevation') is None:\n",
    "        elev.append(0)\n",
    "    else:\n",
    "        elev.append(ap.find('elevation').text)\n",
    "    cc.append(ap.attrib['country'])\n",
    "elev = pd.to_numeric(elev)\n",
    "airport['country'] = cc\n",
    "airport['name'] = an\n",
    "airport['elevation'] = elev\n",
    "airport['country'] = airport['country'].replace(csc, csn)\n",
    "airport = airport.sort_values(['elevation'], ascending=False)\n",
    "airport.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}