20 casos de uso prácticos para trabajar con datos dentro de Excel, SQL, Access, CSV estructurados, APIs y tablas administrativas, sin enfocarnos en carpetas ni nombres de archivos.
20 practical use cases for working with data inside Excel, SQL, Access, structured CSVs, APIs, and administrative tables, without focusing on folders or file names.
PowerShell es excelente para automatizar tareas del sistema, mover archivos y preparar carpetas. Python, en cambio, brilla cuando la data ya está dentro de tablas: Excel, SQL Server, Access, formularios, reportes exportados, listas de empleados, presupuestos, catálogos, encuestas y datasets administrativos.
La idea no es reemplazar Excel, Power BI o SQL, sino crear una capa inteligente de limpieza, validación y transformación que reduzca trabajo manual, errores humanos y tiempo de preparación.
PowerShell is excellent for automating system tasks, moving files, and preparing folders. Python shines when the data already lives inside tables: Excel, SQL Server, Access, exported reports, employee lists, budgets, catalogs, surveys, and administrative datasets.
The goal is not to replace Excel, Power BI, or SQL, but to create an intelligent cleaning, validation, and transformation layer that reduces manual work, human error, and preparation time.
Convierte encabezados como “Employee ID”, “employee_id”, “Emp Id” o “ID Empleado” en un estándar único.
Convert headers such as “Employee ID”, “employee_id”, “Emp Id”, or “ID Empleado” into one consistent standard.
import pandas as pd
df = pd.read_excel("employees.xlsx")
df.columns = (
df.columns
.str.strip()
.str.lower()
.str.replace(" ", "_")
.str.replace("-", "_")
)
print(df.columns)Identifica registros que no deberían pasar a producción porque les falta Employee_ID, email, fecha o departamento.
Identify records that should not move forward because they are missing Employee_ID, email, date, or department.
required = ["employee_id", "email", "department"]
missing_report = df[df[required].isna().any(axis=1)]
print(missing_report[required])Marca correos mal escritos o que no pertenecen al dominio esperado.
Flag malformed emails or emails outside the expected domain.
df["email_valid"] = df["email"].str.match(
r"^[A-Za-z0-9._%+-]+@miempresa\.com$",
na=False
)
invalid_emails = df[~df["email_valid"]]Convierte fechas escritas de varias formas en una columna confiable para análisis.
Convert dates written in different formats into one reliable analysis column.
df["hire_date_clean"] = pd.to_datetime(
df["hire_date"],
errors="coerce"
)
bad_dates = df[df["hire_date_clean"].isna()]Extrae una tabla o consulta SQL directamente hacia Python para validarla antes de llevarla a BI.
Extract a table or SQL query directly into Python for validation before sending it to BI.
import pandas as pd
import pyodbc
conn = pyodbc.connect(
"DRIVER={ODBC Driver 17 for SQL Server};"
"SERVER=SERVERNAME;"
"DATABASE=HR;"
"Trusted_Connection=yes;"
)
df = pd.read_sql("""
SELECT Employee_ID, Department, Salary
FROM dbo.EmployeeMaster
""", conn)Compara una lista externa en Excel contra una tabla oficial en SQL para encontrar diferencias.
Compare an external Excel list against an official SQL table to find differences.
excel = pd.read_excel("local_list.xlsx")
sql = pd.read_sql("SELECT Employee_ID FROM dbo.EmployeeMaster", conn)
review = excel.merge(
sql,
on="Employee_ID",
how="left",
indicator=True
)
not_in_sql = review[review["_merge"] == "left_only"]Permite auditar datos históricos o pequeñas aplicaciones internas que todavía viven en Access.
Audit historical data or small internal applications that still live in Access.
import pyodbc
import pandas as pd
access_conn = pyodbc.connect(
r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
r"DBQ=C:\Data\LegacyApp.accdb;"
)
df_access = pd.read_sql("SELECT * FROM Employees", access_conn)No siempre un duplicado es solo el mismo ID. Puedes evaluar nombre, fecha de nacimiento y email juntos.
A duplicate is not always just the same ID. You can evaluate name, birth date, and email together.
duplicates = df[df.duplicated(
subset=["first_name", "last_name", "birth_date"],
keep=False
)]
duplicates = duplicates.sort_values(
["last_name", "first_name", "birth_date"]
)Convierte una columna de nombre completo en first name, middle name y last name para sistemas que lo requieren separado.
Convert a full name column into first name, middle name, and last name for systems that require separate fields.
name_parts = df["full_name"].str.strip().str.split(" ", expand=True)
df["first_name"] = name_parts[0]
df["last_name"] = name_parts[name_parts.columns[-1]]Agrega columnas que indiquen qué registros necesitan revisión humana.
Add columns that show which records need human review.
df["needs_review"] = (
df["employee_id"].isna() |
df["email"].isna() |
(df["status"] == "Inactive")
)
review_queue = df[df["needs_review"]]Estandariza textos antes de clasificarlos o analizarlos en Power BI.
Standardize text responses before classification or Power BI analysis.
df["answer_clean"] = (
df["answer"]
.astype(str)
.str.strip()
.str.replace(r"\s+", " ", regex=True)
)
df = df[df["answer_clean"].str.lower() != "nan"]Crea una primera taxonomía automática usando palabras clave antes de pasar a una revisión más profunda.
Create a first automated taxonomy using keywords before deeper review.
def classify_comment(text):
text = str(text).lower()
if "training" in text or "learn" in text:
return "Training"
if "workday" in text or "system" in text:
return "Technology"
if "process" in text or "approval" in text:
return "Process"
return "Other"
df["cluster"] = df["answer_clean"].apply(classify_comment)Genera tablas agregadas listas para cargar en Power BI o Excel.
Generate aggregated tables ready to load into Power BI or Excel.
summary = (
df.groupby(["department", "cluster"])
.size()
.reset_index(name="record_count")
.sort_values("record_count", ascending=False)
)
summary.to_excel("dashboard_summary.xlsx", index=False)Encuentra pagos, compras o presupuestos que se salen de los límites esperados.
Find payments, purchases, or budget items outside expected limits.
df["amount"] = pd.to_numeric(df["amount"], errors="coerce")
outliers = df[
(df["amount"] < 0) |
(df["amount"] > 10000)
]Ejemplo: ciertos programas solo permiten ciertas modalidades, campus o códigos.
Example: certain programs only allow specific modalities, campuses, or codes.
allowed = {
"Nursing": ["Onsite"],
"Data Analytics": ["Online", "Hybrid"],
"Patient Care": ["Onsite", "Hybrid"]
}
df["valid_program_mode"] = df.apply(
lambda r: r["modality"] in allowed.get(r["program"], []),
axis=1
)
invalid = df[~df["valid_program_mode"]]Completa datos faltantes consultando una API pública o interna.
Fill missing values by querying a public or internal API.
import requests
def get_zip_info(zip_code):
url = f"https://api.zippopotam.us/us/{zip_code}"
response = requests.get(url, timeout=10)
if response.status_code == 200:
return response.json()["places"][0]["place name"]
return None
df["city_from_zip"] = df["zip"].apply(get_zip_info)Corrige variaciones como “HR”, “Human Resources” y “Human Res.” en un solo valor oficial.
Correct variations like “HR”, “Human Resources”, and “Human Res.” into one official value.
mapping = {
"HR": "Human Resources",
"Human Res.": "Human Resources",
"IT": "Information Technology",
"Info Tech": "Information Technology"
}
df["department_clean"] = df["department"].replace(mapping)Detecta cambios entre la versión de ayer y la de hoy: nuevos registros, eliminados o modificados.
Detect changes between yesterday’s version and today’s version: new, removed, or modified records.
old = pd.read_excel("employees_old.xlsx")
new = pd.read_excel("employees_new.xlsx")
comparison = new.merge(
old,
on="employee_id",
how="outer",
indicator=True,
suffixes=("_new", "_old")
)
new_records = comparison[comparison["_merge"] == "left_only"]
removed_records = comparison[comparison["_merge"] == "right_only"]Entrega un Excel profesional con data limpia, errores, resumen y auditoría.
Deliver a professional Excel workbook with clean data, errors, summary, and audit sheets.
with pd.ExcelWriter("data_quality_report.xlsx") as writer:
df.to_excel(writer, sheet_name="Clean_Data", index=False)
invalid_emails.to_excel(writer, sheet_name="Invalid_Emails", index=False)
missing_report.to_excel(writer, sheet_name="Missing_Values", index=False)
summary.to_excel(writer, sheet_name="Summary", index=False)Convierte pasos repetitivos en una función que puedas usar cada semana o cada mes.
Turn repetitive steps into a function you can reuse every week or month.
def clean_employee_data(path):
df = pd.read_excel(path)
df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")
df["email"] = df["email"].str.lower().str.strip()
df["hire_date"] = pd.to_datetime(df["hire_date"], errors="coerce")
df["needs_review"] = df[["employee_id", "email", "hire_date"]].isna().any(axis=1)
return df
clean = clean_employee_data("employees.xlsx")
clean.to_excel("employees_clean.xlsx", index=False)