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": 184,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from xml.etree import ElementTree as ET\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "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": 185,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "document_tree = ET.parse( './data/mondial_database_less.xml' )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "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": 188,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>body {\n",
       "    margin: 0;\n",
       "    font-family: Helvetica;\n",
       "}\n",
       "table.dataframe {\n",
       "    border-collapse: collapse;\n",
       "    border: none;\n",
       "}\n",
       "table.dataframe tr {\n",
       "    border: none;\n",
       "}\n",
       "table.dataframe td, table.dataframe th {\n",
       "    margin: 0;\n",
       "    border: 1px solid white;\n",
       "    padding-left: 0.25em;\n",
       "    padding-right: 0.25em;\n",
       "}\n",
       "table.dataframe th:not(:empty) {\n",
       "    background-color: #fec;\n",
       "    text-align: left;\n",
       "    font-weight: normal;\n",
       "}\n",
       "table.dataframe tr:nth-child(2) th:empty {\n",
       "    border-left: none;\n",
       "    border-right: 1px dashed #888;\n",
       "}\n",
       "table.dataframe td {\n",
       "    border: 2px solid #ccf;\n",
       "    background-color: #f4f4ff;\n",
       "}\n",
       "h3 {\n",
       "    color: white;\n",
       "    background-color: black;\n",
       "    padding: 0.5em;\n",
       "}\n",
       "</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.core.display import HTML\n",
    "css = open('style-table.css').read() + open('style-notebook.css').read()\n",
    "HTML('<style>{}</style>'.format(css))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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.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": "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": 247,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "root = ET.parse('./data/mondial_database.xml')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "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>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  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": 260,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countryList = []\n",
    "moralityList = []\n",
    "for element in root.iterfind('country'):\n",
    "        mortality = element.find('infant_mortality')\n",
    "        countryName = element.find('name').text\n",
    "        mortalityVal = 0.0 \n",
    "        if(mortality is not None):\n",
    "            mortalityVal = float(mortality.text)\n",
    "            countryList.append(countryName)\n",
    "            moralityList.append(mortalityVal)\n",
    "#print countryList\n",
    "dict = {'country':countryList,'mortality':moralityList}\n",
    "df = pd.DataFrame(dict, columns=('country', 'mortality')).sort_values(by='mortality')\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "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>566</th>\n",
       "      <td>New Taipei</td>\n",
       "      <td>3939305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>604</th>\n",
       "      <td>Kaohsiung</td>\n",
       "      <td>2778659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>608</th>\n",
       "      <td>Taichung</td>\n",
       "      <td>2684893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>Taipei</td>\n",
       "      <td>2673226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>Tainan</td>\n",
       "      <td>1881645</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>910</th>\n",
       "      <td>Harare</td>\n",
       "      <td>1485231</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>642</th>\n",
       "      <td>Managua</td>\n",
       "      <td>1028808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>630</th>\n",
       "      <td>Ciudad de Guatemala</td>\n",
       "      <td>992541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>913</th>\n",
       "      <td>Bulawayo</td>\n",
       "      <td>653337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>636</th>\n",
       "      <td>Villa Nueva</td>\n",
       "      <td>527174</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    city  population\n",
       "566           New Taipei     3939305\n",
       "604            Kaohsiung     2778659\n",
       "608             Taichung     2684893\n",
       "563               Taipei     2673226\n",
       "612               Tainan     1881645\n",
       "910               Harare     1485231\n",
       "642              Managua     1028808\n",
       "630  Ciudad de Guatemala      992541\n",
       "913             Bulawayo      653337\n",
       "636          Villa Nueva      527174"
      ]
     },
     "execution_count": 261,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Top Ten populated cities with in a year, where year is a parameter\n",
    "def getTopTenPopulatedCitiesByYear(root,year):\n",
    "    countryList = []\n",
    "    cityList = []\n",
    "    yearList = []\n",
    "    populationList = []\n",
    "    \n",
    "    for element in root.iterfind('country'):\n",
    "            for city in element.iterfind('city'):\n",
    "                cityName = city.find('name').text\n",
    "                for population in city.iterfind('population'):\n",
    "                    cityList.append(cityName)\n",
    "                    #print population.attrib['year']\n",
    "                    populationList.append(np.int(population.text))\n",
    "                    yearList.append(population.attrib['year'])\n",
    "\n",
    "    #print countryList\n",
    "    dict = {'city':cityList,'population':populationList,'year':yearList}\n",
    "    df = pd.DataFrame(dict, columns=('city', 'year','population'))\n",
    "    df = df[df['year'] == year][['city','population']]\n",
    "    return df.sort_values(by='population',ascending=False).head(10)\n",
    "\n",
    "\n",
    "#You can vary the year value below to get top 10 most populated cities for that year\n",
    "getTopTenPopulatedCitiesByYear(root,'2012')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ethnicGroupName\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\n",
       "Name: ethnicGroupPopulation, dtype: float64"
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def getEthnicDF(root):\n",
    "    countryNameList = []\n",
    "    ethnicGroupNameList = []\n",
    "    ethnicGroupPopulationList = []\n",
    "    totalPopulationList = []\n",
    "    for element in root.iterfind('country'):\n",
    "        countryName =  element.find('name').text\n",
    "        latestYearPopulation = element.findall('population')[-1].text\n",
    "        for ethnicGroup in element.iterfind('ethnicgroup'):\n",
    "            countryNameList.append(countryName)\n",
    "            ethnicGroupName = ethnicGroup.text\n",
    "            ethnicGroupNameList.append(ethnicGroupName)\n",
    "            totalPopulationList.append(latestYearPopulation)\n",
    "            egp = ethnicGroup.get('percentage')\n",
    "            ethnicGroupPopulation = (np.float(egp)*np.float(latestYearPopulation))/100\n",
    "            ethnicGroupPopulationList.append(ethnicGroupPopulation)\n",
    "            \n",
    "    dict = {'countryName':countryNameList,'ethnicGroupName':ethnicGroupNameList,'ethnicGroupPopulation':ethnicGroupPopulationList,'totalPopulation':totalPopulationList}\n",
    "    ethnicDF = pd.DataFrame(dict, columns=('countryName', 'ethnicGroupName','ethnicGroupPopulation','totalPopulation'))\n",
    "    return ethnicDF\n",
    "\n",
    "ethnicDF = getEthnicDF(root)\n",
    "#ethnicDF\n",
    "ethnicDF.groupby(['ethnicGroupName'])['ethnicGroupPopulation'].sum().sort_values(ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#Common Function\n",
    "def getCountryDF(root):\n",
    "    countryCodeList = []\n",
    "    countryNameList = []\n",
    "    riverList = []\n",
    "    for element in root.iterfind('country'):\n",
    "            car_code = element.get('car_code')\n",
    "            countryName = element.find('name').text\n",
    "            #if(car_code is not None):\n",
    "            countryCodeList.append(car_code)\n",
    "            countryNameList.append(countryName)\n",
    "    #print countryList\n",
    "    dict = {'countryCode':countryCodeList,'countryName':countryNameList}\n",
    "    cdf = pd.DataFrame(dict, columns=('countryCode', 'countryName'))\n",
    "    return cdf\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "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>countryCode</th>\n",
       "      <th>riverName</th>\n",
       "      <th>riverLength</th>\n",
       "      <th>countryName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>PE</td>\n",
       "      <td>Amazonas</td>\n",
       "      <td>6448</td>\n",
       "      <td>Peru</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    countryCode riverName  riverLength countryName\n",
       "171          PE  Amazonas         6448        Peru"
      ]
     },
     "execution_count": 250,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Longest River\n",
    "def getRiverDF(root):\n",
    "    countryCodeList = []\n",
    "    riverNameList = []\n",
    "    riverLenList = []\n",
    "    for element in root.iterfind('river'):\n",
    "            source =  element.find('source')\n",
    "            srcCode = source.get('country')\n",
    "            length = element.find('length')\n",
    "            if(length is not None):\n",
    "                riverLength = np.float(length.text)\n",
    "                riverName = element.find('name').text\n",
    "                countryCodeList.append(srcCode)\n",
    "                riverNameList.append(riverName)\n",
    "                riverLenList.append(riverLength)\n",
    "    #print countryList\n",
    "    dict = {'countryCode':countryCodeList,'riverName':riverNameList,'riverLength':riverLenList}\n",
    "    riverDF = pd.DataFrame(dict, columns=('countryCode', 'riverName','riverLength'))\n",
    "    return riverDF\n",
    "\n",
    "countryDF = getCountryDF(root)\n",
    "riverDF = getRiverDF(root)\n",
    "\n",
    "f = pd.merge(riverDF, countryDF, how='inner', left_on=['countryCode'], right_on=['countryCode'])\n",
    "\n",
    "f.sort_values(by=\"riverLength\", ascending=False).head(1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 273,
   "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>countryCode</th>\n",
       "      <th>lakeName</th>\n",
       "      <th>lakeArea</th>\n",
       "      <th>countryName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>R</td>\n",
       "      <td>Caspian Sea</td>\n",
       "      <td>386400</td>\n",
       "      <td>Russia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   countryCode     lakeName  lakeArea countryName\n",
       "47           R  Caspian Sea    386400      Russia"
      ]
     },
     "execution_count": 273,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Largest Lake\n",
    "def getLakeDF(root):\n",
    "    countryCodeList = []\n",
    "    lakeNameList = []\n",
    "    lakeLenList = []\n",
    "    for element in root.iterfind('lake'):\n",
    "            srcCode =  element.get('country').split()[0]\n",
    "            area = element.find('area')\n",
    "            if(area is not None):\n",
    "                lakeLength = np.float(area.text)\n",
    "                lakeName = element.find('name').text\n",
    "                countryCodeList.append(srcCode)\n",
    "                lakeNameList.append(lakeName)\n",
    "                lakeLenList.append(lakeLength)\n",
    "    #print countryList\n",
    "    dict = {'countryCode':countryCodeList,'lakeName':lakeNameList,'lakeArea':lakeLenList}\n",
    "    lakeDF = pd.DataFrame(dict, columns=('countryCode', 'lakeName','lakeArea'))\n",
    "    return lakeDF\n",
    "    \n",
    "countryDF = getCountryDF(root)\n",
    "lakeDF = getLakeDF(root)\n",
    "\n",
    "lkf = pd.merge(lakeDF, countryDF, how='inner', left_on=['countryCode'], right_on=['countryCode'])\n",
    "\n",
    "lkf.sort_values(by=\"lakeArea\", ascending=False).head(1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 259,
   "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>countryCode</th>\n",
       "      <th>airportName</th>\n",
       "      <th>airportElevation</th>\n",
       "      <th>countryName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>BOL</td>\n",
       "      <td>El Alto Intl</td>\n",
       "      <td>4063</td>\n",
       "      <td>Bolivia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   countryCode   airportName  airportElevation countryName\n",
       "80         BOL  El Alto Intl              4063     Bolivia"
      ]
     },
     "execution_count": 259,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Aiport with highest elevation\n",
    "def getAirportDF(root):\n",
    "    countryCodeList = []\n",
    "    airportNameList = []\n",
    "    airportElevationList = []\n",
    "    for element in root.iterfind('airport'):\n",
    "            srcCode =  element.get('country')\n",
    "            elevation = element.find('elevation')\n",
    "            if(elevation is not None):\n",
    "                if(elevation.text is not None):\n",
    "                    airportElevation = np.float(elevation.text)\n",
    "                    airportName = element.find('name').text\n",
    "                    countryCodeList.append(srcCode)\n",
    "                    airportNameList.append(airportName)\n",
    "                    airportElevationList.append(airportElevation)\n",
    "    #print countryList\n",
    "    dict = {'countryCode':countryCodeList,'airportName':airportNameList,'airportElevation':airportElevationList}\n",
    "    airportDF = pd.DataFrame(dict, columns=('countryCode', 'airportName','airportElevation'))\n",
    "    return airportDF\n",
    "    \n",
    "countryDF = getCountryDF(root)\n",
    "airportDF = getAirportDF(root)\n",
    "\n",
    "lkf = pd.merge(airportDF, countryDF, how='inner', left_on=['countryCode'], right_on=['countryCode'])\n",
    "\n",
    "lkf.sort_values(by=\"airportElevation\", ascending=False).head(1)"
   ]
  }
 ],
 "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.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}