在R中读取数据

数据源

  • 文本格式
  • Excel格式
  • 数据库
  • 剪切板

1. 文本格式

  • read.table() 即是以数据框的格式在R中读取数据,可以直接读取txt文件。
  • read.csv() 与read.table函数类似,可以直接读取csv文件和txt文件。
  • data.table的fread() 比常规的read.table或者read.csv效率更高。
read.table(tf, header = T, fill = TRUE)
read.csv(tf, header = T, fill = TRUE)
#fread需先加载data.table
library(data.table)
fread(tf, sep="auto", header=T, select=NULL, drop=NULL)

筛选读取的列

data = "A,B,C,D\n1,3,5,7\n2,4,6,8\n"
#筛选读取的列
fread(data, select=c("A","D"))
fread(data, select=c(1,4))
#筛选不读取的列
fread(data, drop=c("B","C"))
fread(data, drop=2:3)

直接读取URL数据

data <- fread("http://www.stats.ox.ac.uk/pub/datasets/csb/ch11b.dat")

read.csv与fread效率比较

n <- 1e6
DT <- data.table( a=sample(1:1000,n,replace=TRUE),b=sample(1:1000,n,replace=TRUE),
                 c=rnorm(n),d=sample(c("foo","bar","baz","qux","quux"),n,replace=TRUE),
                 e=rnorm(n),f=sample(1:1000,n,replace=TRUE) )
DT[2,b:=NA_integer_]
DT[4,c:=NA_real_]
DT[3,d:=NA_character_]
DT[5,d:=""]
DT[2,e:=+Inf]
DT[3,e:=-Inf]
write.table(DT,"test.csv",sep=",",row.names=FALSE,quote=FALSE)
cat("File size (MB):", round(file.info("test.csv")$size/1024^2),"\n")
##File size (MB): 51 
#新建50 MB (1e6 rows x 6 columns)的数据

system.time(DF1 <-read.csv("test.csv",stringsAsFactors=FALSE))
#第一次读取所需时间 11 sec
system.time(DF1 <- read.csv("test.csv",stringsAsFactors=FALSE))
#重复读取所需时间 8 sec
system.time(DF2 <- read.table("test.csv",header=TRUE,sep=",",quote="",
    stringsAsFactors=FALSE,comment.char="",nrows=n,
    colClasses=c("integer","integer","numeric",
                 "character","numeric","integer")))
#知道字段格式所需时间 6 sec
require(data.table)
system.time(DT <- fread("test.csv"))
#使用fread()更友好、更快!

2. Excel格式

用VBA把xlsx批量转化为csv格式,虽然比较方便,但是还有mac的存在,综合推荐使用openxlsx包

2.1 openxlsx包

read.xlsx(xlsxFile, sheet = 1, startRow = 1, colNames = TRUE,
  rowNames = FALSE, detectDates = FALSE, skipEmptyRows = TRUE,
  rows = NULL, cols = NULL, check.names = FALSE, namedRegion = NULL)

读取excel数据

library(openxlsx)
xlsxFile <- system.file("readTest.xlsx", package = "openxlsx")
df <- read.xlsx(xlsxFile = xlsxFile, sheet = 1, skipEmptyRows = FALSE)
sapply(df, class)

将excel中日期数字转换为日期格式

convertToDate(df$Var5)

获取工作表sheet的名称

getSheetNames(xlsxFile)

创建工作簿并保存

wb <- createWorkbook()
# 创建mtcars工作表
addWorksheet(wb, "mtcars")
# 写入数据
writeData(wb, "mtcars", mtcars)
# 保存工作簿
saveWorkbook(wb, "mtcars.xlsx", overwrite = TRUE)

2.2 用VBA把xlsx批量转化为csv格式

主要参考尾巴ARR语言学习笔记之: 论如何正确把EXCEL文件喂给R处理

在上面的尝试已经发现,xlsx本身就是这个复杂问题的最根本原因。与之相反,R对csv等文本格式支持的很好,而且有fread这个神器,要处理一定量级的数据,还是得把xlsx转化为csv格式。 以此为思路,在参考了两个资料后,我成功改写了一段VBA,可以选中需要的xlsx,然后在其目录下新建csv文件夹,把xlsx批量转化为csv格式

2.3 xlsx包

首先配置java环境,加载rJava包,再加载xlsxjars包和xlsx包。

  1. 安装最新版本的java。
  2. 在R中加载环境,即一行代码,路径要依据你的java版本做出更改。
    Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre1.8.0_45\\')

2.4 剪切板

如果excel文件很小、很干净,可临时采用剪切板的方式。 read.table("clipboard",header=T)

3. 数据库

  • ACCESS
  • MYSQL

3.1 ACCESS

3.1.1 连接数据库

odbcConnect(dsn, uid = "", pwd = "", ...)

library(RODBC)
# datasource为已配制好的本地数据源
channel <- odbcConnect("datasource")

也可以先不配置ODBC源,直接用odbcConnectAccess()

channel <- odbcConnectAccess("AccessFile.mdb")
#odbcConnectAccess只能用于32位windows系统,64位的可以尝试odbcConnectAccess2007()
channel <- odbcConnectAccess2007("AccessFile.accdb")

3.1.2 读取数据

向数据库提交查询,并返回结果。

sqlQuery(channel, query, errors = TRUE, ..., rows_at_time)

myquery <- "select * from testtable"
mydata <- sqlQuery(channel, myquery)
#对于日常的统计口径可以讲select、where和group等模块化
myselect <- "select State, Murder from USArrests"
mywhere <- "where Rape > 30 order by Murder"
mydata1 <- sqlQuery(channel, paste(myselect,mywhere))

3.2 MYSQL

参考内容:

RMySQL数据库编程指南 R语言使用RMySQL连接及读写Mysql数据库

RMySql包安装和加载优点问题,试着根据提示简单安装和加载可以使用,后续再查询资料解决。

3.2.1 连接数据库

dbConnect(MySQL(),host="localhost",dbname,user="",password="", ...)

library(RMySql)
#可能是安装RMySQL的问题,导致直接library(RMySql)提示不存在RMySql包,通过下列方式可以加载成功(又挖一个坑...)
library("RMySQL", lib.loc="/Library/Frameworks/R.framework/Versions/3.3/Resources/library")
# Quark
con <- dbConnect(MySQL(),host="localhost",dbname="Quark",user="root",password="root")
#获取连接信息,查看database下所有表
summary(con)  
dbGetInfo(con)  
dbListTables(con)
#断开连接
dbDisconnect(con)

3.2.2 写入数据

dbWriteTable(conn, name, value, row.names=T...)

  • append=T 在数据库中原表的基础上追加
  • overwrite=T 覆盖数据库中的原表
testA <-data.frame(id=1:6,e=c("a","b","c","d","e","f"),c=c("我","的","世","界","变","得"))  
testB <-data.frame(id=7:13,e=c("g","h","i","j","k","l","m"),c=c("奇","妙","跟","难","以","言","喻"))  
#直接写testA写入test表中  
dbWriteTable(con,"test",testA,row.names=T)
dbReadTable(con,"test")
#追加写testB追加在test表后  
dbWriteTable(con,"test",testB,append=T,row.names=F)  
dbReadTable(con,"test")  
#覆盖写testB覆盖test表  
dbWriteTable(con,"test",testB,overwrite=T,row.names=F)  
dbReadTable(con,"test")

fruits <-data.frame(id=1:5,name=c("苹果","香蕉","梨子","玉米","西瓜"),price=c(8.8,4.98,7.8,6,2.1),status=c("无","打折","无","售罄","批发"))  
dbListTables(con)
dbWriteTable(con,"fruits",fruits,overwrite=T,row.names=F)
dbReadTable(con,"fruits")

3.2.3 读取数据

向数据库提交查询,并返回结果。

  • dbReadTable(conn, name, …) 直接获得所读取表的全部数据
  • dbGetQuery(conn, statement, …) 通过sql语句查询数据
#读数据库
#解决中文乱麻问题
#dbSendQuery(con,'SET NAMES uft8')
dbReadTable(con,"test")

#用SQL语句查询dbGetQuery()和dbSendQuery()两种方法  
dbGetQuery(con, "SELECT * FROM test limit 3")  
   
res <- dbSendQuery(con, "SELECT * FROM test")  
data <- dbFetch(res, n=2) #取前2条数据,n=-1时是获取所有数据  
data  
data <- dbFetch(res, n=-1) #取余下所有数据  
data  
dbClearResult(res)

用SQL语句批量查询,client.flag设置这样支持批量查询

con <- dbConnect(MySQL(),host="localhost",dbname="Quark",user="root",password="root",client.flag= CLIENT_MULTI_STATEMENTS) 
#client.flag设置这样支持批量查询
#dbSendQuery(con,'SET NAMES uft-8')
sql <- "SELECT * FROM fruits;SELECT * FROM test"
res1 <- dbSendQuery(con,sql)
dbFetch(res1, n = -1)
if (dbMoreResults(con)) {
  res2 <- dbNextResult(con)
  dbFetch(res2, n = -1)
}
dbListResults(con)
dbClearResult(res1)
dbClearResult(res2)

3.2.3 删除表

dbRemoveTable(con,"test")
Avatar
Jeevan Yue
Data Analyst

Related