经典的Excel数据分析实战案例,新手必看

文 | 商业智能研究 2021-01-11 11:23:57

今天给大家实操的案例是泰坦尼克号生存率的分析,这个案例已经有很多人写过,算是个比较经典的案例,通过船上乘客的信息分析和建模,预测哪些乘客得以生还,对数据分析新手来说很有学习价值,本篇就带大家用这个数据集进行一次完整的数据分析

使用工具:Excel(对,就是这么简单粗暴)

一、明确目的

1912年泰坦尼克号撞上冰山沉没,船上2224名乘客和机组人员中有1502人遇难,幸存下来的人是出于运气还是存在一定的规律?这是我们比较关心的,所以就要提出问题:

那些人士生还的可能性大?

其次了解数据,数据集总共有以下的字段,其中name、sex、cabin、embarked、ticket是字符串类型,pclass和survived虽然是数值型,但其含义是标签,我们分别从舱位、乘客、船票和地域的维度出发来分析。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

二、数据处理

通过查看,发现Age、fare、embarked、cabin字段都是有缺失的,下面我们一个一个来看。

1、age缺失值处理

筛选age一列为空的有263条数据,缺失率为20%,可以全部填充为年龄的均值或众数,也可以进一步地分析,发现年龄缺失的数据里三等舱的最多,占总缺失值的79%,而三等舱里的未生还的男性占比最多,因此也可以用三等舱年龄的平均值来填充。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

这里为了保持数据的真实性,就不做填充处理了

2、fare缺失值处理

筛选发现fare(票价)只缺失了一个值,我们把它找出来,发现可以用同类型的均值填充掉。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度因此我们筛选三等舱、年龄大于60岁的,登船港口为S的男性的均值票价7来填充这个缺失值。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

3、embarked缺失值处理

embarked登船港口字段也有2个缺失值,筛选出来看下。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度进一步观察到,这两个旅客都是单独出行,没有家人(从sibsp和parch列均为0得知),延续对fare缺失值处理的思路,寻找同类型的进行填充。对第一个旅客,筛选出头等舱的年龄在35~40岁的女性中,港口最多的值填充进去,结果是S。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度
同样的方法,对第二个旅客,筛选头等舱年龄在60~65岁的女性中,登陆港口最多的值,结果也为S。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

4、cabin缺失值处理

对于cabin(客舱)字段缺失值达到了77%,缺失太多了,就不做填充处理了,直接保留或删除,这里先保留着吧。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

三、数据分析

1、舱位维度

pclass对舱位和生还情况分析,插入数据透视表

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度生还的人里,头等舱的占比达到了40%。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

对每个舱位的生存死亡情况做百分比堆积柱形图,可以看到,头等舱生还的人数占比最多,达到61.92%,三等舱的生还人数占比最少,仅25.33%,所以还是那句老话,钱虽然不是万能的,但没钱@#%&^…

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

carbin对carbin(客舱号)做透视,可以看到有295个唯一值,基本上是一个客舱只住一个人。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度但是也发现了有1个客舱对应2个人以上的情况,进一步地把舱位拉进去对比一下,发现三等舱的数值很少,说明carbin缺失值大部分是三等舱缺失的,意思是三等舱的人没有客舱?大通铺?这个有待进一步查证。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

另外发现三等舱有客舱的都是E/F/G开头的客舱号,而头等舱A/B/C就较多,猜测客舱号是随着舱位的降低按字母升序排列的。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

2、乘客维度

namename姓名列没有什么有价值的信息,不过可以进一步思考的是,姓名里其实是对应了头衔的,比如Mr是已婚男士,Mrs是已婚女士等,但是这里就先删除了。

sex对性别和生还情况进行分析

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度生还的人中女性占比67.8%,远高于男性的32.2%。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度女性生还人数占女性总数的72.75%,远远大于男性生还人数占男性总数的19.10%。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度 excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

性别&舱位可以顺便看一下舱位和性别的关系,因为男性人口基数大,所以不管是哪个舱位,男性人数都是多于女性的,同理,各个舱位都是女性获救的人数最多。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度但是呢,头等舱女性的生还比例为97%,远高于其他两个舱位,且三等舱女性的生还比例只有49%。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

age对年龄和生还情况进行分析,这里因为年龄有缺失,仅对有数值的进行分析。

首先对年龄做一个简单的描述统计,用【数据分析】里的【描述统计】功能,可以看到年龄最大值为80岁,最小值为0.17岁,平均值为29.88岁,年龄中位数为28岁,众数为24岁。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度进一步地,可以观察一下年龄的分布情况,做直方图,5岁为一组,可以看到,乘客的年龄主要集中在15-30岁,其中20-25岁的年轻人最多。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

了解了年龄大致的分布后,就要来看特定人群的生还情况了,我们将年龄分为:

少年(0~15岁)

青年(15~40岁)

中年(41~65岁)

老年(66岁以上)

先做一个分组的表,用vlookup的模糊匹配实现分组
在age旁新建一列age分组的辅助列,输入公式

=VLOOKUP(E2,Sheet2!$B$18:$C$21,2,1)

Sheet2!18:21这个区域就是上图预先设置好的分组区域。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度
再对age分组和survived进行透视

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度可以看到生还的人中青年、少年的占比最多,老年占比最少。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度
对各年龄段分组的死亡、生存情况做百分比堆积柱形图,得到结果,少年获救的人数比例最高

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

sibsp对sibsp字段(兄弟姐妹妹/配偶的个数)分析,透视后可以看到标签为0,也就是说没有亲戚的人是船上乘客的大多数。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度同样因为基数大的缘故,生存下来的人中,亲戚数为0的占比最多达到了61.8%。
对各标签做百分比堆积柱形图,这才是比较有意义的结果,可以看到,有1个亲戚数的人群获救的比例最高。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

parch对parch字段分析(父母/小孩个数),同样可以看到,没有父母/小孩的人数是船上总人数的76%,同样,这部分人群获救的数量也最多。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度 excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

做百分比堆积柱形图,可以看到有3个父母/小孩的人群获救的比例最大,达到了62.5%。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

3、船票维度

fare对Fare(票价)字段分析,首先比较关注的是票价和舱位是否存在相关性,正常的逻辑是舱位越高,票价越高,这里算出pclass和fare的相关系数是-0.56,还是比较相关。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度还记得上面我们用vlookup的模糊匹配分组,还可以直接用数据透视表分组。透视以后组合,选择50步长一组,可以再对票价和舱位透视看看,看到100以上的高票价全都是头等舱,二等舱和三等舱的票价大部分为0~50。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

性别&票价女性的票价均价要高于男性

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

性别&舱位&票价头等舱的均价远高于其他两个舱,每个舱女性的均价都要高于男性,其中票价的最大值512出自头等舱的女性。另外一个比较有意思的现象是,票价为0的居然都是男性。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

都写到这儿了,可以再引申出一个问题,票价到底和什么有关?性别?登陆港口?舱位?客舱?有兴趣的小伙伴可以自己再深入探讨一下,这里我们就不探索下去了。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

接下来,50一组看一下fare的分布情况,可以看到票价为0~50的占了船上乘客的82%。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度同时存活数量最多的还是0~50票价的人群,因为它的基数本身就很大。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

从各票价分组的角度来看,做百分比堆积柱形图,可以看到,500-550票价的人群存活比例为100%,而0-50票价的存活比例只有32%。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

ticketticket字段是船票信息/代号,没有特别大的分析意义,这里也就直接删除了。

4、地域维度

embarked对embarked(登船港口)字段分析,透视后发现S港口登船的人数最多,从堆积柱形图中可以看到,C扣登船的生成比例最高。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度 excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

四、生还率同什么有关

生还率同什么相关?这个是我们最关心的,这个问题其实就是survived字段同其他字段的相关系数。

sex列是字符型数据,要映射成数值,我们添加一列命名为性别的辅助列,male为1,female为0.

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度再添加一列f_num字段,是sibsp和parch的和,意思是家庭成员数。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度embarked字段分解为3个辅助列,港口-S,港口-C,港口-Q,同时输入公式:

=IF(N2=”S”,1,0)

如果embarked这个字段是S,那么港口-S列为1,港口-C、港口-Q为0,以此类推。

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度同理对舱位pclass也做同样的处理

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度用【数据分析】里的【相关系数】功能,可以看到每个字段的相关系数

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度
降序排列一下,就可以看出生还率同什么相关了

excel数据分析案例,excel数据分析实战,怎么用excel数据分析,数据分析维度

所以回到我们最初的问题:

哪些人生还的可能性大?

总结一下:

虽然三等舱的人数最多(54%),但头等舱生还的比例最高(62%)

虽然男性的人数(64%)多于女性,但女性的生还率(72%)远高于男性(19%)

头等舱女性的生还比例(97%)远高于三等舱女性的生还比例(49%)

15-40岁的青年人数最多(53%),生还率最高的是0~15岁的少年(56%)

亲戚的个数为0的人数最多(68%),为1的生还率最高(51%)

父母/孩子个数为0的人数最多(76%),为3的生还率最高(63%)

票价在0-50范围内的人数最多(82%),但500~550范围内票价的人生还率为100%

S港口登船的人数最多(70%),但是C港口生还率最高(56%)

很显然在目前的信息时代,借助类似于FineBI的这些工具,可以让企业加速融入企业数据分析的趋势。备受市场认可的软件其实有很多,选择时必须要结合实际的情况。一般的情况下,都建议选择市面上较主流的产品,比较容易达到好的效果,目前企业数据分析BI软件市场占有率前列的,就是帆软BI软件——FineBI。

商业智能BI产品更多介绍:www.finebi.com

免费试用FineBI

阅读次数:2,610


本月阅读量最高的10篇文章

立即试用FineBI,还可获得更多的方案、案例和模板

免费下载和试用 >

售前咨询

服务热线

400-811-8890转1

售后咨询

在线QQ

800049425

服务热线

400-811-8890转2

投诉建议

总裁办24H投诉

173-1278-1526