Чтение файла Excel непосредственно из сценария R

excel r r-faq

180021 просмотра

12 ответа

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

Автор: waanders Источник Размещён: 04.10.2019 12:18

Ответы (12)


40 плюса

Решение

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

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

Автор: Ben Bolker Размещён: 23.05.2011 03:22

48 плюса

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

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

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

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

Автор: JD Long Размещён: 23.05.2011 04:51

44 плюса

И теперь есть 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. Хотя многие люди устанавливают их по другим причинам ,

Автор: Ben Размещён: 17.03.2015 07:54

28 плюса

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

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

Тем не менее, я нахожу read.xlsxфункцию медленной, при открытии больших файлов Excel. 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)))
Автор: Mikko Размещён: 22.01.2013 10:23

22 плюса

Учитывая распространение различных способов чтения файла 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, который я нашел время от времени может создавать засорения памяти. Без лишних слов, вот результаты, которые я нашел:

1000-рядный файл 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".

Автор: MichaelChirico Размещён: 30.07.2015 09:16

19 плюса

Мне повезло с XLConnect: http://cran.r-project.org/web/packages/XLConnect/index.html

Автор: Chase Размещён: 23.05.2011 03:39

12 плюса

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 и могу рекомендовать его.

Автор: Brandon Bertelsen Размещён: 23.05.2011 04:12

7 плюса

Просто openxlsxсегодня попробовал пакет . Это работало очень хорошо (и быстро).

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

Автор: Chris Размещён: 05.02.2015 07:49

5 плюса

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

require(xlsReadWrite)
xls.getshlib()

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

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

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

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

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

Автор: Joris Meys Размещён: 23.05.2011 03:44

4 плюса

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

Автор: russellpierce Размещён: 08.10.2014 08:02

3 плюса

Расширяя ответ, предоставленный @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)))
Автор: JasonAizkalns Размещён: 28.10.2014 01:31

1 плюс

Файл Excel может быть прочитан непосредственно в R следующим образом:

my_data <- read.table(file = "xxxxxx.xls", sep = "\t", header=TRUE)

Чтение файлов xls и xlxs с использованием пакета readxl

library("readxl")
my_data <- read_excel("xxxxx.xls")
my_data <- read_excel("xxxxx.xlsx")
Автор: Ashok Kumar Jayaraman Размещён: 30.08.2018 12:33
Вопросы из категории :
32x32