read - Lea un archivo Excel directamente desde un script R

read excel in r (11)

¿Cómo puedo leer un archivo de Excel directamente en R? ¿O debería exportar primero los datos a un archivo de texto o CSV e importar ese archivo a R?

Acabo de probar el paquete openxlsx hoy. Funcionó muy bien (y rápido).

http://cran.r-project.org/web/packages/openxlsx/index.html

Ampliando la respuesta proporcionada por @Mikko puede usar un buen truco para acelerar las cosas sin tener que "conocer" sus clases de columnas con anticipación. Simplemente use read.xlsx para obtener un número limitado de registros para determinar las clases y luego read.xlsx2 seguimiento con read.xlsx2

Ejemplo

# just the first 50 rows should do... df.temp <- read.xlsx("filename.xlsx", 1, startRow=1, endRow=50) df.real <- read.xlsx2("filename.xlsx", 1, colClasses=as.vector(sapply(df.temp, mode)))

Como se señaló anteriormente en muchas de las otras respuestas, hay muchos buenos paquetes que se conectan al archivo XLS / X y obtienen los datos de una manera razonable. Sin embargo, se le debe advertir que bajo ninguna circunstancia debe usar el archivo del portapapeles (o un archivo .csv) para recuperar datos de Excel. Para ver por qué, ingrese =1/3 en una celda en excel. Ahora, reduzca a dos el número de puntos decimales visibles para usted. Luego copie y pegue los datos en R. Ahora guarde el archivo CSV. En ambos casos, notará que Excel solo ha conservado los datos visibles a través de la interfaz y ha perdido toda la precisión en sus datos de origen reales.

Dada la proliferación de diferentes formas de leer un archivo Excel en R y la gran cantidad de respuestas aquí, pensé en intentar arrojar algo de luz sobre cuáles de las opciones mencionadas aquí funcionan mejor (en algunas situaciones simples).

Yo mismo he estado usando xlsx desde que empecé a usar R , por inercia, y descubrí recientemente que no parece haber información objetiva sobre qué paquete funciona mejor.

Cualquier ejercicio de evaluación comparativa está plagado de dificultades ya que algunos paquetes son seguros para manejar ciertas situaciones mejor que otros, y una cascada de otras advertencias.

Dicho esto, estoy usando un conjunto de datos (reproducibles) que creo que está en un formato bastante común (8 campos de cadena, 3 numéricos, 1 entero, 3 fechas):

set.seed(51423) data.frame( str1 = sample(sprintf("%010d", 1:NN)), #ID field 1 str2 = sample(sprintf("%09d", 1:NN)), #ID field 2 #varying length string field--think names/addresses, etc. str3 = replicate(NN, paste0(sample(LETTERS, sample(10:30, 1L), TRUE), collapse = "")), #factor-like string field with 50 "levels" str4 = sprintf("%05d", sample(sample(1e5, 50L), NN, TRUE)), #factor-like string field with 17 levels, varying length str5 = sample(replicate(17L, paste0(sample(LETTERS, sample(15:25, 1L), TRUE), collapse = "")), NN, TRUE), #lognormally distributed numeric num1 = round(exp(rnorm(NN, mean = 6.5, sd = 1.5)), 2L), #3 binary strings str6 = sample(c("Y","N"), NN, TRUE), str7 = sample(c("M","F"), NN, TRUE), str8 = sample(c("B","W"), NN, TRUE), #right-skewed integer int1 = ceiling(rexp(NN)), #dates by month dat1 = sample(seq(from = as.Date("2005-12-31"), to = as.Date("2015-12-31"), by = "month"), NN, TRUE), dat2 = sample(seq(from = as.Date("2005-12-31"), to = as.Date("2015-12-31"), by = "month"), NN, TRUE), num2 = round(exp(rnorm(NN, mean = 6, sd = 1.5)), 2L), #date by day dat3 = sample(seq(from = as.Date("2015-06-01"), to = as.Date("2015-07-15"), by = "day"), NN, TRUE), #lognormal numeric that can be positive or negative num3 = (-1) ^ sample(2, NN, TRUE) * round(exp(rnorm(NN, mean = 6, sd = 1.5)), 2L) )

Luego escribí esto en csv y lo abrí en LibreOffice y lo guardé como un archivo .xlsx, luego comparé 4 de los paquetes mencionados en este hilo: xlsx , openxlsx , readxl y gdata , usando las opciones predeterminadas (también probé una versión de si especifico o no los tipos de columna, pero esto no cambió las clasificaciones).

Estoy excluyendo RODBC porque estoy en Linux; XLConnect porque parece que su objetivo principal no es leer en hojas únicas de Excel, sino importar libros de trabajo completos de Excel, por lo que poner su caballo en carrera solo con sus capacidades de lectura parece injusto; y xlsReadWrite porque ya no es compatible con mi versión de R (parece que se ha eliminado).

Luego ejecuté los puntos de referencia con NN=1000L y NN=25000L (reiniciando la semilla antes de cada declaración del data.frame anterior) para permitir diferencias con respecto al tamaño del archivo de Excel. gc es principalmente para xlsx , que he encontrado a veces puede crear obstrucciones de memoria. Sin más preámbulos, aquí están los resultados que encontré:

Archivo Excel de 1,000 filas

benchmark1k <- microbenchmark(times = 100L, xlsx = {xlsx::read.xlsx2(fl, sheetIndex=1); invisible(gc())}, openxlsx = {openxlsx::read.xlsx(fl); invisible(gc())}, readxl = {readxl::read_excel(fl); invisible(gc())}, gdata = {gdata::read.xls(fl); invisible(gc())}) # Unit: milliseconds # expr min lq mean median uq max neval # xlsx 194.1958 199.2662 214.1512 201.9063 212.7563 354.0327 100 # openxlsx 142.2074 142.9028 151.9127 143.7239 148.0940 255.0124 100 # readxl 122.0238 122.8448 132.4021 123.6964 130.2881 214.5138 100 # gdata 2004.4745 2042.0732 2087.8724 2062.5259 2116.7795 2425.6345 100

Entonces readxl es el ganador, con openxlsx competitivo y gdata un claro perdedor. Tomando cada medida relativa al mínimo de la columna:

# expr min lq mean median uq max # 1 xlsx 1.59 1.62 1.62 1.63 1.63 1.65 # 2 openxlsx 1.17 1.16 1.15 1.16 1.14 1.19 # 3 readxl 1.00 1.00 1.00 1.00 1.00 1.00 # 4 gdata 16.43 16.62 15.77 16.67 16.25 11.31

Vemos mi favorito, xlsx es un 60% más lento que readxl .

Archivo de Excel de 25,000 filas

Debido a la cantidad de tiempo que lleva, solo hice 20 repeticiones en el archivo más grande, de lo contrario los comandos eran idénticos. Aquí están los datos sin procesar:

# Unit: milliseconds # expr min lq mean median uq max neval # xlsx 4451.9553 4539.4599 4738.6366 4762.1768 4941.2331 5091.0057 20 # openxlsx 962.1579 981.0613 988.5006 986.1091 992.6017 1040.4158 20 # readxl 341.0006 344.8904 347.0779 346.4518 348.9273 360.1808 20 # gdata 43860.4013 44375.6340 44848.7797 44991.2208 45251.4441 45652.0826 20

Aquí están los datos relativos:

# expr min lq mean median uq max # 1 xlsx 13.06 13.16 13.65 13.75 14.16 14.13 # 2 openxlsx 2.82 2.84 2.85 2.85 2.84 2.89 # 3 readxl 1.00 1.00 1.00 1.00 1.00 1.00 # 4 gdata 128.62 128.67 129.22 129.86 129.69 126.75

Entonces readxl es el claro ganador cuando se trata de velocidad. gdata necesitan algo más, ya que es extremadamente lento para leer archivos de Excel, y este problema solo se agrava para las tablas más grandes.

Dos sorteos de openxlsx son 1) sus extensos otros métodos ( readxl está diseñado para hacer solo una cosa, que es probablemente parte de por qué es tan rápido), especialmente su función write.xlsx , y 2) (más un inconveniente para readxl ) el argumento col_types en readxl solamente (a partir de este escrito) acepta algunos R : "text" no estándar en lugar de "character" y "date" lugar de "Date" .

He tenido buena suerte con XLConnect : XLConnect

Otra solución es el paquete xlsReadWrite , que no requiere instalaciones adicionales pero sí requiere que descargue el shlib adicional antes de usarlo la primera vez:

require(xlsReadWrite) xls.getshlib()

Olvidar esto puede causar una total frustración. Estado allí y todo eso ...

En una nota al margen: es posible que desee considerar la conversión a un formato basado en texto (por ejemplo, csv) y leer desde allí. Esto por una serie de razones:

cualquiera que sea su solución (RODBC, gdata, xlsReadWrite) pueden ocurrir algunas cosas extrañas cuando se convierten sus datos. Especialmente las fechas pueden ser bastante engorrosas. El paquete HFWutils tiene algunas herramientas para manejar las fechas EXCEL (según el comentario de @Ben Bolker).
si tiene hojas grandes, leer en archivos de texto es más rápido que leer desde EXCEL.
para archivos .xls y .xlsx, pueden ser necesarias diferentes soluciones. Por ejemplo, el paquete xlsReadWrite actualmente no es compatible con .xlsx AFAIK. gdata requiere que instales bibliotecas perl adicionales para el soporte de .xlsx. xlsxpackage paquete xlsxpackage puede manejar extensiones del mismo nombre.

Permítanme reiterar lo que @Chase recomendó: usar XLConnect .

Las razones para usar XLConnect son, en mi opinión:

Plataforma cruzada XLConnect está escrito en Java y, por lo tanto, se ejecutará en Win, Linux, Mac sin cambio de código R (excepto posibles cadenas de ruta)
Nada más que cargar Simplemente instale XLConnect y continúe con la vida.
Usted solo mencionó leer archivos de Excel, pero XLConnect también escribirá archivos de Excel, incluido el cambio de formato de celda. Y lo hará desde Linux o Mac, no solo con Win.

XLConnect es algo nuevo en comparación con otras soluciones, por lo que se menciona con menor frecuencia en publicaciones de blogs y documentos de referencia. Para mí ha sido muy útil.

Sí. Ver la página relevante en la R wiki . Respuesta corta: read.xls del paquete gdata funciona la mayoría de las veces (aunque necesita tener Perl instalado en su sistema, normalmente ya es cierto en MacOS y Linux, pero da un paso adicional en Windows, es decir, consulte http://strawberryperl.com/ ). Hay varias advertencias y alternativas, que figuran en la página R wiki.

La única razón por la que veo que no debo hacer esto directamente es que es posible que desee examinar la hoja de cálculo para ver si tiene fallas (encabezados extraños, hojas de trabajo múltiples [solo puede leer una a la vez, aunque obviamente puede recorrerlas todas) , parcelas incluidas, etc.). Pero para una hoja de cálculo rectangular bien formada con números simples y datos de caracteres (es decir, números no formateados en coma, fechas, fórmulas con errores de división por cero, valores perdidos, etc. etc.) generalmente no tengo ningún problema con este proceso.

Y ahora está readxl :

El paquete readxl facilita la obtención de datos de Excel y de R. Comparado con los paquetes existentes (por ejemplo, gdata, xlsx, xlsReadWrite, etc.) readxl no tiene dependencias externas, por lo que es fácil de instalar y usar en todos los sistemas operativos. Está diseñado para trabajar con datos tabulares almacenados en una sola hoja.
readxl está construido sobre la biblioteca libxls C, que abstrae muchas de las complejidades del formato binario subyacente.
Es compatible con el formato .xls heredado y .xlsx
readxl está disponible desde CRAN, o puede instalarlo desde github con:

# install.packages("devtools") devtools::install_github("hadley/readxl")

Uso

library(readxl) # read_excel reads both xls and xlsx files read_excel("my-old-spreadsheet.xls") read_excel("my-new-spreadsheet.xlsx") # Specify sheet with a number or name read_excel("my-spreadsheet.xls", sheet = "data") read_excel("my-spreadsheet.xls", sheet = 2) # If NAs are represented by something other than blank cells, # set the na argument read_excel("my-spreadsheet.xls", na = "NA")

Tenga en cuenta que si bien la descripción dice "sin dependencias externas", sí requiere el paquete Rcpp , que a su vez requiere Rtools (para Windows) o Xcode (para OSX), que son dependencias externas a R. Aunque mucha gente las tiene instaladas para otras razones.

EDITAR 2015-Octubre: Como otros han comentado aquí, los paquetes de openxlsx y readxl son mucho más rápidos que el paquete xlsx y realmente logran abrir archivos de Excel más grandes (> 1500 filas y> 120 columnas). @MichaelChirico demuestra que readxl es mejor cuando se prefiere la velocidad y openxlsx reemplaza la funcionalidad proporcionada por el paquete xlsx . Si está buscando un paquete para leer, escribir y modificar archivos de Excel en 2015, elija openxlsx lugar de xlsx .

Pre-2015: He usado el xlsxpackage . Cambió mi flujo de trabajo con Excel y R. No más pop-ups molestos preguntando, si estoy seguro de que quiero guardar mi hoja de Excel en formato .txt. El paquete también escribe archivos de Excel.

Sin embargo, me parece read.xlsx función read.xlsx al abrir archivos de Excel grandes. read.xlsx2 función read.xlsx2 es considerablemente más rápida, pero no cuestiona la clase vectorial de columnas data.frame. colClasses usar el comando colClasses para especificar las clases de columna deseadas, si usa la función read.xlsx2 . Aquí hay un ejemplo práctico:

read.xlsx("filename.xlsx", 1) lee su archivo y hace que las clases de columnas data.frame sean casi útiles, pero es muy lento para grandes conjuntos de datos. Funciona también para archivos .xls .

read.xlsx2("filename.xlsx", 1) es más rápido, pero deberá definir clases de columna manualmente. Un atajo es ejecutar el comando dos veces (ver el ejemplo a continuación). character especificación del character convierte tus columnas en factores. Use las opciones Date y POSIXct por tiempo.

coln <- function(x){y <- rbind(seq(1,ncol(x))); colnames(y) <- colnames(x) rownames(y) <- "col.number"; return(y)} # A function to see column numbers data <- read.xlsx2("filename.xlsx", 1) # Open the file coln(data) # Check the column numbers you want to have as factors x <- 3 # Say you want columns 1-3 as factors, the rest numeric data <- read.xlsx2("filename.xlsx", 1, colClasses= c(rep("character", x), rep("numeric", ncol(data)-x+1)))

library(RODBC) file.name <- "file.xls" sheet.name <- "Sheet Name" ## Connect to Excel File Pull and Format Data excel.connect <- odbcConnectExcel(file.name) dat <- sqlFetch(excel.connect, sheet.name, na.strings=c("","-")) odbcClose(excel.connect)

Personalmente, me gusta RODBC y puedo recomendarlo.