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": 2,
   "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": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "document_tree = ET.parse( './data/mondial_database_less.xml' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "* Albania:\n",
      "Albania, Tirana, Tirane, Shkodër, Durrës, Vlorë, Elbasan, Korçë\n",
      "* Greece:\n",
      "Greece, Anatolikis Makedonias kai Thrakis, Komotini, Kavala, Attikis, Athina, Peiraias, Piräus, Peristeri, Acharnes, Dytikis Elladas, Patra, Patras, Dytikis Makedonias, Kozani, Ionion Nison, Kerkyra, Korfu, Ipeiroy, Ioannina, Kentrikis Makedonias, Thessaloniki, Kritis, Iraklio, Iraklion, Chania, Notioy Aigaioy, Ermoupoli, Rhodes, Rhodos, Peloponnisos, Tripoli, Stereas Elladas, Lamia, Chalkida, Chalkis, Thessalias, Larissa, Volos, Boreioy Aigaioy, Mytilini, Aghion Oros, Karyes\n",
      "* Macedonia:\n",
      "Macedonia, Skopje, Kumanovo\n",
      "* Serbia:\n",
      "Serbia, Beograd, Novi Sad, Niš\n",
      "* Montenegro:\n",
      "Montenegro, Podgorica\n",
      "* Kosovo:\n",
      "Kosovo, Prishtine, Pristina\n",
      "* Andorra:\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('name'):\n",
    "        capitals_string += subelement.text + ', '\n",
    "    print(capitals_string[:-2])"
   ]
  },
  {
   "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": 83,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "document = ET.parse( './data/mondial_database.xml' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>Infant Mortality</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Monaco</th>\n",
       "      <td>1.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Romania</th>\n",
       "      <td>10.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fiji</th>\n",
       "      <td>10.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brunei</th>\n",
       "      <td>10.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grenada</th>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mauritius</th>\n",
       "      <td>10.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Panama</th>\n",
       "      <td>10.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Seychelles</th>\n",
       "      <td>10.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Arab Emirates</th>\n",
       "      <td>10.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Barbados</th>\n",
       "      <td>10.93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Infant Mortality\n",
       "Monaco                           1.81\n",
       "Romania                         10.16\n",
       "Fiji                             10.2\n",
       "Brunei                          10.48\n",
       "Grenada                          10.5\n",
       "Mauritius                       10.59\n",
       "Panama                           10.7\n",
       "Seychelles                      10.77\n",
       "United Arab Emirates            10.92\n",
       "Barbados                        10.93"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Question 1: Find the 10 countries with the lowest infant mortality rates\n",
    "\n",
    "#Initialize dictionary\n",
    "mortRates = {}\n",
    "\n",
    "#Iterate through element tree to extract country names and infant mortality rates. Assign mortality rates to each country in the dictionary\n",
    "for element in document.iterfind('country'):\n",
    "    mortRates[element.findtext('name')] = element.findtext('infant_mortality')\n",
    "\n",
    "#Convert dictionary to dataframe and remove any rows without mortality information\n",
    "mortRatesDF = pd.DataFrame({'Infant Mortality': mortRates})\n",
    "mortRatesDF = mortRatesDF.dropna()\n",
    "\n",
    "#Sort by infant mortality rates and return 10 lowest values\n",
    "sortedMortRates = mortRatesDF.sort_values(by = ['Infant Mortality'])\n",
    "sortedMortRates[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "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 Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Seoul</th>\n",
       "      <td>9708483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Al Qahirah</th>\n",
       "      <td>8471859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bangkok</th>\n",
       "      <td>7506700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hong Kong</th>\n",
       "      <td>7055071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Macao</th>\n",
       "      <td>7055071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ho Chi Minh</th>\n",
       "      <td>5968384</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Singapore</th>\n",
       "      <td>5076700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Al Iskandariyah</th>\n",
       "      <td>4123869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Taipei</th>\n",
       "      <td>3939305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Busan</th>\n",
       "      <td>3403135</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 City Population\n",
       "Seoul                    9708483\n",
       "Al Qahirah               8471859\n",
       "Bangkok                  7506700\n",
       "Hong Kong                7055071\n",
       "Macao                    7055071\n",
       "Ho Chi Minh              5968384\n",
       "Singapore                5076700\n",
       "Al Iskandariyah          4123869\n",
       "New Taipei               3939305\n",
       "Busan                    3403135"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Question 2: Find the 10 cities with the largest populations\n",
    "\n",
    "#Initialize dictionary\n",
    "cityPop = {}\n",
    "\n",
    "#Iterate through element tree to extract city names and populations. Assign population to each city in the dictionary    \n",
    "for country in document.iterfind('country'):\n",
    "    for city in country.iterfind('city'):\n",
    "        city_name = city.findtext('name')\n",
    "        for popData in city.iterfind('population'):\n",
    "            population = popData        \n",
    "        cityPop[city_name] = population.text\n",
    "        \n",
    "#Convert dictionary to dataframe and convert populations to floats\n",
    "cityPopDF = pd.DataFrame({'City Population': cityPop})\n",
    "cityPopDF['City Population'] = cityPopDF['City Population'].apply(pd.to_numeric)\n",
    "\n",
    "#Sort by population and return 10 highest values\n",
    "sortedCityPop = cityPopDF.sort_values(by = ['City Population'], ascending = False)\n",
    "sortedCityPop[0:10]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "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>Ethnic Population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>European</th>\n",
       "      <td>40669887.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>African</th>\n",
       "      <td>29342247.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chinese</th>\n",
       "      <td>27479485.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mestizo</th>\n",
       "      <td>26812786.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arab</th>\n",
       "      <td>15913381.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Polynesian</th>\n",
       "      <td>14594965.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jewish</th>\n",
       "      <td>12452539.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indian</th>\n",
       "      <td>9610953.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Black</th>\n",
       "      <td>9291337.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Armenian</th>\n",
       "      <td>7453545.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Ethnic Population\n",
       "European           40669887.5\n",
       "African            29342247.0\n",
       "Chinese            27479485.0\n",
       "Mestizo            26812786.0\n",
       "Arab               15913381.0\n",
       "Polynesian         14594965.0\n",
       "Jewish             12452539.0\n",
       "Indian              9610953.0\n",
       "Black               9291337.0\n",
       "Armenian            7453545.0"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Question 3: Find the 10 ethnic groups with the largest overall populations (sum of best/latest estimates over all countries)\n",
    "\n",
    "#Initialize dictionary\n",
    "ethnicGrp = {}\n",
    "\n",
    "#Iterate through element tree to extract country population   \n",
    "for country in document.iterfind('country'):\n",
    "    for population in city.iterfind('population'):\n",
    "         totalPopulation = int(population.text)\n",
    "            \n",
    "#Continue iterating through the country's ethnic groups and calculate ethnic population from ethnic % and total population\n",
    "    for ethnicGroup in country.iterfind('ethnicgroup'):\n",
    "        groupName = country.findtext('ethnicgroup')\n",
    "        percentage = float(ethnicGroup.get('percentage'))\n",
    "        ethnicPop = percentage * totalPopulation\n",
    "        if groupName in ethnicGrp.keys():\n",
    "            ethnicGrp[groupName] += ethnicPop\n",
    "        else:\n",
    "            ethnicGrp[groupName] = ethnicPop\n",
    "\n",
    "#Convert dictionary to dataframe\n",
    "ethnicGrpDF = pd.DataFrame({'Ethnic Population': ethnicGrp})\n",
    "\n",
    "#Sort by population and return 10 highest values\n",
    "sortedEthnicPop = ethnicGrpDF.sort_values(by = ['Ethnic Population'], ascending = False)\n",
    "sortedEthnicPop[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('CO BR PE', 'Amazonas')"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Question 4: Find the name and country of a) longest river, b) largest lake and c) airport at highest elevation\n",
    "#Part a\n",
    "#Initialize length of zero to begin comparisons\n",
    "maxLength = 0\n",
    "\n",
    "#Iterate through all rivers\n",
    "for river in document.iterfind('river'):\n",
    "    name = river.findtext('name')\n",
    "    country = river.get('country')\n",
    "    try: \n",
    "        length = float(river.findtext('length'))\n",
    "    except: \n",
    "        pass\n",
    "    if length > maxLength:\n",
    "        maxLength = length\n",
    "        longestRiver = name\n",
    "        countryWithRiver = country\n",
    "countryWithRiver, longestRiver"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 191,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('R AZ KAZ IR TM', 'Caspian Sea')"
      ]
     },
     "execution_count": 191,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Part b\n",
    "#Initialize area of zero to begin comparisons\n",
    "maxArea = 0\n",
    "\n",
    "#Iterate through all lakes\n",
    "for lake in document.iterfind('lake'):\n",
    "    name = lake.findtext('name')\n",
    "    country = lake.get('country')\n",
    "    try: \n",
    "        area = float(lake.findtext('area'))\n",
    "    except: \n",
    "        pass\n",
    "    if area > maxArea:\n",
    "        maxArea = area\n",
    "        largestLake = name\n",
    "        countryWithLake = country\n",
    "countryWithLake, largestLake"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('BOL', 'El Alto Intl')"
      ]
     },
     "execution_count": 192,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Part c\n",
    "#Initialize elevation of zero to begin comparisons\n",
    "maxElevation = 0\n",
    "\n",
    "#Iterate through all airports\n",
    "for airport in document.iterfind('airport'):\n",
    "    name = airport.findtext('name')\n",
    "    country = airport.get('country')\n",
    "    try: \n",
    "        elevation = float(airport.findtext('elevation'))\n",
    "    except: \n",
    "        pass\n",
    "    if elevation > maxElevation:\n",
    "        maxElevation = elevation\n",
    "        highestAirport = name\n",
    "        countryWithAirport = country\n",
    "countryWithAirport, highestAirport"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}