MartinRcromo/Cross-Selling-SQLite
0
1# app.py — Detector de Canastas Llenas (Versión con DB y Unificación CUIT)2# requirements.txt: streamlit, pandas, numpy, plotly, openpyxl3 4import io5import re6import sqlite37import pandas as pd8import numpy as np9import streamlit as st10import plotly.express as px11import plotly.graph_objects as go12from datetime import datetime13from pathlib import Path14 15# -----------------------------16# Config17# -----------------------------18st.set_page_config(19 page_title="Canastas Llenas | Cross-Selling", 20 layout="wide",21 initial_sidebar_state="expanded",22 menu_items={'About': "Sistema de análisis de venta cruzada"}23)24 25# Ruta de la base de datos26DB_PATH = Path("ventas_data.db")27 28# -----------------------------29# CSS Minimalista (mismo que antes)30# -----------------------------31st.markdown("""32<style>33 @import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&display=swap');34 * { font-family: 'Inter', -apple-system, BlinkMacSystemFont, sans-serif; }35 #MainMenu {visibility: hidden;}36 footer {visibility: hidden;}37 .stApp { background: #fafafa; }38 h1 { font-weight: 700; font-size: 2.2rem; color: #1a1a1a; letter-spacing: -0.02em; margin-bottom: 0.3rem; }39 h2 { font-weight: 600; font-size: 1.5rem; color: #2d2d2d; letter-spacing: -0.01em; margin-top: 2rem; margin-bottom: 1rem; }40 h3 { font-weight: 600; font-size: 1.1rem; color: #404040; margin-bottom: 0.8rem; }41 [data-testid="stMetricValue"] { font-size: 1.8rem; font-weight: 600; color: #1a1a1a; }42 [data-testid="stMetricLabel"] { font-size: 0.85rem; font-weight: 500; color: #666; text-transform: uppercase; letter-spacing: 0.05em; }43 [data-testid="stDataFrame"] { border: 1px solid #e5e5e5; border-radius: 8px; overflow: hidden; }44 .stTextInput > div > div > input { border-radius: 6px; border: 1px solid #e0e0e0; padding: 0.6rem 0.8rem; font-size: 0.95rem; }45 .stSelectbox > div > div > div { border-radius: 6px; border: 1px solid #e0e0e0; }46 .stButton > button { border-radius: 6px; padding: 0.5rem 1.5rem; font-weight: 500; font-size: 0.9rem; border: none; background: #1a1a1a; color: white; transition: all 0.2s; }47 .stButton > button:hover { background: #333; box-shadow: 0 2px 8px rgba(0,0,0,0.15); }48 .stDownloadButton > button { border-radius: 6px; padding: 0.5rem 1.5rem; font-weight: 500; border: 1px solid #e0e0e0; background: white; color: #1a1a1a; }49 .stDownloadButton > button:hover { border-color: #1a1a1a; background: #f5f5f5; }50 .stTabs [data-baseweb="tab-list"] { gap: 2rem; border-bottom: 1px solid #e5e5e5; }51 .stTabs [data-baseweb="tab"] { padding: 0.8rem 0; font-weight: 500; font-size: 0.95rem; color: #666; border-bottom: 2px solid transparent; }52 .stTabs [aria-selected="true"] { color: #1a1a1a; border-bottom-color: #1a1a1a; }53 [data-testid="stSidebar"] { background: white; border-right: 1px solid #e5e5e5; }54 .streamlit-expanderHeader { font-weight: 500; font-size: 0.95rem; color: #404040; border-radius: 6px; background: #f8f8f8; padding: 0.6rem 1rem; }55 .info-box { background: #f0f7ff; border-left: 3px solid #0066cc; padding: 1rem 1.2rem; border-radius: 4px; margin: 1rem 0; font-size: 0.9rem; color: #1a1a1a; }56 .success-box { background: #f0fdf4; border-left: 3px solid #16a34a; padding: 1rem 1.2rem; border-radius: 4px; margin: 1rem 0; font-size: 0.9rem; color: #1a1a1a; }57 .warning-box { background: #fffbeb; border-left: 3px solid #f59e0b; padding: 1rem 1.2rem; border-radius: 4px; margin: 1rem 0; font-size: 0.9rem; color: #1a1a1a; }58 .unified-badge { display: inline-block; background: #e8f5e9; color: #2e7d32; padding: 0.2rem 0.6rem; border-radius: 4px; font-size: 0.8rem; font-weight: 500; margin-left: 0.5rem; }59 .block-container { padding-top: 2rem; padding-bottom: 3rem; max-width: 1400px; }60 hr { margin: 2rem 0; border: none; border-top: 1px solid #e5e5e5; }61</style>62""", unsafe_allow_html=True)63 64 65# -----------------------------66# Funciones de Base de Datos67# -----------------------------68def init_database():69 """Inicializa la base de datos SQLite"""70 conn = sqlite3.connect(DB_PATH)71 cursor = conn.cursor()72 73 # Tabla principal de ventas74 cursor.execute("""75 CREATE TABLE IF NOT EXISTS ventas (76 id INTEGER PRIMARY KEY AUTOINCREMENT,77 fuente TEXT,78 empresa TEXT,79 cliente_id TEXT,80 cliente TEXT,81 cuit TEXT,82 vendedor TEXT,83 subrubro TEXT,84 articulo_codigo TEXT,85 articulo_descripcion TEXT,86 importe REAL,87 unidades REAL,88 cant_pedidos REAL,89 anio_mes TEXT,90 fecha_carga TIMESTAMP DEFAULT CURRENT_TIMESTAMP91 )92 """)93 94 # Índices para mejor performance95 cursor.execute("CREATE INDEX IF NOT EXISTS idx_cuit ON ventas(cuit)")96 cursor.execute("CREATE INDEX IF NOT EXISTS idx_empresa ON ventas(empresa)")97 cursor.execute("CREATE INDEX IF NOT EXISTS idx_vendedor ON ventas(vendedor)")98 cursor.execute("CREATE INDEX IF NOT EXISTS idx_subrubro ON ventas(subrubro)")99 100 conn.commit()101 conn.close()102 103 104def save_to_database(df: pd.DataFrame, fuente: str, replace: bool = False):105 """Guarda datos en la base de datos"""106 conn = sqlite3.connect(DB_PATH)107 108 if replace:109 # Borrar datos anteriores de esta fuente110 conn.execute("DELETE FROM ventas WHERE fuente = ?", (fuente,))111 112 # Seleccionar solo las columnas que existen en la tabla de la BD113 columnas_db = [114 'fuente', 'empresa', 'cliente_id', 'cliente', 'cuit', 'vendedor', 115 'subrubro', 'articulo_codigo', 'articulo_descripcion',116 'importe', 'unidades', 'cant_pedidos', 'anio_mes'117 ]118 119 df_copy = df.copy()120 df_copy['fuente'] = fuente121 122 # Seleccionar solo columnas que están en la BD123 columnas_disponibles = [col for col in columnas_db if col in df_copy.columns or col == 'fuente']124 df_to_save = df_copy[columnas_disponibles]125 126 # Guardar en DB127 df_to_save.to_sql('ventas', conn, if_exists='append', index=False)128 129 conn.close()130 131 132def load_from_database(fuente: str = None) -> pd.DataFrame:133 """Carga datos desde la base de datos"""134 conn = sqlite3.connect(DB_PATH)135 136 if fuente:137 query = "SELECT * FROM ventas WHERE fuente = ?"138 df = pd.read_sql_query(query, conn, params=(fuente,))139 else:140 df = pd.read_sql_query("SELECT * FROM ventas", conn)141 142 conn.close()143 return df144 145 146def get_database_stats():147 """Obtiene estadísticas de la base de datos"""148 if not DB_PATH.exists():149 return None150 151 conn = sqlite3.connect(DB_PATH)152 cursor = conn.cursor()153 154 stats = {}155 156 # Total de registros157 cursor.execute("SELECT COUNT(*) FROM ventas")158 stats['total_registros'] = cursor.fetchone()[0]159 160 # Por fuente161 cursor.execute("SELECT fuente, COUNT(*) FROM ventas GROUP BY fuente")162 stats['por_fuente'] = dict(cursor.fetchall())163 164 # Por empresa165 cursor.execute("SELECT empresa, COUNT(*) FROM ventas WHERE empresa != 'SIN_EMPRESA' GROUP BY empresa")166 stats['por_empresa'] = dict(cursor.fetchall())167 168 # Última actualización169 cursor.execute("SELECT MAX(fecha_carga) FROM ventas")170 stats['ultima_actualizacion'] = cursor.fetchone()[0]171 172 conn.close()173 return stats174 175 176def clear_database():177 """Limpia toda la base de datos"""178 conn = sqlite3.connect(DB_PATH)179 conn.execute("DELETE FROM ventas")180 conn.commit()181 conn.close()182 183 184# -----------------------------185# Helpers: lectura + normalización186# -----------------------------187def _normalize_columns(df: pd.DataFrame) -> pd.DataFrame:188 df = df.copy()189 df.columns = (190 df.columns.astype(str)191 .str.strip()192 .str.lower()193 .str.replace(r"\s+", "_", regex=True)194 .str.replace("$", "", regex=False)195 )196 return df197 198 199def _to_number(series: pd.Series) -> pd.Series:200 s = series.astype(str).str.strip()201 s = s.str.replace("\u00a0", "", regex=False).str.replace(" ", "", regex=False)202 mask_comma = s.str.contains(",", regex=False)203 s.loc[mask_comma] = (204 s.loc[mask_comma]205 .str.replace(".", "", regex=False)206 .str.replace(",", ".", regex=False)207 )208 s.loc[~mask_comma] = s.loc[~mask_comma].str.replace(",", "", regex=False)209 s = s.str.replace(r"[^\d\.\-]", "", regex=True)210 return pd.to_numeric(s, errors="coerce")211 212 213def read_any_table(uploaded_file) -> pd.DataFrame:214 name = (uploaded_file.name or "").lower()215 216 if name.endswith(".xlsx") or name.endswith(".xls"):217 uploaded_file.seek(0)218 df = pd.read_excel(uploaded_file)219 return _normalize_columns(df)220 221 uploaded_file.seek(0)222 raw = uploaded_file.read()223 encodings = ["utf-8-sig", "utf-8", "latin1", "utf-16"]224 seps = [";", ",", "\t", "|"]225 226 last_err = None227 for enc in encodings:228 for sep in seps:229 try:230 df = pd.read_csv(io.BytesIO(raw), sep=sep, encoding=enc)231 if df.shape[1] <= 1:232 continue233 return _normalize_columns(df)234 except Exception as e:235 last_err = e236 continue237 raise RuntimeError(f"No pude leer el archivo. Último error: {last_err}")238 239 240REQUIRED = {241 "cliente": ["cliente", "razonsocial", "razon_social", "razon", "cliente_nombre"],242 "cliente_id": ["cliente_id", "cliente_codigo", "codigo_cliente", "id_cliente"],243 "cuit": ["cuit", "cuit_cliente", "tax_id"],244 "empresa": ["empresa", "company", "compania", "compañia"],245 "vendedor": ["vendedor", "cod_vendedor", "codigo_vendedor", "seller"],246 "subrubro": ["subrubro", "articulo_sub_rubro", "articulo_subrubro", "sub_rubro", "subrubros"],247 "importe": ["importe", "importe_", "importe__"],248 "unidades": ["unidades", "unidad", "qty", "cantidad"],249 "cant_pedidos": ["cant_pedidos", "cantidad_pedidos", "cant_pedido", "pedidos"],250 "anio_mes": ["anio_mes", "año_mes", "periodo", "mes", "year_month"],251 "articulo_codigo": ["articulo_codigo", "articulo_cod", "codigo_articulo", "sku", "articulo"],252 "articulo_descripcion": ["articulo_descripcion", "descripcion", "articulo_desc", "producto"],253}254 255 256def pick_col(df_cols, candidates):257 for c in candidates:258 if c in df_cols:259 return c260 return None261 262 263def standardize(df: pd.DataFrame) -> pd.DataFrame:264 df = df.copy()265 cols = set(df.columns)266 267 mapping = {}268 for std_name, candidates in REQUIRED.items():269 col = pick_col(cols, candidates)270 if col:271 mapping[std_name] = col272 273 if "subrubro" not in mapping:274 raise ValueError("Falta columna de subrubro.")275 if "cuit" not in mapping:276 raise ValueError("Falta columna de CUIT (necesaria para unificación de clientes).")277 if "articulo_codigo" not in mapping or "articulo_descripcion" not in mapping:278 raise ValueError("Faltan columnas de artículo.")279 280 df = df.rename(columns={v: k for k, v in mapping.items()})281 282 # Limpiar CUIT (quitar guiones, espacios)283 df["cuit"] = df["cuit"].astype(str).str.replace("-", "").str.replace(" ", "").str.strip()284 285 # Si no hay cliente, usar cliente_id286 if "cliente" not in df.columns and "cliente_id" in df.columns:287 df["cliente"] = df["cliente_id"].astype(str)288 289 # Si no hay cliente ni cliente_id, usar CUIT290 if "cliente" not in df.columns:291 df["cliente"] = df["cuit"]292 293 if "cliente_id" not in df.columns:294 df["cliente_id"] = ""295 296 if "empresa" not in df.columns:297 df["empresa"] = "SIN_EMPRESA"298 299 if "vendedor" not in df.columns:300 df["vendedor"] = "SIN_VENDEDOR"301 302 # Limpiar strings303 df["cliente"] = df["cliente"].astype(str).str.strip()304 df["cliente_id"] = df["cliente_id"].astype(str).str.strip()305 df["empresa"] = df["empresa"].astype(str).str.strip()306 df["vendedor"] = df["vendedor"].astype(str).str.strip()307 df["subrubro"] = df["subrubro"].astype(str).str.strip()308 df["articulo_codigo"] = df["articulo_codigo"].astype(str).str.strip()309 df["articulo_descripcion"] = df["articulo_descripcion"].astype(str).str.strip()310 311 # Convertir números312 df["importe"] = _to_number(df.get("importe", pd.Series([0] * len(df)))).fillna(0.0)313 df["unidades"] = _to_number(df.get("unidades", pd.Series([0] * len(df)))).fillna(0.0)314 df["cant_pedidos"] = _to_number(df.get("cant_pedidos", pd.Series([1] * len(df)))).fillna(1.0)315 316 if "anio_mes" in df.columns:317 df["anio_mes"] = df["anio_mes"].astype(str).str.strip()318 319 # Filtrar filas vacías320 df = df[321 df["cuit"].ne("") & df["cuit"].ne("nan")322 & df["subrubro"].ne("")323 & df["articulo_codigo"].ne("")324 ]325 326 return df327 328 329def unify_by_cuit(df: pd.DataFrame) -> pd.DataFrame:330 """331 Unifica clientes por CUIT.332 Agrupa todos los cliente_id y razones sociales bajo el mismo CUIT.333 """334 df = df.copy()335 336 # Agrupar por CUIT para obtener todos los IDs y razones sociales337 cuit_groups = df.groupby('cuit').agg({338 'cliente_id': lambda x: ' | '.join(sorted(set(str(i) for i in x if str(i) not in ['', 'nan']))),339 'cliente': lambda x: ' / '.join(sorted(set(str(c) for c in x if str(c) not in ['', 'nan'])))340 }).reset_index()341 342 cuit_groups = cuit_groups.rename(columns={343 'cliente_id': 'cliente_ids_unificados',344 'cliente': 'razones_sociales_unificadas'345 })346 347 # Merge de vuelta al dataframe original348 df = df.drop(columns=['cliente_id', 'cliente'], errors='ignore')349 df = df.merge(cuit_groups, on='cuit', how='left')350 351 # Renombrar para mantener compatibilidad352 df = df.rename(columns={353 'razones_sociales_unificadas': 'cliente',354 'cliente_ids_unificados': 'cliente_id'355 })356 357 return df358 359 360def fmt_int(n: int) -> str:361 return f"{int(n):,}".replace(",", ".")362 363 364def fmt_money(n: float) -> str:365 return f"${float(n):,.0f}".replace(",", "X").replace(".", ",").replace("X", ".")366 367 368def metric_col_name(rank_metric: str) -> str:369 return {"importe": "importe", "unidades": "unidades", "pedidos": "cant_pedidos"}[rank_metric]370 371 372def normalize_period(s: str) -> str:373 s = (s or "").strip()374 s = s.replace("/", "-")375 m = re.match(r"^(\d{4})-(\d{1,2})$", s)376 if m:377 y, mo = m.group(1), int(m.group(2))378 return f"{y}{mo:02d}"379 m2 = re.match(r"^(\d{6})$", s)380 if m2:381 return s382 digs = re.sub(r"\D", "", s)383 if len(digs) == 6:384 return digs385 return s386 387 388def export_to_excel(dataframes_dict, filename="analisis.xlsx"):389 output = io.BytesIO()390 with pd.ExcelWriter(output, engine='openpyxl') as writer:391 for sheet_name, df in dataframes_dict.items():392 df.to_excel(writer, sheet_name=sheet_name[:31], index=False)393 output.seek(0)394 return output395 396 397# -----------------------------398# Modelo (cache)399# -----------------------------400@st.cache_data(show_spinner=False)401def build_model(df: pd.DataFrame):402 # Unificar por CUIT ANTES de construir el modelo403 df = unify_by_cuit(df)404 405 agg_cs = df.groupby(["cuit", "cliente", "subrubro"], as_index=False).agg(406 cant_pedidos=("cant_pedidos", "sum"),407 unidades=("unidades", "sum"),408 importe=("importe", "sum"),409 )410 411 pivot_pedidos = agg_cs.pivot_table(412 index="cuit",413 columns="subrubro",414 values="cant_pedidos",415 aggfunc="sum",416 fill_value=0.0,417 )418 419 X_bin = (pivot_pedidos > 0).astype(np.float32).values420 norms = np.linalg.norm(X_bin, axis=1)421 norms[norms == 0] = 1.0422 423 clients = pivot_pedidos.index.to_numpy() # Ahora son CUITs424 subrubros = pivot_pedidos.columns.to_numpy()425 426 co = pd.DataFrame(X_bin.T @ X_bin, index=subrubros, columns=subrubros)427 freq = pivot_pedidos.sum(axis=0).sort_values(ascending=False)428 429 has_period = "anio_mes" in df.columns430 if has_period:431 df = df.copy()432 df["anio_mes_norm"] = df["anio_mes"].map(normalize_period)433 434 return df, agg_cs, pivot_pedidos, co, freq, X_bin, norms, clients, subrubros, has_period435 436 437def recommend_for_client(client_cuit: str, pivot: pd.DataFrame, co: pd.DataFrame, freq: pd.Series, topk=10):438 if client_cuit not in pivot.index:439 return pd.DataFrame(columns=["subrubro", "score_cooc", "freq_global"])440 441 owned = pivot.loc[client_cuit]442 bought = owned[owned > 0].index.tolist()443 not_bought = owned[owned == 0].index.tolist()444 445 if len(bought) == 0:446 rec = freq.head(topk).reset_index()447 rec.columns = ["subrubro", "freq_global"]448 rec["score_cooc"] = np.nan449 return rec[["subrubro", "score_cooc", "freq_global"]]450 451 scores = co.loc[not_bought, bought].sum(axis=1)452 rec = pd.DataFrame({453 "subrubro": scores.index,454 "score_cooc": scores.values,455 "freq_global": freq.reindex(scores.index).fillna(0).values,456 }).sort_values(["score_cooc", "freq_global"], ascending=False)457 458 return rec.head(topk)459 460 461def recommend_for_subrubro(subrubro: str, co: pd.DataFrame, freq: pd.Series, topk=10):462 if subrubro not in co.index:463 return pd.DataFrame(columns=["subrubro", "score_cooc", "freq_global"])464 scores = co.loc[subrubro].drop(index=subrubro).sort_values(ascending=False).head(topk)465 rec = pd.DataFrame({466 "subrubro": scores.index, 467 "score_cooc": scores.values, 468 "freq_global": freq.reindex(scores.index).fillna(0).values469 })470 return rec471 472 473def top_products_global(df: pd.DataFrame, subrubro: str, rank_metric: str, topn: int, has_period: bool):474 metric_col = metric_col_name(rank_metric)475 dfx = df[df["subrubro"] == subrubro].copy()476 477 agg = (478 dfx.groupby(["articulo_codigo", "articulo_descripcion"], as_index=False)479 .agg(importe=("importe", "sum"), unidades=("unidades", "sum"), pedidos=("cant_pedidos", "sum"))480 .sort_values(metric_col, ascending=False)481 .head(topn)482 )483 484 if has_period and "anio_mes_norm" in dfx.columns:485 last_m = (486 dfx.groupby("articulo_codigo", as_index=False)["anio_mes_norm"]487 .max()488 .rename(columns={"anio_mes_norm": "ultimo_mes"})489 )490 agg = agg.merge(last_m, on="articulo_codigo", how="left")491 492 return agg493 494 495def top_products_similar_clients(496 df: pd.DataFrame,497 selected_cuit: str,498 subrubro: str,499 rank_metric: str,500 topn: int,501 pivot: pd.DataFrame,502 X_bin: np.ndarray,503 norms: np.ndarray,504 clients: np.ndarray,505 has_period: bool,506 neighbors_n=50,507 exclude_already_bought=True,508):509 if selected_cuit not in pivot.index:510 return pd.DataFrame(columns=["articulo_codigo", "articulo_descripcion", "score", "vecinos", "importe", "unidades", "pedidos"])511 512 idx = np.where(clients == selected_cuit)[0][0]513 vec = X_bin[idx]514 515 dots = X_bin @ vec516 sims = dots / (norms * norms[idx])517 sims[idx] = -1518 519 top_neighbors_idx = np.argsort(-sims)[:neighbors_n]520 neighbor_cuits = clients[top_neighbors_idx]521 522 dfx = df[df["cuit"].isin(neighbor_cuits) & (df["subrubro"] == subrubro)].copy()523 524 metric_col = metric_col_name(rank_metric)525 agg = (526 dfx.groupby(["articulo_codigo", "articulo_descripcion"], as_index=False)527 .agg(importe=("importe", "sum"), unidades=("unidades", "sum"), pedidos=("cant_pedidos", "sum"), vecinos=("cuit", "nunique"))528 .sort_values(metric_col, ascending=False)529 )530 531 if exclude_already_bought:532 already = df[(df["cuit"] == selected_cuit) & (df["subrubro"] == subrubro)]["articulo_codigo"].unique()533 agg = agg[~agg["articulo_codigo"].isin(already)]534 535 agg["score"] = agg[metric_col] * np.log1p(agg["vecinos"])536 agg = agg.sort_values("score", ascending=False).head(topn)537 538 if has_period and "anio_mes_norm" in dfx.columns:539 last_m = (540 dfx.groupby("articulo_codigo", as_index=False)["anio_mes_norm"]541 .max()542 .rename(columns={"anio_mes_norm": "ultimo_mes"})543 )544 agg = agg.merge(last_m, on="articulo_codigo", how="left")545 546 return agg547 548 549def detect_inactive_clients(df: pd.DataFrame, months_inactive: int = 3):550 """551 Detecta clientes que dejaron de comprar en los últimos N meses552 """553 if "anio_mes_norm" not in df.columns:554 return pd.DataFrame()555 556 # Obtener el período más reciente557 max_period = df["anio_mes_norm"].max()558 559 # Convertir período a número para poder restar meses560 year = int(max_period[:4])561 month = int(max_period[4:])562 563 # Calcular período de corte (restar meses)564 cutoff_month = month - months_inactive565 cutoff_year = year566 while cutoff_month <= 0:567 cutoff_month += 12568 cutoff_year -= 1569 570 cutoff_period = f"{cutoff_year}{cutoff_month:02d}"571 572 # Clientes activos en el período reciente573 active_clients = df[df["anio_mes_norm"] >= cutoff_period]["cuit"].unique()574 575 # Todos los clientes históricos576 all_clients = df["cuit"].unique()577 578 # Clientes inactivos = todos - activos579 inactive_cuits = set(all_clients) - set(active_clients)580 581 if len(inactive_cuits) == 0:582 return pd.DataFrame()583 584 # Obtener info de clientes inactivos585 inactive_df = df[df["cuit"].isin(inactive_cuits)].copy()586 587 # Última compra de cada cliente inactivo588 last_purchase = (589 inactive_df.groupby(["cuit", "cliente"])590 .agg({591 "anio_mes_norm": "max",592 "importe": "sum",593 "subrubro": lambda x: list(x.unique())594 })595 .reset_index()596 .rename(columns={597 "anio_mes_norm": "ultima_compra",598 "importe": "importe_historico",599 "subrubro": "subrubros_comprados"600 })601 )602 603 # Contar cuántos meses inactivo604 last_purchase["meses_inactivo"] = last_purchase["ultima_compra"].apply(605 lambda x: (int(max_period[:4]) - int(x[:4])) * 12 + (int(max_period[4:]) - int(x[4:]))606 )607 608 return last_purchase.sort_values("importe_historico", ascending=False)609 610 611def get_client_favorite_products(df: pd.DataFrame, cuit: str, subrubro: str = None, top_n: int = 5):612 """613 Obtiene los productos favoritos de un cliente (opcionalmente filtrado por subrubro)614 """615 client_df = df[df["cuit"] == cuit].copy()616 617 if subrubro:618 client_df = client_df[client_df["subrubro"] == subrubro]619 620 if len(client_df) == 0:621 return pd.DataFrame()622 623 products = (624 client_df.groupby(["articulo_codigo", "articulo_descripcion", "subrubro"], as_index=False)625 .agg({626 "importe": "sum",627 "unidades": "sum",628 "cant_pedidos": "sum"629 })630 .sort_values("importe", ascending=False)631 .head(top_n)632 )633 634 return products635 636 637def get_current_trending_products(df: pd.DataFrame, subrubro: str, months_recent: int = 3, top_n: int = 5):638 """639 Obtiene los productos más vendidos actualmente en un subrubro640 """641 if "anio_mes_norm" not in df.columns:642 # Si no hay período, usar todos los datos643 recent_df = df[df["subrubro"] == subrubro].copy()644 else:645 # Filtrar por período reciente646 max_period = df["anio_mes_norm"].max()647 year = int(max_period[:4])648 month = int(max_period[4:])649 650 cutoff_month = month - months_recent651 cutoff_year = year652 while cutoff_month <= 0:653 cutoff_month += 12654 cutoff_year -= 1655 656 cutoff_period = f"{cutoff_year}{cutoff_month:02d}"657 658 recent_df = df[659 (df["subrubro"] == subrubro) & 660 (df["anio_mes_norm"] >= cutoff_period)661 ].copy()662 663 if len(recent_df) == 0:664 return pd.DataFrame()665 666 products = (667 recent_df.groupby(["articulo_codigo", "articulo_descripcion"], as_index=False)668 .agg({669 "importe": "sum",670 "unidades": "sum",671 "cant_pedidos": "sum"672 })673 .sort_values("importe", ascending=False)674 .head(top_n)675 )676 677 return products678 679 680# ========================================681# INICIO DE LA APP682# ========================================683 684# Inicializar DB685init_database()686 687st.title("🧺 Canastas Llenas")688st.caption("Sistema inteligente de análisis de cross-selling con unificación por CUIT")689 690# ========================================691# SIDEBAR - GESTIÓN DE DATOS692# ========================================693with st.sidebar:694 st.header("Gestión de Datos")695 696 # Estadísticas de la DB697 stats = get_database_stats()698 if stats and stats['total_registros'] > 0:699 st.success(f"✓ BD: {fmt_int(stats['total_registros'])} registros")700 701 with st.expander("Ver detalles"):702 st.caption("**Por Fuente:**")703 for fuente, count in stats['por_fuente'].items():704 st.caption(f"• {fuente}: {fmt_int(count)}")705 706 if stats.get('por_empresa'):707 st.caption("**Por Empresa:**")708 for empresa, count in stats['por_empresa'].items():709 st.caption(f"• {empresa}: {fmt_int(count)}")710 711 if stats['ultima_actualizacion']:712 st.caption(f"**Última actualización:** {stats['ultima_actualizacion'][:16]}")713 else:714 st.info("No hay datos en la base de datos")715 716 st.divider()717 718 # Cargar datos719 st.subheader("Cargar Archivos")720 721 uploaded_cromosol = st.file_uploader("Cromosol", type=["csv", "xlsx", "xls"], key="cromosol")722 uploaded_bba = st.file_uploader("BBA", type=["csv", "xlsx", "xls"], key="bba")723 724 col_load1, col_load2 = st.columns(2)725 726 with col_load1:727 if st.button("💾 Guardar", use_container_width=True):728 if uploaded_cromosol or uploaded_bba:729 saved_info = []730 with st.spinner("Guardando..."):731 if uploaded_cromosol:732 try:733 df_crom = read_any_table(uploaded_cromosol)734 df_crom = standardize(df_crom)735 save_to_database(df_crom, "CROMOSOL", replace=True)736 saved_info.append(f"✓ CROMOSOL: {len(df_crom):,} registros guardados")737 except Exception as e:738 st.error(f"Error al guardar CROMOSOL: {str(e)}")739 740 if uploaded_bba:741 try:742 df_bba = read_any_table(uploaded_bba)743 df_bba = standardize(df_bba)744 save_to_database(df_bba, "BBA", replace=True)745 saved_info.append(f"✓ BBA: {len(df_bba):,} registros guardados")746 except Exception as e:747 st.error(f"Error al guardar BBA: {str(e)}")748 749 if saved_info:750 for info in saved_info:751 st.success(info)752 st.rerun()753 else:754 st.warning("Sube al menos un archivo")755 756 with col_load2:757 if st.button("🗑️ Limpiar BD", use_container_width=True):758 clear_database()759 st.success("✓ BD limpiada")760 st.rerun()761 762 st.divider()763 764 # Cargar desde DB para análisis765 st.subheader("Fuente de Análisis")766 767 fuente_options = ["TODOS"]768 if stats and 'por_fuente' in stats:769 fuente_options.extend(list(stats['por_fuente'].keys()))770 771 fuente_seleccionada = st.selectbox("Datos a analizar", fuente_options, label_visibility="collapsed")772 773# Verificar que haya datos774if not stats or stats['total_registros'] == 0:775 st.markdown('<div class="warning-box">⚠️ No hay datos en la base de datos. Sube archivos desde el sidebar.</div>', unsafe_allow_html=True)776 st.stop()777 778# Cargar datos desde DB779with st.spinner("Cargando datos..."):780 if fuente_seleccionada == "TODOS":781 df = load_from_database()782 else:783 df = load_from_database(fuente_seleccionada)784 785 # Construir modelo (incluye unificación por CUIT)786 df, agg_cs, pivot, co, freq, X_bin, norms, clients, subrubros, has_period = build_model(df)787 788st.markdown('<div class="success-box">✓ Datos cargados y unificados por CUIT</div>', unsafe_allow_html=True)789 790# ========================================791# SIDEBAR - FILTROS792# ========================================793with st.sidebar:794 st.divider()795 st.header("Filtros")796 797 # Filtro de empresa798 st.subheader("Empresa")799 empresas_disponibles = ["TODAS"] + sorted([e for e in df["empresa"].unique().tolist() if e != "SIN_EMPRESA"])800 empresa_seleccionada = st.selectbox("Filtrar por empresa", empresas_disponibles, label_visibility="collapsed")801 802 if empresa_seleccionada != "TODAS":803 df_filtered = df[df["empresa"] == empresa_seleccionada]804 st.caption(f"Empresa: {empresa_seleccionada}")805 806 with st.spinner("Recalculando..."):807 df, agg_cs, pivot, co, freq, X_bin, norms, clients, subrubros, has_period = build_model(df_filtered)808 809 st.divider()810 811 # Filtro de vendedor812 st.subheader("Vendedor")813 vendedores_disponibles = ["TODOS"] + sorted([v for v in df["vendedor"].unique().tolist() if v != "SIN_VENDEDOR"])814 vendedor_seleccionado = st.selectbox("Filtrar por vendedor", vendedores_disponibles, label_visibility="collapsed")815 816 if vendedor_seleccionado != "TODOS":817 df_filtered = df[df["vendedor"] == vendedor_seleccionado]818 st.caption(f"Vendedor: {vendedor_seleccionado}")819 820 with st.spinner("Recalculando..."):821 df, agg_cs, pivot, co, freq, X_bin, norms, clients, subrubros, has_period = build_model(df_filtered)822 823 st.divider()824 825 # Filtro de período826 if has_period and "anio_mes_norm" in df.columns:827 st.subheader("Período")828 periodos_disponibles = sorted(df["anio_mes_norm"].unique())829 830 if len(periodos_disponibles) > 1:831 use_period_filter = st.checkbox("Filtrar por período", value=False)832 833 if use_period_filter:834 periodo_inicio = st.selectbox("Desde", periodos_disponibles, index=0)835 periodo_fin = st.selectbox("Hasta", periodos_disponibles, index=len(periodos_disponibles)-1)836 837 df_filtered = df[(df["anio_mes_norm"] >= periodo_inicio) & (df["anio_mes_norm"] <= periodo_fin)]838 st.caption(f"{periodo_inicio} - {periodo_fin}")839 840 with st.spinner("Recalculando..."):841 df, agg_cs, pivot, co, freq, X_bin, norms, clients, subrubros, has_period = build_model(df_filtered)842 843 st.divider()844 845 # Filtro de importe846 st.subheader("Importe")847 use_importe_filter = st.checkbox("Filtrar por importe mínimo", value=False)848 849 if use_importe_filter:850 importe_min = st.number_input("Importe mínimo", min_value=0.0, value=0.0, step=100.0)851 if importe_min > 0:852 df_filtered = df[df["importe"] >= importe_min]853 st.caption(f"≥ {fmt_money(importe_min)}")854 855 with st.spinner("Recalculando..."):856 df, agg_cs, pivot, co, freq, X_bin, norms, clients, subrubros, has_period = build_model(df_filtered)857 858# ========================================859# MÉTRICAS860# ========================================861# Mostrar filtros activos862filtros_activos = []863if empresa_seleccionada != "TODAS":864 filtros_activos.append(f"🏢 {empresa_seleccionada}")865if vendedor_seleccionado != "TODOS":866 filtros_activos.append(f"👤 {vendedor_seleccionado}")867if fuente_seleccionada != "TODOS":868 filtros_activos.append(f"📁 {fuente_seleccionada}")869 870if filtros_activos:871 st.markdown(f"**Filtros activos:** {' | '.join(filtros_activos)}")872 873col1, col2, col3, col4 = st.columns(4)874col1.metric("Filas", fmt_int(len(df)))875col2.metric("Clientes (por CUIT)", fmt_int(df["cuit"].nunique()))876col3.metric("Subrubros", fmt_int(df["subrubro"].nunique()))877col4.metric("Importe Total", fmt_money(df['importe'].sum()))878 879st.divider()880 881# ========================================882# TABS PRINCIPALES883# ========================================884tab_dashboard, tab_cliente, tab_subrubro, tab_inactivos, tab_heatmap = st.tabs([885 "Dashboard", 886 "Por Cliente", 887 "Por Subrubro",888 "Clientes Inactivos",889 "Heatmap"890])891 892# TAB 1: DASHBOARD893with tab_dashboard:894 st.subheader("Dashboard Ejecutivo")895 896 col_left, col_right = st.columns(2)897 898 with col_left:899 st.markdown("##### Top 10 Subrubros")900 top_subrubros = (901 df.groupby("subrubro", as_index=False)902 .agg(importe=("importe", "sum"), unidades=("unidades", "sum"), clientes=("cuit", "nunique"))903 .sort_values("importe", ascending=False)904 .head(10)905 )906 907 top_subrubros_sorted = top_subrubros.sort_values("importe", ascending=True)908 909 fig_top_sub = px.bar(910 top_subrubros_sorted,911 x="importe",912 y="subrubro",913 orientation="h",914 labels={"importe": "Importe", "subrubro": ""},915 color="importe",916 color_continuous_scale=[[0, '#f0f0f0'], [1, '#1a1a1a']]917 )918 fig_top_sub.update_layout(919 showlegend=False, height=400, margin=dict(l=0, r=0, t=20, b=0),920 plot_bgcolor='white', paper_bgcolor='white',921 font=dict(family='Inter', size=11, color='#404040')922 )923 fig_top_sub.update_xaxes(showgrid=True, gridcolor='#f0f0f0')924 fig_top_sub.update_yaxes(showgrid=False)925 st.plotly_chart(fig_top_sub, use_container_width=True)926 927 with col_right:928 st.markdown("##### Top 10 Clientes (por CUIT)")929 top_clientes = (930 df.groupby(["cuit", "cliente"], as_index=False)931 .agg(importe=("importe", "sum"), subrubros=("subrubro", "nunique"))932 .sort_values("importe", ascending=False)933 .head(10)934 )935 936 top_clientes_sorted = top_clientes.sort_values("importe", ascending=True)937 938 # Acortar nombres largos para el gráfico939 top_clientes_sorted['cliente_display'] = top_clientes_sorted['cliente'].str[:40]940 941 fig_top_cli = px.bar(942 top_clientes_sorted,943 x="importe",944 y="cliente_display",945 orientation="h",946 labels={"importe": "Importe", "cliente_display": ""},947 color="importe",948 color_continuous_scale=[[0, '#f0f0f0'], [1, '#1a1a1a']]949 )950 fig_top_cli.update_layout(951 showlegend=False, height=400, margin=dict(l=0, r=0, t=20, b=0),952 plot_bgcolor='white', paper_bgcolor='white',953 font=dict(family='Inter', size=11, color='#404040')954 )955 fig_top_cli.update_xaxes(showgrid=True, gridcolor='#f0f0f0')956 fig_top_cli.update_yaxes(showgrid=False)957 st.plotly_chart(fig_top_cli, use_container_width=True)958 959 st.divider()960 961 st.markdown("##### Oportunidades de Cross-Selling")962 co_avg = co.mean(axis=1).sort_values(ascending=False).head(5)963 oportunidades = pd.DataFrame({964 "Subrubro": co_avg.index,965 "Score Co-ocurrencia": co_avg.values.round(1),966 "Clientes": [len(pivot[pivot[s] > 0]) for s in co_avg.index]967 })968 969 st.dataframe(oportunidades, use_container_width=True, hide_index=True, height=250)970 971 if st.button("Exportar Dashboard", use_container_width=True):972 export_data = {973 "Top_Subrubros": top_subrubros,974 "Top_Clientes": top_clientes,975 "Oportunidades": oportunidades976 }977 excel_file = export_to_excel(export_data)978 st.download_button(979 "Descargar Excel",980 excel_file,981 f"dashboard_{datetime.now().strftime('%Y%m%d')}.xlsx",982 "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",983 use_container_width=True984 )985 986 if has_period and "anio_mes_norm" in df.columns:987 st.divider()988 st.markdown("##### Evolución Temporal")989 990 evolucion = (991 df.groupby("anio_mes_norm", as_index=False)992 .agg(importe=("importe", "sum"))993 .sort_values("anio_mes_norm")994 )995 996 fig_evol = go.Figure()997 fig_evol.add_trace(go.Scatter(998 x=evolucion["anio_mes_norm"],999 y=evolucion["importe"],1000 mode='lines',1001 line=dict(color='#1a1a1a', width=2),1002 fill='tozeroy',1003 fillcolor='rgba(26,26,26,0.1)'1004 ))1005 fig_evol.update_layout(1006 height=300, margin=dict(l=0, r=0, t=20, b=0),1007 plot_bgcolor='white', paper_bgcolor='white',1008 xaxis_title="", yaxis_title="Importe",1009 font=dict(family='Inter', size=11, color='#404040')1010 )1011 fig_evol.update_xaxes(showgrid=False)1012 fig_evol.update_yaxes(showgrid=True, gridcolor='#f0f0f0')1013 st.plotly_chart(fig_evol, use_container_width=True)1014 1015# TAB 2: POR CLIENTE (con unificación por CUIT)1016with tab_cliente:1017 st.subheader("Análisis por Cliente (Unificado por CUIT)")1018 1019 # DEBUG: Mostrar estadísticas de clientes1020 with st.expander("🔍 Debug: Ver estadísticas de clientes"):1021 st.caption(f"Total de CUITs únicos en BD: {df['cuit'].nunique()}")1022 st.caption(f"Total de registros: {len(df)}")1023 1024 # Mostrar sample de clientes1025 sample_clientes = df.groupby('cuit').agg({1026 'cliente': 'first',1027 'cliente_id': 'first'1028 }).reset_index().head(10)1029 st.dataframe(sample_clientes, use_container_width=True)1030 1031 # Preparar base de clientes únicos por CUIT1032 base = (1033 df.groupby("cuit")1034 .agg({1035 'cliente': 'first', # Razones sociales unificadas1036 'cliente_id': 'first' # IDs unificados1037 })1038 .reset_index()1039 )1040 1041 st.caption(f"Clientes disponibles: {len(base)}")1042 1043 def make_label(r):1044 parts = [str(r["cliente"])[:50]] # Limitar largo1045 if str(r.get("cliente_id", "")).strip() and str(r.get("cliente_id", "")).lower() != "nan":1046 ids = str(r['cliente_id']).split(' | ')1047 if len(ids) > 1:1048 parts.append(f"IDs: {' | '.join(ids[:3])}{'...' if len(ids) > 3 else ''}")1049 else:1050 parts.append(f"ID: {ids[0]}")1051 parts.append(f"CUIT: {r['cuit']}")1052 return " | ".join(parts)1053 1054 base["label"] = base.apply(make_label, axis=1)1055 1056 q = st.text_input("Buscar cliente", placeholder="Razón Social, ID o CUIT...", label_visibility="collapsed")1057 q_norm = q.strip().lower()1058 1059 if q_norm:1060 # Buscar en label, cliente, cliente_id y cuit1061 mask = (1062 base["label"].str.lower().str.contains(re.escape(q_norm), na=False) |1063 base["cliente"].str.lower().str.contains(re.escape(q_norm), na=False) |1064 base["cliente_id"].str.lower().str.contains(re.escape(q_norm), na=False) |1065 base["cuit"].str.contains(re.escape(q_norm), na=False)1066 )1067 options = base.loc[mask, "label"].tolist()1068 if not options:1069 st.warning(f"No se encontraron clientes con '{q}'. Probá con otra búsqueda.")1070 st.caption(f"Búsqueda realizada en: Razón Social, ID Cliente, CUIT")1071 else:1072 options = base["label"].tolist()1073 1074 selected_label = st.selectbox("Cliente", options=options, index=0 if options else None, label_visibility="collapsed")1075 if not selected_label:1076 st.stop()1077 1078 # Extraer CUIT del label1079 selected_cuit = selected_label.split("CUIT: ")[-1].strip()1080 1081 # Obtener info del cliente1082 client_info = base[base['cuit'] == selected_cuit].iloc[0]1083 1084 # Mostrar badge de unificación si tiene múltiples IDs1085 if ' | ' in str(client_info['cliente_id']):1086 st.markdown(f'<span class="unified-badge">🔗 Cliente Unificado ({len(str(client_info["cliente_id"]).split(" | "))} IDs)</span>', unsafe_allow_html=True)1087 1088 left, right = st.columns([1.1, 0.9], gap="large")1089 1090 with left:1091 st.markdown("##### Subrubros que compra")1092 sub = (1093 agg_cs[agg_cs["cuit"] == selected_cuit]1094 .groupby("subrubro", as_index=False)1095 .agg(cant_pedidos=("cant_pedidos", "sum"), unidades=("unidades", "sum"), importe=("importe", "sum"))1096 .sort_values("importe", ascending=False)1097 )1098 1099 if len(sub) > 0:1100 sub_sorted = sub.head(10).sort_values("importe", ascending=True)1101 1102 fig_sub = px.bar(1103 sub_sorted,1104 x="importe",1105 y="subrubro",1106 orientation="h",1107 labels={"importe": "Importe", "subrubro": ""},1108 color="importe",1109 color_continuous_scale=[[0, '#f0f0f0'], [1, '#1a1a1a']]1110 )1111 fig_sub.update_layout(1112 showlegend=False, height=350, margin=dict(l=0, r=0, t=10, b=0),1113 plot_bgcolor='white', paper_bgcolor='white',1114 font=dict(family='Inter', size=10, color='#404040')1115 )1116 fig_sub.update_xaxes(showgrid=True, gridcolor='#f0f0f0')1117 fig_sub.update_yaxes(showgrid=False)1118 st.plotly_chart(fig_sub, use_container_width=True)1119 1120 sub_show = sub.copy()1121 sub_show["importe"] = sub_show["importe"].map(fmt_money)1122 sub_show["unidades"] = sub_show["unidades"].round(0).astype(int)1123 sub_show["cant_pedidos"] = sub_show["cant_pedidos"].round(0).astype(int)1124 sub_show = sub_show.rename(columns={"cant_pedidos": "pedidos"})1125 st.dataframe(sub_show, use_container_width=True, hide_index=True, height=300)1126 1127 with right:1128 st.markdown("##### Sugerencias de Venta Cruzada")1129 1130 rank_metric = st.radio("Rankear por", ["importe", "unidades", "pedidos"], horizontal=True, label_visibility="collapsed")1131 top_subrubros = st.slider("Subrubros", 3, 25, 10)1132 1133 rec = recommend_for_client(selected_cuit, pivot, co, freq, topk=top_subrubros)1134 rec_show = rec.copy()1135 rec_show["score_cooc"] = rec_show["score_cooc"].fillna(0).astype(int)1136 rec_show["freq_global"] = rec_show["freq_global"].fillna(0).astype(int)1137 st.dataframe(rec_show.rename(columns={"score_cooc": "score"}), use_container_width=True, hide_index=True, height=350)1138 1139 st.markdown("##### Plan de Acción")1140 1141 source_mode = st.radio("Fuente", ["Clientes similares", "Global"], horizontal=True, label_visibility="collapsed")1142 top_products = st.slider("Productos", 3, 30, 10)1143 neighbors_n = st.slider("Clientes similares", 10, 200, 50, 10)1144 exclude_bought = st.checkbox("Excluir ya comprados", value=True)1145 1146 for _, row in rec.head(top_subrubros).iterrows():1147 sr = row["subrubro"]1148 score = int(row["score_cooc"]) if pd.notna(row["score_cooc"]) else 01149 1150 with st.expander(f"{sr} — score {score}"):1151 if source_mode.startswith("Clientes"):1152 g = top_products_similar_clients(1153 df=df, selected_cuit=selected_cuit, subrubro=sr,1154 rank_metric=rank_metric, topn=top_products, pivot=pivot,1155 X_bin=X_bin, norms=norms, clients=clients, has_period=has_period,1156 neighbors_n=neighbors_n, exclude_already_bought=exclude_bought1157 )1158 1159 show = g.copy()1160 if "importe" in show.columns:1161 show["importe"] = show["importe"].map(fmt_money)1162 show["unidades"] = show["unidades"].round(0).astype(int)1163 show["pedidos"] = show["pedidos"].round(0).astype(int)1164 if "score" in show.columns:1165 show["score"] = show["score"].round(2)1166 1167 cols = ["articulo_codigo", "articulo_descripcion", "score", "vecinos", "importe", "unidades", "pedidos"]1168 if has_period and "ultimo_mes" in show.columns:1169 cols.append("ultimo_mes")1170 1171 st.dataframe(show[cols], use_container_width=True, hide_index=True, height=280)1172 1173 else:1174 g = top_products_global(df=df, subrubro=sr, rank_metric=rank_metric, topn=top_products, has_period=has_period)1175 1176 show = g.copy()1177 show["importe"] = show["importe"].map(fmt_money)1178 show["unidades"] = show["unidades"].round(0).astype(int)1179 show["pedidos"] = show["pedidos"].round(0).astype(int)1180 1181 cols = ["articulo_codigo", "articulo_descripcion", "importe", "unidades", "pedidos"]1182 if has_period and "ultimo_mes" in show.columns:1183 cols.append("ultimo_mes")1184 1185 st.dataframe(show[cols], use_container_width=True, hide_index=True, height=280)1186 1187 st.divider()1188 1189 # Mostrar info de unificación1190 if ' | ' in str(client_info['cliente_id']):1191 with st.expander("📋 Ver detalles de unificación"):1192 st.caption("**IDs de Cliente Unificados:**")1193 for id_cliente in str(client_info['cliente_id']).split(' | '):1194 st.text(f"• {id_cliente}")1195 1196 st.caption("**Razones Sociales:**")1197 for razon in str(client_info['cliente']).split(' / '):1198 st.text(f"• {razon}")1199 1200 if st.button("Exportar Análisis del Cliente", use_container_width=True):