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

# # Limpando dados da tabela 'paises'
# Padronizando os dados da tabela 'Faculdades' e adicionando as coordenadas geográficas para realização do heatmap.

# ## Importar biblioteca

# +
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import geopandas as gpds
from pycountry_convert import country_alpha2_to_continent_code, country_name_to_country_alpha2
from geopy.geocoders import Nominatim
import folium
from folium import plugins
from folium.plugins import MarkerCluster

import warnings
warnings.filterwarnings('ignore')
# -

# ## Abrir arquivo csv e avaliar dados

data_paises = pd.read_csv('rank_paises_6oct_2020.csv')

data_paises

# ## Usar rank como index e ordenar

data_paises.set_index('rank', inplace=True)
data_paises

data_paises.sort_index(inplace=True)
data_paises.head()


# ## Criar atributo com sigla geo

def get_continent(col):
    try:
        cn_a2_code =  country_name_to_country_alpha2(col)
    except:
        cn_a2_code = 'Unknown' 
    try:
        cn_continent = country_alpha2_to_continent_code(cn_a2_code)
    except:
        cn_continent = 'Unknown' 
    return (cn_a2_code, cn_continent)


data_paises['code geo'] = [get_continent(row) for row in data_paises['pais']]


data_paises.head(30)

# ## Tratar Unknown values

data_paises.loc[data_paises['code geo'] ==  ('Unknown', 'Unknown')]

data_paises.drop([236, 229, 221], inplace=True)

nomes = {
    'Palestinian Territories': 'Palestine',
    'United Kingdom (Great Britain)': 'United Kingdom',
    'Vatican City (Holy See)': 'Italy',
    'Croatia (hrvatska)' : 'Croatia',
    'Algeria (El Djazaïr)': 'Algeria',
    'San Marino (Republic of)': 'San Marino',
    'Serbia and Montenegro': 'Serbia',
    'Chad (T\'Chad)': 'Chad',
    'Faeroe Islands': 'Faroe Islands',
    'Congo, Republic Of' : 'Congo',
    'RÉunion':'Réunion'
}

unk = data_paises.loc[data_paises['code geo'] ==  ('Unknown', 'Unknown')]

unk

unk['pais'] = unk['pais'].map(nomes)

unk

# ## Remover dados antigos da tabela data_paises e dados com erro da tabela unk

data_paises.drop([17, 36, 69, 73, 97, 100, 108, 133, 146, 159, 167, 169, 175, 201, 232, 215], inplace=True)

unk = unk.dropna()

unk

data_paises.loc[data_paises['code geo'] ==  ('Unknown', 'Unknown')]

# ## Adicionar code geo aos países da tabela unk

unk['code geo'] = [get_continent(row) for row in unk['pais']]

unk

# ## Unir as tabelas

unir = [data_paises, unk]

data_paises_gcode = pd.concat(unir)

data_paises_gcode

# ## Remover valores duplicados

data_paises_gcode.loc[data_paises_gcode['code geo'].duplicated() == True]

data_paises_gcode.loc[data_paises_gcode['code geo'] ==   ('GB', 'EU')]

data_paises_gcode.loc[data_paises_gcode['code geo'] ==   ('IT', 'EU')]

italia_novo = 15+ 36
italia_novo

data_paises_gcode.at[58, 'resolvidos'] = 1569
data_paises_gcode.at[58, 'estudantes'] = 51

data_paises_gcode.drop([69], inplace=True)

data_paises_gcode.loc[data_paises_gcode['code geo'] ==   ('IT', 'EU')]

data_paises_gcode.at[36, 'resolvidos'] = 3224
data_paises_gcode.at[36, 'estudantes'] = 81
data_paises_gcode.at[36, 'sigla'] = 'UK'

data_paises_gcode.drop([66], inplace=True)

data_paises_gcode.loc[data_paises_gcode['code geo'] ==   ('GB', 'EU')]

data_paises_gcode.loc[data_paises_gcode['code geo'].duplicated() == True]

data_paises_gcode['pais'][51] = 'United States Virgin Islands'

# ## Sort()

data_paises_gcode.sort_index(inplace=True)

# ## Primeira etapa de tratamento finalizada

data_paises_gcode

# ##  Coletando Longitude e Latitude

geolocator = Nominatim(user_agent='test')
def geolocate(country):
    try:
        # Geolocate the center of the country
        loc = geolocator.geocode(country)
        # And return latitude and longitude
        return (loc.latitude, loc.longitude)
    except:
        # Return missing value
        return np.nan


# # Separando país de continente

data_paises_gcode['Country'] = [x[0] for x in data_paises_gcode['code geo']]

data_paises_gcode['Continent'] = [x[1] for x in data_paises_gcode['code geo']]

data_paises_gcode.drop(['code geo', 'sigla'], axis=1, inplace=True)

data_paises_gcode

# # Aplicando método geolocate para coletar a lat/long

data_paises_gcode['Geolocate'] = [geolocate(row) for row in data_paises_gcode['pais']]

data_paises_gcode

# # Separando lat/long

data_paises_gcode['Latitude'] = [x[0] for x in data_paises_gcode['Geolocate']]
data_paises_gcode['Longitude'] = [x[1] for x in data_paises_gcode['Geolocate']]

data_paises_gcode.drop(['Geolocate'], axis=1, inplace=True)

data_paises_gcode.head()

data_paises_gcode.to_csv('pais_limpo.csv')

StarcoderdataJupyterScriptsDedupFiltered