Ravindra BagaleCourses & study guides

5. Data Cleaning A–Z

By Last updated:

Chala mitrano, aaj aapan sagalyat important kaam shikuya – data cleaning. Kharya job madhe analyst cha motha vel data swachh karnyat jaato: duplicates, blanks, extra spaces, "pune/PUNE/Pune", text madhle numbers, gondhalleli dates, phone numbers… Pratyek problem sathi ithe BEFORE (messy) aani AFTER (clean) table aahe, steps aahet aani formulas aahet. Shevti ek poora Blinkit export aapan suruvatipasun shevtaparyant clean karu. Ghabru naka – ek ek problem ghyaycha.

What you will learn in this module

  • Finding and removing duplicates, blanks, extra spaces and non-printable characters
  • Fixing case, inconsistent city names (pune / PUNE / Aurangabad → Sambhaji Nagar) with Find & Replace and a mapping table
  • Converting numbers stored as text and text dates into real numbers and dates
  • Splitting and merging columns; cleaning phone numbers, e-mails, pincodes and codes
  • Extracting parts of text, removing unwanted characters, handling outliers and error values
  • The unpivot idea, and a full end-to-end cleaning of one messy Blinkit export

Golden rules before cleaning

1) Keep the original export untouched on a sheet called Raw and clean a copy. 2) Clean with helper columns and formulas first, check, then Copy › Paste Special › Values and delete the helpers. 3) Count rows and total amount before and after – they must reconcile. 4) If you will receive the same messy file every week, record the steps in Power Query (Module 10) instead of repeating them by hand.

Concepts in this chapter

  1. 5.1Duplicates
  2. 5.2Blank Cells
  3. 5.3Extra Spaces and Non-printable Characters
  4. 5.4Inconsistent Case
  5. 5.5Inconsistent City Spellings
  6. 5.6Numbers Stored as Text
  7. 5.7Text Dates and Mixed Date Formats
  8. 5.8Splitting Columns
  9. 5.9Merging Columns
  10. 5.10Phone Numbers
  11. 5.11E-mail Addresses
  12. 5.12Pincodes and Codes with Leading Zeros
  13. 5.13Extracting Parts of Text
  14. 5.14Removing Unwanted Characters
  15. 5.15Outliers
  16. 5.16Error Values in Data
  17. 5.17Unpivot: Wide to Long (Concept)
  18. 5.18End-to-End: Cleaning a Messy Blinkit Export

The chapter recap is at the end of the last concept page.