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

Vào thứ Ba tuần trước, tôi đã chứng kiến một nhân viên phân tích cấp dưới phải mất bốn giờ để sửa thủ công một cơ sở dữ liệu khách hàng 50.000 dòng lẽ ra chỉ mất hai mươi phút. Cô ấy đang sao chép và dán từng ô, nheo mắt nhìn vào các định dạng ngày không nhất quán, và lầm bầm về "người nào xuất ra mớ hỗn độn này." Tôi đã là một tư vấn viên về hoạt động dữ liệu suốt mười hai năm, và tôi thấy tình huống này diễn ra ít nhất hai lần mỗi tuần ở các công ty thuộc Fortune 500 mà tôi làm việc cùng. Sự thật? Hầu hết các chuyên gia không bao giờ được dạy các kỹ thuật làm sạch dữ liệu đúng cách, và điều này đang khiến các doanh nghiệp tiêu tốn khoảng 3,1 nghìn tỷ USD mỗi năm vì mất năng suất và quyết định sai lầm dựa trên dữ liệu bẩn.

Tôi là Sarah Chen, và tôi đã dành hơn một thập kỷ để giải quyết các thảm họa dữ liệu cho các công ty từ các startup nhỏ cho đến các tập đoàn đa quốc gia. Chuyên môn của tôi là biến những bảng tính hỗn loạn thành các tập dữ liệu sạch sẽ, sẵn sàng cho phân tích—một cách nhanh chóng. Điều tôi học được là làm sạch dữ liệu không phải là hoàn hảo; mà là hiệu quả và biết điều nào cần chiến đấu. Hôm nay, tôi sẽ chia sẻ khung làm sạch dữ liệu chính xác mà tôi sử dụng để biến các file CSV lộn xộn thành dữ liệu sạch trong một khoảng thời gian ngắn trong khi hầu hết mọi người vẫn phải vật lộn.

Chi Phí Thực Tế Của Dữ Liệu Lộn Xộn (Và Tại Sao Tốc Độ Quan Trọng)

Trước khi chúng ta đi vào các kỹ thuật, hãy nói về lý do tại sao điều này quan trọng. Năm 2019, tôi đã làm việc với một công ty thương mại điện tử vừa và nhỏ, công ty này đã đưa ra các quyết định về hàng tồn kho dựa trên một bảng tính bán hàng có các mục trùng lặp. Họ không nhận ra rằng sản phẩm "bán chạy nhất" của họ thực sự xuất hiện ba lần trong dữ liệu của họ dưới những định dạng SKU hơi khác nhau. Kết quả là? Họ đã tồn kho vượt quá 340% và đã giữ $1,2 triệu vốn cho những sản phẩm đã nằm trong kho suốt tám tháng.

Đây không phải là một sự cố đơn lẻ. Theo nghiên cứu từ IBM, chất lượng dữ liệu kém đã tiêu tốn khoảng $3,1 nghìn tỷ mỗi năm cho nền kinh tế Hoa Kỳ. Nhưng đây là điều mà hầu hết các bài viết sẽ không nói với bạn: chi phí lớn nhất không phải là những quyết định xấu—mà là thời gian lãng phí. Khi tôi kiểm toán quy trình làm việc của dữ liệu của các công ty, tôi thường thấy rằng các nhà phân tích dành 60-80% thời gian của họ vào việc chuẩn bị dữ liệu thay vì phân tích thực sự. Điều đó không phải vì dữ liệu quá phức tạp; mà là vì họ đang sử dụng các phương pháp không hiệu quả.

Tốc độ quan trọng vì dữ liệu sạch có thời hạn sử dụng. Khi bạn đã sửa thủ công 50.000 dòng, bối cảnh kinh doanh có thể đã thay đổi. Chiến dịch tiếp thị mà bạn đang phân tích có thể đã kết thúc. Hạn chót cho báo cáo quý có thể đã qua. Làm sạch dữ liệu nhanh chóng không phải là cắt bớt; mà là duy trì tính phù hợp và thực sự الوصول đến các thông tin để điều hành quyết định.

Các công ty mà tôi làm việc cùng đã áp dụng các phương pháp làm sạch dữ liệu có hệ thống, hỗ trợ công cụ thường báo cáo tiết kiệm thời gian từ 70-85% cho các tác vụ chuẩn bị dữ liệu thường xuyên. Quan trọng hơn, họ báo cáo rằng mình đưa ra quyết định nhanh hơn 3-4 tuần so với các đối thủ vẫn đang mắc kẹt trong chế độ làm sạch thủ công. Trong các ngành công nghiệp chuyển động nhanh, lợi thế về thời gian đó trực tiếp chuyển thành thị phần.

Chẩn Đoán Trước Khi Điều Trị: Quét Lãnh Thổ Dữ Liệu Của Bạn

Sai lầm lớn nhất mà tôi thấy mọi người mắc phải là lao vào làm sạch mà không hiểu rõ điều mình đang đối mặt. Nó giống như một bác sĩ kê thuốc mà không kiểm tra bệnh nhân. Tôi đã học bài học này theo cách khó khăn trong năm thứ ba làm tư vấn viên khi tôi đã dành sáu giờ để sửa định dạng ngày trong một tập dữ liệu, chỉ để phát hiện ra rằng vấn đề thực sự là các bản ghi trùng lặp đã làm cho việc làm sạch ngày của tôi trở nên hoàn toàn không liên quan.

"Làm sạch dữ liệu không phải là về sự hoàn hảo; mà là về hiệu quả và biết điều nào cần chiến đấu. Mục tiêu là dữ liệu sẵn sàng cho phân tích, không phải dữ liệu hoàn hảo."

Bây giờ, tôi luôn bắt đầu với một quét có hệ thống. Tôi mở file CSV và dành đúng năm phút để thực hiện một đánh giá có cấu trúc. Đầu tiên, tôi kiểm tra số lượng dòng—đây có phải là 500 dòng hay 500.000? Cách tiếp cận sẽ khác nhau rất nhiều. Sau đó, tôi quét các tiêu đề cột. Chúng có mô tả không? Có nhất quán không? Tôi có thấy các vấn đề rõ ràng như "Cột1" hoặc các hàng tiêu đề merged không?

Tiếp theo, tôi nhìn vào các loại dữ liệu. Tôi cuộn qua và xác định cột nào nên là văn bản, cột nào nên là số và cột nào nên là ngày. Tôi ghi nhớ các cột nào có vẻ trộn lẫn loại—như một cột "số điện thoại" mà có một số mục là số và những mục khác là văn bản với định dạng. Những cột hỗn hợp này là dấu hiệu đỏ sẽ gây ra vấn đề sau này.

Tôi cũng thực hiện điều mà tôi gọi là "quét cạnh"—tôi nhìn vào 10 dòng đầu tiên, 10 dòng cuối cùng và một mẫu ngẫu nhiên ở giữa. Tại sao? Bởi vì các vấn đề chất lượng dữ liệu thường tập trung lại. Tôi đã từng tìm thấy một tập dữ liệu nơi 5.000 dòng đầu tiên hoàn hảo, nhưng từ dòng 5.001 trở đi lại có định dạng hoàn toàn khác vì chúng đến từ một hệ thống nguồn khác. Nếu tôi chỉ kiểm tra phần trên cùng, tôi sẽ bỏ lỡ một vấn đề lớn.

Cuối cùng, tôi xác định các cột "đường đi quan trọng"—3-5 cột mà phải sạch để phân tích hoạt động. Trong một cơ sở dữ liệu khách hàng, điều này có thể là ID khách hàng, email và ngày mua. Trong một danh mục sản phẩm, có thể là SKU, giá và loại. Tôi tập trung nỗ lực làm sạch vào những cột này trước tiên. mà bạn hiếm khi cần mọi cột phải hoàn hảo; bạn cần các cột đúng phải hoàn hảo.

Khung Phát Hiện Bản Sao Thực Sự Hiệu Quả

Các bản sao là những kẻ giết người thầm lặng của phân tích dữ liệu. Chúng làm tăng số lần đếm, làm sai lệch trung bình và tạo ra các xu hướng ảo. Tôi đã thấy các nhóm tiếp thị ăn mừng một "sự gia tăng 40% trong số khách hàng mới" trên thực tế chỉ là các mục trùng lặp từ một lần di chuyển hệ thống. Thách thức là các bản sao hiếm khi tự công bố—chúng ẩn mình trong những biến thể tinh tế.

Các bản sao thực sự rất dễ: hai dòng hoàn toàn giống nhau. Hầu hết các công cụ bảng tính có thể tìm thấy chúng tự động. Nhưng trong suốt mười hai năm, tôi hiếm khi gặp các tập dữ liệu nơi các bản sao rõ ràng như vậy. Thay vào đó, tôi làm việc với những gì tôi gọi là "bản sao mờ"—các bản ghi đại diện cho cùng một thực thể nhưng có sự khác biệt nhỏ.

Xem xét các bản ghi khách hàng. "John Smith" và "John Smith" có thể là cùng một người, nhưng "John Smith" và "J. Smith" và "Smith, John" và "john smith" (chữ thường) cũng có thể là như nhau. Một bài kiểm tra trùng lặp ngây thơ sẽ bỏ lỡ tất cả những điều này. Khung của tôi bao gồm việc tạo ra một "khóa đối sánh"—một phiên bản tiêu chuẩn của dữ liệu được sử dụng chỉ để so sánh.

Đối với các tên, tôi tạo ra một khóa đối sánh bằng cách chuyển sang chữ thường, loại bỏ tất cả dấu câu và khoảng trắng thừa, và sắp xếp các từ theo thứ tự bảng chữ cái. Vì vậy "Smith, John" trở thành "john smith" và "John Smith" cũng trở thành "john smith"—bây giờ chúng khớp nhau. Đối với địa chỉ, tôi loại bỏ các số căn hộ, chuyển các chữ viết tắt của đường phố thành các từ đầy đủ, và loại bỏ dấu câu. Đối với số điện thoại, tôi loại bỏ tất cả các định dạng và chỉ giữ lại các chữ số.

Sự hiểu biết chính là bạn không thay đổi dữ liệu gốc của mình—bạn tạo ra một cột đối sánh tạm thời, tìm các bản sao dựa trên cột đó, sau đó xóa cột đối sánh sau khi bạn đã xác định và xử lý các bản sao. Điều này bảo tồn dữ liệu gốc của bạn trong khi cho phép bạn tìm thấy các bản sao ẩn.

Tôi cũng sử dụng điều mà tôi gọi là "đối sánh xác suất" cho các tập dữ liệu lớn. Thay vì tuyên bố rằng hai bản ghi là bản sao chỉ khi chúng hoàn toàn khớp nhau, tôi gán một điểm tương đồng. Nếu hai bản ghi khách hàng khớp nhau về email và số điện thoại nhưng khác nhau một chút về tên, chúng sẽ nhận được điểm tương đồng 90%. Sau đó, tôi sẽ kiểm tra thủ công bất cứ điều gì trên 80% tương đồng—điều này thường bắt được 95% các bản sao trong khi yêu cầu tôi chỉ kiểm tra 2-3% các bản ghi một cách thủ công.

Chế Ngự Quái Vật Định Dạng Ngày

Nếu tôi có một đô la cho mỗi giờ tôi đã dành để sửa định dạng ngày, tôi có thể nghỉ hưu. Các định dạng ngày thực sự rất phức tạp vì chúng trông đơn giản. Nhưng "01/02/2023" có nghĩa là ngày 2 tháng 1 ở Mỹ và ngày 1 tháng 2 ở châu Âu. "2023-01-02" thì không mơ hồ nhưng có thể được lưu trữ dưới dạng văn bản thay vì là ngày. Và đừng để tôi bắt đầu với thói quen của Excel trong việc chuyển đổi ngày thành số tuần tự.

"Chất lượng dữ liệu kém tiêu tốn khoảng $3,1 nghìn tỷ mỗi năm cho nền kinh tế Hoa Kỳ, nhưng kẻ giết người thực sự là thuế ẩn: các nhà phân tích dành 60-80% thời gian của họ để làm sạch dữ liệu thay vì phân tích."

Quy tắc đầu tiên của làm sạch định dạng ngày là xác định định dạng mà bạn đang bắt đầu. Tôi tạo ra một mẫu nhỏ—có thể 20 dòng—và tìm kiếm các mẫu. Tất cả các ngày có cùng định dạng không? Có bất kỳ ngoại lệ rõ ràng nào như "N/A" hoặc "TBD" lẫn vào không? Tôi có thấy bất kỳ ngày nào rõ ràng là sai, như "01/01/1900" (thường là giá trị mặc định) hoặc các ngày trong tương lai khi chúng nên là lịch sử không?