大數(shù)據(jù)采集與預(yù)處理技術(shù)*

*項(xiàng)目二圖書信息數(shù)據(jù)采集與預(yù)處理序號(hào)軟件配置要求1scrapy最新版本2redis最新版本一、項(xiàng)目目標(biāo)：1、完成圖書信息數(shù)據(jù)分布式采集環(huán)境安裝配置；2、完成圖書信息數(shù)據(jù)采集程序設(shè)計(jì)和數(shù)據(jù)采集；3、完成圖書信息數(shù)據(jù)預(yù)處理清洗。4、完成圖書信息數(shù)據(jù)文本標(biāo)注。二、環(huán)境要求：任務(wù)二圖書信息數(shù)據(jù)預(yù)處理一、任務(wù)目標(biāo)1、理解常見的數(shù)據(jù)清洗方法和原理，根據(jù)模型訓(xùn)練要求，完成圖書信息數(shù)據(jù)的缺失值、異常值和重復(fù)值處理。2、理解描述性變量轉(zhuǎn)換為數(shù)值型數(shù)據(jù)的方法和用處，并完成圖書信息數(shù)據(jù)的描述性變量轉(zhuǎn)換。3、掌握數(shù)據(jù)標(biāo)準(zhǔn)化方法，了解文本數(shù)據(jù)訓(xùn)練集、測試集劃分，完成圖書信息數(shù)據(jù)的標(biāo)準(zhǔn)化和測試集劃分。#1、讀取爬取的圖書數(shù)據(jù)并處理重復(fù)值importmysql.connectorimportpandasaspd

#創(chuàng)建數(shù)據(jù)庫連接，注意鏈接參數(shù)需要根據(jù)實(shí)際自行替換conn=mysql.connector.connect(host="",user="root",password="P@ssw0rd",database="doubandb")#使用SQL查詢讀取數(shù)據(jù)query="SELECT*FROMitems"df=pd.read_sql(query,conn)#關(guān)閉連接conn.close()#重復(fù)值處理，如果書名相同就刪除df_drop_duplicated=df.drop_duplicates(subset=['book_name'])print(df_drop_duplicated.shape)#①對(duì)“子連接”、“縮略圖URL”、“出版時(shí)間”缺失值不做處理；#②對(duì)“書名”缺失的記錄做刪除處理；df_dropna=df_drop_duplicated.dropna(axis=0,how="any",subset=["book_name"])#③對(duì)“評(píng)論數(shù)量”、“評(píng)分”做線性插值處理；#線性插值,先由評(píng)論數(shù)推出評(píng)分fromerpolateimportinterp1dimportnumpyasnpimportre#去除book_score空值行df_new=df_dropna[(df_dropna['book_score']!="")&(df_dropna['book_grade']!="")]#可以認(rèn)為評(píng)論數(shù)book_grade是自變量x=np.array(df_new["book_grade"].tolist())#提取數(shù)值，使用正則表達(dá)式extract_numbers=np.vectorize(lambdastr:int(re.findall(r'\d+',str)[0]))grade_values=extract_numbers(x)#認(rèn)為書評(píng)分book_score是因變量,注意數(shù)據(jù)類型轉(zhuǎn)換y=np.array(df_new["book_score"].tolist())score_values=np.char.strip(y).astype(float)#線性插值擬合x,yLinearInsValue=interp1d(grade_values,score_values,fill_value="extrapolate")#針對(duì)book_score值為空的記錄找出其book_grade值foriinrange(len(df_dropna)):if(df_dropna.iloc[i]['book_score']=='')andpd.notna(df_dropna.iloc[i]['book_grade']):grade_value=int(re.findall(r'\d+',df.iloc[i]['book_grade'])[0]) #將book_grade轉(zhuǎn)換為數(shù)值s_value=LinearInsValue([grade_value]) #線性擬合score值print("grade_value:{}vsscore_value:{}".format(grade_value,s_value))df_dropna.iloc[i,df_dropna.columns.get_loc('book_score')]=s_value[0] #將book_score缺失的值用擬合值填充#多項(xiàng)式插值,再由評(píng)分推出評(píng)論數(shù)fromerpolateimportlagrange#用前60條數(shù)據(jù)擬合拉格朗日插多項(xiàng)式,使用已有的LargeInsValue=lagrange(score_values[0:60],grade_values[0:60])#針對(duì)book_score值為空的記錄找出其book_grade值foriinrange(len(df_dropna)):ifpd.isna(df_dropna.iloc[i]['book_grade'])and(df_dropna['book_score']!=""):s_value=df.iloc[i]['book_score'].strip().astype(float)g_value=LargeInsValue([s_value])#注意獲取數(shù)據(jù)索引位置表達(dá)方法

df_dropna.iloc[i,df_dropna.columns.get_loc('book_grade')]=g_value[0]#④對(duì)“作者”、“作者簡介”做直接關(guān)鍵字查找已經(jīng)存在的記錄值做填充處理foriinrange(len(df_dropna)):ifdf_dropna.iloc[i]['book_author']=='none':ifdf_dropna.iloc[i]['book_author_introduction']!='none':#查找簡介相同、作者不為空的第1個(gè)”作者“姓名

book_author=df_dropna.loc[df_dropna['book_author_introduction']==df_dropna.iloc[i]['book_author_introduction']&(df_dropna['book_author']!="none"),'book_author'][0]df_dropna.iloc[i,df_dropna.columns.get_loc('book_author')]=book_authorifbook_authorisnotNoneelse"none"#再由“作者”填充“作者簡介”，參照上面代碼進(jìn)行處理#⑤對(duì)“書的標(biāo)簽”、“簡介”都用固定值“無”填充處理。foriinrange(len(df_dropna)):ifdf_dropna.iloc[i]["book_labels"].find("none")!=-1:df_dropna.iloc[i,df_dropna.columns.get_loc("book_labels")]="無"df_dropna["book_introduction"]=df_dropna["book_introduction"].fillna("無")#①對(duì)“子連接”、“縮略圖URL”、“出版時(shí)間”、“作者簡介”異常值不做處理；#②對(duì)“書名”、“作者”、“書的標(biāo)簽”、“簡介”有任意是“none”的刪除；df_drop1=df_dropna[(df_dropna['book_name']!="none")&(df_dropna['book_author']!="none")]df_drop2=df_drop1[(df_drop1['book_labels']!="none")&(df_drop1['book_introduction']!="none")]#③對(duì)“評(píng)論數(shù)量”、“評(píng)分”使用三西格瑪算法檢測，檢測出的異常值刪除行記錄；#先做數(shù)值轉(zhuǎn)換grades=np.array(df_drop2["book_grade"].tolist())grade_ex=extract_numbers(x)df_drop2["book_grade"]=pd.Series(grade_ex)y=np.array(df_drop2["book_score"].tolist())score_values=np.char.strip(y).astype(float)df_drop2["book_score"]=pd.Series(score_values)columns_list=["book_grade","book_score"]foritemincolumns_list:IQR=df_drop2[item].quantile(0.75)-df_drop2[item].quantile(0.25)#定義下界

q_abnormal_L=df_drop2[item]<df_drop2[item].quantile(0.25)-1.5*IQR#定義上界

q_abnormal_U=df_drop2[item]>df_drop2[item].quantile(0.75)+1.5*IQRdf_drop2=df_drop2[(df_drop2[item]>df_drop2[item].quantile(0.25)-1.5*IQR)&(df_drop2[item]<df_drop2[item].quantile(0.75)+1.5*IQR)]#④再使用箱型圖算法檢測“評(píng)論數(shù)量”、“評(píng)分”，檢測出的異常值刪除行記錄。neg_list=['book_grade','book_score']#當(dāng)數(shù)值超出這個(gè)距離，可以認(rèn)為它是異常值foriteminneg_list:df_drop2[item+'_zscore']=(df_drop2[item]-df_drop2[item].mean())/df_drop2[item].std()df_drop2=df_drop2[abs(df_drop2[item+'_zscore'])<3]#描述性變量轉(zhuǎn)換，圖書作者authors=np.array(df_drop2["book_author"].tolist())author_series=pd.Series(authors)#統(tǒng)一加上前綴col，默認(rèn)連接符是_authors_dum=(pd.get_dummies(author_series,prefix="col"))#調(diào)用train_test_split函數(shù)進(jìn)行訓(xùn)練集、測試集劃分fromsklearn.model_selectionimporttrain_test_split#去除book_labels列,以及其它非必要的特征列X=df_drop2.drop(['book_labels','id','book_url','book_grade_zscore','book_score_zscore'],axis=1)#book_labels作為標(biāo)簽列