如何将 Excel 文件直接读取到 R 中?还是应该先将数据导出到文本或 CSV 文件,然后将该文件导入 R?
让我重申一下@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外部的依赖项。尽管许多人出于其他原因安装了它们。
是的。请参阅 R 维基上的相关页面。 简短的回答:gdata
包中的read.xls
大部分时间都可以工作(尽管您需要在系统上安装Perl - 通常在MacOS和Linux上已经正确,但在Windows上需要额外的步骤,即参见 http://strawberryperl.com/)。R wiki 页面上列出了各种警告和替代方案。
我认为不直接这样做的唯一原因是您可能想检查电子表格以查看它是否有故障(奇怪的标题、多个工作表 [您一次只能阅读一个,尽管您显然可以遍历它们]、包含的绘图等)。 但是对于格式良好的矩形电子表格,其中包含纯数字和字符数据(即,不是逗号格式的数字,日期,带有零除错误的公式,缺失值等)。我通常对这个过程没有问题。
编辑 2015-十月: 正如其他人在这里评论的那样,openxlsx
和readxl
包比xlsx
包快得多,并且实际上设法打开更大的Excel文件(>1500行>120列)。 @MichaelChirico表明,当首选速度并且openxlsx
取代xlsx
包提供的功能时,readxl
更好。如果要在 2015 年查找用于读取、写入和修改 Excel 文件的包,请选择openxlsx
而不是 xlsx
。
2015 年之前:我用过xlsx
包。它改变了我使用 Excel 和 R 的工作流程,不再有烦人的弹出窗口询问我是否确定要以.txt格式保存我的 Excel 工作表。该包还会写入 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)))
R
读取 Excel 文件的不同方法激增以及此处的大量答案,我想我会尝试阐明这里提到的哪些选项表现最好(在一些简单的情况下)。
自从我开始使用R
以来,我自己一直在使用xlsx
,如果没有别的,对于惯性,我最近注意到似乎没有任何关于哪个包效果更好的客观信息。
任何基准测试工作都充满了困难,因为某些软件包肯定会比其他软件包更好地处理某些情况,而其他警告则如瀑布般涌现。
也就是说,我正在使用一个(可重现的)数据集,我认为它采用非常常见的格式(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
,我发现有时会产生内存堵塞。事不宜迟,这是我发现的结果:
1,000 行 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
比readxl
慢 60%。
25,000 行 Excel 文件
由于花费的时间,我只在较大的文件上重复了 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<</p>
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
。它工作得很好(而且很快)。
http://cran.r-project.org/web/packages/openxlsx/index.html
另一种解决方案是 xlsReadWrite
包,它不需要额外的安装,但确实需要您在第一次使用它之前下载额外的 shlib
require(xlsReadWrite)
xls.getshlib()
忘记这一点可能会导致彻底的沮丧。去过那里,所有这些...
旁注:您可能需要考虑转换为基于文本的格式(例如csv)并从那里读入。这有几个原因:
无论您的解决方案是什么(RODBC,gdata,xlsReadWrite),当您的数据被转换时,可能会发生一些奇怪的事情。特别是日期可能相当麻烦。
HFWutils
包有一些工具来处理EXCEL日期(根据Bolker的评论@Ben)。如果您有大型工作表,则读入文本文件比从 Excel 读入更快。
对于.xls和.xlsx文件,可能需要不同的解决方案。例如,xlsReadWrite 包目前不支持 AFAIK .xlsx。
gdata
要求您安装额外的 perl 库以获得.xlsx支持。xlsx
包可以处理同名的扩展。
正如上面在许多其他答案中提到的,有许多好的包可以连接到XLS/X文件并以合理的方式获取数据。 但是,应警告您,在任何情况下都不应使用剪贴板(或.csv)文件从Excel检索数据。 若要查看原因,请在 Excel 中的单元格中输入=1/3
。 现在,将可见的小数点数减少到两个。 然后将数据复制并粘贴到 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)
使用 readxl 包读取 xls 和 xlxs 文件
library("readxl")
my_data <- read_excel("xxxxx.xls")
my_data <- read_excel("xxxxx.xlsx")