Lesson 1.2: Cleaning messy data and handling missing values
Learn to find and fix duplicates, inconsistent labels, impossible values and blanks, and to choose between deleting and filling missing data.
By the end of this lesson you will be able to take a messy table, profile it before touching anything, and fix the five problems that make up most of real-world mess: duplicate rows, inconsistent labels, wrong types, impossible or extreme values, and missing values. You will learn why a blank is sometimes information, how to choose between deleting a row and filling a gap, and how each choice quietly changes the numbers you report later. You should have read Lesson 1.1, so that "row", "column", "type" and "grain" are familiar words. The Python snippets use the pandas library and are short enough to follow without programming experience, but every idea is also explained in plain words and with numbers you can check by hand.
Data cleaning has a poor reputation. People call it the boring part, the part before the real work. That view costs organisations money, because the real work is only as good as the table it runs on. Every decision you make while cleaning is a decision about what the data means, so cleaning is the first piece of analysis.
Why we clean, and why we do not just delete
Imagine you run credit operations at a small non-banking lender and you are handed an export of 10,000 loan applications. Before anyone builds a risk model, someone has to answer plain questions. How many distinct applicants are there? Is the income column in rupees or in thousands? Why do 600 rows have no income at all? Does the same application appear twice because two branches both entered it?
You could delete everything that looks odd and carry on with the rows that remain. It is quick, and it feels safe. It is also how you end up with a model that has never seen a self-employed applicant, because self-employed applicants are the ones most likely to leave income blank. Deleting is a decision, and like every decision it has a cost. The rule that runs through this whole lesson is simple: never change data without knowing why it looks wrong, and never change it without leaving a record of what you did.
The lesson follows the order a careful analyst works in: profile first, fix structure, decide about extremes, handle missing values, and record what you did.
The five kinds of mess
Almost every cleaning task falls into one of five groups. Knowing the group tells you where to look and what to ask.
Kind of mess · Typical sign · Example from a loan table · First question to askDuplicate rows · Totals look too big; the same id twice · The same application entered by two branches · Which copy is the right one?
Inconsistent labels · One category split into many · "Pune", "pune", "PUNE " · What are all the distinct spellings?
Wrong types · Numbers that will not add; dates that will not sort · Income stored as text with a "Rs" prefix · Can every value be read as the type it should be?
Impossible or extreme values · Minimums and maximums that make no sense · Age 214, income of minus 5,000 · Is this an entry error, or a real rare case?
Missing values · Blanks, "NA", "-", 0 used as a placeholder · Income left blank · Why is it missing?
Here is a small piece of the table we will clean throughout. It is made up for teaching, but every problem in it is one you will meet in real data.
appid · city · age · monthlyincome · loanamount · employmentA101 · Pune · 34 · 42000 · 300000 · Salaried
A102 · pune · 29 · 31000 · 150000 · Salaried
A103 · Mumbai · 214 · 56000 · 500000 · Salaried
A104 · Nagpur · 41 · · 250000 · Self-employed
A104 · Nagpur · 41 · · 250000 · Self-employed
A105 · PUNE · 38 · 400000 · 200000 · Business
A106 · Mumbai · 27 · Rs 28,000 · 120000 · Salaried
A107 · Bombay · 45 · 61000 · 700000 · Salaried
Take a minute and find the problems before reading on. There are at least six.
Predict first: how many separate problems can you find in these eight rows? Count the cities, the repeated id, the age, the blank, the income that is far above the rest and the income that carries text.
The answer is that every one of the five kinds of mess is present. "Pune", "pune" and "PUNE" are one city written three ways, and "Mumbai" and "Bombay" are one city under two names. The id A104 appears twice with identical values, a duplicate. The age of 214 is impossible. The income for A104 is missing. The income of 400,000 for A105 is eight times the next highest salary, so it is either an error or a real high earner. The income for A106 is stored as the text "Rs 28,000", so it cannot be added to the others. A tool that reads this table without checking would happily report an average income that is nonsense.
Step one: profile before you fix
The first move is never to fix. It is to look. Profiling means producing a short summary of every column so you can see the shape of the mess before you choose how to deal with it. A good profile answers, for each column: what type is it, how many values are blank, how many distinct values are there, and what are the smallest and largest values?
With pandas, a table is called a DataFrame, and a profile takes a few lines.
python
import pandas as pd
df = pd.readcsv("applications.csv")
df.shape # rows and columns
df.dtypes # the type pandas guessed for each column
df.isna().sum() # blanks per column
df.nunique() # distinct values per column
df.describe() # min, max, mean for numeric columns
df["city"].valuecounts() # every distinct spelling and its count
df.duplicated("appid").sum() # repeated ids
Each line is a question. valuecounts() on a category column is the single most useful profiling tool there is, because it lists every spelling and exposes the inconsistent ones at once. dtypes shows you that monthlyincome was read as text, because one cell contains "Rs 28,000" and a single bad cell makes the whole column text. describe() shows a minimum age of 18 and a maximum of 214, and the maximum is a clear flag.
The profile also gives you a baseline. Write down the number of rows and the number of blanks per column before you change anything. At the end of the cleaning, compare. If you started with 10,000 rows and ended with 9,200, you should be able to say where the 800 went: so many exact duplicates, so many invalid ages, so many removed for another stated reason. A cleaning step that cannot be explained in numbers is a step you cannot defend.
Inconsistent labels: one thing, many names
Category columns are written by people, and people are creative. The same city appears as "Pune", "pune", "PUNE", "Pune " with a stray space at the end, or "Poona". A bank branch appears with and without its code. A product appears under its old and new names. To a computer, these are all different values, so a count or a group-by quietly splits one group into several.
Suppose the full table has 200 applications. Counting the city column before cleaning gives eight spellings.
Read the chart as a detective would. The largest bar is Pune with 61, but "Pune" is not the whole story: pune (14), PUNE (9) and the version with a trailing space (7) add 30 more. After cleaning, Pune has 61 + 14 + 9 + 7 = 91 applications, Mumbai has 55 + 12 + 6 = 73 (the 6 coming from "Bombay"), and Nagpur has 36. The three numbers add to 200, which is the check you should always make: merging labels must not change the total number of rows.
There are two parts to the fix. The first is mechanical: remove leading and trailing spaces and make the case consistent. The second needs judgement: "Bombay" is a different spelling of the same place, but only a person who knows that can say so. You should list the distinct values and write a mapping by hand, because no tool can guess it.
python
df["city"] = df["city"].str.strip().str.title()
df["city"] = df["city"].replace({"Bombay": "Mumbai", "Poona": "Pune"})
df["city"].valuecounts() # check: three cities, 200 rows in all
Two habits keep this safe. First, check the result with valuecounts() again so you see the merged groups. Second, keep the original column or a copy of the raw file, so that if your mapping turns out to be wrong you can go back. Raw data is read-only: you clean a copy.
Duplicates: counting the same thing twice
A duplicate is a row that records something you have already recorded. Duplicates appear when a system sends the same record twice, when a file is loaded twice, when two branches enter the same customer, or when a join between tables multiplies rows. There are two kinds, and they need different handling.
Exact duplicates: every value in the row matches. These are almost always safe to drop, keeping one copy.
Near duplicates: the same entity with small differences, such as the same application with a different phone number or a later status. You must decide which copy to keep, usually the most recent or the most complete, and that needs a rule you can state.
How serious can a small share of duplicates be? Try it with the sliders. The simulator assumes that each duplicate is a copy of a normal record, which is the usual case for exact duplicates.
With 10,000 rows and 4 per cent duplicates, the table records ₹450 crore of loans while the true figure is ₹432 crore, an overstatement of ₹18 crore. Notice that the overstatement as a share of the truth, 4.17 per cent, is slightly higher than the duplicate share of 4 per cent. That is because the 4 per cent is measured against a table that already contains the duplicates, while the truth is a smaller base. Counts of customers or applications are affected in exactly the same way, and so is anything built on them, such as a conversion rate.
To find duplicates you need to say what makes a row unique. This is the grain from Lesson 1.1. If one row should be one application, then appid should never repeat.
python
df.duplicated().sum() # exact duplicate rows
df.duplicated("appid", keep=False) # every row whose id repeats, to inspect
df = df.dropduplicates() # drop exact copies, keep the first
df = df.sortvalues("updatedat").dropduplicates("appid", keep="last") # keep latest
Always look at a few duplicate pairs before dropping. Sometimes a repeated id is not an error at all: if the table is one row per application per month, the id is meant to repeat, and the unique thing is the pair of id and month.
Wrong types and impossible values
A column's type was the subject of Lesson 1.1, and cleaning is where wrong types are repaired. The usual culprits are numbers stored as text because of a currency symbol, commas or a stray note, dates written in two formats in one column, and identifiers turned into numbers by a spreadsheet. The repair has the same shape each time: strip what does not belong, convert, and then look at what failed to convert.
python
df["monthlyincome"] = (
df["monthlyincome"].astype(str)
.str.replace("Rs", "", regex=False)
.str.replace(",", "", regex=False)
.str.strip()
)
df["monthlyincome"] = pd.tonumeric(df["monthlyincome"], errors="coerce")
The argument errors="coerce" turns anything that cannot be read into a blank instead of stopping with an error. That is convenient and also dangerous, because it hides problems. After converting, count how many blanks appeared and check that you can explain each one. If the count jumped from 600 to 1,100, then 500 values failed to convert and you should look at them.
Impossible values come from entry errors, unit mix-ups and system defaults. A person cannot be 214 years old. An income cannot be negative. A date of 1 January 1970 in a field that should be recent is the computer's "zero" date, which means the value was missing and something filled in a default. The best protection is a list of validity rules, which are simple statements about what each column can hold: age between 18 and 100, income above zero, loan amount below the product's limit, date not in the future. Check each rule, count the violations and decide what to do with them. A violation can be corrected if the true value is obvious, such as 214 meant to be 21 or 41. It can be set to blank and treated as missing. Or, if the row cannot be trusted at all, it can be removed, with the count written down.
Extreme values: error or real?
The income of 400,000 in our table deserves its own treatment, because it shows the most common wrong reflex in cleaning: deleting a value because it is large. An outlier is a value far from the others. It might be an error, such as an extra zero. It might be a real but rare case, such as a business owner whose income really is ₹4 lakh a month. Which one it is matters, because deleting real rare cases removes exactly the people a lender most needs to understand, and keeping errors can wreck an average.
A simple, widely used way to flag candidates is the interquartile range rule. Sort the values. The first quartile, written Q1, is the value a quarter of the way up, and the third quartile, Q3, is three quarters of the way up. The distance between them is the interquartile range, or IQR. A value is flagged if it lies more than 1.5 times the IQR below Q1 or above Q3. The multiplier of 1.5 is a convention, not a law.
Take ten monthly incomes, in thousands of rupees: 18, 22, 25, 27, 30, 32, 35, 40, 45 and 400. The lower half is 18, 22, 25, 27, 30, with a middle value of 25, so Q1 is 25. The upper half is 32, 35, 40, 45, 400, with a middle value of 40, so Q3 is 40. The IQR is 15, and 1.5 times that is 22.5. The upper fence is 40 + 22.5 = 62.5 and the lower fence is 25 − 22.5 = 2.5. Only the 400 lies outside the fences. Try the rule with your own numbers.
Slide the multiplier to 3 and the fences move to 70 and −20 in the same example, which still flags 400. Slide the tested value to 60 and it is inside the default fences, although it is above every other salary but one. The rule is a flag, not a verdict. After flagging, ask three questions. Is the value physically possible? Does another column agree with it, for example a business income alongside a large loan and a registered company? Can someone who knows the source confirm it? Only then decide: keep it, correct it, or set it to blank.
Missing values: why they are missing matters most
Missing values were introduced in Lesson 1.1 as the blanks that eat your table. Here we go further, because the right treatment depends on why the value is missing. Statisticians describe three situations, and it is worth learning the names.
Missing completely at random: the blank has nothing to do with any value in the table. A form was lost in transit, or a sensor failed during a power cut. Deleting such rows loses information but does not bias the picture.
Missing at random, given other columns: the blank depends on something you do observe. Younger applicants might skip the "years at current address" question more often. If you know the age, you can predict who is likely to leave it blank.
Missing not at random: the blank depends on the missing value itself. High earners who do not want to disclose income leave income blank. Borrowers in trouble skip the question about existing loans. This is the dangerous case, because the rows you lose are systematically different from the rows you keep.
In our loan table, application A104 is self-employed and left income blank. That is a clue. If self-employed applicants leave income blank far more often than salaried ones, the blank is linked to the employment column, and deleting blank rows would remove self-employed people from the data almost entirely. A model trained on what is left would know nothing about them. Always check whether the blanks cluster in some group before choosing a strategy.
python
df["monthlyincome"].isna().groupby(df["employment"]).mean() # share blank in each group
If the result shows 3 per cent of salaried applicants and 38 per cent of self-employed ones with blank income, you have found a pattern, and you will treat those blanks differently from random gaps.
Strategies for missing values
There is no single right way to handle a gap. There are several strategies, each with a cost.
Strategy · What it does · Good when · CostDelete the rows · Removes any row with a blank · Few blanks, missing completely at random · Loses data and can bias the sample
Delete the column · Removes a column that is mostly blank · A column is more than about half blank and not essential · Loses a possibly useful signal
Fill with the mean or median · Replaces a blank with one typical number · A numeric column, modest share missing · Shrinks the spread and ignores other columns
Fill with the most common value · Replaces a blank with the mode · A category column · Overstates the common category
Fill by group · Uses the median of a similar group · Blanks depend on another column · Needs a sensible grouping
Fill with a model · Predicts the blank from other columns · Larger projects, missing at random · Complex; can leak information if done badly
Add a missing flag · Keeps the blank and adds a yes-or-no column · The fact of being blank carries meaning · An extra column to maintain
The most useful single trick in the table is the last one. Create a column such as incomemissing, set to 1 where income was blank and 0 otherwise, and then fill the blank with a typical value. Now the model, or the analyst, can see both the filled number and the fact that it was a guess. When missingness is informative, which in business data it often is, the flag can be one of the best predictors in the table.
Mean or median?
Between the two common fills, the median is usually safer when a few large values pull the mean upwards. To see why, take eight known monthly incomes in thousands of rupees: 22, 25, 28, 30, 32, 35, 40 and 120. Two more applicants have blanks, and let us pretend we later learn that their true incomes were 29 and 33.
The mean of the eight known values is (22 + 25 + 28 + 30 + 32 + 35 + 40 + 120) ÷ 8 = 332 ÷ 8 = 41.5. The median is the average of the two middle values, 30 and 32, which is 31. The true average of all ten values is (332 + 29 + 33) ÷ 10 = 394 ÷ 10 = 39.4.
If you fill both blanks with the mean of 41.5, the average of the ten values stays at 41.5, which is 2.1 too high because the large value of 120 dragged the mean up. If you fill with the median of 31, the average becomes (332 + 31 + 31) ÷ 10 = 39.4, which matches the truth here. If you simply delete the two rows, the average of the remaining eight stays at 41.5.
This is a tiny example, so do not generalise from it. The point is the mechanism. A single extreme value drags the mean, and a fill based on the mean then carries that distortion into every blank. The median resists it. With skewed data such as income, house prices or transaction amounts, the median is the usual first choice.
Filling shrinks the spread
There is a second, quieter cost of filling with one number, and it does not show up in the average at all. When you replace many blanks with the same value, you add many points sitting exactly at the centre. The spread of the column, measured by its standard deviation, shrinks. Any analysis that depends on spread, such as a confidence interval, a correlation or a model's idea of how variable income is, will then be too confident. The effect is bigger the more you fill. Try it.
At 20 per cent blank and a standard deviation of 15, the filled column's standard deviation falls to about 13.4, a loss of about 10.6 per cent of the spread, even though the average has not moved. If 40 per cent were blank, the standard deviation would fall to about 11.6, which is a loss of about 22.6 per cent. This is the reason that the more sophisticated fills, which add some realistic variation rather than one repeated number, exist. For a first pass on a beginner project, filling with the median plus a missing flag is a sensible and honest default, as long as you remember what it does to the spread.
A cleaning checklist you can reuse
You now have the parts of a routine. Run through it in this order every time.
1. Keep the raw file untouched and work on a copy.
2. Profile: shape, types, blanks per column, distinct values, minimum and maximum, duplicates. Write the numbers down.
3. Fix types: strip symbols and convert. Count the blanks that appear and explain them.
4. Standardise labels: trim, fix case, merge spellings with a hand-made mapping. Check totals are unchanged.
5. Remove duplicates: say what makes a row unique, inspect a few pairs, then drop or keep the latest.
6. Check validity rules: ranges, impossible values and default dates. Count violations.
7. Flag extremes: use the interquartile range rule or your own domain rule, and investigate before acting.
8. Handle missing values: find out why, check whether blanks cluster in a group, choose a strategy, add a flag where the blank itself is informative.
9. Reconcile: rows in, rows out, and a reason for every difference.
10. Save the code and a short log, not only the cleaned file.
The last two steps separate cleaning from tinkering: a cleaned file nobody can reproduce is a file nobody can trust.
Two more worked examples
Example 1: the income that was not an error. A model-building team flagged an income of ₹4 lakh a month for a borrower with a business loan. The interquartile range rule flagged it, and a junior analyst wanted to delete it. A colleague checked the other columns: the borrower owned a registered company, the loan was large, and the bank statements supported the income. The value was real. They kept it, and because incomes in the table were heavily skewed, they used the median for any fill and later modelled income on a logarithmic scale, a technique you will meet in the regression lessons. Deleting it would have made the model blind to a whole class of customers.
Example 2: the blank that carried the signal. A lender found that 11 per cent of applications had no recorded "existing loans" figure. Instead of dropping them, an analyst checked how those applicants behaved. They defaulted at a noticeably higher rate than applicants who filled the field in. Missing here was not random; it was linked to risk, possibly because applicants with several loans avoided the question. The analyst filled the number with the median and added a flag, existingloansmissing. The flag turned out to be one of the more useful inputs to the later model. This is the clearest case for keeping the blank visible.
Common mistakes
Cleaning the raw file in place. Always work on a copy and keep the original, or you cannot undo a wrong decision.
Deleting rows with blanks by reflex. In a wide table this can discard half your data, and it removes the very groups that leave blanks.
Treating every outlier as an error. Large values are often the most interesting rows. Investigate before you delete.
Using errors="coerce" and never looking at the damage. Count the blanks created by conversion and explain them.
Filling blanks with 0. Zero is a real value. A blank income filled with 0 says the person earns nothing and ruins averages and models.
Filling before you split the data for modelling. The fill value, such as a median, must be learned from the training rows only, or information leaks from the test rows. Lesson 6.4 returns to this.
Key takeaways
Cleaning is analysis. Every cleaning decision changes what the data means, so profile first and record every change.
The five kinds of mess are duplicates, inconsistent labels, wrong types, impossible or extreme values and missing values. Each has a first question to ask.
Merge category spellings with a hand-made mapping and check that the row total is unchanged. Small duplicate rates overstate totals by about the same share.
An outlier is a candidate to investigate, not a row to delete. The interquartile range rule flags values beyond 1.5 times the IQR from the quartiles.
Why a value is missing matters more than how many are missing: completely at random, at random given other columns, or not at random.
Prefer the median to the mean for skewed columns, add a missing flag when the blank is informative, and remember that filling shrinks the spread.
Keep the raw data, script the cleaning and reconcile the row counts. A cleaned table nobody can reproduce is not trustworthy.
Check yourself
1. A city column has the spellings "Delhi", "delhi", "New Delhi" and "DELHI " in 500 rows. What do you do and what must stay the same afterwards?
Trim spaces, fix the case and map "New Delhi" to "Delhi" by hand. The total of 500 rows must be unchanged, and there should now be one group.
2. A table of 20,000 rows has 3 per cent exact duplicates. Roughly by what share is a row count overstated?
About 3.1 per cent, since 3 ÷ 97 is about 0.031. The true row count is about 19,400.
3. Using the interquartile range rule with Q1 = 30, Q3 = 50 and a multiplier of 1.5, what are the fences, and is a value of 95 flagged?
The IQR is 20, so the fences are 30 − 30 = 0 and 50 + 30 = 80. Yes, 95 is above 80 and is flagged for checking.
4. A column is 25 per cent blank and you fill it with the mean. The average stays the same. What gets worse?
The spread shrinks, because many points now sit exactly at the centre. Anything that depends on spread becomes too confident.
5. Income is blank for 40 per cent of self-employed applicants and 3 per cent of salaried ones. Is deleting blank rows a good idea?
No. The blanks are linked to employment, so deleting them would remove most self-employed applicants and bias the data. Fill by group or with a flag instead.
Try this
Take a real messy table. A good choice is a downloaded open government data file, a sample of your own bank or card statement, or an export from a work system. Set aside forty to forty-five minutes.
1. Keep the original file untouched, and make a copy to work on. Note the number of rows and columns.
2. Profile every column. For each, write the type, the number of blanks, the number of distinct values, and the minimum and maximum if it is numeric.
3. Pick one category column. List every distinct value, spot spellings of the same thing, and write a mapping from each spelling to one standard name. Check the row total does not change.
4. Decide what makes a row unique. Count duplicates on that key and look at three pairs before removing anything.
5. Write two validity rules for a numeric column and a date column, count the violations and decide what to do with each group.
6. For one numeric column, compute Q1, Q3 and the interquartile range, find the fences, and look at each flagged value. Write one line for each saying keep, correct or blank, and why.
7. For one column with blanks, check whether the blanks cluster in another column. Choose a strategy from the table and say what it costs. Use the spread simulator above with your own blank share to see how much a mean fill would shrink the spread.
8. Reconcile: rows at the start, rows at the end, and a reason for each difference.
A good result is a one-page cleaning log that a colleague could use to repeat your steps and get the same table. It should list what was found, what was done, how many rows were affected, and one decision you were unsure about. That log is your first reusable piece of work as a data scientist, and the next lesson uses a clean table to describe what the numbers in it say.
Tags: Data Science Data cleaning Missing values Pandas Beginner