<jupyter_start><jupyter_text>XML exercise Using data from [**mondial database**](https://drive.google.com/file/d/14lFT4nWHgwN36ij4XZh6OUuup-K9qLgR/view?usp=sharing) find the answers to following questions:1. 10 countries with the lowest infant mortality rates2. 10 cities with the largest population3. name and country of a) longest river, b) largest lake and c) airport at highest elevation<jupyter_code>import xml.etree.ElementTree as ET
import pandas as pd

tree = ET.parse('data/mondial.xml')
root = tree.getroot()
print(root.tag) # name of the root?
print(root.attrib) # any attributes to the root?
print(len(root)) # How many children?
# Q1 - 10 countries with the lowest infant mortality rates
inf_mort = {
    'Country' : [],
    'infant_mortality' : []
}
for country in root.findall('country'):
    # name is child of the country
    NAME = country.findtext('name')
    # infant_mortality is another child of the country
    IM = country.findtext('infant_mortality')
    
    inf_mort['Country'].append(NAME)
    inf_mort['infant_mortality'].append(IM)

df = pd.DataFrame(inf_mort)
df['infant_mortality'] = df['infant_mortality'].astype(float)

df.sort_values(by='infant_mortality').head(10)
# Q2 - 10 cities with the largest population
city_data = {}

for country in root.iterfind('country'):
    cities = country.findall('city')
    if len(cities) == 0:
        cities = country.findall("province/city")
    for subelement in cities:
        this_element= {}
        try:
            this_element["population"] = int(subelement.findall('population')[-1].text)
        except:
            pass
            #print element.findall('city')
        city_data[subelement.findtext('name')] = this_element
city_data_pd = pd.DataFrame(city_data).transpose()
city_data_pd.sort_values("population", ascending=False)[:10]
# Q3 - name and country of a) longest river

river_data = {
    'river_name' : [],
    'Country' : [],
    'length' : []
}

for river in root.iterfind('river'):
    riverName = river.findtext('name')
    riverCountry = river.get('country')
    riverLength = river.findtext('length')

    river_data['river_name'].append(riverName)
    river_data['Country'].append(riverCountry)
    river_data['length'].append(riverLength)
    
df = pd.DataFrame(river_data)
df['length'] = df['length'].astype(float)
df.sort_values('length', ascending=False).head(1)
# Q3 - name and country of b) largest lake
lake_data = {
    'lake_name' : [],
    'Country' : [],
    'area' : []
}

for lake in root.iterfind('lake'):
    Name = lake.findtext('name')
    Country = lake.get('country')
    Area = lake.findtext('area')

    lake_data['lake_name'].append(Name)
    lake_data['Country'].append(Country)
    lake_data['area'].append(Area)
    
df = pd.DataFrame(lake_data)
df['area'] = df['area'].astype(float)
df.sort_values('area', ascending=False).head(2)
# Q3 - name and country of c) airport at highest elevation
airport_data = {
    'airport_name' : [],
    'Country' : [],
    'elevation' : []
}

for airport in root.iterfind('airport'):
    Name = airport.findtext('name')
    Country = airport.get('country')
    Elevation = airport.findtext('elevation')

    airport_data['airport_name'].append(Name)
    airport_data['Country'].append(Country)
    airport_data['elevation'].append(Elevation)
    
df = pd.DataFrame(airport_data)
df['elevation'] = df['elevation'].astype(float)
df.sort_values('elevation', ascending=False).head(1)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14602534562211983, 'fraction_numerical': 0.009504608294930876, 'mean_word_length': 4.278115501519757, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '640908', 'license': 'MIT', 'n_tokens_mistral': 1180, 'n_tokens_neox': 1126, 'n_words': 241, 'path': 'APIs and Other Data Types/XML Exercise.ipynb', 'repo_name': 'robynmundle/data_science', 'size': '3472'}