什麼是資料清洗
資料清洗是指在資料處理過程中對原始資料進行篩選、轉換和修正，以確保資料的準確性、一致性和完整性的過程。它是資料預處理的一部分，旨在處理和糾正可能存在的錯誤、缺失值、異常值和不一致性等資料質量問題。
為什麼要資料清洗
Excel在資料採集場景中非常常用。作為一款電子表格軟體，它提供了豐富的功能和易用的介面，使其成為大部分人首選的資料採集工具之一。
而在資料採集的過程中，因為採集渠道多樣，資料格式也多種多樣，從而會出現部分資料的丟失和不準確的情況，因此為了處理掉這些 “垃圾”資料，需要對資料進行清洗。
哪些資料需要進行清洗
通常在這幾種情況下需要進行資料清洗。
1.缺失資料處理：資料在採集或遷移的過程中，出現資料的遺漏。
2.錯誤資料判斷：資料在採集或遷移的過程中與原資料不一致。
3.重複資料處理：一條資料重複出現多次。
4.資料格式轉換：資料在採集或遷移的過程中出現了亂碼。
資料清洗都需要做些什麼
下面讓我們看一下資料清洗都會涉及的處理步驟：
- 分析需求：透過對資料原本的格式，特徵進行分析，規劃資料清洗的業務規則及需求。
- 開啟檔案：把Excel檔案開啟，通常這一步需要依賴Excel元件庫，比如使用POI，GcExcel，EasyExcel等。
- 讀取資料：透過Excel庫中的API，讀取需要操作的資料，這裡比較一下三個產品的特點：
GcExcel提供了IRange(區域）的概念，可以透過API快速的讀取有資料的區域。POI和EasyExcel（POJO註解）則需要遍歷每一個單元格。
根據業務需求，可以選擇使用API，也可以選擇遍歷所有單元格。
- 資料清洗：根據需求，結合Excel庫的API，進行資料清洗。如：用預設值填寫缺失資料的單元格，刪除整個空行，刪除重複資料，把不符合範圍的資料刪除掉，或者把日期數字的格式統一起來，等等。
- 資料持續化：把處理好的資料回存至Excel檔案，或者儲存在資料庫中或者CSV檔案中。
如何使用GcExcel實現資料清洗
GcExcel有IRange的API，可以讓資料清洗時程式碼寫的更簡單，因此下面我們選擇用GcExcel的程式碼為例解決上面提到的幾個場景。
基於IRange，GcExcel提供一些快速查詢的API，如下（在檔案中查詢特殊單元格）：
Workbook workbook = new Workbook();
workbook.open("data.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
//尋找sheet中，使用到的所有單元格
IRange usedRange = sheet.getUsedRange();
//尋找所有的公式單元格
IRange allFormulas = sheet.getCells().specialCells(SpecialCellType.Formulas);
//尋找所有的常量單元格
IRange allConstants = sheet.getCells().specialCells(SpecialCellType.Constants);
雖然GcExcel提供了API，但資料清洗時，也可能有需求需要遍歷，下面是GcExcel遍歷單元格的程式碼，後面我們就有可能會用到。
public void FetchCellBasedOnRange(IRange area) {
for (int column = 0; column < area.getColumns().getCount(); column++) {
for (int row = 0; row < area.getRows().getCount(); row++) {
IRange cell = area.get(row, column);
//獲取單元格的值
Object val = cell.getValue();
}
}
}
場景一：缺失資料處理
假如有一個Excel的資料，現在藍色的格子是空的，我們需要對不同列下的藍色格子做不同的處理，例如姓名的空格子替換為匿名，年齡替換成-1，身份證號填寫N/A，住址填寫為未知。
程式碼如下：
public void replaceBlankCell() {
Workbook workbook = new Workbook();
workbook.open("resources/BlankCells.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
IRange blankRanges = sheet.getCells().specialCells(SpecialCellType.Blanks);
for (IRange area : blankRanges.getAreas()) {
for (int column = 0; column < area.getColumns().getCount(); column++) {
for (int row = 0; row < area.getRows().getCount(); row++) {
IRange cell = area.get(row, column);
Object defaultVal = getDefaultVal(cell.getColumn());
cell.setValue(defaultVal);
}
}
}
workbook.save("Result.xlsx");
}
private Object getDefaultVal(int column) {
switch (column) {
case 1:
return "匿名";
case 2:
return -1;
case 3:
return "N/A";
case 4:
return "未知";
}
return null;
}
要注意的是，sheet.getCells().specialCells(SpecialCellType.Blanks);返回的區域是多個，因此我們需要遍歷透過遍歷areas來對每一個區域進行遍歷。
cell.getColumn()可以獲取到當前格子對應到sheet上的第幾列，因此獲取預設值時使用該方法。
場景二：錯誤資料判斷
錯誤資料的判斷，與缺失資料處理相似，透過制定一些規則找出錯誤的值，對於錯誤值可以透過修改背景顏色進行高亮處理，用來提示，進行人工修改。
通常規則可以有兩種選擇：
- 使用Java直接編寫判斷邏輯。
- 使用資料校驗（Datavalidation）功能，或者條件格式（ConditionFormat）來進行處理。
假如我們有下面一份資料，其中聯絡電話中有兩條是錯誤的，位數不夠，貨物ID有兩條是錯誤的，貨物ID不能小於0，我們需要把他們找出來。
public void MarkErrorData(){
Workbook workbook = new Workbook();
workbook.open("resources/ErrorData.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
IRange telRange = sheet.getRange("C2:D5");
for (int r=0; r<telRange.getRows().getCount();r++){
IRange cell = telRange.get(r,0);
if(cell.getValue().toString().length() != 11){
cell.getInterior().setColor(Color.GetOrangeRed());
}
}
IFormatCondition condition =
(IFormatCondition) sheet.getRange("D2:D5").getFormatConditions().
add(FormatConditionType.CellValue, FormatConditionOperator.Less, 1, null);
condition.getInterior().setColor(Color.GetOrangeRed());
workbook.save("Result.xlsx");
}
在程式碼中，我們對C2:C5進行遍歷，判斷字串長度，然後對長度不合法的資料進行顏色標記。
而對於貨物，設定了條件格式，可以讓Excel在開啟時，自行標記錯誤的資料。
場景三：重複資料處理
假如我們有一份資料，其中有一些行資料是完全重複的，我們需要刪除這些行，如圖所示。
public void RemoveDuplicateData() {
Workbook workbook = new Workbook();
workbook.open("resources/DuplicateRows.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
IRange usedRange = sheet.getUsedRange();
HashSet<String> set = new HashSet<>();
Stack<IRange> deleteRows = new Stack<>();
for (int r = 1; r < usedRange.getRows().getCount(); r++) {
IRange row = usedRange.getRows().get(r);
StringBuilder rowKey = new StringBuilder();
for (int c = 0; c < row.getColumns().getCount(); c++) {
rowKey.append(usedRange.get(r, c).getValue().toString());
}
if (set.contains(rowKey.toString())) {
deleteRows.push(row);
} else {
set.add(rowKey.toString());
}
}
while (!deleteRows.isEmpty()) {
deleteRows.pop().delete();
}
workbook.save("Result.xlsx");
}
可以看到，重複的行被移除掉了。程式碼中用到了雜湊set和棧，其中我們用雜湊set來查詢重複的行。
另外使用棧來記錄需要被刪除的行，這裡特地用了棧，而沒有使用佇列，陣列或者ArraryList的原因是，GcExcel在刪除一行時，會讓這行下面的資料上移，這樣我們之前記錄的行就會便宜，導致刪除錯誤的行。
簡而言之，我們需要從下向上刪除，來避免行位移導致刪錯的問題。
場景四：資料格式轉換
例如我們有一些日期資料，或者貨幣資料，在資料採集時資料格式不同，我們需要分別統一訂單日期，金額的格式。
程式碼如下：
public void unifyFormat() {
Workbook workbook = new Workbook();
workbook.open("resources/DifferentFormat.xlsx");
IWorksheet sheet = workbook.getActiveSheet();
IRange usedRange = sheet.getUsedRange();
for (int row = 1; row < usedRange.getRows().getCount(); row++) {
IRange dateCell = usedRange.get(row, 1);
IRange priceCell = usedRange.get(row, 2);
dateCell.setValue(parseDate(dateCell.getValue()));
dateCell.setNumberFormat("yyyy年MM月dd日");
priceCell.setValue(parsePrice(priceCell.getValue()));
priceCell.setNumberFormat("¥0.00");
}
sheet.getRange("B1").setNumberFormat("");
workbook.save("Result.xlsx");
}
private Double parsePrice(Object value) {
if (value == null)
return null;
String val = value.toString();
if (val.startsWith("$") || val.startsWith("¥")) {
val = val.substring(1);
}
return Double.parseDouble(val);
}
private LocalDateTime parseDate(Object value) {
if (value == null)
return null;
if (value instanceof LocalDateTime) {
return (LocalDateTime) value;
}
DateTimeFormatter[] formatters = {
DateTimeFormatter.ofPattern("yyyy/MM/dd"),
DateTimeFormatter.ofPattern("MM-dd-yyyy"),
DateTimeFormatter.ofPattern("yyyy年MM月dd日"),
DateTimeFormatter.ofPattern("yyyy.MM.dd")
};
LocalDate datetime = null;
for (DateTimeFormatter formatter : formatters) {
try {
datetime = LocalDate.parse(value.toString(), formatter);
break;
} catch (DateTimeParseException e) {
e.printStackTrace();
}
}
assert datetime != null;
return datetime.atStartOfDay();
}
需要注意的是在處理日期和金額時，由於value的型別不太一致，需要寫特定的方法來進行處理。
擴充套件連結：