Excel数据分析实战1：Titanic生存率的分析

一、数据集

可以去kaggle下载：Titanic - Machine Learning from Disaster | Kaggle

二、数据集信息

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

舱位维度	pclass 舱位	1代表头等舱，2代表二等舱，3代表三等舱
	cabin	客舱号
	survived	是否生还，0代表死亡，1代表死亡
乘客维度	name	姓名
	sex	性别
	age	年龄
	sibsp	兄弟姐妹/配偶的个数
	parch	父母/小孩个数
船票维度	ticket	船票信息
	fare	票价
地域维度	embarked	登船港口

三、数据处理

通过查看表 ，发现Age、fare、embarked、cabin字段有缺失。

 1.age缺失值处理

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

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

2.Fare缺失值：Fare值都是0，不算是缺失值

3.Embark缺失值

进一步观察到，这两个旅客都是单独出行，没有家人（从sibsp和parch列均为0得知），寻找同类型的进行填充。对第一个旅客，筛选出头等舱的年龄在35~40岁的女性中，港口最多的值填充进去，结果是S。

同样的方法，对第二个旅客，筛选头等舱年龄在60~65岁的女性中，登陆港口最多的值，结果也为S。

对这两个缺失值进行填充 

 4.Cabin缺失值

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

四、数据分析

1、舱位维度(pclass、cabin)

pclass

对舱位和生还情况分析，插入数据透视表，根据透视表可视化图

生还的人里，头等舱的占比达到了40%。

对每个舱位的生存死亡情况做百分比堆积柱形图，可以看到，头等舱生还的人数占比最多，达到62.92%，三等舱的生还人数占比最少，仅24.24%

 cabin

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

但是也发现了有1个客舱对应2个人以上的情况，进一步地把舱位拉进去对比一下，发现三等舱的数值很少，说明carbin缺失值大部分是三等舱缺失的，意思是三等舱的人没有客舱？大通铺？这个有待进一步查证。

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

---

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

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

生还的人中女性占比68.13%，远高于男性的31.87%。

 

女性生还人数占女性总数的74.20%，远远大于男性生还人数占男性总数的18.89%。

 

 

 性别&舱位

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

但是呢，头等舱女性的生还比例为96.8%，远高于其他两个舱位，且三等舱女性的生还比例只有50%。

 

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

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

进一步地，可以观察一下年龄的分布情况，做直方图，5岁为一组，可以看到，乘客的年龄主要集中在20~35岁，其中25~30岁的年轻人最多。

 

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

• 少年（0~15岁）
• 青年（15~40岁）
• 中年（41~65岁）
• 老年（66岁以上）

 先做一个分组的表，用vlookup的模糊匹配实现分组

 由于Vlookup公式查找匹配空白时，显示为0，所以少年组的阈值我们取0.0000001，这样就不会出现年龄是空白的列返回少年组，应该返回空白。

 在age旁新建一列age分组的辅助列，输入公式：=IF(ISERROR(VLOOKUP(F2,Sheet1!$H$59:$I$62,2,1)),"",VLOOKUP(F2,Sheet1!$H$59:$I$62,2,1))

 再对age分组和survived进行透视

可以看到生还的人中青年、少年的占比最多，老年占比最少。

对各年龄段分组的死亡、生存情况做百分比堆积柱形图，得到结果，少年获救的人数比例最高。

 

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

 

 同样因为基数大的缘故，生存下来的人中，亲戚数为0的占比最多达到了61.8%。

 对各标签做百分比堆积柱形图，这才是比较有意义的结果，可以看到，有1个亲戚数的人群获救的比例最高。

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

 

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

 

3、船票维度

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

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

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

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

 

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

 

 

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

 

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

4、地域维度

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

四、生还率同什么有关

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

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

再添加一列f_num字段，是sibsp和parch的和，意思是家庭成员数。

 embarked字段分解为3个辅助列，港口-S，港口-C，港口-Q，同时输入公式：

=IF(L2="S",1,0)，=IF(L2="C",1,0)，=IF(L2="Q",1,0)

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

 同理对舱位pclass也做同样的处理

 做数据分析里的相关系数。