File size: 5,317 Bytes
f120be8 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 |
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
Script para mostrar datos con gráficas ASCII en la terminal
Visualización profesional sin necesidad de navegador
"""
import sqlite3
import os
from datetime import datetime
def get_project_root():
"""Obtener ruta del proyecto"""
script_dir = os.path.dirname(os.path.abspath(__file__))
return os.path.join(script_dir, '..')
def crear_barra_progreso(valor, max_valor=100, ancho=30):
"""Crear barra de progreso ASCII"""
if max_valor == 0:
porcentaje = 0
else:
porcentaje = (valor / max_valor) * 100
barra_llena = int((porcentaje / 100) * ancho)
barra_vacia = ancho - barra_llena
barra = f"[{'█' * barra_llena}{'░' * barra_vacia}] {porcentaje:.0f}%"
return barra
def mostrar_datos_con_graficas():
"""Mostrar datos con gráficas ASCII"""
os.chdir(get_project_root())
conn = sqlite3.connect('data/sentiment_analysis.db')
conn.row_factory = sqlite3.Row
cursor = conn.cursor()
# Obtener estadísticas
cursor.execute('SELECT COUNT(*) as count FROM customer_profiles')
total_clientes = cursor.fetchone()[0]
cursor.execute('SELECT COUNT(*) as count FROM conversations')
total_analyses = cursor.fetchone()[0]
cursor.execute('SELECT AVG(sentiment_score) as avg FROM conversations')
avg_sentiment = cursor.fetchone()[0] or 0
cursor.execute('SELECT COUNT(*) as count FROM risk_alerts WHERE resolved = 0')
active_alerts = cursor.fetchone()[0]
cursor.execute('''
SELECT customer_id, churn_risk, lifetime_sentiment, total_interactions
FROM customer_profiles
ORDER BY churn_risk DESC
''')
clientes = cursor.fetchall()
# Mostrar header
print("\n" + "="*70)
print(" SENTIMENT EVOLUTION TRACKER - DASHBOARD EJECUTIVO")
print("="*70 + "\n")
# Estadísticas principales
print("📊 ESTADÍSTICAS GENERALES")
print("-" * 70)
print(f" Total Clientes: {total_clientes}")
print(f" Total Análisis: {total_analyses}")
print(f" Alertas Activas: {active_alerts}")
print(f" Sentimiento Promedio: {avg_sentiment:.1f}/100")
print()
# Gráfica de sentimiento promedio
print("📈 SENTIMIENTO PROMEDIO (0-100)")
print("-" * 70)
sentimiento_barra = crear_barra_progreso(avg_sentiment, 100, 40)
color_sentimiento = "🟢" if avg_sentiment > 60 else "🟡" if avg_sentiment > 40 else "🔴"
print(f" {color_sentimiento} {sentimiento_barra}")
print()
# Tabla de clientes con gráficas
print("👥 CLIENTES POR RIESGO DE CHURN")
print("-" * 70)
print(f"{'ID':<15} {'RIESGO':<25} {'SENTIM':<12} {'INTERACT':<10}")
print("-" * 70)
for cliente in clientes:
customer_id = cliente['customer_id'][:12]
churn_risk = cliente['churn_risk']
sentiment = cliente['lifetime_sentiment']
interactions = cliente['total_interactions']
# Barra de riesgo
riesgo_barra = crear_barra_progreso(churn_risk * 100, 100, 20)
# Icono de riesgo
if churn_risk > 0.7:
icon = "🔴"
elif churn_risk > 0.5:
icon = "🟡"
else:
icon = "🟢"
print(f"{customer_id:<15} {icon} {riesgo_barra:<24} {sentiment:>6.1f}/100 {interactions:>8}")
print()
print("="*70)
print(" Leyenda: 🔴 Alto Riesgo (>70%) 🟡 Medio Riesgo (>50%) 🟢 Bajo Riesgo")
print("="*70 + "\n")
# Alertas detalladas
print("⚠️ ALERTAS ACTIVAS")
print("-" * 70)
cursor.execute('''
SELECT customer_id, alert_type, severity, created_at
FROM risk_alerts
WHERE resolved = 0
ORDER BY created_at DESC
''')
alerts = cursor.fetchall()
if alerts:
for alert in alerts:
severity_icon = "🔴" if alert['severity'] == 'HIGH' else "🟡"
print(f" {severity_icon} [{alert['severity']}] {alert['customer_id']}: {alert['alert_type']}")
print(f" Creada: {alert['created_at']}")
else:
print(" ✅ Sin alertas activas")
print()
# Análisis recientes
print("📋 ÚLTIMOS 5 ANÁLISIS")
print("-" * 70)
cursor.execute('''
SELECT customer_id, sentiment_score, trend, predicted_action, analysis_date
FROM conversations
ORDER BY analysis_date DESC
LIMIT 5
''')
recent = cursor.fetchall()
for i, analysis in enumerate(recent, 1):
sentiment_icon = "📈" if analysis['trend'] == 'RISING' else "📉" if analysis['trend'] == 'DECLINING' else "➡️ "
action_icon = "🚨" if analysis['predicted_action'] == 'CHURN' else "✅" if analysis['predicted_action'] == 'RESOLUTION' else "⚠️ "
print(f" {i}. {analysis['customer_id']}")
print(f" {sentiment_icon} Sentimiento: {analysis['sentiment_score']:.0f}/100 | Tendencia: {analysis['trend']}")
print(f" {action_icon} Acción: {analysis['predicted_action']}")
print(f" Fecha: {analysis['analysis_date']}")
print()
print("="*70 + "\n")
conn.close()
if __name__ == "__main__":
mostrar_datos_con_graficas()
|