Este artículo detalla un enfoque práctico para la automatización de tareas relacionadas con archivos Excel utilizando la biblioteca pandas en Python. Se centra en la lectura de múltiples hojas de cálculo, la extracción y reestructuración de datos, y la exportación de resultadso.

Lectura y Procesamiento de Hojas de Cálculo

La lectura de un archivo Excel que contiene varias hojas se puede realizar de manera eficinete con pandas.read_excel. Al especificar sheet_name=None, la función devuelve un diccionario donde las claves son los nombres de las hojas y los valores son DataFrames correspondientes.

import pandas as pd

nombre_archivo_base = 'SistemaImpuestos'
ruta_archivo = f"C:/Ruta/A/Tus/Datos/{nombre_archivo_base}.xlsx"
datos_por_hoja = pd.read_excel(ruta_archivo, sheet_name=None)

Para accceder a los nombres de todas las hojas, se puede convertir las claves del diccionario resultante en una lista:

nombres_hojas = list(datos_por_hoja)

Se puede iterar sobre esta lista para procesar cada hoja individualmente. A menudo, una hoja específica actúa como un índice o directorio. Se puede extraer esta hoja y luego eliminarla de la lista de procesamiento para evitar duplicados o errores.

# Asumiendo que 'Directorio' es el nombre de la hoja que contiene metadatos
df_directorio = datos_por_hoja.get('Directorio')
nombres_hojas.remove('Directorio')

Dentro del bucle de procesamiento, se selecciona una hoja específica usando .get(nombre_hoja). Luego, se extraen las columnas de interés. Si se necesitan múltiples columnas, estas deben especificarse como una lista anidada.

dataframe_final = None
for hoja in nombres_hojas:
    df_hoja_actual = datos_por_hoja.get(hoja)
    # Seleccionar solo las columnas deseadas
    df_seleccionado = df_hoja_actual[['NombreDato', 'NombreDatoIngles']]

    # Recuperar información de la tabla y descripción de la hoja a partir del DataFrame del directorio
    # Se usa .loc para filtrar por el nombre de la hoja y obtener el valor de la columna 'NombreTablaBD'
    # .values[0] se utiliza para extraer el valor escalar de la Serie resultante
    nombre_tabla_bd = df_directorio.loc[df_directorio['NombreTabla'] == hoja, 'NombreTablaBD'].values[0]

    # Insertar nuevas columnas en el DataFrame seleccionado
    df_seleccionado.insert(0, 'NombreTablaBD', nombre_tabla_bd)
    df_seleccionado.insert(0, 'DescripcionTabla', hoja)
    df_seleccionado.insert(0, 'NombreSistema', nombre_archivo_base)

    # Concatenar los DataFrames
    if dataframe_final is None:
        dataframe_final = df_seleccionado
    else:
        dataframe_final = pd.concat([dataframe_final, df_seleccionado], ignore_index=True)

La función .insert() permite añadir columnas en una posición específica. Los argumentos son: el índice de la nueva columna, el nombre de la columna y el valor a insertar. Si se proporciona un valor escalar, se repetirá para todas las filas.

La concatenación de DataFrames se realiza con pd.concat(). El argumento ignore_index=True asegura que se genere un nuevo índice continuo para el DataFrame combinado.

Exportación de Resultados

Una vez que los datos han sido procesados y combinados, se pueden exportar a un nuevo archivo Excel. Es recomendable excluir el índice predeterminado de pandas utilizando index=False.

ruta_salida = f"C:/Ruta/A/Tus/Salidas/{nombre_archivo_base}_procesado.xlsx"
dataframe_final.to_excel(ruta_salida, index=False)

Código Completo para Procesamiento Específico

import pandas as pd
import numpy as np

nombre_sistema = 'SistemaImpuestos'
ruta_origen = f"C:/Ruta/A/Tus/Datos/{nombre_sistema}.xlsx"
datos_todas_hojas = pd.read_excel(ruta_origen, sheet_name=None)

nombres_hojas_lista = list(datos_todas_hojas)
df_directorio = datos_todas_hojas.get('Directorio') # Asumiendo que existe una hoja 'Directorio'
nombres_hojas_lista.remove('Directorio')

dfs_consolidado = None
for nombre_hoja in nombres_hojas_lista:
    df_hoja = datos_todas_hojas.get(nombre_hoja)
    df_seleccion = df_hoja[['NombreDato', 'NombreDatoIngles']]

    # Obtener nombre de tabla de BD correspondiente
    tabla_bd = df_directorio.loc[df_directorio['NombreTabla'] == nombre_hoja, 'NombreTablaBD'].values[0]

    df_seleccion.insert(0, 'NombreTablaBD', tabla_bd)
    df_seleccion.insert(0, 'DescripcionTabla', nombre_hoja)
    df_seleccion.insert(0, 'NombreSistema', nombre_sistema)

    if dfs_consolidado is None:
        dfs_consolidado = df_seleccion
    else:
        dfs_consolidado = pd.concat([dfs_consolidado, df_seleccion], ignore_index=True)

ruta_destino_excel = f"C:/Ruta/A/Tus/Salidas/{nombre_sistema}_consolidado.xlsx"
dfs_consolidado.to_excel(ruta_destino_excel, index=False)

Función para Fusionar Múltiples Archivos Excel

El siguiente código proporciona una solución genérica para fusionar todos los archivos Excel de un directorio especificado que tengan un formato similar.

import pandas as pd
import numpy as np
import os

def obtener_rutas_archivos(directorio: str) -> list:
    '''Devuelve una lista de rutas completas a todos los archivos en el directorio dado.'''
    archivos_en_directorio = os.listdir(directorio)
    rutas_completas = [os.path.join(directorio, archivo) for archivo in archivos_en_directorio]
    return rutas_completas

def fusionar_archivos_excel(lista_rutas_archivos: list) -> pd.DataFrame:
    '''Fusiona DataFrames de una lista de archivos Excel y devuelve un único DataFrame.'''
    df_resultado = None
    for ruta in lista_rutas_archivos:
        df_actual = pd.read_excel(ruta)
        if df_resultado is None:
            df_resultado = df_actual
        else:
            df_resultado = pd.concat([df_resultado, df_actual], ignore_index=True)
    return df_resultado

# Ejemplo de uso:
ruta_directorio_origen = "C:/Ruta/A/Tus/ArchivosProcesados/"
rutas_a_fusionar = obtener_rutas_archivos(ruta_directorio_origen)
df_fusionado = fusionar_archivos_excel(rutas_a_fusionar)

# Exportar el DataFrame fusionado
ruta_exportacion = os.path.join(ruta_directorio_origen, 'exportacion_final.xlsx')
df_fusionado.to_excel(ruta_exportacion, index=False)