lección 3
Día 2 — Limpiar, y decidir tú qué es el margen
Con el mapa en la cabeza, hoy tocas los datos: fechas en tres formatos (una con el mes en inglés), primas e importes con «€», descuentos con «%», canal y ramo y estado en muchas grafías, pólizas y siniestros duplicados, outliers, y los dos vacíos con significado. Y la decisión que no es de limpieza sino de criterio, la que Rubén no te va a dar: qué es el «margen» de un canal, la métrica que gobierna toda la semana.
⏱ 60 min
### Un dato español que viene en inglés
Ayer contaste los vacíos, las grafías y los duplicados; hoy los arreglas. Y empiezas por lo que más despista a quien nunca ha limpiado datos de varias fuentes: una de las tres formas en que vienen las fechas trae el mes en inglés abreviado, «12 Mar 2024» en lugar de «12/03/2024» o «2024-03-12». No es un error del que exportó: es que Stripe, la plataforma de siniestros y las herramientas de los comparadores son internacionales, y por dentro trabajan en inglés aunque Ampara sea de Madrid y sus clientes escriban en español. Es una lección real del oficio: los datos vienen en el idioma de la herramienta que los generó, no en el idioma del país.
Ya sabes de tus encargos anteriores que una fecha guardada como texto no sirve para nada: no puedes ordenar por ella, ni restar dos para saber cuánto duró una póliza, ni agrupar por mes. El primer paso de la limpieza es siempre convertir el texto en fechas de verdad. La forma robusta en Pandas es pd.to_datetime con format='mixed' (que le dice «cada fila puede venir en un formato distinto, apáñatelas») y dayfirst=True (que aclara que «03/12/2024» es 3 de diciembre, formato español, no 12 de marzo, formato americano). Ese dayfirst importa: sin él, medio dataset se interpretaría del revés. Y tiene una consecuencia práctica que hay que conocer: las funciones que convierten texto en fecha entienden los meses en inglés («Mar», «Dec»), no en español, porque las escribió gente que trabajaba en inglés. Un «12 Mar 2024» se parsea sin problema; un hipotético «12 Dic 2024» daría un vacío. Por suerte, aquí el mes viene en inglés, que es justo lo que la función espera.
### Los euros que son texto y los porcentajes que son texto
La prima y el importe de los siniestros vienen como texto: «480,00 €», «1.250,00 €», «12.480,00 €». Mientras sean texto no puedes sumarlos ni promediarlos, y si lo intentas, Pandas o te da error o —peor— concatena cadenas. Convertirlos a número tiene truco en España, porque nuestro formato usa la coma como separador decimal y el punto como separador de miles, justo al revés que el formato con el que trabajan los números por dentro. El orden seguro es: quitar el símbolo del euro y los espacios, quitar los puntos de los miles, y solo entonces cambiar la coma decimal por un punto. Si te saltas el paso de quitar los puntos de miles, «1.250,00» se convierte en «1.250» y luego en 1,25 en lugar de 1.250: un error de tres órdenes de magnitud que dispararía cualquier suma.
El descuento trae el mismo problema en pequeño: viene como «15 %», texto con el símbolo de porcentaje y un espacio. Se limpia igual —quitar el símbolo y el espacio, convertir a número— y se queda como un número (15, no 0,15; el contexto manda, y aquí lo dejamos como el porcentaje entero). El descuento no es crítico para el hallazgo, pero conviene tenerlo limpio: más adelante comprobarás si el descuento distingue a los canales o si es otra variable uniforme (te adelanto que lo segundo).
Al convertir texto a número usa siempre errors='coerce', que convierte en vacío (NaN) lo que no se pueda parsear en lugar de reventar con un error. Pero cuidado: eso significa que un valor imposible se convierte en vacío silenciosamente, así que después de convertir hay que contar cuántos vacíos aparecieron y decidir qué hacer con ellos. Aquí la prima tiene 15 valores imposibles (cero o negativos) que hay que excluir del análisis con un filtro prima > 0, y el importe de los siniestros tiene outliers (el clásico 9.999.999 € y algún negativo por recobro) que se excluyen con importe > 0 y importe < 1.000.000. Convertir no es limpiar: convertir y luego validar el rango sí lo es.
Este orden importa tanto que merece grabarlo: en el formato español, el punto separa los miles y la coma los decimales, exactamente al revés que el formato interno de los números. Si haces la sustitución de la coma por punto antes de quitar los puntos de miles, te quedan dos puntos en «1.250.00» y la conversión falla o trunca. El único orden seguro es quitar primero el símbolo del euro y los espacios, después los puntos de miles, y en último lugar convertir la coma decimal en punto. Es la clase de detalle que no da error rojo pero envenena silenciosamente una suma de millones, y por eso conviene tener una función de limpieza de números escrita una vez y reutilizada en todas las columnas monetarias: la prima, el importe de los siniestros, el coste de las campañas y la franquicia de los productos.
### El campo del que depende todo: normalizar el canal
Ayer viste que el canal tiene 22 grafías para 5 canales reales. Hoy las unificas, y este es el paso más importante de toda la limpieza, porque de la columna canal cuelga cada número que vas a calcular esta semana. La técnica es un diccionario de mapeo: una tabla que dice a qué canal canónico corresponde cada grafía. Primero se normaliza la forma —quitar espacios con .str.strip() y pasar a minúsculas con .str.lower()— para que «Comparadores» y «comparadores» sean lo mismo, y luego se traduce cada grafía a su canónico con .map(diccionario). El mismo patrón sirve para el ramo (mapeando la columna «nombre» de productos, que ayer descubriste que es donde vive el ramo real), para el estado y para el tipo de póliza.
Consejo de senior: cuando normalices con .map(diccionario), comprueba siempre que no queda ningún vacío nuevo. Si una grafía que no habías previsto no está en el diccionario, .map() la convierte en NaN, y esa póliza desaparece de tu segmentación sin avisar. La forma de comprobarlo es contar los vacíos de la columna después de mapear: si aparecen huecos que antes no estaban, es que se te escapó una grafía. Medir el desorden antes (ayer, con nunique) y comprobar el resultado después (hoy) es lo que evita descubrir a media semana que llevabas días contando el 4% de las pólizas de menos.
### Limpiar lo justo: no todo merece el mismo esfuerzo
Hay más columnas sucias, y aquí es donde el criterio separa a un analista de un robot que limpia todo por igual. El estado de la póliza viene en varias formas (vigente, Vigente, activa son lo mismo; anulada, cancelada, baja son otro); el tipo (nueva, alta; renovacion, renov, renovada); el estado del siniestro (pagado, Pagado, cerrado; abierto, en tramite); el fraude (si, Sí, 1, true frente a no, No, 0, false). Todas se normalizan con el mismo patrón que el canal: minúsculas, quitar espacios, mapear a un valor canónico. Pero no todas importan igual. El tipo de póliza es crítico (de él depende separar altas de renovaciones), el fraude importa para descartarlo como explicación, y el estado de la póliza es casi decorativo para tu encargo. Un buen analista limpia a fondo lo que va a usar y no gasta una hora en pulir una columna que no toca.
Esta idea —limpiar lo justo— no es pereza, es economía de esfuerzo. Los datos perfectos no existen, y perseguir la perfección en cada columna es la mejor forma de no terminar nunca. La pregunta correcta no es «¿está limpia esta columna?», sino «¿necesito esta columna para responder mi pregunta, y está lo bastante limpia para eso?». El canal tiene que estar impecable porque de él cuelga toda la segmentación; el ramo, porque es el control de confusión; la prima y el importe, porque se suman. La zona del mediador, el nombre de la campaña o el sexo del cliente pueden quedarse a medias sin que tu hallazgo se resienta. Saber dónde poner el esfuerzo es parte del oficio, y se aprende preguntándose siempre qué decisión depende de cada dato.
Cuidado con el orden al normalizar el fraude a booleano. Los valores vienen como texto («si», «Sí», «1», «true», «no», «No», «0», «false»), y el error clásico es convertir a booleano de forma que un «0» de texto se interprete como verdadero (porque una cadena no vacía es «verdadera» en muchos lenguajes). Lo seguro es decidir explícitamente qué valores son verdaderos —«si», «sí», «1», «true»— y marcar todo lo demás como falso, en lugar de confiar en una conversión automática. Un fraude mal normalizado te haría descartar o contar mal siniestros, y aunque el fraude sea ruido en este caso, un error así puede pasar inadvertido y contaminar otras cifras.
### La decisión del día: qué es el «margen» de un canal
Y aquí llega lo que de verdad separa este encargo de los anteriores. Rubén no te ha dado la definición de la métrica central, y no lo va a hacer: quiere que la decidas tú y la defiendas ante Marta. La pregunta es «¿qué canal deja más margen?», y «margen» no viene definido en ninguna tabla. Tienes que construirlo. Y no hay una única respuesta correcta: hay una defendible. Puedes mirar el canal por pólizas o por prima suscrita (lo que hace Álex), por loss ratio, por combined ratio, por margen técnico por póliza, por retención. La que aguanta ante una CEO que sabe de seguros combina las cuatro piezas que viste el primer día en una sola cifra por póliza.
La definición defendible es esta, y conviene escribirla antes de tocar una consulta: el margen de un canal es el margen técnico medio de sus pólizas —prima menos siniestros menos gastos menos captación—, apoyado en su loss ratio, su combined ratio y su tasa de renovación, y su aportación al conjunto es el margen total que suma o resta a Ampara. En fórmulas: el combined ratio es el loss ratio más el ratio de gastos (un 22% fijo en Ampara) más el CAC dividido entre la prima media; el margen por póliza es la prima media por (1 menos el combined ratio); y el margen total del canal es ese margen por póliza multiplicado por el número de pólizas del canal. Medir el canal por pólizas o por prima suscrita —lo que hace Distribución— es justo el error que ha cometido la empresa hasta hoy.
Con la definición escrita, hoy no calculas todavía el margen —eso es SQL, y llega a partir de mañana—. Hoy dejas los datos listos y sacas la foto de superficie: cuántas pólizas nuevas trae cada canal. Y aquí hay dos decisiones de limpieza que son en realidad decisiones de análisis, y que Hugo ya te avisó. La primera: qué es una póliza nueva. Solo las de tipo «nueva» (no las renovaciones) y con prima válida (prima > 0), porque una renovación no es una captación y una prima cero es basura. La segunda: los dos vacíos con significado se conservan. El mediador_id vacío se queda (es venta digital) y mañana se cruzará con LEFT JOIN; el siniestro sin fecha de cierre se queda (sigue costando). Tirarlos sería el error que invierte el análisis.
Consejo de senior: define la métrica en una frase antes de escribir una sola consulta. «El margen de un canal es el margen técnico medio de sus pólizas —prima menos siniestros, gastos y captación—, y su aportación es el margen total que suma o resta al conjunto.» Si no puedes decirlo en una frase que Marta entienda, no lo tienes claro tú, y un número que no sabes explicar no cambia ninguna decisión. La definición es la mitad del trabajo; el SQL es la otra mitad, y la fácil.
Cierras el día con los datos limpios y la métrica definida. Mañana empiezas a calcular, y lo harás en SQL sobre las tablas ya saneadas, que es como trabaja el equipo: la exploración y la limpieza se hacen en Pandas, pero el análisis —los números que acaban en el panel— se produce en SQL. Y empezarás por donde Álex quiere que empieces: confirmándole que, por pólizas, prima y CAC, los comparadores ganan de calle. Le vas a dar la razón. Durante un día entero, el titular de Álex va a parecer inatacable.
Regístrate para guardar tu progreso.