Data Cleaning Tips: Fix Messy Spreadsheets Fast — csv-x.com

Selasa lalu, saya melihat seorang analis junior menghabiskan empat jam untuk secara manual memperbaiki basis data pelanggan yang terdiri dari 50.000 baris yang seharusnya memakan waktu dua puluh menit. Dia menyalin dan menempel sel individu, menyipitkan mata pada format tanggal yang tidak konsisten, dan bergumam tentang "siapa pun yang mengekspor kekacauan ini." Saya telah menjadi konsultan operasi data selama dua belas tahun, dan saya melihat skenario ini terjadi setidaknya dua kali seminggu di perusahaan-perusahaan Fortune 500 yang saya kerjakan. Kebenarannya? Sebagian besar profesional tidak pernah diajarkan teknik pembersihan data yang tepat, dan ini mengakibatkan biaya yang diperkirakan $3,1 triliun setiap tahun dalam produktivitas yang hilang dan keputusan buruk yang berdasarkan pada data kotor.

Saya Sarah Chen, dan saya telah menghabiskan lebih dari satu dekade membersihkan bencana data untuk perusahaan mulai dari startup kecil hingga perusahaan multinasional. Spesialisasi saya adalah mengubah spreadsheet yang kacau menjadi kumpulan data siap analisis yang bersih—dengan cepat. Apa yang saya pelajari adalah bahwa pembersihan data bukan tentang kesempurnaan; itu tentang efisiensi dan mengetahui pertempuran mana yang perlu diperjuangkan. Hari ini, saya berbagi kerangka kerja tepat yang saya gunakan untuk mengubah file CSV yang kotor menjadi data bersih dalam waktu yang jauh lebih singkat daripada yang dihabiskan kebanyakan orang untuk berjuang.

Biaya Nyata dari Data Kotor (Dan Kenapa Kecepatan Itu Penting)

Sebelum kita menyelami teknik, mari kita bicarakan mengapa ini penting. Pada tahun 2019, saya bekerja dengan sebuah perusahaan e-commerce menengah yang membuat keputusan inventaris berdasarkan spreadsheet penjualan dengan entri duplikat. Mereka tidak menyadari bahwa produk "terlaris" mereka sebenarnya muncul tiga kali dalam data mereka dengan format SKU yang sedikit berbeda. Hasilnya? Mereka mengisi terlalu banyak sebesar 340% dan mengikat $1,2 juta dalam modal untuk produk yang tergeletak di gudang selama delapan bulan.

Ini bukan insiden tersendiri. Menurut penelitian dari IBM, kualitas data yang buruk menelan biaya ekonomi AS sekitar $3,1 triliun per tahun. Tetapi inilah apa yang tidak akan diberitahu oleh kebanyakan artikel: biaya terbesar bukanlah keputusan buruk—itu adalah waktu yang terbuang. Ketika saya mengaudit alur kerja data perusahaan, saya biasanya menemukan bahwa analis menghabiskan 60-80% dari waktu mereka untuk persiapan data ketimbang analisis yang sebenarnya. Itu bukan karena datanya sangat kompleks; itu karena mereka menggunakan metode yang tidak efisien.

Kecepatan itu penting karena data bersih memiliki masa pakai. Pada saat Anda secara manual memperbaiki 50.000 baris, konteks bisnis mungkin telah berubah. Kampanye pemasaran yang Anda analisis mungkin sudah berakhir. Batas waktu laporan triwulanan mungkin sudah terlewat. Pembersihan data yang cepat bukan tentang memotong sudut—itu tentang menjaga relevansi dan benar-benar mendapatkan wawasan yang mendorong keputusan.

Perusahaan-perusahaan yang saya kerjakan yang telah mengadopsi pendekatan pembersihan data yang sistematis dan dibantu alat melaporkan penghematan waktu sebesar 70-85% pada tugas persiapan data rutin. Yang lebih penting, mereka melaporkan membuat keputusan 3-4 minggu lebih cepat daripada pesaing yang masih terjebak dalam mode pembersihan manual. Dalam industri yang bergerak cepat, keuntungan waktu itu langsung diterjemahkan ke dalam pangsa pasar.

Diagnosis Sebelum Pengobatan: Memindai Lanskap Data Anda

Kesalahan terbesar yang saya lihat orang lakukan adalah langsung terjun ke pembersihan tanpa memahami apa yang mereka hadapi. Ini seperti seorang dokter yang meresepkan obat tanpa memeriksa pasien. Saya belajar pelajaran ini dengan cara yang sulit di tahun ketiga saya sebagai konsultan ketika saya menghabiskan enam jam memperbaiki format tanggal dalam satu set data, hanya untuk menemukan bahwa masalah sebenarnya adalah catatan duplikat yang membuat pembersihan tanggal saya menjadi tidak relevan sama sekali.

"Pembersihan data bukan tentang kesempurnaan; itu tentang efisiensi dan mengetahui pertempuran mana yang perlu diperjuangkan. Tujuannya adalah data siap analisis, bukan data yang sempurna."

Sekarang, saya selalu memulai dengan pemindaian sistematis. Saya membuka file CSV dan menghabiskan tepat lima menit untuk melakukan penilaian terstruktur. Pertama, saya memeriksa jumlah baris—apakah ini 500 baris atau 500.000? Pendekatan berbeda secara dramatis. Kemudian saya memindai header kolom. Apakah mereka deskriptif? Konsisten? Apakah saya melihat masalah yang jelas seperti "Kolom1" atau baris header yang digabung?

Selanjutnya, saya melihat tipe data. Saya menggulir dan mengidentifikasi kolom mana yang seharusnya berupa teks, mana yang berupa angka, dan mana yang seharusnya berupa tanggal. Saya mencatat secara mental kolom yang tampaknya mencampurkan tipe—seperti kolom "nomor telepon" yang memiliki beberapa entri sebagai angka dan yang lain sebagai teks dengan format. Kolom campuran ini adalah tanda bahaya yang akan menyebabkan masalah ke depan.

Saya juga melakukan apa yang saya sebut "pemindaian tepi"—saya melihat 10 baris pertama, 10 baris terakhir, dan sampel acak di tengah. Kenapa? Karena masalah kualitas data seringkali terkelompok. Saya pernah menemukan satu set data di mana 5.000 baris pertama bersih, tetapi baris 5.001 dan seterusnya memiliki format yang sama sekali berbeda karena mereka berasal dari sistem sumber yang berbeda. Jika saya hanya memeriksa bagian atas, saya akan melewatkan masalah yang besar.

Terakhir, saya mengidentifikasi kolom "jalur kritis"—3-5 kolom yang benar-benar harus bersih agar analisis bisa berjalan. Dalam basis data pelanggan, ini mungkin berupa ID pelanggan, email, dan tanggal pembelian. Dalam katalog produk, ini mungkin berupa SKU, harga, dan kategori. Saya memfokuskan upaya pembersihan saya pada kolom-kolom ini terlebih dahulu. Anda jarang memerlukan setiap kolom untuk sempurna; Anda memerlukan kolom yang tepat untuk sempurna.

Kerangka Deteksi Duplikat yang Sebenarnya Berfungsi

Duplikat adalah pembunuh diam dari analisis data. Mereka membesar-besarkan penghitungan, memiringkan rata-rata, dan menciptakan tren hantu. Saya telah melihat tim pemasaran merayakan "peningkatan 40% dalam pelanggan baru" yang sebenarnya hanyalah entri duplikat dari migrasi sistem. Tantangannya adalah bahwa duplikat jarang mengumumkan diri mereka—mereka bersembunyi dalam variasi halus.

Duplikat sejati mudah: dua baris yang 100% identik. Kebanyakan alat spreadsheet dapat menemukan ini secara otomatis. Tetapi dalam dua belas tahun, saya jarang menemui kumpulan data di mana duplikatnya begitu jelas. Sebaliknya, saya berurusan dengan apa yang saya sebut "duplikat kabur"—catatan yang mewakili entitas yang sama tetapi memiliki perbedaan kecil.

Pertimbangkan catatan pelanggan. "John Smith" dan "John Smith" mungkin merupakan orang yang sama, tetapi demikian juga "John Smith" dan "J. Smith" serta "Smith, John" dan "john smith" (huruf kecil). Pengecekan duplikat yang naïf akan melewatkan semua ini. Kerangka kerja saya melibatkan pembuatan "kunci pencocokan"—versi standar dari data yang digunakan murni untuk perbandingan.

Untuk nama, saya membuat kunci pencocokan dengan mengubah menjadi huruf kecil, menghapus semua tanda baca dan spasi ekstra, serta mengurutkan kata-kata secara alfabetis. Jadi "Smith, John" menjadi "john smith" dan "John Smith" juga menjadi "john smith"—sekarang mereka cocok. Untuk alamat, saya menghapus nomor apartemen, mengubah singkatan jalan menjadi kata-kata penuh, dan menghilangkan tanda baca. Untuk nomor telepon, saya menghapus semua format dan hanya menyimpan angka.

Poin kunci adalah bahwa Anda tidak memodifikasi data asli Anda—Anda membuat kolom pencocokan sementara, menemukan duplikat berdasarkan kolom itu, lalu menghapus kolom pencocokan setelah Anda mengidentifikasi dan menangani duplikat. Ini menjaga data asli Anda tetap utuh sambil memberikan Anda kekuatan untuk menemukan duplikat yang tersembunyi.

Saya juga menggunakan apa yang saya sebut "pencocokan probabilistik" untuk kumpulan data besar. Alih-alih mendeklarasikan dua catatan sebagai duplikat hanya jika mereka cocok persis, saya memberikan skor kesamaan. Jika dua catatan pelanggan cocok pada email dan telepon tetapi sedikit berbeda dalam nama, mereka mendapatkan skor kesamaan 90%. Saya kemudian meninjau secara manual segala sesuatu di atas skor kesamaan 80%—ini biasanya menangkap 95% duplikat sambil mengharuskan saya meninjau hanya 2-3% catatan secara manual.

Menjinakkan Binatang Format Tanggal

Jika saya memiliki satu dolar untuk setiap jam yang saya habiskan untuk memperbaiki format tanggal, saya bisa pensiun. Tanggal sangat kompleks karena terlihat sederhana. Tetapi "01/02/2023" berarti 2 Januari di AS dan 1 Februari di Eropa. "2023-01-02" tidak ambigu tetapi mungkin disimpan sebagai teks alih-alih tanggal. Dan jangan membuat saya mulai tentang kebiasaan Excel yang mengubah tanggal menjadi angka seri.

"Kualitas data yang buruk menyebabkan ekonomi AS $3,1 triliun setiap tahun, tetapi pembunuh sebenarnya adalah pajak tersembunyi: analis menghabiskan 60-80% dari waktu mereka membersihkan alih-alih menganalisis."

Aturan pertama pembersihan tanggal adalah mengidentifikasi format apa yang Anda mulai. Saya membuat sampel kecil—mungkin 20 baris—dan mencari pola. Apakah semua tanggal dalam format yang sama? Apakah ada outlier yang jelas seperti "N/A" atau "TBD" yang bercampur? Apakah saya melihat tanggal yang jelas salah, seperti "01/01/1900" (sering menjadi nilai default) atau tanggal di masa depan ketika seharusnya merupakan sejarah?