Capítulo 12 Soporte en R

En este capítulo vamos a usar R para realizar las transformaciones para obtener un esquema en estrella a partir de los datos de nuestra provincia.

Usaremos el paquete flattabler (Samos 2020a) para transformar los datos del archivo Excel en una tabla plana. Por otro lado, partiendo de la tabla plana obtenida, o a partir del archivo CSV con los datos equivalentes, y del resto de archivos de datos, usaremos los paquetes de tidyverse (Wickham et al. 2019) para generar una tabla plana con todos los datos; por último, mediante el paquete starschemar (Samos 2020b), generaremos el diseño en estrella.

Objetivos del capítulo

  • Usar R para hacer transformaciones básicas de datos.

  • Transformar una tabla dinámica en una tabla plana mediante R.

  • Generar un diseño en estrella a partir de una tabla plana en R.

12.1 R y RStudio

R es un lenguaje de programación de código libre frecuentemente utilizado en Estadística y Análisis de Datos.

Sus funcionalidades básicas se complementan con las desarrolladas en paquetes específicos que se instalan y usan en el entorno de trabajo. CRAN es una red de servidores ftp que almacenan el código y documentación de R, junto a paquetes. Puede haber paquetes en otros servidores como por ejemplo GitHub. Los paquetes de CRAN están verificados, pueden fallar, pero no van a realizar ninguna operación dañina, fuera de nuestro control, en nuestro sistema. Todos los paquetes que se usan en esta actividad son paquetes de CRAN.

Para trabajar con R es recomendable usar RStudio un IDE (Integrated Development Environment) gratuito.

Para usar un paquete, previamente tenemos que instalarlo. Para instalar un paquete de CRAN tenemos que ejecutar la función install.packages indicando el nombre del paquete, como se muestra a continuación.

install.packages('flattabler')

Los paquetes que se usan en este capítulo están previamente instalados en el entorno de prácticas, por lo que no sería necesarios volverlos a instalar. En cualquier caso, podemos actualizarlos a las versiones más recientes mediante la función update.packages.

update.packages()

Para ejecutar código en RStudio, creamos un nuevo archivo R, pulsando sobre File > New File > R Script (figura 12.1). A continuación lo guardamos en nuestra carpeta de trabajo, pulsando sobre el icono de la barra de herramientas Save current document.

Crear un archivo R en RStudio.

Figura 12.1: Crear un archivo R en RStudio.

Si vamos a trabajar con archivos, es conveniente establecer como carpeta de referencia la carpeta donde hemos guardado el archivo R de manera que, para acceder a los archivos, partimos de esa ubicación. Para definir esta carpeta como la de referencia, pulsamos sobre Session > Set Working Directory > To Source File Location (figura 12.2).

Establecer la carpeta de trabajo en RStudio.

Figura 12.2: Establecer la carpeta de trabajo en RStudio.

El resultado de esta operación es que se ejecuta la función setwd pasando como parámetro la carpeta donde hemos guardado el archivo. Esta función aparece en la ventana Console en la zona inferior de la ventana de RStudio (figura 12.3).

Ejecutar código R en RStudio.

Figura 12.3: Ejecutar código R en RStudio.

En otro momento, cuando queramos repetir la ejecución, deberemos comenzar definiendo la carpeta de trabajo. En lugar de pulsar sobre la opción del menú, como hemos hecho, podemos copiar la función R generada en el archivo de trabajo y ejecutarla directamente.

Así, para ejecutar código R en RStudio, lo pegamos en nuestro archivo de trabajo, situamos el cursor en la instrucción a ejecutar (o seleccionamos el fragmento de código a ejecutar) y pulsamos sobre el botón Run (figura 12.3).

El resultado se muestra en la ventana Console, es la interfaz de RStudio con R (se muestra la instrucción ejecutada y su resultado).

Para ver el contenido de una variable, lo podemos hacer en la ventana Environment, situada en la zona superior derecha; también podemos ejecutar el nombre de la variable, con lo que se mostrará como resultado en la ventana Console. Si la variable contiene una tabla, puede resultar útil usar la función View, que la muestra en una hoja, junto al resto de documentos que tengamos abiertos.

Por ejemplo si tenemos una tabla en una variable llamada granada y queremos mostrarla en un documento, ejecutaremos la función siguiente.

View(granada)

El resultado obtenido se muestra en la figura 12.4).

Tabla como documento en RStudio.

Figura 12.4: Tabla como documento en RStudio.

Si queremos borrar todas las variables del entorno para comenzar la ejecución de nuevo, es útil conocer la combinación de teclas Ctrl + Mayús + F10, que hace precisamente esa operación.

El código incluido en el resto del capítulo es directamente ejecutable (basta con copiar-y-pegar en el archivo R de trabajo y ejecutarlo, como se ha indicado). Si se desea solo habría que cambiar los nombres de las variables (se ha usado el nombre de la provincia asignada como base) y del archivo CSV con los datos de la provincia asignada; también habría que indicar la carpeta de trabajo correspondiente.

12.2 Transformar una tabla dinámica

El objetivo de este apartado es transformar una tabla dinámica almacenada en formato Excel en una tabla plana equivalente a la almacenada en formato CSV, para ello utilizaremos el paquete flattabler que está pensado para llevar a cabo esa transformación de forma sencilla e intuitiva.

Para componer las funciones, usaremos la sintaxis pipe mediante el operador binario %>% implementado en el paquete magrittr (Bache and Wickham 2020).

El código necesario para leer el archivo Excel y transformar la tabla dinámica en una tabla plana es el siguiente:

library(flattabler)
library(magrittr)

granada <- read_excel_sheet("datos/2871.xlsx") %>%
  define_labels(n_col = 1, n_row = 2) %>%
  remove_top(6) %>%
  remove_bottom(9) %>%
  fill_labels() %>%
  fill_values() %>%
  unpivot() %>%
  dplyr::transmute(
    Municipios = col1,
    Sexo = row1,
    Periodo = row2,
    Total = as.integer(value)
  )

Mediante la función library, importamos las funciones del paquete que se indica al entorno de trabajo actual. Cada función se compone con la siguiente usando el operador binario %>% (que hemos importado de magrittr). Las transformaciones realizadas son las siguientes:

  • read_excel_sheet: leemos una hoja del archivo Excel.

  • define_labels: definimos la cantidad de columnas y filas de la tabla dinámica que tienen etiquetas.

  • remove_top, remove_bottom: eliminamos la líneas de la cabecera y el pie que contienen comentarios, para dejar solo el contenido de la tabla dinámica.

  • fill_labels: rellenamos las etiquetas de la tabla con los valores previos que no se incluyen para no repetirlos.

  • fill_values: rellenamos los valores de la tabla con NA si están vacíos.

  • unpivot: transformamos la tabla dinámica en una tabla plana eliminando los valores no definidos.

  • dplyr::transmute: función del paquete dplyr49 de tidyverse, renombramos las columnas y cambiamos el tipo a la medida.

El resultado obtenido está en la variable granada (el nombre de la provincia asignada), cuyo contenido se muestra a continuación, como resultado de seleccionar el nombre de la variable y pulsar sobre Run para ejecutarla.

## # A tibble: 11,721 x 4
##    Municipios Sexo  Periodo  Total
##    <chr>      <chr> <chr>    <int>
##  1 18 Granada Total 2019    914678
##  2 18 Granada Total 2018    912075
##  3 18 Granada Total 2017    912938
##  4 18 Granada Total 2016    915392
##  5 18 Granada Total 2015    917297
##  6 18 Granada Total 2014    919455
##  7 18 Granada Total 2013    919319
##  8 18 Granada Total 2012    922928
##  9 18 Granada Total 2011    924550
## 10 18 Granada Total 2010    918072
## # ... with 11,711 more rows

Es un objeto de la clase tibble, definida en tidyverse, que implementa una tabla plana.

12.3 Alternativa a transformar una tabla dinámica

Transformar una tabla dinámica en R sin disponer de las funciones del paquete flattabler es bastante complicado. En este caso, tenemos los mismos datos disponibles en forma de tabla plan en formato CSV.

La alternativa a transformar la tabla dinámica es obtener los datos directamente desde el archivo en formato CSV. Esta operación se lleva a cabo mediante el siguiente código:

granada_alt <-
  readr::read_delim(
    "datos/2871.csv",
    delim = ";",
    col_types = readr::cols(.default = readr::col_character())
  ) %>%
  dplyr::mutate_at(
    c("Total"),
    ~ as.integer((
        stringr::str_replace_all(
          .,
          pattern = "\\.",
          replacement = "")
        )
     )
  ) %>%
  dplyr::filter(!is.na(Total))

Las transformaciones realizadas son las siguientes:

  • readr::read_delim: leemos el archivo CSV mediante la función del paquete readr. Indicamos el delimitador de campos y el tipo de las columnas. En este caso leemos todas las columnas como strings.

  • dplyr::mutate_at: la columna Total, tiene separador de miles que tenemos que eliminar (con stringr::str_replace_all) y transformamos el resultado en un valor de tipo entero.

  • dplyr::filter: eliminamos las filas cuyo valor de la medida no esté definido.

El resultado obtenido se almacena en la variable granada_alt, que se muestra a continuación, como se ha hecho antes para la variable granada.

## # A tibble: 11,721 x 4
##    Municipios Sexo  Periodo  Total
##    <chr>      <chr> <chr>    <int>
##  1 18 Granada Total 2019    914678
##  2 18 Granada Total 2018    912075
##  3 18 Granada Total 2017    912938
##  4 18 Granada Total 2016    915392
##  5 18 Granada Total 2015    917297
##  6 18 Granada Total 2014    919455
##  7 18 Granada Total 2013    919319
##  8 18 Granada Total 2012    922928
##  9 18 Granada Total 2011    924550
## 10 18 Granada Total 2010    918072
## # ... with 11,711 more rows

Podemos observar que la estructura es la misma que la obtenida en la variable granada, con el mismo número de filas. El resultado es el mismo.

12.4 Obtener una tabla plana con todos los datos

Partiendo de la variable granada (daría igual partir de granada_alt), mediante funciones del paquete tidyverse, vamos a obtener una tabla plana a nivel individuo, completando las mediciones con otras disponibles en los archivos adicionales de que disponemos.

12.4.1 Obtener una tabla a nivel de individuo

En primer lugar, vamos a reestructurar la tabla para eliminar los datos agregados y que cada fila se corresponda con un individuo (un municipio en un periodo). El código para llevar a cabo estas transformaciones es el siguiente:

granada <- granada %>%
  dplyr::filter(Municipios != "18 Granada" & Sexo != "Total") %>%
  tidyr::pivot_wider(names_from = Sexo, values_from = Total) %>%
  dplyr::transmute(
    cod_municipio = substr(Municipios, 1, 5),
    municipio = substr(Municipios, 7, 50),
    periodo = Periodo,
    mujeres = as.integer(Mujeres),
    hombres = as.integer(Hombres),
    habitantes = mujeres + hombres,
    cod_habitantes = ifelse (
      habitantes > 500000, "9", ifelse (
      habitantes > 100000, "8", ifelse (
      habitantes > 50000, "7", ifelse (
      habitantes > 10000, "6", ifelse (
      habitantes > 5000, "5", ifelse (
      habitantes > 1000, "4", ifelse (
      habitantes > 500, "3", ifelse (
      habitantes > 100, "2", "1")))))))
      )
  )

Las transformaciones realizadas son las siguientes:

  • dplyr::filter: seleccionamos las filas que cumplan las condiciones expresadas, es decir, eliminamos los datos agregados.

  • tidyr::pivot_wider: agrupamos los datos para tenerlos a nivel de individuo (definimos nuevas columnas a partir de los valores de la columna Sexo, los valores se toman de la columna Total).

  • dplyr::transmute: redefinimos las columnas del resultado, en cada caso según la expresión asociada.

El resultado se almacena de nuevo en la variable granada, cuyo contenido se muestra en la figura 12.5, obtenido mediante la función View.

Tabla con filas a nivel de individuo.

Figura 12.5: Tabla con filas a nivel de individuo.

Se puede observar cómo se ha reducido el número de filas, por eliminar los agregados y, principalmente, por agrupar las observaciones a nivel de individuo.

12.4.2 Obtener medidas adicionales de los individuos

Disponemos de datos adicionales en los archivos en formato CSV cod-habitantes.csv y municipios.csv. Vamos a leerlos y a unirlos a los datos de la variable granada.

cod-habitantes.csv

Leemos el contenido del archivo cod-habitantes.csv mediante el código siguiente:

cod_habitantes <-
  readr::read_delim(
    "datos/datos-comunes/cod-habitantes.csv",
    delim = ";", locale = readr::locale(encoding = "ISO8859-1"),
    col_types = readr::cols(.default = readr::col_character())
  )

Es la misma función con la que leímos anteriormente el archivo CSV con los datos de la provincia. En este caso, la diferencia fundamental es que como este archivo está codificado usando la codificación Windows, en lugar de UTF-8 que es la codificación por defecto, debemos indicarlo mediante la función readr::locale. Los datos leídos se almacenan en la variable cod_habitantes50.

Podemos hacer un Left Join entre las tablas almacenadas en ambas variables, mediante la función dplyr::left_join.

granada <- granada %>%
  dplyr::left_join(cod_habitantes, 
                   by = c("cod_habitantes" = "Cod")) %>%
  dplyr::mutate(
    nivel_habitantes = paste(cod_habitantes, 
                             `Tamaño de Municipio`, 
                             sep= " - ")
  )

Definimos una nueva columna nivel_habitantes como resultado de la unión del código y el descriptor del tamaño del municipio, para que la ordenación alfabética y lógica coincidan.

El resultado lo volvemos a almacenar en la variable granada, cuyo contenido se muestra en la figura 12.6.

Resultado de la unión de las tablas.

Figura 12.6: Resultado de la unión de las tablas.

El número de filas es el mismo que antes de esta transformación. Se han añadido las columnas adicionales de la tabla de códigos de tamaño.

municipios.csv

El contenido del archivo municipios.csv lo leemos también indicando su codificación. Para los campos que no son de tipo string y están expresando en formato correcto, indicamos la función para que los lea con el tipo adecuado.

municipios <-
  readr::read_delim(
    "datos/datos-comunes/municipios.csv",
    delim = ";", locale = readr::locale(encoding = "ISO8859-1", 
                                        decimal_mark = ","),
    col_types = readr::cols(.default = readr::col_character(),
                            ALTITUD_M = readr::col_integer(),
                            SUPERFICIE_HA = readr::col_double()
    )
  ) %>%
  dplyr::mutate(cod_municipio = substr(COD_INE, 1, 5))

Añadimos un nuevo campo cod_municipio donde seleccionamos el valor que se corresponde con el código de los municipios de nuestra tabla de datos para poder unirlos. El resultado se ha almacenado en la variable municipios.

Definimos la operación de Left Join entre ambas tablas mediante la función dplyr::left_join, usando los campos cod_municipio de cada tabla.

granada <- granada %>%
  dplyr::left_join(municipios, by = c("cod_municipio" = "cod_municipio"))

Por último, añadimos a la tabla nueva columnas, en particular:

  • decada: derivada a partir de periodo.

  • nivel_superficie: codificando la columna SUPERFICIE_HA en tres niveles que me han parecido adecuados a la vista del valor de este campo para los municipios de la provincia asignada.

granada <- granada %>%
  dplyr::mutate(
    decada = paste(substr(periodo, 1, 3), "0", sep= ""),
    nivel_superficie = ifelse (
      SUPERFICIE_HA > 10000, "3 - más de 10.000 Ha", ifelse (
      SUPERFICIE_HA > 2000, "2 - entre 2.001 y 10.000 Ha", 
                            "1- hasta 2.000 Ha")
      )
  )

Parte del resultado obtenido se muestra a continuación, en la figura 12.7.

Tabla plana con todos los datos, resultado de las transformaciones.

Figura 12.7: Tabla plana con todos los datos, resultado de las transformaciones.

12.5 Generar hechos y dimensiones

Disponemos de una tabla plana a nivel de observación, con todos los datos de interés incluidos en forma de columnas. Esta tabla se encuentra en la variable granada.

A partir de esta tabla, mediante las funciones de transformación proporcionadas por el paquete starschemar, vamos a generar las tablas de hechos y dimensiones.

Como tenemos que seleccionar las columnas que componen cada tabla, es de utilidad la función dput combinada con la función colnames, que presentan en una estructura los nombres de las columnas de la variable. De esta manera solo tenemos que copiar-y-pegar los nombres que necesitemos.

dput(colnames(granada))

En primer lugar, tenemos que definir el esquema según el modelo de datos multidimensional: tenemos que indicar las columnas que componen los hechos y las dimensiones, y el nombre que les damos. Lo definimos en la variable dm_granada.

library(starschemar)

dm_granada <- dimensional_model() %>%
  define_fact(
    name = "padron_granada",
    measures = c(
      "mujeres", 
      "hombres",
      "SUPERFICIE_HA"
    )
  ) %>%
  define_dimension(
    name = "cuando_granada",
    attributes = c(
      "periodo",
      "decada"
    )
  ) %>%
  define_dimension(
    name = "donde_granada",
    attributes = c(
      "cod_municipio", 
      "municipio",
      "CAPITAL", 
      "COMARCA",
      "PROVINCIA", 
      "COMUNIDAD_AUTONOMA",
      "NIVEL_ALTITUD", 
      "nivel_superficie", 
      "nivel_habitantes"
    )
  ) 

Las definiciones realizadas son las siguientes:

  • dimensional_model: define la estructura multidimensional que utilizarán las funciones siguientes.

  • define_fact: define la tabla de hechos, su nombre y las columnas que se corresponden con mediciones.

  • define_dimension: define la tabla de una dimensión, su nombre y las columnas que se corresponden con atributos.

Una vez tenemos los datos (en la variable granada) y el esquema multidimensional definido usando sus columnas (en la variable dm_granada), generamos es esquema en estrella mediante la función star_schema.

st_granada <- star_schema(granada, dm_granada) %>%
  snake_case()

Lo transformamos para que las columnas sigan el criterio de nomenclatura snake case (más adecuado para trabajar con BD) mediante la función snake_case.

Como resultado, obtenemos una estructura que, mediante la función star_schema_as_tibble_list, podemos transformar en una lista de objetos de clase tibble y exportarlos para ser usados en una BD.

tl_granada <- st_granada %>%
  star_schema_as_tibble_list()

El contenido de la variable tl_granada se muestra en las figuras 12.8, 12.9 y 12.10.

Tabla de hechos.

Figura 12.8: Tabla de hechos.

Tabla de la dimensión Cuándo.

Figura 12.9: Tabla de la dimensión Cuándo.

Tabla de la dimensión Dónde.

Figura 12.10: Tabla de la dimensión Dónde.

Se han generado las tablas de las dimensiones, cada una con su llave generada, que a su vez son llaves externas de la tabla de hechos.

Ejercicio 12.1 Genera las tablas de hechos y dimensiones para la provincia asignada51 (captura una pantalla del resultado final para cada una de las tablas obtenidas).

Bibliografía

Bache, Stefan Milton, and Hadley Wickham. 2020. Magrittr: A Forward-Pipe Operator for R. https://CRAN.R-project.org/package=magrittr.

Samos, Jose. 2020a. Flattabler: Obtaining a Flat Table from Pivot Tables. https://CRAN.R-project.org/package=flattabler.

Samos, Jose. 2020b. Starschemar: Obtaining Star Schemas from Flat Tables. https://CRAN.R-project.org/package=starschemar.

Wickham, Hadley, Mara Averick, Jennifer Bryan, Winston Chang, Lucy D’Agostino McGowan, Romain François, Garrett Grolemund, et al. 2019. “Welcome to the tidyverse.” Journal of Open Source Software 4 (43): 1686. https://doi.org/10.21105/joss.01686.


  1. Al no haber importado este paquete, para usar una función suya tenemos que indicar el nombre del paquete como prefijo.↩︎

  2. Tiene el mismo nombre que una columna de la variable granada, pero esto no es un problema.↩︎

  3. No hay que modificar el código incluido en el capítulo, solo los nombres de las variables y del archivo CSV con los datos de la provincia, y la carpeta de trabajo.↩︎