SQL(Structured Query Language, 结构化查询语言)是用于访问和处理数据库的标准的计算机语言,也是数据清洗的神器。
日常的数据统计分析工作中,80%的时间是在做数据清洗,只有20%的时间在优化模型、分析统计结果等。数据清洗的工作的重要性不言而喻,今天先简单介绍下数据清洗中最重要的工具--SQL。
1. sqldf包简介
通过sqldf包,可直接在R中的数据框(data.frame)(类似数据库中的表)上进行SQL操作,R中数据清洗常用的dplyr包的许多函数操作也是将相应的命令转化为SQL语句来执行。sqldf包支持SQLite(默认), H2,MySQL及PostgreSQL作为后台来执行SQL语句。SQLite及H2是两个无服务器端,无需配置的轻量级数据库管理系统,在R中安装好并加载sqldf包就可以直接使用SQLite数据库来操作数据,H2同时还需要安装并加载RH2包即可使用(MySQL和PostgreSQL需要繁琐的服务器端客户端的配置)。SQLite灵活轻便,应用非常广泛,集成到了许多IOS及Android的app中。SQLite 没有一个单独的用于存储日期和/或时间的存储类,但 SQLite 能够把日期和时间存储为文本或数值类型(在用SQLite处理日期数据的时候要非常小心,以后再细说)。
2.R中的简单SQL语句
主要介绍使用SQLite为后台,通过sqldf中执行SQL语句
library(sqldf)
df2 <- sqldf('select * from df',drv = 'SQLite')
##这里的drv不写默认就是SQLite。选择df数据库所有变量(* 代表所有变量)
Examples
在R中不使用SQL与使用SQL比较
2.1 head
> df1 <- head(warpbreaks, 5)
> df2 <- sqldf('select * from warpbreaks limit 5;')
> identical(df1, df2)
[1] TRUE
2.2 subset
> data(farms, package = 'MASS')
> df1 <- subset(farms, Manag %in% c('SF', 'BF'))
> df2 <- sqldf("select * from farms where Manag in ('SF', 'BF')")
> row.names(df1) <- NULL
> identical(df1, df2)
[1] TRUE
> df1 <- subset(warpbreaks, breaks >= 20 & breaks <= 30)
> df2 <- sqldf('select * from warpbreaks where breaks between 20 and 30;')
> row.names(df1) <- NULL
> identical(df1, df2)
[1] TRUE
> df1 <- subset(farms, Mois == 'M1')
> df2 <- sqldf('select * from farms where Mois = "M1"', row.names = T)
> identical(df1, df2)
[1] TRUE
2.3 rbind
> df1 <- subset(farms, Mois == 'M1')
> df2 <- subset(farms, Mois == 'M2')
> df3 <- sqldf('select * from farms where Mois = "M1"', row.names = T)
> df4 <- sqldf('select * from farms where Mois = "M2"', row.names = T)
> df12 <- rbind(df1, df2)
> df34 <- sqldf('select * from df3 union all select * from df4', row.names = T)
> identical(df12, df34)
[1] TRUE
2.4 aggregate
> df1 <- aggregate(iris[1:2], iris[5], mean)
> df2 <- sqldf('select Species, avg("Sepal.Length") as "Sepal.Length",
+ avg("Sepal.Width") as "Sepal.Width" from iris group by Species;')
> all.equal(df1, df2)
[1] TRUE
2.5 order
> df1 <- head(warpbreaks[order(warpbreaks$breaks, decreasing = T), ], 3)
> df2 <- sqldf('select * from warpbreaks order by breaks desc limit 3;')
> row.names(df1) <- NULL
> identical(df1, df2)
[1] TRUE
今天先到这里,有空再写~~~