資料品質屬性的驗證
資料品質屬性:完整性、無重複、準確性、一致性、遵循規則、可用性
「資料品質」可以根據:完整性、無重複、準確性、一致性、遵循規則、可用性的屬性來定義。
「驗證」是把收集的資料檢查其品質。
驗證是一個迭代過程,應該按計劃重複,特別是在實施新的資料庫或軟體時。
此步驟將“原始資料”轉換為“經驗證的資料”。
在開始對資料進行任何操作之前,即使是簡單的有效性檢查,也請務必將原始資料保存在安全的位置,並使用主副本進行任何操作。
完成每個驗證階段後,你應該生成一個「驗證報告」,列出所有可疑資料併計算資料恢復率。
完整性
-
需要完整性的字段(欄)都沒有缺失值
-
存在的記錄數是適當的數據量
-
所有必需的字段(欄)都存在
-
主鍵是存在的,唯一的和良好的格式
-
所有外鍵字段(欄)都存在和良好的格式
-
適當處理可疑和丟失的數據
無重複
-
重複的記錄不存在
-
冗餘字段(欄)不存在
-
不同的數據集之間的重複記錄不存在
準確性
-
「分類變量」的驗證(存在的數據值是特定字段(欄)的預定值之一)。
A variable is categorical if its values fall into a distinct set of categories that do not overlap.
An example of a common categorical variable is "gender."
Every individual will be grouped into either "male" or "female", and possibly "unknown" if you have missing data.
Other categorical variables may include:
Pain scale (1~10), Likert scales such as 1=strongly disagree, 2=disagree, 3=neutral, 4=agree, 5=strongly agree.
Typical validation procedures: frequency checks (counting the number in each group), cross tabulations or pivot tables (counting the number in each group of combinations of variables)
{example}
-
「連續變量」的驗證(數值在預定的特定範圍內)。
Continuous variables are numeric variables. You can count, order and measure continuous data.
Some examples: age, weight, height, temperature.
Typical validation procedures are checking the minimum value, the maximum value, the mean, and the median.
Check that the result seems reasonable.
{example}
-
驗證「日期時間」變量(數據值落在預定時間段內)。
Make sure admission-discharge fields are in proper temporal sequence.
{example}
-
數據異常值(極端值)不存在或已被識別和校正/解釋
Identify outliers using trimmed mean, standard deviation, or control charts
{example}
-
派生數據字段(欄)通過已正確的邏輯計算
Definitions and exclusions
{example}
-
計算的數據字段(欄)已正確計算
statistical analysis: agegroups INT(age/10) vs. CEILING(age/10,1) vs. FLOOR(age/10,1)
Newborn less than 1 year-old. What about zero?
{example} 53 year old which agegroup category:
INT(53/10)=5, CEILING(53/10,1)=6, FLOOR(53/10,1)=5
3 month old baby (3/12 year old): which agegroup category:
INT(0.25/10)=0, CEILING(0.25/10,1)=0, FLOOR(0.25/10,1)=1
What about zero?
一致性
-
符號數據值在整個給定數據集中是一致的
-
相關數據集中的數據值保持一致
雖然外科手術資訊系統可以捕獲傷口類,但手術室工作人員可能覺得只是輸入最常見的值更方便(例如,對於心臟手術的“清潔”)。 醫院需要明確定義所期望的記錄和程序,以應對在手術中發生的事情改變傷口類,以確保捕獲這些資料。
遵循規則
-
所有規則都已確定並且準確無誤
-
數據已經過測試並遵循數據規則
-
對於代表數據類型,所有字段(欄)數據的格式正確
示例:用於決定跨月的重返是否計算或排除的規則;或用於計數侵入性處置的使用天數:每天一次或從插管的時間一直到拔管的時間如何計算的規則。
可用性
驗證報告
在“數據驗證日誌”中維護所有數據驗證操作的完整記錄。 此文件應包含每個已拒絕和替換值的以下信息: - 報告值
- 發生日期和時間
- 不符合的驗證條件
- 指定的驗證碼
- 任何替代值的來源
數據恢復率
數據恢復率定義為收集的有效數據記錄數與報告期間可能的記錄數做比較。應該為每個主要度量(每個站點的所有級別)計算。計算方法如下:
數據恢復率(%)= 收集的數據記錄數除以可能的數據記錄數
其中 "收集的數據記錄" = 可能的數據記錄數減去無效記錄數。
截尾平均值 (Trimmed Mean)
在計算留下的數值之算術平均數之前,截尾平均值是透過排除在數值範圍兩端的數值。 這是為了避免計算平均值的時候受到極端值(也叫做離群值)的影響造成偏移。
Excel 中計算截尾平均值的函數
TRIMMEAN(array, percent)
〝percent〞代表數量的多少百分比應被排除。這個百分比被除以2,以取得從整個範圍的一端要被排除的數量。這個計算所得的百分比無條件捨去到最靠近 2 的倍數。舉例來說,十個數值的序列,截尾平均值為:
-
百分比為 15%,即 1.5 個值,這將會向下捨入到 0(在計算平均值之前,沒有數值需要排除)。
-
百分比為 20%,即 2 個值,這將會向下捨入到 1,在用剩餘的值計算平均值之前,範圍兩端要各排除一個值。
-
百分比為 25%,即 2.5 個值,這將會向下捨入到 2(在用剩餘的值計算平均值之前,範圍兩端要各排除兩個值)。
在使用 TRIMMEAN 後,標準差的計算方法說明於下(命名範圍的資料料〝Range1〞,TRIMMEAN 百分比為〝Percent〞)
"={STDEV(SMALL(Range1,ROW(INDIRECT(INT(COUNT(Range1)*Percent/2)+1&":"&COUNT(Range1)-INT(COUNT(Range1)*Percent/3)))))}"
要注意這是陣列的公式,用大括號框起來 { }。(看參考文獻 #4,了解如何利用 CTRL+SHIFT+ENTER 在 Excel 中輸入陣列公式)。
表 1、心率的原始資料
和篩查結果
表 2、用於計算極端值的Excel函數
表 1 顯示在欄 D 中 (D2:D31) 患者的心率(“HR”)值(欄 A 是患者 “ID”)。命名範圍 “rHR” 是 “D2:D31”。有的值丟失、有的值太高、有的值為 “NA”。
在欄 R 中使用與每個項目相同行所示的 Excel 函數計算平均值(R2),標準偏差(R3),截尾平均值(R7)和截尾標準差(R8) 。
注意 “TrimSD”(R8):請注意,是由 “{ }” 括起來。 Excel 使用特殊方法將其指定為數組公式(不要輸入 “{” 和 “}”)。參見下面的參考 #4。
極端值的篩檢
欄 J 顯示篩選出來的值為超過平均± 2 標準差,使用以下的公式:
J2: "=IF(AND(D2<>"",OR(D2<$R$5,D2>$R$4)),D2,"")"
這顯示 D22 (=900) 及 D28 (=NA) 為極端值。注意這個方法確實偵測到〝NA〞為無效,但 D11 及 D31 的空值未被偵測到。空值應該被偵測到且應在篩檢極端值之前被移除。
欄 K 顯示篩選出來的值為超過截尾平均± 20% (R13),使用以下的公式:
K2: "=IF(AND(D2<>"",OR(D2<$R$10,D2>$R$9)),D2,"")"
這顯示 D5, D9, D15, D19, D22, D23, D24, D25, D28 為極端值:
{101, 210, 22, 208, 900, 10, 48, 22, NA}
日期及時間
醫院資料庫中的日期和時間數據如果經常導出到Excel 常作為數字:20161003(YYYYMMDD)或 201610031155(YYYYMMDDHHMM),您需要將其轉換為Excel 可以識別的日期值(2016/10/03 和 2016/10/03 11:55)。
簡單地將單元格格式更改為日期或使用 Excel 對文本變量工作的 DATE 和 TIME 函數不會成功 - 結果是 “##########”。
為了轉換這樣的數字(〝10510010807〞)為日期時間欄位:將數值匯入 Excel 工作表的時候以文字方式匯入 D2 儲存格,且在同一行的進行處理。
-
如果你的醫院使用不同的計年系統(如,日本用天皇的名字或台灣用民國),須先調整為西元年。例如,在台灣,年度+1911。
E2: "=VALUE(LEFT(D2,3))+1911"
→ 2016
-
使用文字函數 LEFT 及 MID,以轉換數字為 Excel 認可的日期時間字串。
F2: "=E2&"/"&MID(D2,4,2)&"/"&MID(D2,6,2)&" "&MID(D2,8,2)&":"&MID(D2,10,2) "
→ 2016/10/01 08:07
-
使用Excel VALUE 函數,將文字格式的日期轉換為 Excel 確認為日期的流水號。VALUE 是用來結合單一變數的日期及時間。最較早的 Excel 版本(尚未此函數),使用 DATEVALUE(F2)+TIMEVALUE(F2)。
G2: "=VALUE(F2)"
→ 42644.34
-
轉換到 Excel 日期時間變數格式
H2: "=TEXT(G2,"YYYY-MM-DD HH:MM")"
→ 2016/10/01 08:07
-
確認為有效日期,否則留白。
I2: "=IF(ISNUMBER(G2),H2,"")"
→ TRUE
-
使用日期時間運算公式(如,兩個日期時間之間的時間)
表 3、急診室重返資料
急診重返(例子)
-
將從醫院急診重返資料庫匯出的匯入 Excel 工作表(表 3),確保日期時間欄位(欄 C 及欄 D)存為文字格式,而不是數字。欄位:
A:病人性別("M"=男性 或 "F"=女性)
B:病人年齡(數值)
C:初次離開急診的日期時間
D:再次回到急診的日期時間
-
首先,測試欄 C 和 D 以查看它們是否是 DATETIME 欄位的有效格式。欄 F 和欄 G 呈現檢測的結果,顯示為〝TRUE〞或〝FALSE〞。
-
接著欄 F 和欄 G 用來轉換欄 C 和欄 D 為有效的日期時間欄位,將結果存在欄 H 和欄 I。無效欄位(G11)將留白(I11),且之後的計算中省略。
-
欄H和欄I將以日期時間運算程式以計算兩者間的間隔時間(〝間隔〞)。
-
為了驗證報告,D11病人重返的日期時間將註記為無效。
F2: "=ISNUMBER(VALUE(TEXT(VALUE(LEFT(C2,3))+1911,"0000")&"/"&MID(C2,4,2)&"/"&MID(C2,6,2)&" "&MID(C2,8,2)&":"&MID(C2,10,2)))"
G2: "=ISNUMBER(VALUE(TEXT(VALUE(LEFT(D2,3))+1911,"0000")&"/"&MID(D2,4,2)&"/"&MID(D2,6,2)&" "&MID(D2,8,2)&":"&MID(D2,10,2)))"
H2: "=IF(F2,VALUE(TEXT(VALUE(LEFT(C2,3))+1911,"0000") & "/" & MID(C2,4,2) & "/" & MID(C2,6,2) & " " & MID(C2,8,2) & ":" & MID(C2,10,2)),"")"
I2: "=IF(G2,VALUE(TEXT(VALUE(LEFT(D2,3))+1911,"0000") & "/" & MID(D2,4,2) & "/" & MID(D2,6,2) & " " & MID(D2,8,2) & ":" & MID(D2,10,2)),"")"
J2: "=IF(AND(F2,G2),I2-H2,"")"
資料類型和限制值
表 4、用於驗證資料類型的邏輯函數
-
是否欄 A 為數值格式?
=ISNUMBER(VALUE(A2))
-
是否欄 B(性別)為〝M〞或〝F〞之一。
=ISNUMBER(MATCH(TRUE,EXACT({"M","F"},B2),0))
-
是否欄C(日期)不是空值,且是有效的 Excel 日期時間 DATETIME 的格式?
=AND(C2<>"",ISNUMBER(VALUE(C2)))
-
是否欄 D(心率)為介於 40 到 100 之間(含)的數值?
=AND(ISNUMBER(VALUE(D2)),D2>=40,D2<=100)
-
是否欄E(收縮壓)為介於 80 到 200 之間(含)的數值?
=AND(ISNUMBER(VALUE(E2)),E2>=80,E2<=200)
-
是否欄F(舒張壓)為介於 60 到 120 之間(含)的數值?
=AND(ISNUMBER(VALUE(F2)),F2>=60,F2<=120)
-
是否欄 G(CD-9-CM 診斷碼)大於 0且小於 999(含)?
=AND(ISNUMBER(VALUE(G2)),G2>"0",G2<="999")
-
是否欄 H(不良事件)為一個數字,且不是0 就是 1?
=ISNUMBER(MATCH(TRUE,EXACT({"0","1"},H2),0))
-
是否這一行是有效的(同一病人的所有資料)?
=AND(J2,K2,L2,M2,N2,O2,P2,Q2)
表 5、表 4 的資料驗證的結果
對於較簡單的配對,可以使用篩選功能。
-
點選篩選按鈕,讓 Excel 篩選所有資料的欄位
-
每一欄的標題可下拉篩選清單
-
取消選取(點選全部)
-
取消選取有效的選項(如,欄B的〝M〞及〝F〞)
資料表將僅顯示出沒有〝M〞或〝F〞的欄位;也就是無效的資料。
-
為了驗證個別的欄位,所有的欄位逐一進行,以確保除了正在驗證的欄位以外的欄位篩選的條件都為全部。
-
最後,藉由每欄的篩選功能選出有效的資料。這將列出在驗證後剩下的資料,以進行後續的分析。