Cómo limpiar datos con Excel (cuatro funciones que cambiarán tu vida)

Este post fue publicado originalmente en inglés por Noah Veltmann en School of Data, y traducido al español por Aura Montemayor de SocialTIC.

Imagen: Frédéric Bisson

Escuela de Datos retomó la publicación de Noah Veltman para Learning Lunches, un tutorial para periodistas que se dedica a disipar la idea de que las herramientas técnicas son difíciles de usar cuando se trata de obtener datos.

Las primeras herramientas que compara son SQL y Excel.

Para qué es bueno Excel:

Excel tiene una mala reputación, pero lo cierto es que también es un software muy flexible y potente, y en él puedes hacer muchas cosas como las siguientes:

• Navegar por los datos con facilidad.
• Introducir y editar datos manualmente con facilidad.
• Compartir copias de los archivos.
• Escoger la presentación visual.
• Aprovechar la estructura flexible que dan las celdas únicas.
• Integrar tu trabajo con el software de cualquier oficina.
• Aprovechar la vivacidad que da la posibilidad de uso de fórmulas.
• Usar los íconos de ayuda para realizar gráficos, que dan sugerencias y tienen corrector ortográfico.
• Aprender a usarlo de manera relativamente fácil.

Para qué no es bueno Excel:

Desafortunadamente, Excel tiene límites, y es malo para otras cosas como las siguientes:

• No integra los datos, ya que cada celda es única; esto hace que el documento pueda ser muy inconsistente. Un número no tiene necesariamente el formato de un número; así puede haber variaciones entre datos. Debes poner atención a los detalles.

• No es recomendable para trabajar con múltiples bases de datos.

• Cuando los datos llegan a determinada cantidad, el rendimiento del documento se reduce al igual que la interfaz. Las celdas son limitadas.

• Cuando se trata de trabajar en equipo, es muy difícil saber quién modificó el documento y en qué lugar.

Bases de datos relacionadas

¿Qué es una base de datos relacionada? Podríamos ser muy exigentes con la terminología, pero, en términos generales, se trata de un «servidor» que almacena todos tus datos (piensa en una enorme biblioteca) con un mecanismo para que alguien más las pueda consultar (piensa en un bibliotecario).

La consulta SQL (Structured Query Language) es una sintaxis para solicitar cosas desde la base de datos. Es el lenguaje que el bibliotecario habla. Sigue leyendo y te hablaremos más de esto.

La parte de «relacionar» es un indicio de que estas bases de datos se preocupan por las relaciones entre los datos. Y sí: también existen las bases de datos no relacionadas, pero asumiremos por el momento que todos somos amigos.

El mantra de las bases de datos: cada cosa en su lugar

En una base de datos, debes guardar las cosas lógicamente. Incluso, algunas veces ésta te obliga a guardarlos de cierta manera.

Piensa que cada base de datos es como una tabla, y cada tabla corresponde a una hoja de cálculo en un archivo de Excel. Una tabla de base de datos se compone de columnas y filas.

Columnas

A cada columna se le asigna un nombre (como «Dirección» ) y un tipo de columna definida (como  ‘ Fecha ‘, ‘Fecha + Hora ‘ o ‘Texto’) . Tienes que escoger un tipo de columna y mantener el mismo formato para cada fila. La base de datos relacionará a todos los datos que pongas en esa columna bajo ese tipo. Esto podría parecer tedioso y molesto, pero es muy útil. Si trataras de poner datos incorrectos en una columna, Excel marcaría un error hasta que fueran introducidos de manera correcta.

También puede especificar cosas útiles, como las siguientes:

• Si una columna tiene valores duplicados.
• Si una columna está vacía.
• El valor predeterminado de una columna si no especificas tú uno.

Importante: Las columnas definen la estructura de los datos.

Filas

Las filas son los datos reales de la tabla. Una vez que establezcas la estructura de la columna, puedes agregar tantas líneas como desees.

Cada fila tiene un valor para cada columna. Excel es un lienzo visual y te permitirá crear cualquier tipo de celdas y fusionarlas como desees. Incluso puedes hacer formas tan complejas como en un juego de Tetris. Sin embargo, esto no va con la idea de una base de datos donde se prevé una red real. Si dejas celdas vacías de manera intencional, el documento sabe distinguir que esa celda está vacía, y que no es lo mismo que una no existente.

Mesas, combinaciones y llaves

Más adelante retomaremos este tema, pero poner todo en el lugar correcto significa hacer tu vida más sencilla. Puedes hacer y deshacer tus datos para ponerlos en diferentes tablas o categorías y poder trabajar con ellos como un conjunto.

Que los datos sean datos

Las bases de datos sólo se centran en las capas superficiales e ignoran por completo la presentación visual de los datos. Colores, formas, bordes, fuentes… básicamente no existen para las bases de datos. Lo que ves es lo que hay. Eso se puede tomar como una buena noticia, pero también como una mala. La buena es que las bases de datos son excelentes para hacer cálculos; pero la mala es que tienen que ser complementadas con otro programa para crear un producto final, como un gráfico o una página web.

Al ser muy buenas en el almacenamiento y procesamiento de datos y no en otras cosas, las bases de datos son extremadamente escalables. ¿Necesitas 1 millón de filas de datos? ¿10 millones de dólares? No hay problema: no hay prácticamente ningún límite a la cantidad de datos que pueden almacenar.

Bases de datos y web

Las bases de datos son excelentes para hacer análisis preliminares, explorar datos y limpiarlos… Pero son mejores para conectarlos con otros programas una vez que sepas lo que quieres hacer con ellos. Virtualmente, todo el internet se sustenta en bases de datos como las antes mencionadas. Por ejemplo Twitter, Facebook y Gmail son complejas bases de datos que nos dan información.

Cuando se trata de noticias y se maneja una gran cantidad de datos (o se espera que éstos cambien con el tiempo), tiene a utilizarse una base de datos. En vez de tener un archivo JSON estático con todos tus datos, mantienes una base de datos y escribes un app que hace búsquedas en ella con los datos actuales. Y así, cuando los datos cambian, lo único que tienes que hacer es actualizar la base de datos – los cambios se verán automáticamente reflejados en el app.

Para los casos de apps en las que los datos no van a cambiar y la cantidad no es grande, una base de datos es demasiado esfuerzo que no rendirá frutos, aunque tal vez quieras usar una al principio para crear un archivo de datos de cierto tipo.

Si estás usando un API para introducir datos a un app, estás usando también una base de datos; la diferencia es que estás dejando que alguien más la almacene por ti. Esto es mucho más fácil, pero también aumenta la vulnerabilidad de tus datos, pues esas personas pueden acceder a tus datos cuando quieran.

Pequeñeces que vale la pena mencionar: a veces un app no accede directamente a una base de datos para jalar información. A veces accede a archivos en cache, pero esos archivos sí son generados automáticamente con base en lo que está en la base de datos.

¿Entonces cuándo debo usar una base de datos en lugar de Excel?

Excel y las bases de datos son dos cosas totalmente distintas. Ninguna herramienta es buena o mala. Te recomendamos usar una base de datos si tu proyecto cumples con las siguientes características:

• Si tienes muchos datos.

• Si tus datos están desordenados o son muy complejos.

• Quieres proyectar otras cosas con tus datos.

• Si otras personas necesitan trabajar con tus datos.

OK, me gusta. ¿Cómo puedo empezar?

Las bases de datos tienen su propia profundidad. Es recomendable llevarla con calma y no sumergirse de inmediato en lo más profundo. Úsalas cuando cuando las ventajas superen las inconveniencias, y cuando lo que necesites sea muy fácil de conseguir. Mientras más practiques, te vas a sentir más confiado y podrás extraer mayor información.

Opción 1: SQLite

SQLite es una excelente manera de empezar. Puedes instalar SQLite Manager.

Si necesitas asesoría de SQL, visita: https://github.com/tthibo/SQL-Tutorial

Opción 2: Microsoft Access

Microsoft Access se ejecuta en SQL y presenta una interfaz de software tradicional. Dependiendo de a quién le preguntes, es una herramienta útil… o simplemente hace las cosas más confusas. Nosotros NO lo recomendamos, pero las opiniones pueden variar.

Opción 3: Configurar una cuenta web compartida

Puedes configurar una cuenta web compartida, y esto puede costar 20 libras (32 USD) al año. Típicamente, estas cuentas vienen con una interfaz que permite crear, editar e interactuar con bases de datos sin escribir ningún SQL. También puedes jugar con las otras habilidades relacionadas con la web que te interesen y compartir los resultados con los demás.

Visita: A Small Orange (una opción buena y barata para hosting): http://asmallorange.com/

Opción 4: ¿Instalo MySQL o PostgreSQL en mi computadora?

Puedes instalar MAMP en una Mac o WAMP en una  PC. Esto instalará MySQL, así como una interfaz llamada phpMyAdmin (http://www.phpmyadmin.net). Una vez que hayas instalado MySQL, tendrás muchas opciones adicionales, ya que el software libre sirve como un navegador o como editor para tus bases de datos SQL. Si lo prefieres, puedes instalar PostgreSQL, que es diferente a MySQL. Ambos son populare,s ya que tienen una gran cantidad de datos. Pero, si estás iniciando, por lo pronto no pienses mucho en eso.

Apéndice: Consulta para la diversión y el beneficio

Gran parte de las bases de datos proviene de SQL, un lenguaje muy flexible para hacer preguntas acerca de las base de datos. Al principio no es fácil de usar, pero, con los siguientes ejemplos, todo será más claro.

SQL tiene 4 bloques básicos:
SELECT (buscar algo);
UPDATE (modificar algunas filas existentes);
INSERT (añadir nuevas filas);
DELETE (eliminar algunas filas).
Hay muchos otros comandos, pero éstos son los que más vas a utilizar; especialmente SELECT.

Imaginemos una tabla llamada “atletas olímpicos ” que tiene seis columnas (nombre, país, fecha de nacimiento, estatura, peso y sexo):

name
country
birthdate
height
weight
gender

Cuando creamos nuestra tabla, podemos especificar cosas como “no es necesario poner el país” o “el sexo debe ser M o F”.

Misión 1: Obtén una lista de todos los atletas en orden alfabético. Desde aquí puedes ver toda la tabla, ordenarlos por nombre de la A a la Z . Lo puedes hacer de la siguiente manera:

SELECT

*
FROM athletes
ORDER BY name ASC

Misión 2: Obtén una lista de todos los atletas del equipo de Gran Bretaña. Sólo obtendrás las filas para los atletas británicos. No especificaste como ordenarlos, así que no saldrán en orden alfabético. Sigue los siguientes pasos:

SELECT
*
FROM athletes
WHERE country = ‘Great Britain’

Misión 3: ¿Qué país tiene el promedio de atletas más pesado? Esto tomará todas las filas y las pondrá en grupos por país. Se abrirá una lista de los nombres de los países y el peso promedio de cada grupo. Esto lo puedes lograr así:

SELECT
country,AVG(WEIGHT)
FROM athletes
GROUP BY country

Misión 4: ¿En qué mes nacen más atletas olímpicos? Tal vez quieras probar una teoría astrológica sobre que el talento de los leos para el deporte. Desde aquí puedes ver el número de atletas olímpicos que nacieron en ese mes.

SELECT
MONTH(birthdate),COUNT(*)
FROM athletes
GROUP BY MONTH(birthdate)

Misión 5: Añade un nuevo atleta. Agrega un atleta a la tabla. Para insertar una fila, especifica las columnas que vas a añadir y el valor de cada uno.

INSERT
INTO athletes
(name,country,height,weight,gender)
VALUES (‘Andrew Leimdorfer’,’Great Britain’,180,74.8,’M’)

Misión 6: Ordena a todos los atletas en orden de estatura y peso; puede ser que notes algo muy extraño con el atleta canadiense Ian Warner.

SELECT
*
FROM athletes
WHERE gender = ‘M’
ORDER BY height/weight ASC

Misión 7: Si obtienes tus datos del portal london2012.com, pensarás que Ian Warner mide 5′ 7″ y pesa 160 kg. Lo más probable es que su peso fue marcado en libras. Vamos a arreglar esto.

UPDATE
athletes
SET weight = weight/2.2
WHERE name = ‘Ian Warner’

Misión 8: Borra a todos los atletas de Canadá y Estados Unidos.

DELETE
FROM athletes
WHERE country = ‘United States of America’ OR country = ‘Canada’;

Una vez que nos fijamos en  las operaciones para cambiar las bases de datos, seguro notas lo siguiente: verás que una consulta es como una oración y tiene reglas gramaticales. Tiene un «verbo» (¿Qué tipo de acción quiero?), un «objeto» (¿Qué tabla es la que quiero que haga la acción?) y «adverbios» opcionales (¿Cómo quiero hacer la acción?). Los «adverbios» incluyen detalles como «ordenar esta columna» y «sólo hacer esto para ciertas filas.»

Misión de bonus: Varias tablas y una breve muestra de JOIN

Es probable que haya mucho más datos que sólo los atletas. Para cada país, también puedes tener su bandera, su población y su ciudad capital; también tienes todos los eventos olímpicos y los atletas que participaron en ellos. Para cada evento, también tienes los resultados que obtuvieron: cuántas medallas y los resultados finales.

Si usaras Excel para depurar este tipo de bases de datos, sería una verdadera locura, ya que tendrías un montón de hojas de cálculo y con mucho esfuerzo lograrías hacer una referencia cruzada, o tendrías una mega-hoja de cálculo con columnas infinitas (las fuentes de datos gubernamentales usualmente aman las mega-hojas de cálculo).

Es posible que haya una hoja de cálculo donde cada fila es un atleta, y haya una larga lista de columnas llenas de una gran cantidad de información redundante y sin sentido, como:

name, country, birthdate, height, weight, gender, country_population, country_flag_url, country_gdp, event1, event1_date, event1_result, event2_date, event2_result, event3_date, event3_result, event4_date, event4_result, number_of_medals

Estas enormes bases de datos tienen muchas debilidades entre las que sobresalen:

Pierdes la capacidad de visualizar los datos. Cuando la información es tan grande, se vuelve un desastre

La estructura se vuelve muy poco flexible. Esto es casi una ley: en las mega hojas de cálculo, el número de columnas nunca es suficiente.

No tiene ningún sentido de las relaciones. Los atletas son una unidad aquí, pero hay otros. Hay países que tienen eventos (que pertenecen a los deportes), tienen resultados (que pertenecen a los eventos), que tienen atletas (que compiten en los eventos, que dan lugar a esos acontecimientos, y casi siempre pertenecen a los países). Estas relaciones probablemente serán la base para un montón de historias interesantes basadas en los datos, y la mega-hoja de cálculo hace un mal trabajo para contarlas.

El análisis es difícil. ¿Cómo puedes encontrar todos los atletas hombres que corren los 100 metros? Algunos de ellos podrían tener su tiempo en event1_result, otros en event2_result. Intenta (no lo creo) divertirte con los datos que están anidados; o si introduces manualmente cualquiera de estos datos, hay una buena probabilidad de que obtengas muchas  inconsistencias textuales como 100 metros, 100m de los hombres, Hombres y 100m.

SQL te permite mantener estas cosas en un montón de tablas separadas, pero utiliza conexiones lógicas entre ellos para que puedas trabajar con un gran conjunto de datos. Para combinar las tablas de este tipo, se utiliza JOIN.

Es posible crear una tabla para los atletas con la información básica, como la estatura y el peso; una tabla para eventos con detalles sobre dónde y cuándo se lleva a cabo y el récord mundial actual; una tabla para los países con información sobre cada país; así como una tabla de resultados en la que cada fila contiene un atleta, un evento, su resultado, y la medalla que ganaron (si los hay).

¡A continuación, te enseñamos como combinar temporalmente las tablas!

¿Quién ganó las medallas de oro el día de hoy?

SELECT
athletes.name, athletes.country, event.name
FROM athletes, results, events
WHERE athletes.id = results.athlete_id AND event.id = results.event_id
AND event.date = DATE(NOW()) AND results.medal = ‘Gold’

¿Cuántas medallas ha ganado cada país?

SELECT
countries.name, COUNT(*)
FROM athletes, countries, results, events
WHERE athletes.id = results.athlete_id AND event.id = results.event_id AND athletes.country_id = countries.id
AND results.medal IN (‘Gold’,’Silver’,’Bronze’)
GROUP BY countries.id