Cómo limpiar automáticamente los datos de una hoja de cálculo con OpenRefine

¿Alguna vez ha tenido que editar manualmente información sucia, desordenada y antigua de algún software obsoleto?

Una vez trabajé para una empresa que almacenó papeleo fuera del sitio durante 60 años. Los materiales se indexaron en una mesa para documentos. La mayoría de los registros tenían un número de caja, una fecha de almacenamiento, un número de recibo del proveedor de almacenamiento y una idea aproximada del contenido. , eso sí.

Durante 60 años la lista se volvió … desordenada. Los contratos de almacenamiento cambiaron varias veces, por lo que los códigos de caja y los recibos de los proveedores variaron con el tiempo. Agregue los errores aleatorios que se acumularon con el tiempo y tuvo un gran desastre.

Mi trabajo consistía en transferir todo a otro contratista, lo que significaba limpiar miles de registros para jugar bien con el elegante inventario en línea del nuevo proveedor. Fue una tarea ardua, una tarea que muchos de nosotros enfrentamos cuando tratamos de organizar los datos.

La buena noticia es que si puede guardar sus datos desordenados en una hoja de cálculo, puede limpiarlos y reformatearlos. Mi herramienta favorita para esto se llama OpenRefine, y su especialidad es «reconciliar» o «normalizar», lo que facilita la búsqueda de errores tipográficos, variaciones de frases, errores de formato, espacios adicionales y otras cosas que son difíciles de detectar en filas y filas. de información.

¿Qué es OpenRefine?

OpenRefine se anuncia a sí mismo, simplemente, como «una poderosa herramienta para trabajar con datos desordenados». Lanzado originalmente en 2010 como «Freebase Gridworks», más tarde se llamó «Google Refine» después de ser adquirido por el gigante de las búsquedas. Hoy en día, es un proyecto de código abierto administrado por la comunidad para, bueno, refinar sus datos.

Para ti, esto podría significar varias cosas. Es posible que su equipo de ventas desee exportar datos antiguos de la tienda, reorganizarlos e importarlos a una nueva aplicación de comercio electrónico. Es posible que su personal de contabilidad tenga datos heredados de hace años. Su personal de relaciones públicas podría tener varias listas de correo electrónico de campañas anteriores que desea fusionar, modificar o anular la duplicación.

Tal vez los resultados de su encuesta sean confusos, las exportaciones de su aplicación sean confusas o sus necesidades de datos analíticos combinados de múltiples fuentes.

OpenRefine se creó especialmente teniendo en cuenta esos tipos de operaciones masivas. Puede que sea solo lo que necesita para finalmente terminar ese proyecto de datos que ha estado posponiendo.

Introducción a OpenRefine

Comenzar es fácil. Simplemente descargue OpenRefine (funciona en Windows, Mac y Linux) e inicie el programa. Abrirá una pestaña del navegador que se parece mucho a otras aplicaciones de Google y le pedirá que cree un proyecto o abra un proyecto que ya ha comenzado.

Necesitará algunos datos para trabajar con OpenRefine, y abre cualquier dato en un formato de hoja de cálculo: CSV, XLS o incluso una hoja de cálculo de Google Sheets en línea. También puede tomar archivos XML y JSON, si ese es tu problema.

Empecemos un nuevo proyecto. Este ejercicio utilizará un conjunto de datos disponibles públicamente del Gobierno de Ontario, que, al igual que muchos datos públicos, es un poco desordenado. Vayamos con un tema cercano y querido a mi corazón: la cerveza. Copie el enlace al XLSX archivo, que incluye detalles sobre las microcerveceras y las marcas de Ontario. Cambie a la pestaña OpenRefine, comience un nuevo proyecto, seleccione la opción y pegue el enlace de su hoja de cálculo.

Tan pronto como ingresa un conjunto de datos, OpenRefine genera una vista previa para asegurarse de que se muestre correctamente. Puede realizar una limpieza preliminar: eliminar filas vacías, establecer la primera fila como un encabezado con nombres de columna o convertir columnas en tipos de datos específicos (fechas, números enteros, etc.).

Haga clic en «Crear proyecto» cuando se haya asegurado de que los datos se muestren correctamente y se le llevará a la pantalla donde ocurre toda la magia.

Lo primero que notará es que OpenRefine no muestra sus datos como una hoja de cálculo con una larga lista de filas. En cambio, muestra un máximo de 50 filas a la vez, esencialmente una vista previa suficiente para que piense en lo que está trabajando. Puede hojear sus datos si lo necesita, pero creo que pronto se sentirá cómodo al sentirse abrumado.

Limpiar datos con OpenRefine Facets

El primer paso es aprender sobre las facetas. Estos muestran con precisión qué valores se utilizan en una columna, por lo que puede encontrar errores tipográficos o variaciones en cosas que se supone que son idénticas. Comencemos con el nombre del fabricante. Haga clic en el botón desplegable junto al encabezado, seleccione y luego. Se le presentará una columna como esta, que muestra un recuento de las veces que aparece cada elemento en el conjunto de datos:

Podemos ver, por ejemplo, que Big Rig Brewery tiene 13 cervezas diferentes; Cervecería Big Rock, 6 cervezas diferentes. Ya podemos ver algunos datos confusos aquí: «Black Swan Brewing Company» y «BLACK SWAN BREWING COMPANY INC». son la misma empresa, pero con nombres ligeramente diferentes en esta hoja de cálculo.

Para solucionar este problema, coloque el mouse sobre el nombre que desea cambiar, haga clic en «editar» y escriba el nuevo nombre. Haga clic en y edita automáticamente todas las entradas coincidentes en el conjunto de datos.

Aceleremos el proceso identificando automáticamente todas las facetas que son similares y fusionándolas, sin escribir nada, agrupando los datos. Haga clic en el botón en la parte superior de la pantalla de facetas y verá todas las entradas similares identificadas por OpenRefine:

Para algunos de ellos, es solo un espacio adicional (como al final de «Square Timber Brewing Company») o una coma adicional (como en Blood Brothers Brewing), o el uso liberal de mayúsculas. Como puede ver en la entrada «Bevin Palmateer», OpenRefine también identifica palabras que están fuera de orden.

Marque las casillas para cualquier cosa que desee arreglar. Si no le gusta el nuevo valor sugerido, por ejemplo, el nombre en mayúscula sugerido para NITA BEER, puede hacer clic en la opción en minúsculas y cambiará ese campo. Si no le gusta ninguna de las opciones, simplemente escriba su nombre preferido.

Haga clic para hacer otra verificación. Cuando la comprobación no encuentre resultados, pruebe con otro método de agrupación para buscar más (debería encontrar «Walkervile» y «Walkerville»).

Es minería de datos, pero no es necesario que aprenda la teoría avanzada de minería de datos para obtener resultados: simplemente haga clic en todas las opciones. Comenzará a ver falsos positivos (por ejemplo, «Bell City» no es «River City»), que puede ignorar.

También hay algunas herramientas de transformación comunes que puede usar para limpiar cosas, como eliminar todos los espacios antes y después del texto. También eliminemos todos los nombres de cervecerías en mayúsculas transformando toda la columna en. Vuelva a hacer clic en el menú desplegable de la columna, vaya a y lea todas las posibilidades.

Categorizar datos automáticamente en OpenRefine

El siguiente paso es hacer cosas inteligentes con todos estos datos. Supongamos que estas cervezas son nuestros datos de productos y queremos agregar categorías de cerveza a nuestro catálogo. No queremos etiquetar manualmente cada entrada, así que ahorremos algo de tiempo identificando los tipos de cerveza a partir de los nombres de las cervezas.

Podemos hacer una comprobación rápida de un tipo de cerveza utilizando una faceta de texto personalizado. Buscaremos todos los valores de celda que contengan «Porter» (esto también distingue entre mayúsculas y minúsculas, pero ahora que hemos puesto todo en titlecase, las mayúsculas P debería atrapar todo). Una faceta de texto personalizado en la columna Marca del fabricante abre esta ventana, en la que ingresamos un filtro:

value.contains("Porter")

Esta función devuelve true y false-y true aquí significa que 25 cervezas son porteros en la lista. (También hay 79 cervecerías sin cervezas reales disponibles: el (blank) categoría, pero ignoremos eso por ahora.)

Estos filtros son excelentes cuando desea manipular un subconjunto de su hoja de cálculo sin tener que eliminar el resto o mantener las filas de enfoque seleccionadas. Puede aplicar un filtro, hacer un montón de operaciones y luego eliminarlo más tarde. OpenRefine incluso incluye algunas recetas comunes para formatear datos, como estandarizar formatos de fecha o transformar «Nombre Apellido» en «Apellido, Nombre».

Usemos eso para transformar nuestros datos en algo útil. Agregaremos una nueva columna basada en la columna «Marca del fabricante», usando análisis de texto para adivinar qué tipo de cerveza es. No funcionará en todas las entradas, pero para las cervezas que tienen «IPA», «lager», «stout», «lime», «red», «wheat», etc. en su nombre, tendremos cierto éxito.

Comience haciendo clic en «Marca del fabricante». Seleccione y luego elija. Para buscar «lager» y reemplazar la totalidad del valor de los tipos de cerveza por «lager» cuando corresponda, usamos una declaración if:

if(value.contains("Lager"),"lager",value)

If Las declaraciones aquí son sencillas: si la primera parte es verdadera, transforme el valor completo en «lager»; de lo contrario, reemplace el valor de la celda por sí mismo (o no haga nada).

Si queremos categorizar un gran conjunto de tipos de cerveza a la vez, anidamos una serie de declaraciones if una dentro de la otra. Parece un poco tonto, pero hace el trabajo:

if(value.contains("Lager"),"Lager",if(value.contains("IPA"),"IPA",if(value.contains("Wheat"),"Wheat",if(value.contains("Pilsner"),"Pilsner",if(value.contains("Brown"),"Brown",if(value.contains("Kolsch"),"Kolsch",if(value.contains("Light"),"Light",if(value.contains("Red"),"Red",if(value.contains("English"),"English",if(value.contains("Stout"),"Stout",if(value.contains("Porter"),"Porter",value)))))))))))

Básicamente, si no se encontró «Lager», intente «IPA», luego intente «Trigo», luego intente «Pilsner», etc., etc. No es una sintaxis de programación estándar, pero hace el trabajo.

Aplica esa transformación, luego revisa las facetas de la columna para ver nuestro progreso.

Ya que estamos en eso, limpiemos los resultados. Concilie «IPA» y «India Pale Ale» con «IPA» con los pasos que aprendió anteriormente. También tenga en cuenta que las operaciones funcionan en orden: querrá convertir «India Pale Ale» antes de reformatear «Pale Ale». Debido a que estas transformaciones también distinguen entre mayúsculas y minúsculas, la transformación a «India pale ale» en minúsculas también protegería su trabajo cuando busque «Pale Ale» más adelante.

Con un poco de categorización, podemos comenzar a ver la propagación de tipos de cerveza en Ontario. (¡Pruébelos todos hoy!) Definitivamente es más rápido que etiquetarlos todos a mano, y debería darle una idea de cómo hacer que los filtros OpenRefine funcionen para usted.

Si esta fuera una lista de productos para nuestra tienda en línea, nos gustaría exportar nuestra hoja de cálculo limpia y de valor agregado de OpenRefine e importarla a nuestra tienda de comercio electrónico. El botón es tu amigo. Puede exportar sus datos como una hoja de cálculo con una variedad de opciones y formularios de datos. También puede cargar los datos directamente en una nueva hoja de cálculo de Google Sheets o en una tabla de Google Fusion.

Haga más con OpenRefine

Hay algunas otras herramientas útiles de OpenRefine. La opción Deshacer / Rehacer le brinda información detallada sobre todas sus actividades en lugar de simplemente deshacer sus errores, lo cual es muy útil para aprender a sacar más provecho de OpenRefine. Recuerde también: OpenRefine está diseñado en torno a bases de datos para que pueda usar sus registros y filas por separado para organizar sus datos.

Ahora es tu turno de probarlo. ¿Tiene datos desordenados de la exportación de una aplicación o una hoja de cálculo vieja llena de datos confusos?

Una excelente manera es usar OpenRefine para organizar sus contactos: encuentre errores tipográficos y de formato en direcciones de correo electrónico, números de teléfono o nombres de empresas antes de importar los datos a una nueva aplicación. Lo he usado para reformatear los datos antiguos de Mailchimp cuando cambiamos los diseños de nuestros formularios de registro, muy útil.

No pierda horas formateando sus datos nuevamente. OpenRefine puede hacerlo por usted en minutos.

Sigue leyendo