Cómo juntar varios archivos de Excel en uno con Python y pandas

Tienes una carpeta con los Excel de ventas de cada sucursal, o uno por mes, y necesitas un consolidado. En el artículo anterior de Power Query viste cómo hacerlo con clics. Aquí lo harás con Python y pandas: un script de menos de 30 líneas que junta todos los archivos, marca de dónde vino cada fila, revisa errores y guarda el consolidado con un resumen.

Si es tu primera vez con pandas, empieza por el artículo “Python para usuarios de Excel” de este blog. Vas a necesitar pandas y openpyxl instalados:

pip install pandas openpyxl

La estructura de ejemplo

reportes/
├── consolidar.py
└── ventas/
    ├── Ventas_Manta.xlsx
    ├── Ventas_Portoviejo.xlsx
    ├── Ventas_Guayaquil.xlsx
    └── Ventas_Quito.xlsx

Cada archivo tiene una hoja Ventas con las columnas Fecha, Producto, Cantidad y Total.

Paso 1: listar los archivos de la carpeta

La librería pathlib, que viene con Python, permite buscar archivos con un patrón:

from pathlib import Path

carpeta = Path("ventas")
archivos = sorted(carpeta.glob("*.xlsx"))

for a in archivos:
    print(a.name)

Si los archivos están repartidos en subcarpetas, usa carpeta.rglob("*.xlsx") para buscar en todas.

Paso 2: leer cada archivo y apilarlos

Leemos cada Excel, le agregamos una columna con su origen y al final unimos todo con pd.concat, que equivale a pegar una tabla debajo de la otra:

import pandas as pd

tablas = []
for a in archivos:
    if a.name.startswith("~$"):      # archivos temporales de Excel abierto
        continue
    df = pd.read_excel(a, sheet_name="Ventas")
    df["Archivo"] = a.name
    df["Sucursal"] = a.stem.replace("Ventas_", "")
    tablas.append(df)

consolidado = pd.concat(tablas, ignore_index=True)
print(consolidado.shape)

a.stem es el nombre del archivo sin la extensión: de Ventas_Manta.xlsx obtenemos Ventas_Manta y luego Manta.

Paso 3: revisar que todos tengan las mismas columnas

El error más común al consolidar es que un archivo tenga un encabezado distinto. pandas no falla: simplemente crea una columna nueva y llena con vacíos. Por eso conviene revisar antes de unir:

esperadas = {"Fecha", "Producto", "Cantidad", "Total"}

for a in archivos:
    if a.name.startswith("~$"):
        continue
    cols = set(pd.read_excel(a, sheet_name="Ventas", nrows=0).columns)
    faltan = esperadas - cols
    sobran = cols - esperadas
    if faltan or sobran:
        print(f"{a.name}: faltan {faltan or '-'} | sobran {sobran or '-'}")

Con nrows=0 solo se leen los encabezados, así que la revisión es instantánea aunque los archivos sean grandes.

Paso 4: limpiar lo básico

consolidado["Fecha"] = pd.to_datetime(consolidado["Fecha"], errors="coerce")
consolidado["Producto"] = consolidado["Producto"].astype(str).str.strip()
consolidado = consolidado.dropna(subset=["Fecha", "Total"])
consolidado = consolidado.drop_duplicates()

• errors="coerce" convierte en vacío cualquier fecha que no se pueda leer, en lugar de detener el script.
• str.strip() quita espacios al inicio y al final, como ESPACIOS en Excel.
• drop_duplicates() elimina filas idénticas, útiles si alguien envió dos veces el mismo archivo.

Paso 5: guardar el consolidado y un resumen

resumen = (consolidado
           .groupby("Sucursal", as_index=False)
           .agg(Ventas=("Total", "sum"), Pedidos=("Total", "count"))
           .sort_values("Ventas", ascending=False))

with pd.ExcelWriter("consolidado_ventas.xlsx") as w:
    consolidado.to_excel(w, sheet_name="Detalle", index=False)
    resumen.to_excel(w, sheet_name="Resumen", index=False)

El script completo

from pathlib import Path
import pandas as pd

CARPETA = Path("ventas")
HOJA = "Ventas"
ESPERADAS = {"Fecha", "Producto", "Cantidad", "Total"}

tablas = []
for a in sorted(CARPETA.glob("*.xlsx")):
    if a.name.startswith("~$"):
        continue
    df = pd.read_excel(a, sheet_name=HOJA)
    faltan = ESPERADAS - set(df.columns)
    if faltan:
        print(f"Omitido {a.name}: faltan columnas {faltan}")
        continue
    df["Archivo"] = a.name
    df["Sucursal"] = a.stem.replace("Ventas_", "")
    tablas.append(df)

consolidado = pd.concat(tablas, ignore_index=True)
consolidado["Fecha"] = pd.to_datetime(consolidado["Fecha"], errors="coerce")
consolidado["Producto"] = consolidado["Producto"].astype(str).str.strip()
consolidado = consolidado.dropna(subset=["Fecha", "Total"]).drop_duplicates()

resumen = (consolidado.groupby("Sucursal", as_index=False)
           .agg(Ventas=("Total", "sum"), Pedidos=("Total", "count"))
           .sort_values("Ventas", ascending=False))

with pd.ExcelWriter("consolidado_ventas.xlsx") as w:
    consolidado.to_excel(w, sheet_name="Detalle", index=False)
    resumen.to_excel(w, sheet_name="Resumen", index=False)

print(f"{len(tablas)} archivos, {len(consolidado)} filas. Listo: consolidado_ventas.xlsx")

Variantes útiles

• Varias hojas por archivo: pd.read_excel(a, sheet_name=None) devuelve un diccionario con todas las hojas; recórrelo y apila cada una agregando una columna con el nombre de la hoja.
• Archivos CSV: cambia el patrón a "*.csv" y usa pd.read_csv(a, sep=";", decimal=",") si vienen con formato regional latinoamericano.
• Solo los archivos nuevos: filtra por fecha de modificación con a.stat().st_mtime.

¿Power Query o Python?

Si el consolidado termina en Excel o Power BI y otras personas lo van a mantener con clics, Power Query es más cómodo. Si necesitas reglas de validación más complejas, procesar cientos de archivos, enviar el resultado por correo o integrarlo con otros sistemas, Python te da más control. Muchas empresas usan los dos: Python prepara y valida, y Power BI presenta.