Data Cleaning Techniques Every Data Professional Should Master

It was 11:30 PM on a Friday. I had already missed the last fast local train from Andheri, and I was staring at a spreadsheet that honestly looked like someone had dropped it in a blender.

The regional sales team had just emailed their “master” file for the quarter. Someone had typed their 12-digit Aadhaar number into the ‘Last Name’ field. Half the dates were in the US format, the other half were standard Indian, and one cell literally just said “kal” (yesterday).

I wanted to cry. I mean it.

If you spend five minutes on LinkedIn, you’d think working in data is all about building sleek AI models and designing dashboards that look like they belong in a sci-fi movie. Total lie. The reality? About 80% of this job is just data preparation. It is tedious, it is frustrating, and it is absolutely necessary.

Because if you feed garbage into a machine learning model that cost lakhs of rupees to deploy, guess what you get out? Highly calculated, very expensive garbage.

So, let’s talk about the actual data cleaning techniques that will save your weekend. No fluff. Just survival skills.

The Black Hole of Missing Values

Blank cells are the absolute bane of my existence. You will never, ever get a perfectly filled-out dataset in the real corporate world. Users get lazy, systems glitch, and suddenly you have three thousand rows missing their pincodes.

You basically have two options when wrangling this mess. The first is amputation—just drop the rows entirely. If a customer record is missing an email, phone number, and name, chuck it. Useless.

But if you just have a few missing salary figures in a column, you can’t just delete half your database. That’s where imputation comes in. You fill in the blanks with the average or median of that column. Think of it as mathematical jugaad. It’s not perfect, but it keeps the whole thing from crashing before the heavy data processing even starts.

Wrestling with Strings (and People’s Terrible Typing)

Some folks call it data cleansing, but I prefer to call it “fixing human laziness.”

People are terrible at typing on their phones. You will get a dataset where the city is listed as “Mumbai”, “mumbai”, “BOM”, “Bombay”, and “Navi Mumbai”. If you try to run a total sales report by city right now, your software thinks those are five totally different places. Your data quality is officially ruined.

You have to learn how to manipulate strings. Force everything into lowercase. Strip out the extra spaces that people accidentally hit at the end of their words (the TRIM() function will become your best friend). Use find-and-replace logic to standardize those abbreviations.

Spotting the “Wait, What?” (Outliers)

Data wrangling is basically playing detective. You have to look at the numbers and ask, “Boss, does this make any logical sense?”

I once ran a summary on an e-commerce site and saw a user who supposedly bought 400 ceiling fans on a random Tuesday. Now, unless they were opening a massive hotel in Goa overnight, that was a system glitch.

If you skip looking for outliers, that one weird transaction will skew your entire monthly average. The management will think revenue is up 300%, and you’ll be the one awkwardly explaining the mistake on the Monday morning Zoom call. Learn how to plot your data on a quick scatter graph. See those weird dots sitting miles away from everything else? Investigate them.

Dates: The Ultimate Boss Fight

I don’t know who decided that the world needs fifteen different ways to write down what day it is, but I have a serious grudge against them.

Dealing with dates is arguably the most frustrating step in the entire data analysis process. You absolutely have to force every single date into one standard format (usually YYYY-MM-DD). If you don’t, your software won’t know if 05/04/2024 is the 5th of April (how we write it) or the 4th of May (how the US software defaults write it).

Getting to the Root of the Problem

Look, data cleaning is a grind. After a few months of manually fixing uppercase letters and hunting down missing commas, you might realize something: you are cleaning up messes that shouldn’t exist in the first place.

If your startup’s mobile app is letting users type “dog” into a “Date of Birth” field, the problem isn’t the data—it’s the app. Sometimes the best solution is to walk over to the tech team and tell them to hire a competent flutter app developer in mumbai to rebuild the input forms with actual validation rules. Fix the leak, don’t just keep mopping the floor.

Or, maybe this whole experience makes you realize you hate analyzing the data and would rather build the systems that catch it. It’s super common for analysts to pivot into engineering. Taking a rigorous java full stack course is a pretty standard route here in India if you want to learn how to build the robust backend databases and APIs that stop bad data dead in its tracks.

But until you make that jump? Roll up your sleeves. Get comfortable with the mess. The person who can take a dumpster fire of a CSV and turn it into pristine, workable numbers is the person who never struggles to get a hike.