Tus datos más limpios...(II). Excel, "Waterproof"

20 de junio de 2017
Como ya anunciamos en el post anterior , en este segundo vamos a trabajar sobre un ejemplo práctico. Aunque hay muchas herramientas especializadas en Data Cleansing que pueden ser más eficientes, hemos elegido empezar por Excel porque es una herramienta muy sencilla, accesible y para la que hay multitud de recursos de ayuda. Al mismo tiempo, es una herramienta muy potente. Por tanto, es una excelente elección para aquellos que se inician en este campo.

Cuando trabajamos con tantos datos, es fundamental trabajar de forma sistemática y ordenada. Aunque a primera vista parezca que esto nos puede ralentizar, a la larga veremos que sale a cuenta. Por ello, crearemos una hoja de Excel para cada paso lógico, y la etiquetaremos.
  • Datos originales
  • Datos de trabajo
  • Datos limpios
Nuestro objetivo es mantener los datos significativos y eliminar todo aquello que dificulte su análisis o afecte a la calidad de los resultados estadísticos que obtengamos.

¿Qué tipo de cosas queremos eliminar?

  • Registros duplicados
  • Caracteres extraños
  • Valores fuera de rango
También nos puede interesar realizar acciones como:
  • Sustituir caracteres, por ejemplo, ‘Madrid’ y ‘MAD’
  • Seleccionar partes del contenido de una celda, por ejemplo, el código de área de un número de teléfono como 001 (206) 123 4567
  • Combinar celdas, por ejemplo, si los campos “nombre” y “apellidos” están en dos columnas separadas
  • Distribuir el contenido de una celda en diferentes columnas (operación inversa a la anterior)

Vamos a trabajar sobre un dataset de ejemplo procedente del archivo de datos abiertos del Gobierno de Canarias. Este conjunto de datos describe la observación de cetáceos por parte de empresas autorizadas en las costas de las islas. Dado que los datos son recogidos sobre el terreno por empresas muy diversas, podemos encontrar varias de las situaciones que hemos comentado. Es un excelente ejemplo para limpieza de datos. Así que, ¡vamos a ello!.

Figura 1: Portal de datos abiertos del Gobierno de Canarias. Figura 1: Portal de datos abiertos del Gobierno de Canarias.
Nos descargamos los datos del siguiente enlace. Creamos una “Hoja Nueva”, seleccionando el signo + al que apunta la flecha, y la llamamos “Datos de Trabajo. Sobre ella copiamos los datos originales:



Figura 2: Añadir hoja nueva. Figura 2: Añadir hoja nueva.

#0 Lo ponemos “bonito”. Cabeceras más visibles con función MAYÚSCULA

Empecemos por el más fácil: Ponemos otro color de fondo que nos permita ver mejor las cabeceras y…empezamos a limpiar.

Seleccionamos la fila con los títulos, y, elegimos la opción “Insertar” fila del menú contextual (Botón derecho). Una vez agregada esta fila, vamos a poner los títulos en mayúsculas con la función MAYUSC. Para ello, en la celda vacía escribimos “+” y seleccionamos la función que nos interesa del combo box. Aparecerán las más frecuentes, pero si no está entre ellas, podemos buscarla seleccionando “Más funciones” al final de la lista. En este caso, se trata de una función de texto.


Figura 3: Ejemplo de aplicación de la función MAYUSCULAS. Figura 3: Ejemplo de aplicación de la función MAYUSCULAS.

Una vez damos a “Aceptar”, podemos extender la fórmula por el resto de la fila. Cambiamos el color de relleno al que más nos guste y ocultamos la fila inicial, seleccionando la opción “Ocultar” en el menú contextual, para tener una vista más clara.

Figura 4: Resultado del cambio a mayúsculas y cambio de color. Figura 4: Resultado del cambio a mayúsculas y cambio de color. 
Ahora que lo vemos un poco mejor, vamos a empezar a corregir cosas.


#1 Buscar y Reemplazar.

Es una de las funciones más conocidas y sencillas de aplicar. Nos permite encontrar determinada palabra y sustituirla por otra.
Dentro de las opciones del menú Inicio, seleccionamos “Buscar/ Reemplazar”.


Figura 5: Aplicar Función REEMPLAZAR. Figura 5: Aplicar Función REEMPLAZAR. Como atributos, seleccionamos: Buscar “Mog n”, Reemplazar “Mogan”. Marcamos la opción “Reemplazar todos” y así corrige todos los casos en la columna seleccionada. Esta función también es útil para rellenar campos vacíos, o para sustituir caracteres incorrectos.
  • Por ejemplo, vamos a corregir algunos caracteres que aparecen de forma errónea debido a los acentos: “¢” por “o”, “Tel‚fonos” por “Teléfonos”

También podemos usar esta función para rellenar campos vacíos.
  • En este ejemplo, podemos rellenar los campos “Municipio” que están vacíos con una etiqueta “No registrado”. En las opciones de sustitución hemos elegido un formato diferente (color rojo y cursiva) para detectarlos mejor.
Como resultado, obtenemos esto:

Figura 6: Resultado de reemplazar con un formato diferente. Figura 6: Resultado de reemplazar con un formato diferente.


#2 Combinar/Separar texto.

La función “COMBINAR” es muy útil cuando, por ejemplo, los datos de “nombre” y “apellidos” aparecen en distintas columnas y nos interesa unirlas. En este caso tenemos la situación opuesta. Los campos “Dirección Postal” y “Teléfonos e Internet” contienen información demasiado mezclada. Vamos a separarlos.

Vemos que, en principio, los campos están separados por “;”. El problema es que en unas celdas están todos los campos, y en otras no. También, cuando hay varios números de teléfono, en algunos casos están separados por “,”, y en otros por “-“. Vamos a cambiar estos guiones por comas para que estén todos igual. Después, separaremos la columna de los teléfonos.

Dentro del menú “Datos”, seleccionamos la opción “Texto en Columnas”. Le diremos al asistente que son delimitados por “;” y “C” para separar los teléfonos del resto de datos.

Figura 8: Ejemplo de separar texto en columnas. Figura 8: Ejemplo de separar texto en columnas.
Quitamos la etiqueta “Teléfonos” (con Reemplazar “Teléfono” por un espacio en blanco). Nos queda algo así:

Figura 9: Resultado de aplicar "texto en columnas". Figura 9: Resultado de aplicar "texto en columnas".
Imaginemos ahora que sólo nos interesa conservar uno de los teléfonos. (Sí de acuerdo, no tiene mucho sentido eliminar información, pero este supuesto nos viene muy bien para explicar cómo se aplicaría la siguiente función. Así que lo tomaremos como “Licencia artística”).


#3 Funciones IZQUIERDA/DERECHA

Estas fórmulas nos van a permitir elegir con qué parte concreta del contenido de una celda queremos quedarnos. Por ejemplo, en la columna “Teléfonos” podemos decidir quedarnos únicamente con los primeros 9 caracteres.
Insertamos una nueva columna a la derecha de la columna “Teléfonos”, en la primera celda escribimos el signo “+” y seleccionamos la última opción de la lista de funciones “Más funciones”. En el cuadro “Insertar Función”, seleccionamos la categoría “Funciones de Texto”, y dentro de ella, la función “ IZQUIERDA”. Como parámetros, tenemos que dar el texto de la celda (simplemente seleccionándola) y el número de caracteres que queremos elegir empezando por la izquierda, en este caso, 11 (por los caracteres en blanco).


Figura 10: Ejemplo de aplicación de la función IZQUIERDA. Figura 10: Ejemplo de aplicación de la función IZQUIERDA.

De esta forma, nos quedamos con un único número de teléfono para cada registro. Igual que hemos seleccionados los números empezando a contar caracteres por la izquierda, la función DERECHA nos permite hacerlo empezando a contar por el final.

Figura 11: Resultado. Figura 11: Resultado.
Y ya que hemos separado un campo que puede ser bastante identificativo, ¿qué tal si intentamos localizar datos duplicados?, una de las problemáticas más habituales que podemos encontrar en un dataset.


#4 Eliminar duplicados

Es frecuente encontrarse con registros repetidos, que conviene identificar y, en ocasiones, eliminar. Vamos a ver cómo detectarlos con la función Lógica “SI”.

Añadimos una nueva columna a la derecha de la columna Teléfonos. Vamos introducir en la primera celda una función lógica que compare cada valor con el de la celda superior. Como antes, añadimos en la primera celda el signo “+” para agregar una función y seleccionamos la función lógica “SI”. El argumento de la prueba lógica es la comparación entre el valor de las dos celdas (en este caso J2=J3). Si son iguales se cumplirá la condición “Valor_si_verdadero” y aparecerá una etiqueta “Duplicado”. Si no, lo dejará en blanco.

Figura 12: Ejemplo de aplicación de la función lógica SI. Figura 12: Ejemplo de aplicación de la función lógica SI.
Extendemos la aplicación de la fórmula al resto de la columna (arrastrando hacia abajo) y ¡ya está! Hemos detectado varios registros en los que los teléfonos coinciden, aunque los nombres comerciales registrados no. Puede ser interesante investigar un poco más detalladamente para saber si son los mismos propietarios, si están en activo ambas licencias etc.


Figura 13: Valores duplicados detectados. Figura 13: Valores duplicados detectados.
Recordamos que, al separar los teléfonos, nos quedaron algunos datos de faxes en esa columna, y otra con un batiburrillo de email, fax y página web. Vamos a intentar organizar estos datos. Como los datos de correo ya están bien separados, podemos eliminar el resto de etiqueta “orreos”.


#5 Función CONCATENAR

Observamos que los valores de Fax y Páginas web están distribuidos entre las columnas I, K y L.

Figura 14: Los datos están distribuidos desordenadamente entre distintas columnas. Figura 14: Los datos están distribuidos desordenadamente entre distintas columnas.

Para organizarlos, tendremos que juntarlos todos en una misma columna (usando la función CONCATENAR). Como siempre, añadimos una nueva columna (M), introducimos en la primera celda una función de texto CONCATENAR, seleccionamos como argumentos los valores I2, K2 y L2, y aplicamos el resultado de la fórmula al resto de la columna. Obtenemos el siguiente resultado:


Figura 15: Agrupamos todos los datos en una única columna con la función CONCATENAR.

Ahora, tenemos que volver a separarlos en columnas independientes seleccionando el separador más adecuado. Quitamos las etiquetas “Faxes”, que ya no nos hacen falta (Usando la función REEMPLAZAR que hemos visto anteriormente).

Usaremos esta misma función para convertir la etiqueta “P ginas web” en un separador “*” que nos permita separar los datos relativos a los números de fax de las URLs de las páginas web.

Figura 16: Convertimos la etiqueta P gina web en un separador "*". Figura 16: Convertimos la etiqueta Página web en un separador "*".
Y copiamos la columna resultante y la pegamos como “Pegado Especial: valores” en una columna nueva. De esta forma, podemos aplicar nuevamente la función “Texto en columnas”, usando como separador el carácter “*”.
Ya tenemos separados los número de Fax y las URL. Ocultamos las columnas que no nos interesan, ponemos los títulos correctos y, ya sólo nos queda un pequeño detalle.

#6 ESPACIOS

Por último, observamos que la columna “Faxes” los números están muy descolocados.

Figura 17: Datos separados, pero con espacios blancos sobrantes. Figura 17: Datos separados, pero con espacios blancos sobrantes.

Usaremos la función ESPACIOS para eliminar los espacios en blanco sobrantes.

Figura 18: Ejemplo de la función ESPACIOS para eliminar espacios en blanco sobrantes. Figura 18: Ejemplo de la función ESPACIOS para eliminar espacios en blanco sobrantes.

Et voilá, ya está listo. Guardamos los datos finales, con todos los detalles ya corregidos en la Hoja Final y cerramos el proceso de limpieza. Los datos limpios pueden ya procesarse con los otros conjuntos de datos procedentes de otras fuentes y ser incorporados al paquete estadístico.

¿Entendéis ahora por qué la mayor parte del tiempo de trabajo de un Data Scientist se van en preparar bien los datos?

Hacerlo con Excel no es complicado, pero puede resultar un poco tedioso y repetitivo. Por eso las herramientas que agilicen estos procesos serán más que bienvenidas.

Por si acaso te perdiste el primer post de esta serie, aquí tienes el enlace: Tus datos más limpios, casi sin frotar.