# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

import pandas as pd
import numpy as np

import requests
import json
import urllib

from pathlib import Path
PATH = Path('/home/ubuntu/climate-classification/')

GOOGLE_API_KEY = 

# +
# # !curl --header "Host: data.un.org" --header "Connection: keep-alive" "http://data.un.org/Handlers/DownloadHandler.ashx?DataFilter=tableCode:240;sexCode:0&DataMartId=POP&Format=csv&c=2,3,6,8,10,12,14,16,17,18&s=_countryEnglishNameOrderBy:asc,refYear:desc,areaCode:asc" -o "UNdata_Export.zip" -L
# # !unzip "UNdata_Export.zip"
# # !rm "UNdata_Export.zip"
# # !mv UNdata*.csv population.csv
# -

df = pd.read_csv(PATH/'population.csv')
df.columns = df.columns.str.lower()

# only look at records with these parameters
df = df[df['city type']=='City proper']
df = df[df['reliability']=='Final figure, complete']


# +
def refine_information(m):
    latest = m.loc[m['source year'].idxmax()]
    res = latest[['country or area','value']]
    return res

def get_latest_data(df):
    summary = df.groupby('city').apply(refine_information)
    summary.columns = ['country','population']
    summary.sort_values('population', inplace=True, ascending=False)
    summary.reset_index(inplace=True)
    return summary


# -

df = get_latest_data(df)

df['population'] = df['population'].astype('int')
df['combined_city_country'] = df['city'] + ' ' + df['country']

# make google map's lookup easier
df.loc[df['country']=="Democratic People's Republic of Korea",'country']='North Korea'
df.loc[df['city']=='Bhalswa Jahangir Pur','city']='Bhalswa Jahangirpuri'


def get_info(city_country, key=GOOGLE_API_KEY):

    address = urllib.parse.quote(city_country)
    url = f'https://maps.googleapis.com/maps/api/geocode/json?&address={address}&key={key}'
    r = requests.get(url).json()
    
    if r['status']=='OK':
        g = r['results'][0]
        slsn, slln, lsn, lln, asn, aln, csn, cln = get_place_heirarchy(g)
        formatted_address = g['formatted_address']
        lat = g['geometry']['location']['lat']
        lng = g['geometry']['location']['lng']
    else:
        slsn, slln, lsn, lln, asn, aln, csn, cln, formatted_address, lat, lng = tuple(None for _ in range(11))
    
    return slsn, slln, lsn, lln, asn, aln, csn, cln, formatted_address, lat, lng


def get_place_heirarchy(g):
    sublocality_short_name, sublocality_long_name, \
    locality_short_name, locality_long_name, \
    admin_level_short_name, admin_level_long_name, \
    country_short_name, country_long_name = \
        None, None, None, None, None, None, None, None
    for comp in g['address_components']:
        if 'country' in comp['types']:
            country_short_name = comp['short_name']
            country_long_name = comp['long_name']
        if 'administrative_area_level_1' in comp['types']:
            admin_level_short_name = comp['short_name']
            admin_level_long_name = comp['long_name']
        if 'locality' in comp['types']:
            locality_short_name = comp['short_name']
            locality_long_name = comp['long_name']
        if 'sublocality' in comp['types']:
            sublocality_short_name = comp['short_name']
            sublocality_long_name = comp['long_name']
    return sublocality_short_name, sublocality_long_name, \
        locality_short_name, locality_long_name, \
        admin_level_short_name, admin_level_long_name, \
        country_short_name, country_long_name



# run ALL the lookups with Google maps API
info = [get_info(cc) for cc in df.combined_city_country]

columns = [
    'sublocality_short_name', 'sublocality_long_name',
    'locality_short_name', 'locality_long_name',
    'admin_level_short_name', 'admin_level_long_name',
    'country_short_name', 'country_long_name',
    'formatted_address', 'lat', 'lng'
]
gth = pd.DataFrame(info, columns=columns)
gth = pd.concat([df, gth], sort=False, axis=1)



# manual updates to results from Google
gth.loc[gth['locality_long_name']=='ADT',['locality_short_name','locality_long_name']] = 'Milagro'
gth=gth[gth.city!='Greater Perth']
gth.loc[gth['country_long_name']=='Namibia','country_short_name']='NM'
# interesting that Google comes back with a NULL country for Palestinian cities
gth.loc[gth['country']=='State of Palestine','country_short_name']='PA'


def create_abbrev_map(gth):
    grpd=gth.groupby('country_long_name').apply(lambda x:x['country_short_name'].mode())
    #|grpd.index = grpd.index.droplevel(1)
    return grpd


abbrevs = create_abbrev_map(gth)

# these can't be none in order to work with group by
gth['admin_level_short_name'].fillna('', inplace=True)
gth['admin_level_long_name'].fillna('', inplace=True)

gth['country_long_name'].fillna(gth['country'], inplace=True)

gth['country_short_name'] = [abbrevs.loc[k][0] for k in gth.country_long_name]


def manage_sub_localities(m):
    if len(m)==1:
        return m
    else:
        country = m['country'].values[0]
        city = m['locality_long_name'].values[0]
        pop = m['population'].sum()
        query = ' '.join([m['locality_long_name'].values[0], m['country_long_name'].values[0]])
        i = get_info(query)

        columns = [
            'city','country','population','combined_city_country',
            'sublocality_short_name', 'sublocality_long_name',
            'locality_short_name', 'locality_long_name',
            'admin_level_short_name', 'admin_level_long_name',
            'country_short_name', 'country_long_name',
            'formatted_address', 'lat', 'lng'
        ]
        v = tuple([city, country, pop, query] + [i for i in get_info(query)])
        return pd.DataFrame([v], index=pd.RangeIndex(1), columns=columns)


# group by locality and sum population
reduced = gth.groupby(['locality_long_name','admin_level_long_name','country_long_name']).apply(manage_sub_localities)

reduced.drop(['locality_long_name','admin_level_long_name','country_long_name'],axis=1,inplace=True)
reduced.reset_index(inplace=True)
reduced.drop('level_3',axis=1,inplace=True)

# +
# doops = reduced.groupby(['locality_long_name','admin_level_long_name','country_long_name']).count()
# doops[doops.max(axis=1) > 1]
# -

# get rid of state of
reduced.loc[[r.startswith('State of ') for r in reduced['admin_level_long_name']],'admin_level_long_name'] = [r[9:] for r in reduced['admin_level_long_name'] if r.startswith('State of ')]
reduced.loc[[r.startswith('State of ') for r in reduced['country_long_name']],'country_long_name'] = [r[9:] for r in reduced['country_long_name'] if r.startswith('State of ')]

final = reduced[['locality_long_name','admin_level_short_name','admin_level_long_name',\
                 'country_short_name','country_long_name',\
                 'population','lat','lng']].copy()
final.columns = ['city','region_abbrev','region',\
                 'country_abbrev','country',\
                 'population','lat','lng']

final.loc[final['city']=='창원시','city']='Changwon'

final.sort_values('population',inplace=True,ascending=False)

final.to_csv('final.csv', sep=',', index=False, header=True, encoding='utf8', quoting=2)

StarcoderdataJupyterScriptsDedupFiltered