Data Cleaning for Beginners: Fix Missing Values, Duplicates and Messy Formats

Here is a real-looking export from a college placement cell:

Try asking "What is the average package of CSE students placed in Bengaluru?" and every row fights you: a duplicate student, branch written two ways, CGPA as a percentage, packages in three formats, the same city spelt three ways with an extra space. This is normal. Clean data is something you produce, not something you receive.

The six problems you will see everywhere

1. Duplicates — the same record entered twice, often from merged files.
2. Missing values — blank cells, "NA", "-", or 0 used to mean "unknown".
3. Inconsistent categories — CSE / cse / Computer Science; Bangalore / Bengaluru.
4. Wrong data types — numbers stored as text ("4,20,000"), dates as text in mixed formats.
5. Different units or scales — LPA vs rupees, CGPA vs percentage.
6. Outliers and impossible values — a CGPA of 87, an age of 210, a delivery time of −5 minutes.

Fixing them, one by one

Duplicates

Decide what makes a row unique (a roll number, an order ID) before deleting anything. Two different students can share a name; one student should not share a roll number.

Missing values

There is no single right answer — it depends on why the value is missing:

• Leave it and report it — often the most honest choice. "CGPA missing for 12 of 340 students."
• Remove the row — fine if only a few rows are affected and they are not special in some way.
• Fill it — with the median (safer than the mean when there are outliers), a group median (median CGPA of that branch), or a clear label like "Unknown" for categories.

Inconsistent text

Trim spaces, standardise case, then map variations to one value with a small lookup table (Bangalore, bengaluru → Bengaluru).

Types and units

Convert everything to one unit in a new column — for example package_lpa — and keep the original column until you have checked the result.

The same cleaning in pandas

import pandas as pd

df = pd.read_csv("placements.csv")
df = df.drop_duplicates(subset=["roll_no"])                      # one row per student
df["branch"] = df["branch"].str.strip().str.upper().replace({"COMPUTER SCIENCE": "CSE"})
df["city"]   = df["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru"})

# "6.5 LPA", "650000", "4,20,000" -> lakhs per annum
def to_lpa(value):
    text = str(value).upper().replace(",", "").replace("LPA", "").strip()
    number = float(text)
    return number if number < 100 else number / 100000
df["package_lpa"] = df["package"].apply(to_lpa)

print(df.isna().sum())                                            # missing values per column
print(df.groupby("branch")["package_lpa"].median())

In Excel you would do the same with Remove Duplicates, TRIM, PROPER, Find & Replace and a helper column — or with Power Query, which records your cleaning steps so you can repeat them next month with one click.

A cleaning checklist for every dataset

1. How many rows and columns? Does that match what you expected?
2. What does one row represent? (One order? One customer? One day?)
3. Any duplicates on the unique key?
4. Missing values per column — and why are they missing?
5. Correct data types? Dates as dates, numbers as numbers?
6. Category spellings standardised?
7. Minimum and maximum of every number — anything impossible?
8. Write down every change you made, so someone else can repeat it.