How to Clean Messy CSV Data (A Practical Checklist)

Thứ Ba vừa rồi, tôi đã chứng kiến một nhà phân tích junior dành bốn giờ đồng hồ chỉnh sửa thủ công một tệp CSV mà lẽ ra chỉ mất hai mươi phút để làm sạch. Cô ấy đã sao chép và dán từng ô một, cố gắng sửa các định dạng ngày đã được xuất từ ba hệ thống khác nhau. Khi cô ấy hoàn thành, đôi mắt cô đã đờ đẫn, và tệp vẫn còn những sự không nhất quán mà cô chưa phát hiện ra. Tôi đã là một kỹ sư dữ liệu trong mười hai năm, và tôi thấy kịch bản này diễn ra liên tục—những người thông minh lãng phí cả buổi chiều trên những tệp CSV rối rắm vì họ không có cách tiếp cận hệ thống.

Sự thật là, tệp CSV vừa là định dạng dữ liệu phổ biến nhất vừa là định dạng rắc rối nhất hiện có. Mọi hệ thống đều có thể xuất chúng, mọi công cụ đều có thể nhập chúng, và tuy nhiên chúng là một bãi mìn của các vấn đề mã hóa, sự nhầm lẫn về ký tự phân cách, và các không nhất quán cấu trúc. Theo một khảo sát năm 2023 của Viện Chất lượng Dữ liệu, các chuyên gia dữ liệu dành trung bình 19,2 giờ mỗi tuần cho các nhiệm vụ làm sạch dữ liệu, với việc xử lý CSV chiếm khoảng 40% thời gian đó. Đó gần như là tám giờ mỗi tuần—một ngày làm việc hoàn toàn—bị lãng phí để sửa chữa những vấn đề có thể phòng tránh.

Tôi là Marcus Chen, và tôi đã dành thập kỷ vừa qua để xây dựng các pipeline dữ liệu cho các công ty, từ những startup thông minh đến các doanh nghiệp Fortune 500. Tôi đã xử lý hàng tỷ hàng dữ liệu CSV, và tôi đã thấy mọi cách mà những tệp này có thể hỏng. Điều tôi đã học được là làm sạch dữ liệu CSV rối rắm không phải là vấn đề thông minh—mà là vấn đề hệ thống. Bạn cần một danh sách kiểm tra, một quy trình có thể lặp lại để phát hiện các vấn đề trước khi chúng lan rộng vào phân tích hoặc cơ sở dữ liệu của bạn. Bài viết này là danh sách kiểm tra đó, được chắt lọc từ hàng nghìn giờ thí nghiệm thực tế với dữ liệu.

Hiểu điều gì làm cho tệp CSV trở nên có vấn đề

Trước khi chúng ta đi vào quy trình làm sạch, bạn cần phải hiểu tại sao tệp CSV gây ra nhiều đau đầu như vậy. Định dạng này có vẻ đơn giản đáng ngờ: các giá trị được ngăn cách bởi dấu phẩy, một hàng trên mỗi dòng. Nhưng sự đơn giản đó chính là vấn đề. Không giống như các định dạng có cấu trúc như JSON hay XML, CSV không có quy định chính thức nào mà mọi người đều tuân thủ. Tiêu chuẩn RFC 4180 tồn tại, nhưng nó giống như một gợi ý hơn là một quy tắc, và hầu hết các hệ thống hoàn toàn bỏ qua nó.

Tôi từng nhận một tệp CSV từ hệ thống kế toán cũ của một khách hàng mà sử dụng dấu chấm phẩy làm ký tự phân cách, có dấu phẩy trong các giá trị số (như "1,234.56"), sử dụng cả dấu nháy đơn và dấu nháy đôi cho ra ngoài văn bản, và trộn lẫn các kết thúc dòng Windows và Unix trong cùng một tệp. Tôi đã mất ba giờ chỉ để phân tích nó chính xác, và đó là với các công cụ chuyên nghiệp. Tệp đó đã hoạt động "tốt" trong hệ thống của họ trong nhiều năm vì phần mềm của họ có logic tùy chỉnh để xử lý tất cả những đặc điểm này.

Các vấn đề chính với tệp CSV rơi vào một số danh mục. Đầu tiên, có vấn đề ký tự phân cách—dấu phẩy là phổ biến trong dữ liệu, vì vậy các hệ thống sử dụng tab, ống, dấu chấm phẩy, hoặc các ký tự khác, nhưng họ không phải lúc nào cũng cho bạn biết ký tự nào. Thứ hai, có văn bản ra ngoài—khi nào bạn cần dấu nháy quanh các giá trị, và điều gì xảy ra khi dữ liệu của bạn chứa các ký tự dấu nháy? Thứ ba, có vấn đề mã hóa—đây có phải là UTF-8, Latin-1, Windows-1252, hay một cái gì đó hoàn toàn khác? Nếu làm sai, bạn sẽ thấy các ký tự rác trong khi đáng ra đó là các chữ cái có dấu.

Rồi còn những vấn đề cấu trúc. Tệp CSV không có phương pháp tích hợp để đại diện cho kiểu dữ liệu, vì vậy mọi thứ đều là văn bản cho đến khi bạn phân tích chúng. Một ngày có thể là "2024-01-15", "01/15/2024", "15-Jan-24", hoặc "January 15, 2024"—và đôi khi cả bốn định dạng đều xuất hiện trong cùng một cột vì những người dùng khác nhau đã nhập dữ liệu khác nhau. Số có thể có ký hiệu tiền tệ, dấu phần trăm, hoặc ký tự phân cách hàng ngàn. Giá trị boolean có thể là "true/false", "yes/no", "1/0", "Y/N", hoặc bất kỳ sự kết hợp nào của chúng.

Điều tồi tệ nhất là nhiều vấn đề CSV là im lặng. Công cụ nhập của bạn có thể thành công mà không có lỗi, nhưng dữ liệu của bạn bị hỏng một cách tinh vi. Tôi đã thấy những trường hợp mà các số 0 đứng đầu đã bị loại khỏi mã sản phẩm, biến "00123" thành "123" và làm hỏng hệ thống hàng tồn kho. Tôi đã thấy ngày tháng được diễn giải không đúng bởi vì hệ thống giả định MM/DD/YYYY trong khi tệp sử dụng DD/MM/YYYY, khiến cho các đơn hàng được lên lịch cho những ngày không thể xảy ra như ngày 31 tháng 2. Những lỗi này không tự thông báo—they ẩn mình trong dữ liệu của bạn cho đến khi ai đó nhận thấy rằng các số không khớp nhau.

Bước Một: Kiểm tra trước khi bạn nhập

Sai lầm lớn nhất mà mọi người mắc phải với tệp CSV là mở chúng trực tiếp trong Excel hoặc nhập chúng ngay lập tức vào cơ sở dữ liệu. Đừng làm điều này. Bước đầu tiên của bạn luôn nên là kiểm tra bằng một trình soạn thảo văn bản hoặc công cụ dòng lệnh. Tôi sử dụng một sự kết hợp của head, tail, và wc -l trên các hệ thống Unix, hoặc một trình soạn thảo văn bản lập trình như VS Code hoặc Sublime Text có thể xử lý các tệp lớn mà không bị nghẹt.

"Tệp CSV là gián của các định dạng dữ liệu—chúng sống sót qua mọi thứ, hoạt động ở khắp nơi, và gây ra những vấn đề bạn không bao giờ mong đợi."

Bắt đầu bằng cách nhìn vào 20-30 dòng đầu tiên của tệp. Điều này cho bạn biết ký tự phân cách, có hàng tiêu đề hay không, và cấu trúc chung trông như thế nào. Nhưng đừng dừng lại ở đó—cũng hãy nhìn vào 20-30 dòng cuối cùng. Tôi không thể đếm bao nhiêu lần tôi đã phát hiện rằng một tệp bắt đầu sạch sẽ nhưng kết thúc với rác: các hàng không đầy đủ, thông báo lỗi từ quy trình xuất, hoặc các thống kê tóm tắt mà ai đó một cách hữu ích đã thêm vào dữ liệu. Hệ thống ERP của một khách hàng đã thêm một chân trang với "Tổng số bản ghi: 45,892" ở cuối mỗi lần xuất, điều này sẽ làm sập bất kỳ quy trình nhập ngu ngốc nào.

Kiểm tra số lượng dòng với wc -l filename.csv và so sánh với những gì bạn mong đợi. Nếu tệp mà bạn thấy có 10,000 bản ghi cộng với một tiêu đề, bạn sẽ thấy 10,001 dòng. Nếu bạn nhìn thấy 10,247 dòng, có điều gì đó không ổn—có thể có các dòng nằm trong trường văn bản. Điều này cực kỳ phổ biến trong các tệp CSV chứa nội dung do người dùng tạo ra như bình luận hoặc mô tả. Ai đó viết "Sản phẩm này thật tuyệt\nRất đáng khuyên" và bỗng dưng bạn có một dòng ngắt ở giữa một hàng.

Tìm ký tự phân cách bằng cách kiểm tra một vài dòng đầu tiên. Đếm các dấu phẩy, tab, ống, hoặc dấu chấm phẩy trong mỗi hàng. Chúng nên nhất quán. Nếu hàng một có 12 dấu phẩy và hàng hai có 15, bạn có một vấn đề—hoặc là ký tự phân cách sai, hoặc bạn có các trường văn bản không được xác định chứa ký tự phân cách. Tôi đã từng dành một giờ để gỡ lỗi một tệp trước khi nhận ra nó sử dụng ký tự ống (|) làm ký tự phân cách, cái mà gần như vô hình trong phông chữ terminal của tôi.

Kiểm tra mã hóa bằng cách tìm ký tự không phải ASCII. Nếu bạn thấy các ký hiệu kỳ lạ như ’ thay vì dấu nháy đơn, hoặc é thay vì é, bạn đã gặp một sự không tương thích mã hóa. Tệp có khả năng là UTF-8 nhưng đang được đọc như Latin-1, hoặc ngược lại. Sử dụng một công cụ như file -i filename.csv trên Unix để phát hiện mã hóa, hoặc sử dụng tính năng phát hiện mã hóa của trình soạn thảo văn bản của bạn. Làm sai điều này từ sớm có nghĩa là mọi trường văn bản trong dữ liệu của bạn sẽ bị hỏng.

Bước Hai: Xác thực cấu trúc

Khi bạn đã kiểm tra tệp một cách thủ công, bước tiếp theo là xác thực cấu trúc. Mỗi hàng nên có số trường giống nhau, và số đó nên khớp với hàng tiêu đề của bạn. Điều này nghe có vẻ hiển nhiên, nhưng nó thường bị vi phạm trong dữ liệu thực tế. Tôi sử dụng Python với mô-đun csv hoặc pandas để thực hiện việc xác thực này một cách lập trình, bởi vì việc kiểm tra hàng nghìn hàng bằng mắt là không thể.

Đây là một đoạn mã Python đơn giản mà tôi chạy trên mỗi tệp CSV tôi nhận được: nó đếm số trường trong mỗi hàng và báo cáo bất kỳ trường nào không khớp với tiêu đề. Trong một dự án gần đây, đoạn mã này đã phát hiện rằng 347 trên tổng số 50,000 hàng có thêm trường vì quy trình xuất có một lỗi đã nhân đôi cột cuối cùng trong một số điều kiện nhất định. Nếu không có kiểm tra này, những hàng đó sẽ bị hỏng một cách âm thầm trong khi nhập, và dữ liệu thêm sẽ bị cắt ngắn hoặc đẩy vào các cột sai.

Chú ý đặc biệt đến các trường được bao quanh bởi dấu nháy. CSV sử dụng dấu nháy để cho phép các ký tự phân cách và dòng ngắt nằm trong giá trị trường, nhưng các quy tắc bao quanh khá phức tạp và thường được thực hiện không chính xác. Một trường như "Smith, John" được bao quanh đúng cách, nhưng còn "He said "hello" to me"? Định dạng đúng là "He said ""hello"" to me" với dấu nháy đôi, nhưng nhiều hệ thống đã sai và sản xuất "He said "hello" to me", điều này làm hỏng việc phân tích.

Kiểm tra các hàng trống, thường xuất hiện ở cuối các tệp hoặc giữa các phần dữ liệu. Những điều này nên