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:

img1

Final del archivo:

img2

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()
INEGI. Encuesta Nacional de Ocupación y Empleo. Indicadores estratégicos. Primer trimestre de 2020 Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10 Unnamed: 11 Unnamed: 12 Unnamed: 13 Unnamed: 14 Unnamed: 15 Unnamed: 16 Unnamed: 17 Unnamed: 18
0 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 Indicadores Estratégicos de Ocupación y Empleo... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 Entidad Federativa: NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
3 Aguascalientes NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
4 NaN NaN NaN INDICADOR Enero - Marzo 2020 NaN NaN Coeficientes de Variación (%) NaN NaN Errores Estándar NaN NaN Intervalos de Confianza al 90% NaN NaN NaN NaN NaN

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)
INEGI. Encuesta Nacional de Ocupación y Empleo. Indicadores estratégicos. Primer trimestre de 2020 Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10 Unnamed: 11 Unnamed: 12 Unnamed: 13 Unnamed: 14 Unnamed: 15 Unnamed: 16 Unnamed: 17 Unnamed: 18
296 14 Se consideran "personas con interés para traba... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
297 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
298 Las estimaciones que aparecen en este cuadro e... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
299 Nivel de precisión de las estimaciones: NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
300 Alta, CV en el rango de (0,15) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
301 Moderada, CV en el rango de [15, 30) NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
302 Baja, CV de 30% en adelante NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

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)
Unnamed: 0_level_0 Unnamed: 1_level_0 Unnamed: 2_level_0 INDICADOR Enero - Marzo 2020 Coeficientes de Variación (%) Errores Estándar Intervalos de Confianza al 90%
Unnamed: 0_level_1 Unnamed: 1_level_1 Unnamed: 2_level_1 Unnamed: 3_level_1 Unnamed: 4_level_1 Unnamed: 5_level_1 Unnamed: 6_level_1 Unnamed: 7_level_1 Unnamed: 8_level_1 Unnamed: 9_level_1 Unnamed: 10_level_1 Unnamed: 11_level_1 Unnamed: 12_level_1 Total Hombres Mujeres
Unnamed: 0_level_2 Unnamed: 1_level_2 Unnamed: 2_level_2 Unnamed: 3_level_2 Total Hombres Mujeres Total Hombres Mujeres Total Hombres Mujeres LIIC LSIC LIIC LSIC LIIC LSIC
0 I. Población total 1 NaN NaN NaN 1363581.0 661998.0 701583.0 1.423850 1.77911 1.43904 19415.0 11777 10096 1.33164e+06 1.39552e+06 642624 681372 684975 718191
1 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 2. Población de 15 años y más NaN NaN NaN 1014307.0 484719.0 529588.0 1.350378 1.6399 1.4333 13696.0 7948 7590 991775 1.03684e+06 471643 497795 517102 542074

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)
Unnamed: 0_level_0 Unnamed: 1_level_0 Unnamed: 2_level_0 INDICADOR Enero - Marzo 2020 Coeficientes de Variación (%) Errores Estándar Intervalos de Confianza al 90%
Unnamed: 0_level_1 Unnamed: 1_level_1 Unnamed: 2_level_1 Unnamed: 3_level_1 Unnamed: 4_level_1 Unnamed: 5_level_1 Unnamed: 6_level_1 Unnamed: 7_level_1 Unnamed: 8_level_1 Unnamed: 9_level_1 Unnamed: 10_level_1 Unnamed: 11_level_1 Unnamed: 12_level_1 Total Hombres Mujeres
Unnamed: 0_level_2 Unnamed: 1_level_2 Unnamed: 2_level_2 Unnamed: 3_level_2 Total Hombres Mujeres Total Hombres Mujeres Total Hombres Mujeres LIIC LSIC LIIC LSIC LIIC LSIC
277 NaN Tasas calculadas contra la población ocupada n... NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
278 NaN NaN Tasa de ocupación en el sector informal 2 (TOSI2) NaN 20.1963 22.8466 16.3304 4.116332 4.48344 6.14149 0.831347 1.02432 1.00293 18.829 21.564 21.162 24.532 14.681 17.98
279 NaN NaN Tasa de informalidad laboral 2 (TIL2) NaN 39.3077 37.4318 42.0439 2.655421 3.20142 3.24678 1.043784 1.19835 1.36507 37.591 41.025 35.461 39.403 39.798 44.289

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()
INDICADOR Enero - Marzo 2020 Coeficientes de Variación (%) Errores Estándar Intervalos de Confianza al 90%
Unnamed: 4_level_1 Unnamed: 5_level_1 Unnamed: 6_level_1 Unnamed: 7_level_1 Unnamed: 8_level_1 Unnamed: 9_level_1 Unnamed: 10_level_1 Unnamed: 11_level_1 Unnamed: 12_level_1 Total Hombres Mujeres
Total Hombres Mujeres Total Hombres Mujeres Total Hombres Mujeres LIIC LSIC LIIC LSIC LIIC LSIC
10. Tasas Tasas calculadas contra la población ocupada Tasa de ocupación en el sector informal 1 (TOSI1) Mediana 19.4007 21.4890 16.1900 4.109763 4.49951 6.14659 0.797323 0.9669 0.995131 18.089 20.712 19.898 23.08 14.553 17.827
Tasa de informalidad laboral 1 (TIL1) Mediana 40.9383 40.0765 42.2632 2.532868 2.97923 3.20628 1.036912 1.19397 1.35508 39.233 42.644 38.112 42.041 40.034 44.492
Tasas calculadas contra la población ocupada no agropecuaria Tasa de informalidad laboral 1 (TIL1) Mediana NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
Tasa de ocupación en el sector informal 2 (TOSI2) Mediana 20.1963 22.8466 16.3304 4.116332 4.48344 6.14149 0.831347 1.02432 1.00293 18.829 21.564 21.162 24.532 14.681 17.98
Tasa de informalidad laboral 2 (TIL2) Mediana 39.3077 37.4318 42.0439 2.655421 3.20142 3.24678 1.043784 1.19835 1.36507 37.591 41.025 35.461 39.403 39.798 44.289

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()
Unnamed: 4_level_1 Unnamed: 5_level_1 Unnamed: 6_level_1
Total Hombres Mujeres
I. Población total 1 NaN NaN NaN 1363581.0 661998.0 701583.0
NaN NaN NaN NaN
2. Población de 15 años y más NaN NaN NaN 1014307.0 484719.0 529588.0
Población económicamente activa (PEA) NaN NaN 603802.0 366686.0 237116.0
Ocupada NaN 583762.0 353706.0 230056.0

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()
Total Hombres Mujeres
I. Población total 1 NaN NaN NaN 1363581.0 661998.0 701583.0
NaN NaN NaN NaN
2. Población de 15 años y más NaN NaN NaN 1014307.0 484719.0 529588.0
Población económicamente activa (PEA) NaN NaN 603802.0 366686.0 237116.0
Ocupada NaN 583762.0 353706.0 230056.0

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()
Total Hombres Mujeres
I. Población total 1 NaN NaN NaN 1363581.0 661998.0 701583.0
2. Población de 15 años y más NaN NaN NaN 1014307.0 484719.0 529588.0
Población económicamente activa (PEA) NaN NaN 603802.0 366686.0 237116.0
Ocupada NaN 583762.0 353706.0 230056.0
Desocupada NaN 20040.0 12980.0 7060.0

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()
sexo Total Hombres Mujeres
nivel_1 nivel_2 nivel_3 nivel_4
I. Población total 1 Total Total Total 1363581.0 661998.0 701583.0
2. Población de 15 años y más Total Total Total 1014307.0 484719.0 529588.0
Población económicamente activa (PEA) Total Total 603802.0 366686.0 237116.0
Ocupada Total 583762.0 353706.0 230056.0
Desocupada Total 20040.0 12980.0 7060.0

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
C:\ProgramData\Anaconda3\lib\site-packages\ipykernel_launcher.py:1: PerformanceWarning: indexing past lexsort depth may impact performance.
  """Entry point for launching an IPython kernel.
sexo Total Hombres Mujeres
nivel_3 nivel_4
Total Total 603802.0 366686.0 237116.0
Ocupada Total 583762.0 353706.0 230056.0
Desocupada Total 20040.0 12980.0 7060.0

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()
nivel_3 sexo poblacion_Total poblacion_Ocupada poblacion_Desocupada
0 Total 603802.0 583762.0 20040.0
1 Hombres 366686.0 353706.0 12980.0
2 Mujeres 237116.0 230056.0 7060.0

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()
C:\ProgramData\Anaconda3\lib\site-packages\ipykernel_launcher.py:1: PerformanceWarning: indexing past lexsort depth may impact performance.
  """Entry point for launching an IPython kernel.
sexo til_1
0 Total 40.9383
1 Hombres 40.0765
2 Mujeres 42.2632

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')
nivel_3 sexo poblacion_Desocupada poblacion_Ocupada poblacion_Total estado
0 Total 30743.0 1766690.0 1797433.0 Oaxaca
1 Hombres 18948.0 999821.0 1018769.0 Oaxaca
2 Mujeres 11795.0 766869.0 778664.0 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
nivel_3 sexo poblacion_Desocupada poblacion_Ocupada poblacion_Total estado
0 Total 30743.0 1766690.0 1797433.0 Oaxaca
1 Hombres 18948.0 999821.0 1018769.0 Oaxaca
2 Mujeres 11795.0 766869.0 778664.0 Oaxaca
3 Total 55562.0 2068483.0 2124045.0 Chiapas
4 Hombres 32413.0 1417037.0 1449450.0 Chiapas
5 Mujeres 23149.0 651446.0 674595.0 Chiapas
6 Total 57702.0 1031968.0 1089670.0 Tabasco
7 Hombres 31558.0 643777.0 675335.0 Tabasco
8 Mujeres 26144.0 388191.0 414335.0 Tabasco
9 Total 12364.0 435961.0 448325.0 Campeche
10 Hombres 7750.0 269822.0 277572.0 Campeche
11 Mujeres 4614.0 166139.0 170753.0 Campeche
12 Total 25607.0 851473.0 877080.0 Quintana_Roo
13 Hombres 14241.0 525859.0 540100.0 Quintana_Roo
14 Mujeres 11366.0 325614.0 336980.0 Quintana_Roo
15 Total 21992.0 1086089.0 1108081.0 Yucatán
16 Hombres 11421.0 651820.0 663241.0 Yucatán
17 Mujeres 10571.0 434269.0 444840.0 Yucatán

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.