sliderule_dsi_xml_exercise_RH2.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": "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": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# My Anwers to the Exercises"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Importing Modules and reading in the data\n",
    "from xml.etree import ElementTree as ET\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "document = ET.parse( './data/mondial_database.xml' )\n",
    "root = document.getroot()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# Exercise1: 10 countries with the lowest infant mortality rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "countries = []      # list to hold country names\n",
    "mortalities = []    # list to hold infant mortalities\n",
    "\n",
    "# Find elements tagged with 'country', iterate over all such elements\n",
    "for country in root.getiterator(tag='country'):\n",
    "    \n",
    "    # Add country name to the list\n",
    "    countries.append(country.find('name').text)\n",
    "    \n",
    "    # Add infant mortality to the list\n",
    "    mort = country.find('infant_mortality')\n",
    "    if mort == None:           #if data don't exist, insert None\n",
    "        mortality = None\n",
    "    else:\n",
    "        mortality = float(mort.text)     #if data exist, convert str to float\n",
    "    mortalities.append(mortality)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\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",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Monaco</td>\n",
       "      <td>1.81</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Japan</td>\n",
       "      <td>2.13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>Bermuda</td>\n",
       "      <td>2.48</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Norway</td>\n",
       "      <td>2.48</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>2.53</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>2.60</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Czech Republic</td>\n",
       "      <td>2.63</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Hong Kong</td>\n",
       "      <td>2.73</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Macao</td>\n",
       "      <td>3.13</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Iceland</td>\n",
       "      <td>3.15</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            country  infant_mortality  Rank\n",
       "38           Monaco              1.81     1\n",
       "98            Japan              2.13     2\n",
       "117         Bermuda              2.48     3\n",
       "36           Norway              2.48     4\n",
       "106       Singapore              2.53     5\n",
       "37           Sweden              2.60     6\n",
       "10   Czech Republic              2.63     7\n",
       "78        Hong Kong              2.73     8\n",
       "79            Macao              3.13     9\n",
       "44          Iceland              3.15    10"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create DataFrame with the above list, list lowest top10 countries\n",
    "d = {'country': countries, 'infant_mortality': mortalities}\n",
    "imort = pd.DataFrame(d)\n",
    "d1 = imort.sort_values('infant_mortality', ascending=True).head(10)\n",
    "d1['Rank'] = [i for i in range(1, 11)]\n",
    "d1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# Exercise2: 10 cities with the largest population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cities = []         # list to hold city names\n",
    "populations = []    # list to hold city populations\n",
    "\n",
    "# Find elements tagged with 'city', iterate over all such elements\n",
    "for city in root.getiterator(tag='city'):\n",
    "    \n",
    "    # Add country name to the list    \n",
    "    cities.append(city.find('name').text)\n",
    "    \n",
    "    # Obtain the latest population information\n",
    "    years = []    # list to hold years that population data are available\n",
    "    pops = {}     # dict to hold populations for those years\n",
    "    for pop in city.findall('population'):\n",
    "        year = pop.get('year')\n",
    "        years.append(int(year))\n",
    "        pops[year] = int(pop.text)\n",
    "    \n",
    "    # If data available, get the latest, if not, insert 0\n",
    "    if years != []:\n",
    "        populations.append(pops[str(max(years))])\n",
    "    else:\n",
    "        populations.append(None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\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",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1341</th>\n",
       "      <td>Shanghai</td>\n",
       "      <td>22315474.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>771</th>\n",
       "      <td>Istanbul</td>\n",
       "      <td>13710512.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1527</th>\n",
       "      <td>Mumbai</td>\n",
       "      <td>12442373.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>479</th>\n",
       "      <td>Moskva</td>\n",
       "      <td>11979529.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1340</th>\n",
       "      <td>Beijing</td>\n",
       "      <td>11716620.0</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2810</th>\n",
       "      <td>São Paulo</td>\n",
       "      <td>11152344.0</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1342</th>\n",
       "      <td>Tianjin</td>\n",
       "      <td>11090314.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1064</th>\n",
       "      <td>Guangzhou</td>\n",
       "      <td>11071424.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1582</th>\n",
       "      <td>Delhi</td>\n",
       "      <td>11034555.0</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067</th>\n",
       "      <td>Shenzhen</td>\n",
       "      <td>10358381.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           City  Population  Rank\n",
       "1341   Shanghai  22315474.0     1\n",
       "771    Istanbul  13710512.0     2\n",
       "1527     Mumbai  12442373.0     3\n",
       "479      Moskva  11979529.0     4\n",
       "1340    Beijing  11716620.0     5\n",
       "2810  São Paulo  11152344.0     6\n",
       "1342    Tianjin  11090314.0     7\n",
       "1064  Guangzhou  11071424.0     8\n",
       "1582      Delhi  11034555.0     9\n",
       "1067   Shenzhen  10358381.0    10"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create DataFrame with the above list, list largest top10 cities\n",
    "d2 = {'City': cities, 'Population': populations}\n",
    "city_populations = pd.DataFrame(d2).dropna()     # Drop data points with None (NaN)\n",
    "city_populations = city_populations.sort_values('Population', ascending=False).head(10)\n",
    "city_populations['Rank'] = [i for i in range(1, 11)]\n",
    "city_populations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# Exercise3: 10 ethnic groups with the largest overall populations (sum of best/latest estimates over all countries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "data = {}\n",
    "\n",
    "for country in root.getiterator(tag='country'):\n",
    "    country_name = country.find('name').text\n",
    "    years = []\n",
    "    pops = {}\n",
    "    for pop in country.findall('population'):\n",
    "        year = pop.get('year')\n",
    "        years.append(int(year))\n",
    "        pops[year] = int(pop.text)\n",
    "    if years != []:\n",
    "        country_population = pops[str(max(years))]\n",
    "    else:\n",
    "        country_population = 0\n",
    "    \n",
    "    egroup = country.findall('ethnicgroup')\n",
    "    for e in egroup:\n",
    "        if e.text not in data.keys():\n",
    "            data[e.text] = 0\n",
    "        data[e.text] += country_population * float(e.get('percentage')) / 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Population (millions)</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Han Chinese</th>\n",
       "      <td>12450.6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indo-Aryan</th>\n",
       "      <td>8718.2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>European</th>\n",
       "      <td>4948.7</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>African</th>\n",
       "      <td>3183.3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dravidian</th>\n",
       "      <td>3027.1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mestizo</th>\n",
       "      <td>1577.3</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bengali</th>\n",
       "      <td>1467.8</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russian</th>\n",
       "      <td>1318.6</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Japanese</th>\n",
       "      <td>1265.3</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Malay</th>\n",
       "      <td>1219.9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Population (millions)  Rank\n",
       "Han Chinese                12450.6     1\n",
       "Indo-Aryan                  8718.2     2\n",
       "European                    4948.7     3\n",
       "African                     3183.3     4\n",
       "Dravidian                   3027.1     5\n",
       "Mestizo                     1577.3     6\n",
       "Bengali                     1467.8     7\n",
       "Russian                     1318.6     8\n",
       "Japanese                    1265.3     9\n",
       "Malay                       1219.9    10"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ethnic_data = pd.Series(data)\n",
    "d2 = (ethnic_data/100000).round(1).sort_values(ascending=False).head(10)\n",
    "d2= d2.to_frame('Population (millions)')\n",
    "d2['Rank'] = [i for i in range(1, 11)]\n",
    "d2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# Exercise4: name and country of a) longest river, b) largest lake and c) airport at highest elevation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# This time, I dealt with try/except to deal with data that are missing/not available\n",
    "# Besides that, the approach here is the same as above"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# a) Longest Rivers\n",
    "rivers = []\n",
    "for river in root.getiterator(tag='river'):\n",
    "    try:\n",
    "        river_name = river.find('name').text\n",
    "    except:\n",
    "        river_name = None\n",
    "        \n",
    "    try:\n",
    "        length = int(river.find('length').text)\n",
    "    except:\n",
    "        length = 0\n",
    "    try:\n",
    "        country = river.get('country')\n",
    "    except:\n",
    "        country = None\n",
    "    rivers.append({'River name': river_name, 'country': country, 'Length (km)': length})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Length (km)</th>\n",
       "      <th>River name</th>\n",
       "      <th>country</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>6448</td>\n",
       "      <td>Amazonas</td>\n",
       "      <td>CO BR PE</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>6380</td>\n",
       "      <td>Jangtse</td>\n",
       "      <td>CN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>4845</td>\n",
       "      <td>Hwangho</td>\n",
       "      <td>CN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>123</th>\n",
       "      <td>4400</td>\n",
       "      <td>Lena</td>\n",
       "      <td>R</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>4374</td>\n",
       "      <td>Zaire</td>\n",
       "      <td>RCB ZRE</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>138</th>\n",
       "      <td>4350</td>\n",
       "      <td>Mekong</td>\n",
       "      <td>CN LAO THA K VN</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>115</th>\n",
       "      <td>4248</td>\n",
       "      <td>Irtysch</td>\n",
       "      <td>R KAZ CN</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>4184</td>\n",
       "      <td>Niger</td>\n",
       "      <td>RMM RN WAN RG</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>4130</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>USA</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>4092</td>\n",
       "      <td>Jenissej</td>\n",
       "      <td>R</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Length (km) River name          country  Rank\n",
       "174         6448   Amazonas         CO BR PE     1\n",
       "137         6380    Jangtse               CN     2\n",
       "136         4845    Hwangho               CN     3\n",
       "123         4400       Lena                R     4\n",
       "205         4374      Zaire          RCB ZRE     5\n",
       "138         4350     Mekong  CN LAO THA K VN     6\n",
       "115         4248    Irtysch         R KAZ CN     7\n",
       "186         4184      Niger    RMM RN WAN RG     8\n",
       "160         4130   Missouri              USA     9\n",
       "119         4092   Jenissej                R    10"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "river_data = pd.DataFrame(rivers)\n",
    "d3a = river_data.sort_values('Length (km)', ascending=False).head(10)\n",
    "d3a['Rank'] = [i for i in range(1, 11)]\n",
    "d3a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# b) Largest Lakes\n",
    "lakes = []\n",
    "for lake in root.getiterator(tag='lake'):\n",
    "    try:\n",
    "        lake_name = lake.find('name').text\n",
    "    except:\n",
    "        lake_name = None\n",
    "        \n",
    "    try:\n",
    "        area = int(lake.find('area').text)\n",
    "    except:\n",
    "        area = 0\n",
    "    try:\n",
    "        country = lake.get('country')\n",
    "    except:\n",
    "        country = None\n",
    "    lakes.append({'Lake name': lake_name, 'country': country, 'Area (km^2)': area})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Area (km^2)</th>\n",
       "      <th>Lake name</th>\n",
       "      <th>country</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>386400</td>\n",
       "      <td>Caspian Sea</td>\n",
       "      <td>R AZ KAZ IR TM</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>82103</td>\n",
       "      <td>Lake Superior</td>\n",
       "      <td>CDN USA</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>68870</td>\n",
       "      <td>Lake Victoria</td>\n",
       "      <td>EAT EAK EAU</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>59600</td>\n",
       "      <td>Lake Huron</td>\n",
       "      <td>CDN USA</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>57800</td>\n",
       "      <td>Lake Michigan</td>\n",
       "      <td>USA</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>41650</td>\n",
       "      <td>Dead Sea</td>\n",
       "      <td>IL JOR WEST</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>32893</td>\n",
       "      <td>Lake Tanganjika</td>\n",
       "      <td>ZRE Z BI EAT</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>31792</td>\n",
       "      <td>Great Bear Lake</td>\n",
       "      <td>CDN</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>31492</td>\n",
       "      <td>Ozero Baikal</td>\n",
       "      <td>R</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>29600</td>\n",
       "      <td>Lake Malawi</td>\n",
       "      <td>MW MOC EAT</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Area (km^2)        Lake name         country  Rank\n",
       "54        386400      Caspian Sea  R AZ KAZ IR TM     1\n",
       "109        82103    Lake Superior         CDN USA     2\n",
       "81         68870    Lake Victoria     EAT EAK EAU     3\n",
       "106        59600       Lake Huron         CDN USA     4\n",
       "108        57800    Lake Michigan             USA     5\n",
       "47         41650         Dead Sea     IL JOR WEST     6\n",
       "83         32893  Lake Tanganjika    ZRE Z BI EAT     7\n",
       "98         31792  Great Bear Lake             CDN     8\n",
       "43         31492     Ozero Baikal               R     9\n",
       "89         29600      Lake Malawi      MW MOC EAT    10"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lake_data = pd.DataFrame(lakes)\n",
    "d3b = lake_data.sort_values('Area (km^2)', ascending=False).head(10)\n",
    "d3b['Rank'] = [i for i in range(1, 11)]\n",
    "d3b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# c) Highest airports\n",
    "airports = []\n",
    "for ap in root.getiterator(tag='lake'):\n",
    "    try:\n",
    "        ap_name = ap.find('name').text\n",
    "    except:\n",
    "        ap_name = None\n",
    "        \n",
    "    try:\n",
    "        elevation = int(ap.find('elevation').text)\n",
    "    except:\n",
    "        area = 0\n",
    "    try:\n",
    "        country = ap.get('country')\n",
    "    except:\n",
    "        country = None\n",
    "    airports.append({'Airport name': ap_name, 'Country': country, 'Elevation (m)': elevation})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Airport name</th>\n",
       "      <th>Country</th>\n",
       "      <th>Elevation (m)</th>\n",
       "      <th>Rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Licancabur Crater Lake</td>\n",
       "      <td>RCH BOL</td>\n",
       "      <td>5600</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Nam Co</td>\n",
       "      <td>CN</td>\n",
       "      <td>4718</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Lake Titicaca</td>\n",
       "      <td>BOL PE</td>\n",
       "      <td>3812</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Poopo</td>\n",
       "      <td>BOL</td>\n",
       "      <td>3686</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Salar de Uyuni</td>\n",
       "      <td>BOL</td>\n",
       "      <td>3650</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Koli Sarez</td>\n",
       "      <td>TAD</td>\n",
       "      <td>3250</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>Lake Irazu</td>\n",
       "      <td>CR</td>\n",
       "      <td>3200</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Qinghai Lake</td>\n",
       "      <td>CN</td>\n",
       "      <td>3195</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Segara Anak</td>\n",
       "      <td>RI</td>\n",
       "      <td>2008</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>Lake Tahoe</td>\n",
       "      <td>USA</td>\n",
       "      <td>1900</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Airport name  Country  Elevation (m)  Rank\n",
       "134  Licancabur Crater Lake  RCH BOL           5600     1\n",
       "64                   Nam Co       CN           4718     2\n",
       "131           Lake Titicaca   BOL PE           3812     3\n",
       "132                   Poopo      BOL           3686     4\n",
       "133          Salar de Uyuni      BOL           3650     5\n",
       "58               Koli Sarez      TAD           3250     6\n",
       "128              Lake Irazu       CR           3200     7\n",
       "63             Qinghai Lake       CN           3195     8\n",
       "62              Segara Anak       RI           2008     9\n",
       "118              Lake Tahoe      USA           1900    10"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ap_data = pd.DataFrame(airports)\n",
    "d4b = ap_data.sort_values('Elevation (m)', ascending=False).head(10)\n",
    "d4b['Rank'] = [i for i in range(1, 11)]\n",
    "d4b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}