如何檢測缺失數據

A. 請問哪個excel函數可以迅速檢查一列中的重復數據和缺失數據

用公式自動生成序號，絕對不會重復或缺失
例如：
A2為1開始的序號。在A2中輸入
=row()-1
下拉填充公式。
或者在A2中輸入1，在A3中輸入公式
=A2+1
下拉填充公式。

現有的序號要找出重復的，可插入一列B，在B2中輸入公式
=if(countif(a2:a451,a2)>1,a2,"")
下拉填充公式，可看到重復的序號。

現有序號要找出缺失的，
可插入一列C，在C2中輸入公式
=if(countif(a2:a451,row()-1)=0,row()-1,"")
下拉填充公式，可看到缺失的序號。

補充：你改的公式不對，如果按你的例子，在C5中輸入
=IF(COUNTIF($A$5:$A$454,ROW()+$A$5-1)=0,ROW()+$A$5-1,"")
下拉即可找出缺失的

6366
6367
6368
6368
6369
6370
6371
6372
6372
6373
6374
6375
6376
6377
6378 6384
6379 6385
6380
6381
6382
6383
6386
6387
6388
6389
6390 6394
6391 6395
6392
6393
6396
6397
6398
6399 6401
6400 6402

B. 如何檢測業務數據中的異常

處理異常值

異常值的定義是與均值的偏差超過兩倍標准，但是在臟數據中，異常值的情況不止這一種：

1）比如一列數據你打開看全部是數字，當你把它當數值型處理，它會報錯；那就得仔細查找原因，遇到比較多的情況是一列數字中夾雜了幾個奇怪的字元串或者符號等元素，如果幾萬條數據中只有一兩個這種字元，即使認真從前到後仔細查看也很難發現還浪費大量時間，效率極低。

還有一種情況比較常見，就是看起來是數字，實際上都是字元串的形式，但是以表格查看的時候是看不到字元串的引號；這兩種情況可以通過查看特徵類型來提前發現，在python中用type()或者dtypes()函數，兩者使用對象有差別，可自行了解；

2）幾種常用異常值檢測方法：

• 3σ探測方法

3σ探測方法的思想其實就是來源於切比雪夫不等式。

對於任意ε>0，有：

當時，如果總體為一般總體的時候，統計數據與平均值的離散程度可以由其標准差反映，因此有：

• 一般所有數據中，至少有3/4（或75%）的數據位於平均2個標准差范圍內。

• 所有數據中，至少有8/9（或88.9%）的數據位於平均數3個標准差范圍內。

• 所有數據中，至少有24/25（或96%)的數據位於平均數5個標准差范圍內。
  

所以如果我們一般是把超過三個離散值的數據稱之為異常值。這個方法在實際應用中很方便的使用，但是他只有在單個屬性的情況下才適用。

• z-score
  

Z-score是一維或低維特徵空中的參數異常檢測方法。該技術假定數據是高斯分，異常值是分布尾部的數據點，因此遠離數據的平均值。距離的遠近取決於使用公式計算的歸一化數點z i的設定閾值Zthr：



Zthr值一般設置為2.5、3.0和3.5。該技術是使用KNIME工作流中的行過濾器節點實現的。

這種異常值處理需要結合最終需求來決定怎麼處理，常見的是不處理或者按缺失值的方法處理，但是在實際場景中，異常值有時候會有非常突出的表現，比如在現金貸業務中，異常值中的壞賬率遠高於整體壞賬水平或其他區間壞賬水平，這時候異常值就得保留並作為決策閾值的參考值。

• IQR

觀察箱型圖，或者通過IQR（InterQuartile Range）計算可以得到數據分布的第一和第四分位數，異常值是位於四分位數范圍之外的數據點。

這個方法真的很簡單，因為只需要給數據排個序就行了，顯然過於籠統，但在實際場景中，觀察箱型圖仍然是一個很好的探索數據分布的方法。

畢竟，所有復雜的探索，都是從最開始簡單的探索一步步得來的嘛！

C. R語言缺失值處理

2016-08-23 05:17 砍柴問樵夫

數據缺失有多種原因，而大部分統計方法都假定處理的是完整矩陣、向量和數據框。

缺失數據的分類：

完全隨機缺失 ：若某變數的缺失數據與其他任何觀測或未觀測變數都不相關，則數據為完全隨機缺失（MCAR）。

隨機缺失： 若某變數上的缺失數據與其他觀測變數相關，與它自己的未觀測值不相關，則數據為隨機缺失（MAR）。

非隨機缺失： 若缺失數據不屬於MCAR或MAR，則數據為非隨機缺失（NMAR） 。

處理缺失數據的方法有很多，但哪種最適合你，需要在實踐中檢驗。

下面一副圖形展示處理缺失數據的方法：

處理數據缺失的一般步驟：

1、識別缺失數據

2、檢測導致數據缺失的原因

3、刪除包含缺失值的實例或用合理的數值代替（插補）缺失值。

1、識別缺失數據：

R語言中， NA 代表缺失值， NaN 代表不可能值， Inf 和 -Inf 代表正無窮和負無窮。

在這里，推薦使用 is.na ， is.nan ， is.finite ， is.infinite 4個函數去處理。

x<-c(2,NA,0/0,5/0)

#判斷缺失值

is.na(x)

#判斷不可能值

is.nan(x)

#判斷無窮值

is.infinite(x)

#判斷正常值

is.finite(x)

推薦一個函數： complete.case() 可用來識別矩陣或數據框中沒有缺失值的行！

展示出數據中缺失的行 （數據集sleep來自包VIM）

sleep[!complete.cases(sleep),]

判斷數據集中有多少缺失

針對復雜的數據集，怎麼更好的探索數據缺失情況呢？

mice包 中的 md.pattern() 函數可以生成一個以矩陣或數據框形式展示缺失值模式的表格。

備註：0表示變數的列中沒有缺失，1則表示有缺失值。

第一行給出了沒有缺失值的數目（共多少行）。

第一列表示各缺失值的模式。

最後一行給出了每個變數的缺失值數目。

最後一列給出了變數的數目（這些變數存在缺失值）。

在這個數據集中，總共有38個數據缺失。

圖形化展示缺失數據：

aggr(sleep,prop=F,numbers=T)

matrixplot(sleep)

淺色表示值小，深色表示值大，默認缺失值為紅色。

marginmatrix(sleep)

上述變數太多，我們可以選出部分變數展示：

x <- sleep[, 1:5]

x[,c(1,2,4)] <- log10(x[,c(1,2,4)])

marginmatrix(x)

為了更清晰，可以進行成對展示：

marginplot(sleep[c("Gest","Dream")])

在這里(左下角)可以看到，Dream和Gest分別缺失12和4個數據。

左邊的紅色箱線圖展示的是在Gest值缺失的情況下Dream的分布，而藍色箱線圖展示的Gest值不缺失的情況下Dream的分布。同樣的，Gest箱線圖在底部。

2、缺失值數據的處理

行刪除法： 數據集中含有缺失值的行都會被刪除，一般假定缺失數據是完全隨機產生的，並且缺失值只是很少一部分，對結果不會造成大的影響。

即：要有足夠的樣本量，並且刪除缺失值後不會有大的偏差！

行刪除的函數有 na.omit() 和 complete.case()

newdata<-na.omit(sleep)

sum(is.na(newdata))

newdata<-sleep[complete.cases(sleep),]

sum(is.na(newdata))

均值/中位數等填充： 這種方法簡單粗暴，如果填充值對結果影響不怎麼大，這種方法倒是可以接受，並且有可能會產生令人滿意的結果。

方法1：

newdata<-sleep

mean(newdata$Dream,na.rm = T)

newdata[is.na(newdata$Dream),"Dream"]<-1.972

方法2：

Hmisc包更加簡單，可以插補均值、中位數等，你也可以插補指定值。

library(Hmisc)

impute(newdata$Dream,mean)

impute(newdata$Dream,median)

impute(newdata$Dream,2)

mice包插補缺失數據： 鏈式方程多元插值，首先利用mice函數建模再用complete函數生成完整數據。

下圖展示mice包的操作過程：

mice()：從一個含缺失值的數據框開始，返回一個包含多個完整數據集對象（默認可以模擬參數5個完整的數據集）

with()：可依次對每個完整數據集應用統計建模

pool()：將with()生成的單獨結果整合到一起

library(mice)

newdata<-sleep

data<-mice(newdata,m = 5,method='pmm',maxit=100,seed=1)

在這里，m是默認值5，指插補數據集的數量

插補方法是pmm：預測均值匹配，可以用methods(mice)查看其他方法

maxit指迭代次數，seed指設定種子數（和set.seed同義）

概述插補後的數據：

summary(data)

在這上面可以看到數據集中變數的觀測值缺失情況，每個變數的插補方法， VisitSequence 從左至右展示了插補的變數， 預測變數矩陣 （PredictorMatrix）展示了進行插補過程的含有缺失數據的變數，它們利用了數據集中其他變數的信息。（在矩陣中，行代表插補變數，列代表為插補提供信息的變數，1

和0分別表示使用和未使用。）

查看整體插補的數據：

data$imp

查看具體變數的插補數據：

data$imp$Dream

最後，最重要的是生成一個完整的數據集

completedata<-complete(data)

判斷還有沒有缺失值，如果沒有，結果返回FLASE

anyNA(completedata)

針對以上插補結果，我們可以查看原始數據和插補後的數據的分布情況

library(lattice)

xyplot(data,Dream~NonD+Sleep+Span+Gest,pch=21)

圖上，插補值是洋紅點呈現出的形狀，觀測值是藍色點。

densityplot(data)

圖上，洋紅線是每個插補數據集的數據密度曲線，藍色是觀測值數據的密度曲線。

stripplot(data, pch = 21)

上圖中，0代表原始數據，1-5代表5次插補的數據，洋紅色的點代表插補值。

下面我們分析對數據擬合一個線性模型：

完整數據：

library(mice)

newdata<-sleep

data<-mice(newdata,m = 5,method='pmm',maxit=100,seed=1)

model<-with(data,lm(Dream~Span+Gest))

pooled<-pool(model)

summary(pooled)

fim指的是各個變數缺失信息的比例，lambda指的是每個變數對缺失數據的貢獻大小

缺失數據（在運行中，自動會行刪除）：

lm.fit <- lm(Dream~Span+Gest, data = sleep，na.action=na.omit)

summary(lm.fit)

完整數據集和缺失數據集進行線性回歸後，參數估計和P值基本一直。 缺失值是完全隨機產生的 。如果缺失比重比較大的話，就不適合使用行刪除法，建議使用多重插補法。

kNN插值法： knnImputation函數使用k近鄰方法來填充缺失值。對於需要插值的記錄，基於歐氏距離計算k個和它最近的觀測。接著將這k個近鄰的數據利用距離逆加權算出填充值，最後用該值替代缺失值。

library(DMwR)

newdata<-sleep

knnOutput <- knnImputation(newdata)

anyNA(knnOutput)

head(knnOutput)

D. 對於缺失值的處理

建議：不同場景下的數據缺失機制不同，這需要工程師基於對業務選擇合適的填充方法。

如何判斷缺失值類型？
缺失值的分類按照數據缺失機制可分為：
可忽略的缺失

不可忽略的缺失

平常工作中遇到的缺失值大部分情況下是隨機的（缺失變數和其他變數有關）

這個就可以用estimator來做了，選其中一個變數（y），然後用其他變數作為X，隨便選個值填充X的缺失部分，用X train一個estimator，再預測y的缺失部分（大致思路）

此外有些數據是符合某種分布的，利用這個分布呢也可以填充缺失的數據，如(EM演算法)

處理缺失數據的三個標准：
1. 非偏置的參數估計
不管你估計means, regressions或者是odds ratios，都希望參數估計可以准確代表真實的總體參數。在統計項中，這意味著估計需要是無偏的。有缺失值可能會影響無偏估計，所以需要處理。
2. 有效的能力：
刪除缺失數據會降低采樣的大小，因此會降低power。如果說問題是無偏的，那麼得到的結果會是顯著的，那麼會有足夠的能力來檢驗這個效力（have adequate power to detect your effects)。反之，整個檢測可能失效。
3. 准確的標准差（影響p值和置信區間）：
不僅需要參數估計無偏，還需要標准差估計准確，在統計推斷中才會有效。

缺失值處理的方法大致分為這幾類：1、刪除法；2、基於插補的方法；3、基於模型的方法; 4、不處理; 5、映射高維

有些處理方法是基於完全隨機缺失假設（MCAR），一般來說，當數據不是 MCAR 而 是隨機缺失（MAR）時，這些方法是不適用的；而有些方法(如似然估計法)在 MAR 的假設下是適用的，因此，在進行缺失數據處理時，首先需要認真分析缺失數 據產生的原因，然後採取有針對性的補救措施，這樣才能夠獲得無偏或弱偏估計。

此處關於使用多重插補來處理非隨機缺失（MNAR）的問題，它其實效果不一定，也可能出現效果倒退的情況，總的說多重更適合MAR

註：此處一元與多元指的是僅有一個特徵有缺失值與多個特徵有缺失值

對於不同類別的缺失值的處理方法如上圖。

以下展開介紹各個方法：

註： k-means插補 與KNN插補很相似，區別在於k-means是利用無缺失值的特徵來尋找最近的N個點，然後用這N個點的我們所需的缺失的特徵平均值來填充，而KNN則是先用均值填充缺失值再找最近的N個點。

類似的還有 隨機回歸插補 ：也優於純回歸插補

其他單一插補法：

與單一插補方法相比較，多重插補方法充分地考慮了數據的不確定性。多重插補的主要分為三個步驟，綜合起來即為：插補、分析、合並。插補步是為每個缺失值都構造出 m 個可能的插補值，缺失模型具有不確定性，這些插補值能體現出模型的這個性質，利用這些可能插補值對缺失值進行插補就得到了 m 個完整數據集。分析步是對插補後的 m 個完整數據集使用一樣的統計數據分析方法進行分析，同時得到 m 個統計結果。綜合步就是把得到的這 m 個統計結果綜合起來得到的分析結果，把這個分析結果作為缺失值的替代值。多重插補構造多個插補值主要是通過模擬的方式對估計量的分布進行推測，然後採用不同的模型對缺失值進行插補，這種插補是隨機抽取的方式，這樣以來能提高估計的有效性和可靠性。
多重插補-python手冊

多重插補法主要有以下幾種：

（使用回歸、貝葉斯、隨機森林、決策樹等模型對缺失數據進行預測。）

基於已有的其他欄位，將缺失欄位作為目標變數進行預測，從而得到較為可能的補全值。如果帶有缺失值的列是數值變數，採用回歸模型補全；如果是分類變數，則採用分類模型補全。

常見能夠自動處理缺失值模型包括：KNN、決策樹和隨機森林、神經網路和樸素貝葉斯、DBSCAN（基於密度的帶有雜訊的空間聚類）等。

處理思路：
自動插補 ：例如XGBoost會通過training loss rection來學習並找到最佳插補值。
忽略 ：缺失值不參與距離計算，例如：KNN，LightGBM
將缺失值作為分布的一種狀態 ：並參與到建模過程，例如：決策樹以及變體。
不基於距離做計算 ：因此基於值得距離計算本身的影響就消除了，例如：DBSCAN。

ID3、c4.5、cart、rf到底是如何處理缺失值的？

最精確的做法，把變數映射到高維空間。
比如性別，有男、女缺失三種情況，則映射成3個變數:是否男、否女、是否缺失。連續型變數也可以這樣處理。比如Google、 網路的CTR預估模型，預處理時會把所有變數都這樣處理，達到幾億維。又或者可根據每個值的頻數，將頻數較小的值歸為一類'other'，降低維度。此做法可最大化保留變數的信息。

前推法 （LOCF，Last Observation Carried Forward，將每個缺失值替換為缺失之前的最後一次觀測值）與 後推法 （NOCB，Next Observation Carried Backward，與LOCF方向相反——使用缺失值後面的觀測值進行填補）

這是分析可能缺少後續觀測值的縱向重復測量數據的常用方法。縱向數據在不同時間點跟蹤同一樣本。當數據具有明顯的趨勢時，這兩種方法都可能在分析中引入偏差，表現不佳。

線性插值 。此方法適用於具有某些趨勢但並非季節性數據的時間序列。

季節性調整+線性插值 。此方法適用於具有趨勢與季節性的數據。

總而言之，大部分數據挖掘的預處理都會使用比較方便的方法來處理缺失值，比如均值法，但是效果上並不一定好，因此還是需要根據不同的需要選擇合適的方法，並沒有一個解決所有問題的萬能方法。

具體的方法採用還需要考慮多個方面的：

在做數據預處理時，要多嘗試幾種填充方法，選擇表現最佳的即可。

總結來說，沒有一個最完美的策略，每個策略都會更適用於某些數據集和數據類型，但再另一些數據集上表現很差。雖然有一些規則能幫助你決定選用哪一種策略，但除此之外，你還應該嘗試不同的方法，來找到最適用於你的數據集的插補策略。

當前最流行的方法應該是 刪除法、KNN、多重插補法 。

參考文獻： 龐新生. 缺失數據處理方法的比較[J]. 統計與決策, 2010(24):152-155.

E. 探索性數據分析之缺失值檢測與處理

查看一下數據（數據集已處理為pandas.dataframe）

如果發現有缺失值要對缺失值進行分析，輸出每個列丟失值也即值為NaN的數據和，並從多到少排序

統計缺失值的比例

處理缺失值時根據缺失值的具體情況有兩種策略

可以選擇忽略有缺失特徵的列。

在缺失的行數比較少的情況下應僅僅忽略出現缺失的那幾行

使用特殊值來填補缺失值，特殊值的選取需要根據情況來判斷。

sklearn的Imputer類提供了補全缺失值的基本策略：