![e17622846574f7e6845c3c67f5cd43eccd659582.gif](https://i0.hdslb.com/bfs/article/e17622846574f7e6845c3c67f5cd43eccd659582.gif)
Excel和Python,作为数据分析的主流工具,在从效率提升到数据商业化的整个过程中,都起到了重要作用。不管是在Excel中通过鼠标点选实现,亦或是利用Python通过代码实现,数据分析中的很多基础功能都是相通的。
在数据量级大跃进的今天,对于从业者来说,熟练掌握用于数据处理的编程语言非常必要,通晓两者可以更增竞争力。而借助大家最为熟悉的Excel操作,对照学习相应的Python实现,可以帮助更快理解,轻松掌握。
都说Excel关键要掌握4个核心函数(vlookup,if,sum,sumif)和1个核心功能(数据透视表),本期我们就聚焦在广受喜欢的数据透视表和VLOOKUP的操作。
数据透视表
使用数据透视表,可以快速汇总数据,进而进行进一步的分析。
例如,对于一张简单的家庭开支列表,可以基于该列表制作数据透视表![51470ed321de4a07212805e305f060b7390a9bc8.png](https://i0.hdslb.com/bfs/article/51470ed321de4a07212805e305f060b7390a9bc8.png)
![a1a55d0628d89c0ac62524c5431570b0b18ebf09.png](https://i0.hdslb.com/bfs/article/a1a55d0628d89c0ac62524c5431570b0b18ebf09.png)
1.Excel实现
使用数据透视表,在“插入”——“数据透视表”,选中需要创建数据透视表的数据。
我们可以看到如下界面:上半部分为数据透视表中的所有字段,下半部分为数据透视表的选项,把上方的字段拖入下方对应框中即可完成数据透视表。
这里使用2018年国内上市公司管理层报酬统计数据为例,以“公司行业”为行标签,“公司组织形式”为列标签,对“年度报酬总额(万元)”数据计算平均数,得到如下分类汇总和聚合计算。![6881b8ea46fca92a0d3f8340c402a71ecff62650.png](https://i0.hdslb.com/bfs/article/6881b8ea46fca92a0d3f8340c402a71ecff62650.png)
![75141506c6f61d31721cd7ee7aa1946623814e0b.png](https://i0.hdslb.com/bfs/article/75141506c6f61d31721cd7ee7aa1946623814e0b.png)
![f3aca61850d8926264b557acaf7fd6408c8ca13d.png](https://i0.hdslb.com/bfs/article/f3aca61850d8926264b557acaf7fd6408c8ca13d.png)
2.Python实现
Python中数据透视表的实现原理与Excel类同,使用pandas库中的pivot_table()方法。
data:要进行数据透视表操作的DataFrame对象
values:要进行值计算的列,对应Excel操作中“数值”框
index:行索引,对应Excel操作中“行标签”框
columns:列名,对应Excel操作中“列标签”框
aggfunc:设定对values要进行的计算类型
fill_value:对空值的填充值
margins:是否显示合计列
dropna:是否删除缺失值
margins_name:设置合计列的列名![c33d91b47ed46ea01f18d4b9d93b4e7d170ee4c9.png](https://i0.hdslb.com/bfs/article/c33d91b47ed46ea01f18d4b9d93b4e7d170ee4c9.png)
![2261e7cc3886200bff5cdfe5cc4f6a9a56e8bd95.png](https://i0.hdslb.com/bfs/article/2261e7cc3886200bff5cdfe5cc4f6a9a56e8bd95.png)
![dbb1ad8ef86c28f175196364407c679922d0fa9a.png](https://i0.hdslb.com/bfs/article/dbb1ad8ef86c28f175196364407c679922d0fa9a.png)
![fdbd84cd5d739f7cde45a90576513e0d7916a418.jpg](https://i0.hdslb.com/bfs/article/fdbd84cd5d739f7cde45a90576513e0d7916a418.jpg)
![6eea143872bf0cddb3f9fd88e93c22630e1af5c8.png](https://i0.hdslb.com/bfs/article/6eea143872bf0cddb3f9fd88e93c22630e1af5c8.png)
当然,我们可以进行更多灵活操作,如对各类别的“前三名高管报酬总额”求中位数,对“年度报酬总额”求均值,这种对不同的值进行不同类型计算,可以通过aggfunc传入字典(键为列名,值为计算方式)来设定。![4fa2e75ea13c9bc62f72504e288e6f30c8556eaa.png](https://i0.hdslb.com/bfs/article/4fa2e75ea13c9bc62f72504e288e6f30c8556eaa.png)
![d00dcdf9b5a6694b8f2ef9dbd278efcb8ef9a3cc.png](https://i0.hdslb.com/bfs/article/d00dcdf9b5a6694b8f2ef9dbd278efcb8ef9a3cc.png)
VLOOKUP
实际工作中,我们经常会需要用到查找功能,VLOOKUP很多时候是Excel用户学习的第一个查找函数,也是Excel最常使用的三个函数之一,被称为Excel中的效率之王。它应用非常广泛,比如可以将多表匹配、合并,达到对比、查漏等效果,甚至前段时间新诞生的xlookup也借着它的大名火了一把。
例如,在如下信息表中,我们可以查找到与ID号102完全匹配的姓氏,进行返回。![0a26715e7544bc51ddee041916c0bd1e2d23864d.png](https://i0.hdslb.com/bfs/article/0a26715e7544bc51ddee041916c0bd1e2d23864d.png)
1.Excel实现
VLOOKUP这个名称中,v为vertical竖直的意思,lookup即为查找,它实现的是纵向按列查找,返回该列所需查询序列所对应的值。
函数表示:![d996b7e8b77b9d1255a9e79468195dbb47e34af4.png](https://i0.hdslb.com/bfs/article/d996b7e8b77b9d1255a9e79468195dbb47e34af4.png)
即:VLOOKUP(你想要查找的内容, 要查找的范围, 包含要返回的值的区域中的列号, 返回近似或精确匹配)
这里我们继续使用2018年国内上市公司管理层报酬统计数据,需要查找指定的10家上市公司名称、年度报酬总额、前三名董事报酬总额,及前三名高管报酬总额。在Excel中,就可使用VLOOKUP函数实现,如L2的名称数据,是以J2为要进行查找的关键字,查找范围为A2:H3594,返回列数为2(表示查找范围的第2列,即“名称”),匹配模式为FALSE(表示精确匹配)而得到。![ced4fbaf4def8efecc5735204b2d11eec2df88cc.jpg](https://i0.hdslb.com/bfs/article/ced4fbaf4def8efecc5735204b2d11eec2df88cc.jpg)
2.Python实现
Python中有多种方法可以实现该操作,推荐使用pandas库中的merge()函数,它是全功能、高性能的连接操作,在习惯上非常类似于SQL等关系数据库。
left:参与合并的左侧DataFrame
right:参与合并的右侧DataFrame
how:合并方式,为inner,outer,left,right其中之一
on:用于连接的列名,必须存在于左右两侧数据
left_on:左侧DataFrame中用作连接键的列
right_on:右侧DataFrame中用作连接键的列
left_index:将左侧的行索引用作其连接键
right_index:将右侧的行索引用作其连接键
sort:根据连接键对合并后的数据进行排序![425d6cbbba4e35f9701f5592c6bb1e0dfe6e806c.png](https://i0.hdslb.com/bfs/article/425d6cbbba4e35f9701f5592c6bb1e0dfe6e806c.png)
![3fbd1aca5cd5c8b822f99b74ca26efb17d5b55ad.png](https://i0.hdslb.com/bfs/article/3fbd1aca5cd5c8b822f99b74ca26efb17d5b55ad.png)
![f64732abccddf9f84da6e8e7955552ae847b7904.png](https://i0.hdslb.com/bfs/article/f64732abccddf9f84da6e8e7955552ae847b7904.png)
![76281d4e3b1fb07cb18327450b4d7fec268c6bde.png](https://i0.hdslb.com/bfs/article/76281d4e3b1fb07cb18327450b4d7fec268c6bde.png)
通过how进行设置的合并方法,主要取决于merge操作的目的:
若只需要查找到指定的10家上市公司数据,则可采用左连接,以放在左侧的avgs数据为基准;
若主要想对右侧salary数据中的3593家上市公司管理层报酬数据进行分析,仅是将已有的员工人均薪酬数据加入,则可考虑使用右连接;
若想保留同时出现在左右两侧的数据,则可考虑使用内连接,取键的交集;
若想将左右两侧数据都进行保留,则可考虑使用外连接,取键的并集。![688fcbc3efea14f8d7d0d8a4a9f4597dede4f707.png](https://i0.hdslb.com/bfs/article/688fcbc3efea14f8d7d0d8a4a9f4597dede4f707.png)
![18e14619191abc80462e1aeb6fc1528675fa8ab0.jpg](https://i0.hdslb.com/bfs/article/18e14619191abc80462e1aeb6fc1528675fa8ab0.jpg)
除了使用on指定连接键,我们也可将代码设置为index,通过index作为连接键进行匹配
![4eb30729132d4c74573e89f1459c77fded7da722.png](https://i0.hdslb.com/bfs/article/4eb30729132d4c74573e89f1459c77fded7da722.png)
![2906f0c1d6328b71cc03aea4ab99d1b93dcc2c73.png](https://i0.hdslb.com/bfs/article/2906f0c1d6328b71cc03aea4ab99d1b93dcc2c73.png)
![0b1fc7523e1d8e9e188a7c4083daafb881911189.png](https://i0.hdslb.com/bfs/article/0b1fc7523e1d8e9e188a7c4083daafb881911189.png)
![f19045cb3cce982bd3b23d85acf20d6a6a764a92.png](https://i0.hdslb.com/bfs/article/f19045cb3cce982bd3b23d85acf20d6a6a764a92.png)
![8eb516022b24cb76af6012408050b54a6ed951f7.png](https://i0.hdslb.com/bfs/article/8eb516022b24cb76af6012408050b54a6ed951f7.png)
![c95d034743a38d7fb63a78e871fefde98a4abb2d.jpg](https://i0.hdslb.com/bfs/article/c95d034743a38d7fb63a78e871fefde98a4abb2d.jpg)