[MySQL]獵聘網數據分析職位數據清洗
通過python編寫爬蟲，爬取了獵聘網關鍵詞「數據分析」的全國範圍內1月內發布的企業職位。
本次分析源數據的職位發布日期：2017年9月19日-2017年10月19日
爬蟲介紹：爬取獵聘網職位信息
源數據：職位數據
清洗完成後，用於分析的數據2320條
數據清洗Query代碼：datacleaning.sql
數據清洗結果：用於分析的數據
獵聘網職位爬取及分析系列文章已全部完成
系列文章一：[requests,pyquery]爬取獵聘網職位信息
系列文章三：[python,pandas]數據分析求職指南——獵聘網數據分析職位解析
數據清洗
觀察原始數據（共27633條）情況，並使用MySQL對爬取的原始數據進行清洗。
原始數據欄位如下所示：
- JobTitle VARCHAR(255) ：職位名稱
- company VARCHAR(255)：公司名稱
- salary VARCHAR(255)：薪酬
- position VARCHAR(255)：工作地
- PubTime VARCHAR(255)：發布時間
- qualification VARCHAR(255)：職位要求，包含學歷、工作經驗、語言、年齡
- tag_list VARCHAR(255)：職位標籤，tag1,tag2,...
- description TEXT：職位描述，詳細介紹職位工作內容，任職要求等信息
- industry VARCHAR(255)：行業
- industry_detail VARCHAR(255)：獵聘更加細化的行業區分
- companySize VARCHAR(255)：公司規模，人員數
- is_end INT(255)：職位是否已結束，未結束為0，結束為1
薪酬數據
salary格式如4-5萬 72小時反饋、面議 24小時反饋，包含職位反饋時間信息
- 新建欄位min_salary INT(255)、max_salary INT(255)、average_salary FLOAT
- 從salary欄位中提取min_salary、max_salary，並有average_salary = avg(min_salary,max_salary)
- 薪酬為面議時，min_salary = max_salary = average_salary = 0
工作地數據
position格式為國家 、 城市 、 城市-區縣，如：新加坡、北京、廣州-天河區，將-前後兩部分分開
- 新建欄位position1 VARCHAR(255)、position2 VARCHAR(255)
- 若position包含-，前一部分為position1，後一部分為position2
- 若position不包含-，則position1 = position，position2 = null
發布時間
PubTime格式為XXXX年XX月XX日，是字元串形式
- 新建欄位pdate date
- 提取pubtime中年月日，轉化成 pdate = %Y-%m-%d
異常及空值處理
- 因獵聘網職位詳情頁源碼格式問題，若職位未給出公司規模數據，則爬取得到的公司規模欄位實際為公司地址，令出現問題的行comAddress = companySize，並刪除companySize欄位內容
- industry_detail欄位為空時，令industry_detail = industry
- comAddress欄位為空時，令comAddress = position
- companySize、tag_list、description欄位為空，填充null
用於分析的數據
根據分析目的篩選用於分析的數據，獲得2320條
- 因獵聘網搜索較為模糊，結果中包含會計助理、項目運營等無關職位，對JobTitle進行篩選：包含數據分析、大數據、數據運營、data等
- 只分析未結束職位：is_end = 0
- 去重
源代碼
--提取salary，獲得min-max-average salaryn--新建列nALTER TABLE DATA ADD (nmin_salary FLOAT(10),nmax_salary FLOAT(10),naverage_salary FLOAT(10)n) ;n--when `salary` like %面議%,min/max/average=0nUPDATE DATA SET `min_salary`=0, `max_salary`=0, `average_salary`=0nWHERE `salary` LIKE %面議% ;n--salary不是面談時，salary格式min-max萬n--min_salary=minnUPDATE DATA SET `min_salary`= SUBSTRING_INDEX(`salary`,-,1)nWHERE `salary` LIKE %-%萬%;n--max_Salary=maxnUPDATE DATA SET `max_salary`= (SUBSTRING(SUBSTRING_INDEX(`salary`,萬,1),LOCATE(-,SUBSTRING_INDEX(`salary`,萬,1))+1))nWHERE `salary` LIKE %-%萬%;n--average_salary = (min+max)/2nUPDATE DATA SET `average_salary`= (min_salary+max_salary)/2nWHERE `salary` LIKE %-%萬%;nn--整理position，XX-XX或XXnALTER TABLE DATA ADD (nposition1 VARCHAR(255),nposition2 VARCHAR(255)n);nUPDATE DATA SET position1 = positionnWHERE position NOT LIKE %-%;nUPDATE DATA SET position2 = nullnWHERE position NOT LIKE %-%;nUPDATE DATA SET position1 = SUBSTRING_INDEX(`position`,-,1)nWHERE position LIKE %-%;nUPDATE DATA SET position2 = SUBSTRING(`position`,LOCATE(-,`position`)+1)nWHERE position LIKE %-%;nn--發布時間pubtime：str->date to newfield：pubdatenSELECT @y :=LEFT(data.`PubTime`,4) FROM `data`;nSELECT @m :=mid(pubtime,6,2) FROM `data`;nSELECT @d :=mid(pubtime,9,2) FROM `data`;nUPDATE `data` SET `pubdate`=str_to_Date(nconcat(@y,-,@m,-,@d),%Y-%m-%d);nn--整理qualificationn--學歷nSELECT substring_index(qualification, ,1)nFROM `data`;nUPDATE TABLE `data` SET education = nsubstring_index(qualification, ,1)n--工作經驗nUPDATE `data` SET n`workexperience` = substring(substring_index(qualification, ,2),char_length(substring_index(qualification, ,1))+2) ;n--年齡要求nUPDATE `DATA` SET n`age` = substring_index(qualification, ,-1) ;n--工作語言nUPDATE `data` SET workLANGUAGE = nmid(nqualification,nchar_length(substring_index(qualification, ,2))+2,nchar_length(substring(`qualification`,char_length(substring_index(qualification, ,2))+2))-char_length(`age`)-1n);nn--找出comsize內容實際為address的，將其內容賦予comAddr並刪除comsize內的內容nUPDATE DATA SET `comAddress`=`companySize`nWHERE (`companySize` NOT LIKE %-%人% AND companySize NOT LIKE %人以上) ;nUPDATE DATA SET `companySize`= nWHERE (`companySize` NOT LIKE %-%人% AND companySize NOT LIKE %人以上) ;nn--填充空缺內容n--industry_detail為空時，用industry的內容填充nUPDATE DATA SET industry_detail = industry nWHERE `industry_detail`= ;n--addr內容為空時用position內容填充nUPDATE DATA SET `comAddress` = `position`nWHERE `comAddress`= ;n--size內容為空時，填寫nullnUPDATE DATA SET `companySize` = nullnWHERE `companySize`= ;n--tag_list內容為空時，填寫nullnUPDATE DATA SET `tag_list` = nullnWHERE `tag_list`= ;n--description內容為空時，填寫nullnUPDATE DATA SET `description` = nullnWHERE `description`= ;nn--查詢用於分析的數據nSELECT jobtitle,company,min_salary,max_salary,average_salary,pdate,ntag_list,description,industry,companysize,comaddress,position1,neducation,workexperience,worklanguage,age,is_endnFROM `data`nWHERE (njobtitle LIKE %數據%分析% nOR jobtitle LIKE%大數據% nOR jobtitle LIKE %大數據% nOR jobtitle LIKE %數據%運營%nOR jobtitle LIKE %data%n)nAND (is_end = 0);n
推薦閱讀：