贝壳网二手房市场数据分析

下面以贝壳网二手房情况的为例，利用Excel进行数据分析。

Excel数据分析的一般步骤：

1. 提出问题（业务为基，解决什么问题）；

2. 理解数据（表格中字段含义）；

3. 清洗数据（清理“脏”数据，统一格式内容）；

4. 构建模型及数据可视化（用工具将问题结论呈现出来，Excel数据透视表，数据用图形表格展现）；

在这之前，进行数据分析前的准备工作：

数据来源：贝壳网二手房板块

爬取数据：GooSeeker

数据内容：字段包含：房源标题、房源概况、房源位置、市场热度、总价、单价等重点项。

爬取结果：8个重点城市(重庆、成都、武汉、大连、杭州、深圳、北京、上海)二手房数据24000条（每个城市3000条，贝壳网前100页）

1. 提出问题

2. 8大城市的市场均价排名情况、老旧房屋数量对比？

3. 8大城市主力户型占比分布是怎样的及其原因？

4. 重庆最热门十大商圈板块是哪些；

二、理解数据

Part 1：数据收集：GooSeeker爬取贝壳网8大城市二手房数据

GooSeeker爬取流程较为简单，设置好规则后分别按城市网址爬取，数据来源如下：见图01：

字段包含：城市、房源标题、房源概况、房源位置、市场热度、总价、单价

（图02为原始数据）

三、清洗数据

2.1：数据清洗， 是整个数据分析过程中不可缺少的一个环节，其结果质量直接关系到模型效果和最终结论。该过程通常要花费60%-70%的时间，用来将数据统一格式，一致化处理，下面我们来简单了解一下数据清洗的步骤：

第一步：缺失值清洗

1、确定缺失值范围（确定缺失值的重要性和缺失比例）

2、去除/隐藏不需要字段（勤备份，小规模试验再处理全量数据）

3、填充缺失内容（可以用经验值/同一指标[均值、中位数、众数]/不同指标）

4、重新取数（非常重要的指标，缺失率又很高的数据需要重新获取）

第二步：格式内容清洗

1、时间、日期、数值、全/半角等显示格式不一致；

2、内容中有不该存在的字符：例如身份证号出现汉字；

3、内容与该字段应有内容不符：姓名里写性别，身份证号写成手机号；

第三步：逻辑错误清洗

1、去重（注意字符的细小差别，不同的字符却表示的同一个意思）；

2、去除不合理值（比如年龄200岁，年收入100000万等）；

3、修正矛盾内容（比如年龄字段和身份证字段中的年龄不符）；

第四步：非需求数据清洗

尽量不删除字段，数据清洗过程中，一定要勤备份；

第五步：关联性验证

现在我们按照上面步骤来清洗数据，工具Excel2016，先介绍几个快捷键，以方便使用：

Ctrl+Shif+：选中该列，看项数是否相同，是否存在缺失；

Ctrl+Home：返回到工作表的开头；

Ctrl+E：按照样例向下填充单元格；

Ctrl+F：查找替换；

Ctrl+G：定位，用于定位缺失值；

Alt+D+F+F：字段行，筛选动作，用于筛选特定值，如空格；

2.1.1数据量很大时，如何快速查找缺失值

第一种：选中包含要查字段的一列，看右下角数据计项，与其他列作比较，这里就计数是24000个，可以查出缺失值；再用Ctrl+G：定位，定位条件为空，用于定位缺失单元格；

第二种：点字段行，按快捷键Alt+D+F+F，出现筛选各列，下拉全部值，筛选看是否存在空格；再用Ctrl+G：定位，定位条件为空，用于定位缺失单元格；见图3

第三种：依次选中A1，B1、C1等列，按快捷键Ctrl+Shif+，看计数项是否一样都为24000；再用Ctrl+G：定位，定位条件为空，用于定位缺失单元格；见图4

图3 筛选

图4 定位空值

筛选出空值后，如果较多：需要填充的值一样，可以填写一个后再按Ctrl+Enter，将所有空格填充（空格不连续也可）。但如果需要填充的值不一样，只有一个个填充。

2.2.2一致化处理

上面处理好缺失值后，还要将复杂的字段处理成简单的字段便于理解，2.2.2.1如下图2原始数据中第三列房源概况分解成7个子字段：小区名、房屋结构、面积、朝向、装修情况、有无电梯、是否别墅。

选中需要分列的列，在菜单栏中依次点击“数据”“分列”，在弹出来的对话框中，选择“分割符号”，下一步在其他中输入“|”，依次下一步，最后完成。如图5 概况拆分

按照2.1的步骤查找这7项的缺失值，仅有无电梯字段缺失，以房屋结构项筛选出现特别值别墅及玖誉，见下面图6 分列出现别墅：

图6 分列出现别墅

发现房屋结构默认为普通几室几厅（省去这列）外，值“别墅”及“玖誉”导致这几项错位显示。因此，需要调整：1）将房屋结构列值别墅列复制至最后空白列，整体的左移，房屋结构显示值几室几厅，电梯情况缺失值应补为无电梯，其余的电梯情况都应填充为有电梯（具体操作为选中有无电梯列，按快捷键Ctrl+G定位空值，输入一个空格值为有电梯后，按Ctrl+Enter填充其他的单元格）；2）“玖誉”值应为错误，处理为空格，电梯情况应为有电梯。

3）别墅值有79项，其余项均填充为“否”（方法参见1））；

调整后的结果如下图7：房源概况拆分结果

2.2.2.2其他字段的处理：

总价和单价提取出来变为数值类型：

方法1）分别用Ctrl+F查找替换总价字段中“万”成””，“单价”成“ ”，“元/平米”成“ ”，得到数值；

方法2）用字符串提取函数，总价(万)= =LEFT(F2,FIND("万",F2)-1),单价（元/平米）=MID(H2,FIND("单价",H2)+2,FIND("元",H2)-LEN("单价")-1)；将光标移动到单元格右下方，待光标变成“+”形状，双击填充剩下的单元格；见图8

方法3）快捷键Ctrl+E快速填充法：单价首行单元格直接填写单价H2中数值17228，再选择单元格I2，按下快捷键Ctrl+E，进行快速填充如下图9：

字段 市场热度的处理：方法1）按照数据、分列的步骤，分成三列，用“/”作为分隔符号，再根据字符串提取函数（这里较简单，略），这里取2列关注人数、带看次数，反映市场热度；

方法2）快捷键Ctrl+E快速填充法：单价首行单元格直接填写单价F2中数值613，再选择单元格I2，按下快捷键Ctrl+E，进行快速填充;效果见图10

字段 房源位置的处理：按照数据、分列的步骤，用“-”作为分隔符号，分成两列楼层&建成年代、具体商圈。楼层&建成年代再分开用上述的快捷键Ctrl+E快速填充法，得到下面图11：

由于房源位置字段缺失建成年代，因此，1283个单元格全改为“暂无，处理结果如下：

将分列前不必要的字段：房源标题、房源位置、房源概况、总价、单价等字段隐藏，简化图表；

字段 面积：按下快捷键Ctrl+F，查找替换平米成“ ”空值，该列转换成数值；由于面积（单位平米）太详细，因此用VLOOKUP函数（M2=VLOOKUP(L2,面积范围定义!$A$1:$C$8,2,1)）对面积划分成如下：见下图13

字段：建筑年代：用VLOOKUP函数对其划分成如下，见下图14

处理完的最后的数据效果见下图15：

清洗后的结果见图16：

四、构建模型及数据可视化

下面我们通过Excel2016的数据透视表，对刚才清洗完成的数据做一次简单的数据分析旅行。针对提出的问题：

1、8大城市的市场均价排名情况、老旧房屋数量对比：

图18

图17

结论：8个城市房价单价排名依次是北京、深圳、上海、杭州、武汉、成都、重庆、大连。一线城市高于新一线城市，北京的房价是大连房价4倍多。北京、上海房屋大多建于2010年以前，城市化进程发展很早，房地产相当数量是建于2000年以前；新一线城市中重庆、武汉、成都由于承接一线城市产业转移，吸纳大量新的人口，城市化进程加速，很大部分房产建于2010年后。

1. 8大城市主力户型占比分布是怎样的及其原因？

8大城市的面积户型：各大城市最多户型几乎都是70-90平，仅武汉例外90-120平最多，这部分由于年轻人结婚作为刚需房需求可以理解；再大户型如120-150平、150-200平、200平以上的改善型住房数量就不那么多了，毕竟总价高，但这里看到重庆市场较为特殊，这三项占比远高于其他城市：一是房价相对较低，二是重庆人民比较会享受（开商200平的房子相对较多），从下图也可以看出：

8大城市各3000条房源，仅上海1套、成都2套，杭州16套、重庆却有54套别墅，其他城市北京、深圳、武汉、大连均无在售别墅。

3、重庆最热门十大商圈板块是哪些？

从图表可以看出，人气最高的十大板块是照母山、汽博、人和、大学城、南滨路、龙头寺、北滨路、江北嘴、大坪、南坪；前10大热门板块渝北江北区就占了6个，重庆一路向北的发展战略效果已显现，人群的聚集，更多的基建房产落地，表明渝北、江北发展势头良好，当然老城区的南岸区(南滨路、南坪)、渝中区（大坪）发展的早，根基很厚，人口密度很大，配套完善，房价依然很高；位于文化老城沙坪坝区有唯一一个商圈——大学城，由于学区房概念热炒，周边十几所大学、数量众多的中学、小学林立，优质教育资源集聚，也迅速抬高了房价。