SQL和Python中的特征工程：一种混合方法

在了解Pandas之前，我很早就了解SQL，Pandas忠实地模拟SQL的方式使我很感兴趣。通常，SQL是供分析人员使用的，他们将数据压缩为内容丰富的报告，而Python供数据科学家使用的数据来构建（和过度拟合）模型。尽管它们在功能上几乎是等效的，但我认为这两种工具对于数据科学家有效地工作都是必不可少的。从我在熊猫的经历中，我注意到了以下几点：

• 当探索不同的功能时，我最终得到许多CSV文件。
• 当我聚合一个大DataFrame时，Jupyter内核就会死掉。
• 我的内核中有多个数据框，名称混乱（且太长）。
• 我的特征工程代码看起来很丑陋，散布在许多单元中。

当我直接开始使用SQL进行功能设计时，这些问题自然就会解决。因此，在这篇文章中，我将通过处理实战挑战数据集来分享一些我最喜欢的技巧。如果您了解一点SQL，那么现在就可以充分利用它了。

首先，您需要一个SQL Server。我在这篇文章中使用MySQL。您可以通过安装本地桌面服务器之一（例如MAMP，WAMP或XAMPP）来获得MySQL服务器。在线上有许多教程，值得一试。

设置服务器后，请确保准备好三个项目：用户名，密码，端口号。通过输入以下命令通过终端登录（此处我们的用户名为“ root”，密码为1234567）。

然后在MySQL控制台中创建一个名为“ Shutterfly”的数据库（您可以随意命名）。这两个表将被加载到该数据库中。

您需要Pandas和sqlalchemy才能在Python中使用SQL。你可能已经有Pandas了。然后通过激活所需的环境以启动Jupyter Notebook来安装sqlalchemy，然后输入：

sqlalchemy模块还需要 MySQLdb 和 mysqlclient 模块。根据您的操作系统，可以使用不同的命令进行安装 。

在此示例中，我们将从两个CSV文件加载数据 ，并直接在MySQL中设计工程师功能。要加载数据集，我们需要 使用用户名，密码，端口号和数据库名称实例化 引擎对象。将创建两个表： Online 和 Order。将在每个表上创建一个自然索引。

在MySQL控制台中，您可以验证是否已创建表。

由于我们尚未构建任何特征，因此这似乎违反直觉。但这实际上非常整洁，因为我们要做的就是按索引拆分 数据集。通过设计，我还包括了我们尝试预测的标签。加载要素时，我们只需将索引与要素表连接。

在MySQL控制台中，您可以验证是否已创建训练和测试集。

这是繁重的部分。我直接在Sublime Text中编写SQL代码，然后将其粘贴到MySQL控制台中来调试代码。因为此数据集是一个事件日志，所以我们必须避免将来的信息泄漏到每个数据点中。可以想象，每个功能都需要在历史记录中汇总！

连接表是最慢的操作，因此我们希望从每个连接中获得尽可能多的功能。在此数据集中，我实现了四种类型的联接，从而产生了四组要素。详细信息并不重要，但是您可以在此处找到我的所有SQL代码段 。每个代码段都会创建一个表格。 该索引将保留，并且必须与训练集和测试集中的响应变量正确匹配。 每个代码段的结构如下：

要生成特征表，请打开一个新的终端，导航到包含sql文件的文件夹，然后输入以下命令和密码。第一个代码段创建了一些必要的索引，以加快联接操作。接下来的四个代码片段将创建四个特征表。使用索引，大约需要20分钟（在本地计算机上还不错）。

现在，您应该在数据库中具有以下表格。请注意，派生的功能与原始事件日志分开存储，这有助于防止混乱和灾难。

在这里，我编写了一个实用程序函数，该函数从MySQL服务器提取数据。

• 如果只需要数据的子集，则该函数将表名称“ trn_set”（训练集）或“ tst_set”（测试集）作为输入，并使用可选的 limit 子句。
• 删除唯一列和缺少大多数值的列。
• 日期列映射到月份，以帮助捕获季节性影响。
• 注意功能表是如何连续连接的。这实际上是有效的，因为我们总是在一对一映射上连接索引。

最后，让我们看一下5个训练示例及其特征。

现在，您已经有了定义明确的数据集和特征集。您可以调整每个特征的比例和缺失值，以适合您模型的要求。

对于不变于特征缩放的基于树的方法，我们可以直接应用模型，仅关注调整参数。

很高兴看到，除了类别 功能以外，所有有用的功能都是经过精心设计的 。我们的努力得到了回报！同样，事件2的最具预测性的特征是在事件2中观察到了多少个空值。这是一个说明性案例 ，其中我们无法用中值或平均值替换空值，因为缺少空值的事实与响应变量相关！

如您所见，我们没有中间的CSV文件，笔记本中没有非常干净的名称空间，功能工程代码简化为一些简单的SQL语句。在两种情况下，SQL方法更加有效：

• 如果您的数据集已部署在云上，则您可以运行分布式查询。今天，大多数SQL Server支持分布式查询。在熊猫中，您需要一些名为Dask DataFrame的扩展 。
• 如果您有能力实时提取数据，则可以创建SQL 视图 而不是表。这样，每次在Python中提取数据时，您的数据将始终是最新的。

这种方法的一个基本限制是您必须能够直接使用Python连接到SQL Server。如果无法做到这一点，则可能必须将查询结果下载为CSV文件并将其加载到Python中。