Подтвердить что ты не робот

Прочитайте файл Excel непосредственно из R script

Как я могу прочитать файл Excel непосредственно в R? Или я должен сначала экспортировать данные в текстовый или CSV файл и импортировать этот файл в R?

4b9b3361

ОТВЕТЫ

Ответ 1

Да. См. соответствующую страницу в вики wiki. Короткий ответ: read.xls из пакета gdata работает большую часть времени (хотя вам необходимо установить Perl в вашей системе, как правило, уже в MacOS и Linux, но делает дополнительный шаг в Windows, то есть см. http://strawberryperl.com/). Существуют различные оговорки и альтернативы, перечисленные на странице вики R.

Единственная причина, по которой я не вижу этого, заключается в том, что вы можете проверить электронную таблицу, чтобы увидеть, есть ли у нее глюки (странные заголовки, несколько листов (вы можете читать только по одному, хотя вы, очевидно, можете все они], включали графики и т.д.). Но для хорошо сформированной прямоугольной электронной таблицы с простыми числами и символьными данными (т.е. Не с запятыми форматированными числами, датами, формулами с ошибками по-нулю, отсутствующими значениями и т.д. И т.д.). У меня вообще нет проблем с этим процессом.

Ответ 2

Позвольте мне повторить, что @Chase рекомендуется: Используйте XLConnect.

Причины использования XLConnect, на мой взгляд,

  • Кросс-платформа. XLConnect написан на Java и, таким образом, будет работать на Win, Linux, Mac без изменения вашего R-кода (кроме возможных строк пути).
  • Больше нечего загружать. Просто установите XLConnect и продолжайте жизнь.
  • Вы упомянули только о файлах Excel, но XLConnect также будет писать файлы Excel, включая изменение форматирования ячеек. И он будет делать это от Linux или Mac, а не только от Win.

XLConnect несколько отличается от других решений, поэтому его реже упоминают в блогах и справочных документах. Для меня это было очень полезно.

Ответ 3

РЕДАКТИРОВАТЬ 2015-октябрь:. Другие комментируют здесь openxlsx и readxl намного быстрее, чем пакет xlsx, и фактически удается открыть большие файлы Excel ( > 1500 строк и > 120 столбцов). @MichaelChirico демонстрирует, что readxl лучше, когда скорость предпочтительнее, а openxlsx заменяет функциональность, предоставляемую пакетом xlsx. Если вы ищете пакет для чтения, записи и изменения файлов Excel в 2015 году, выберите openxlsx вместо xlsx.

До 2015 года: Я использовал xlsx package. Он изменил мой рабочий процесс на Excel и R. Нет более раздражающих всплывающих окон, если я уверен, что хочу сохранить листок Excel в формате .txt. Пакет также записывает файлы Excel.

Однако при открытии больших файлов Excel я обнаруживаю, что функция read.xlsx работает медленно. Функция read.xlsx2 значительно быстрее, но не требует векторного класса столбцов data.frame. Вы должны использовать команду colClasses для указания нужных классов столбцов, если вы используете функцию read.xlsx2. Вот пример:

read.xlsx("filename.xlsx", 1) читает ваш файл и делает классы столбца data.frame почти полезными, но для больших наборов данных он очень медленный. Работает также для файлов .xls.

read.xlsx2("filename.xlsx", 1) работает быстрее, но вам придется определять классы столбцов вручную. Ярлык состоит в том, чтобы дважды запустить команду (см. Пример ниже). character спецификация преобразует ваши столбцы в факторы. Используйте опции Date и POSIXct для времени.

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)))

Ответ 4

И теперь есть readxl:

Пакет readxl позволяет легко извлекать данные из Excel и в R. По сравнению с существующими пакетами (например, gdata, xlsx, xlsReadWrite и т.д.), readxl не имеет внешних зависимостей, поэтому его легко установить и использовать все операционные системы. Он предназначен для работы с табличными данными, хранящимися в одном листе.

readxl построен поверх библиотеки libxls C, которая абстрагируется многие из сложностей базового двоичного формата.

Он поддерживает как устаревший формат .xls, так и .xlsx

readxl доступен из CRAN, или вы можете установить его из github с помощью:

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

Использование

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")

Обратите внимание, что хотя в описании указано "нет внешних зависимостей", для этого требуется пакет Rcpp, который, в свою очередь, требует Rtools (для Windows) или Xcode (для OSX), которые являются зависимыми от R., хотя многие люди устанавливают их по другим причинам.

Ответ 6

Учитывая распространение различных способов чтения файла Excel в R и множество ответов здесь, я думал, что попытаюсь пролить свет на то, какие из упомянутых здесь опций выполняют лучшие (в нескольких простых ситуации).

Я сам использовал xlsx, так как начал использовать R, для инерции, если ничего другого, и я недавно заметил, что, похоже, нет объективной информации о том, какой пакет работает лучше.

Любое упражнение по бенчмаркингу чревато трудностями, поскольку некоторые пакеты, несомненно, справляются с определенными ситуациями лучше других, и водопад других оговорок.

Тем не менее, я использую (воспроизводимый) набор данных, который, по моему мнению, находится в довольно распространенном формате (8 строковых полей, 3 числовых, 1 целое число, 3 даты):

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)
)

Затем я написал это в csv и открыл его в LibreOffice и сохранил его как файл .xlsx, а затем сравнил 4 пакета, упомянутые в этом потоке: xlsx, openxlsx, readxl и gdata, используя параметры по умолчанию (я также попробовал версию того, задаю ли я типы столбцов, но это не меняло ранжировки).

Я исключаю RODBC, потому что я нахожусь в Linux; XLConnect, потому что, по-видимому, его основная цель заключается не в том, чтобы читать отдельные листы Excel, а импортировать целые книги Excel, поэтому поставить свою лошадь в гонку только на то, что ее возможности чтения кажутся несправедливыми; и xlsReadWrite, потому что он больше не совместим с моей версией R (похоже, был отключен).

Затем я выполнил тесты с NN=1000L и NN=25000L (сбросив семя перед каждым объявлением data.frame выше), чтобы разрешить различия в отношении размера файла Excel. gc в основном для xlsx, который я нашел иногда, может создавать блокировки памяти. Без дальнейших церемоний, вот результаты, которые я нашел:

Файл Excel с тысячами строк

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

Итак, readxl - победитель, с openxlsx конкурентом и gdata явным неудачником. Принимая каждую меру относительно минимума столбца:

#       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

Мы видим свою любимую, xlsx на 60% медленнее, чем readxl.

Файл Excel в 25 000 строк

Из-за количества времени, которое требуется, я сделал только 20 повторений в большем файле, иначе команды были идентичны. Здесь необработанные данные:

# 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

Здесь относительные данные:

#       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

Итак, readxl - явный победитель, когда дело доходит до скорости. gdata лучше иметь что-то еще для этого, так как он очень медленно читает файлы Excel, и эта проблема только усугубляется для больших таблиц.

Две ничьи openxlsx: 1) его обширные другие методы (readxl предназначены для выполнения только одной вещи, которая, вероятно, является частью ее так быстро), особенно ее функция write.xlsx и 2) ( больше недостатка для readxl) аргумент col_types только в readxl (на момент написания) принимает несколько нестандартных R: "text" вместо "character" и "date" вместо "date".

Ответ 7

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)

Лично мне нравится RODBC и могу порекомендовать его.

Ответ 9

Другим решением является пакет xlsReadWrite, который не требует дополнительных установок, но требует загрузки дополнительного шлиба перед его использованием в первый раз:

require(xlsReadWrite)
xls.getshlib()

Забыть это может вызвать полное разочарование. Был там и все такое...

На стороне: вы можете подумать о преобразовании в текстовый формат (например, csv) и прочитать оттуда. Это по ряду причин:

  • независимо от вашего решения (RODBC, gdata, xlsReadWrite) могут произойти некоторые странные вещи, когда ваши данные преобразуются. Особенно даты могут быть довольно громоздкими. Пакет HFWutils имеет некоторые инструменты для работы с датами EXCEL (за комментарий @Ben Bolker).

  • если у вас большие листы, чтение в текстовых файлах происходит быстрее, чем чтение из EXCEL.

  • для файлов .xls и .xlsx могут потребоваться различные решения. EG пакет xlsReadWrite в настоящее время не поддерживает .xlsx AFAIK. gdata требует установки дополнительных библиотек perl для поддержки .xlsx. xlsx пакет может обрабатывать расширения с тем же именем.

Ответ 10

Как отмечалось выше во многих других ответах, существует много хороших пакетов, которые подключаются к файлу XLS/X и получают данные разумным образом. Однако вам следует предупредить, что ни при каких обстоятельствах вы не должны использовать файл буфера обмена (или CSV) для извлечения данных из Excel. Чтобы узнать, почему, введите =1/3 в ячейку в excel. Теперь уменьшите количество десятичных точек, видимых вам до двух. Затем скопируйте и вставьте данные в R. Теперь сохраните CSV. Вы заметите, что в обоих случаях Excel помогает только сохранить данные, которые были видны вам через интерфейс, и вы потеряли всю точность в ваших фактических исходных данных.

Ответ 11

Развернувшись на ответе, предоставленном @Mikko, вы можете использовать опрятный трюк, чтобы ускорить работу, не предварительно "узнав" ваши классы столбцов раньше времени. Просто используйте read.xlsx, чтобы захватить ограниченное количество записей, чтобы определить классы, а затем выполнили его с помощью read.xlsx2

Пример

# 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)))