data_wrangling_xml/sliderule_dsi_xml_exercise.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "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": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from xml.etree import ElementTree as ET"
   ]
  },
  {
   "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": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "document_tree = ET.parse( './data/mondial_database_less.xml' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'area': '28750',\n",
       " 'capital': 'cty-Albania-Tirane',\n",
       " 'car_code': 'AL',\n",
       " 'memberships': 'org-BSEC org-CEI org-CD org-SELEC org-CE org-EAPC org-EBRD org-EITI org-FAO org-IPU org-IAEA org-IBRD org-ICC org-ICAO org-ICCt org-Interpol org-IDA org-IFRCS org-IFC org-IFAD org-ILO org-IMO org-IMF org-IOC org-IOM org-ISO org-OIF org-ITU org-ITUC org-IDB org-MIGA org-NATO org-OSCE org-OPCW org-OAS org-OIC org-PCA org-UN org-UNCTAD org-UNESCO org-UNIDO org-UPU org-WCO org-WFTU org-WHO org-WIPO org-WMO org-UNWTO org-WTO'}"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document_tree.getroot()[0].attrib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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": 5,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "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.iterfind('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": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'measured': 'est.', 'year': '1950'}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "document_tree.getroot()[0].find('population').attrib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1214489 year: 1950\n",
      "1618829 year: 1960\n",
      "2138966 year: 1970\n",
      "2734776 year: 1980\n",
      "3446882 year: 1990\n",
      "3249136 year: 1997\n",
      "3304948 year: 2000\n",
      "3069275 year: 2001\n",
      "2800138 year: 2011\n"
     ]
    }
   ],
   "source": [
    "for i in document_tree.getroot()[0].findall('population'):\n",
    "    print(str(i.text) +  \" year: \"+ str(i.get('year')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Albania infant : 13.19\n",
      "Greece infant : 4.78\n",
      "Macedonia infant : 7.9\n",
      "Serbia infant : 6.16\n"
     ]
    },
    {
     "ename": "AttributeError",
     "evalue": "'NoneType' object has no attribute 'text'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-16-73bf8ab058a5>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;32mfor\u001b[0m \u001b[0mchild\u001b[0m \u001b[1;32min\u001b[0m \u001b[0mdocument_tree\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mgetroot\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 2\u001b[1;33m     \u001b[1;32mprint\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mchild\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfind\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'name'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m \u001b[1;33m+\u001b[0m \u001b[1;34m' infant : '\u001b[0m\u001b[1;33m+\u001b[0m\u001b[0mchild\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfind\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'infant_mortality'\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m: 'NoneType' object has no attribute 'text'"
     ]
    }
   ],
   "source": [
    "for child in document_tree.getroot():\n",
    "    print(child.find('name').text + ' infant : '+child.find('infant_mortality').text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# print names of all countries\n",
    "for child in document_tree.getroot()[0]:\n",
    "    print child.find('infant_mortality')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "testroot=document_tree.getroot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Albania infant : 13.19\n",
      "Greece infant : 4.78\n",
      "Macedonia infant : 7.9\n",
      "Serbia infant : 6.16\n",
      "Andorra infant : 3.69\n"
     ]
    }
   ],
   "source": [
    "for child in root.findall(\".//infant_mortality/..\"):\n",
    "    print(child.find('name').text + ' infant : '+str(float(child.find('infant_mortality').text)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('2800138', '2011')]\n",
      "[('10816286', '2011')]\n",
      "[('2059794', '2011')]\n",
      "[('7120666', '2011')]\n",
      "[('620029', '2011')]\n",
      "[('1733872', '2011')]\n",
      "[('78115', '2011')]\n"
     ]
    }
   ],
   "source": [
    "for child in testroot.findall(\"./country\"):\n",
    "    print([(i.text,i.get('year')) for i in child.findall(\"population[@year='2011']\")])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Albanian\n",
      "Greek\n"
     ]
    }
   ],
   "source": [
    "for child in testroot[0].findall('./ethnicgroup'):\n",
    "    print(child.text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2716891"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ET.tostring(root))[:2717100].rfind('river')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'ntry=\"H\" id=\"island-MargitSziget\" river=\"river-Donau\">\\n      <name>Margit Sziget</name>\\n      <locat'"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ET.tostring(root))[2716850:2716950]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "river Thjorsa\n",
      "river Joekulsa a Fjoellum\n",
      "river Glomma\n",
      "river Lagen\n",
      "river Goetaaelv\n",
      "river Klaraelv\n",
      "river Umeaelv\n",
      "river Dalaelv\n",
      "river Vaesterdalaelv\n",
      "river Oesterdalaelv\n"
     ]
    }
   ],
   "source": [
    "for i in root.find('./river'):\n",
    "    if i.tag!='located_at':\n",
    "        print(i.tag + ' ' + i.find('name').text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 256,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for i in root.findall('./country/airport')[:10]:\n",
    "    print list(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 248,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mondial\n"
     ]
    }
   ],
   "source": [
    "for i in root.findall('.//airport/..')[:10]:\n",
    "    print(i.tag)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 257,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'airport'"
      ]
     },
     "execution_count": 257,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "root.find('.//gmtOffset/..').tag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34.210017"
      ]
     },
     "execution_count": 271,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "float(root.find('.//airport/latitude').text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'country': 'IS'}"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "root.find('./river').find('source').attrib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'<ethnicgroup percentage=\"43\">Spanish</ethnicgroup>\\n      '"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ET.tostring(testroot[6].find('./ethnicgroup'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "p = []\n",
    "for child in testroot.findall('.//ethnicgroup/..//population/..'):\n",
    "    p += [[i.text,float(i.get('percentage')),child.find('name').text,float(child.find(\"population[@year='2011']\").text)] for i in child.findall('ethnicgroup')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['Albanian', 95.0, 'Albania', 2800138.0],\n",
       " ['Greek', 3.0, 'Albania', 2800138.0],\n",
       " ['Greek', 93.0, 'Greece', 10816286.0],\n",
       " ['Macedonian', 64.2, 'Macedonia', 2059794.0],\n",
       " ['Albanian', 25.2, 'Macedonia', 2059794.0],\n",
       " ['Turkish', 3.9, 'Macedonia', 2059794.0],\n",
       " ['Gypsy', 2.7, 'Macedonia', 2059794.0],\n",
       " ['Serb', 1.8, 'Macedonia', 2059794.0],\n",
       " ['Serb', 82.9, 'Serbia', 7120666.0],\n",
       " ['Montenegrin', 0.9, 'Serbia', 7120666.0],\n",
       " ['Hungarian', 3.9, 'Serbia', 7120666.0],\n",
       " ['Roma', 1.4, 'Serbia', 7120666.0],\n",
       " ['Bosniak', 1.8, 'Serbia', 7120666.0],\n",
       " ['Croat', 1.1, 'Serbia', 7120666.0],\n",
       " ['Montenegrin', 43.0, 'Montenegro', 620029.0],\n",
       " ['Serb', 32.0, 'Montenegro', 620029.0],\n",
       " ['Bosniak', 8.0, 'Montenegro', 620029.0],\n",
       " ['Albanian', 5.0, 'Montenegro', 620029.0],\n",
       " ['Albanian', 92.0, 'Kosovo', 1733872.0],\n",
       " ['Serbian', 5.0, 'Kosovo', 1733872.0],\n",
       " ['Spanish', 43.0, 'Andorra', 78115.0],\n",
       " ['Andorran', 33.0, 'Andorra', 78115.0],\n",
       " ['Portuguese', 11.0, 'Andorra', 78115.0],\n",
       " ['French', 2.0, 'Andorra', 78115.0],\n",
       " ['African', 5.0, 'Andorra', 78115.0]]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "p"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "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>epop</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ethnicgroup</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Greek</th>\n",
       "      <td>10143150.120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Serb</th>\n",
       "      <td>6138517.686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albanian</th>\n",
       "      <td>4805362.878</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Macedonian</th>\n",
       "      <td>1322387.748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Montenegrin</th>\n",
       "      <td>330698.464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hungarian</th>\n",
       "      <td>277705.974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bosniak</th>\n",
       "      <td>177774.308</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Roma</th>\n",
       "      <td>99689.324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Serbian</th>\n",
       "      <td>86693.600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Turkish</th>\n",
       "      <td>80331.966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Croat</th>\n",
       "      <td>78327.326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gypsy</th>\n",
       "      <td>55614.438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spanish</th>\n",
       "      <td>33589.450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Andorran</th>\n",
       "      <td>25777.950</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Portuguese</th>\n",
       "      <td>8592.650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>African</th>\n",
       "      <td>3905.750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>French</th>\n",
       "      <td>1562.300</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     epop\n",
       "ethnicgroup              \n",
       "Greek        10143150.120\n",
       "Serb          6138517.686\n",
       "Albanian      4805362.878\n",
       "Macedonian    1322387.748\n",
       "Montenegrin    330698.464\n",
       "Hungarian      277705.974\n",
       "Bosniak        177774.308\n",
       "Roma            99689.324\n",
       "Serbian         86693.600\n",
       "Turkish         80331.966\n",
       "Croat           78327.326\n",
       "Gypsy           55614.438\n",
       "Spanish         33589.450\n",
       "Andorran        25777.950\n",
       "Portuguese       8592.650\n",
       "African          3905.750\n",
       "French           1562.300"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "testeth=pd.DataFrame(p,columns=['ethnicgroup','percentage','county','cpop'])\n",
    "testeth['epop']=testeth['percentage']*testeth['cpop']/100.0\n",
    "testeth[['ethnicgroup','epop']].groupby('ethnicgroup').sum().sort('epop',ascending=False)"
   ]
  },
  {
   "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": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "document = ET.parse( './data/mondial_database.xml' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "root=document.getroot()\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>infant mortality</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  infant mortality\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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([[child.find('name').text, float(child.find('infant_mortality').text)] for child in root.findall(\".//infant_mortality/..\")], columns=['country','infant mortality']).sort('infant mortality')[:10]"
   ]
  },
  {
   "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>city</th>\n",
       "      <th>pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>Seoul</td>\n",
       "      <td>10229262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>7055071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>Al Qahirah</td>\n",
       "      <td>6053000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>Bangkok</td>\n",
       "      <td>5876000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Ho Chi Minh</td>\n",
       "      <td>3924435</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>Busan</td>\n",
       "      <td>3813814</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>New Taipei</td>\n",
       "      <td>3722082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>Hanoi</td>\n",
       "      <td>3056146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Al Iskandariyah</td>\n",
       "      <td>2917000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>Taipei</td>\n",
       "      <td>2626138</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                city       pop\n",
       "165            Seoul  10229262\n",
       "123        Hong Kong   7055071\n",
       "154       Al Qahirah   6053000\n",
       "75           Bangkok   5876000\n",
       "87       Ho Chi Minh   3924435\n",
       "166            Busan   3813814\n",
       "205       New Taipei   3722082\n",
       "84             Hanoi   3056146\n",
       "153  Al Iskandariyah   2917000\n",
       "204           Taipei   2626138"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame([[child.find('name').text, int(child.find('population').text)] for child in root.findall(\"./country/city/population/..\")], columns=['city','pop']).sort('pop',ascending=False)[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "p = []\n",
    "for child in root.findall('.//ethnicgroup/..//population/..'):\n",
    "    p += [[i.text,float(i.get('percentage')),child.find('name').text,float(child.findall(\"population\")[-1].text)] for i in child.findall('ethnicgroup')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>epop</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ethnic group</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": [
       "                      epop\n",
       "ethnic group              \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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eth=pd.DataFrame(p,columns=['ethnic group','percentage','county','cpop'])\n",
    "eth['epop']=eth['percentage']*eth['cpop']/100.0\n",
    "eth[['ethnic group','epop']].groupby('ethnic group').sum().sort('epop',ascending=False)[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create CAR code dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "codedict={child.get('car_code'):child.find('name').text for child in root.findall('./country')}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create DF of all rivers and lengths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "rivers=pd.DataFrame([[i.find('name').text,float(i.find('length').text),i.find('source').get('country')] for i in root.findall('./river/name/../length/..')], columns=['rname','length','scountry'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the river with the longest length"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The longest river is the Amazonas, with it's source located in Peru\n"
     ]
    }
   ],
   "source": [
    "maxriver=rivers.iloc[rivers['length'].idxmax('length')]\n",
    "print('The longest river is the '+maxriver['rname']+\", with it's source located in \"+codedict[maxriver['scountry']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "lakes=pd.DataFrame([[i.find('name').text,float(i.find('area').text),i.find('located').get('country')] for i in root.findall('./lake/name/../area/../located/..')], columns=['lname','area','lco'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The largest lake is the Caspian Sea, located in Russia\n"
     ]
    }
   ],
   "source": [
    "maxlake=lakes.iloc[lakes['area'].idxmax()]\n",
    "print('The largest lake is the '+maxlake['lname']+\", located in \"+codedict[maxlake['lco']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I don't know how we're supposed to find the airport country, as the data file doesnt have country info as attributes or children of the airports, and the airports are not children of the root of the tree.  They do have latitude and longitude data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "airport=pd.DataFrame([[i.find('name').text,i.find('elevation').text,i.find('latitude').text,i.find('longitude').text] for i in root.findall('./airport/name/../latitude/../longitude/../elevation/..')], columns=['aname','elevation','latitude','longitude'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "airport['elevation']=airport['elevation'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The highest airport is El Alto Intl, located at latitude -16.513339, longitude -68.192256\n"
     ]
    }
   ],
   "source": [
    "maxairport=airport.iloc[airport['elevation'].idxmax()]\n",
    "print('The highest airport is '+maxairport['aname']+\", located at latitude \"+maxairport['latitude']+\", longitude \"+ maxairport['longitude'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}