Trabajando con archivos de Excel complejos en Pandas
Cómo leer archivos de Excel con estructuras complicadas usando Pandas
Elaborado por Juan Javier Santos Ochoa (@jjsantoso)
Pandas es la biblioteca por excelencia para trabajar con datos tabulares en Python. Muchos de estos datos, especialmente los que vienen de instituciones públicas, están en formato de Excel, que en ocasiones pueden ser particularmente difíciles de leer por su estructura con celdas combinadas. En esta entrada veremos cómo usar algunas de las opciones y trucos de Pandas para leer estos archivos complejos de forma efectiva.
Es necesario tener instaladas las bibliotecas pandas y xlrd.
Como ejemplo usaremos los tabulados del mercado laboral que publica el INEGI para México. Estos datos se pueden descargar desde su página: https://www.inegi.org.mx/programas/enoe/15ymas/default.html#Tabulados . En particular, usaremos los tabulados del primer trimestre de 2020 para el estado de Aguascalientes. Acá puedes decargar el archivo que uso en este notebook y acá uno con todos los estados.
El archivo luce de la siguiente forma visto en Excel.
Encabezado:
Final del archivo:
Podemos ver que la estructura de los datos es bastante compleja. Algunos de los desafíos de este archivo son:
- El encabezado de la tabla de datos no empieza desde la primera fila, sino a partir de la fila 6
- El encabezado está conformado por 3 filas (la 6, 7 y 8) con diferentes niveles de información. Por ejemplo, el primer nivel contiene los valores "Enero-Marzo 2020", "Coeficientes de Variación (%)", "Errores Estándar" e "Intervalos de Confianza al 90%". El segundo nivel solo contiene valores para las columnas que están bajo "Intervalos de Confianza al 90%". El tercer nivel es la desagregación por sexo, junto con el total, excepto en la columna "Intervalos de Confianza al 90%", donde representa los límites inferiores y superiores.
- Hay hasta 4 niveles de desagregación de los indicadores, que vienen especificados en las columnas A, B, C y D. Estos niveles expresan jerarquía entre las categorías. Por ejemplo, la celda C14 hace referencia a la población desocupada, que hace parte de la PEA (B12) y de la población de 15 años y más (A11)
- Después que la tabla de datos termina, hay un montón de notas al pie y comentarios en las celdas siguientes, que pueden ser entendidas como datos.
- Las variables de las que nos interesa obtener información están como filas, cuando quisiéramos que fueran columnas.
Queda muy claro que estos datos distan mucho de tener una estructura Tidy.
En ocasiones ante una estructura tan compleja, lo más fácil es hacer manualmente los cambios necesarios para que la tabla quede en un formato mucho más entendible para nuestro programa que va a leer los datos. Esto es válido cuando solo hay que modificar uno o pocos archivos, pero si se trata de un proceso que se tiene que aplicar para muchos archivos o se va a estar haciendo de manera recurrente tenemos que pensar en una forma de automatizar el preprocesamiento.
Afortunadamente Pandas cuenta con características que nos ayudan mucho con este tipo de archivos que tienen múltiples niveles en las filas y en las columnas. Esto coincide bastante bien con las características de Multindex y Multicolumn de los dataframes.
Empecemos importando los datos y viendo cómo lucen si los cargamos tal cual como vienen.
import pandas as pd
df = pd.read_excel('datos/2020_trim_1_Entidad_Aguascalientes.xls')
df.head()
Podemos hacer muy poco con esto, no hay nombres en las columnas y hay valores nulos por todos lados. Veamos la parte final del dataframe, donde tenemos todas las notas al pie, que realmente no es información que necesitamos para el análisis.
df.tail(7)
Para empezar a arreglar un poco las cosas, usemos las opción header que nos permite especificar cuáles son las filas que son el encabezado de los datos. Si tuvieran un formato tidy entonces solo necesitaríamos una fila como header, pero en este caso tenemos 3 niveles para el header. Pandas nos permite especificar varios niveles si a la opción header le pasamos una lista con los número de las filas (contando desde 0).
df = pd.read_excel('datos/2020_trim_1_Entidad_Aguascalientes.xls', header=[5, 6, 7])
df.head(3)
El resultado que obtenemos es un dataframe con 3 niveles de columnas (Multicolumn). Ahora al menos los datos empiezan donde deberían. Hagamos también que la tabla termine donde debe terminar. Para eso usamos la opción skipfooter a la que le especificamos el número de filas que debe ignorar partiendo desde la última hacia arriba. En este caso son 16 filas con contenido que no nos interesa.
df = pd.read_excel('datos/2020_trim_1_Entidad_Aguascalientes.xls', header=[5, 6, 7], skipfooter=16)
df.tail(3)
Ahora el dataframe termina donde está el último indicador, que es "Tasa de informalidad laboral 2 (TIL2)".
Ahora continuaremos con la opción index_col que le permite a Pandas entender que las primeras 4 columnas serán el índice del dataframe. Lo mejor es que además entiende la estructura jerárquica que está implícita en las celdas combinadas.
df = pd.read_excel('datos/2020_trim_1_Entidad_Aguascalientes.xls', header=[5, 6, 7], skipfooter=16, index_col=[0, 1, 2, 3])
df.tail()
En este caso nos interesa obtener las estimaciones de los indicadores y no los coeficientes de variación y los otros cálculos. Estos datos están bajo la columna "Enero - Marzo 2020", así que ya que tenemos encabezados es fácil obtenerlos.
df['Enero - Marzo 2020'].head()
Queda sobrando un nivel que en realidad no necesitamos porque no agrega nada de información ['Unnamed: 4_level_1', 'Unnamed: 5_level_1', 'Unnamed: 6_level_1']. Este lo podemos eliminar con el método .droplevel()
valores = df['Enero - Marzo 2020'].droplevel(level=0, axis=1)
valores.head()
Ya tenemos un resultado bastante útil. Todavía nos quedan algunos ajustes que hacer. Primero, hay que eliminar las filas que solo contienen valores nulos
valores = valores.dropna(subset=['Total', 'Hombres', 'Mujeres'])
valores.head()
Ahora pongamos nombres a los niveles para que sea fácil identificarlos. También cambiemos los NaN que hay en el índice por un valor de texto, como por ejemplo "Total". Esto ayuda porque pandas no maneja muy bien valores nulos en el índice.
valores = valores.rename_axis(index=['nivel_1', 'nivel_2', 'nivel_3', 'nivel_4'], columns=['sexo'])\
.rename(lambda x: 'Total' if pd.isna(x) else x)
valores.head()
Y bueno, ya con esto prácticamente podemos obtener el valor de cualquiera de los indicadores. Por ejemplo, si queremos la "Población económicamente activa (PEA)"
pea = valores.loc[('2. Población de 15 años y más', 'Población económicamente activa (PEA)')]
pea
En este resultado el nivel_4 es innecesario, así que lo podemos eliminar. En general, podemos eliminar cualquier nivel que no aporte información para quedarnos con una estructura más sencilla. Además modificamos la estructura para que sea tidy y cada columna sea una variable
pea.droplevel(1)\
.T\
.add_prefix('poblacion_')\
.reset_index()
Podemos intentar con otro indicador como la Tasa de informalidad laboral 1 (TIL1), haciendo algunas otras modificaciones:
valores.loc[('10. Tasas', 'Tasas calculadas contra la población ocupada', 'Tasa de informalidad laboral 1 (TIL1)')]\
.unstack('sexo')\
.to_frame('til_1')\
.droplevel('nivel_4')\
.reset_index()
Podemos convertir este procedimiento en una función para que podamos obtener estas variables para cualquier estado. Por ejemplo, para obtener la población económicamente activa creamos esta función que depende solo del nombre del estado (como aparece en el archivo de INEGI que descargué)
def obtiene_pea(edo: str):
df = pd.read_excel(f'datos/2020_trim_1_Entidad_{edo}.xls', header=[5, 6, 7], skipfooter=16, index_col=[0, 1, 2, 3])
pea = df['Enero - Marzo 2020'].droplevel(level=0, axis=1)\
.dropna(subset=['Total', 'Hombres', 'Mujeres'])\
.rename_axis(index=['nivel_1', 'nivel_2', 'nivel_3', 'nivel_4'], columns=['sexo'])\
.rename(lambda x: 'Total' if pd.isna(x) else x)\
.sort_index()\
.loc[('2. Población de 15 años y más', 'Población económicamente activa (PEA)')]\
.droplevel(1)\
.T\
.add_prefix('poblacion_')\
.reset_index()\
.assign(estado=edo.replace('_', ' '))
return pea
Probamos la función en otro estado y nos da el resultado esperado:
obtiene_pea('Oaxaca')
Acá por ejemplo, obtenemos la pea para los estados del Sur-Sureste mexicano:
pea_sur = pd.concat([obtiene_pea(e) for e in ['Oaxaca', 'Chiapas', 'Tabasco', 'Campeche', 'Quintana_Roo', 'Yucatán']], ignore_index=True)
pea_sur
De esta manera logré obtener los indicadores que necesitaba de un archivo que parecía imposible de aprovechar en su estado original.
Lo que más quería destacar en esta entrada es que Pandas, con su estructura de multindex, puede facilitar mucho leer archivos de Excel cuya estructura incluye celdas combinadas y anidadas. No hay garantía de que siempra se pueda leer adecuadamente archivos de Excel muy complejos, pero es bueno saber que tampoco está todo perdido si llega a tus manos uno de estos mosntruos.
Revisa otras entradas de este blog:
- Integración de PyQGIS con Jupyter Lab usando Anaconda
- Introducción a PyQGIS (Python + QGIS)
- Mapas de puntos con Python
- Introducción a bases de datos relacionales y SQL para científicos sociales
- Recuadros para mapas en Geopandas
- Etiquetado de variables y valores en las encuestas de INEGI usando Python
- Generando archivos de Excel con formatos y gráficas usando Python