mysql的 l与 r_[LR]读取数据

数据源

文本格式

Excel格式

数据库

剪切板

read.table("clipboard",header=T)

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

read.csv与fread效率比较

n

DT

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

#第一次读取所需时间 11 sec

system.time(DF1

#重复读取所需时间 8 sec

system.time(DF2

stringsAsFactors=FALSE,comment.char="",nrows=n,

colClasses=c("integer","integer","numeric",

"character","numeric","integer")))

#知道字段格式所需时间 6 sec

require(data.table)

system.time(DT

#使用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

df

sapply(df, class)

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

convertToDate(df$Var5)

获取工作表sheet的名称

getSheetNames(xlsxFile)

创建工作簿并保存

wb

# 创建mtcars工作表

addWorksheet(wb, "mtcars")

# 写入数据

writeData(wb, "mtcars", mtcars)

# 保存工作簿

saveWorkbook(wb, "mtcars.xlsx", overwrite = TRUE)

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

在上面的尝试已经发现，xlsx本身就是这个复杂问题的最根本原因。与之相反，R对csv等文本格式支持的很好，而且有fread这个神器，要处理一定量级的数据，还是得把xlsx转化为csv格式。

以此为思路，在参考了两个资料后，我成功改写了一段VBA，可以选中需要的xlsx，然后在其目录下新建csv文件夹，把xlsx批量转化为csv格式

2.3 xlsx包

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

安装最新版本的java。

在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

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

channel

#odbcConnectAccess只能用于32位windows系统，64位的可以尝试odbcConnectAccess2007()

channel

3.1.2 读取数据

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

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

myquery

mydata

#对于日常的统计口径可以讲select、where和group等模块化

myselect

mywhere 30 order by Murder"

mydata1

3.2 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

#获取连接信息，查看database下所有表

summary(con)

dbGetInfo(con)

dbListTables(con)

#断开连接

dbDisconnect(con)

3.2.2 写入数据

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

append=T 在数据库中原表的基础上追加

overwrite=T 覆盖数据库中的原表

testA

testB

#直接写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

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

data

data

data

data

dbClearResult(res)

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

con

#client.flag设置这样支持批量查询

#dbSendQuery(con,'SET NAMES uft-8')

sql

res1

dbFetch(res1, n = -1)

if (dbMoreResults(con)) {

res2

dbFetch(res2, n = -1)

}

dbListResults(con)

dbClearResult(res1)

dbClearResult(res2)

3.2.3 删除表

dbRemoveTable(con,"test")