R读取并处理较大数据

1. 概述

之前在读取和处理较大的csv数据时，在尝试了一次直接读取处理后发现非常耗时，就改用导入数据库，再通过RMySQL或者RODBC来读取处理数据。今天来比较一下。

加载相关packages

library(data.table)
library(dplyr)
library(sqldf)
library(lubridate)
library(RMySQL)

2. 读取数据

2.1 使用data.table

首先通过data.table的fread()读取数据，fread()比read.csv()的效率高很多，这里不进行详细比较。

time_fread <- system.time(
test <- fread("test.csv")
)
## 数据的大小
paste("数据的大小为：",format(object.size(test),units="auto"))

数据的大小为： 573.1 Mb

2.2 使用RMySQL

导入数据库后效率最高，虽然导入数据库消耗的时间较长，但便于后续统计.

con <- dbConnect(MySQL(),host="localhost",dbname="test_db",user="root",password="root")
# dbListTables(con)
# dbRemoveTable(con,"test")
# 将数据写入数据库
time_mysql_write <- system.time(
  dbWriteTable(con,"test",test)
)

通过R导入数据库的效率，相对比较耗时，建议通过其他方式导入。

kable(rbind(time_mysql_write),row.names = F)

user.self	sys.self	elapsed	user.child	sys.child
124.757	2.822	151.031	0.779	0.053

读取数据库中的数据表

time_mysql_read <- system.time(
  db_test <- dbReadTable(con,"test")
)
time_mysql_query <- system.time(
  db_test <- dbGetQuery(con,"select * from test")
)

2.3 读取数据比较

kable(rbind(time_fread,time_mysql_read))

name	user.self	sys.self	elapsed	user.child	sys.child
time_fread	6.534	0.455	7.265	0	0
time_mysql_read	13.185	1.441	25.888	0	0
time_mysql_query	6.338	1.298	18.143	0	0

很明显fread()的效率最高，所以如果只是读取数据，还是强烈推荐fread()。针对读取数据库表全部数据，dbReadTable()比dbGetQuery()读取数据的效率还差。

3. 处理数据

分别通过data.table、dplyr、sqldf、RMySQL这四种方式来统计相关数据。

3.1 使用data.table

time_DT <- system.time(
test_month1 <- test[,.(value = sum(数据)), by=.(year=year(日期), month = month(日期))]
)

3.2 使用dplyr

time_dplyr <- system.time(
test_month2 <- test %>%
  group_by(year=year(日期),month=month(日期)) %>%
  summarise(value=sum(数据)) %>%
  ungroup()
)

3.3 使用sqldf

## 需要先卸载RMySQL
detach("package:RMySQL", unload=TRUE)
time_sqldf_s <- proc.time()
test$日期 <- as.Date(test[,日期])

test_month3 <- sqldf("select strftime('%Y', 日期 * 3600 * 24, 'unixepoch') as year,
strftime('%m',日期) as month,sum(数据) as test_amount from test group by strftime('%Y', 日期 * 3600 * 24, 'unixepoch'),
strftime('%m',日期)")
time_sqldf <- proc.time()-time_sqldf_s

3.4 使用RMySQL

library(RMySQL)
con <- dbConnect(MySQL(),host="localhost",dbname="test_db",user="root",password="root")
test_month_sql <- "SELECT YEAR(日期) as year, month(日期) as month,
sum(数据) as test_amount FROM test GROUP BY YEAR(日期),month(日期)"
time_mysql <- system.time(
  test_month4 <- dbGetQuery(con, test_month_sql)
)

3.5 处理数据比较

rbind(time_DT,time_dplyr,time_sqldf,time_mysql)

name	user.self	sys.self	elapsed	user.child	sys.child
time_DT	7.846	1.112	9.063	0	0
time_dplyr	8.155	1.182	9.487	0	0
time_sqldf	37.343	2.650	40.868	0	0
time_mysql	0.001	0.000	2.449	0	0

通过数据库来处理数据效率最高，其次为data.table和dplyr，二者不相伯仲，data.table语法更加优雅易读，dplyr语法更加简洁，看自己的喜好啦。而sqldf的效率最差，不推荐。

4. 总结

推荐使用data.table的fread()读取数据，再导入数据库（通过R导入数据库比较耗时，推荐用其他方式导入），再通过加载数据库包通过dbGetQuery()读取数据。dbReadTable()读取全部数据，效率较差，不推荐。