欄位清理與標準化

使用 Java 進行資料清理

Dennis Lee

Software Engineer

書籍銷售資料集

Books	Authors	Language	First_Published	Sales_in_Millions	Average_Price
A Tale of Two Cities	Charles Dickens	English	1859	200.0	12.99
The Little Prince (Le Petit Prince)	Antoine de Saint-Exupéry	French	1943	200.0	15.50
Harry Potter and the Philosopher's Stone	J. K. Rowling	English	1997	120.0	19.99
And Then There Were None	Agatha Christie	English	1939	100.0	14.95

檢視資料型別

import tech.tablesaw.api.Table;
import tech.tablesaw.api.StringColumn;
import tech.tablesaw.api.DoubleColumn;

Table books = Table.read().csv("bestsellers.csv");

for (String colName : books.columnNames()) {

    // 列印欄位名稱與其型別
    System.out.println(colName + ": " + books.column(colName).type());
}

檢視資料型別：輸出

Books: STRING
Authors: STRING
Language: STRING
First_Published: INTEGER
Sales_in_Millions: INTEGER
Average_Price: DOUBLE

轉換數值格式

// 將 Tablesaw 推斷為 INTEGER 的 IntColumn 轉為 DoubleColumn
DoubleColumn sales = books.intColumn("Sales_in_Millions")
      .asDoubleColumn();

System.out.println("Data type of sales column after conversion: " + sales.type());

Data type of sales column after conversion: DOUBLE

// 也可以將 DoubleColumn 轉為 IntColumn
IntColumn salesAsInt = sales.asIntColumn();

欄位相乘

DoubleColumn averagePrice = books.doubleColumn("Average_Price");

// 透過相乘兩個欄位計算總營收
DoubleColumn totalRevenue = sales.multiply(averagePrice)

        .setName("Total_Revenue_Millions");

books.addColumns(totalRevenue);

System.out.println("\nMultiplying two columns: Sales × Price = Revenue");
System.out.println(books.selectColumns("Books", "Sales_in_Millions", 
                                       "Average_Price", "Total_Revenue_Millions")
        .first(4).print());

欄位相乘：輸出

Multiplying two columns: Sales * Price = Revenue

| Books                               | Sales_in_  | Average_  | Total_Revenue_ |
|                                     | Millions   | Price     | Millions       |
|-------------------------------------|------------|-----------|----------------|
| A Tale of Two Cities                | 200        | 12.99     | 2598           |
| The Little Prince (Le Petit Prince) | 200        | 15.50     | 3100           |
| Harry Potter and                    | 120        | 19.99     | 2398.80        |
|   the Philosopher's Stone           |            |           |                |
| And Then There Were None            | 100        | 14.95     | 1495           |

比對括號的正規表示式

規則 \\(.*\\) 可比對括號內容
範例比對："(Organic)"、"(2 pack)"、"(Large)"
結果："Apple (Organic)" 會變成 "Apple"

\\(     // 比對開括號（以 \ 跳脫）
.*      // 比對任意字元（.）出現 0 次或多次（*）
\\)     // 比對閉括號（以 \ 跳脫）

處理特殊字元

// 從標題移除括號內的翻譯
StringColumn titlesNoParentheses = titles
        // 對每個標題移除括號與兩側空白
        .map(t -> t.replaceAll("\\(.*\\)", "").trim())
        // 設定新欄位名稱
        .setName("Titles without parentheses");

System.out.println("Original: " + titles.get(1)); // 取得第 1 筆欄位項目
System.out.println("Cleaned: " + titlesNoParentheses.get(1));

Original: The Little Prince (Le Petit Prince)
Cleaned: The Little Prince

結合字串處理

// 合併：移除括號並轉為小寫
StringColumn titlesCombinedCleaning =
        titles.map(t -> t.replaceAll("\\(.*\\)", "") // 移除括號

                         .trim() // 去除首尾空白

                         .toLowerCase()) // 轉為小寫
              .setName("Clean_Titles_Combined"); // 設定欄位名稱

System.out.println("Original title: " + titles.get(1));
System.out.println("Title after cleaning: " + titlesCombinedCleaning.get(1));

Original title: The Little Prince (Le Petit Prince)
Title after cleaning: the little prince

整合操作

books.column(colName).type(); // 取得欄位型別
books.intColumn("Sales_in_Millions").asDoubleColumn(); // 轉換型別

DoubleColumn totalRevenue = sales.multiply(averagePrice) // 欄位相乘
    .setName("Total_Revenue_Millions"); // 命名新欄位

titles.map(String::toLowerCase) // 轉為小寫
    .setName("Lowercase_Titles"); // 命名新欄位

titles.map(t -> t.replaceAll("\\(.*\\)", "").trim()) // 移除括號
    .setName("Titles without parentheses"); // 命名新欄位

一起來練習吧！

使用 Java 進行資料清理