欄位篩選與彙總

使用 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.selection.Selection;

Table books = Table.read().csv("bestsellers.csv");
// 銷量超過 70M 的條件
Selection highSales = books.intColumn("Sales_in_Millions").isGreaterThan(70);

// 英文書的條件
Selection english = books.stringColumn("Language").isEqualTo("English");

// 1950 年後出版的條件
Selection recentlyPublished =
        books.intColumn("First_Published").isGreaterThan(1950);

// 類似方法：.isLessThan(), .isLessThanOrEqualTo(), .isGreaterThanOrEqualTo()

依條件進行篩選

// 直接用單一條件篩選
books.where(books.intColumn("Sales_in_Millions").isGreaterThan(70));

// 使用預先定義的 Selection 篩選
books.where(highSales);


// 以 .and() 結合多個條件
Table popular = books.where(highSales)          // Sales > 70M
        .and(english)                           // 英文
        .and(recentlyPublished);                // 1950 年後
        .sortDescendingOn("Sales_in_Millions"); // 依 Sales_in_Millions 由大到小排序
System.out.println("Modern high-revenue English books:\n");
System.out.println(modern.select("Books", "First_Published", "Total_Revenue"));

依條件篩選：輸出

Modern high-revenue English books:

| Book                                     | Sales_in_Millions | First_Published |
|------------------------------------------|-------------------|-----------------|
| Harry Potter and the Philosopher's Stone | 120               | 1997            |
| The Da Vinci Code                        | 80                | 2003            |
| Harry Potter and the Chamber of Secrets  | 77                | 1998            |

以平均值彙總

// 匯入平均值計算函式
import static tech.tablesaw.aggregate.AggregateFunctions.mean;

Table overallMean = books.summarize("Sales_in_Millions", mean).apply();
System.out.println("\nOverall mean sales: \n" + overallMean);

Overall mean sales:

| Mean [Sales_in_Millions]  |
|---------------------------|
|        73.03225806451614  |

依群組聚合

// 針對每種語言（如 English、French）計算平均銷量
Table salesByLanguage = 
        books.summarize("Sales_in_Millions", mean)  // 計算平均

             .by("Language"); // 依語言分組

// 顯示前 5 個語言群組
System.out.println("Average sales by language:\n");
System.out.println(salesByLanguage.first(5));

依群組聚合：輸出

Average sales by language:

| Language   | Mean [Sales_in_Millions] |
|------------|--------------------------|
| English    | 69.96                    |
| French     | 200.0                    |
| Chinese    | 100.0                    |
| Portuguese | 65.0                     |
| Spanish    | 50.0                     |

結合篩選與彙總

import static tech.tablesaw.aggregate.AggregateFunctions.sum;

// 篩選後依語言彙總銷量
Table bestsellersByLanguage = books
        // 僅選 1950 年後的書籍
        .where(books.intColumn("First_Published").isGreaterThan(1950))

         // 計算總銷量
        .summarize("Sales_in_Millions", sum)

         // 依語言分組
        .by("Language");
System.out.println("Modern bestsellers (post-1950) by language:\n");
System.out.println(bestsellersByLanguage);

結合篩選與彙總：輸出

Modern bestsellers (post-1950) by language:

| Language   | Sum [Sales_in_Millions] |
|------------|-------------------------|
| English    | 1071                    |
| Portuguese | 65                      |
| Spanish    | 50                      |
| Italian    | 50                      |

綜合運用

books.where(sales.isGreaterThan(150)); // 基本：單一條件篩選

books.where(highSales.and(english)); // 多條件：用 .and() 結合

// 基本彙總
books.summarize("Sales_in_Millions", mean)       // 計算平均
    .by("Language");                             // 依語言分組

// 組合操作
books.where(books.intColumn("First_Published").isGreaterThan(1950)) // 先篩選
    .summarize("Sales_in_Millions", mean) // 再彙總
    .by("Language"); // 最後分組

一起來練習吧！

使用 Java 進行資料清理