sqldf程序包是R语言中实用的数据管理辅助工具，但最新版本的包在处理中文时出现乱码，待解决
Usage: sqldf(x, stringsAsFactors = FALSE, row.names = FALSE...)
- row.names: 为True时，行名重命名不重新命名
- 需安装sqldf 包： install.packages("sqldf")
- 加载如下包：
library(gsubfn) library(proto) library(RSQLite) library(DBI) library(tcltk) library(sqldf) - 取前几行数据示例
> x <-head(iris,2) > y <- sqldf("select * from iris limit 2") > #比较两个数据框是否相同 > identical(x,y) [1] TRUE > y Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3.0 1.4 0.2 setosa - like提取数据子集
> #取出物种列中以se开头的数据子集 > a2r <- subset(iris, grepl("^se", Species)) > a2s <- sqldf("select * from iris where Species like 'se%'") > all.equal(as.data.frame(a2r), a2s) [1] TRUE -
in 提取子集
> #在iris数据集中，选出量物种是setosa和virginica的行 > a3r <- subset(iris, Species %in% c("setosa", "virginica")) > a3s <- sqldf("select * from iris where Species in ('setosa', 'virginica')")#注意单引号和双引号 > > #a3r选的是子集，因而行名还是与原数据集相同 > row.names(a3r) <- NULL > identical(a3r, a3s) [1] TRUE -
数据合计
> head(iris,2) Sepal.Length Sepal.Width Petal.Length Petal.Width Species 1 5.1 3.5 1.4 0.2 setosa 2 4.9 3.0 1.4 0.2 setosa > aggregate(iris[1:2], iris[5], mean) #计算物种前两个变量的平均值 Species Sepal.Length Sepal.Width 1 setosa 5.006 3.428 2 versicolor 5.936 2.770 3 virginica 6.588 2.974 > sqldf('select Species, avg("Sepal.Length") , avg("Sepal.Width") from iris group by Species') Species avg("Sepal.Length") avg("Sepal.Width") 1 setosa 5.006 3.428 2 versicolor 5.936 2.770 3 virginica 6.588 2.974
-
order by
> head(warpbreaks,2) breaks wool tension 1 26 A L 2 30 A L > head(warpbreaks[order(warpbreaks$breaks, decreasing = TRUE), ], 2) breaks wool tension 5 70 A L 9 67 A L > sqldf("select * from warpbreaks order by breaks desc limit 2") breaks wool tension 1 70 A L 2 67 A L
-