Uno de los formatos de documento más comunes en los que se almacenan datos es en hojas de cálculo, en particular, las creadas y compatibles con el programa Excel, parte de la suite de ofimática Microsoft Office.
Por esta razón, resulta útil conocer cómo trabajar con este tipo de documentos en R.
En este documento revisaremos como importar datos contenidos en hojas de cálculo de Excel usando el paquete readxl así como solucionar algunos problemas a los que nos enfrentamos regularmente al realizar esta tarea.
readxlreadxl es un paquete diseñado para hacer una sola tarea: importar hojas de Excel a R. Esto hace que sea un paquete ligero y eficiente, a cambio de no contar con funciones avanzadas.
Este paquete funciona en Windows, Linux y OSX. A diferencia de otros paquetes diseñados para importar hojas de cálculo de Excel a R, readxl no necesita de la instalación de dependencias adicionales en el sistema operativo, tales como Java o Perl.
readxl es compatible con hojas de cálculo de Excel 97-03, con extensión .xls, y con hojas de cálculo de las versiones más recientes de Excel, con extensión, .xlsx. Sin embargo, no es compatible con hojas de cálculo con extensión .ods, común en paquetería de oficina de código libre como LibreOffice.
Si una celda de una pestaña contiene una fórmula, se importa es el resultado de esa fórmula. En general, lo que ves es lo que obtienes al importar una hoja de cálculo con readxl. Si deseas recuperar información contenida en fórmulas, tendrás que buscar otras alternativas.
El propósito final de usar readxl es contar con datos ordenados (tidy data), esto es, que cada renglón represente una observación y cada columna representa una variable. Cumplir con estas dos condiciones nos proporciona una estructura ideal para realizar análisis estadísticos.
Instalamos readxl y dplyr.
En este documento usaremos dplyr para facilitar algunas tareas. Específicamente usaremos el operador %>% (pipe), para escribir nuestro código. Este operador nos sirve para usar el resultado de la operación a su izquierda como argumento para la operación a su derecha. Así, en lugar de anidar operaciones, las escribimos de izquierda a derecha, lo cual es más fácil de leer para un ser humano.
install.packages(c("readxl", "dplyr"))Cargamos los paquetes.
library(readxl)
library(dplyr)Ahora, necesitamos un documento para trabajar.
La hoja de cálculo de Excel que usaremos es excel_prueba.xlsx.
Puedes descargarla del siguiente enlace a tu espacio de trabajo.
Ya con un documento con el cual trabajar, empecemos a importar datos.
Antes de empezar, necesitamos conocer el contenido de nuestra hoja de cálculo. Podemos usar la función excel_sheets para conocer qué pestañas contiene nuestra hoja de cálculo sin salir de R.
excel_sheets("excel_prueba.xlsx")## [1] "iris" "women" "airquality"Tenemos tres pestañas, “iris”, “women” y “airquality”. Estas corresponden a los conjuntos de datos del mismo nombre disponibles por defecto en R, con algunas modificaciones para fines de ilustrar el uso de readxl.
Con este conocimiento en mente, procedemos a importar el contenido de las tres pestañas.
Usaremos la función read_excel indicando la ruta del documento que queremos importar a nuestro espacio de trabajo de R, sin parámetros adicionales. Asignaremos el resultado de esta función al objeto excel_iris.
excel_iris <- read_excel("excel_prueba.xlsx")Realizamos la exploración del objeto que tenemos. Veremos su estructura (str), un resumen de su contenido (summary), y sus primeros (head) y últimos (tail) renglones.
excel_iris %>% str()## Classes 'tbl_df', 'tbl' and 'data.frame': 150 obs. of 5 variables:
## $ Sepal.Length: num 5.1 4.9 4.7 4.6 5 5.4 4.6 5 4.4 4.9 ...
## $ Sepal.Width : num 3.5 3 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1 ...
## $ Petal.Length: num 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5 ...
## $ Petal.Width : num 0.2 0.2 0.2 0.2 0.2 0.4 0.3 0.2 0.2 0.1 ...
## $ Species : chr "setosa" "setosa" "setosa" "setosa" ...excel_iris %>% summary()## Sepal.Length Sepal.Width Petal.Length Petal.Width
## Min. :4.300 Min. :2.000 Min. :1.000 Min. :0.100
## 1st Qu.:5.100 1st Qu.:2.800 1st Qu.:1.600 1st Qu.:0.300
## Median :5.800 Median :3.000 Median :4.350 Median :1.300
## Mean :5.843 Mean :3.057 Mean :3.758 Mean :1.199
## 3rd Qu.:6.400 3rd Qu.:3.300 3rd Qu.:5.100 3rd Qu.:1.800
## Max. :7.900 Max. :4.400 Max. :6.900 Max. :2.500
## Species
## Length:150
## Class :character
## Mode :character
##
##
## excel_iris %>% head()## Source: local data frame [6 x 5]
##
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## <dbl> <dbl> <dbl> <dbl> <chr>
## 1 5.1 3.5 1.4 0.2 setosa
## 2 4.9 3.0 1.4 0.2 setosa
## 3 4.7 3.2 1.3 0.2 setosa
## 4 4.6 3.1 1.5 0.2 setosa
## 5 5.0 3.6 1.4 0.2 setosa
## 6 5.4 3.9 1.7 0.4 setosaexcel_iris %>% tail()## Source: local data frame [6 x 5]
##
## Sepal.Length Sepal.Width Petal.Length Petal.Width Species
## <dbl> <dbl> <dbl> <dbl> <chr>
## 1 6.7 3.3 5.7 2.5 virginica
## 2 6.7 3.0 5.2 2.3 virginica
## 3 6.3 2.5 5.0 1.9 virginica
## 4 6.5 3.0 5.2 2.0 virginica
## 5 6.2 3.4 5.4 2.3 virginica
## 6 5.9 3.0 5.1 1.8 virginica¡Estupendo! Todo indica que hemos importado correctamente la primera pestaña de nuestra hoja de cálculo, aunque por lo general, es raro encontrarnos con hojas de cálculo con una estructura ordenada como esta y que todo marche tan fácilmente.
Aún tenemos que importar un par de pestañas por importar que ilustrarán lo anterior
Importaremos la segunda pestaña usando la misma función read_excel, asignando un valor al parámetro sheet.
Este parámetro nos permite determinar qué pestaña queremos importar. Podemos indicar la pestaña que deseemos con un número, en nuestro caso con tres pestañas, de la 1 a la 3; o podemos indicar la pestaña que deseamos con su nombre,
Importaremos la segunda pestaña usando su nombre, que conocimos al usar la función excel_sheets, y la asignamos al objeto excel_women.
excel_women <- read_excel("excel_prueba.xlsx", sheet = "women")Realizamos la misma exploración que la primera pestaña.
str(excel_women)## Classes 'tbl_df', 'tbl' and 'data.frame': 14 obs. of 2 variables:
## $ 58 : num 59 60 61 62 63 64 65 66 67 68 ...
## $ 115: num 117 120 123 126 129 132 135 139 142 146 ...summary(excel_women)## 58 115
## Min. :59.00 Min. :117.0
## 1st Qu.:62.25 1st Qu.:126.8
## Median :65.50 Median :137.0
## Mean :65.50 Mean :138.3
## 3rd Qu.:68.75 3rd Qu.:149.0
## Max. :72.00 Max. :164.0head(excel_women)## Source: local data frame [6 x 2]
##
## 58 115
## <dbl> <dbl>
## 1 59 117
## 2 60 120
## 3 61 123
## 4 62 126
## 5 63 129
## 6 64 132tail(excel_women)## Source: local data frame [6 x 2]
##
## 58 115
## <dbl> <dbl>
## 1 67 142
## 2 68 146
## 3 69 150
## 4 70 154
## 5 71 159
## 6 72 164Al parecer, se han importado correctamente los datos, pero con un problema. Los nombres de las columnas no son los correctos.
Esto se debe a que las columnas de esta pestaña no tienen encabezado, entonces al importarla se toman como nombres de columna los valores del primer renglón.
Podemos comprobar esto usando la función names.
names(excel_women)## [1] "58" "115"Existen dos soluciones a este problema.
La primera, si no conocemos los nombres que deben tener las columnas, es usar el parámetro col_names con valor igual a FALSE. De este modo, a cada columna se le asignará como nombre de X0 hasta Xn, donde n es el número de columnas que tengamos.
read_excel("excel_prueba.xlsx", sheet = "women", col_names = FALSE)## Source: local data frame [15 x 2]
##
## X0 X1
## <dbl> <dbl>
## 1 58 115
## 2 59 117
## 3 60 120
## 4 61 123
## 5 62 126
## 6 63 129
## 7 64 132
## 8 65 135
## 9 66 139
## 10 67 142
## 11 68 146
## 12 69 150
## 13 70 154
## 14 71 159
## 15 72 164La segunda, si conocemos los nombres de las columnas, es usar col_names con valor igual a un vector de texto con los nombres de las columnas.
En esta pestaña, la primera columna son estaturas y la segunda son pesos, así que usaremos la segunda solución y asignaremos su resultado al objeto excel_woman.
excel_women <-
read_excel(
"excel_prueba.xlsx",
sheet = "women",
col_names = c("estatura", "peso")
)
excel_women %>% names## [1] "estatura" "peso"Si usamos de nuevo las funciones str, summary, head y tail comprobaremos que los datos se han importado correctamente a R.
Ahora pasemos a la tercera pestaña, más complicada que las dos primeras
Intentemos abrir la tercera pestaña por su nombre y veamos sus primeros diez renglones usando head con el parámetro n igual a 10. Como podrás ver, podemos usar el operador %>% para realizar estas tareas en secuencia, sin necesidad de asignar ningún resultado a un objeto.
read_excel(
"excel_prueba.xlsx",
sheet = "airquality"
) %>%
head(n = 10)## Source: local data frame [10 x 11]
##
## Calidad del aire NA NA NA NA
## <chr> <chr> <chr> <chr> <chr>
## 1 NA NA NA NA NA
## 2 Mediciones de Mayo a Septiembre NA NA NA NA
## 3 NA Ozono Sorlar.R Biento Temp
## 4 1 41 190 7.4 67
## 5 2 36 118 8 72
## 6 3 12 149 12.6 74
## 7 4 18 313 11.5 62
## 8 5 - - 14.3 56
## 9 6 28 - 14.9 66
## 10 7 23 299 8.6 65
## Variables not shown: NA <chr>, NA <chr>, NA <chr>, NA <chr>, NA <chr>, NA
## <chr>.El resultado no luce bien.
Veamos la estructura de lo que estamos importando.
read_excel(
"excel_prueba.xlsx",
sheet = "airquality"
) %>%
str()## Classes 'tbl_df', 'tbl' and 'data.frame': 156 obs. of 11 variables:
## $ Calidad del aire: chr NA "Mediciones de Mayo a Septiembre" NA "1" ...
## $ NA : chr NA NA "Ozono" "41" ...
## $ NA : chr NA NA "Sorlar.R" "190" ...
## $ NA : chr NA NA "Biento" "7.4" ...
## $ NA : chr NA NA "Temp" "67" ...
## $ NA : chr NA NA "Observaciones" NA ...
## $ NA : chr NA NA "Fecha" "42125" ...
## $ NA : chr NA NA "Mes" "5" ...
## $ NA : chr NA NA "Dia" "1" ...
## $ NA : chr NA NA "Año" "2015" ...
## $ NA : chr NA NA "Fechas" NA ...Definitivamente algo anda mal. Si usas las funciones str, summary y tail podrás comprobar esto con mayor certeza.
Si abrimos con Excel u otra aplicación similar la hoja de cálculo con la que estamos trabajando, nos daremos cuenta que la pestaña airquality tiene celdas combinadas, encabezados, espacio en blanco y otros elementos que no es necesario conservar al importar a R.
Esto es algo común al trabajar con las hojas de cálculo, pues todos estos elementos suelen ser usados para mejorar la presentación de la información o para prepararla para imprimirla en papel. Desde luego, esto no representa un conjunto de datos ordenados, así que hay que hacer ajustes.
Usaremos el parámetro skip con un valor igual a 3 para omitir la lectura de los tres primeros renglones, los cuales contienen títulos y subtítulos de la pestaña. De nuevo usaremos head para ver sólo los diez primeros renglones de la pestaña.
read_excel(
"excel_prueba.xlsx",
sheet = "airquality",
skip = 3) %>%
head(10)## Source: local data frame [10 x 11]
##
## NA Ozono Sorlar.R Biento Temp Observaciones Fecha Mes Dia
## <dbl> <chr> <chr> <dbl> <dbl> <dbl> <time> <dbl> <dbl>
## 1 1 41 190 7.4 67 NA 2015-05-01 5 1
## 2 2 36 118 8.0 72 NA 2015-05-02 5 2
## 3 3 12 149 12.6 74 NA 2015-05-03 5 3
## 4 4 18 313 11.5 62 NA 2015-05-04 5 4
## 5 5 - - 14.3 56 NA 2015-05-05 5 5
## 6 6 28 - 14.9 66 NA 2015-05-06 5 6
## 7 7 23 299 8.6 65 NA 2015-05-07 5 7
## 8 8 19 99 13.8 59 NA 2015-05-08 5 8
## 9 9 8 19 20.1 61 NA 2015-05-09 5 9
## 10 10 - 194 8.6 69 NA 2015-05-10 5 10
## Variables not shown: Año <dbl>, Fechas <dbl>.Luce mucho mejor, pero aún tenemos problemas a resolver.
NA para indicar valores perdidos, se ha usado un guion (-). Esta es la razón por la que las columnas donde tenemos números han sido identificadas como si su contenido fuera texto.Por fortuna, todos estos problemas tienen solución.
na con un valor igual a -. De esta manera, indicamos que los datos perdidos se han codificado con un guion.col_types un vector de texto indicando el tipo de datos que contiene cada columna. Podemos elegir entre los valores “numeric”, “text”, “date” o “blank”, usando un valor para cada columna. De este modo, elegiremos “date” para la columna fecha y “numeric” para las demás."blank" a col_types. Esto es lo que haremos con la primera columna y las columnas sin datos.col_names, como lo hicimos en la segunda pestaña. Usaremos NA como nombre de las columnas que omitiremos.Combinamos las soluciones y vemos los primeros diez renglones de la tercera pestaña.
read_excel(
"excel_prueba.xlsx",
sheet = "airquality",
skip = 4,
na = "-",
col_types = c("blank", "blank", "numeric", "numeric", "numeric", "numeric", "blank", "date", "numeric", "numeric", "numeric", "blank"),
col_names = c(NA, NA, "ozono", "rad_solar", "viento", "temp", NA, "fecha", "dia", "año", "mes", NA)
) %>%
head(10)## Source: local data frame [10 x 8]
##
## ozono rad_solar viento temp fecha dia año mes
## <dbl> <dbl> <dbl> <dbl> <time> <dbl> <dbl> <dbl>
## 1 41 190 7.4 67 2015-05-01 5 1 2015
## 2 36 118 8.0 72 2015-05-02 5 2 2015
## 3 12 149 12.6 74 2015-05-03 5 3 2015
## 4 18 313 11.5 62 2015-05-04 5 4 2015
## 5 NA NA 14.3 56 2015-05-05 5 5 2015
## 6 28 NA 14.9 66 2015-05-06 5 6 2015
## 7 23 299 8.6 65 2015-05-07 5 7 2015
## 8 19 99 13.8 59 2015-05-08 5 8 2015
## 9 8 19 20.1 61 2015-05-09 5 9 2015
## 10 NA 194 8.6 69 2015-05-10 5 10 2015¡Excelente! El resultado luce satisfactorio así que lo que asignaremos al objeto excel_airquality y veamos su estructura, resumen y últimas columnas.
excel_airquality <-
read_excel(
"excel_prueba.xlsx",
sheet = "airquality",
skip = 4,
na = "-",
col_types = c("blank", "blank", "numeric", "numeric", "numeric", "numeric", "blank", "date", "numeric", "numeric", "numeric", "blank"),
col_names = c(NA, NA, "ozono", "rad_solar", "viento", "temp", NA, "fecha", "dia", "año", "mes", NA)
)
excel_airquality %>% str()## Classes 'tbl_df', 'tbl' and 'data.frame': 153 obs. of 8 variables:
## $ ozono : num 41 36 12 18 NA 28 23 19 8 NA ...
## $ rad_solar: num 190 118 149 313 NA NA 299 99 19 194 ...
## $ viento : num 7.4 8 12.6 11.5 14.3 14.9 8.6 13.8 20.1 8.6 ...
## $ temp : num 67 72 74 62 56 66 65 59 61 69 ...
## $ fecha : POSIXct, format: "2015-05-01" "2015-05-02" ...
## $ dia : num 5 5 5 5 5 5 5 5 5 5 ...
## $ año : num 1 2 3 4 5 6 7 8 9 10 ...
## $ mes : num 2015 2015 2015 2015 2015 ...excel_airquality %>% summary()## ozono rad_solar viento temp
## Min. : 1.00 Min. : 7.0 Min. : 1.700 Min. :56.00
## 1st Qu.: 18.00 1st Qu.:115.8 1st Qu.: 7.400 1st Qu.:72.00
## Median : 31.50 Median :205.0 Median : 9.700 Median :79.00
## Mean : 42.13 Mean :185.9 Mean : 9.958 Mean :77.88
## 3rd Qu.: 63.25 3rd Qu.:258.8 3rd Qu.:11.500 3rd Qu.:85.00
## Max. :168.00 Max. :334.0 Max. :20.700 Max. :97.00
## NA's :37 NA's :7
## fecha dia año mes
## Min. :2015-05-01 Min. :5.000 Min. : 1.0 Min. :2015
## 1st Qu.:2015-06-08 1st Qu.:6.000 1st Qu.: 8.0 1st Qu.:2015
## Median :2015-07-16 Median :7.000 Median :16.0 Median :2015
## Mean :2015-07-16 Mean :6.993 Mean :15.8 Mean :2015
## 3rd Qu.:2015-08-23 3rd Qu.:8.000 3rd Qu.:23.0 3rd Qu.:2015
## Max. :2015-09-30 Max. :9.000 Max. :31.0 Max. :2015
## excel_airquality %>% tail()## Source: local data frame [6 x 8]
##
## ozono rad_solar viento temp fecha dia año mes
## <dbl> <dbl> <dbl> <dbl> <time> <dbl> <dbl> <dbl>
## 1 14 20 16.6 63 2015-09-25 9 25 2015
## 2 30 193 6.9 70 2015-09-26 9 26 2015
## 3 NA 145 13.2 77 2015-09-27 9 27 2015
## 4 14 191 14.3 75 2015-09-28 9 28 2015
## 5 18 131 8.0 76 2015-09-29 9 29 2015
## 6 20 223 11.5 68 2015-09-30 9 30 2015Confirmamos que hemos importado correctamente la pestaña airquality a R.
En este documento hemos revisamos cómo importar datos contenidos en hojas de cálculo de Excel a R, algunos problemas que se presentan al realizar esta tarea y cómo solucionarlos.
Debido al uso generalizado de Excel y otros programas para trabajar con hojas de cálculo, las dificultades y comportamientos poco usuales en los documentos que intentemos importar a R son innumerables.
Lo revisado en este documento, aunque no abarca todas las posibilidades, proporciona suficientes herramientas para trabajar con este tipo de documentos.
El código de este documento está disponible en github:
Comentarios, correcciones y sugerencias son bienvenidas