Definition
A data set of 10,000 patients has 412 exact duplicates, ages stored as text, and a body temperature of 980 °F. Each problem is small. Together they can wreck any model trained on it.
Data cleaning includes removing duplicate records, converting columns to their correct types and handling outliers. Duplicates are rows that repeat the same record exactly or nearly; wrong types occur when numbers or dates are stored as text; and outliers are values far from the rest, which may be errors to fix or genuine extremes to keep.
In simple words
Imagine checking a class register: one student written twice, a mark written as "seventy" instead of 70, and an age of 145. You would fix the first two at once, and check the third with the office before changing it.
Overview
- Duplicates are repeated records that inflate counts and can leak between training and test sets.
- Wrong types, such as numbers stored as text, block calculations and sorting.
- Outliers are values far from the rest; they may be errors or genuine rare cases.
- duplicated(), drop_duplicates(), astype(), pd.to_numeric() and the IQR rule are the main tools.
- Every cleaning decision should be recorded so that it can be explained and repeated.
- Never delete outliers automatically; first find out what they are.
Why it matters
Duplicates inflate counts and leak between training and test sets; wrong types break calculations; and outliers can dominate means, regression lines and distance-based models. Clean data is the foundation of trustworthy results.
Key terms
- Exact duplicate
- A row identical to another in every column.
- Near duplicate
- Records of the same entity that differ slightly, such as spacing or capitalisation.
- Type conversion
- Changing a column to its correct type, such as with pd.to_numeric or pd.to_datetime.
- errors="coerce"
- An option that turns unconvertible values into NaN instead of raising an error.
- Domain rule
- A real-world limit, such as a human age between 0 and 120.
- Winsorising
- Capping extreme values at chosen percentiles instead of deleting them.
Types
Three families of data problem are covered here, and each comes in several types. These are the types you will meet most often.
1. Exact and partial duplicates
Exact duplicates repeat every column; partial duplicates repeat the key fields with small differences.
- How it works: duplicated() flags exact copies; duplicated(subset=["phone"]) flags repeats of chosen columns; drop_duplicates(keep="first") removes them.
- Example: The same order recorded twice by a payment retry, or "Ravi Kumar" and "Ravi Kumar" with the same phone number.
- When to use it: Remove exact duplicates; for partial ones, clean the text first and decide which record to keep.
2. Wrong numeric types
Numbers stored as text because of symbols, commas, units or stray words.
- How it works: Strip symbols with .str.replace("[₹,]", "", regex=True), then pd.to_numeric(errors="coerce") turns anything unconvertible into NaN for review.
- Example: A price column with "₹1,250", "1200" and "N/A".
- When to use it: Fix these before any statistics, or averages and sorting will be wrong or fail.
3. Inconsistent categories
The same category spelt or capitalised in different ways.
- How it works: Lower-case and strip the text, then map variants to one standard label with replace() or a dictionary; convert to the category type.
- Example: "Bengaluru", "Bangalore", "bengaluru " and "BLR" all meaning one city.
- When to use it: Fix them before grouping, or one group will be split into several.
4. Error outliers
Impossible or clearly mistaken values caused by typing, unit or sensor errors.
- How it works: Range checks (age between 0 and 120) and domain rules catch them; fix them from the source if possible, otherwise set them to NaN.
- Example: A height of 1,750 cm (typed for 175.0) or a temperature of −999 used as a sensor's error code.
- When to use it: Correct or remove these: they are not real data.
5. Genuine outliers
Real but extreme values, such as a very large purchase or a heatwave day.
- How it works: Detect them with the IQR rule, z-scores or box plots, then decide: keep them, cap them (winsorise) or model them separately.
- Example: A wedding order of ₹2 lakh at a catering company whose usual order is ₹5,000.
- When to use it: Keep them when they are the point (fraud, extreme weather); cap or transform them with logs when they distort a model.
Explained in detail
Duplicates
df.duplicated().sum() counts exact duplicate rows and df.drop_duplicates() removes them, keeping the first. Often a record should be unique by key rather than across every column: df.duplicated(subset=["patient_id", "visit_date"]) finds the same visit entered twice even if one copy has a typo elsewhere. Near duplicates are harder: "Ravi Kumar" and "ravi kumar" refer to the same person. Normalise text first, stripping spaces and lowering case, then check again. Removing duplicates before splitting into training and test sets is essential, because a record that appears in both makes the test score meaninglessly high.
Fixing types
| Problem | Fix |
|---|---|
| Numbers stored as text because of "N/A" or "₹1,200" | Strip symbols, then pd.to_numeric(s, errors="coerce") |
| Dates stored as text | pd.to_datetime(s, dayfirst=True, errors="coerce") |
| Yes/No as text | s.map({"Yes": True, "No": False}) |
| A small set of repeated labels | s.astype("category") to save memory and fix allowed values |
| IDs stored as numbers, losing leading zeros such as pin codes | Read them as text with dtype={"pincode": str} |
After errors="coerce", count the new NaNs: they are the values that could not be converted, and you should inspect a few to understand why.
Outliers: error or extreme?
Detect candidates with domain rules first, because they are the most reliable: an adult's body temperature of 980 °F is impossible, whereas a statistical rule would only call it unusual. Then use statistical rules such as the 1.5 × IQR fences or |z| > 3 for values that are possible but rare. For each candidate decide: is it an error, such as a misplaced decimal point, which you fix or set to NaN; or a real extreme, such as a genuinely large hospital bill, which you keep? If real extremes distort a model, consider capping them at the 1st and 99th percentiles, transforming with a logarithm or using robust methods.
- Keep a cleaning log: what you changed, how many rows were affected and why.
- Never silently delete outliers that might be the most important cases, such as fraud.
- Write cleaning steps as functions so they can be re-run on new data.
Cleaning is rarely finished in one pass. Each fix can reveal a new problem, so re-run your checks after every change.
How it works
- Remove exact duplicates, then check duplicates by key after normalising text.
- Convert numeric, date and boolean columns to their correct types, counting values that fail.
- Apply domain rules to flag impossible values.
- Use IQR or z-score rules to find rare values, and investigate each type.
- Fix, set to NaN, cap or keep, and record every decision.
In code
import numpy as np
import pandas as pd
df = pd.DataFrame({
"patient": ["Ravi Kumar", "ravi kumar", "Asha", "Asha", "Imran", "Neha"],
"visit": ["01/08/2024", "01/08/2024", "03/08/2024", "03/08/2024", "05/08/2024", "06/08/2024"],
"temp_f": ["98.6", "98.6", "99.1", "99.1", "980", "N/A"],
"bill": ["₹1,200", "₹1,200", "₹850", "₹850", "₹2,40,000", "₹700"],
})
print("Exact duplicates:", int(df.duplicated().sum()))
df["patient"] = df["patient"].str.lower().str.split().str.join(" ").str.title()
df = df.drop_duplicates()
print("Rows after de-duplication:", len(df))
df["visit"] = pd.to_datetime(df["visit"], dayfirst=True)
df["temp_f"] = pd.to_numeric(df["temp_f"], errors="coerce")
df["bill"] = pd.to_numeric(df["bill"].str.replace("[₹,]", "", regex=True))
df.loc[~df["temp_f"].between(90, 110), "temp_f"] = np.nan # domain rule
print(df.dtypes.to_dict())
print(df)Output
Exact duplicates: 1
Rows after de-duplication: 4
{'patient': dtype('O'), 'visit': dtype('<M8[us]'), 'temp_f': dtype('float64'), 'bill': dtype('int64')}
patient visit temp_f bill
0 Ravi Kumar 2024-08-01 98.6 1200
2 Asha 2024-08-03 99.1 850
4 Imran 2024-08-05 NaN 240000
5 Neha 2024-08-06 NaN 700Try it yourself
Three small problems
A hospital analyst prepares visit records for a model that predicts readmission.
Fix, set to missing or keep?
Decide for each flagged value.
Height of 1,750 cm
A genuine ₹50 lakh home loan in a loans data set
A record-breaking rainfall day during a cyclone
Age recorded as −4
Common mistakes
- Removing duplicates after splitting, leaving copies in both sets.
- Converting with errors="coerce" and not checking which values became NaN.
- Deleting all statistical outliers, including genuine extremes.
Check yourself
Summary
- Remove exact and key-based duplicates, normalising text first.
- Convert columns to correct types and inspect values that fail.
- Flag outliers with domain and statistical rules; fix errors, keep real extremes, log decisions.
Finished reading?
Sources: Python for Data Analysis, 3rd edition (Wes McKinney) · Hadley Wickham: Tidy Data (Journal of Statistical Software, 2014)