Чтение файла Excel непосредственно из сценария R
Как я могу прочитать файл Excel прямо в R? Или я должен сначала экспортировать данные в текстовый или CSV-файл и импортировать этот файл в R?
12 ответов
Да. Смотрите соответствующую страницу в R wiki. Короткий ответ: read.xls от gdata Пакет работает большую часть времени (хотя вам нужно установить Perl в вашей системе - обычно это уже верно для MacOS и Linux, но для Windows требуется дополнительный шаг, т. е. см. http://strawberryperl.com/). Существуют различные предостережения и альтернативы, перечисленные на странице R wiki.
Единственная причина, по которой я не делаю этого напрямую, заключается в том, что вы, возможно, захотите изучить электронную таблицу, чтобы увидеть, есть ли у нее глюки (странные заголовки, несколько рабочих листов [вы можете читать только по одной за раз, хотя вы, очевидно, можете зацикливать их все), включены участки и т. д.). Но для правильно сформированной, прямоугольной электронной таблицы с простыми числами и символьными данными (т. Е. Не запятыми числами, датами, формулами с ошибками деления на ноль, пропущенными значениями и т. Д. И т. Д.) У меня обычно нет проблем с этим процессом.
Позвольте мне повторить то, что рекомендовал @Chase: используйте XLConnect.
Причины использования XLConnect, на мой взгляд:
- Кроссплатформенный. XLConnect написан на Java и, таким образом, будет работать на Win, Linux, Mac без изменений вашего кода R (за исключением, возможно, строк пути)
- Больше нечего загружать. Просто установите XLConnect и продолжайте жить.
- Вы упомянули только чтение файлов Excel, но XLConnect также будет записывать файлы Excel, включая изменение форматирования ячеек. И он будет делать это из Linux или Mac, а не только из Win.
XLConnect является несколько новым по сравнению с другими решениями, поэтому он реже упоминается в блогах и справочных документах. Для меня это было очень полезно.
И теперь есть 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. Хотя у многих людей они установлены по другим причинам.
РЕДАКТИРОВАТЬ 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)))
Учитывая распространение различных способов чтения файла 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",
Мне повезло с XLConnect: http://cran.r-project.org/web/packages/XLConnect/index.html
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 и могу рекомендовать его.
Просто дал посылку openxlsx попробуй сегодня. Это работало очень хорошо (и быстро).
Другим решением является 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Пакет может обрабатывать расширения с тем же именем.
Как отмечалось выше во многих других ответах, есть много хороших пакетов, которые подключаются к файлу XLS/X и получают данные разумным способом. Однако следует предупредить, что ни при каких обстоятельствах не следует использовать файл буфера обмена (или CSV-файл) для извлечения данных из Excel. Чтобы понять почему, введите =1/3 в клетку в Excel. Теперь уменьшите количество видимых вам десятичных знаков до двух. Затем скопируйте и вставьте данные в R. Теперь сохраните CSV. Вы заметите, что в обоих случаях Excel хранит только те данные, которые были видны вам через интерфейс, и вы потеряли всю точность ваших фактических исходных данных.
Расширяя ответ, предоставленный @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)))
Файл 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")