Con frecuencia tenemos la necesidad de importar datos a R directamente desde Excel o sacar tablas de datos desde R hacia una o varias hojas de un libro de Excel. En esta sesión revisaremos como hacer este tipo de operaciones con el paquete openxlsx.

Librerías

library(tidyverse)
library(openxlsx)

Consulte la viñeta del paquete openxlsx aquí.


Desde Excel a R

Descargue el archivo biomasaMeses.xlsx y revise su contenido. Observe que el archivo tiene dos hojas llamadas zonaA y zonaB.

Suponga que usted quiere importar a R la hoja 1 (zonaA) del archivo. Para esto usamos el comando read.xlsx.

datA <- read.xlsx("biomasaMeses.xlsx")
str(datA)
'data.frame':   12 obs. of  3 variables:
 $ mes_num: num  1 2 3 4 5 6 7 8 9 10 ...
 $ mes_tex: chr  "Jan" "Feb" "Mar" "Apr" ...
 $ biomasa: num  22.4 23.9 26.5 28.1 28.8 ...


El comando read.xlsx importa por defecto la 1era. hoja y detecta de forma automática el tipo de columnas (números, texto) en el archivo de Excel.

Para importar a R una hoja diferente a la número 1, utilice el argumento sheet para indicar la hoja con un número o con su nombre (entre comillas):

datB <- read.xlsx("biomasaMeses.xlsx", sheet = "zonaB", )
str(datB)
'data.frame':   12 obs. of  3 variables:
 $ mes_num: num  1 2 3 4 5 6 7 8 9 10 ...
 $ mes_tex: chr  "Jan" "Feb" "Mar" "Apr" ...
 $ biomasa: num  31.5 31.3 35 40.7 40.4 ...


Para otros argumentos que le permiten personalizar aún más la importación revisar la ayuda del comando: ?read.xlsx.


Desde R a un libro nuevo de Excel

Para exportar datos desde R a hojas en un nuevo libro de Excel, usamos el comando write.xlsx. Aquí presentamos dos escenarios, exportar un sólo data.frame o varios.

Exportando un solo data.frame

A continuación juntamos las tablas datA y datB en un sólo data.frame y luego exportamos este nuevo data.frame a un nuevo libro de Excel:

# Se juntan datA y datB, pegando una abajo de la otra
dat <- bind_rows(
  mutate(datA, zona = "A"),
  mutate(datB, zona = "B")
)
str(dat)  # se imprime la estructura
'data.frame':   24 obs. of  4 variables:
 $ mes_num: num  1 2 3 4 5 6 7 8 9 10 ...
 $ mes_tex: chr  "Jan" "Feb" "Mar" "Apr" ...
 $ biomasa: num  22.4 23.9 26.5 28.1 28.8 ...
 $ zona   : chr  "A" "A" "A" "A" ...
# Se exporta la nueva tabla a un nuevo libro de excel llamado biomasa2.xlsx
write.xlsx(x = dat, file = "biomasa2.xlsx")

Ahora revise en su directorio de trabajo el nuevo archivo biomasa2.xlsx para verificar el proceso de exportación.


Exportando varios data.frames

Preparemos tres data.frame de ejemplo desde el paquete MASS para exportar a un nuevo libro de Excel, pero en tres hojas diferentes:

library(MASS)   # se activa MASS (no hay que instalar)
# (1) Datos cabbage: experimento con coles ----
data(cabbages)  # se activan los datos
str(cabbages)   # se revisa su estrucutra
'data.frame':   60 obs. of  4 variables:
 $ Cult  : Factor w/ 2 levels "c39","c52": 1 1 1 1 1 1 1 1 1 1 ...
 $ Date  : Factor w/ 3 levels "d16","d20","d21": 1 1 1 1 1 1 1 1 1 1 ...
 $ HeadWt: num  2.5 2.2 3.1 4.3 2.5 4.3 3.8 4.3 1.7 3.1 ...
 $ VitC  : int  51 55 45 42 53 50 50 52 56 49 ...
# (2) Datos genotype: experimento con ratas ----
data(genotype)  # se activan los datos
str(genotype)   # se revisa su estrucutra
'data.frame':   61 obs. of  3 variables:
 $ Litter: Factor w/ 4 levels "A","B","I","J": 1 1 1 1 1 1 1 1 1 1 ...
 $ Mother: Factor w/ 4 levels "A","B","I","J": 1 1 1 1 1 2 2 2 3 3 ...
 $ Wt    : num  61.5 68.2 64 65 59.7 55 42 60.2 52.5 61.8 ...
# (3) Datos bacteria: precensia de H. influenzae luego de trat ----
data(bacteria)  # se activan los datos
str(bacteria)   # se revisa su estrucutra
'data.frame':   220 obs. of  6 variables:
 $ y   : Factor w/ 2 levels "n","y": 2 2 2 2 2 2 1 2 2 2 ...
 $ ap  : Factor w/ 2 levels "a","p": 2 2 2 2 1 1 1 1 1 1 ...
 $ hilo: Factor w/ 2 levels "hi","lo": 1 1 1 1 1 1 1 1 2 2 ...
 $ week: int  0 2 4 11 0 2 6 11 0 2 ...
 $ ID  : Factor w/ 50 levels "X01","X02","X03",..: 1 1 1 1 2 2 2 2 3 3 ...
 $ trt : Factor w/ 3 levels "placebo","drug",..: 1 1 1 1 3 3 3 3 2 2 ...


Ahora usamos el comando write.xlsx para realizar la exportación, la clave es poner todos los data.frame en una lista:

# Se exportan varios data.frame a un nuevo libro de excel,
# cada data.frame en una hoja diferente con un nombre
write.xlsx(
  
  # Use una lista nombrada para reunir los data.frame
  x = list(
    coles      = cabbages,
    genotRatas = genotype,
    influenza  = bacteria
  ),
  
  file = "variosDatos.xlsx"
  
)

Revise en su directorio de trabajo el archivo variosDatos.xlsx para verificar el proceso de exportación. Observe como cada hoja quedó nombrada con las etiquetas usadas en la lista para cada data.frame.


Desde R a un libro de Excel ya existente

Si queremos poner datos de R en un libro de Excel que ya exista, en este caso debemos decidir si poner la tabla en una hoja ya existente en el libro o poner los datos en una hoja nueva.

Para mostrar el procedimiento para ambos escenarios haga una copia del archivo biomasaMeses.xlsx y trabajaremos sobre la copia. El siguiente código realiza la copia:

# Codigo para realizar una copia de un archivo en el directorio
# de trabajo:
file.copy(from = "biomasaMeses.xlsx", 
          to   = "biomasaMeses2.xlsx")

El procedimiento es el siguiente:

  1. Cargue libro a R con el comando loadWorkbook a un objeto de R, digamos wb.

  2. Desde R agregue una hoja nueva con el comando addWorksheet al objeto wb. Verifique las hojas del objeto con el comando sheets. Debería aparecer la nueva hoja adicionada.

  3. Desde R use el comando writeData para agregar el data.frame deseado a una ubicación (celda, columna) en alguna de las hojas del objeto wb.

  4. Salve el objeto wb con los cambios en el mismo archivo de excel del paso 1 (o en uno nuevo) con el comando saveWorkbook.

A continuación mostramos el código R para realizar el procedimiento:

# Se carga un libro de excel existente
wb <- loadWorkbook(file = "biomasaMeses2.xlsx")
class(wb)  # revisemos la clase
[1] "Workbook"
attr(,"package")
[1] "openxlsx"
wb   # se imprime en consola
A Workbook object.
 
Worksheets:
 Sheet 1: "zonaA"
 

 Sheet 2: "zonaB"
 

 
 Worksheet write order: 1, 2
 Active Sheet 1: "zonaA" 
    Position: 1
# Se verifican las hojas del libro
sheets(wb)                
[1] "zonaA" "zonaB"
addWorksheet(wb, "todo")   # se adiciona una nueva hoja llamada "todo"
sheets(wb)                 # se verifican las hojas del libro (de nuevo)
[1] "zonaA" "zonaB" "todo" 
# Se escriben datos a la nueva hoja iniciando en la columna 3 y fila 4
writeData(wb = wb, sheet = "todo", x = dat, startCol = 3, startRow = 4)

# Se exportan el objeto/libro al mismo archivo (se sobreescribe)
saveWorkbook(wb = wb, file = "biomasaMeses2.xlsx", overwrite = T)

Revise en su directorio de trabajo el archivo biomasaMeses2.xlsx para verificar el proceso de exportación. Observe que tenemos una nueva hoja llamada todo con la nueva tabla, además la tabla inicia en la celda C4 (columna 3, fila 4). El coomando writeData permite controlar otros rasgos de formato al momento de poner los datos en la hoja de excel tales como bordes, colores, etc.


Resumen de comandos

Comando Descripción
read.xlsx Importa datos desde una hoja de un libro de Excel
write.xlsx Exporta uno o varios data.frame a un libro de Excel nuevo
loadWorkbook Carga un archivo de Excel a un objeto de clase ‘workbook’
saveWorkbook Salva un objeto de clase ‘workbook’ a un archivo de Excel
addWorksheet Adiciona una nueva hoja a un objeto de clase ‘workbook’
sheets Imprime los nombre de las hojas de un objeto de clase ‘workbook’
writeData Escribe datos de R en un objeto de clase ‘workbook’


Ejercicios

Use el archivo de excel variosDatos.xlsx que creamos atrás con tres hojas para realizar lo siguiente con código R y los comandos del paquete openxlsx:

  1. Cree un objeto de clase ‘workbook’ a partir del archivo variosDatos.xlsx.

  2. Adicione una nueva hoja al objeto y escriba en esta hoja el data.frame iris (no requiere activar los datos o librerías) que viene de ejemplo en R.

  3. Sobreescriba el archivo variosDatos.xlsx desde el objeto ‘workbook’ con la nueva hoja. Verifique que el archivo ahora contenga la nueva hoja.

Código solución 

wb <- loadWorkbook(file = "variosDatos.xlsx")
sheets(wb)
addWorksheet(wb, sheetName = "iris")
writeData(wb, sheet = "iris", x = iris)
saveWorkbook(wb, file = "variosDatos.xlsx", overwrite = T)