本节书摘来异步社区《机器学习与数据科学(基于R的统计学习方法)》一书中的第2章,第2.11节,作者:【美】Daniel D. Gutierrez(古铁雷斯),更多章节内容可以访问云栖社区“异步社区”公众号查看。
2.11 R中的SQL等价表述
使用类似于SQL的功能,R有很多种方法能够连接保存在数据框中的数据集。如果你已经了解SQL,那么理解和SQL命令具有相同功能的、R用来操作数据的工具将会是很有用的。在本节中,我们将看到一些在连接数据时早晚会派上用场的工具。为了对这些工具进行举例说明,我们将使用在基础R系统里即可获得的CO2数据集。让我们看看这个数据集的结构和内容:
> data(CO2)
> head(CO2)
Plant Type Treatment conc uptake
1 Qn1 Quebec nonchilled 95 16.0
2 Qn1 Quebec nonchilled 175 30.4
3 Qn1 Quebec nonchilled 250 34.8
4 Qn1 Quebec nonchilled 350 37.2
5 Qn1 Quebec nonchilled 500 35.3
6 Qn1 Quebec nonchilled 675 39.2```
这个CO2数据集包含了84条观测值和5个变量:Plant、Type、Treatment、conc和uptake。通常,你要对数据集做的第一件事是基于一个过滤器筛选数据行。
SELECT * FROM CO2 WHERE conc>400 AND uptake>40`
R中的等价表述使用了下面这种简单的语法:
CO2_subset <- CO2[CO2$conc>400 & CO2$uptake>40,]
head(CO2_subset)
Plant Type Treatment conc uptake
12 Qn2 Quebec nonchilled 500 40.6
13 Qn2 Quebec nonchilled 675 41.4
14 Qn2 Quebec nonchilled 1000 44.3
19 Qn3 Quebec nonchilled 500 42.9
20 Qn3 Quebec nonchilled 675 43.9
21 Qn3 Quebec nonchilled 1000 45.5
> dim(CO2_subset) # 8 observations selected
[1] 8 5```
然后,我们将看一条SQL SELECT语句的ORDER BY子句。在这里,我们希望将结果集根据变量conc进行排列(升序序列),然后根据变量uptake进行排列(降序序列)。下面是SQL的表述:
SELECT * FROM CO2 ORDER BY conc, uptake DESC`
R中的等价表述使用了下面这种简单的语法。我已经加了一些附加的R语句将结果限制在前20行,但是这只是为了方便起见,因为我们不想在这里展示CO2数据集全部的84行观测值。
> CO2[order(CO2$conc, -CO2$uptake),][1:20,]
Plant Type Treatment conc uptake
15 Qn3 Quebec nonchilled 95 16.2
1 Qn1 Quebec nonchilled 95 16.0
36 Qc3 Quebec chilled 95 15.1
22 Qc1 Quebec chilled 95 14.2
8 Qn2 Quebec nonchilled 95 13.6
50 Mn2 Mississippi nonchilled 95 12.0
57 Mn3 Mississippi nonchilled 95 11.3
43 Mn1 Mississippi nonchilled 95 10.6
78 Mc3 Mississippi chilled 95 10.6
64 Mc1 Mississippi chilled 95 10.5
29 Qc2 Quebec chilled 95 9.3
71 Mc2 Mississippi chilled 95 7.7
16 Qn3 Quebec nonchilled 175 32.4
2 Qn1 Quebec nonchilled 175 30.4
9 Qn2 Quebec nonchilled 175 27.3
30 Qc2 Quebec chilled 175 27.3
23 Qc1 Quebec chilled 175 24.1
51 Mn2 Mississippi nonchilled 175 22.0
37 Qc3 Quebec chilled 175 21.0
58 Mn3 Mississippi nonchilled 175 19.4```
另一个强大的SQL结构是GROUP BY子句,用来计算聚合值,例如平均值。在这种情况下,我们想要计算每个不同的Plant值对应的uptake的平均值。下面是完成这个是SQL表述:
SELECT Plant, AVG(uptake) FROM CO2 GROUP BY Plant`
R中的等价表述基于aggregate函数,使用了下面的语法来主导处理过程。第一个自变量x,对于aggregate()来说是CO2[,c(“uptake”)],将uptake这一列从CO2的数据框中分离出来。第二个变量by,是data.frame(CO2$Plant),是一个组变量。最后,R函数中的FUN变量用于计算摘要统计量,在这个例子中,代表的是mean。
> aggregate(x=CO2[,c("uptake")], by=data.frame(CO2$Plant), FUN="mean")
CO2.Plant x
1 Qn1 33.22857
2 Qn2 35.15714
3 Qn3 37.61429
4 Qc1 29.97143
5 Qc3 32.58571
6 Qc2 32.70000
7 Mn3 24.11429
8 Mn2 27.34286
9 Mn1 26.40000
10 Mc2 12.14286
11 Mc3 17.30000
12 Mc1 18.00000```
我们将举一个如何用R做SQL多表查询的例子来结束讨论。考虑这样一种情况:你想从一张标注了州和省的次表中查找国家。下面是完成这个任务的SQL语句:
SELECT c.Type,
c.Plant,
c.Treatment,
c.conc,
c.uptake,
g.country
FROM geo_map g
LEFT JOIN CO2 c ON(c.Type = g.Type)`
R中的等价表述使用了下面这种基于merge()函数的语法。看看CO2数据集中的前几行观测数据,我们了解到变量Type中包含了植物最初生活的州或省。我们使用这个变量作为通用的键值。下一步,我们将创建一个新的数据框geo_map,它会扮演将国家和州/省配对的查找表的角色。然后,在为geo_map设定了合适的列名之后,使用merge()来生成和SQL多表查询得到的相同的结果集joinCO2。注意新的数据框含有一个变量country,它是基于Type的查找值。这和SQL多表查询有相同的效果。
> head(CO2)
Plant Type Treatment conc uptake
1 Qn1 Quebec nonchilled 95 16.0
2 Qn1 Quebec nonchilled 175 30.4
3 Qn1 Quebec nonchilled 250 34.8
4 Qn1 Quebec nonchilled 350 37.2
5 Qn1 Quebec nonchilled 500 35.3
6 Qn1 Quebec nonchilled 675 39.2
> stateprov <- c("Mississippi", "California", "Victoria",
"New South Wales", "Quebec", "Ontario")
> country <- c("United States", "United States", "Australia", "Australia", "Canada", "Canada")
> geo_map <- data.frame(country=country, stateprov=stateprov)
> geo_map
country Type
1 United States Mississippi
2 United States California
3 Australia Victoria
4 Australia New South Wales
5 Canada Quebec
6 Canada Ontario
> colnames(geo_map) <- c("country", "Type")
> joinCO2 <- merge(CO2, geo_map, by=c("Type"))
> head(joinCO2)
Type Plant Treatment conc uptake country
1 Mississippi Mn1 nonchilled 95 10.6 United States
2 Mississippi Mn1 nonchilled 175 19.2 United States
3 Mississippi Mn1 nonchilled 250 26.2 United States
4 Mississippi Mn1 nonchilled 350 30.0 United States
5 Mississippi Mn1 nonchilled 500 30.9 United States