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' )

document_tree.getroot()[0].attrib

# 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 + ':',
    capitals_string = ''
    for subelement in element.getiterator('city'):
        capitals_string += subelement.find('name').text + ', '
    print capitals_string[:-2]

document_tree.getroot()[0].find('population').attrib

for i in document_tree.getroot()[0].findall('population'):
    print(str(i.text) +  " year: "+ str(i.get('year')))

for child in document_tree.getroot():
    print(child.find('name').text + ' infant : '+child.find('infant_mortality').text)

# print names of all countries
for child in document_tree.getroot()[0]:
    print child.find('infant_mortality')

testroot=document_tree.getroot()

for child in root.findall(".//infant_mortality/.."):
    print(child.find('name').text + ' infant : '+str(float(child.find('infant_mortality').text)))

for child in testroot.findall("./country"):
    print([(i.text,i.get('year')) for i in child.findall("population[@year='2011']")])

for child in testroot[0].findall('./ethnicgroup'):
    print(child.text)

(ET.tostring(root))[:2717100].rfind('river')

(ET.tostring(root))[2716850:2716950]

for i in root.find('./river'):
    if i.tag!='located_at':
        print(i.tag + ' ' + i.find('name').text)

for i in root.findall('./country/airport')[:10]:
    print list(i)

for i in root.findall('.//airport/..')[:10]:
    print(i.tag)

root.find('.//gmtOffset/..').tag

float(root.find('.//airport/latitude').text)

root.find('./river').find('source').attrib

ET.tostring(testroot[6].find('./ethnicgroup'))

p = []
for child in testroot.findall('.//ethnicgroup/..//population/..'):
    p += [[i.text,float(i.get('percentage')),child.find('name').text,float(child.find("population[@year='2011']").text)] for i in child.findall('ethnicgroup')]

p

testeth=pd.DataFrame(p,columns=['ethnicgroup','percentage','county','cpop'])
testeth['epop']=testeth['percentage']*testeth['cpop']/100.0
testeth[['ethnicgroup','epop']].groupby('ethnicgroup').sum().sort('epop',ascending=False)

"""
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
"""


import pandas as pd

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

root=document.getroot()


pd.DataFrame([[child.find('name').text, float(child.find('infant_mortality').text)] for child in root.findall(".//infant_mortality/..")], columns=['country','infant mortality']).sort('infant mortality')[:10]

pd.DataFrame([[child.find('name').text, int(child.find('population').text)] for child in root.findall("./country/city/population/..")], columns=['city','pop']).sort('pop',ascending=False)[:10]

p = []
for child in root.findall('.//ethnicgroup/..//population/..'):
    p += [[i.text,float(i.get('percentage')),child.find('name').text,float(child.findall("population")[-1].text)] for i in child.findall('ethnicgroup')]

eth=pd.DataFrame(p,columns=['ethnic group','percentage','county','cpop'])
eth['epop']=eth['percentage']*eth['cpop']/100.0
eth[['ethnic group','epop']].groupby('ethnic group').sum().sort('epop',ascending=False)[:10]

"""
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
"""


codedict={child.get('car_code'):child.find('name').text for child in root.findall('./country')}

"""
Explanation: Create CAR code dictionary
End of explanation
"""


rivers=pd.DataFrame([[i.find('name').text,float(i.find('length').text),i.find('source').get('country')] for i in root.findall('./river/name/../length/..')], columns=['rname','length','scountry'])

"""
Explanation: Create DF of all rivers and lengths
End of explanation
"""


maxriver=rivers.iloc[rivers['length'].idxmax('length')]
print('The longest river is the '+maxriver['rname']+", with it's source located in "+codedict[maxriver['scountry']])

lakes=pd.DataFrame([[i.find('name').text,float(i.find('area').text),i.find('located').get('country')] for i in root.findall('./lake/name/../area/../located/..')], columns=['lname','area','lco'])

maxlake=lakes.iloc[lakes['area'].idxmax()]
print('The largest lake is the '+maxlake['lname']+", located in "+codedict[maxlake['lco']])

"""
Explanation: Find the river with the longest length
End of explanation
"""


airport=pd.DataFrame([[i.find('name').text,i.find('elevation').text,i.find('latitude').text,i.find('longitude').text] for i in root.findall('./airport/name/../latitude/../longitude/../elevation/..')], columns=['aname','elevation','latitude','longitude'])

airport['elevation']=airport['elevation'].astype(float)

maxairport=airport.iloc[airport['elevation'].idxmax()]
print('The highest airport is '+maxairport['aname']+", located at latitude "+maxairport['latitude']+", longitude "+ maxairport['longitude'])

"""
Explanation: I don't know how we're supposed to find the airport country, as the data file doesnt have country info as attributes or children of the airports, and the airports are not children of the root of the tree.  They do have latitude and longitude data
End of explanation
"""


