Data Cleaning in Pandas
Learn how to identify, inspect, and prepare messy datasets using Pandas data cleaning techniques.
1. Introduction to Data Cleaning
Data cleaning is the process of identifying and correcting problems in a dataset before performing analysis. Real-world datasets may contain missing values, duplicate records, inconsistent text, incorrect data types, and invalid values.
Pandas provides several methods to inspect and clean DataFrames. Cleaning data improves consistency, reduces errors, and helps produce more reliable analysis results.
Common Data Quality Problems
| Problem | Example | Typical Solution |
|---|---|---|
| Missing values | Age = NaN | isna(), dropna(), fillna() |
| Duplicate records | The same customer appears twice. | duplicated(), drop_duplicates() |
| Inconsistent text | "pune", "Pune", " PUNE " | str.strip(), str.title() |
| Incorrect data types | Age stored as text | astype(), to_numeric() |
| Invalid values | Negative age | Validation rules and filtering |
A typical data cleaning workflow begins with inspection and ends with validation of the cleaned dataset.
2. Create a Dataset with Common Data Problems
The following DataFrame contains missing ages, duplicate records, inconsistent city names, and numeric values stored as text.
import pandas as pd
import numpy as np
df = pd.DataFrame({
"Name": [
"Samadhan",
"Pradnya",
"Samiksha",
"Rahul",
"Samadhan",
"Neha"
],
"Age": [22, np.nan, 24, 21, 22, -5],
"City": [
" pune ",
"Mumbai",
"PUNE",
"Nashik",
" pune ",
"Mumbai"
],
"Salary": [
"45000",
"52000",
None,
"48000",
"45000",
"30000"
]
})
print(df)
Expected Output:
Name Age City Salary 0 Samadhan 22.0 pune 45000 1 Pradnya NaN Mumbai 52000 2 Samiksha 24.0 PUNE None 3 Rahul 21.0 Nashik 48000 4 Samadhan 22.0 pune 45000 5 Neha -5.0 Mumbai 30000
3. Inspect the Dataset Before Cleaning
Always inspect the dataset before modifying it. This helps identify its structure, data types, missing values, and potential quality problems.
View the First Rows
print(df.head())
Check Column Data Types
print(df.dtypes)
Expected Output:
Name object Age float64 City object Salary object dtype: object
The Salary column is stored as an object because it contains text values and a missing entry. Converting it to a numeric type will make numeric operations easier.
Get a Dataset Summary
df.info()
The info() method displays the number of rows,
column names, non-null counts, data types, and memory usage.
Use it to identify columns that contain missing values.
Count Missing Values
print(df.isna().sum())
Expected Output:
Name 0 Age 1 City 0 Salary 1 dtype: int64
4. Handle Missing Values
Missing values occur when information is unavailable or was not recorded. Pandas provides methods to detect, remove, or replace missing entries.
Example 1: Identify Missing Values
print(df[df["Age"].isna()])
Expected Output:
Name Age City Salary 1 Pradnya NaN Mumbai 52000
Example 2: Fill Missing Age with the Median
df["Age"] = df["Age"].fillna(
df["Age"].median()
)
print(df["Age"])
Expected Output:
0 22.0 1 22.0 2 24.0 3 21.0 4 22.0 5 -5.0 Name: Age, dtype: float64
The median is calculated from the available age values. It replaces the missing age without changing the other values. The invalid negative age still requires separate validation.
Example 3: Remove Rows with Missing Values
Use dropna() when removing incomplete rows is
appropriate for the analysis.
complete_df = df.dropna() print(complete_df)
The method returns a DataFrame without rows containing missing values. In this example, the missing age has already been filled, so rows with missing Salary values are removed.
5. Detect and Remove Duplicate Records
Duplicate records can inflate counts, distort totals, and
affect analytical results. Pandas provides
duplicated() to identify duplicates and
drop_duplicates() to remove them.
Example 1: Find Duplicate Rows
print(df.duplicated())
The result is a Boolean Series. A value of True indicates that a row duplicates an earlier row according to the selected columns and duplicate-detection settings.
Example 2: Display Duplicate Records
duplicates = df[
df.duplicated(keep=False)
]
print(duplicates)
The keep=False argument marks every member of
a duplicate group as True rather than keeping one occurrence
unmarked.
Example 3: Remove Duplicate Rows
clean_df = df.drop_duplicates() print(clean_df)
By default, drop_duplicates() keeps the first
occurrence of each duplicate row and removes subsequent copies.
Example 4: Detect Duplicates Using Specific Columns
duplicate_names = df[
df.duplicated(subset=["Name"], keep=False)
]
print(duplicate_names[["Name", "City"]])
This example identifies rows that share a Name value. However, names alone may not uniquely identify a person. Choose columns that reflect the actual business definition of a duplicate record.
6. Standardize Text Values
Text values can contain leading spaces, trailing spaces, or inconsistent capitalization. Standardizing text makes grouping, filtering, and comparisons more reliable.
Example 1: Remove Extra Spaces
df["City"] = df["City"].str.strip() print(df["City"])
The strip() string method removes whitespace from
the beginning and end of each string.
Example 2: Standardize Capitalization
df["City"] = df["City"].str.title() print(df["City"])
Expected Output:
0 Pune 1 Mumbai 2 Pune 3 Nashik 4 Pune 5 Mumbai Name: City, dtype: object
The city values are now consistently formatted. This is especially useful when the same category has been entered in different capitalization styles.
7. Convert Columns to the Correct Data Types
Correct data types make calculations and comparisons more reliable. A numeric value stored as text should be converted before performing numerical analysis.
Example 1: Convert Salary to Numeric
df["Salary"] = pd.to_numeric(
df["Salary"],
errors="coerce"
)
print(df["Salary"].dtype)
Expected Output:
float64
The errors="coerce" option converts invalid
numeric entries into missing values instead of raising an
exception. Inspect the resulting missing values before
deciding how to handle them.
Example 2: Convert a Column to Integer
df["Marks"] = pd.Series(
[85, 68, 92, 76, 55, 70],
dtype="int64"
)
print(df["Marks"].dtype)
Expected Output:
int64
Ordinary integer types cannot represent missing values.
If an integer column must retain missing entries, Pandas
nullable integer types such as Int64 may be useful.
8. Identify and Handle Invalid Values
A value can have the correct data type and still be invalid. For example, a negative age may be a valid integer but violate the rules of a student or customer dataset.
Example 1: Find Invalid Ages
invalid_ages = df[
(df["Age"] < 0) |
(df["Age"] > 120)
]
print(invalid_ages[["Name", "Age"]])
This filter identifies ages outside the example's permitted range. The correct range should always be chosen according to the requirements of the dataset.
Example 2: Replace Invalid Ages with Missing Values
df.loc[
(df["Age"] < 0) | (df["Age"] > 120),
"Age"
] = np.nan
print(df["Age"])
The invalid values are replaced with missing values so they can be reviewed and handled using an appropriate missing-data strategy.
9. Rename Columns for Consistency
Clear and consistent column names make DataFrame operations
easier to understand. You can use rename() to
change selected column names.
df = df.rename(
columns={
"Name": "StudentName",
"City": "CityName"
}
)
print(df.columns.tolist())
Expected Output:
['StudentName', 'Age', 'CityName', 'Salary']
Renaming columns is useful when imported datasets contain unclear names, inconsistent naming conventions, or spaces that make code harder to maintain.
10. Validate the Cleaned Dataset
Data cleaning should finish with validation. Check the cleaned DataFrame again to confirm that the intended changes were applied.
Check Remaining Missing Values
print(df.isna().sum())
Check Remaining Duplicates
print(df.duplicated().sum())
Inspect Data Types
print(df.dtypes)
Check Basic Statistics
print(df.describe(include="all"))
Validation helps identify issues that remain after cleaning. Some missing values may be intentional, and not every duplicate or unusual value is necessarily an error. Review results in the context of the dataset's requirements.
11. Complete Data Cleaning Example
The following example demonstrates a simple cleaning workflow on a fresh copy of the sample dataset. It removes duplicate records, standardizes city names, converts Salary to numeric, and marks invalid ages as missing.
import pandas as pd
import numpy as np
df = pd.DataFrame({
"Name": [
"Samadhan",
"Pradnya",
"Samiksha",
"Rahul",
"Samadhan",
"Neha"
],
"Age": [22, np.nan, 24, 21, 22, -5],
"City": [
" pune ",
"Mumbai",
"PUNE",
"Nashik",
" pune ",
"Mumbai"
],
"Salary": [
"45000",
"52000",
None,
"48000",
"45000",
"30000"
]
})
# 1. Standardize city names
df["City"] = df["City"].str.strip().str.title()
# 2. Convert Salary to a numeric column
df["Salary"] = pd.to_numeric(
df["Salary"],
errors="coerce"
)
# 3. Mark invalid ages as missing
df.loc[
(df["Age"] < 0) | (df["Age"] > 120),
"Age"
] = np.nan
# 4. Remove exact duplicate rows
df = df.drop_duplicates()
# 5. Inspect the cleaned data
print(df)
print(df.isna().sum())
print(df.dtypes)
This example deliberately leaves missing values in place rather than filling them with arbitrary values. The appropriate strategy for missing ages or salaries depends on the analysis requirements and the meaning of the data.
12. Data Cleaning Best Practices
- Inspect the original dataset before making changes.
- Keep an unchanged copy of important source data.
- Understand why values are missing before filling or removing them.
- Check duplicate records using meaningful identifying columns.
- Standardize text only when the transformation is appropriate.
- Validate values against domain-specific rules.
- Convert data types only after inspecting the actual values.
- Recheck missing values, duplicates, and data types after cleaning.
13. Practical Exercises
Exercise 1: Missing Values
Create a DataFrame with missing age values and count the missing entries in every column.
Hint: Use isna().sum().
Exercise 2: Duplicate Records
Identify duplicate rows and create another DataFrame without exact duplicate records.
Hint: Use duplicated() and drop_duplicates().
Exercise 3: Text Standardization
Standardize city values such as " pune ", "PUNE", and "Pune" into a consistent format.
Hint: Use str.strip() and str.title().
Exercise 4: Numeric Conversion
Convert a Salary column containing numeric strings into a numeric data type.
Hint: Use pd.to_numeric().
Exercise 5: Data Validation
Identify records with negative ages and replace those values with missing values for further review.
Hint: Use loc[], a Boolean
condition, and np.nan.
14. Quick Revision Questions
- What is data cleaning, and why is it important?
- Which Pandas method identifies missing values?
- What is the difference between
dropna()andfillna()? - How can duplicate records be identified and removed?
- How do
str.strip()andstr.title()help standardize text? - What does
pd.to_numeric(errors="coerce")do? - Why should invalid values be checked against domain rules?
- Why is validation important after cleaning a DataFrame?
Summary
Data cleaning prepares datasets for reliable analysis by
identifying missing values, duplicate records, inconsistent
text, incorrect data types, and invalid values. Pandas provides
methods such as isna(), fillna(),
dropna(), duplicated(),
drop_duplicates(), rename(),
and to_numeric() to handle common data quality issues.
A reliable cleaning workflow begins with inspection, applies appropriate transformations, and finishes with validation. Cleaning decisions should follow the meaning of the data and the requirements of the analysis rather than relying on automatic changes without review.