Creación de Datos de Muestra: Desarrollamos conjuntos de datos de ejemplo para trabajar.
Generación de Archivos de Ventas Divididos: Dividimos los datos de ventas en tres archivos de Excel separados.
Preparación de Datos Incompletos para Limpieza: Creamos conjuntos de datos que requerían limpieza y preprocesamiento.
Configuración de Datos de Ventas para Análisis: Organizamos los archivos de datos de ventas para que estuvieran listos para un análisis en profundidad.
Además, implementamos algunos scripts de automatización fundamentales, que incluyen:
Fusión de Archivos de Excel: Combinamos múltiples archivos de Excel en un único conjunto de datos.
Funciones de Limpieza de Datos: Automatizamos el proceso de limpieza y preparación de datos para el análisis.
Informes Mensuales Automatizados: Creamos scripts para generar informes mensuales sin intervención manual.
Índice
Mejorando Nuestra Automatización con Análisis Avanzado
Construyendo sobre esta sólida base, nuestro objetivo para esta entrega es introducir características analíticas más prácticas y asegurarnos de que todo se explique en detalle para principiantes. Usaremos las últimas versiones de pandas (2.3.2) y openpyxl (3.1.4) a partir de 2025, siguiendo las mejores prácticas de la documentación oficial.
Prerrequisitos y Configuración del Entorno
Paso 1: Comprueba tu Versión de Python
Primero, asegúrate de tener Python 3.9 o posterior instalado:
python --version
# o
python3 --version
Salida esperada:Python 3.9.0 o superior (hasta 3.13)
Paso 2: Crear un Entorno Virtual
Importante: Siempre usa un entorno virtual para proyectos de Python para evitar conflictos de dependencias.
En Windows:
# Navega a tu carpeta de proyecto
cd C:\Users\TuNombre\excel-automation
# Crear entorno virtual
python -m venv excel_env
# Activarlo
excel_env\Scripts\activate
En macOS/Linux:
# Navega a tu carpeta de proyecto
cd ~\excel-automation
# Crear entorno virtual
python3 -m venv excel_env
# Activarlo
source excel_env/bin/activate
Indicador de éxito: El prompt de tu terminal ahora debería mostrar (excel_env) al principio.
Paso 3: Crear Estructura del Proyecto
Crea la siguiente estructura de carpetas:
excel-automation/
│
├── excel_env/ # Entorno virtual (creado arriba)
├── excel_files/ # Para almacenar archivos de Excel generados
├── scripts/ # Scripts de Python
└── requirements.txt # Dependencias de paquetes
Crear carpetas:
mkdir excel_files scripts
Instalando Paquetes Requeridos
Paso 1: Crear requirements.txt
Crea un archivo llamado requirements.txt con el siguiente contenido:
"""Prueba si todos los paquetes requeridos están instalados correctamente"""
import sys
print(f"Versión de Python: {sys.version}")
try:
import pandas as pd
print(f"✓ pandas {pd.__version__} instalado")
except ImportError as e:
print(f"✗ pandas no instalado: {e}")
try:
import openpyxl
print(f"✓ openpyxl {openpyxl.__version__} instalado")
except ImportError as e:
print(f"✗ openpyxl no instalado: {e}")
try:
import scipy
print(f"✓ scipy {scipy.__version__} instalado")
except ImportError as e:
print(f"✗ scipy no instalado: {e}")
try:
import numpy as np
print(f"✓ numpy {np.__version__} instalado")
except ImportError as e:
print(f"✗ numpy no instalado: {e}")
print("\n¡Todos los paquetes se importaron con éxito!")
Ejecútalo:
python scripts/test_imports.py
Creando Datos de Muestra
Paso 1: Generar Datos de Ventas Completos
Crea scripts/create_sample_data.py:
"""
Crea archivos de Excel de muestra para aprender la automatización de Excel
"""
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import os
from pathlib import Path
# Establecer semilla aleatoria para reproducibilidad
np.random.seed(42)
def create_sales_data():
"""Genera datos de ventas realistas con múltiples dimensiones"""
# Configuración
start_date = datetime(2023, 1, 1)
end_date = datetime(2023, 12, 31)
num_records = 1000
# Categorías de datos de muestra
products = ['Laptop', 'Mouse', 'Keyboard', 'Monitor', 'Headphones',
'Webcam', 'USB Cable', 'Phone Charger', 'Tablet', 'Smart Watch']
categories = ['Electronics', 'Accessories', 'Peripherals']
sales_reps = [f'Rep {i}' for i in range(1, 11)]
regions = ['North', 'South', 'East', 'West']
# Generar fechas aleatorias
date_range = (end_date - start_date).days
random_days = np.random.randint(0, date_range, num_records)
dates = [start_date + timedelta(days=int(day)) for day in random_days]
# Crear el dataframe principal
df = pd.DataFrame({
'Date': dates,
'Year-Month': [date.strftime('%Y-%m') for date in dates],
'Product': np.random.choice(products, num_records),
'Category': np.random.choice(categories, num_records),
'Sales_Rep': np.random.choice(sales_reps, num_records),
'Region': np.random.choice(regions, num_records),
'Quantity': np.random.randint(1, 50, num_records),
'Unit_Price': np.round(np.random.uniform(10, 1000, num_records), 2),
})
# Calcular el Monto de Ventas
df['Sales_Amount'] = np.round(df['Quantity'] * df['Unit_Price'], 2)
# Añadir algunos patrones realistas# Ventas más altas en el Q4 (temporada de vacaciones)
q4_mask = df['Date'].dt.quarter == 4
df.loc[q4_mask, 'Quantity'] = (df.loc[q4_mask, 'Quantity'] * 1.5).astype(int)
df.loc[q4_mask, 'Sales_Amount'] = np.round(df.loc[q4_mask, 'Quantity'] *
df.loc[q4_mask, 'Unit_Price'], 2)
# Ordenar por fecha
df = df.sort_values('Date').reset_index(drop=True)
return df
def create_messy_data():
"""Genera datos que necesitan limpieza para practicar"""
# Crear datos intencionadamente desordenados
data = {
'Customer_Name': ['John Doe', ' Jane Smith ', 'Bob Johnson', None, 'Alice Brown'],
'Email': ['john@email.com', 'JANE@EMAIL.COM', 'bob@', 'invalid-email', 'alice@email.com'],
'Phone': ['123-456-7890', '9876543210', '(555) 123-4567', 'not a phone', None],
'Purchase_Date': ['2023-01-15', '2023/02/20', '03-15-2023', 'yesterday', '2023-04-10'],
'Amount': [100.50, '200', None, 'fifty', 350.75],
'Status': ['Active', 'active', 'ACTIVE', 'Inactive', None]
}
# Duplicar algunas filas
df = pd.DataFrame(data)
df = pd.concat([df, df.iloc[[0, 2]]], ignore_index=True)
return df
def split_sales_files(df):
"""Divide los datos de ventas principales en múltiples archivos"""
# Crear el directorio excel_files si no existe
Path('excel_files').mkdir(exist_ok=True)
# Dividir los datos en 3 partes
chunk_size = len(df) // 3
for i in range(3):
start_idx = i * chunk_size
if i == 2: # El último trozo obtiene el resto
chunk = df.iloc[start_idx:]
else:
chunk = df.iloc[start_idx:start_idx + chunk_size]
filename = f'excel_files/sales_data_{i+1}.xlsx'
chunk.to_excel(filename, index=False, engine='openpyxl')
print(f"Creado: {filename} ({len(chunk)} registros)")
return True
def main():
"""Función principal para crear todos los archivos de muestra"""
print("=" * 60)
print("Creando Archivos de Excel de Muestra para Aprender")
print("=" * 60)
try:
# Crear datos de ventas principales
print("\n1. Generando datos de ventas...")
sales_df = create_sales_data()
sales_df.to_excel('sales_data_original.xlsx', index=False, engine='openpyxl')
print(f" ✓ Creado sales_data_original.xlsx ({len(sales_df)} registros)")
# Mostrar muestra de los datos
print("\n Muestra de datos de ventas:")
print(sales_df[['Date', 'Product', 'Sales_Amount']].head())
# Crear datos desordenados para la práctica de limpieza
print("\n2. Generando datos desordenados para limpieza...")
messy_df = create_messy_data()
messy_df.to_excel('messy_data.xlsx', index=False, engine='openpyxl')
print(f" ✓ Creado messy_data.xlsx ({len(messy_df)} registros)")
# Dividir archivos
print("\n3. Creando archivos divididos...")
split_sales_files(sales_df)
print("\n" + "=" * 60)
print("✓ ¡Todos los archivos de muestra creados con éxito!")
print("=" * 60)
# Mostrar estructura de archivos
print("\nTu estructura de archivos ahora debería verse así:")
print("""\
excel-automation/
├── sales_data_original.xlsx
├── messy_data.xlsx
└── excel_files/
├── sales_data_1.xlsx
├── sales_data_2.xlsx
└── sales_data_3.xlsx
""")
except Exception as e:
print(f"\n✗ Error al crear archivos: {e}")
print("\nConsejos para solucionar problemas:")
print("1. Asegúrate de estar en el directorio correcto")
print("2. Comprueba que tienes permisos de escritura")
print("3. Cierra cualquier archivo de Excel que pueda estar abierto")
return False
return True
if __name__ == "__main__":
main()
Operaciones Básicas de Excel
Paso 1: Fusionar Múltiples Archivos de Excel
Crea scripts/merge_excel_files.py:
"""
Fusionar múltiples archivos de Excel en uno
"""
import pandas as pd
from pathlib import Path
import os
def merge_excel_files(input_folder='excel_files', output_file='merged_sales.xlsx'):
"""
Fusiona todos los archivos de Excel en una carpeta en un solo archivo
Parámetros:
-----------
input_folder : str
Carpeta que contiene los archivos de Excel a fusionar
output_file : str
Nombre del archivo de salida fusionado
"""
# Comprobar si la carpeta existe
if not os.path.exists(input_folder):
raise FileNotFoundError(f"¡Carpeta '{input_folder}' no encontrada!")
# Obtener todos los archivos de Excel
excel_files = list(Path(input_folder).glob('*.xlsx'))
if not excel_files:
raise ValueError(f"No se encontraron archivos de Excel en '{input_folder}'")
print(f"Se encontraron {len(excel_files)} archivos de Excel para fusionar:")
for file in excel_files:
print(f" - {file.name}")
# Leer y combinar todos los archivos
all_dataframes = []
for file in excel_files:
try:
df = pd.read_excel(file, engine='openpyxl')
all_dataframes.append(df)
print(f" ✓ Leído {file.name}: {len(df)} registros")
except Exception as e:
print(f" ✗ Error al leer {file.name}: {e}")
# Fusionar todos los dataframes
if all_dataframes:
merged_df = pd.concat(all_dataframes, ignore_index=True)
# Guardar en Excel
merged_df.to_excel(output_file, index=False, engine='openpyxl')
print(f"\n✓ Se fusionaron con éxito {len(all_dataframes)} archivos")
print(f" Total de registros: {len(merged_df)}")
print(f" Guardado en: {output_file}")
return merged_df
else:
print("✗ No se pudieron leer archivos")
return None
if __name__ == "__main__":
# Ejecutar la fusión
merged_data = merge_excel_files()
if merged_data is not None:
print("\nPrimeras 5 filas de datos fusionados:")
print(merged_data.head())
Paso 2: Escribir en Múltiples Hojas
Crea scripts/multi_sheet_writer.py:
"""
Escribir datos en múltiples hojas en un archivo de Excel
"""
import pandas as pd
import numpy as np
from datetime import datetime
def create_multi_sheet_report(sales_df):
"""
Crea un informe de Excel con múltiples hojas
"""
# Crear el objeto escritor de Excel
output_file = 'multi_sheet_report.xlsx'
# Usar el administrador de contexto asegura que el archivo se guarde correctamente
with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
# Hoja 1: Datos Crudos
sales_df.to_excel(writer, sheet_name='Raw Data', index=False)
print("✓ Añadida hoja 'Raw Data'")
# Hoja 2: Resumen Mensual
monthly_summary = sales_df.groupby('Year-Month').agg({
'Sales_Amount': ['sum', 'mean', 'count'],
'Quantity': 'sum'
}).round(2)
monthly_summary.columns = ['Total_Sales', 'Avg_Sales', 'Num_Transactions', 'Total_Quantity']
monthly_summary.to_excel(writer, sheet_name='Monthly Summary')
print("✓ Añadida hoja 'Monthly Summary'")
# Hoja 3: Rendimiento del Producto
product_summary = sales_df.groupby('Product').agg({
'Sales_Amount': 'sum',
'Quantity': 'sum'
}).sort_values('Sales_Amount', ascending=False).round(2)
product_summary.to_excel(writer, sheet_name='Product Performance')
print("✓ Añadida hoja 'Product Performance'")
# Hoja 4: Análisis Regional
regional_summary = sales_df.groupby(['Region', 'Category']).agg({
'Sales_Amount': 'sum'
}).unstack(fill_value=0).round(2)
regional_summary.to_excel(writer, sheet_name='Regional Analysis')
print("✓ Añadida hoja 'Regional Analysis'")
# Hoja 5: Mejores Rendimientos
top_reps = sales_df.groupby('Sales_Rep')['Sales_Amount'].sum().nlargest(5)
top_products = sales_df.groupby('Product')['Sales_Amount'].sum().nlargest(5)
top_df = pd.DataFrame({
'Top Sales Reps': top_reps.index,
'Rep Sales': top_reps.values,
'Top Products': top_products.index,
'Product Sales': top_products.values
})
top_df.to_excel(writer, sheet_name='Top Performers', index=False)
print("✓ Añadida hoja 'Top Performers'")
print(f"\n✓ Informe de múltiples hojas guardado en: {output_file}")
return output_file
if __name__ == "__main__":
# Cargar los datos de ventas
try:
df = pd.read_excel('sales_data_original.xlsx', engine='openpyxl')
print(f"Cargados {len(df)} registros de sales_data_original.xlsx")
# Crear el informe
report_file = create_multi_sheet_report(df)
except FileNotFoundError:
print("✗ Error: ¡sales_data_original.xlsx no encontrado!")
print(" Por favor, ejecuta create_sample_data.py primero")
except Exception as e:
print(f"✗ Error: {e}")
Análisis Avanzado con SciPy
Script de Análisis Mejorado
Crea scripts/smart_analysis.py:
"""
Análisis avanzado de datos de Excel con SciPy e información estadística
"""
import pandas as pd
import numpy as np
from scipy import stats
import warnings
warnings.filterwarnings('ignore')
def check_column_names(df):
"""
Muestra las columnas disponibles y comprueba las columnas requeridas
"""
print("\n=== Comprobación de la Estructura de Datos ===")
print(f"Forma: {df.shape[0]} filas × {df.shape[1]} columnas")
print("\nColumnas disponibles:")
for i, col in enumerate(df.columns, 1):
print(f" {i}. {col}")
# Comprobar las columnas requeridas
required_columns = ['Year-Month', 'Sales_Amount', 'Product', 'Region']
missing_columns = [col for col in required_columns if col not in df.columns]
if missing_columns:
print(f"\n⚠ Advertencia: Columnas faltantes: {missing_columns}")
print(" ¡Los nombres de las columnas distinguen entre mayúsculas y minúsculas!")
return False
else:
print("\n✓ Todas las columnas requeridas encontradas")
return True
def discover_insights(df):
"""
Extrae información completa de los datos de ventas
"""
insights = []
# Asegurarse de que tenemos las columnas correctas
if not check_column_names(df):
print("\nIntentando corregir los nombres de las columnas...")
# Intentar corregir problemas comunes con los nombres de las columnas
df.columns = df.columns.str.replace(' ', '_')
if not check_column_names(df):
return ["Error: No se encontraron las columnas requeridas en los datos"]
print("\n=== Analizando Datos ===")
try:
# 1. Análisis de Ventas Mensuales
monthly_sales = df.groupby('Year-Month')['Sales_Amount'].sum()
# Mejores y peores meses
best_month = monthly_sales.idxmax()
best_month_sales = monthly_sales.max()
worst_month = monthly_sales.idxmin()
worst_month_sales = monthly_sales.min()
insights.append(
f"Mejor Mes de Ventas: {best_month} "
f"(¥{best_month_sales:,.0f})"
)
insights.append(
f"Peor Mes de Ventas: {worst_month} "
f"(¥{worst_month_sales:,.0f})"
)
# 2. Análisis Estadístico
sales_mean = df['Sales_Amount'].mean()
sales_median = df['Sales_Amount'].median()
sales_std = df['Sales_Amount'].std()
insights.append(
f"Transacción Promedio: ¥{sales_mean:,.2f} "
f"(Mediana: ¥{sales_median:,.2f}, Desv. Est.: ¥{sales_std:,.2f})"
)
# 3. Análisis de Tendencia usando SciPy
months_numeric = np.arange(len(monthly_sales))
slope, intercept, r_value, p_value, std_err = stats.linregress(
months_numeric, monthly_sales.values
)
trend = "creciente ↑" if slope > 0 else "decreciente ↓"
insights.append(
f"Tendencia de Ventas: {trend} "
f"(¥{abs(slope):,.0f}/mes, R²={r_value**2:.3f})"
)
# 4. Rendimiento del Producto
top_product = df.groupby('Product')['Sales_Amount'].sum().idxmax()
top_product_sales = df.groupby('Product')['Sales_Amount'].sum().max()
insights.append(
f"Producto Principal: {top_product} "
f"(¥{top_product_sales:,.0f} en ventas totales)"
)
# 5. Información Regional
regional_sales = df.groupby('Region')['Sales_Amount'].sum()
top_region = regional_sales.idxmax()
top_region_sales = regional_sales.max()
insights.append(
f"Región Principal: {top_region} "
f"(¥{top_region_sales:,.0f} en ventas totales)"
)
# 6. Detección de Valores Atípicos usando SciPy
z_scores = np.abs(stats.zscore(df['Sales_Amount']))
outliers = df[z_scores > 3]
if len(outliers) > 0:
insights.append(
f"Valores Atípicos Detectados: {len(outliers)} transacciones "
f"(>{3} desviaciones estándar de la media)"
)
# 7. Comprobación de Estacionalidad (Q4 vs otros trimestres)
if 'Date' in df.columns:
df['Date'] = pd.to_datetime(df['Date'])
df['Quarter'] = df['Date'].dt.quarter
q4_sales = df[df['Quarter'] == 4]['Sales_Amount'].sum()
other_q_sales = df[df['Quarter'] != 4]['Sales_Amount'].sum()
q4_boost = ((q4_sales / 3) / (other_q_sales / 9) - 1) * 100
if q4_boost > 10:
insights.append(
f"Patrón Estacional: El Q4 muestra un {q4_boost:.1f}% más de ventas "
f"(efecto de la temporada de vacaciones)"
)
except KeyError as e:
insights.append(f"Error: Columna {e} no encontrada en los datos")
print(f"\n✗ KeyError: {e}")
print(" Por favor, comprueba que los nombres de tus columnas coincidan exactamente")
except Exception as e:
insights.append(f"Error de análisis: {str(e)}")
print(f"\n✗ Error inesperado: {e}")
return insights
def save_insights_to_excel(insights, df, output_file='analysis_report.xlsx'):
"""
Guarda la información y los datos en un archivo de Excel formateado
"""
with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
# Crear dataframe de información
insights_df = pd.DataFrame({
'Insight': insights,
'Generated': pd.Timestamp.now().strftime('%Y-%m-%d %H:%M:%S')
})
# Escribir información
insights_df.to_excel(writer, sheet_name='Key Insights', index=False)
# Escribir datos crudos
df.head(100).to_excel(writer, sheet_name='Sample Data', index=False)
# Obtener los objetos workbook y worksheet
workbook = writer.book
worksheet = writer.sheets['Key Insights']
# Añadir formato
header_format = workbook.add_format({
'bold': True,
'bg_color': '#4CAF50',
'font_color': 'white',
'border': 1
})
# Aplicar formato de encabezado
for col_num, value in enumerate(insights_df.columns.values):
worksheet.write(0, col_num, value, header_format)
# Ajustar anchos de columna
worksheet.set_column('A:A', 80)
worksheet.set_column('B:B', 20)
print(f"\n✓ Informe de análisis guardado en: {output_file}")
# Ejecución Principal
if __name__ == "__main__":
try:
# Cargar los datos de muestra
print("Cargando datos...")
df = pd.read_excel("sales_data_original.xlsx", engine='openpyxl')
print(f"✓ Cargados {len(df)} registros")
# Ejecutar el análisis
print("\nIniciando análisis...")
found_insights = discover_insights(df)
# Mostrar los resultados
print("\n" + "=" * 60)
print("RESULTADOS DEL ANÁLISIS")
print("=" * 60)
for i, insight in enumerate(found_insights, 1):
print(f"{i}. {insight}")
# Guardar en Excel
save_insights_to_excel(found_insights, df)
except FileNotFoundError:
print("\n✗ Error: ¡sales_data_original.xlsx no encontrado!")
print("\nSolución:")
print("1. Ejecuta: python scripts/create_sample_data.py")
print("2. Asegúrate de estar en el directorio correcto")
except Exception as e:
print(f"\n✗ Error inesperado: {e}")
print("\nSolución de problemas:")
print("1. Comprueba que todos los paquetes estén instalados correctamente")
print("2. Verifica que el archivo de Excel no esté corrupto")
print("3. Asegúrate de que el archivo no esté abierto en Excel")
Guía de Solución de Problemas
Errores Comunes y Soluciones
1. ModuleNotFoundError
Error:
ModuleNotFoundError: No module named 'pandas'
Solución:
# Asegúrate de que tu entorno virtual esté activado# Windows:
excel_env\Scripts\activate
# macOS/Linux:
source excel_env/bin/activate
# Luego reinstala
pip install pandas
2. KeyError con Nombres de Columna
Error:
KeyError: 'Year-Month'
Solución: Comprueba los nombres exactos de las columnas (distinguen entre mayúsculas y minúsculas):
import pandas as pd
df = pd.read_excel('tu_archivo.xlsx')
print("Nombres de columna reales:")
print(df.columns.tolist())
# Corrige los nombres de las columnas si es necesario
df.columns = df.columns.str.replace(' ', '_')
✅ Usa siempre entornos virtuales – Aísla las dependencias del proyecto ✅ Usa administradores de contexto – with pd.ExcelWriter() asegura el cierre adecuado del archivo ✅ Añade manejo de errores – Usa bloques try-except para un código robusto ✅ Comprueba los nombres de las columnas – Distinguen entre mayúsculas y minúsculas ✅ Cierra los archivos de Excel – Antes de ejecutar scripts de Python ✅ Usa nombres de variables significativos – Hace que el código se autodocumente ✅ Añade indicadores de progreso – Para operaciones de larga duración ✅ Control de versiones – Usa Git para rastrear cambios ✅ Documenta tu código – Añade docstrings y comentarios ✅ Prueba incrementalmente – Ejecuta el código paso a paso al depurar
Ejemplo de Flujo de Trabajo Completo
Así es como se ejecuta todo de principio a fin:
# 1. Configurar el entorno (solo una vez)
python -m venv excel_env
source excel_env/bin/activate # o excel_env\Scripts\activate en Windows
pip install -r requirements.txt
# 2. Crear datos de muestra
python scripts/create_sample_data.py
# 3. Ejecutar operaciones básicas
python scripts/merge_excel_files.py
python scripts/multi_sheet_writer.py
# 4. Ejecutar análisis avanzado
python scripts/smart_analysis.py
# 5. Comprobar los archivos generados
ls *.xlsx
ls excel_files/*.xlsx
Próximos Pasos y Recursos
Documentación Oficial
La documentación de pandas está disponible en pandas.pydata.org
Visualización de Datos: Añade gráficos usando xlsxwriter
Formato: Aplica formato condicional y estilos
Fórmulas: Añade fórmulas de Excel programáticamente
Rendimiento: Maneja archivos grandes con troceado (chunking)
Automatización: Programa scripts con cron/Programador de Tareas
Ideas de Proyectos
Generador de dashboards de ventas
Informes mensuales automatizados
Verificador de calidad de datos
Rellenador de plantillas de Excel
Consolidador de múltiples archivos
Conclusión
Ahora tienes una base completa para la automatización de Excel con Python. Esta guía ha cubierto:
Configuración adecuada del entorno con entornos virtuales
Creación y manipulación de archivos de Excel con múltiples hojas
Análisis avanzado usando SciPy para obtener información estadística
Manejo de errores y solución de problemas completos
Recuerda siempre consultar la documentación oficial para obtener la información más actualizada y practica con tus propios datos para consolidar estos conceptos.
