《机器学习与数据科学（基于R的统计学习方法）》——2.11　R中的SQL等价表述

本节书摘来异步社区《机器学习与数据科学（基于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