【Python基礎(chǔ)系列】常見的數(shù)據(jù)預(yù)處理方法（附代碼）

import pandas as pddata = pd.read_csv(r'../filename.csv')	#讀取csv文件data = pd.read_table(r'../filename.txt')	#讀取txt文件data = pd.read_excel(r'../filename.xlsx')  #讀取excel文件
#  獲取數(shù)據(jù)庫中的數(shù)據(jù)import pymysqlconn = pymysql.connect(host='localhost',user='root',passwd='12345',db='mydb')	#連接數(shù)據(jù)庫,注意修改成要連的數(shù)據(jù)庫信息cur = conn.cursor()	#創(chuàng)建游標(biāo)cur.execute("select * from train_data limit 100")	#train_data是要讀取的數(shù)據(jù)名data = cur.fetchall()	#獲取數(shù)據(jù)cols = cur.description	#獲取列名conn.commit()	#執(zhí)行cur.close()	#關(guān)閉游標(biāo)conn.close()	#關(guān)閉數(shù)據(jù)庫連接col = []for i in cols:	col.append(i[0])data = list(map(list,data))data = pd.DataFrame(data,columns=col)

#合并多個csv文件成一個文件import glob
#合并def hebing():    csv_list = glob.glob('*.csv') #查看同文件夾下的csv文件數(shù)    print(u'共發(fā)現(xiàn)%s個CSV文件'% len(csv_list))    print(u'正在處理............')    for i in csv_list: #循環(huán)讀取同文件夾下的csv文件        fr = open(i,'rb').read()        with open('result.csv','ab') as f: #將結(jié)果保存為result.csv            f.write(fr)    print(u'合并完畢！')
#去重    def quchong(file):        df = pd.read_csv(file,header=0)            datalist = df.drop_duplicates()            datalist.to_csv(file)
if __name__ == '__main__':        hebing()        quchong("result.csv.csv")

##csv比較大，打不開，將其切分成一個個小文件，看數(shù)據(jù)形式f = open('NEW_Data.csv','r') #打開大文件i = 0 #設(shè)置計(jì)數(shù)器
#這里1234567表示文件行數(shù)，如果不知道行數(shù)可用每行長度等其他條件來判斷while i<1234567 :     with open('newfile'+str(i),'w') as f1:        for j in range(0,10000) : #這里設(shè)置每個子文件的大小            if i < 1234567: #這里判斷是否已結(jié)束，否則最后可能報錯                f1.writelines(f.readline())                i = i+1            else:                break

data.head() #顯示前五行數(shù)據(jù)data.tail() #顯示末尾五行數(shù)據(jù)data.info() #查看各字段的信息data.shape #查看數(shù)據(jù)集有幾行幾列,data.shape[0]是行數(shù),data.shape[1]是列數(shù)data.describe() #查看數(shù)據(jù)的大體情況，均值，最值，分位數(shù)值...data.columns.tolist()   #得到列名的list

print(data.isnull().sum())  #統(tǒng)計(jì)每列有幾個缺失值missing_col = data.columns[data.isnull().any()].tolist() #找出存在缺失值的列
import numpy as np#統(tǒng)計(jì)每個變量的缺失值占比def CountNA(data):    cols = data.columns.tolist()    #cols為data的所有列名    n_df = data.shape[0]    #n_df為數(shù)據(jù)的行數(shù)    for col in cols:        missing = np.count_nonzero(data[col].isnull().values)  #col列中存在的缺失值個數(shù)        mis_perc = float(missing) / n_df * 100        print("{col}的缺失比例是{miss}%".format(col=col,miss=mis_perc))

data.dropna(axis=0,how="any",inplace=True)  #axis=0代表'行','any'代表任何空值行,若是'all'則代表所有值都為空時，才刪除該行data.dropna(axis=0,inplace=True)  #刪除帶有空值的行data.dropna(axis=1,inplace=True)  #刪除帶有空值的列

data = data.fillna(0)   #缺失值全部用0插補(bǔ)data['col_name'] = data['col_name'].fillna('UNKNOWN')  #某列缺失值用固定值插補(bǔ)

freq_port = data.col_name.dropna().mode()[0]  # mode返回出現(xiàn)最多的數(shù)據(jù),col_name為列名data['col_name'] = data['col_name'].fillna(freq_port)   #采用出現(xiàn)最頻繁的值插補(bǔ)

data['col_name'].fillna(data['col_name'].dropna().median(),inplace=True)  #中位數(shù)插補(bǔ)，適用于偏態(tài)分布或者有離群點(diǎn)的分布data['col_name'].fillna(data['col_name'].dropna().mean(),inplace=True)    #均值插補(bǔ)，適用于正態(tài)分布

data['col_name'] = data['col_name'].fillna(method='pad')    #用前一個數(shù)據(jù)填充data['col_name'] = data['col_name'].fillna(method='bfill')  #用后一個數(shù)據(jù)填充

from scipy.interpolate import lagrange#自定義列向量插值函數(shù),s為列向量,n為被插值的位置,k為取前后的數(shù)據(jù)個數(shù)，默認(rèn)5def ployinterp_columns(s, n, k=5):    y = s[list(range(n-k,n)) + list(range(n+1,n+1+k))]  #取數(shù)    y = y[y.notnull()]  #剔除空值    return lagrange(y.index, list(y))(n)    #插值并返回插值結(jié)果
#逐個元素判斷是否需要插值for i in data.columns:    for j in range(len(data)):        if (data[i].isnull())[j]:   #如果為空即插值            data[i][j] = ployinterp_columns(data[i],j)

neg_list = ['col_name_1','col_name_2','col_name_3']for item in neg_list:    neg_item = data[item] < 0    print(item + '小于0的有' + str(neg_item.sum())+'個')
#刪除小于0的記錄for item in neg_list:    data = data[(data[item]>=0)]

#當(dāng)數(shù)值超出這個距離，可以認(rèn)為它是異常值for item in neg_list:    data[item + '_zscore'] = (data[item] - data[item].mean()) / data[item].std()    z_abnormal = abs(data[item + '_zscore']) > 3    print(item + '中有' + str(z_abnormal.sum())+'個異常值')

#IQR(差值) = U(上四分位數(shù)) - L(下四分位數(shù))#上界 = U + 1.5IQR#下界 = L-1.5IQRfor item in neg_list:    IQR = data[item].quantile(0.75) - data[item].quantile(0.25)    q_abnormal_L = data[item] < data[item].quantile(0.25) - 1.5*IQR    q_abnormal_U = data[item] > data[item].quantile(0.75) + 1.5*IQR    print(item + '中有' + str(q_abnormal_L.sum() + q_abnormal_U.sum())+'個異常值')

#尋找描述變量，并將其存儲到cat_vars這個list中去cat_vars = []print('\n描述變量有:')cols = data.columns.tolist()for col in cols:    if data[col].dtype == 'object':        print(col)        cat_vars.append(col)···
##若變量是有序的##     print('\n開始轉(zhuǎn)換描述變量...')from sklearn import preprocessingle = preprocessing.LabelEncoder()#將描述變量自動轉(zhuǎn)換為數(shù)值型變量，并將轉(zhuǎn)換后的數(shù)據(jù)附加到原始數(shù)據(jù)上for col in cat_vars:    tran = le.fit_transform(data[col].tolist())    tran_df = pd.DataFrame(tran,columns=['num_'+col])    print('{col}經(jīng)過轉(zhuǎn)化為{num_col}'.format(col=col,num_col='num_'+col))    data = pd.concat([data, tran_df], axis=1)    del data[col]	#刪除原來的列

##若變量是無序變量## #值得注意的是one-hot可能引發(fā)維度爆炸for col in cat_vars:    onehot_tran = pd.get_dummies(data.col)    data = data.join(onehot_tran)	#將one-hot后的數(shù)據(jù)添加到data中    del data[col]	#刪除原來的列

法一、直接調(diào)用train_test_split函數(shù)from sklearn.model_selection import train_test_splitX = data.drop('目標(biāo)列',1)	#X是特征列y = data['目標(biāo)列']	#y是目標(biāo)列X_train,X_test,y_train,y_test = train_test_split(X,y,test_size=0.3)	

#隨機(jī)選數(shù)據(jù)作為測試集test_data = data.sample(frac=0.3,replace=False,random_state=123,axis=0)#frac是抽取30%的數(shù)據(jù)，replace是否為有放回抽樣，取replace=True時為有放回抽樣，axis=0是抽取行、為1時抽取列#在data中除去test_data，剩余數(shù)據(jù)為訓(xùn)練集train_data = (data.append(test_data)).drop_duplicates(keep=False)X_train = train_data.drop('目標(biāo)列',1)X_test = test_data.drop('目標(biāo)列',1)y_train = train_data['目標(biāo)列']y_test = test_data['目標(biāo)列']

from sklearn.preprocessing import MinMaxScaler
x_scaler = MinMaxScaler()y_scaler = MinMaxScaler()
#特征歸一化x_train_sca = x_scaler.fit_transform(X_train)x_test_sca = x_scaler.transform(X_test)y_train_sca = y_scaler.fit_transform(pd.DataFrame(y_train))

from sklearn.preprocessing import StandardScaler
#一般把train和test集放在一起做標(biāo)準(zhǔn)化，或者在train集上做標(biāo)準(zhǔn)化后，用同樣的標(biāo)準(zhǔn)化器去標(biāo)準(zhǔn)化test集scaler = StandardScaler()train = scaler.fit_transform(train)test = scaler.transform(test)