EL United States Green Building Council (USGBG) tiene una base de datos de edificios que cuentan con certificación LEED alrededor del mundo. La pagina web de USGBG cuenta con una interfaz para hacer consultas directamente a su base de datos, sin embargo no cuenta con una API o una URL directa para descarga masiva por lo que es necesario enviar el query a la base de datos desde la URL de USBG: https://www.usgbc.org/projects/list?page=17&keys=Mexico
Después de esperar a que la base de datos interprete el query, regresa el archivo "leed_projects.xls" que quedará guardado como "D:\PCCS\00_RawData\01_CSV\LEED\leed_projects.xls"
In [2]:
# Librerias utilizadas
import pandas as pd
import sys
import os
import csv
from lxml import html
import requests
import time
In [2]:
# Configuracion del sistema
print('Python {} on {}'.format(sys.version, sys.platform))
print('Pandas version: {}'.format(pd.__version__))
import platform; print('Running on {} {}'.format(platform.system(), platform.release()))
El archivo tal como se descarga, a pesar de ser tabulados de excel, envia un mensaje de error cuando se intenta abrir directamente como se descargó. Por lo tanto, antes de procesarlo es necesario abrirlo en excel y guardarlo con formato .xlsx
In [4]:
path = r'D:\PCCS\00_RawData\01_CSV\LEED\leed_projects.xlsx'
raw_data = pd.read_excel(path)
raw_data.index.name = 'Building'
raw_data.head()
Out[4]:
In [5]:
# Eliminar columnas que no pertenecen a Mexico
print('La tabla tiene {} registros'.format(len(raw_data)))
x = 'United States [us]'
raw_data = raw_data[raw_data['Country'] != x]
print('Quitando los registros donde el país es "{}", la tabla queda con {} registros'.format(x, len(raw_data)))
x = 'Colombia'
raw_data = raw_data[raw_data['Country'] != x]
print('Quitando los registros donde el país es "{}", la tabla queda con {} registros'.format(x, len(raw_data)))
x = 'United States'
raw_data = raw_data[raw_data['Country'] != x]
print('Quitando los registros donde el país es "{}", la tabla queda con {} registros'.format(x, len(raw_data)))
raw_data.head()
Out[5]:
La base de datos es un listado de edificios que incluye para cada edificio:
Debido a que las columnas de Ciudad, estado y país no están realizadas bajo ningun estándar, es necesario asignar a cada renglón las claves geoestadísticas municipales de 5 dígitos correspondientes al municipio en el que se ubica el edificio. Esto se hará manualmente pues cada renglón tiene que ser interpretado individualmente.
Durante la revision me di cuenta que si bien la tabla no tiene una clave para identificar cada ciudad y municipio, la liga de cada edificio nos lleva a una ficha del municipio que usualmente sí contiene un código postal; y desde el código postal es posible obtener el municipio y el estado. A continuacion se hace la revision de una pagina para conocer su estructura y hacer un webscrapping desde esta estructura, esperando que sea igual en todas las fichas:
In [6]:
# Descarga el HTML de la pagina
page = requests.get('https://www.usgbc.org/projects/reforma-180')
tree = html.fromstring(page.content)
In [7]:
# Obten variables desde la estructura
street = tree.xpath('//span[@itemprop="streetAddress"]/text()')
locality = tree.xpath('//span[@itemprop="addressLocality"]/text()')
postalcode = tree.xpath('//span[@itemprop="postalCode"]/text()')
country = tree.xpath('//span[@itemprop="addressCountry"]/text()')
In [8]:
''.join(street).replace('\n', '')
Out[8]:
In [9]:
# A ver, que datos sacaste?
print('len({}), type({}) - {}'.format(len(street), type(street), street))
print('len({}), type({}) - {}'.format(len(locality), type(locality), locality))
print('len({}), type({}) - {}'.format(len(postalcode), type(postalcode), postalcode))
print('len({}), type({}) - {}'.format(len(country), type(country), country))
Todos los datos son listas, pero "street" tiene 2 elementos. Entonces para el script lo que voy a hacer será eliminar todos los saltos de linea y concatenar el texto de todos los elementos de la lista
In [10]:
# Script para extraer datos de fichas a partir de la URL
def webcrawler(x):
time.sleep(0.05)
url = x
try:
page = requests.get(x)
tree = html.fromstring(page.content)
except: # Regresa false si no logras entrar a la URL
street = False
locality = False
postalcode = False
country = False
return [street, locality, postalcode, country]
# Saca los datos del tree. Regresa None si no encontraste
try:
street = ''.join(tree.xpath('//span[@itemprop="streetAddress"]/text()'))
except:
street = None
try:
locality = tree.xpath('//span[@itemprop="addressLocality"]/text()')
except:
locality = None
try:
postalcode = tree.xpath('//span[@itemprop="postalCode"]/text()')
except:
postalcode = None
try:
country = tree.xpath('//span[@itemprop="addressCountry"]/text()')
except:
country = None
return [street, locality, postalcode, country]
In [11]:
# Pon al crawler a hacer su chamba (Pero no si el archivo ya existe)
archivoraw = r'D:\PCCS\00_RawData\01_CSV\LEED\crawl_leed.xlsx'
if os.path.isfile(archivoraw):
print('NO SE REALIZÓ EL WEBCRAWL PORQUE YA SE TIENEN LOS DATOS EN \n {}'.format(archivoraw))
print('*** Mejor importa el archivo para no gastar tantos recursos ***')
else:
raw_data['crawl'] = raw_data.Path.apply(webcrawler)
Reemplaza los enters en cada lista
(Voy a saltarme este paso porque lo que me interesa en realidad es el Codigo Postal, pero dejo el codigo por si lo ocupo en el futuro)
def listtotext(x): templist = [] for element in x: if element == None or element == False: templist.append(element) else: templist.append(''.join(x).replace('\n', '')) return templist
In [244]:
raw_data.head()
Out[244]:
In [496]:
# Guarda una copia de raw_data por si es necesario ocupar este dataset de nuevo,
# que no se tenga que hacer nuevamente el webcrawiling porque consume mucho tiempo
writer = pd.ExcelWriter(archivoraw)
raw_data.to_excel(writer, sheet_name = 'DATOS')
writer.save()
In [470]:
# Crea una copia de trabajo de raw_data
datasetfinal = raw_data
In [471]:
# Crea una columna única con los datos de dirección y código postal extraídos con el crawler.
datasetfinal['address'] = datasetfinal.crawl.apply(lambda x: x[0].replace('\n', ''))
# raw_data['city'] = raw_data.crawl.apply(lambda x: x[1][0].replace('/n', ''))
datasetfinal['CP'] = datasetfinal.crawl.apply(lambda x: str(x[2][0]))
# raw_data['city'] = raw_data.crawl.apply(lambda x: x[3][0].replace('/n', ''))
datasetfinal.head(2)
Out[471]:
A partir de los Codigos Postales ya es posible identificar la ciudad y municipio a la que pertenece cada edificio. Para esto, vamos a utilizar la base de datos de codigos postales del SEPOMEX que se descargó en otra minería de datos:
In [472]:
bd_sepo = r'D:\PCCS\01_Dmine\Datasets\SEPOMEX\sepomex_CP_CVEMUN.xlsx'
SEPOMEX = pd.read_excel(bd_sepo, dtype={'CVE_MUN':'str', 'CP':'str'})
SEPOMEX.head(3)
Out[472]:
Con la base de datos del SEPOMEX ya es posible unir ambos datasets para obtener las claves municipales de cada edificio
In [473]:
datasetfinal.head()
Out[473]:
In [474]:
# Copiar CVE_MUN del dataset en base al codigo postal
datasetfinal = datasetfinal.reset_index().merge(SEPOMEX, on='CP', how='left').set_index('Building')
datasetfinal.head()
Out[474]:
Quedan 70 filas en donde no fue posible identificar la clave Municipal
In [475]:
len(datasetfinal[datasetfinal['CVE_MUN'].isnull()])
Out[475]:
In [476]:
mira = ['City', 'State', 'CP', 'address', 'CVE_MUN'] # El diccionario 'mira' se utilizará en adelante para imprimir subsets de la informacion
sinmun = datasetfinal[datasetfinal['CVE_MUN'].isnull()][mira]
sinmun.head()
Out[476]:
In [477]:
len(sinmun['CP'].unique())
Out[477]:
En el siguiente diccionario recopila las CVE_MUN que se asignarán a los códigos postales que requieren asignacion individual. Los códigos cuyo valor es None se asignarán mas adelante
In [478]:
# Diccionario creado en donde key = 'CP' y value = 'CVE_MUN'
defmuns = {'00000': None,
'00100': '09010',
'00502': '15024',
'00604': '15121',
'00702': '15051',
'01006': '09010',
'01152': '09010',
'01209': '09004',
'01300': '09004',
'03130': '09014',
'03210': '09014',
'05300': '09004',
'05490': '15104',
'05940': '15013',
'08424': '14094',
'11010': '09016',
'11111': '14098',
'11570': '09016',
'12345': None,
'21118': '02002',
'22320': '02004',
'23410': '03008',
'23479': '03008',
'31240': '08019',
'46685': '14006',
'48219': '16053',
'56277': '15099',
'66601': '19006',
'67114': '19026',
'76232': '22014',
'77780': '23009',
'78341': '24028',
'87131': None}
El siguiente diccionario incluye códigos postales que requieren ser corregidos
In [479]:
# Diccionario en donde key = Codigo postal listado en el dataset; value = Codigo postal correcto
deberiaser = {'00100': '45620',
'00502': '54830',
'00604': '54713',
'00702': '52004',
'03130': '03103',
'11111': '45620',
'48219': '58218'}
In [480]:
# Reemplazar las CVE_MUN identificadas en el dataset final
datasetfinal['CVE_MUN'] = datasetfinal['CP'].map(defmuns).fillna(datasetfinal['CVE_MUN'])
Algunos edificios, marcados con los codigos postales 00000 y 12345 (Intuyo que por desidia del capturista) se tendrán que asignar individualmente
In [481]:
sinmun.loc[sinmun['CP'].isin(['00000', '12345'])]
Out[481]:
In [482]:
# Diccionario con edificios que se asignaran individualmente
# Para este diccionario key = Nombre del edificio, value = CVE_MUN que se asignará a este edificio
buildings = {
'Grainger Mexico HQ': '19039',
'La Concha Pearl': '03003',
#'Schneider Electric at COK': '66629', # Este edificio esta repetido, por lo que no se le asignará nada y se eliminará al final
'Bank of America-Reforma 115 5th floor': '09016',
'Vesta Corporate Headquarters': '09016',
'Air Traffic Control Tower': '15101', # Estoy considerando que esta es la Torre de Control del NAICM
'Passenger Terminal Building': '15101', # El edificio del NAICM
'Area Control Center': '15101', # Infraestructura del NAICM
'Corporativo TRIO': '09004',
'Casa GF': '19019',
'Eurocenter 2': '09004',
'ROUZ TOWER': '09014',
'Periferico Sur Parque Industrial': '14098'
}
In [483]:
# Hay un edificio duplicado. El duplicado se eliminará mas adelante
datasetfinal.loc['Schneider Electric at COK'][mira]
Out[483]:
In [484]:
# Reemplazar valores individuales en el dataset.
for k, v in buildings.items():
building = datasetfinal.loc[k].name
CVEMUN_prev = datasetfinal.loc[k]['CVE_MUN']
datasetfinal.at[k, 'CVE_MUN'] = v
print('Edificio:{} - la CVE_MUN {} se reemplazó por {}'.format(building, CVEMUN_prev, datasetfinal.at[k, 'CVE_MUN']))
El dataset contiene dos edificios en el dataset que no corresponden a México:
In [485]:
sinmun[sinmun['CP'] == '87131']
Out[485]:
Se eliminarán del dataset los siguientes edificios:
In [486]:
datasetfinal[datasetfinal['CVE_MUN'].isnull()][mira]
Out[486]:
El primero por estar repetido y el resto por que no están en los Estados Unidos Mexicanos.
In [487]:
datasetfinal = datasetfinal.dropna(subset=['CVE_MUN'])
datasetfinal.head(3)
Out[487]:
Los edificios que requieren correccion de codigos postales son los siguientes:
In [488]:
datasetfinal[datasetfinal['CP'].isin(list(deberiaser.keys()))][mira]
Out[488]:
In [489]:
# Corregir codigos postales erróneos
datasetfinal['CP'] = datasetfinal['CP'].map(deberiaser).fillna(datasetfinal['CP'])
datasetfinal[mira].head()
Out[489]:
In [490]:
# Renombrar columnas para crear variables únicas
columns={
'address':'direccion',
'Path': 'URL',
'Certification date': 'usgbc_fecha_cert',
'Rating system':'usgbc_sis_val',
'Version': 'usgbc_ver_sisv',
'Certification level': 'usgbc_nv_cert',
}
datasetfinal = datasetfinal.rename(columns=columns)
datasetfinal.head(2)
Out[490]:
In [491]:
# Descripciones de columnas
variables = {
'direccion': 'Ubicacion (Calle y numero)',
'CVE_MUN': 'Clave geoestadística de 5 digitos a nivel municipal, de acuerdo con el Catálogo Único de Claves de Áreas Geoestadísticas Estatales, Municipales y Localidades de INEGI',
'usgbc_fecha_cert': 'Fecha de certificacion como edificio LEED por el United States Green Building Council',
'usgbc_sis_val': 'Sistema de valoracion aplicado por el United States Green Building Council al edificio',
'usgbc_ver_sisv': 'Version del Sistema de valoracion aplicado por el United States Green Building Council al edificio',
'usgbc_nv_cert': 'Nivel de certificacion como edificio LEED alcanzado por el edificio',
'CP': 'Codigo Postal',
'URL': 'Uniform Resource Locator, referencia a recurso en línea'
}
# Convertir descripciones a dataframe
variables = pd.DataFrame.from_dict(variables, orient='index', dtype=None)
variables.columns = ['Descripcion']
variables = variables.rename_axis('Mnemonico')
variables.head()
Out[491]:
In [492]:
# Eliminar columnas que ya no se utilizarán y reordenar
setfinal = [
'direccion',
'CVE_MUN',
'usgbc_fecha_cert',
'usgbc_sis_val',
'usgbc_ver_sisv',
'usgbc_nv_cert',
'CP',
'URL']
datasetfinal = datasetfinal[setfinal]
datasetfinal.head()
Out[492]:
In [496]:
metadatos = {
'Nombre del Dataset': 'Edificios con Certificación LEED',
'Descripcion del dataset': 'Edificios que han recibido algún nivel de certificación de Liderazgo en Energía y desarrollo Ambiental' \
' (LEED, por sus siglas en ingles) Otorgado por el Consejo de edificios Verdes de Estados Unidos (USGBC' \
' por sus suglas en inglés)',
'Disponibilidad Temporal': '2007 - 2018',
'Periodo de actualizacion': 'No Definido',
'Nivel de Desagregacion': 'Edificio',
'Notas': 's/n',
'Fuente': 'United States Green Buildings Council',
'URL_Fuente': 'https://www.usgbc.org/projects/list?page=17&keys=Mexico',
'Dataset base': None
}
# Metadatos a dataframe para exportar
metadatos = pd.DataFrame.from_dict(metadatos, orient='index', dtype=None)
metadatos.columns = ['Descripcion']
metadatos = metadatos.rename_axis('Metadato')
metadatos
Out[496]:
In [497]:
# Guardar el dataset
file = r'D:\PCCS\01_Dmine\Datasets\LEED\PCCS_leed_projects.xlsx'
writer = pd.ExcelWriter(file)
datasetfinal.to_excel(writer, sheet_name = 'DATOS')
metadatos.to_excel(writer, sheet_name = 'METADATOS')
variables.to_excel(writer, sheet_name = 'VARIABLES')
writer.save()
print('---------------TERMINADO---------------')