3.1.4 Data Cleaning

Why clean data?

Real data is messy. People type city names three different ways, leave boxes empty, write "absent" where a number should be, and sometimes a 16-year-old's age gets entered as 160. A model cannot tell that these are mistakes. It just learns from them, and a model trained on rubbish makes rubbish predictions.

Cleaning means finding these problems and deciding what to do about each one. It is rarely exciting, but in a real task it often matters more than which model you pick. Some IOAI datasets are already clean; many are not, and noticing that quickly is a skill.

You will need Pandas for everything below, so do that module first.

The main course: Kaggle Learn Data Cleaning

Kaggle Learn Data Cleaning has five short lessons, each with an exercise notebook on real, messy data. Some of its topics are covered elsewhere in this guide, as the table shows.

A messy file, cleaned step by step

Here is a small, deliberately messy file, messy.csv. It is made up, but every problem in it is one you will meet in real data. Download it and put it next to your notebook:

Download messy.csv

Two cells are empty, and Ravi's city has spaces around it, shown here in quotes:

Can you spot the problems before reading on? Load the file:

import pandas as pd

df = pd.read_csv("messy.csv")

pandas turns each empty cell into NaN ("not a number"), which is how it marks a missing value. We will fix the rest in a sensible order: look, remove duplicates, fix text and types, deal with impossible values, and only then fill the gaps.

1. Look first

df.info()

<class 'pandas.DataFrame'>
RangeIndex: 7 entries, 0 to 6
Data columns (total 5 columns):
 #   Column  Non-Null Count  Dtype
---  ------  --------------  -----
 0   name    7 non-null      str
 1   city    7 non-null      str
 2   age     6 non-null      float64
 3   maths   6 non-null      str
 4   joined  7 non-null      str
dtypes: float64(1), str(4)
memory usage: 412.0 bytes

Two warning signs: maths should hold numbers but is str (text), because one entry is "absent"; and joined holds dates but is also str. isna().sum() counts the missing values in each column:

df.isna().sum()

name      0
city      0
age       1
maths     1
joined    0
dtype: int64

2. Duplicates

The last row is an exact copy of Ravi's row. duplicated() marks rows that repeat an earlier one:

df.duplicated().sum()

np.int64(1)

df = df.drop_duplicates()
df.shape

(6, 5)

Notice the df = .... Like most pandas methods, drop_duplicates returns a new table and leaves the old one alone, so you have to keep the result.

3. Messy text

Look at the cities:

df["city"].value_counts()

city
Delhi       3
 mumbai     1
Pune        1
MUMBAI      1
Name: count, dtype: int64

Mumbai appears twice, once with spaces around it and once in capitals, so pandas counts it as two different cities. The .str methods work on every value in a text column at once: strip() removes spaces at the ends, and title() makes the first letter a capital and the rest small.

df["city"] = df["city"].str.strip().str.title()
df["city"].value_counts()

city
Delhi     3
Mumbai    2
Pune      1
Name: count, dtype: int64

For spellings that differ more, such as "Bombay" and "Mumbai", use replace with a dictionary: df["city"].replace({"Bombay": "Mumbai"}).

4. Numbers stored as text

pd.to_numeric turns a text column into numbers. On its own, it stops at the first value it cannot read:

pd.to_numeric(df["maths"])

ValueError: Unable to parse string "absent" at position 3

errors="coerce" tells it to turn anything unreadable into NaN instead:

df["maths"] = pd.to_numeric(df["maths"], errors="coerce")
df["maths"]

0    91.0
1    64.0
2    78.0
3     NaN
4    58.0
5     NaN
Name: maths, dtype: float64

"absent" is now an honest missing value, and the column is numbers again.

5. Dates

pd.to_datetime turns text like 2025-01-10 into real dates. Then .dt gives you parts of each date, which often make better features than the raw date:

df["joined"] = pd.to_datetime(df["joined"])
df["joined"].dt.month

0    1
1    2
2    1
3    3
4    2
5    1
Name: joined, dtype: int32

6. Impossible values

describe() is the quickest way to catch values that cannot be right:

df["age"].describe()

count      5.000000
mean      45.200000
std       64.177099
min       16.000000
25%       16.000000
50%       17.000000
75%       17.000000
max      160.000000
Name: age, dtype: float64

A maximum age of 160 is clearly a typo, and it drags the mean up to 45. A histogram shows the same thing at a glance. We do not know the true age, so the honest fix is to mark it as missing:

df.loc[df["age"] > 100, "age"] = None
df.isna().sum()

name      0
city      0
age       2
maths     2
joined    0
dtype: int64

Be careful here: an unusual value is not always a wrong one. A very tall person or a very expensive house is real. Only change values you are sure are mistakes.

7. Missing values

Now the gaps. There are three common choices.

Drop the rows. Simple, but here it would throw away most of the data:

df.dropna().shape

(2, 5)

Only 2 of 6 rows survive. Dropping is fine when very few rows have gaps, and wasteful otherwise.

Fill them in. For a number column, the median (the middle value) is a safe default, because unlike the mean it is not dragged around by extreme values. Remember that a value was missing, too: the fact that someone was "absent" may itself be useful to a model.

df["maths_missing"] = df["maths"].isna()
df["age"] = df["age"].fillna(df["age"].median())
df["maths"] = df["maths"].fillna(df["maths"].median())
df

Compare it with the file you started from: no duplicate, one spelling of Mumbai, real numbers and dates, and no gaps. Mei's missing age and Lena's impossible one both became the median, 16.5, and the two missing maths marks became 71.0.

Leave them. Some models, such as decision trees and XGBoost, can work with missing values directly.

Every column is now clean, with the right type:

df.info()

<class 'pandas.DataFrame'>
RangeIndex: 6 entries, 0 to 5
Data columns (total 6 columns):
 #   Column         Non-Null Count  Dtype
---  ------         --------------  -----
 0   name           6 non-null      str
 1   city           6 non-null      str
 2   age            6 non-null      float64
 3   maths          6 non-null      float64
 4   joined         6 non-null      datetime64[us]
 5   maths_missing  6 non-null      bool
dtypes: bool(1), datetime64[us](1), float64(2), str(2)
memory usage: 378.0 bytes

Files that will not open

Sometimes read_csv fails before you have even started, with an error like this:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 13: invalid continuation byte

The file was saved with a different character encoding, a different way of storing letters such as é or ã as bytes. pandas expects UTF-8, the most common one. Tell it the right one and the file loads:

pd.read_csv("latin.csv", encoding="latin-1")

"latin-1" fixes most Western European files; Kaggle's Character Encodings lesson shows how to find the encoding when that does not work.

What comes after cleaning

Cleaning makes the data correct. Getting it ready for a model is the next step, and it lives in Scikit-learn Basics: scaling numbers to similar ranges, turning categories into numbers, and filling gaps inside a pipeline.

Common mistakes

Check yourself

More practice

CodeChef's Learn Pandas course and pandas practice path both include data cleaning problems; they are in the practice table below. The Kaggle course's exercises use real messy datasets, which makes them good practice too.

What comes next

That is the end of the foundations. Part 4 starts machine learning itself: begin with Terminology Used in Classical Machine Learning.