Hai bài đăng blog tiếp theo này sẽ được dành toàn bộ như một bản tóm tắt của hội thảo đào tạo mà tôi đã tổ chức gần đây cùng với BusinessOne, một câu lạc bộ tư vấn chuyên nghiệp do sinh viên điều hành có trụ sở tại Đại học Melbourne
Hội thảo nhằm mục đích giúp các nhóm dự án tại BusinessOne tìm hiểu các kỹ thuật làm sạch dữ liệu cơ bản bằng Microsoft Excel, cũng như giới thiệu Microsoft Power BI như một công cụ để xây dựng bảng điều khiển tương tác truyền đạt thông tin chuyên sâu chính trong tập dữ liệu
Bài đăng trên blog hôm nay sẽ tập trung vào nửa đầu của hội thảo, đó là sử dụng Excel để thực hiện kiểm tra dữ liệu đơn giản và làm sạch dữ liệu
Xem bản ghi đầy đủ của hội thảo dưới đây
Các tài liệu được sử dụng cho hội thảo cũng có thể được tìm thấy trên GitHub của tôi tại đây
Giới thiệu
Microsoft Excel là một phần của bộ sản phẩm phần mềm Microsoft Office. Đó là một bảng tính không chỉ cho phép dữ liệu được lưu trữ ở dạng bảng mà tôi. e. hàng và cột, mà còn có các chức năng tính toán, công cụ vẽ đồ thị, bảng tổng hợp, v.v.
Với mục đích của bài viết này, chúng tôi sẽ chủ yếu tập trung vào cách làm việc với dữ liệu trong Excel, thực hiện các kiểm tra cơ bản và làm sạch dữ liệu, cũng như một số phép tính cơ bản
Tab Trang chủ trong Microsoft Excel;
Mỗi ô trong bảng tính được tham chiếu bằng bảng chữ cái theo sau là số e. g. ô A1, A2, v.v.
Thay đổi kích thước cộtBảng trước chiều rộng cột autofit;
Giả sử chúng ta được khách hàng cung cấp tập dữ liệu thô cụ thể này và chúng ta có nhiệm vụ làm sạch, phân tích và giải thích dữ liệu
Trước hết, chúng ta sẽ nhận thấy gần như ngay lập tức rằng các cột không có kích thước phù hợp, điều này khiến chúng ta không thể nhìn thấy giá trị đầy đủ trong mỗi ô. Để giải quyết vấn đề này, chúng tôi có thể thay đổi kích thước từng cột để chúng trở nên đủ rộng để ghi lại từ dài nhất trong mỗi cột
Trước tiên, chúng ta cần đánh dấu toàn bộ bảng thông qua Ctrl + A và khi bảng được đánh dấu, hãy nhấn Alt, sau đó là H, O và I. Điều này sẽ tự động điều chỉnh tất cả các cột để điều chỉnh độ rộng của chúng
Bảng sau autofit chiều rộng cột;
Trước khi chúng tôi bắt đầu bất kỳ quá trình làm sạch hoặc phân tích nào, điều quan trọng là trước tiên chúng tôi phải hiểu rõ về tập dữ liệu mà chúng tôi đang làm việc với
Ở đây, chúng ta có thể quan sát một bảng có vẻ là một tập dữ liệu giao dịch, trong đó mỗi hàng đại diện cho một khách hàng mua một sản phẩm vào một ngày nhất định tại một cửa hàng cụ thể. Di chuyển qua các cột, chúng tôi được cung cấp thêm thông tin như tên sản phẩm, số lượng, tổng doanh số, cùng với các thuộc tính của khách hàng như giai đoạn sống và cấp độ cao cấp của họ
Trước tiên, luôn luôn là một phương pháp hay để kiểm tra các hàng và cột của một tập dữ liệu, đặc biệt là dữ liệu mà chúng ta chưa từng thấy hoặc làm việc với nó trước đây, vì điều này sẽ giúp cho chúng ta biết những điều cần chú ý khi thực hiện kiểm tra dữ liệu và sau đó là làm sạch dữ liệu
Đổi tên tên cộtĐể đổi tên hoặc thay thế giá trị trong một ô, chỉ cần chọn ô bạn muốn thay đổi và bắt đầu nhập giá trị mới
Giả sử ở đây chúng ta muốn đổi tên tất cả tên cột trong bảng
- LYLTY_CARD_NBR. Số thẻ khách hàng thân thiết
- NGÀY THÁNG. Ngày tháng
- CỬA HÀNG_NBR. Số cửa hàng
- TXN_ID. ID giao dịch
- SẢN PHẨM_NBR. Số sản phẩm
- SẢN PHẨM_NAME. tên sản phẩm
- SẢN PHẨM_QTY. Chất lượng sản phẩm
- TOT_SALES. Tổng doanh thu
- CHẶN ĐƯỜNG ĐỜI. Chặn đường đời
- PREMIUM_CUSTOMER. Khách hàng cao cấp
Mỗi ô trong Excel được gán một kiểu dữ liệu xác định cách dữ liệu được lưu trữ và hiển thị
Chúng ta có thể thấy trong cột B rằng các ngày không hiển thị như bình thường và đó là do chúng được đặt làm kiểu dữ liệu chung. Để giải quyết vấn đề này, chúng ta cần chuyển đổi cột thành ngày ngắn
Đầu tiên, đánh dấu cột B và thay đổi kiểu dữ liệu từ chung thành ngày ngắn
Thay đổi loại dữ liệu từ chung thành ngày ngắn;
Trong cột F, chúng ta có thể thấy các mẫu khoảng cách bất thường xảy ra trong tên sản phẩm. Để giải quyết vấn đề này, chúng ta có thể sử dụng hàm trim trong Excel và chuyển tên sản phẩm làm đối số của nó
Chức năng cắt bỏ tất cả các mẫu giãn cách bất thường;
Hơn nữa, trong cột I, chúng tôi quan sát thấy rằng các văn bản đều được viết hoa. Sử dụng chức năng thích hợp, chúng ta chỉ có thể viết hoa chữ cái đầu tiên của mỗi từ và giữ các chữ cái còn lại dưới dạng chữ thường
Hàm thích hợp viết hoa chữ cái đầu tiên của mỗi từ;
Bản sao là bản sao chính xác của cùng một bản ghi trong tập dữ liệu và chúng phải bị xóa vì chúng không thêm bất kỳ giá trị bổ sung nào
Xóa trùng lặp trong Excel rất đơn giản. Trong tab Dữ liệu, chọn loại bỏ trùng lặp
Loại bỏ các bản sao;
Ở đây, chúng tôi định nghĩa một bản sao là bất kỳ bản ghi nào là một bản sao chính xác trên tất cả các trường trong bảng, do đó lý do tại sao chúng tôi đã bao gồm tất cả các cột trong danh sách lựa chọn ở trên
Khi bạn nhấn OK, Excel sẽ thông báo cho bạn số lượng bản sao được tìm thấy và tự động xóa chúng khỏi bảng
Thêm ký hiệu đô la vào cột tổng doanh thuVì tổng doanh thu là số liệu đô la, nên chỉ cần thêm ký hiệu đô la bên cạnh các số
Để thực hiện việc này, chỉ cần đánh dấu cột và chuyển đổi cột thành định dạng số kế toán, được biểu thị bằng ký hiệu đô la trong tab Trang chủ
Flash điền kích thước gói từ tên sản phẩmFlash fill là một chức năng tiên đoán mạnh mẽ trong Excel. Nó có thể cảm nhận một mẫu logic mà bạn đang cố gắng thực hiện và tự động áp dụng mẫu đó cho toàn bộ cột
Để chứng minh điều này, giả sử chúng tôi muốn trích xuất kích thước gói ra khỏi tên sản phẩm trong cột F. Chúng ta có thể thấy rằng các kích cỡ gói, ví dụ: 175g, 150g, 210g, v.v. nằm ở cuối mỗi tên sản phẩm. Do đó, chúng tôi có thể tận dụng flash fill để nhận ra mẫu này và sau đó điền vào một cột mới với kích thước gói từ sản phẩm tương ứng
Thực tế mà nói, để bắt đầu flash fill, trước tiên chúng ta phải nhập kích thước gói đầu tiên, trong trường hợp này là 175g và chuyển sang hàng tiếp theo. Khi bạn bắt đầu nhập 150g cho tên sản phẩm tiếp theo, bạn sẽ thấy một tùy chọn bật lên trong đó Excel đang cố gắng lấp đầy toàn bộ cột bằng tất cả các kích cỡ gói. Chỉ cần nhấn Enter trên bàn phím của bạn để áp dụng màu flash
Ngoài ra, nếu tùy chọn không xuất hiện, hãy nhấn Ctrl + E, đây là phím tắt để điền flash
Flash lấp đầy kích thước gói từ tên sản phẩm;
Giả sử chúng ta không thực sự quan tâm đến hương vị của sản phẩm mà thay vào đó, chúng ta chỉ muốn tên thương hiệu. Cách dễ nhất để làm điều này là trích xuất từ đầu tiên trong mỗi tên sản phẩm. Để thực hiện điều này, chúng ta cần hai chức năng riêng biệt. trái và tìm
Left trả về số ký tự đã chỉ định từ đầu chuỗi văn bản. Mặt khác, Find trả về vị trí bắt đầu của một chuỗi văn bản trong một chuỗi văn bản khác
Công thức kết hợp việc sử dụng hai hàm này như sau
LEFT[[Product Name], WHICH[" ", [Product Name]]-1]Nói một cách đơn giản, công thức về cơ bản cho biết tìm vị trí trước ký tự khoảng trắng đầu tiên và trả về tất cả các ký tự từ tên sản phẩm cho đến vị trí đó. Kết quả là, chúng tôi nhận được như sau
Trích xuất từ đầu tiên trong tên sản phẩm;
Sau khi lấy tên thương hiệu từ các sản phẩm, chúng tôi có thể phát hiện ra một số tên không nhất quán trong đó một số sản phẩm được dán nhãn. Một số ví dụ bao gồm
- Infzns và Infuzions
- Snbts và Sunbites
- WW và Woolworths
- RRD và Red Rock Deli
Ghi nhãn tên sản phẩm không nhất quán;
Là nhà khoa học dữ liệu siêng năng như chúng tôi, điều này cần được giải quyết đúng cách trước khi có thể thực hiện bất kỳ phân tích nào nữa
Có một số cách để thực hiện việc này nhưng phương pháp mà tôi đã trình bày trong hội thảo là sử dụng tìm và thay thế, phím tắt Ctrl+H
Chỉ định từ bạn muốn tìm và từ bạn muốn thay thế bằng từ đó và để Excel thực hiện phần còn lại. Tuy nhiên, hãy nhớ rằng phương pháp này cũng sẽ ghi đè lên từ nếu xuất hiện ở nơi khác trong cùng một bảng tính. Tiến hành thận trọng
Xóa ngoại lệNói về việc trở thành một nhà khoa học dữ liệu siêng năng, một điều khác cần chú ý khi thực hiện kiểm tra dữ liệu là các ngoại lệ
Ngoại lệ là các điểm dữ liệu sai lệch đáng kể so với phân phối của phần còn lại của dữ liệu. Điều quan trọng là chúng ta phải loại bỏ chúng vì chúng không chỉ bóp méo quan điểm của chúng ta về một biến mà còn tác động tiêu cực đến mô hình của chúng ta nếu và khi chúng ta xây dựng một biến.
Có hai ngoại lệ trong bộ dữ liệu giao dịch của chúng tôi. Điều này có thể thấy qua cột số lượng sản phẩm và tổng doanh số. Cụ thể, một khách hàng cụ thể có số thẻ khách hàng thân thiết 226000 đã mua 200 gói chip với tổng doanh số là 650 USD vào hai dịp riêng biệt
Với mục đích của hội thảo này, chúng tôi sẽ loại bỏ những ngoại lệ này khỏi bảng
Thiếu ngàyCuối cùng nhưng không kém phần quan trọng, chúng tôi có một ngày bị thiếu trong dữ liệu, điều mà tôi phải thừa nhận là khó khăn đối với những người mới bắt đầu phát hiện nhưng dù sao, đây là một bước quan trọng trong nỗ lực của chúng tôi nhằm làm sạch hoàn toàn bộ dữ liệu
Để tìm ngày bị thiếu, chúng ta cần chuyển toàn bộ cột ngày vào một hàm gọi là duy nhất, hàm này chỉ trả về các giá trị duy nhất trong một mảng đã cho. Sau đó, sắp xếp ngày từ cũ nhất đến mới nhất
Mặc dù không có cách đơn giản nào để tìm ngày bị thiếu trong Excel, nhưng chúng ta vẫn có thể nghĩ ra cách tiếp cận linh hoạt của riêng mình để có được những gì chúng ta cần. Cụ thể, chúng ta có thể xây dựng câu lệnh if-else như sau
IF[[Tomorrow] - [Today] = 1, "Yes", "No"]Điều này có nghĩa là nếu chênh lệch giữa ngày mai và ngày hôm nay bằng một, thì ngày đó là tuần tự và do đó là chính xác. Nếu không, có một ngày mất tích
Nếu làm đúng ta sẽ thấy ngày thiếu rơi vào ngày 25/12/2018, tức là ngày Noel. Điều này là hợp lý vì cửa hàng bán lẻ có thể đóng cửa trong ngày lễ và do đó, không đăng ký bất kỳ doanh số bán hàng nào cho ngày cụ thể đó
Mặc dù Excel có thể không phải là công cụ linh hoạt và toàn diện nhất để làm sạch dữ liệu, tuy nhiên, nó cung cấp một cách rất nhanh chóng và dễ dàng để kiểm tra tập dữ liệu và loại bỏ một số tác vụ cơ bản
Để bổ sung cho bài viết này, tôi thực sự khuyên bạn nên xem toàn bộ video trên kênh YouTube của mình, nơi tôi sẽ trình bày chi tiết hơn từng bước được trình bày tại đây
Nếu bạn tìm thấy bất kỳ giá trị nào từ bài viết này và chưa phải là thành viên Phương tiện, điều đó sẽ có ý nghĩa rất lớn đối với tôi cũng như những người viết khác trên nền tảng này nếu bạn đăng ký thành viên bằng liên kết bên dưới. Nó khuyến khích chúng tôi tiếp tục đưa ra nội dung chất lượng cao và nhiều thông tin giống như nội dung này — cảm ơn bạn trước