
import pandas as pd
import requests
from bs4 import BeautifulSoup
from IPython import display
import datetime as dt
pd.options.display.max_columns = 100

def get_listado():
    """
    Descarga la lista de unidades educativas y sus atributos básicos
    desde el servidor geoserver.
    """
    
    url = 'http://seie.minedu.gob.bo:8080/geoserver/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=minedu:vw_unidad_geo7&outputFormat=json'
    r = requests.get(url)
    return pd.DataFrame([feature['properties'] for feature in r.json()['features']])


def extract_datosgenerales(html):
    """
    Extrae los atributos en "Datos Generales"
    de la ficha de una unidad
    """
    
    dd = html.select('.dl-horizontal dd')
    return dict(
        director = dd[0].get_text().strip(),
        direccion = dd[1].get_text().strip(),
        telefono = dd[2].get_text().strip(),
        dependencia = dd[3].get_text().strip(),
        nivel = dd[4].get_text().strip(),
        turno = dd[5].get_text().strip()
    )


def extract_tabla(html, selector, selector_orden, tabla_name):
    """
    Extrae valores de la tabla que se encuentra en 
    el selector css `selector` en el orden `selector_orden`
    y aplica el nombre `tabla_name` al inicio de cada atributo
    para evitar colisiones
    """
    
    tabla = pd.read_html(str(html.select(selector)[selector_orden]))[0]
    tabla = tabla.set_index('Sexo').unstack()
    tabla.index = tabla.index.map(lambda x: '{}_{}_{}'.format(tabla_name, x[1], x[0]))
    return tabla.to_dict()


def extract_infraestructura(html):
    """
    Extrae atributos de infraestructura 
    de la ficha de una unidad
    """
    
    boxes = html.select('.box .info-box-content h3')
    return dict(
        agua = boxes[0].get_text().strip(),
        energia_electrica = boxes[1].get_text().strip(),
        baterias_de_bano = boxes[2].get_text().strip(),
        internet = boxes[3].get_text().strip(),
    )


def extract_listado(html, selector, selector_orden, listado_nombre):
    """
    Extrae valores de un listado no-ordenado cuyo
    selector css es `selector` y cuyo orden es `selector_orden`
    y aplica el nombre `listado_nombre` al inicio de cada
    atributo para evitar colisiones. Si un atributo no tiene nombre,
    su nombre será `listado_nombre`.
    """
    
    lista = html.select(selector)[selector_orden]
    return {'{}_{}'.format(listado_nombre, li.contents[0].get_text().strip().replace(' ', '_')) if len(li.contents) > 1 else listado_nombre:li.contents[-1].get_text().strip() for li in  lista.select('li')}


def hydrate_unidad(cod_ue):
    """
    Extrae información de la ficha de una unidad educativa
    cuyo código es `cod_ue`. Retorna un diccionario.
    """
    
    url = "http://seie.minedu.gob.bo/reportes/mapas_unidades_educativas/ficha/ver/{}".format(cod_ue.strip())
    r = requests.get(url)
    html = BeautifulSoup(r.text, 'html.parser')
    
    datosgenerales = extract_datosgenerales(html)
    matricula = extract_tabla(html, '#data-table-sexo', 0, 'matricula')
    promovidos = extract_tabla(html, '#data-table-sexo', 1, 'estudiantes_promovidos')
    reprobados = extract_tabla(html, '#data-table-sexo', 2, 'estudiantes_reprobados')
    abandono = extract_tabla(html, '#data-table-sexo', 3, 'estudiantes_abandono')
    infraestructura = extract_infraestructura(html)
    ambientes_pedagogicos = extract_listado(html, "ul.tama", 0, "ambientes_pedagogicos")
    ambientes_deportivos = extract_listado(html, "ul.tama", 1, "ambientes_deportivos")
    ambientes_administrativos = extract_listado(html, "ul.tama", 2, "ambientes_administrativos")
    bachillerato_humanistico = extract_listado(html, "ul.tama", 3, "bachillerato_humanistico")
    viviendas_maestros = extract_listado(html, "ul.tama", 4, "viviendas_maestros")
    
    return {
        **datosgenerales, **matricula, **promovidos, **reprobados, **abandono, **infraestructura, **ambientes_pedagogicos, **ambientes_deportivos, **ambientes_administrativos, **bachillerato_humanistico, **viviendas_maestros
    }


def hydrate_unidades(ue, offset=0):
    """
    Extrae información de todas las unidades educativas
    en el dataframe `ue` desde la fila `offset`, y la 
    almacena como diccionarios en la lista `unidades`
    definida en el scope global
    """
    total = len(ue)
    for i, row in ue.iloc[offset:].iterrows():
        display.clear_output(wait=True)
        print('{}/{}'.format(i+1+offset, total))
        unidades.append({**row.to_dict(), **hydrate_unidad(row['cod_ue'])})

def format_udf_estado(udf):
    """
    Construye un dataframe con datos generales sin una estampa de tiempo
    para cada unidad educativa
    """
    
    udf_estado = udf[[col for col in udf.columns if '20' not in col]]
    udf_estado.columns = ['geoserver_id', 'departamento_codigo', 'departamento', 'provincia_codigo', 'provincia', 'municipio_codigo', 'municipio', 'distrito_educativo_codigo', 'distrito_educativo', 'cod_le', 'codigo_rue'] + list(udf_estado.columns[11:])
    udf_estado.columns = [col.lower().replace('nº', 'numero').replace(':', '').strip() for col in udf_estado.columns]
    udf_estado = udf_estado[['codigo_rue'] + [col for col in udf_estado.columns if col != 'codigo_rue']]

    ints = ['codigo_rue', 'geoserver_id', 'departamento_codigo', 'provincia_codigo', 'municipio_codigo', 'distrito_educativo_codigo', 'turnoals', 'depend', 'ambientes_pedagogicos_numero_de_aulas', 'ambientes_pedagogicos_numero_de_laboratorios', 'ambientes_pedagogicos_numero_de_bibliotecas','ambientes_pedagogicos_numero_de_salas_de_computación', 'ambientes_deportivos_numero_de_canchas', "ambientes_deportivos_numero_de_gimnasios", "ambientes_deportivos_numero_de_coliseos", "ambientes_deportivos_numero_de_piscinas", "bachillerato_humanistico_numero_de_talleres"]
    floats = ['latitud', 'longitud']
    booleans = ["viviendas_maestros", "agua", "energia_electrica", 'baterias_de_bano', 'internet', 'ambientes_administrativos_dirección', "ambientes_administrativos_secretaría", "ambientes_administrativos_sala_de_reuniones"]

    for col in ints:
        udf_estado[col] = pd.to_numeric(udf_estado[col].astype(str).str.replace('--', '0'))
    for col in floats:
        udf_estado[col] = pd.to_numeric(udf_estado[col])
    for col in booleans:
        udf_estado[col] = udf_estado[col].map({'SI': True, '--':False})
        
    udf_estado = udf_estado.set_index('codigo_rue')
    
    return udf_estado

def format_udf_time(udf):
    """
    Construye un dataframe con series de tiempo de cada unidad educativa
    """

    dfs = []
    udf_time = udf[['cod_ue'] + [col for col in udf.columns if '20' in col]]
    for col in udf_time.columns[1:]:
        splits = col.split('_')
        variable = '_'.join(splits[:-1]).lower()
        year = int(splits[-1])
        dfi = udf_time[['cod_ue', col]].rename(columns={col:'valor'})
        dfi.insert(1, 'variable', variable)
        dfi.insert(2, 'year', year)
        dfs.append(dfi)

    udf_time = pd.concat(dfs)
    udf_time = udf_time.rename(columns={"cod_ue":"codigo_rue"})
    udf_time = udf_time.dropna()
    for col in ['codigo_rue', 'year', 'valor']:
        udf_time[col] = udf_time[col].astype(int)
        
    return udf_time


display.display(display.Markdown('En este proyecto construyo y comparto una base de datos de características de unidades educativas en Bolivia. Los datos fueron extraídos del [Sistema de Estadísticas e Indicadores Educativos](http://seie.minedu.gob.bo/reportes/mapas_unidades_educativas), limpiados y publicados el {}'.format(dt.datetime.now().strftime('%Y-%m-%d'))))


# Descarga datos para cada unidad educativa

ue = get_listado()
unidades = []
hydrate_unidades(ue)


# Construye tablas cómodas de utilizar

udf = pd.DataFrame(unidades)
udf_estado = format_udf_estado(udf)
udf_time = format_udf_time(udf)

udf.to_csv('data/unidades_educativas_raw.csv', index=False)
udf_estado.to_csv('data/unidades_educativas_estado.csv')
udf_time.to_csv('data/unidades_educativas_tiempo.csv', float_format="%.0f")


def link_file(filename):
    return '[{0}](https://github.com/mauforonda/unidades_educativas_bolivia/blob/master/data/{0})'.format(filename)

display.display(display.Markdown('Existen datos para {} unidades educativas en {} departamentos, {} provincias, {} municipios y {} distritos educativos diferentes. {} unidades están ubicadas en el área rural y {} en el área urbana. Además existen series de tiempo del número de estudiantes que en algunos casos vienen desde {} hasta {}. Entre los atributos para cada unidad, existen aquellos publicados como parte de una serie de tiempos, por ejemplo número de estudiantes matriculados en un año, y atributos más estables como la ubicación o características de la infraestructura. Para compartir estos datos, construyo una tabla distinta para cada uno de estos dos tipos de atributos. Series de tiempo son compartidas en {}, que describe en cada fila el `valor` de una `variable` en una unidad educativa con un `codigo_rue` y en un `year` específico. Una muestra de 5 filas escogidas aleatóriamente se ve así:'.format(
    len(udf_estado), 
    len(udf_estado.departamento_codigo.unique()), 
    len(udf_estado.provincia_codigo.unique()),
    len(udf_estado.municipio_codigo.unique()),
    len(udf_estado.distrito_educativo_codigo.unique()),
    len(udf_estado[udf_estado.area == 'R']),
    len(udf_estado[udf_estado.area == 'U']),
    udf_time.year.min(),
    udf_time.year.max(),
    link_file('unidades_educativas_tiempo.csv')
)))
display.display(udf_time.sample(5))
display.display(display.Markdown('Y para atributos más estables, comparto {} que describe todo tipo de características geográficas y de infraestuctura para cada unidad educativa. Una muestra de 5 filas se ve así:'.format(
    link_file('unidades_educativas_estado.csv')
)))
display.display(udf_estado.sample(5))
display.display(display.Markdown('Finalmente comparto {} que describe cada atributo antes de ser limpiado y ordenado de la forma que lo ofrece el sistema.'.format(
    link_file('unidades_educativas_raw.csv')
)))

	codigo_rue	variable	year	valor
8298	80600054	estudiantes_abandono_total	2019	0
14829	81980883	estudiantes_abandono_total	2016	8
7698	80480080	estudiantes_reprobados_mujer	2015	47
10783	80980215	estudiantes_promovidos_hombre	2019	238
9461	80730339	estudiantes_promovidos_mujer	2017	219

	geoserver_id	departamento_codigo	departamento	provincia_codigo	provincia	municipio_codigo	municipio	distrito_educativo_codigo	distrito_educativo	cod_le	turnoals	area	depend	nivel	des_ue	latitud	longitud	director	direccion	telefono	dependencia	turno	agua	energia_electrica	baterias_de_bano	internet	ambientes_pedagogicos_numero_de_aulas	ambientes_pedagogicos_numero_de_laboratorios	ambientes_pedagogicos_numero_de_bibliotecas	ambientes_pedagogicos_numero_de_salas_de_computación	ambientes_deportivos_numero_de_canchas	ambientes_deportivos_numero_de_gimnasios	ambientes_deportivos_numero_de_coliseos	ambientes_deportivos_numero_de_piscinas	ambientes_administrativos_dirección	ambientes_administrativos_secretaría	ambientes_administrativos_sala_de_reuniones	bachillerato_humanistico_numero_de_talleres	viviendas_maestros
codigo_rue
81230127	51196	4	ORURO	401	CERCADO	40101	CAPITAL (ORURO)	4001	ORURO	81230095	1	U	1	Inicial/Primaria	JACINTO RODRIGUEZ DE HERRERA	-17.912962	-67.128278	GOMEZ SANCHEZ MARIA ELENA	URBANIZACION LA AURORA	5240732 / 76147150	FISCAL	--	True	True	False	True	11	0	0	1	1	0	0	0	True	True	True	0	True
61460020	43084	5	POTOSI	503	CORNELIO SAAVEDRA	50303	TACOBAMBA	5010	TACOBAMBA	61460020	4	R	1	Primaria	CRUZ PAMPA	-19.183300	-65.643082	CASTILLO CONDORI PRIMO	CRUZ PAMPA	73861811 / 69616115	FISCAL	--	True	True	True	False	1	0	0	0	0	0	0	0	True	False	False	0	True
82190081	55177	8	BENI	805	MOXOS	80501	SAN IGNACIO	8010	SAN IGNACIO	82190080	1	R	1	Inicial/Primaria/Secundaria	CIPRIANO BARACE	-15.098684	-65.698586	SIRPA CONDORI VERONICA	SANTISIMA TRINIDAD	77467845 / 72837102	FISCAL	--	False	False	False	False	7	0	0	0	1	0	0	0	True	False	False	0	True
40630096	40338	2	LA PAZ	211	SUD YUNGAS	21105	LA ASUNTA	2044	LA ASUNTA	40630094	1	R	1	Inicial/Primaria	ALTO BALLIVIAN	-16.274401	-67.302776	USCAMAYTA ARGANI LIDYA	SINDICATO ALTO BALLIVIAN	73262450 / S/N	FISCAL	--	True	False	True	False	1	0	0	0	1	0	0	0	False	False	False	0	True
61890108	43460	7	SANTA CRUZ	710	OBISPO SANTIESTEVAN	71003	MINEROS	7036	MINEROS	61890070	1	U	0	Inicial/Primaria	ADVENTISTA MINERO	-17.119262	-63.234655	SIN DATO	CALLE MARCELIANO MONTERO	68921403 / 39246211	PRIVADO	--	True	True	False	False	2	0	0	0	1	0	0	0	True	False	False	0	True

Datos de Unidades Educativas en Bolivia¶