用Python执行SQL、Excel常见操作？10个方法全搞定！

来源&＃xff1a;Python全栈技术


数据从业者有许多工具可用于分割数据。有些人使用 Excel&＃xff0c;有些人使用SQL&＃xff0c;有些人使用Python。对于某些任务&＃xff0c;使用 Python 的优点是显而易见的。以更快的速度处理更大的数据集。使用基于 Python 构建的开源机器学习库。你可以轻松导入和导出不同格式的数据。

由于其多功能性&＃xff0c;Python 可以成为任何数据分析师工具箱的重要组成部分。但是&＃xff0c;这很难开始。大多数数据分析师可能熟悉 SQL 或 Excel。本篇是涉及帮助你将技能和技术从 EXcel 和 SQL 转移到 Python。

首先&＃xff0c;让我们来设置 Python。最简单的方法就是使用 Jupyter Notebook 和 Anaconda。这个可视化界面将允许你插入 Python 代码并立即查看输出。这也将使你轻松跟随本教程的其余部分。

我强烈推荐使用 Anaconda&＃xff0c;但这个初学者指南也将帮助你安装 Python——尽管这将使本篇文章更加难以接受。

我们从基础开始&＃xff1a;打开一个数据集。

01 导入数据

你可以导入.sql 数据库并用 SQL 查询中处理它们。在Excel中&＃xff0c;你可以双击一个文件&＃xff0c;然后在电子表格模式下开始处理它。在 Python 中&＃xff0c;有更多复杂的特性&＃xff0c;得益于能够处理许多不同类型的文件格式和数据源的。

使用一个数据处理库 Pandas&＃xff0c;你可以使用 read 方法导入各种文件格式。使用这个方法所能导入完整的文件格式清单是在 Pandas 文档中。你可以导入从 CSV 和 Excel 文件到 HTML 文件中的所有内容&＃xff01;

使用 Python 的最大优点之一是能够从网络的巨大范围中获取数据的能力&＃xff0c;而不是只能访问手动下载的文件。在 Python 的requests 库可以帮助你分类不同的网站&＃xff0c;并从它们获取数据&＃xff0c;而 BeautifulSoup 库可以帮助你处理和过滤数据&＃xff0c;那么你将精确得到你所需要的。如果你要去这条路线&＃xff0c;请小心使用权问题。

在这个例子中&＃xff0c;我们将获取许多国家人均 GDP&＃xff08;一个技术术语&＃xff0c;意思是一个国家的人均收入&＃xff09;的维基百科表格&＃xff0c;并在 Python 中使用 Pandas 库对数据进行排序。

首先&＃xff0c;导入我们需要的库。

需要 Pandas 库处理我们的数据。需要 numpy 库来执行数值的操作和转换。我们需要 requests 库来从网站获取 HTML 数据。需要 BeautifulSoup 来处理这些数据。最后&＃xff0c;需要 Python&＃xff08;re&＃xff09;的正则表达式库来更改在处理数据时将出现的某些字符串。

在 Python 中,不需要知道很多关于正则表达式的知识&＃xff0c;但它们是一个强大的工具&＃xff0c;可用于匹配和替换某些字符串或子字符串。如果你想了解更多&＃xff0c;请参考以下内容。

02 信任这个网站的一些代码

这是一个更具技术性的解释&＃xff0c;详细说明如何使用 Python 代码来获取 HTML 表格。

你可以将上面的代码复制粘贴到你自己的 Anaconda 中&＃xff0c;如果你用一些 Python 代码运行&＃xff0c;可以迭代它&＃xff01;

下面是代码的输出&＃xff0c;如果你不修改它&＃xff0c;就是所谓的字典。

你会注意到逗号分隔起来的括号的 key-value 列表。每个括号内的列表都代表了我们 dataframe 中的一行&＃xff0c;每列都以 key 表示&＃xff1a;我们正在处理一个国家的排名&＃xff0c;人均 GDP&＃xff08;以美元表示&＃xff09;及其名称&＃xff08;用「国家」&＃xff09;。

有关数据结构&＃xff0c;如列表和词典&＃xff0c;如何在 Python 中的运行的更多信息&＃xff0c;本篇将有所帮助。

幸运的是&＃xff0c;为了将数据移动到 Pandas dataframe 中&＃xff0c;我们不需要理解这些数据&＃xff0c;这是将数据聚合到 SQL 表或 Excel 电子表格的类似方式。使用一行代码&＃xff0c;我们已经将这些数据分配并保存到 Pandas dataframe 中 —— 事实证明是这种情况&＃xff0c;字典是要转换为 dataframe 的完美数据格式。

通过这个简单的 Python 赋值给变量 gdp&＃xff0c;我们现在有了一个 dataframe&＃xff0c;可以在我们编写 gdp 的时候打开和浏览。我们可以为该词添加 Python 方法&＃xff0c;以创建其中的数据的策略视图。作为我们刚刚在 Python 中使用等号和赋值的一点深入了解&＃xff0c;很有帮助。

03 快速查看数据

现在&＃xff0c;如果要快速查看我们所做的工作&＃xff0c;我们可以使用 head() 方法&＃xff0c;它与 Excel 中的选择几行或SQL中的 LIMIT 方法非常相似。轻松地使用它来快速查看数据集&＃xff0c;而无需加载整个数据集&＃xff01;如果要查看特定数量的行&＃xff0c;还可以在 head() 方法中插入行数。

我们得到的输出是人均 GDP 数据集的前五行&＃xff08;head 方法的默认值&＃xff09;&＃xff0c;我们可以看到它们整齐地排列成三列以及索引列。请注意&＃xff0c;Python 索引从0开始&＃xff0c;而不是1&＃xff0c;这样&＃xff0c;如果要调用 dataframe 中的第一个值&＃xff0c;则使用0而不是1&＃xff01;你可以通过在圆括号内添加你选择的数字来更改显示的行数。试试看&＃xff01;

04 重命名列

有一件你在 Python 中很快意识到的事是&＃xff0c;具有某些特殊字符&＃xff08;例如$&＃xff09;的名称处理可能变得非常麻烦。我们将要重命名某些列&＃xff0c;在 Excel 中&＃xff0c;可以通过单击列名称并键入新名称&＃xff0c;在SQL中&＃xff0c;你可以执行 ALTER TABLE 语句或使用 SQL Server 中的 sp_rename。

在 Pandas 中&＃xff0c;这样做的方式是rename 方法。

在实现上述方法时&＃xff0c;我们将使用列标题 「gdp_per_capita」 替换列标题「US $」。一个快速的 .head() 方法调用确认已经更改。

05 删除列

有一些数据损坏&＃xff01;如果你查看 Rank 列&＃xff0c;你会注意到散乱的随机破折号。这不是很好&＃xff0c;由于实际的数字顺序被破坏&＃xff0c;这使得 Rank 列无用&＃xff0c;特别是使用 Pandas 默认提供的编号索引。

幸运的是&＃xff0c;使用内置的 Python 方法&＃xff1a;del&＃xff0c;删除列变得很容易。

现在&＃xff0c;通过另外调用 head 方法&＃xff0c;我们可以确认 dataframe 不再包含 rank 列。

06 在列中转换数据类型

有时&＃xff0c;给定的数据类型很难使用。这个方便的教程将分解 Python 中不同数据类型之间的差异&＃xff0c;以便你需要复习。

在 Excel 中&＃xff0c;你可以右键单击并找到将列数据转换为不同类型的数据的方法。你可以复制一组由公式呈现的单元格&＃xff0c;并将其粘贴为值&＃xff0c;你可以使用格式选项快速切换数字&＃xff0c;日期和字符串。

有时候&＃xff0c;在 Python 中切换一种数据类型为其他数据类型并不容易&＃xff0c;但当然有可能。

我们首先在 Python 中使用 re 库。我们将使用正则表达式来替换 gdp_per_capita 列中的逗号&＃xff0c;以便我们可以更容易地使用该列。

re.sub 方法本质上是使用空格替换逗号。以下详细介绍了 re库 的各个方法。

现在我们已经删除了逗号&＃xff0c;我们可以轻易地将列转换为数字。

现在我们可以计算这列的平均值。

我们可以看到&＃xff0c;人均 GDP 的平均值约为13037.27美元&＃xff0c;如果这列被判断为字符串&＃xff08;不能执行算术运算&＃xff09;&＃xff0c;我们就无法做到这一点。现在&＃xff0c;可以对我们以前不能做的人均 GDP 列进行各种计算&＃xff0c;包括通过不同的值过滤列&＃xff0c;并确定列的百分位数值。

07 选择/过滤数据

任何数据分析师的基本需求是将大型数据集分割成有价值的结果。为了做到这一点&＃xff0c;你必须检查一部分数据&＃xff1a;这对选择和过滤数据是非常有帮助的。在 SQL 中&＃xff0c;这是通过混合使用 SELECT 和不同的其他函数实现的&＃xff0c;而在 Excel 中&＃xff0c;可以通过拖放数据和执行过滤器来实现。

你可以使用 Pandas 库不同的方法或查询快速过滤。

作为一个快速的代表&＃xff0c;只显示人均 GDP 高于 5 万美元的国家。

这是这样做到的&＃xff1a;

我们为一个新的 dataframe 分配一个布尔索引的过滤器&＃xff0c;这个方法基本上就是说「创建一个人均 GDP 超过 50000 的新 dataframe」。现在我们可以显示gdp50000。

有12个国家的 GDP 超过 50000&＃xff01;

选择属于以 s 开头的国家的行。

现在可以显示一个新 dataframe&＃xff0c;其中只包含以 s 开头的国家。使用 len 方法快速检查&＃xff08;一个用于计算 dataframe 中的行数的救星&＃xff01;&＃xff09;表示我们有 25 个国家符合。

要是我们想把这两个过滤条件连在一起呢&＃xff1f;

这里是连接过滤的方法。在多个过滤条件之前&＃xff0c;你想要了解它的工作原理。你还需要了解 Python 中的基本操作符。为了这个练习的目的&＃xff0c;你只需要知道「&」代表 AND&＃xff0c;而「|」代表 Python 中的 OR。然而&＃xff0c;通过更深入地了解所有基础运算符&＃xff0c;你可以用各种条件轻松地处理的数据。

让我们继续工作&＃xff0c;并在过滤选择以「S」开头且有大于 50,000 人均 GDP 的国家。

现在过滤以「S」开头 或人均 GDP 超过 50000 的国家。

我们正在努力处理 Pandas 中的过滤视图。

08 用计算机来处理数据

没有可以帮助计算不同的结果的方法&＃xff0c;那么 Excel 会变成什么&＃xff1f;

在这种情况下&＃xff0c;Pandas 大量依赖于 numpy 库和通用 Python 语法将计算放在一起。对我们一直在研究的 GDP 数据集进行一系列简单的计算。例如&＃xff0c;计算人均国民生产总值超过 5 万的总和。

这将给你答案为 770046 。使用相同的逻辑&＃xff0c;我们可以计算各种的值 — 完整列表位于左侧菜单栏下的计算/描述性统计部分的 Pandas 文档。

09 数据可视化&＃xff08;图表/图形&＃xff09;

数据可视化是一个非常强大的工具 – 它允许你以可理解的格式与其他人分享你获得的见解。毕竟&＃xff0c;一张照片值得一千字。SQL 和 Excel 都具有将查询转换为图表和图形的功能。使用 seaborn 和 matplotlib 库&＃xff0c;你可以使用 Python 执行相同操作。

有关数据可视化选项的综合的教程 – 我最喜欢的是这个 Github readme document &＃xff08;全部在文本中&＃xff09;&＃xff0c;它解释了如何在 Seaborn 中构建概率分布和各种各样的图。这应该让你了解 Python 中数据可视化的强大功能。如果你感到不知所措&＃xff0c;你可以使用一些解决方案&＃xff0c;如Plot.ly&＃xff0c;这可能更直观地掌握。

我们不会检查每一个数据可视化选项&＃xff0c;只要说使用 Python&＃xff0c;可以比任何 SQL 提供的功能具有更强大的可视化功能&＃xff0c;必须权衡使用 Python 获得更多的灵活性&＃xff0c;以及在 Excel 中通过模板生成图表的简易性。

在这种情况下&＃xff0c;我们将建立一个简单的直方图&＃xff0c;显示人均 GDP 超过 5 万美元的国家的人均 GDP 分布。

有了这个强大的直方图方法 (hist())&＃xff0c;我们现在可以生成一个直方图&＃xff0c;显示出大部分人均 GDP 在 5 万到 7 万美元之间&＃xff01;

10 分组和连接数据

在 Excel 和 SQL 中&＃xff0c;诸如 JOIN 方法和数据透视表之类的强大工具可以快速汇总数据。

Pandas 和 Python 共享了许多从 SQL 和 Excel 被移植的相同方法。可以在数据集中对数据进行分组&＃xff0c;并将不同的数据集连接在一起。你可以看看这里的文档。你会发现&＃xff0c;由 Pandas 中的merge 方法提供的连接功能与 SQL 通过 join 命令提供的连接功能非常相似&＃xff0c;而 Pandas 还为过去在 Excel 中使用数据透视表的人提供了 pivot table 方法。

我们将制定的人均 GDP 的表格与世界银行的世界发展指数清单进行简单的连接。

首先导入世界发展指数的 .csv文件。

使用 .head() 方法快速查看这个数据集中的不同列。

现在我们完成了&＃xff0c;我们可以快速看看&＃xff0c;添加了几个可以操作的列&＃xff0c;包括不同年份的数据来源。

现在我们来合并数据&＃xff1a;

我们现在可以看到&＃xff0c;这个表格包含了人均 GDP 列和具有不同列的遍及全国的数据。对于熟悉 SQL join 的用户&＃xff0c;你可以看到我们正在对原始 dataframe 的 Country 列进行内部连接。

现在我们有一个连接表&＃xff0c;我们希望将国家和人均 GDP 按其所在地区进行分组。
我们现在可以使用 Pandas 中的 group 方法排列按区域分组的数据。

要是我们想看到 groupby 总结的永久观点怎么办&＃xff1f;Groupby 操作创建一个可以被操纵的临时对象&＃xff0c;但是它们不会创建一个永久接口来为构建聚合结果。为此&＃xff0c;我们必须使用 Excel 用户的旧喜爱&＃xff1a;数据透视表。幸运的是&＃xff0c;Pandas 拥有强大的数据透视表方法。

你会看到我们收集了一些不需要的列。幸运的是,使用 Pandas 中的 drop 方法&＃xff0c;你可以轻松地删除几列。

现在我们可以看到&＃xff0c;人均 GDP 根据世界不同地区而不同。我们有一个干净的、包含我们想要的数据的表。

这是一个非常肤浅的分析&＃xff1a;你想实际做一个加权平均数&＃xff0c;因为每个国家的人均 GDP 不代表一个群体中每个国家的人均 GDP&＃xff0c;因为在群体中的人口不同。

事实上&＃xff0c;你将要重复我们所有的计算&＃xff0c;包括反映每个国家的人口列的方法&＃xff01;看看你是否可以在刚刚启动的 Python notebook 中执行此操作。如果你可以弄清楚&＃xff0c;你将会很好地将 SQL 或 Excel 知识转移到 Python 中。