from xml.etree import ElementTree as ET

"""
Explanation: XML example and exercise


study examples of accessing nodes in XML tree structure  
work on exercise to be completed and submitted



reference: https://docs.python.org/2.7/library/xml.etree.elementtree.html
data source: http://www.dbis.informatik.uni-goettingen.de/Mondial
End of explanation
"""


document_tree = ET.parse( './data/mondial_database_less.xml' )

# print names of all countries
for child in document_tree.getroot():
    print (child.find('name').text)

# print names of all countries and their cities
for element in document_tree.iterfind('country'):
    print ('* ' + element.find('name').text + ':', end=''),
    capitals_string = ''
    for subelement in element.getiterator('city'):
        capitals_string += subelement.find('name').text + ', '
    print (capitals_string[:-2])

"""
Explanation: XML example

for details about tree traversal and iterators, see https://docs.python.org/2.7/library/xml.etree.elementtree.html
End of explanation
"""


document = ET.parse( './data/mondial_database.xml' )

# print child and attributes
#for child in document.getroot():
#    print (child.tag, child.attrib)

import pandas as pd

# Create a list of country and their Infant Mortality Rate 
country_imr=[]
for country in document.getroot().findall('country'):
    name = country.find('name').text
    infant_mortality_rate = country.find('infant_mortality')
    if infant_mortality_rate is not None:
        infant_mortality_rate=infant_mortality_rate.text
    else :
        infant_mortality_rate = -1
    country_imr.append((name, (float)(infant_mortality_rate)))

"""
Explanation: XML exercise
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

10 countries with the lowest infant mortality rates
10 cities with the largest population
10 ethnic groups with the largest overall populations (sum of best/latest estimates over all countries)
name and country of a) longest river, b) largest lake and c) airport at highest elevation
End of explanation
"""


df = pd.DataFrame(country_imr, columns=['Country', 'Infant_Mortality_Rate'])
df_unknown_removed = df[df.Infant_Mortality_Rate != -1] 
df_unknown_removed.set_index('Infant_Mortality_Rate').sort().head(10)

city_population=[]
for country in document.iterfind('country'):
    for state in country.iterfind('province'):
        for city in state.iterfind('city'):
            try:
                city_population.append((city.find('name').text, float(city.find('population').text)))
            except:
                next
    for city in country.iterfind('city'):
        try:
            city_population.append((city.find('name').text, float(city.find('population').text)))
        except:
            next

"""
Explanation: 10 countries with the lowest infant mortality rates
End of explanation
"""


df = pd.DataFrame(city_population, columns=['City', 'Population'])
#df.info()
df.sort_index(by='Population', ascending=False).head(10)

ethnic_population={}
country_population={}
for country in document.iterfind('country'):
    try:
        country_population[country.find('name').text]= float(country.find('population').text)
    except:
        next
    for state in country.iterfind('province' or 'state'):
        try:
            country_population[country.find('name').text] += float(state.find('population').text)
        except:
            next
        for city in state.iterfind('city'):
            try:
                country_population[country.find('name').text] += float(city.find('population').text)
            except:
                next

for country in document.iterfind('country'):
    for ethnicgroup in country.iterfind('ethnicgroup'):
        try:
            if ethnicgroup.text in ethnic_population:
                ethnic_population[ethnicgroup.text] += country_population[country.find('name').text]*float(ethnicgroup.get('percentage'))/100
            else:
                ethnic_population[ethnicgroup.text] = country_population[country.find('name').text]*float(ethnicgroup.get('percentage'))/100
        except:
            next

"""
Explanation: 10 cities with the largest population
End of explanation
"""


pd.DataFrame(sorted(ethnic_population.items(), key=lambda x:x[1], reverse=True)[:10], columns=['Ethnic_Groups', 'Population'])

"""
Explanation: 10 ethnic groups with the largest overall populations (sum of best/latest estimates over all countries)
End of explanation
"""


rivers_list=[]
rivers_df = pd.DataFrame()
for rivers in document.iterfind('river'):
    try:
        rivers_list.append({'name':rivers.find('name').text, 'length':int(rivers.find('length').text), 'country':rivers.find('located').attrib['country']})
    except:
        next
rivers_df = pd.DataFrame(rivers_list)
rivers_df.sort('length', ascending=False).head(1)

"""
Explanation: Longest River
End of explanation
"""


lake_list=[]
lake_df = pd.DataFrame()
for lakes in document.iterfind('lake'):
    try:
        lake_list.append({'name':lakes.find('name').text, 'area':int(lakes.find('area').text), 'country':lakes.find('located').attrib['country']})
    except:
        next
lakes_df = pd.DataFrame(lake_list)
lakes_df.sort('area', ascending=False).head(1)

"""
Explanation: Largest Lake
End of explanation
"""


ap_list=[]
ap_df = pd.DataFrame()
for ap in document.iterfind('airport'):
    try:
        ap_list.append({'name':ap.find('name').text, 'elevation':int(ap.find('elevation').text), 'country':ap.attrib['country']})
    except:
        next
ap_df = pd.DataFrame(ap_list)
ap_df.sort('elevation', ascending=False).head(1)

"""
Explanation: Airport At Highest Elevation
End of explanation
"""


