Esta nota es la segunda parte de “Magia en Data Studio p. 1” que podes consultar haciendo click aqui. En esta ocasión, trataremos los problemas que nos encontramos cotidianamente trabajando con Data Studio. Por ejemplo, dificultades para generar tablas, gráficos y filtros como los imaginamos en nuestra cabeza. En la primer parte veremos los formatos wide y long y en la segunda, cómo pasar de uno a otro mediante DataPrep, BigQuery, R y Python.

Repasando…

En la nota anterior, veíamos que las bases de datos tienen distintas funciones y que incluso las tablas de datos estructurados con la misma información pueden presentarse en 2 formatos diferentes que denominamos wide y long. Vimos cómo cada uno de esos formatos se puede utilizar y qué restricciones hay en Data Studio para visualizar la información.

A lo largo de la nota usamos un ejemplo sencillo, presentado en las 2 tablas que siguen a continuación:

Tabla en formato wide
Tabla en formato long

En esta nota, vamos a ver de qué manera podemos transformar una tabla de un tipo a otro mediante la utilización de 3 herramientas: DataPrep (de GCP), BigQuery y el lenguaje R.

Transpose, melt y pivot de tablas

Antes de pasar a las herramientas, es necesario aclarar los conceptos porque prestan lugar a la confusión. La operación que estamos buscando realizar es pivotear la tabla, no transponerla. La transposición implica intercambiar las filas por columnas. Es decir, si un dato está en la celda (1,4) (1era fila, 4ta columna), al transponerla va a pasar al lugar (4,1) (4ta fila, 1era columna). En cambio, la operación de pivot o melt implica redefinir cuáles son las métricas y dimensiones de la tabla.

En este caso, al transponer, este sería el resultado final:

Tabla con formato transpuesto

A diferencia de esta operación, pivotear una tabla requiere una definición por lo cual los pasos a realizar en cada caso van a diferir según cuál sea el resultado final que se quiera alcanzar.

A continuación, vamos  a ver cómo realizar esta transformación en diferentes entornos, siempre a partir de los datos de una Sheet pero trabajando en con las herramientas de Google Cloud Platform (GCP).

DataPrep

La primer herramienta que vamos a utilizar es DataPrep. Es parte del entorno de GCP y sirve para la preparación y transformación de datasets. Uno de sus principales atractivos es que tiene un montón de transformaciones predefinidas para aplicar a los datos. Además posee una User Interface (UI) muy accesible y cómoda, con sugerencias inteligentes de transformaciones basadas en el tipo de dato de cada fuente. Por último, una vez elaborada la “receta” (serie de pasos para la transformación) permite visualizarla y automatizarla en DataFlow (otro de los productos de GCP).

Sin ahondar mucho más en el funcionamiento de DataPrep vamos a ver cómo podemos utilizarlo concretamente para este caso de uso partiendo de la información en una tabla de Google Sheets.

1. Conectar la Sheet con BigQuery

El primer paso es disponibilizar la información en BigQuery para poder proceder a la transformación. DataPrep solo admite tablas en BigQuery que sean nativas, es decir tablas almacenadas cuyos datos no varían. Por eso si el origen de la información es una Sheet (que se puede actualizar constantemente), tenemos 2 opciones. La primera es descargar el archivo .csv a nuestro disco local y subirlo a DataPrep (a un repositorio temporal de Google Cloud Storage) con la opción de Upload file. La otra forma es crear una tabla federada (es decir que se vuelve a crear cada vez que la consultamos) alimentada desde la Sheet original. Sobre esta tabla realizamos una query SELECT * (para obtener la tabla entera) y la guardamos como una nueva tabla en el dataset. Vamos a ver el paso a paso de esta última opción ya que es la más complicada. 

Para eso vamos a un dataset en nuestro proyecto y clickeamos en “Create table”. De las opciones que aparecen, es importante que modifiquemos las siguientes: formato “Google Sheet”, autodetección del esquema y saltear la 1er fila en caso que funcione como encabezado.

Luego, realizamos la query SELECT *  y grabamos los resultados como una tabla en el mismo dataset.

2. Conectar la tabla de BigQuery con DataPrep

DataPrep reconoce 3 fuentes para los datos: algún archivo compatible almacenado en el disco local, en un bucket de Cloud Storage (GCS, que es algo así como un filesystem pero en la nube) o en una tabla de BigQuery. Para este último caso, abrimos DataPrep (como todo producto de GCP va a tener que estar vinculado a un proyecto), clickeamos en Import data y simplemente seleccionamos el dataset y la tabla pertinentes que creamos en el punto anterior:

  1. Generar un flow para la transformación

Una vez que tenemos el dataset creado en DataPrep, procedemos a abrirlo en un flow que nos permita realizar la transformación que buscamos.

3. Realizamos la transformación sobre los datos

La función que buscamos no es la de pivot sino su inversa, unpivot. Seleccionamos las columnas con las métricas que queremos despivotear (sic) y clickeamos en el botón pertinente. Podremos ver las columnas seleccionadas a la derecha y un preview abajo que muestra el resultado final, coincidente con el formato long que estamos buscando generar.

4. Elegimos la manera en la que grabaremos los resultados

Al darle a Run Job, se abre una ventana donde podemos seleccionar cuál será el output de la transformación. En este caso, elegimos generar una nueva tabla en el dataset en BigQuery y alojar allí los resultados.

5. Corroboramos que se haya ejecutado correctamente

Al ingresar a BigQuery, vemos que se generó la tabla nativa y podemos consultarla para quedarnos seguros que la información está como la queríamos.

BigQuery

El segundo método que vamos a probar tiene que ver con realizar la transformación directamente en BigQuery. Para este ejemplo sencillo, no reviste mayor dificultad; sin embargo, veremos que es un método que resulta difícil de aplicar cuando la tabla cuenta con muchas más columnas a pivotear.

Partiendo de la tabla nativa en formato wide que generamos en el punto anterior, vamos a ver de qué manera podemos reconvertirla en el formato long desde BigQuery.

1. Visualizamos la tabla original

Lo primero que hacemos es un SELECT * para ver los datos de la tabla original y chequear que estén en el formato wide

2. Armamos un array con las columnas

El segundo paso consiste en armar un array que vamos a llamar “agregado” en formato key-value donde la key sea el nombre de la columna que en el nuevo formato va a pasar a representar el valor de la dimensión “Fruta”, mientras que el value va a ser el valor de la celda indicada o la “Cantidad”. El resultado final va a ser el siguiente:

Este paso requiere la escritura manual de los valores de la dimensión “Fruta” (la key de nuestro array). Esta es la principal limitación a la hora de escalar este proceso para tablas más grandes. Sin embargo antes de eso vamos a ir desandando paso a paso cómo llegamos hasta esta query. Si la entendés con solo leerla, podés saltear esta explicación y pasar directo al próximo punto.

Supongamos que queremos pasar al formato long y lo primero que queremos seleccionar es la cantidad de bananas y manzanas por día. Probamos intuitivamente la siguiente query:

Vemos que el resultado se muestra en columnas diferentes. Por eso el siguiente paso es armar un array para cada día que pueda reunir ambas cantidades (y más cuando lo extendamos a las peras).

Ahí va tomando color. Como vemos, los corchetes indican la creación de un array por cada valor de la dimensión restante (dia_de_pedido en este caso). Sin embargo, necesitamos dar un paso más para diferenciar cuál de las cantidades refiere a cada fruta. Para eso usamos el tipo de dato STRUCT que nos permite representar un objeto que tenga distintos elementos. En este caso, dentro del array de cada día vamos a incorporar la dimensión que indica el nombre de la fruta además de la cantidad correspondiente.

La diferencia entre esta query y la que inaugura el punto consiste solamente en que incorpora las peras y utiliza nombres más claros para cada columna.

3. Desanidamos el array

Una vez que completamos el paso anterior, tenemos una estructura que se parece bastante a nuestro resultado final. Lo que resta es desanidar los campos que están en cada array y consolidar esos resultados en filas diferentes.

Lo primero que hacemos es darle un nombre a la tabla en la que formamos el array por día. Luego procedemos a utilizar UNNEST para desanidar el array y seleccionamos todos los campos para ver en qué forma se trae esa información.

4. Seleccionamos los campos de la tabla final

Con el resultado en la mano, vemos que lo único que nos falta es quedarnos con la primera línea de cada array y con las columnas dia_de_pedido, key y value.

Finalmente con esta query logramos llegar al resultado buscado al principio que es la tabla en formato long chequeando que efectivamente los valores son los correctos.

Esta query la podemos utilizar para guardarla en una nueva tabla (mediante Save query) o bien para generar una scheduled query (para mantener la tabla actualizada con nuevos datos).

Una alternativa más sencilla o intuitiva pero a su vez menos escalable es la siguiente:

En este caso, diseñamos la tabla final a partir de la selección de cada bloque de filas y uniendolos a partir del UNION ALL.

Lenguajes R y Python

La última alternativa que vamos a revisar tiene que ver con utilizar los datos de BigQuery en un entorno que nos permita correr código en otros lenguajes, como R y Python. 

¿Por qué R? Porque es un lenguaje muy utilizado en la ciencia de datos (particularmente con fines estadísticos) y porque uno de sus paquetes más utilizados, tidyR, tiene una función específica para este proceso.

¿Por qué Python? Porque es otro de los estándares a la hora de procesar grandes volúmenes de información y uno de sus paquetes básicos, pandas, también cuenta con la función de pivotear tablas. Además, tiene disponible un entorno muy cómodo para procesar datos de GCP que son los notebooks de Google Colab.

Notebook con R

En R, hay 2 alternativas para poder conectarse con los datos de BigQuery. Una de ellas es correr R en el disco local y utilizar un paquete llamado BigRQuery que sirve para combinar el lenguaje de R con los datasets a los que tengamos acceso en BigQuery. La otra es utilizar AI Platform Notebook y replicar el comportamiento anterior pero en vez de correrlo localmente, utilizando el entorno de GCP. Vamos a llevar adelante este último camino:

Desde la AI Platform Notebook, creamos una nueva instancia de R 3.6. Una vez que se crea, abrimos el Jupyter Notebook.

A continuación cargamos las librerías necesarias (BigRQuery para las consultas, tidyR para el pivoteo). Luego autenticamos con los permisos necesarios, seleccionamos el proyecto que contiene la tabla y escribimos una query para traer todos los datos y almacenarlos en el contexto de R.

Una vez hecho esto, ya tenemos la información para manipularla mediante el lenguaje R. En este caso vamos a usar una función llamada gather que permite pasar del formato wide al long y viceversa.

El último paso sería alojar la tabla generada en BigQuery. Para eso, primero almacenamos el resultado en un dataframe dentro de R y luego utilizamos el comando de bigRquery llamado insert_upload_job.

Por último, refrescamos la vista de BigQuery y vemos la tabla con formato long recientemente creada.

Notebook con Python

Para realizar el mismo procedimiento pero con el lenguaje Python, podemos crear un nuevo Notebook y seleccionamos el kernel Python 3.

Una vez abierto, con la función mágica %%bigquery llamamos al SELECT * de la tabla en formato wide y elegimos un nombre para el dataframe donde almacenamos los resultados.

Teniendo el dataframe, utilizamos la función melt de pandas para pasar wide a long.

Vemos que funciona correctamente así que almacenamos nuevamente en un objeto. 

A continuación nos queda escribir estos datos en una tabla dentro del dataset de BigQuery. Para eso, primero tenemos que instalar el paquete pandas-gbq, que seguramente nos pida reiniciar el kernel. A continuación cargamos el paquete y procedemos a almacenar los datos en una tabla en el dataset seleccionado en el proyecto de GCP.

Por último, vamos a BigQuery y chequeamos que todo se haya realizado correctamente.

Por último, siempre que trabajemos con AI Platform Notebook tenemos que recordar apagar las instancias que hayamos generado porque sino seguirán consumiendo datos (y por ende, plata) del proyecto de GCP.

Resumiendo…

En esta nota vimos de qué manera pasar de una tabla wide a una long. En todos los casos partimos de una sheet en Google Drive que subimos a BigQuery.

  • Con DataPrep usamos la función unpivot y corrimos el trabajo en DataFlow
  • Con BigQuery utilizamos una query a partir de hacer un array con cada columna y el nombre de la misma para luego desanidarlos en la tabla final
  • Para R y Python utilizamos AI Platform Notebook que nos permite crear notebooks que corren con ambos lenguajes en el entorno de GCP
  • En R usamos el paquete BigRQuery y la función gather del paquete tidyR
  • En Python usamos la función melt del paquete pandas y pandas_gbq para guardar los resultados

Tomás Reneboldi
Head of Operations