用Python实现Excel经典功能:VLOOKUP及Pivot Tables

用Python实现Excel经典功能:VLOOKUP及Pivot Tables

Excel——一种无处不在的数据分析工具。因为很容易上手,所以大多数人都有这个工具,而且一旦你掌握了其中诀窍,它就会变得非常强大!同时,人们一般会认为Python更具挑战性,但不得不说,它也有无着限的潜力。下面这篇文章,我们会一起来探讨在Python中可以轻松完成的三件Excel功能。如果你想了解更多数据分析相关内容,可以阅读以下这些文章:
用Python (scikit-learn) 做PCA分析
使用Python的scikit-learn进行特征缩放
用 Python中 Matplotlib 对行为进行可视化的4条贴士,另附简易教程

首先,我们要从导入Pandas开始,然后从工作簿中可用的表格里加载出两个数据库,分别把它们命名为“sales”和“states”。

import pandas as pd
sales = pd.read_excel('https://github.com/datagy/mediumdata/raw/master/pythonexcel.xlsx', sheet_name = 'sales')
states = pd.read_excel('https://github.com/datagy/mediumdata/raw/master/pythonexcel.xlsx', sheet_name = 'states')

将数据集导入Pandas

想象一下,我们在数据上运行了head()方法,代码如下所示:

print(sales.head())

与Excel中的结果进行比较:

Excel和Python显示结果的对比

可以看到,该过程显示的数据与Excel显示数据的方式比较相似,但还是有一些主要区别的,比如:

  • Excel是从第1行开始,而Pandas从第0行(“索引”)开始,
  • Excel用A开头的字母来标记列,而Pandas用变量的名字标记每个列

接下来,让我们一起深入了解如何用Pandas来完成Excel任务。

Python中的IF函数

在Excel中使用IF函数非常容易,而且我们可以根据另一个单元格的条件来应用特定的标签。比如,我们可能需要需要创建一个新列,来判断B列中某单元格的值是否大于500。在Excel中,我们将列E取名为“大于500(MoreThan500)”,然后在单元格E2内输入:

在Excel中使用IF函数(Source: Nik Piepenbreier)

如果我们想在Pandas中做到这个功能,就可以简单地通过列表推导式(list comprehensions)应用相同的if语句:

列表推导式的详细解释

列表推导式是做这类工作的好工具,因为它减少了复杂的if/else语句的编写。当然,这些工作也可以用if/else语句完成,但用列表推导可以节省更多时间,而且还能让代码更简洁。

Pandas上的VLOOKUP

在我们的数据集中,城市在一张表上,而州/省在另一张表上。虽然可能不太理想,但我们可以在Excel中用VLOOKUP把数据连接起来。VLOOKUP的工作原理跟左连接(left join)很像,都会把左边这个数据集(left dataset)中的每条记录保存下来。在Excel表格中,上下查找某一列的特定值,然后就会在右边栏里得到一个值。

添加一个名为“State”的列,然后用VLOOKUP得到“State”表中的对应值。

使用VLOOKUP添加州/省信息 (图源:Nik Piepenbreier)

在Python中,我们也可以用Pandas的合并功能完成相同的操作。Pandas的合并功能会采用两个数据帧,然后把它们合并起来。代码如下:

接下来,让我们逐步解释一下这个代码:

  • 1、第一个参数是原始数据框(original dataframe)
  • 2、第二个参数是我们要查找值的目标数据框
  • 3、第三个参数是指定join的类型
  • 4、指定我们要合并的变量(如果变量在每个表里有不同的名称,我们还可以用left_on和right_on)

Pandas中的数据透视表
(Pivot Tables)

数据透视表是Excel最强大的功能之一——它可以让我们快速提取大型数据集中有实际意义的数据。接下来,让我们给每个城市的总销售额创建一个透视表。

在Excel中生成数据透视表 (图源:Nik Piepenbreier)

在这里,我们只需将“城市(City)”拖到“行(Rows)”部分,然后将“销售(Sales)”拖到“值(Values)”部分。Excel就会自动汇总每个城市的销售额。

为了在Pandas中生成相同的数据透视表,我们要写以下代码。

让我们再详细解释一下:

  • 1、使用sales.pivot_table,告诉Pandas我们要建立一个基于sales数据的透视表
  • 2、用Index指定汇总的条件
  • 3、用Values指定要汇总什么数据
  • 4、用Aggfunc指定要使用的函数(我们还可以使用均值(mean),最大值(max),最小值(min)等)

小结

本文中,我们学习了如何将Excel数据导入Pandas,如何完成IF和VLOOKUP函数,以及如何生成数据透视表。

你可能会问,如果Excel都可以完成这些,那为什么要用Pandas?这个问题,目前还没有准确答案。但可以说的是,Python能生成可重复利用、可追溯的代码,让我们可以轻松复制分析的设计过程。而Excel可能只适合小型的数据分析。总之,我非常建议你试一下Pandas,看看它能不能给你带来意外的收获。你还可以订阅我们的YouTube频道,观看大量数据科学相关公开课:https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ;在LinkedIn上关注我们,扩展你的人际网络!https://www.linkedin.com/company/dataapplab/

原文作者:Nik Piepenbreier
翻译作者:Lea
美工编辑:过儿
校对审稿:Jiawei Tong
原文链接:https://towardsdatascience.com/learn-how-to-easily-do-3-advanced-excel-tasks-in-python-925a6b7dd081