Pembersihan dan standarisasi kolom

Pembersihan Data di Java

Dennis Lee

Software Engineer

Dataset penjualan buku

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

Memeriksa tipe data

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()) {

    // Cetak nama kolom dan tipenya
    System.out.println(colName + ": " + books.column(colName).type());
}

Memeriksa tipe data: output

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

Mengonversi format numerik

// Ubah IntColumn ke DoubleColumn karena Tablesaw menafsirkannya sebagai INTEGER
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

// Dimungkinkan mengubah DoubleColumn ke IntColumn
IntColumn salesAsInt = sales.asIntColumn();

Mengalikan kolom

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

// Hitung total pendapatan dengan mengalikan dua kolom
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());

Hasil pengalian kolom

Mengalikan dua kolom: 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           |

Regex untuk mencocokkan tanda kurung

Pola \\(.*\\) mencocokkan tanda kurung
Contoh kecocokan: "(Organic)", "(2 pack)", "(Large)"
Hasil: "Apple (Organic)" menjadi "Apple"

\\(     // Cocokkan kurung buka (di-escape dengan \)
.*      // Cocokkan karakter apa pun (.) nol kali atau lebih (*)
\\)     // Cocokkan kurung tutup (di-escape dengan \)

Menangani karakter khusus

// Hapus terjemahan dalam tanda kurung dari judul
StringColumn titlesNoParentheses = titles
        // Untuk tiap judul di titles, hapus tanda kurung dan spasi sekitarnya
        .map(t -> t.replaceAll("\\(.*\\)", "").trim())
        // Atur nama kolom baru
        .setName("Titles without parentheses");

System.out.println("Original: " + titles.get(1)); // Ambil item kolom pertama
System.out.println("Cleaned: " + titlesNoParentheses.get(1));

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

Menggabungkan operasi string

// Gabungkan penghapusan tanda kurung dan konversi ke huruf kecil
StringColumn titlesCombinedCleaning =
        titles.map(t -> t.replaceAll("\\(.*\\)", "") // Hapus tanda kurung

                         .trim() // Hapus spasi sekitar

                         .toLowerCase()) // Ubah ke huruf kecil
              .setName("Clean_Titles_Combined"); // Atur nama kolom

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

Menyatukan semuanya

books.column(colName).type(); // Dapatkan tipe kolom
books.intColumn("Sales_in_Millions").asDoubleColumn(); // Konversi tipe

DoubleColumn totalRevenue = sales.multiply(averagePrice) // Kalikan kolom
    .setName("Total_Revenue_Millions"); // Nama kolom baru

titles.map(String::toLowerCase) // Ubah ke huruf kecil
    .setName("Lowercase_Titles"); // Nama kolom baru

titles.map(t -> t.replaceAll("\\(.*\\)", "").trim()) // Hapus tanda kurung
    .setName("Titles without parentheses"); // Nama kolom baru

Ayo berlatih!

Pembersihan Data di Java