Data Cleaning and Transformation

Data Cleaning and Transformation

After this lesson you will be able to: detect and handle missing values, duplicate rows, wrong data types and messy strings — the tasks that take up most of the time in a real data project.

1. Missing values

Pandas records an empty cell as NaN. First, count them:

import pandas as pd

s = pd.Series([8.0, None, 6.0, None, 10.0])
print(s.isna().sum())      # 2   (True counts as 1)
print(s.isna().tolist())   # [False, True, False, True, False]

There are two main ways to handle them:

print(s.dropna().tolist())              # [8.0, 6.0, 10.0]      ← drop the missing values
print(s.fillna(s.mean()).tolist())      # [8.0, 8.0, 6.0, 8.0, 10.0]  ← fill with the mean

Which one to choose depends on the problem: dropping rows loses data; filling with the mean keeps the number of rows but makes the distribution "flatter". When computing statistics, mean() skips NaN by default rather than treating NaN as 0:

print(s.mean())            # 8.0   ← (8 + 6 + 10) / 3
print(s.fillna(0).mean())  # 4.8   ← filling with 0 drags the mean down

2. Duplicate rows

d = pd.DataFrame({"id": [1, 2, 2, 3], "v": ["a", "b", "b", "c"]})
print(d.duplicated().sum())        # 1   (the second row of the duplicate pair)
print(len(d.drop_duplicates()))    # 3   (keeps the first occurrence)

3. Wrong data types

A numeric column that was read as text (because of stray characters) cannot be used in calculations. Use astype when you are sure the data is clean, or to_numeric when corrupted values may be mixed in:

print(pd.Series(["1", "2", "3"]).astype(int).sum())    # 6

t = pd.to_numeric(pd.Series(["1", "2", "x"]), errors="coerce")
print(t.tolist())    # [1.0, 2.0, nan]  ← "x" cannot be converted, so it becomes NaN

errors="coerce" turns corrupted values into NaN so you can handle them next with fillna/dropna.

4. Cleaning strings

names = pd.Series([" An ", "BINH"])
print(names.str.strip().str.lower().tolist())   # ['an', 'binh']

.str.strip() removes whitespace at both ends and .str.lower() converts to lowercase. This way "IT", "it" and " IT" are no longer counted as three different groups when you compute statistics. You can chain several steps together with dots.

To replace specific values: s.replace("N/A", None) turns the string "N/A" into a missing value.

Suggested workflow

1. Get an overview: df.info(), df.isna().sum(), df.describe().
2. Fix data types (to_numeric, astype).
3. Normalize strings (.str.strip().str.lower()).
4. Handle missing values (dropna or fillna).
5. Remove duplicate rows (drop_duplicates).
6. Re-check after each step with shape and isna().sum().

Key takeaways

• Count missing values with isna().sum(); mean() skips NaN.
• dropna removes rows, fillna fills in a value.
• drop_duplicates keeps the first occurrence.
• to_numeric(..., errors="coerce") turns corrupted values into NaN.
• Normalize strings before grouping or counting.

Reference

McKinney, W. (2022). Python for Data Analysis: Data Wrangling with Pandas, NumPy, and Jupyter (3rd ed.). O'Reilly Media.