樣本數(shù)據(jù)處理 課件 ch04數(shù)據(jù)清洗

數(shù)據(jù)清洗新工科建設(shè)之路·數(shù)據(jù)科學(xué)與大數(shù)據(jù)系列樣本數(shù)據(jù)處理第四章01數(shù)據(jù)清洗概述Kettle里沒有單一的數(shù)據(jù)清洗步驟，很多數(shù)據(jù)清洗工作都需要結(jié)合多個(gè)步驟來組合完成。數(shù)據(jù)清洗工作從抽取數(shù)據(jù)時(shí)就開始，在很多輸入步驟中都可以設(shè)置特定的數(shù)據(jù)格式，按照特定的數(shù)據(jù)格式來讀取數(shù)據(jù)。示例代碼是從一張studentinfo表中抽取studentid和score兩個(gè)字段，并按score字段降序排列得到的。Kettle常用的數(shù)據(jù)清洗步驟注意：首先，如果SQL語句太過復(fù)雜，就會(huì)導(dǎo)致以后的維護(hù)工作非常困難；其次，如果數(shù)據(jù)在進(jìn)入Kettle時(shí)已經(jīng)做過清理，那么ETL便不能提供數(shù)據(jù)審計(jì)的功能。因此，是需要原樣讀入數(shù)據(jù)，還是利用SQL在數(shù)據(jù)抽取時(shí)清理，需要用戶在效率和可維護(hù)性之間進(jìn)行平衡。這里主要介紹Kettle在轉(zhuǎn)換（Transform）目錄下提供的數(shù)據(jù)清洗步驟。這是一個(gè)功能豐富的步驟，它提供了很多預(yù)定義的函數(shù)來處理輸入字段，并且隨著版本的更新還在不斷增加。計(jì)算器從字面上看很簡(jiǎn)單，只有了解到這個(gè)步驟可以支持正則表達(dá)式時(shí)，才會(huì)真正感受到它的強(qiáng)大之處。字符串替換Kettle常用的數(shù)據(jù)清洗步驟該步驟提供了很多常規(guī)的字符操作，如大小寫轉(zhuǎn)換，字符填充、移除空白字符等。字段選擇剪切字符串。字符串剪切這3個(gè)步驟通過使用分隔符來拆分、合并字段。拆分字段、合并字段和拆分字段成多行Kettle常用的數(shù)據(jù)清洗步驟該步驟使用一個(gè)標(biāo)準(zhǔn)的值來替換字段里的其他值。值映射該步驟可以對(duì)字段進(jìn)行選擇、刪除、重命名等操作，還可以更改字段的數(shù)據(jù)類型、長(zhǎng)度和精度等元數(shù)據(jù)。字段選擇這兩個(gè)步驟主要通過指定字段來清除重復(fù)記錄。去除重復(fù)記錄和去除重復(fù)記錄Kettle常用的數(shù)據(jù)清洗步驟字符串清理“DataGrid”的元數(shù)據(jù)如圖4-1所示。字符串清理另外輸入一組示例數(shù)據(jù)，內(nèi)容如圖4-2所示。字符串清理“Stringoperations”的具體設(shè)置如圖4-3所示。字符串清理清理步驟設(shè)置完畢。為了展示結(jié)果，我們使用一個(gè)“MicrosoftExcelOutput”步驟輸出清理后的結(jié)果。"MicrosoftExcelOutput”步驟的設(shè)置方法前面已經(jīng)講過，這里不再贅述。這時(shí)整個(gè)轉(zhuǎn)換流程如圖4-4所示。字符串清理執(zhí)行string_op轉(zhuǎn)換，打開輸出文件，可以看到如圖4-5所示的結(jié)果。字符串清理這時(shí)將這個(gè)步驟添加到“Stringoperations”步驟和“MicrosoftExcelOutput”步驟之間，string_op轉(zhuǎn)換的流程變成如圖4-7所示的流程。字符串清理執(zhí)行轉(zhuǎn)換并打開輸出文件，可以看到如圖4-8所示的結(jié)果。字符串清理“Stringscut”的具體設(shè)置如圖4-9所示。字符串清理組合幾個(gè)步驟后，string_op轉(zhuǎn)換的最終流程如圖4-10所示。字符串清理執(zhí)行上面的轉(zhuǎn)換，系統(tǒng)將得出最終結(jié)果，如圖4-11所示。關(guān)于字段清理，這里簡(jiǎn)單介紹4個(gè)常用步驟：“拆分字段成多行（Splitfiledtorows）"、“拆分字段（SplitFields）”、“合并字段（ConcatFields）”和“字段選擇（Selectvalues）"。字段清理字段清理“Splitfiledtorows”步驟新建一個(gè)轉(zhuǎn)換，命名為field_op，同樣使用“DataGrid”步驟作為輸入，設(shè)計(jì)一個(gè)表格，記錄編號(hào)、省份、城市，如圖4-12所示。字段清理表格設(shè)計(jì)完成后，輸入一組示例數(shù)據(jù)，如圖4-13所示。字段清理這里我們就用這個(gè)正則表達(dá)式來作為分隔符“Splitfieldtorows"的具體設(shè)置如圖4-14所示。字段清理同樣選擇輸出一個(gè)Excel文件，整個(gè)轉(zhuǎn)換的流程如圖4-15所示。字段清理這時(shí)查看結(jié)果，可以發(fā)現(xiàn)數(shù)據(jù)增加了一個(gè)字段“城市New”，具體結(jié)果如圖4-16所示。字段清理“SplitFields”步驟和“ConcatFields”步驟具體設(shè)置如圖4-17所示。字段清理對(duì)該步驟的結(jié)果同樣用Excel輸出。field_op_1轉(zhuǎn)換的流程如圖4-18所示。字段清理執(zhí)行轉(zhuǎn)換，“SplitFields”的輸出結(jié)果如圖4-19所示。字段清理具體設(shè)置如圖4-20所示。字段清理同樣選擇Excel作為輸出步驟，field_op_1轉(zhuǎn)換的流程如圖4-21所示。字段清理執(zhí)行轉(zhuǎn)換，“ConcatFields”的具體結(jié)果如圖4-22所示。字段清理“Selectvalues”步驟元數(shù)據(jù)設(shè)置成如圖4-23所示的結(jié)構(gòu)。字段清理對(duì)“DataGrid”步驟輸入一組示例數(shù)據(jù)，如圖4-24所示。字段清理我們可以在該頁面上添加“Age”字段將其刪除，具體設(shè)置如圖4-25所示。字段清理單擊“Quick

launch”按鈕，這里會(huì)看到如圖4-26所示的結(jié)果。字段清理具體設(shè)置如圖4-27所示。字段清理預(yù)覽該步驟，可以查看到如圖4-28所示的結(jié)果。字段清理具體設(shè)置如圖4-29所示。字段清理預(yù)覽該步驟，可以查看到如圖4-30所示的最終清理結(jié)果。使用參照表清理數(shù)據(jù)在某些場(chǎng)合，我們無法直接從當(dāng)前表中識(shí)別出數(shù)據(jù)的錯(cuò)誤。在某些場(chǎng)合，我們無法直接從當(dāng)前表中識(shí)別出數(shù)據(jù)的錯(cuò)誤。例如，客戶信息表包含客戶的郵編、城市及其他信息，但錄入時(shí)可能出現(xiàn)城市與郵編不一致的情況，這時(shí)就需要訪問外部的一些參照數(shù)據(jù)來檢查和修正這些錯(cuò)誤。外部的參照數(shù)據(jù)里的信息是完整的、標(biāo)準(zhǔn)的，也稱這些參照數(shù)據(jù)為主數(shù)據(jù)。外部的這些主數(shù)據(jù)從何而來？主數(shù)據(jù)一般都有相應(yīng)的公司對(duì)其進(jìn)行銷售和維護(hù)，當(dāng)然也有極少的主數(shù)據(jù)是可以免費(fèi)下載的。使用參照表校驗(yàn)數(shù)據(jù)的準(zhǔn)確性參照表的用途很多，其中最常見的用法就是用參照表來做查詢和校驗(yàn)。使用參照表使數(shù)據(jù)一致不同的系統(tǒng)對(duì)性別的記錄可能都不一樣，有的系統(tǒng)用M表示男，用F表示女；有的系統(tǒng)用數(shù)字0表示男，用1表示女，或者用1表示男，用2表示女；而有的系統(tǒng)則用Male和Female分別表示男和女。對(duì)未知性別的表示也不盡相同，用Unknown、0、NULL、U等都可以表示。使用參照表清理數(shù)據(jù)在Kettle7.1中，校驗(yàn)?zāi)夸浵掠幸韵?個(gè)步驟。（1）數(shù)據(jù)校驗(yàn)（DataValidator）。（2）信用卡校驗(yàn)（CreditCardValidator）。（3）電子郵箱校驗(yàn)（MailValidator）。（4）XML文件校驗(yàn)（XMLValidator）。后3個(gè)步驟都是功能相對(duì)“單一”的步驟。所謂單一，并不是指功能簡(jiǎn)單，而是指針對(duì)性很強(qiáng)。數(shù)據(jù)校驗(yàn)可以給一列設(shè)置多個(gè)約束：有的列可能需要多個(gè)約束，該步驟不會(huì)限制在同一列上設(shè)置幾個(gè)約束。錯(cuò)誤合并：將一行數(shù)據(jù)里的所有錯(cuò)誤通過分隔符連接，合并成一個(gè)字符串，保存到錯(cuò)誤描述字段中。校驗(yàn)數(shù)據(jù)類型：當(dāng)輸入的是日期類型的數(shù)據(jù)或者字符串格式的數(shù)值時(shí)非常有用，可以設(shè)置掩碼，找到非法數(shù)據(jù)。約束條件參數(shù)化：幾乎所有的約束條件都可以參數(shù)化。正則表達(dá)式：支持功能強(qiáng)大且靈活的正則匹配。數(shù)據(jù)校驗(yàn)02數(shù)據(jù)排重現(xiàn)實(shí)世界中的一個(gè)實(shí)體，理論上在數(shù)據(jù)庫或者數(shù)據(jù)倉庫中應(yīng)該只有一條與之對(duì)應(yīng)的記錄。很多原因（如數(shù)據(jù)錄入出錯(cuò)、數(shù)據(jù)不完整、數(shù)據(jù)縮寫，以及多個(gè)數(shù)據(jù)集成過程中不同系統(tǒng)對(duì)數(shù)據(jù)的表示不盡相同）會(huì)導(dǎo)致集成后同一實(shí)體對(duì)應(yīng)多條記錄。在數(shù)據(jù)清洗的過程中，重復(fù)記錄的檢測(cè)與清除是一項(xiàng)非常重要的工作。如何識(shí)別重復(fù)數(shù)據(jù)重復(fù)數(shù)據(jù)分為兩類，一類是完全重復(fù)數(shù)據(jù)，另一類是不完全重復(fù)數(shù)據(jù)。完全重復(fù)數(shù)據(jù)很好理解，就是兩個(gè)數(shù)據(jù)行的數(shù)據(jù)完全一致，這類重復(fù)數(shù)據(jù)很好辨別，也很容易清除。對(duì)于完全重復(fù)數(shù)據(jù)，一個(gè)最簡(jiǎn)單的方式就是對(duì)數(shù)據(jù)集排序，然后通過比較相鄰記錄進(jìn)行合并，Kettle的“Sortrows（記錄排序）”步驟可以用于排序。Kettle有兩個(gè)去除重復(fù)記錄的步驟“Uniquerows（去除重復(fù)記錄）”和“Uniquerows（HashSet）（去除重復(fù)記錄（哈希值））"。前一個(gè)步驟只能針對(duì)有序記錄去重，后一個(gè)步驟不需要。如何識(shí)別重復(fù)數(shù)據(jù)對(duì)于不完全重復(fù)數(shù)據(jù)，檢查可能的重復(fù)記錄需要保證有充足的計(jì)算能力，因?yàn)闄z查一條記錄就需要遍歷整個(gè)數(shù)據(jù)集，也就是說對(duì)整個(gè)數(shù)據(jù)集的檢查需要在所有記錄之間進(jìn)行兩兩匹配，其計(jì)算復(fù)雜度為O（n2）。對(duì)可能重復(fù)記錄的檢測(cè)需要使用模糊匹配的邏輯，它可以計(jì)算字符串的相似度。首先通過模糊匹配找出疑似重復(fù)的數(shù)據(jù)，然后結(jié)合其他參考字段做數(shù)據(jù)排重。相同點(diǎn)“Redirectduplicaterow"：選中該選項(xiàng)后，需要對(duì)該步驟進(jìn)行錯(cuò)誤處理，否則一旦有重復(fù)記錄，系統(tǒng)將會(huì)以出錯(cuò)處理。相同點(diǎn)“Fieldstocompare"：設(shè)置哪些字段參與比較。若留空，則表示整條記錄參與比較。去除完全重復(fù)數(shù)據(jù)選項(xiàng)只在“Uniquerows（HashSet）”步驟中有設(shè)置，選中該選項(xiàng)后，會(huì)附加比較存儲(chǔ)在內(nèi)存中的記錄值，這樣可以防止哈希碰撞沖突?！癈ompareusingstoredrowvalues？”步驟會(huì)將所有記錄的比較字段值存儲(chǔ)在內(nèi)存中?！癠niquerows（HashSet）”選項(xiàng)只在“Uniquerows”步驟中有設(shè)置，選中該選項(xiàng)后，該步驟將對(duì)重復(fù)記錄計(jì)數(shù)，并根據(jù)“Counterfield”設(shè)置的字段名輸出到下一個(gè)步驟。“Addcountertooutput”去除完全重復(fù)數(shù)據(jù)選項(xiàng)只在“Uniquerows”步驟中有設(shè)置，在添加要比較的字段后面，可以設(shè)置“Ignorecase”為“Y”狀態(tài)，從而在比較該字段時(shí)忽略大小寫?！癐gnorecase”步驟要求輸入的數(shù)據(jù)是事先排好序的，因?yàn)樗峭ㄟ^比較相鄰記錄的值來判斷是否重復(fù)的；"Uniquerows（HashSet）”步驟對(duì)記錄的順序沒有要求，它可以在內(nèi)存中操作?！癠niquerows”去除完全重復(fù)數(shù)據(jù)去除不完全重復(fù)數(shù)據(jù)“模糊匹配”步驟的設(shè)置主要分如下兩個(gè)方面。（1）常規(guī)設(shè)置：這里要設(shè)置一個(gè)查詢數(shù)據(jù)流的步驟（Lookupstep）和字段（Lookupfield），以及一個(gè)主數(shù)據(jù)流的字段（Mainstreamfield），此外需要配置模糊匹配時(shí)所使用的算法（Algorithm）。（2）字段設(shè)置：這里設(shè)置的字段都是針對(duì)輸出的，可以設(shè)置匹配字段名（Matchfield）、相似度字段名（Valuefield），以及匹配記錄的其他相關(guān)字段。03使用腳本組件進(jìn)行數(shù)據(jù)清洗使用JavaScript代碼組件清理數(shù)據(jù)現(xiàn)有一份圖書館的借閱信息（見圖4-57）。使用JavaScript代碼組件清理數(shù)據(jù)然后根據(jù)借閱的周數(shù)設(shè)定狀態(tài)，并在該步驟下方的字段欄里填上返回的狀態(tài)字段及類型。該步驟的具體設(shè)置如圖4-58所示。使用JavaScript代碼組件清理數(shù)據(jù)通過預(yù)覽“ModifiedJavaScriptValue”步驟，不難看到該轉(zhuǎn)換的輸出結(jié)果，如圖4-59所示。雖然“Replaeeinstring（字符串替換）”“DataValidator（數(shù)據(jù)校驗(yàn)）”等步驟都用到了正則表達(dá)式，但正則表達(dá)式真正強(qiáng)大的功能卻是通過“Regex