OpenRefine para todxs

Table of Contents

Descarga

Extensiones

  • BioVel, del Museo Botánico y Jardín Botánico de Berlín-Dahlem,
  • Crowdsourcing, por Zemanta.
  • DBpedia, por Zemanta.
  • History tools, pivot tool y scatterplot con D3, por VIB-BITS.
  • Diff, por VIB-BITS
  • LMF Extension, por Salzburg Research
  • Named-Entity Recognition, por Ruben Verborgh Free Your Metadata
  • Opentree, de nickynicolson, para mostrar árboles filogenéticos del Open Tree of Life,
  • RDF, por DERI, http://refine.deri.ie/
  • Stats, estadísticas, dos extensiones:
    • Chicago Tribune para Refine 2.1.
    • sparkica, para Refine 2.5 +.
  • extraCTU-plugin para extraer correos electrónicos, números de teléfono, URL y números de identificación por giTorto.
  • geoXtension, basado en gdal, proj y geos. Su instalación lleva mucho tiempo, se recomienda la instalación del docker.
  • Kuali, requiere credenciales del servidor Kuali OLE.

Primeros pasos

  • Refine es una aplicación web cliente-servidor.
  • Cuando lanzamos Refine, estamos activando el servidor.
  • A la vez, se abre el navegador con una pestaña que apunta a la dirección 127.0.0.1:3333.
  • El navegador actúa como cliente de Refine.
  • No necesita conexión a Internet para funcionar.
  • 127.0.0.1 es la dirección del locahost, el equipo local, el ordenador donde trabajamos.
  • 3333 es el puerto que utiliza la aplicación

Si lo lanzamos desde la terminal, podemos modificar las opciones: 

./refine -p 3333 -i 0.0.0.0 -m 6000M -d /ruta/directorio/trabajo

Donde:

  • ./refine es la ejecución del programa
  • -p 3333 le dice el puerto en el que queremos que se lance.
  • -i 0.0.0.0 especifica la dirección donde se abrirá.
  • -m 6000M es el tamaño máximo que soporta para la sesión.
  • -d /ruta/directorio/trabajo indica el directorio donde almacenará refine la información.

Si en GNU/Linux nos da un error de JAVA:

Google Refine requires Java version 6 or later. If you have multiple versions of Java installed, please set the environment variable JAVA_HOME to the correct version.

Significa que no tenemos ningún valor para la variable JAVA_HOME, por lo que tenemos que añadir la ruta correcta a la variable de entorno con el comando export.

En mi caso: 

export JAVA_HOME="/usr/lib/jvm/java-8-oracle"

Y comprobamos: 

env | grep JAVA

Abrir un proyecto

  • Dado que la opción estable y en desarrollo es en inglés, seguiremos los menús en este idioma aunque lo traduciremos al castellano/español.
  • Cuando se abre Refine, en la columna de la izquierda hay cuatro opciones:
    • Create project o crear proyecto.
    • Open Project o abrir proyecto.
    • Import Project o importar proyecto.
    • Language Settings o configuración de idioma.

Language Settings

  • Actualmente, las versiones estable y en desarrollo solo están disponibles en Inglés (English) o Italiano (Italian).
  • En la versión 2.6-rc.2 puedes disfrutar, además, de francés, chino y español.

Import Project

  • Puedes cargar un proyecto de Refine, importar un proyecto, si:
    • Has trabajado en algún proyecto y lo has exportado.
    • Alguien te ha pasado un proyecto.
  • Si no tienes un proyecto en el que trabajar, comienza uno.

Open Proyect

  • Refine guarda todos los proyectos con los que trabajes en el equipo.
  • No hace falta que guardes durante el proceso de análisis o limpieza de datos, aunque copias extra siempre son recomendables.
  • Puedes exportar el proyecto por si quieres tener una copia más del mismo (ver más adelante cómo).
  • En la opción de Abrir Proyecto se muestran estos datos, siempre que no sea la primera vez que abres el programa:
    • Una x por si quieres borrarlo.
    • Un rename o renombrar para renombrarlo.
    • La última fecha de modificación, last modified o Última modificación.
    • El nombre del proyecto o name.

Create project

  • Para crear un proyecto, necesitamos que Refine tenga algunos datos.
  • Podemos importar datos de algún archivo TSV, CSV, *SV, Excel (.xls y .xlsx), JSON, XML, RDF como XML y datos de documentos de Google Drive.
  • Otros formatos también se soportan a través de algunas extensiones (ver extensiones)
  • Lo más habitual es subir un archivo de datos que tengamos en nuestra carpeta de datasets.
  • Se pueden cargar datos desde una dirección URL que contenga un archivo de datos con alguna de las extensiones anteriores.
  • Si disponemos de unos datos en el portapapeles, también se pueden cargar desde aquí from clipboard.
  • O podemos cargar datos que contengamos en Google Drive. Esta opción no es la más recomendable si pretendes mantener la privacidad sobre los datos que estás utilizando.
  • Es importante:
    • En Project name no escribir caracteres no ASCII ni mayúsculas ni espacios en blanco.
    • Echar un vistazo a los datos y elegir el Character encoding apropiado. Normalmente, UTF-8 valdrá pero puede que el texto esté codificado en otro conjunto de caracteres.
    • En caso de subir un CSV, elegir el separador adecuado.

Records y rows

  • Refine distingue entre registros Records y filas o Rows y permite trabajar de una u otra manera.
  • Una fila o row es una línea de datos. El número total de filas se indica en la parte superior de la página, N rows
  • Un registro o record es una combinación de una o muchas filas que se identifican por un objeto único y compartido en función de los datos de la primera columna.
  • Es decir, si en la primera columna de datos --no en la columna All o Todo-- hay datos en algunas filas y en la segunda columna hay datos que se refieren a la primera.
  • Si quisiéramos convertir nuestro conjunto de datos de filas a registros, podríamos seguir este tutorial, que explicamos a continuación.

Crear registros

  • Primero hay que identificar la columna que contiene el marcador de registros, una cuyas celdas se repitan.
  • Para ver esto, podemos utilizar una faceta de texto sobre la columna desde el menú de columna > facetas.
  • Movemos esa columna al principio desde el menú de columna > editar columna > mover columna al principio.
  • Ordenamos la columna desde el menú de la columna > Ordenar (/sort/).
  • Desde el menú de ordenar que sale en la parte gris de los menús de arriba > reordenar filas permanentemente o /reorder rows permanently/
  • Desde la misma columna, opción Editar celdas > vaciar hacia abajo o /blank down/
  • Finalmente, mostramos como registros o records.

Filas

Si quisiéramos un número distinto de filas, podríamos modificar el archivo que las produce, data-table-view.js. Suponiendo que estemos en el directorio raíz de Refine, editamos el archivo con nuestro editor de textos favorito: 

nano webapp/modules/core/scripts/views/data-table/data-table-view.js

En la línea número 185 se muestra este código (si no es en la 185, buscar la cadena 50): 

  var sizes = [ 5, 10, 25, 50 ];

Modificamos los valores o añadimos, guardamos y cerramos y recargamos el navegador donde aparece Refine.

Facetas

Las facetas o facets se utilizan para filtrar el contenido, para explorar, ver o encontrar valores erróneos o que se salgan de la norma, mostrar distintas caras de los datos.

  • Se pueden anidar y se puede modificar el orden de anidación para obtener distintos resultados.

Sobre la columna, con la opción Facet podemos hacer:

  • Text facet, faceta de texto, nos agrupa los datos de las celdas si son iguales.
  • Numeric facet, si se trata de datos numéricos.
  • Timeline facet, si se trata de datos que reconoce como fechas.
  • Scatterplot facet, si se trata de datos numéricos, para hacer gráficos de dispersió donde encontrar patrones, tendencias o outliers (valores extremos, extraños).
  • Text filter, escribimos los valores que queremos encontrar.

Buscar

  • Para encontrar determinados valores, pincha en facet y luego en custom text facet.
  • Introducimos lo que queremos buscar.
  • Pincha en count para ver el número de repeticiones.

Por ejemplo: 

value.contains("millones")

Localiza el valor millones con la función .contains().

Se pueden anidar de esta manera: 

value.contains("millones")+value.contains("euros")

Búsqueda de valores duplicados

Se pueden encontrar valores duplicados a través de las facetas: 

Facet > Customized facets > Duplicates facet

Se pueden ver las partes que se repiten de los valores de una columna 

Facet > Customized facets > Word facet

Ver todos los valores de una columna: 

Facet > Text facet

Ver (y cambiar o agrupar) variantes similares de valores: 

edit cells > cluster and edit

Borrar celdas vacías de una columna

A veces hay registros que producen celdas que no son necesarias. - Para eliminar los registros del dataset: 

Facet > Customized facets > Facet by blank y clic en ‘true’

Celdas vacías

Situar las celdas vacías al final 

Edit cells > Blank down
  • Filtramos para mostrar solo los valores vacíos Facet > Customized facets > Facet by blank y pinchamos en true
  • Podemos eliminar, ver punto siguiente.
  • Se pueden explorar los datos con gráficos de dispersión.
  • En la columna que nos interese: Facet > Scatterplot facet, pincha en log para verlo mejor. Se muestran las relaciones entre todos los valores numéricos en cada una de las columnas.
  • Pincha en en uno de los gráficos para filtrar/mostrar esos registros.
  • Pincha y arrastra sobre el gráfico de la izqda. para seleccionar un grupo de registros y mostrarlos.

Transformar datos

  • Tras aplicar 1 o más filtros en el panel izquierdo podemos editar lo que interese.
  • Cambiar conjunto de valores tras filtrar por nombre: renombrar, renombrar uno para que se fusione con otro, agrupar, etc.
  • También podemos hacerlo en cualquier celda de la dcha (datos filtrados) y aplicar los cambios a todas las instancias o celdas similares.

Transformar a numérico

Si queremos pasar millón a números, escribiremos: 

 toNumber(value.replace(" millón", ""))*1000000

Con value.replace reemplazamos el valor millón y lo sustituimos por la multiplicación del valor.

Transformaciones

Se realizan con: 

Edit cells > transform

Eliminar caracteres

value.unescape(‘url’)

Elimina los caracteres raros de una URL 

value.replace("+","")

Elimina el signo +, lo reemplaza por nada.

Obtener URL

‘http://’+ cells[‘nombre_columna’].value +’.com’

Obtiene la URL http://valor.com

Añadir y borrar a la vez

‘Avenida ’+value.replace(‘AV.’,’’)

Añadimos Avenida al mismo tiempo que eliminamos AV

Uso de regexp

replace(value, /\d/,"")

Elimina la parte numérica de las celdas. 

value.replace(regex, “”)

En general, usamos cualquier tipo de expresiones regulares

Eliminar espacios en blanco

Edit cells > Common transforms > Trim leading and trailing whitespace

Elimina espacios blancos del inicio y final de los valores

Eliminar registros seleccionados

All > Edit rows > Remove all matching rows

Clustering

Agrupaciones basada en similitudes. En el panel de la izqda., en filtro, aparece el botón de cluster donde se abre una ventana que podemos fusionar o merge los diferentes grupos escribiendo en todos el mismo nombre como nuevo valor de celda. También podemos hacerlo con: 

edit cells > cluster and edit

Si no obtenemos los resultados esperados, podemos repetir el proceso para todos los algoritmos cambiando el método, la función y el radius

Limpiar valores de columna numérica

Para limpiar valores de columna numérica, hacemos una faceta numérica 

Facet > numeric facet

A la izqda. seleccionamos solo el tipo non-numeric para transformar estos datos. Si vemos que, erróneamente, los valores están muy dispersos, tenemos varias opciones:

Usar escala logarítmica para corregir. Pinchamos en Change (submenú izq) y escribimos: 

value.log()

Aceptamos, si la previsualización es correcta.

Arrastramos los extremos del gráfico para visualizar el subconjunto de datos más dispersos sobre los que hacer las transformaciones necesarias con edit cells > transform: 

value.replace(",",".")

Convertir en valores numéricos: 

Edit cells > common transforms > to number

Limpiar fechas

Primero convertimos valores de columna a texto para evitar q haya números: 

Edit cells -> Common transformations -> To text

Luego convertir valores a fecha: 

Edit cells -> Common transformations -> To date

A continuación mostramos patrones de fecha 

Facet -> Timeline facet

Seleccionamos sólo los valores non-time para transformar sólo éstos y extraemos sólo el año mediante una expresión regular: 

Edit cells -> Transform

Entonces, escribimos: 

value.match(/.*(d{4}).*/)[0]

Donde:

  • Dentro de la función (), las barras // indican que se trata de una expresión regular.
  • .* significa que se trata de una secuencia de cero o más caracteres (letras, números, símbolos, etc.). Es el comodín para cualquier tipo de carácter.
  • d indica que estamos buscando dígitos.
  • {4} muestra que queremos encontrar cuatro dígitos.
  • La función value.match devuelve una matriz o array de resultados, de manera que usamos [0] para recuperar sólo la primera coincidencia (en las matrices o arrays, el primer valor es el 0).

Convertimos estos valores extraidos a fechas con: 

Edit cells -> Common transformations -> To date

Si hay fechas con varios formatos, utilizamos: 

Edit cells -> Transform

Y así extraemos el valor que nos interesa con la función toString, de tal modo que: 

value.toString('yyyy')

Obtiene el año, 2013. 

value.toString('M')

Obtiene el dígito del mes, es decir, el 1 de enero. 

value.toString('MM')

Obtenemos dos dígitos del mes, es decir, 01 de enero. 

value.toString('MMM')

Obtenemos tres caracteres del mes, es decir, Ene de enero. 

value.toString('MMMM')

Obtenemos los caracteres del mes completo, es decir, Enero de enero.

Podemos concatenar las funciones toDate y toString para modificar el formato en una sola línea: 

value.toDate(‘MM/yy’,’MMM-yy’).toString(‘yyyy-MM’)

Partir/Unir columnas

Separar en distintas columnas: 

Edit column > split into several columns

Se pone el separador y el número de columnas. Funciona de izqda. a dcha. Después se puede renombrar la nueva columna con: 

Edit Column -> Rename this column

Añadir nuevas columnas

Añadimos una nueva columna basada en otra columna: 

edit column>add column based on this column

Ponemos un nombre a la nueva columna y escribimos: 

cells("nombre-una-columna").value+""+cells("nombre-otra-columna").value

Jugando con value

Si escribimos: 

not(value.startsWith("-"))

Si queremos que extraiga los valores 2-5: 

value[1,5]

Si queremos extraer a partir del séptimo carácter: 

value.substring(6)

Para crear nueva columna con el nº de repeticiones de la palabra "hola": 

value.facetCount("value", "hola")

Una nueva columna con el resultado de la división de dos valores: 

cells[“A”].value / cells[“C”].value

Variables

cell

Es la celda actual. Tiene los campos value y recon

value

El valor de la celda actual. Es una abreviación para cell.value

row

La fila actual. Tiene 5 campos:

  • flagged
  • starred
  • index
  • cells
  • record

cells

Las celdas de la fila actual. Es una abreviación para row.cells. Una celda particular puede ser recuperada con cells.<nombre de la columna> (sin los caracteres menor que y mayor que), si el nombre de la columna no está separada por espacios. De lo contrario, habrá que ponerlo entre comillas y entre corchetes con cells["<nombre de la columna>"]

rowIndex

Es el índice de la columna actual. Es una abreviación para 'row.index'.

Más funciones

Las funciones se invocan de dos maneras:

  • nombreFuncion(argumento0, argumento1, ...)
  • argumento0.nombreFuncion(argumento1, ...)

La primera forma es la normal, mientras que la segunda es un atajo que se conoce como syntactic sugar.

Funciones Booleanas

https://github.com/OpenRefine/OpenRefine/wiki/GREL-Boolean-Functions

and

and(booleano b1, booleano b2, ...) crea un booleano en el que las dos condiciones sean verdaderas.

Por ejemplo: 

and(value.contains("servicios"),value.startsWith("Otros"))

Los resultados serán true o false, verdadero o falso, si la celda contiene la cadena servicios y la celda comienza con la cadena Otros

or

or(booleano b1, booleano b2, ...) crea un booleano en el que o bien la primera condición o el segundo sean verdaderos.

Por ejemplo: 

or(value.contains("servicios"),value.startsWith("Otros"))

Los resultados serán true o false, verdadero o falso, si la celda contiene la cadena servicios o la celda comienza con la cadena Otros

not

not(booleano b), crea un booleano si no se cumple la condición que se declara.

Por ejemplo: 

not(value.contains("servicios")

value.contains("servicios") daría un verdadero si la celda contuviese servicios y un falso si no los tuviera. Al ponerle la condición, la función booleana not, al revés, dará un falso si los contiene y verdadero si no lo contiene.

Funciones de cadenas de caracteres, literales o string

length

length(cadena s) devuelve la cadena s como un número.

Por ejemplo: 

value.length()

Y también: 

length(value)

abs

abs (number d) devuelve: number Returns the absolute value of a number acos (number d) devuelve: number Returns the arc cosine of an angle, in the range 0 through PI and (boolean a, boolean b) devuelve: boolean ANDs two boolean values asin (number d) devuelve: number Returns the arc sine of an angle in the range of -PI/2 through PI/2 atan (number d) devuelve: number Returns the arc tangent of an angle in the range of -PI/2 through PI/2 atan2 (number x, number y) devuelve: number theta Converts rectangular coordinates (x, y) to polar (r, theta) ceil (number d) devuelve: number Returns the ceiling of a number chomp (string str, string separator) devuelve: string Removes separator from the end of str if it's there, otherwise leave it alone. combin (number d) devuelve: number Returns the number of combinations for n elements as divided into k contains (string s, string frag) devuelve: boolean Returns whether s contains frag cos (number d) devuelve: number Returns the trigonometric cosine of an angle cosh (number d) devuelve: number Returns the hyperbolic cosine of a value cross (cell c, string projectName, string columnName) devuelve: array TODO datePart (date d, string part) devuelve: date Returns part of a date degrees (number d) devuelve: number Converts an angle from radians to degrees. diff (o1, o2, time unit (optional)) devuelve: string for strings, number for dates For strings, returns the portion where they differ. For dates, it returns the difference in given time units endsWith (string s, string sub) devuelve: boolean Returns whether s ends with sub escape (string s, string mode ['html','xml','csv','url','javascript']) devuelve: string Escapes a string depending on the given escaping mode. even (number d) devuelve: number Rounds the number up to the nearest even integer exp (number n) devuelve: number Returns e^n facetCount (choiceValue, string facetExpression, string columnName) devuelve: number Returns the facet count corresponding to the given choice value fact (number i) devuelve: number Returns the factorial of a number factn (number i) devuelve: number Returns the factorial of a number fingerprint (string s) devuelve: string Returns the fingerprint of s, a derived string that aims to be a more canonical form of it (this is mostly useful for finding clusters of strings related to the same information). floor (number d) devuelve: number Returns the floor of a number as an integer gcd (number d, number e) devuelve: number Returns the greatest common denominator of the two numbers get (o, number or string from, optional number to) devuelve: Depends on actual arguments If o has fields, returns the field named 'from' of o. If o is an array, returns o[from, to]. if o is a string, returns o.substring(from, to) hasField (o, string name) devuelve: boolean Returns whether o has field name htmlAttr (Element e, String s) devuelve: String attribute Value Selects a value from an attribute on an Html Element htmlText (Element e) devuelve: String text Selects the text from within an element (including all child elements) inc (date d, number value, string unit (default to 'hour')) devuelve: date Returns a date changed by the given amount in the given unit of time indexOf (string s, string sub) devuelve: number Returns the index of sub first ocurring in s innerHtml (Element e) devuelve: String innerHtml The innerHtml of an HTML element join (array a, string sep) devuelve: string Returns the string obtained by joining the array a with the separator sep jsonize (value) devuelve: JSON literal value Quotes a value as a JSON literal value lastIndexOf (string s, string sub) devuelve: number Returns the index of sub last ocurring in s lcm (number d, number e) devuelve: number Returns the greatest common denominator of the two numbers

length

La longitud de la cadena de caracteres. 

length(value)

length (array or string o) devuelve: number Returns the length of o ln (number n) devuelve: number Returns the natural log of n log (number n) devuelve: number Returns the base 10 log of n match (string or regexp) devuelve: array of strings Returns an array of the groups matching the given regular expression max (number a, number b) devuelve: number Returns the greater of two numbers md5 (string s) devuelve: string Returns the MD5 hash of s min (number a, number b) devuelve: number Returns the smaller of two numbers mod (number a, number b) devuelve: number Returns a modulus b multinomial (one or more numbers) devuelve: number Calculates the multinomial of a series of numbers ngram (string s, number n) devuelve: array of strings Returns an array of the word ngrams of s ngramFingerprint (string s, number n) devuelve: string Returns the n-gram fingerprint of s not (boolean b) devuelve: boolean Returns the opposite of b now (undefined) devuelve: date Returns the current time odd (number d) devuelve: number Rounds the number up to the nearest even integer or (boolean a, boolean b) devuelve: boolean Returns a OR b ownText (Element e) devuelve: String ownText Gets the text owned by this HTML element only; does not get the combined text of all children. parseHtml (string s) devuelve: HTML object Parses a string as HTML parseJson (string s) devuelve: JSON object Parses a string as JSON partition (string s, string or regex frag, optional boolean omitFragment) devuelve: array Returns an array of strings [a,frag,b] where a is the string part before the first occurrence of frag in s and b is what's left. If omitFragment is true, frag is not returned. phonetic (string s, string encoding (optional, defaults to 'metaphone3')) devuelve: string Returns the a phonetic encoding of s (optionally indicating which encoding to use') pow (number a, number b) devuelve: number Returns a^b quotient (number numerator, number denominator) devuelve: number Returns the integer portion of a division radians (number d) devuelve: number Converts an angle in degrees to radians reinterpret (string s, string encoder) devuelve: string Returns s reinterpreted thru the given encoder. replace (string s, string or regex f, string r) devuelve: string Returns the string obtained by replacing f with r in s replaceChars (string s, string f, string r) devuelve: string Returns the string obtained by replacing all chars in f with the char in s at that same position reverse (array a) devuelve: array Reverses array a round (number n) devuelve: number Returns n rounded rpartition (string s, string or regex frag, optional boolean omitFragment) devuelve: array Returns an array of strings [a,frag,b] where a is the string part before the last occurrence of frag in s and b is what's left. If omitFragment is true, frag is not returned. select (Element e, String s) devuelve: HTML Elements Selects an element from an HTML elementn using selector syntax sha1 (string s) devuelve: string Returns the SHA-1 hash of s sin (number d) devuelve: number Returns the trigonometric sine of an angle sinh (number d) devuelve: number Returns the hyperbolic sine of an angle slice (o, number from, optional number to) devuelve: Depends on actual arguments If o is an array, returns o[from, to]. if o is a string, returns o.substring(from, to) smartSplit (string s, optional string sep) devuelve: array Returns the array of strings obtained by splitting s with separator sep. Handles quotes properly. Guesses tab or comma separator if "sep" is not given. sort (array a) devuelve: array Sorts array a split (string s, string or regex sep, optional boolean preserveAllTokens) devuelve: array Returns the array of strings obtained by splitting s with separator sep. If preserveAllTokens is true, then empty segments are preserved. splitByCharType (string s) devuelve: array Returns an array of strings obtained by splitting s grouping consecutive chars by their unicode type splitByLengths (string s, number n, ...) devuelve: array Returns the array of strings obtained by splitting s into substrings with the given lengths

startsWith

Comprobar si comienza con una cadena de caracteres 

value.startsWith("Compra")

O bien, lo contrario: 

not(value.startsWith("Compra"))

Todas

startsWith(string s, string sub)
devuelve boolean.
strip(string s)
devuelve string, omitiendo los espacios en blanco iniciales y finales.
sum(array a)
devuelve number, suma los números de la serie a.
tan(number d)
devuelve number, la tangente trigonométrica de un ángulo.
tahn(number d)
devuelve number, la tangente hiperbólica de un valor.
toDate(o, boolean month_first / format1, format2, ... (all optional))
devuelve date, o convertido en un objeto temporal, se puede dar pistas sobre si el día o el mes se lista primero, o da una lista ordenada de formatos posibles que usan esa sintaxis.
toLowercase(string s)
devuelve string, s lo convierte a minúsculas.
toNumber(o)
devuelve number, o convertido en número.
toString(o, string format (optional))
devuelve string, o lo convierte en cadena de caracteres.
toTitlecase(string s)
devuelve string, s se convierte en caracteres de titulares, la primera letra en mayúsculas.
toUppercase(string s)
devuelve string, s convertido en mayúsculas.
trim(string s)
devuelve string sin espacios en blanco iniciales y finales.
type(object o)
devuelve string, el tipo de objeto que es o.
unescape(string s, string mode ['html','xml','csv','url','javascript'])
devuelve string. Desescapa todas las partes escapadas de la cadena según el modo de escape dado.
unicode(string s)
devuelve string. Devuelve una serie de cadenas que describen cada carácter en s en su notación Unicode completa.
unicodeType(string s)
devuelve string, una serie de cadenas que describe cada carácter de s en su notación Unicode completa.
uniques(array a)
devuelve una serie con los duplicados borrados.
urlify(string s)
devuelve una serie donde reemplaza los espacios en blanco con guión bajo.
xor(boolean a, boolean b)
devuelve boolean, XOR dos valores booleanos.

Controls

filter(expression a, variable v, expression test)
devuelve una serie. Evalúa la expresión a a una serie. Entonces, por cada elemento de la serie, vincula su valor a la variable v, evalúa la expresión test que debería devolver un booleano. Si el booleano es verdadero, lleva v a la serie resultante.
forEach(expression a, variable v, expression e)
devuelve una serie. Evalúa la expresión a a una serie; entonces para cada elemento de la serie vincula su valor al nombre de la variable v, evalúa la expresión e y lleva el resultado a la serie resultante.
forEachIndex(expression a, variable i, variable v, expression e)
devuelve una serie. Se evalúa la expresión a a una serie. Entonces, por cada elemento de la serie, vincula su índice a la variable i y su valor al nombre de la variable v, evalúa la expresión e y desplaza el resultado a la serie resultante.
forNonBlank(expression o, variable v, expression eNonBlank, expression eBlank)
la devolución depende de lo evaluado. Se evalúa la expresión o. Si no está en blanco, vincula su valor al nombre de la variable v, evalúa la expresión eNonBlank y devuelve el resultado. De otra manera (si o evalúa a blanco), evalúa la expresión eBlank y devuelve el resultado.
forRange(number from, number to, number step, variable v, expression e)
devuelve una serie. Se itera sobre la variable v empezando desde from, se incrementa por step cada vez mientras que sea menos que to. Para cada iteración, se evalúa la expresión e y desplaza el resultado a la serie resultante.
if(expression o, expression eTrue, expression eFalse)
la respuesta depende de los argumentos de la expresión. Se evalúa la expresión o. Si es verdadero, evalúa la expresión eTrue y devuelve el resultado. De otra forma, evalúa la expresión eFalse y devuelve su resultado en vez de eso.
isBlank(expression o)
devuelve boolean si o es un error.
isNonBlank(expression o)
devuelve boolean si o no es nulo y no es una cadena vacía.
isNotNull(expression o)
devuelve booleano, si o no es null.
isNull(expression o)
devuelve: boolean. Returns whether o is null.
isNumeric(expression o)
devuelve: boolean. Returns whether o can represent a number
with(expression o, variable v, expression e)
Depends on actual arguments. Evaluates expression o and binds its value to variable name v. Then evaluates expression e and returns that result

Georeferenciación

Sobre el documento 1_entidades_credito se puede extraer la localización en el mapa a través de la API de Google.

Sobre la columna de dirección se elige la opción Edit column y dentro la opción Add column by fetching URLs y se escribe la expresión: 

"http://maps.googleapis.com/maps/api/geocode/json?&sensor=false&address=" + escape(value, "URL")

Con ello se obtiene la dirección en formato JSON de la API de Google. De ese JSON se extrae la longitud y la latitud activando la opción Edit column y dentro la opción Add column based on this column:

Obtener la latitud: 

value.parseJson().results[0].geometry.location.lat

Obtener la longitud: 

value.parseJson().results[0].geometry.location.lng

Para comprobar que la latitud y la longitud de los puntos es correcta, se exporta a CSV y se importa en CartoDB para ver dónde se representan los puntos.

Si no se representan en España, quizás sea porque la dirección sea y haya más localidades en el mundo con esa dirección. De hecho, se puede comprobar mirando el JSON que hay direcciones con más de un punto.

Por eso hay que añadir más datos a la dirección: el código postal, la localidad y el país.

Para crear una columna con el país que no sea España sino Spain, se crea una columna basada en esta columna: 

value.replace(value,"Spain")

La dirección está en mayúsculas. Refine contempla tres funciones para tratar con los caracteres de texto:

  • toLowercase(), convierte todos los caracteres en minúsculas.
  • toUppercase(), convierte todos los caracteres en mayúsculas.
  • toTitlecase(), la primera letra en mayúsculas.

Para tener una dirección más legible, creamos una columna basada en dirección, con el nombre dir, y se emplea la fórmula: 

toTitlecase(value)

Y entonces se crea una nueva columna de dirección con todos los datos de las otras columnas con esta fórmula: 

cells["dir"].value + "," + " " + cells["cp"].value + "," + " " + cells["localidad"].value + "," + " " + cells["country"].value

Y se repite el proceso de georeferenciación.

Nominatim

También podemos utilizar cualquier otro servicio de geodatos como Nominatim y obtener longitud y latitud: 

'http://open.mapquestapi.com/nominatim/v1/search.php?' + 'format=json&' + 'q=' + escape(value, 'url')

Expresiones regulares

Se pueden utilizar expresiones regulares en las siguientes funciones de GREL:

  • replace
  • match
  • partition
  • rpartition
  • split

Por ejemplo, sobre esta estructura: 

- Nombre Apellido (Empresa) Lorem ipsum

Con esta expresión: 

value.match(/- ([^\(]*)\s\(([^\)]*)\).*/).join('::')

A través de la función value.match, utilizamosbusca la expresión de la siguiente manera con RegExp:

  • Para indicar que se trata de una expresión regular, se escribe entre barras //
  • El guión y el espacio en blanco se escriben tal cual, aunque éste podría representarse por el símbolo \s.
  • Luego, selecciona lo que no empieza por paréntesis ([^\(]*)
  • Y que preceden a un espacio en blanco seguido por una apertura de paréntesis \s\(
  • Todo junto: ([^\(]*)\s\(
  • Después extrae lo que aparece antes del cierre del paréntesis: ([^\)]*)\)
  • Por último, descarta todos los caracteres hasta el final de la cadena: .*
  • Finalmente con la función join une las dos coincidencias separándolas con dobles dos puntos.

Recursos

Recetas y consejos de OpenRefine

Este blog recoge consejos y recetas sobre Open Refine http://googlerefine.blogspot.com.es/

Verificación de fuentes de datos

Con Refine puedes contrastar tus datos contra cualquier servicio web que soporte la API de verificación de Refine. En la investigación The Dollars for Docs Data Guides 1. Using Google Refine to Clean Messy Data, guía de Refine por Dan Nguyen tal como lo utilizaron ProPublica para su proyecto Dollars for Docs para limpiar datos y verificar las imperfecciones de los datos que habían obtenido.

Limpieza de datos con Open Refine

Carolina Cristanchi dedica cuatro artículos de su blog a Open Refine: para novatos, para curiosos, para fans y para quienes quieren llegar a una visualización impecable. Además, está en castellano.

Lista de expresiones regulares

De la web de ayuda de LibreOffice, https://help.libreoffice.org/Common/List_of_Regular_Expressions/es

Recursos externos de OpenRefine

Listado de turoriales y recursos desarrollados fuera del wiki de OpenRefine https://github.com/OpenRefine/OpenRefine/wiki/External-Resources

Reconcilia tus metadatos

De Free Your Metadata, cómo reconciliar tus metadatos: http://freeyourmetadata.org/reconciliation/

Utiliza Refine para manipular HTML

Using Refine to manipulate HTML es una guía interesante sobre el uso de Refine para transformación de texto HTML.

http://www.meanboyfriend.com/overdue_ideas/2014/12/using-openrefine-to-manipulate-html/

How to clean a converted PDF using Refine

Otro uso más de Refine: limpiar el texto extraído de un PDF:

Limpiando Datos con Google Refine

Taller impartido por David Cabo sobre Google Refine, una herramienta gratuita de Google que permite organizar y transformar datos, ya sea de la web o de las propias bases de datos.

Actividad en el marco de la Segunda sesión formativa de periodismo de datos: Análisis y tratamiento de datos.

Accede a la visualización desde la siguiente dirección web: http://medialab-prado.es/article/limpiando_datos_google_refine http://medialab-prado.es/mmedia/8/8502/8502.flv/download

Introducción a las Expresiones Regulares con Open Refine

Taller de Introducción a las Expresiones Regulares con Open Refine impartido por Juan Elosua en la jornada decimoséptima del Grupo de Periodismo de Datos de Medialab-Prado. Las expresiones regulares las podemos utilizar en nuestra vida diaria, bien para buscar un archivo, para cambiar varios de nombre o para trabajar con hojas de cálculo.

Accede a la visualización desde la siguiente dirección web: http://medialab-prado.es/mmedia/13534/view

Introduction to Open Refine:

Explora los datos. OpenRefine puede ayudarte a explorar grandes conjuntos de datos inconsistentes o sucios, limpiar o transformar datos o reconciliar y encontrar ddatos

Accede a la visualización desde la siguiente dirección web:

Refine por Freeyourmetadata

por Sydney Powerhouse Museum No importa cuanto esfuerzo tengas que poner para conseguir unos datos limpios, siempre vas a tener que hacer alguna limpieza de los mismos. Con Refine podemos hacer algunas partes del proceso de manera semi automática.

Accede a la visualización desde la siguiente dirección web:

Google Refine para el periodismo de investigación

Dan Nguyen y Joe Kokenge de ProPublica realizan una introducción a una de las mejores herramientas para periodistas de cualquier nivel técnico. Aprenderás las características básicas de forma muy potente con algunos ejemplos de periodismo de datos. No require conocimientos previos de estadística, programación o Excel.

Hay un vídeo y un tutorial de cómo han usado Refine para la premiada investigación sobre ProPublica:

https://dannguyen.github.io/NICAR-Google-Refine/

Bibliografía

Dudas

  • sort anidados?
  • cluster que no encuentra obra
  • cambiar en todas las columnas un value.replace

Taller de limpieza y análisis de datos

Un conjunto de técnicas y consejos básicos para periodistas de datos que se enfrentan por primera vez a datasets sin estructurar.

Vamos a trabajar con un caso real --Las tarjetas black de Cajamadrid-- modificado para poder aplicar varias técnicas de limpieza.

Es bastante común encontrarse con datasets fragmentados, mal formateados, con errores... Para poder realizar un buen analisis, antes tenemos que unificar, limpiar y estructurar.

En este caso tenemos tres archivos Excel:

  • tarjetas_01.xlsx
  • tarjetas_02.xlsx
  • tarjetas_03.xlsx

que contienen los registros de las tarjetas black de los ejecutivos de Cajamadrid.

Tenemos que unir los tres. Cada archivo tiene varias sub-hojas, lo que dificulta un poco la tarea.

Los archivos están en /datasets/hoja_calculo_tarjetas_black/.

En este directorio hay varias carpetas numeradas. Si te pierdes en alguno de los pasos, puedes ir a la carpeta siguiente y coger el dataset ya tratado.

Necesitaremos:

Descargas opcionales:

1. Unificar

1.1. Hoja de cálculo

Bajamos el archivo /datasets/hoja_calculo_tarjetas_black/01_originales/tarjetas_01.xlsx y lo subimos a Google Drive.

Una vez en Drive, lo abrimos con Google Spreadsheets

spreadsheet-icon.png

¡Problema! Hay varias sub-hojas.

Para analizar los datos cómodamente necesitamos una sola tabla que contenga todos los datos. Google Spreadsheets no tiene opción de juntar todas las sub-hojas. Pero con un poco de Javascript podemos sacar el contenido en varios archivos csv y luego juntarlos.

  • Vamos a Herramientas --> Editor de Secuencias de comandos...
  • Le damos nombre al script, por ejemplo "hojas\_a\_csv". Borramos todo e insertamos el siguiente código, que nos servirá para guardar todas las hojas en CSV.
    function onOpen() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var csvMenuEntries = [{name: "export as csv files", functionName: "saveAsCSV"}];
      ss.addMenu("csv", csvMenuEntries);
    };

    function saveAsCSV() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheets = ss.getSheets();
      // crea un directorio con el nombre de la hoja
      var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
      for (var i = 0 ; i < sheets.length ; i++) {
        var sheet = sheets[i];
        // añade la extension ".csv" al nombre de la hoja
        fileName = sheet.getName() + ".csv";
        // convierte todos los datos disponibles de la hoja a formato csv
        var csvFile = convertRangeToCsvFile_(fileName, sheet);
        // crea un archivo con el nombre y los datos csv
        folder.createFile(fileName, csvFile);
      }
      Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
    }

    function convertRangeToCsvFile_(csvFileName, sheet) {
      // toma el rango disponible de datos en la hoha de calculo
      var activeRange = sheet.getDataRange();
      try {
        var data = activeRange.getValues();
        var csvFile = undefined;

        // hace un loop en el rango de los datos y crea un string con los datos csv
        if (data.length > 1) {
          var csv = "";
          for (var row = 0; row < data.length; row++) {
            for (var col = 0; col < data[row].length; col++) {
              if (data[row][col].toString().indexOf(",") != -1) {
                data[row][col] = "\"" + data[row][col] + "\"";
              }
            }

            // une las columnas de cada fila
            // añade retorno de carro al final de cada fila, excepto la ultima
            if (row < data.length-1) {
              csv += data[row].join(",") + "\r\n";
            }
            else {
              csv += data[row];
            }
          }
          csvFile = csv;
        }
        return csvFile;
      }
      catch(err) {
        Logger.log(err);
        Browser.msgBox(err);
      }
    }
  • Le damos a Guardar y ejecutamos la función onOpen
  • Una vez ejecutado, volvemos a la pestaña de la hoja de cálculo. Debería aparecer un nuevo menú llamado csv.
  • Seleccionamos csv-->Export as csv files. Puede tardar un poco. Un pop up nos avisa del destino de los archivos: una carpeta en el directorio raíz de Google Drive.

¡Bien! Ya tenemos nuestros archivos csv.

  • Vamos a la carpeta de salida en Drive, seleccionamos y botón derecho --> Descargar. Nos sandrá un zip con todos los archivos.
  • Descompriminos en nuestro escritorio y le damos un nombre más reconocible a la carpeta.

Repetir el proceso con los archivos tarjetas_02.xlsx y tarjetas_03.xlsx.

Tenemos 64 archivos .csv Vamos a unirlos con Talend Open Studio for Big Data.

1.2. Talend Open Studio for Big Data

Talend es una herramienta muy potente que permite trabajar con datos a través de una interfaz gráfica, sin tener que escribir código. Pero tiene una curva de aprendizaje bastante pronunciada y la cantidad de opciones puede ser abrumadora.

Utiliza componentes que se van añadiendo a una mesa de trabajo y se conectan entre sí a modo de diagrama. Esto hace que sea muy flexible a la hora de afrontar un problema. Podemos crear nuestros propios trabajos personalizados.

En este caso vamos a crear un trabajo que nos permita extraer todos los archivos csv de un directorio y fundirlos en uno.

  • Abrir Talend --> Create a new project

talend_01.png

  • Create a new... --> Job (o "Trabajo")

talend_02.png

  • En la pestaña derecha, Palette buscar el componente tFileList. Arrastrarlo a la mesa de trabajo.

talend_03.png

  • Hacer lo mismo con los componentes tFileInputDelimited, tUnite, tLogRow y tFileOutputDelimited
  • Conectar de izquierda a derecha todos los componentes. Botón derecho en el primer componente -->Fila-->Iterate y pinchamos con el botón izquierdo en el siguiente componente.

talend_05.png talend_06.png

Vamos a definir las opciones de entrada y salida de nuestros csv.

  • Pinchar en tFileList\_1 y a continuación, abajo, en la pestaña Component. Seleccionar el directorio donde tenemos todos los archivos csv.

talend_07.png

  • Pinchar en tFileInputDelimited\_1 y a continuación, abajo, en la pestaña Component. En Nombre de Archivo/flujo pulsamos Ctrl-Espacio y en el desplegable seleccionamos tFileList.CURRENT\_FILEPATH

talend_08.png

  • Como Separador de Campo ponemos ","
  • Marcamos la casilla opciones csv. Es importante, porque si no definimos bien los separadores de fila y columna de nuestros csv, se pueden perder datos.
  • Vamos a definir las columnas que tienen nuestros archivos csv. Pinchamos en Edit Schema y en el signo + 9 veces para agregar 9 columnas. Nombramos cada columna con el nombre que tienen en los csv. en Tipo (de dato) dejamos todos como string. Al aceptar nos preguntará si propagamos los cambios al resto de componentes. Le decimos que sí.

talend_09.png

  • Pinchar en tLogRow\_1 y a continuación, abajo, en la pestaña Component. En Mode Seleccionar Table.

talend_10.png

  • Pinchar en tFileOutputDelimited\_1 y a continuación, abajo, en la pestaña Component. Como Separador de Campo ponemos ";". Hay que fijarse en Nombre de Archivo. En esa ruta se guardará nuestro archivo csv resultante. Por defecto se llamará out.csv.

talend_11.png

  • Finalmente vamos a la pestaña Run y ejecutamos el trabajo con el botón Run

talend_12.png Ya tenemos nuestro archivo único, out.csv. Vamos a limpiarlo con Open Refine.

2. Limpiar

2.1. Open Refine

Al ejecutar Refine no se abre ninguna ventana. Lo que hace Refine es montar un servidor local en el puerto 3333. Así que vamos a abrir una pestaña del navegador con la dirección http://127.0.0.1:3333/.

refine_01.png

  • Vamos a la pestaña Create Project y seleccionamos nuestro archivo, out.csv.

refine_02.png

  • Refine previsualiza la tabla. Tenemos que seleccionar la codificación de caracteres: UTF-8. Si tenemos un falso encabezamiento de columnas, hay que seleccionar Ignore first 1 line(s) at beginning of file.

refine_022.png Para poder analizar nuestra tabla necesitamos que los elementos que se repiten (por ejemplo, todas las entradas con un nombre y apellidos) sean exactamente iguales. En nuestra tabla hay espacios de más que no vemos, o puntos al final de un nombre. Estos caracteres extra harán que más tarde, al agrupar los elementos para su análisis, aparezcan grupos distintos con entradas que deberían ser iguales.

  • Vamos a comprobarlo. Vamos a la columna NOMBRE. Pinchamos en el encabezamiento y seleccionamos Facet --> Text facet.

refine_023.png A la izquierda aparecen todas las entradas distintas de la columna NOMBRE. Al lado de cada una aparece el número de filas que tiene cada una. Por ejemplo: Hay 448 filas con el NOMBRE "ACOSTA CUBERO, JOSE".

Podemos ver que, por ejemplo "CAFRANGA CAVESTANY, MARIA CARMEN" aparece dos veces. No debería ser así, vamos a arreglarlo.

Además, tenemos una columna con dos tipos de entradas juntas: Nombre de comercio y Actividad. Para saber, por ejemplo, cuanto dinero gastaba un asesor en un comercio determinado, tenemos que separar esos conceptos.

¡Empecemos!

  • Lo primero es limpiar las celdas de espacios no deseados. Vamos a la columna NOMBRE. Pinchamos en el encabezamiento y seleccionamos Edit cells --> Common transforms --> Trim leading and trailing whitespace. Repetimos el proceso en todas las columnas.

refine_03.png

  • Ahora vamos a editar masivamente los nombres en la columna NOMBRE para unificar los que no son iguales. Para ello usaremos Edit cells --> Cluster and edit

refine_04.png

  • Cluster & edit reconoce los patrones de caracteres parecidos y sugiere fusiones. Comprobamos que las sugerencias son correctas. Pinchamos en Select all y a continuación en Merge selected & re-cluster. Si todo va bien, en un segundo reconocimiento todo está limpio. Refine utiliza distintos métodos de reconocimiento de patrones. El que viene por defecto nos sirve en este caso, pero a veces el patrón es más complejo.
  • Ahora vamos a separar esa columna con dos tipos de entradas juntas. Pinchamos en el encabezado Nombre\_Comercio\_y\_Actividad y seleccionamos Edit column --> Split into several columns

refine_05.png

  • Tenemos varias opciones para separar la columna. En nuestro caso, hay un patrón de caracteres que separa un concepto de otro: tres guiones bajos. Así que vamos a poner tres guiones bajos en el campo Separator.

refine_06.png

  • Podemos aplicar más transformaciones. Por ejemplo, separar las columnas de los apellidos y el nombre por la coma. Alternar el nombre de las columnas, sustituir caracteres de forma masiva...
  • Cuando nuestra tabla esté limpia, exportamos. Vamos a guardar en formato Excel para poder trabajar indistintamente en Excel o Google Spreadsheets. Export --> Excel

3. Analizar

3.1. Estructurar

Para realizar un buen análisis es muy importante que los datos estén bien estratificados.

Lo principal: "Pensar en vertical". Cada columna aporta una capa de conocimiento. Cuanto más concreto, mejor.

Por ejemplo: Tenemos una columna con la fecha completa de cada transacción. Pero si queremos un análisis más profundo, podemos separar esa columna en días, meses y años.

  • Hoja de cálculo

    Volvemos a Google Drive. Subimos nuestro archivo limpio y lo abrimos con Spreadsheets.

    • Buena práctica 1: Añadimos al nombre de nuestro archivo \_ORIGINAL y creamos una copia con el añadido \_TRABAJO. Así, si comentemos errores podemos volver a la hoja original.
    • Buena práctica 2: Añadimos una columna con el encabezado ID. Ponemos un 1 en la primera fila, y rellenamos la serie automáticamente.
    • Vamos a crear columnas con rangos de fecha que nos serán útiles para analizar los días, meses y horas en los que se hacía mayor uso de las tarjetas. Creamos columnas llamadas DIA, MES, AÑO y RANGO HORA
    • La función =TEXT (o =TEXTO si estamos en Excel y tenemos una versión en castellano) convierne un número a texto con un formato predefinido. En la columna MES escribimos =TEXT(B2;"MMMM"). Estamos diciendo que tome el valor de la celda B2 y de ahí pase el mes a texto, en formato largo: "MMMM".
    • Hacemos algo similar en la columna AÑO: =TEXT(B2;"AAAA")
    • Y en la columna DIA: =TEXT(B2;"DD")
    • Además vamos a sacar las horas. Pero los datos no están en formato de hora hh:mm:ss por lo que tenemos que sustituir el punto . por dos puntos :. Seleccionamos la columna entera y damos a Editar --> Buscar y sustituir.
    • Ahora sí, En la columna RANGO HORA escribimos: =TEXT(F2;"hh")

3.2. Preguntar a los datos

  • Para interrogar a los datos usamos tablas dinámicas. Hay que empezar por lo general (¿cuanto dinero se han gastado en total?, ¿quién ha gastado más dinero?) a lo concreto (¿Quién hizo el cargo más grande? ¿En qué días se usaron más las tarjetas?)
  • Hoja de cálculo
    • Tablas dinámicas
      • Colocamos en cursor en la casilla A1. Vamos al menú Datos --> Tabla dinámica
      • En el apartado Valores seleccionamos IMPORTE. A continuación, vamos probando conjuntos de datos en los apartados Filas y columnas
  • Base de datos

    Excel tiene un límite de 1,048,576 filas y 16,384 columnas. Para analizar bases de datos grandes tenemos que utilizar herramientas de tratamiento de bases de datos. Existen varios lenguajes de bases de datos relacionales. El más extendido es SQL, así que vamos a utilizar una herramienta open source gratuita: PostgreSQL.

    • PostgreSQL

      SQL es un lenguaje declarativo para hacer consultas y modificaciones en bases de datos. Si tenemos tiempo, practicaremos unas queries a la base de datos del ministerio de Agricultura que agrupa todas las playas de España. El archivo está en datasets/sql_playas/playas.sql

Date: 2016-07-16 Sat 00:30

Author: Adolfo Antón Bravo

Created: 2021-11-05 Fri 17:15

Validate