Data Cleaning: Making Dirty Data Usable

After completing this topic, you will be able to:

Identify and handle missing values, duplicates, and outliers, which are common problems in real-world data.

Why is data cleaning necessary?

Real-world data is not clean. There are missing survey responses, the same person entered twice, and a row with an age of -5. If you analyze this data as is, you cannot trust the results.

It is said that 60-80% of data analysis work is spent on cleaning. This step is much more important than fancy analysis techniques.

Step 1: Understand the overall data

python

import pandas as pd

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

# Basic information
print(df.shape)        # (1000, 8) - 1000 rows, 8 columns
print(df.dtypes)       # Data type of each column
print(df.info())       # Summary, including missing value status

# Check the first/last
print(df.head())       # Top 5 rows
print(df.tail())       # Bottom 5 rows

# Statistical summary
print(df.describe())   # Mean, standard deviation, minimum/maximum, etc. for numeric columns

When you run df.info(), you will see the Non-Null Count. If it is less than the total number of rows, it means there are missing values. In df.describe(), if min/max is outside the reasonable range, suspect outliers.

Step 2: Handling missing values

python

# Check for missing values
print(df.isnull().sum())
# name         0
# age          15
# blood_type   3
# weight       42

# Strategy 1: Deletion - when the number of missing rows is small
df_clean = df.dropna(subset=["blood_type"])  # Delete 3 rows with missing blood_type

# Strategy 2: Imputation - for numeric columns
df["age"] = df["age"].fillna(df["age"].median())    # Impute with the median
df["weight"] = df["weight"].fillna(df["weight"].mean())  # Impute with the mean

# Strategy 3: Impute with a specific value
df["blood_type"] = df["blood_type"].fillna("Unknown")

Which strategy to use depends on the context.

If the missing value ratio is less than 5%, deletion is the safest.
For numeric values, impute with the median (robust to outliers) or the mean.
For categorical values (blood type, gender), impute with the mode or "Unknown."

Step 3: Removing duplicates

python

# Check for duplicates
print(df.duplicated().sum())  # 23 duplicate rows

# View duplicate entries
print(df[df.duplicated(keep=False)])  # Show both original and duplicate

# Remove duplicates (keep the first)
df = df.drop_duplicates()

# Remove duplicates based on a specific column
df = df.drop_duplicates(subset=["patient_id"], keep="last")

keep="first" keeps the first row and deletes the rest. keep="last" keeps the last. If the data is time-series, last keeps the latest record.

Step 4: Detecting outliers

python

# Check with basic statistics
print(df["age"].describe())
# min: -5    <- Abnormal
# max: 200   <- Abnormal

# Range filtering
df = df[(df["age"] >= 0) & (df["age"] <= 120)]

# IQR method - statistical outlier detection
Q1 = df["weight"].quantile(0.25)
Q3 = df["weight"].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

outliers = df[(df["weight"] < lower) | (df["weight"] > upper)]
print(f"Found {len(outliers)} outliers")

# Remove outliers
df = df[(df["weight"] >= lower) & (df["weight"] <= upper)]

You should not always delete outliers. An age of -5 is clearly an error and should be deleted, but a weight of 150kg may actually exist. Domain knowledge is key to judging outliers.

Step 5: Type conversion and format unification

python

# Convert numeric strings to numeric types
df["age"] = pd.to_numeric(df["age"], errors="coerce")

# Convert date strings to datetime
df["visit_date"] = pd.to_datetime(df["visit_date"])

# Clean up strings - remove leading/trailing spaces, unify case
df["name"] = df["name"].str.strip()
df["blood_type"] = df["blood_type"].str.upper()

errors="coerce" converts values that cannot be converted (e.g., "N/A") to NaN. This allows you to proceed without errors, which is useful for large datasets.

Key takeaways

Step	Check	Main tool
Overall understanding	Number of rows/columns, type, missing value status	`info()`, `describe()`
Missing values	Check ratio -> Delete or impute	`isnull()`, `fillna()`, `dropna()`
Duplicates	Duplicate rows or based on key	`duplicated()`, `drop_duplicates()`
Outliers	Reasonable range + IQR	`describe()`, range filtering
Type/Format	Unify numeric, date, and string	`to_numeric()`, `to_datetime()`, `str.strip()`

Data cleaning is a repetitive and tedious task, but skipping this step will undermine all subsequent analysis. "Garbage in, garbage out" - if the input is garbage, the output will be garbage.

BioPlayground

Data Cleaning — Making Dirty Data Usable

Data Cleaning: Making Dirty Data Usable

After completing this topic, you will be able to:

Why is data cleaning necessary?

Step 1: Understand the overall data

Step 2: Handling missing values

Step 3: Removing duplicates

Step 4: Detecting outliers

Step 5: Type conversion and format unification

Key takeaways

💬 Questions & Comments