R与SQL....._鲟曦研习社

介绍

作为一名数据科学家，你可能已经听说过SQL和R。

SQL非常适合与关系数据库交互。另一方面，R是执行高级统计分析的绝佳工具。

然而，有些任务在SQL中比在R中更简单，反之亦然。如果我们可以有一个工具，可以将每种工具的美结合在一起呢？

这就是sqldfc的用武之地。本文旨在强调sqldf的一些特性，类似于SQL中的特性。

什么是SQLDf

sqldf是一个开源库，用于在R数据帧上运行SQL语句。它适用于多个数据库，如SQLite、H2、PostgreSQL和MySQL数据库。

入门

安装软件包

是时候开始动手了！但是，我们首先需要使用install.packages函数安装sqldf库。

# Install the library 
install.packages("sqldf") 

# Load the library 
library("sqldf")

数据和预处理

在本文中，我们将使用UCI机器学习许可证下免费提供的标准机器学习数据集之一，称为“adult-all”。

从直接从Github读取数据集开始，或者使用read.csv函数下载并保存在当前工作目录中。

data_url = "https://raw.githubusercontent.com/keitazoumana/Medium-Articles-Notebooks/main/data/adult-all.csv"

# Read the data
income_data <- read.csv(data_url)

# Check the first 5 rows of the data
head(income_data, 5)

数据的列（V1，V1，…，V15）不可理解，我们可以使用以下语法重命名它们。这些名字来自UCI机器学习网站。

new_columns = c("Age", "Workclass", "fnlwgt", "Education", "EducationNum", "MartialStatus", "Occupation", 
           "Relationship", "Race", "Sex", "CapitalGain", 
           "CapitalLoss", "HoursPerWeek", "Country", "Income")

# Change column names
colnames(income_data) <- new_columns

# Check the first 5 rows of the data again
head(income_data, 5)

更改已成功执行，如你在上一个截图中看到的。

最后，让我们使用tide向数据集添加一个ID列，这将是标识符。你将在本文后面找到本列的好处。

# Add the ID column to the dataset
income_data$ID <- 1:nrow(income_data)

# Show the first 5 rows

使用sqldf的SQL查询

为了能够执行任何SQL查询，你需要使用sqldf函数，该函数将字符串格式的查询作为参数，如下所示。

sqldf("YOUR_SQL_QUERY")

在本节中，我们将从列选择开始，介绍从简单到高级的不同查询。

列选择

我们可以检索满足一个或多个条件的数据列。例如，我们可以提取古巴成年人的年龄、种族、性别、每周小时数和收入。

注意：在语法中，确保不要忘记Cuba周围的''符号以使其正常工作。

cuba_query = "SELECT Age, Race, Sex, HoursPerWeek, Income \
              FROM income_data \ 
              WHERE Country = 'Cuba'"
cuba_data = sqldf(cuba_query)
head(cuba_data, 5)

我们可能想增加一个额外的限制，以便只允许每周工作超过40小时且年龄不足40岁的古巴成年人。

cuba_query_2 = "SELECT Age, Race, Sex, HoursPerWeek, Income \
              FROM income_data \ 
              WHERE Country = 'Cuba'\ 
              AND HoursPerWeek > 40 \ 
              AND Age > 40"

cuba_data_2 = sqldf(cuba_query_2)
head(cuba_data_2, 5)

GROUP BY语句

除了选择列之外，我们还可能希望将数据划分为不同的组，以便在AVG、COUNT、MAX、MIN和SUM等函数的帮助下获得更全面的概述。使用GROUPBY，不同行中具有相同值的特定列将被分组在一起。

例如，让我们考虑每个国家每周平均工作时间，然后按平均工作时间的递增顺序排序。

# Prepare the query
wwh_per_country_query = "SELECT Country, AVG(HoursPerWeek) 
                         AS AvgWorkHours \
                         FROM income_data 
                         GROUP BY Country 
                         ORDER BY AvgWorkHours ASC"

# Run the query
wwh_per_country_data = sqldf(wwh_per_country_query) 

# Get the first 5 observations 
head(wwh_per_country_data, 5)

# Get the last 5 observations
tail(wwh_per_country_data, 5)

让我们把问题分解，以便更好地澄清。

SELECT Country, AVG(HoursPerWeek) AS AvgWorkHours：我们选择所有国家及其各自的每周小时数。然后使用AVG函数计算平均工时的结果，并将其存储在名为AvgWorkHours的新列中。
GROUP BY Country（按国家分组）：在上一个语句的末尾，所有同名国家的平均工时结果相同。然后使用GROUP BY创建每个国家的唯一实例及其相应的平均工时。
ORDER BY AvgWorkHours ASC：此最终语句用于使用ASC（升序）函数按升序对AvgWorkHours进行排序。

如果你是一个更图形化的人，你可以使用纯R脚本显示之前的结果。从R切换到SQL！这不是很神奇吗？）

# Create a plot

# Create room for the plot
graphics.off()

# Sets or adjusts plotting parameters
par("mar")
par(mar = c(12, 4, 2, 2) + 0.2)

# Show the final plot
barplot(height = wwh_per_country_data$AvgWorkHours, 
        names.arg = wwh_per_country_data$Country,
        main ="Average Working Hours Per Country",
        ylab = "Average Weekly Hours",
        las = 2)

par函数用于调整绘图参数，而mar是长度为4的向量，并分别设置底部、左侧、顶部和右侧的边距大小。
las=2用于以垂直方式显示国家名称，以便更好地可视化。值1将水平显示它们。

这只是sqldf所能做的？仅列选择和分组依据？

当然不是！可以执行的SQL查询多得多。让我们用JOINS来完成这篇文章。

JOINS声明

这些用于根据链接这些表的列组合来自至少两个数据集（即表）的行。为了成功演示这个场景，我们需要创建一个额外的数据集。

数据集创建

让我们从创建两个不同的数据集开始。

第一个名为personal_info_data，它将包含一个人的所有个人信息。
第二个名为backg_info_data，它将包含所有的学术、工资信息等。

# Prepare the query 
query_pers_info = "SELECT ID, Age, MartialStatus, Relationship, Race, Sex, Country FROM income_data"

# Store the result in the personal_info_data variable
personal_info_data = sqldf(query_pers_info)

# Show the first 5 rows of the result
head(personal_info_data, 5)

使用与前一个类似的方法创建第二个:

# Prepare the query
query_backg_info = "SELECT ID,Workclass, Education, Occupation, CapitalGain, CapitalLoss, HoursPerWeek, Income FROM income_data"

# Store the result in the backg_info_data variable 
backg_info_data = sqldf(query_backg_info)

# Show the first 5 rows of the result
head(backg_info_data, 5)

请注意，personal_info_data中的ID是指backg_info_ddata中的ID。因此，我们两个数据集之间的关系是ID列。sqldf可以执行所有不同类型的连接，但我们的重点是INNER JOIN，它返回两个表中具有匹配值的所有记录。

以下陈述摘录了成年人的年龄、婚姻状况、国家、教育程度和收入。

# Prepare the query
join_query = "SELECT p_info.ID, \
                  p_info.Age, \
                  p_info.MartialStatus, \
                  p_info.Country, \
                  bg_info.Education,\
                  bg_info.Income \
              FROM personal_info_data p_info \ 
              INNER JOIN backg_info_data bg_info \
              ON p_info.ID = bg_info.ID"

# Run the qery
join_data = sqldf(join_query)

# Show the first 5 observations
head(join_data, 5)

为了澄清和可读性，查询已被分解，并创建了附加变量。

spinfo：写personalinfodata会太长。教育，个人信息数据。我们创建了一个别名/实例，可以用来代替原始名称。别名通常比原始别名短。
bginfo：与前一个类似，是backginfodata的别名。

结论

你刚刚学习了如何使用sqldf与R数据帧交互。如果你仍然在执行复杂的任务，使用SQL可能会更容易，那么现在是时候试试sqldf了，它可能会帮助你和你的同事节省时间，提高效率！

R与SQL.....

介绍