服务器上导出excel 文件扩展名与文件格式不匹配 本地正常_智联Python相关职位的数据分析及可视化-Excel篇...

碎碎念：

数据分析、数据挖掘、机器学习、神经网络、深度学习，这些五花八门的名词貌似逼格满满， 初入门的新手(譬如我就是)很容易被高大上的词汇吸引但又不知如何静下心来入门和学习、晕头转向。 此篇是利用Excel进行智联招聘职位分析的第一篇，让我门看看如何分析，能分析出哪些有意思的东西。 还有一篇姊妹篇，用同样的数据进行分析、不过用的是Python中的Pandas和Matplotlib而不是Excel。敬请期待！

---

数据来源：

Python爬虫爬取了智联招聘关键词：【Python】、全国30个主要城市的搜索结果。数据存储到本地MySql和MongoDB服务器上。从服务器上导出文件、用Python的Pandas库简单处理后保存为本地excel。A.使用的软件：Excel-2011，Python-3.6，Mysql-5.7(macOS系统)

B.前期准备：

将Mysql里的数据导出到本地JSON文件，用Python的Pandas库读取JSON文件并进行简单的格式处理之后用to_excel() 方法保存至本地.xls的Excel文件。

#在终端输入ipython --pylab进入ipython的界面import numpy as npimport pandas as pdimport matplotlib.pyplot as pltdf = pd.read_json('/Users/zhaoluyang/Desktop/Python_全国JSON.json')df.info()

通过http://df.info()可以看见：df是一个pandas里的DataFrame格式的文件，类似于excel中的表格，本质是一个方便处理的二维数组。 整个df中有18326行数据、15列，其中ZL_Job_id一列是mysql中我添加的id标记。

Int64Index: 18326 entries, 0 to 18325表示pandas给我的数据自动添加了int64类型的索引，从0-18325. 再通过df[['ZL_Job_id','公司名称']].head()看看前5行的信息(因为15列不方便阅读，所以只看前两列的)

可以看到ZL_Job_id序号很混乱，而且左边自动添加的索引不匹配。那么现在问题来了。我需要给 ZL_Job_id一列重新递增排序，然后用此列替换掉左边的默认索引。

我就直接摆代码了：

df.index = df['ZL_Job_id']del(df['ZL_Job_id'])df1 = df.sort_index()df = df1

好了，现在可以导出格式比较规整的数据了。

df.to_excel('/Users/zhaoluyang/Desktop/Python_nation.xls')

打开文件看看导出的是否正确？看了一下，应该是没问题的！

前期准备：

1.分析的目的：

A.希望通过对全国30个主要城市招聘Python相关的职位，来看看最近很火的Python到底待遇如何？工资水平怎么样？ 对工作经验和学历有什么要求？需要掌握的岗位技能有哪些？各个城市比较下、平均下？B.其实想怎么分析，完全是随个人喜好的，譬如还可以分析下全国所有的公司、看看哪个招聘的数量最多？ (还可以画个逼格满满的基于地理位置的密度图)分析下公司给开出的平均薪水，看看哪个公司最慷慨？ 将岗位职责的描述用python分词处理、再统计词频看看哪些名称经常出现、再生成个词云图玩玩？C.本次分析主要通过Excel，大概的过程讲解和流程梳理，最后通过Excel里的图表进行数据呈现。

2.数据清洗：

看着excel，有没有发现全是文字？包括职位月薪也是很不规则的？6000-8000元/月、面议..我想要的是纯粹的数字如：6000、8000。工作地点也是五花八门的，明明都是苏州，还分了苏州-工业园区，苏州-姑苏区，这样子待会我统计城市的时候是不是很不友好？！ 哎，都是坑！为此，需要做数据清洗的工作：A.将【职位月薪】列细化，新增3列【bottom】【top】【平均月薪】。【bottom】表示最低月薪，【top】表示最高月薪，【平均月薪】则为中间值。譬如原来职位月薪是6000-8000元/月的，bottmm=6000，top=8000，平均月薪=7000；原来职位月薪是面议的，bottom=top=平均月薪=面议。B.将【工作地点】列细化，新增一列【工作城市】。 譬如不论工作地点是苏州还是苏州-工业园区，苏州-姑苏区，【工作城市】一列统统=苏州。

要想清洗以上数据、用excel的公式还是比较方便的，我单独把【职位月薪】和【工作地点】两列拿了出来，构造了新的测试表如下：

“平均月薪”列很好计算，主要bottom和top都完成以后直接(top+bottom)/2就好，暂时忽略此列，让我们看看怎么得到【bottom】列？如果【职位月薪】=面议，那么bottom也=面议，否则bottom的值在“-”的前半部分，好了，一个嵌套的IF函数就可以解决。让我们在C3单元格写如下的表达式：

【bottom】：

=IF(A3="面议";"面议";LEFT(A3;FIND("-";A3;1)-1))

函数表达式的语法和对应解释：

IF(表达式；返回结果a;返回结果b)；

如果表达式成立，则返回结果a，否则返回结果b。

LEFT(text；字符个数num)；

返回一段text文本中，从左起，指定个数的字符。

譬如text=“数据分析工程师”LEFT(text;4)的结果：“数据分析”

FIND(text；目标text；起始位数)

查找一段text在目标文本text中的起始位置。

譬如text=“工资”，目标text=“数据分析工程师的工资有多少”，FIND(text；目标text；5)

从目标text字符串中的第5位开始查找，查找“工资”在目标text出现的位置，结果：9.

让我们回到表达式IF(A3="面议";"面议";LEFT(A3;FIND("-";A3;1)-1))，表达式的自然语言大意是：如果表达式A3="面议"成立，那么返回结果a，对应的就是字符“面议”；不成立，则返回结果b。结果b=表达式LEFT(A3;FIND("-";A3;1)-1)，可以返回A3单元格中文本从左起到第【FIND("-";A3;1)-1】位的字符,FIND("-";A3;1)就是查找“-”字符在A3中文本位置的数值，减1表示前一位。

写了这么多感觉很啰嗦，反而容易让人看晕，所以就不详细解释各个公式的用法了！其实很简单，用到的时候自己在excel里试试，百度一下各个函数具体的用法，练两次就会用了！！！

【top】列的公式：

=IF(A3="面议";"面议";MID(A3;FIND("-";A3;1)+1;LEN(A3)-LEN(B3)-4))

【平均月薪】列的公式：

=IF(A3="面议";"面议";(B3+C3)/2)

【工作地点】列的公式：

=IF(ISERROR(FIND("-";E3;1));E3;LEFT(E3;FIND("-";E3;1)-1))

最后，将excel公式套到原文件中，构造出可用的数据，至此数据清洗的工作暂时告一段落了！

分析过程和结果：

打开Excel表，除了具体的公司和职位名称以外，我们还比较关心几个关键词：平均月薪、工作经验、工作城市、最低学历和岗位职责描述，这里岗位职责描述以后会用python分词做词云图，所以目前筛选出【平均月薪】、【工作经验】、【工作城市】、【最低学历】这四个标签，这些标签可以两两组合产生各种数据。譬如我想知道各个城市的招聘数量分布情况，会不会大部分的工作机会都集中在北上广深？是不是北上广深的平均工资也高于其他城市？我想知道Python这个关键词的18000多条招聘数据中对学历的要求和对工作经验的要求，以及它们分别占比多少？我还想知道平均月薪和工作经验的关系？最低学历和平均月薪的关系？---------好，让我们一个个来。

1.各个城市职位数量及分布

根据猜想北上广深杭一定占据了Pyhton这个关键词下大部分的工作机会，会不会符合28定律？20%的城市占据了80%的岗位？有可能！用Eecel数据透视表筛选出【工作城市】作为行标签、【职位链接】为计数字段，然后行标签中将无关的杂项城市和found no element字段删除，筛选出有效职位链接18170条。

用柱形图和饼图作图：

可见果然Python这个关键词下的职位，北、上、深、杭、广占据了不小的比重！这5个城市占据了全国68%的职位数量！令人意外的是北京的工作机会领先第二名上海不少，果然是帝都！杭州表现亮眼，超越了广州，这个和阿里巴巴对杭州互联网文化的贡献应该有很大关系！北上广深航+成都、南京、郑州，这8个城市占据了中国80%的工作机会！剩下的22个城市合起来只占据了20%

2.工作经验-职位数量及分布

Python虽然是一名比较老的语言，但是在人们的印象中火起来也就最近几年，Python相关的工作对于【工作经验】是怎样要求的呢？让我们来看看！数据筛选的时候过滤掉了一些很少量的标签譬如“5年以上”，“2年以上”，“1-2年”，“1年以上”等，这些标签下职位的数量都在10以内，不太具备统计意义，筛选后共计18215个职位。

筛选字段数据透视以后总共得到18215条职位。从直方图里可以明显看出工作机会集中与“不限”、1-3年、3-5年，看来即使是初入门者，大家的机会也还是有不少的！其中“不限”占比34%，1-3年占比27%，再加上1年以下、无经验的，汇总可发现工作经验要求3年以内的占比64%！果不其然！

3.工作经验-平均月薪

这个嘛，大家闭着眼都能想到！肯定是工作经验越久的拿钱越多了！再猜猜？无经验的和5-10年经验的收入差距有多大？这个，嘿嘿就不好猜了！！

由于样本数量原因“1年以下”、“3年以上”、“10年以上”的数据量较小，可能不那么准确，看看就好！我们看到1-3年工作经验的平均月薪是11831，还不错！但是“1年以下”，“无经验”的平均月薪就没那么好看了！综合一下平均的月薪为14122元！10年以上工作经验的平均月薪达到了恐怖的34891元，是无经验5883的5.9倍！

4.工作城市-平均月薪

对了，刚才说到北上广深占据了全国大部分的工作机会，那么北上广深的平均月薪如何呢？会不会也碾压小城市？让我们来看看！

果然，北上广深杭不仅工作机会多，而且平均月薪也是领先全国的！北京表现抢眼平均月薪达到17096！全国平均月薪达到了14126元！

5.学历-职位数量

由于样本数量的关系，筛选的时候排除了学历为：高中、中专、高职等等标签，并不是我看不起高中生哈！总共得到18068个有效职位。

可见【本科】独占鳌头，占据了55%的市场！【不限】和【大专】也合计占比38%不容小觑！看起来，只要技术过硬，学历从来都不是问题！！！作为对比【硕士】占比6%，【博士】更是少到只有1%，果然稀缺到百里挑一！

6.学历-平均月薪

看看学历和平均月薪的关系：

平均月薪14139元，可以看到学历越高果然工资越高，博士级别的更是碾压，达到了29563元。平均月薪最低的【大专】也超过了10000元！