Course: Python Training
Topic: Cleaning Data and Handling Missing or Duplicate Values
Course Link: Python Training Course
Demo Link: Register for a Free Course Demo
Official Website: JustAcademy
Data cleaning is the process of identifying and correcting inaccurate, incomplete, inconsistent, duplicated, or incorrectly formatted data. It is an important step in data analysis, machine learning, reporting, and application development.
Real-world datasets often contain empty cells, repeated records, inconsistent text, invalid values, and unexpected data types. If these problems are not handled properly, they can lead to incorrect calculations, misleading visualizations, and unreliable machine learning models.
Python provides useful tools for cleaning data. The pandas library is particularly valuable because it offers functions for detecting missing values, removing duplicates, replacing incorrect entries, and transforming columns into appropriate data types.
| Problem | Description | Example |
|---|---|---|
| Missing values | Required information is unavailable. | A customer record has no email address. |
| Duplicate records | The same record appears more than once. | The same order is stored twice. |
| Inconsistent formatting | Values use different formats. | "Mumbai", "mumbai", and " MUMBAI ". |
| Incorrect data types | A value is stored using an unsuitable type. | A price column contains numeric strings. |
| Invalid values | Values violate expected rules. | A person's age is -5. |
| Inconsistent missing markers | Missing information uses different representations. | Empty strings, "N/A", and "unknown". |
| Outliers | Values are unusually far from the expected range. | A product price is recorded as 9999999 by mistake. |
Pandas is a Python library used for data manipulation and analysis. It provides two main data structures:
Install pandas if it is not already available in your Python environment:
pip install pandasImport pandas and create a sample DataFrame:
import pandas as pd
data = {
"Name": ["Aarav", "Diya", "Kabir", None, "Diya"],
"Age": [25, 30, None, 28, 30],
"City": ["Mumbai", "Pune", "Mumbai", "Delhi", "Pune"],
"Salary": [45000, 60000, 52000, None, 60000]
}
df = pd.DataFrame(data)
print(df)This dataset contains missing values and repeated information. It will be used throughout the examples in these notes.
Before changing data, examine its structure and quality. Understanding the dataset helps you choose suitable cleaning operations without accidentally deleting useful information.
print(df.head())
print(df.tail())head() displays the first five rows by default, while tail() displays the last five. You can specify the number of rows, such as df.head(10).
print(df.shape)The result is a tuple containing the number of rows and columns. For example, (5, 4) represents five rows and four columns.
print(df.columns)
print(df.dtypes)
df.info()Use these commands to identify incorrect data types, unexpected column names, and columns with missing values.
print(df.describe())
print(df.describe(include="all"))The first statement summarizes numeric columns by default. The second includes descriptive information for other column types where applicable.
A missing value represents information that is not available in a dataset. Pandas commonly represents missing values with NaN, None, or its dedicated pd.NA marker, depending on the column's data type.
A missing value may indicate a genuine absence of information rather than a mistake. For example, a customer without a second phone number may be perfectly valid. Before filling or deleting missing values, understand what they mean in the context of the dataset.
Both isna() and isnull() identify missing values in pandas.
print(df.isna())
print(df.isnull())These methods return a Boolean DataFrame: True indicates a missing value, and False indicates a non-missing value.
missing_counts = df.isna().sum()
print(missing_counts)The sum() method counts the True values in each column, helping you identify columns that need attention.
missing_percentage = (df.isna().mean() * 100).round(2)
print(missing_percentage)This calculation shows the percentage of missing values in each column. It is useful when deciding whether a column can be retained, filled, or removed.
rows_with_missing = df[df.isna().any(axis=1)]
print(rows_with_missing)The expression any(axis=1) checks each row and returns True if at least one column is missing a value.
complete_rows = df[df.notna().all(axis=1)]
print(complete_rows)The expression all(axis=1) selects rows in which every column contains a non-missing value.
The dropna() method removes rows or columns containing missing values. It should be used carefully because removing records may discard valuable information or introduce bias.
clean_df = df.dropna()
print(clean_df)By default, dropna() removes every row that contains at least one missing value. The original DataFrame remains unchanged unless you assign the result back or use an appropriate in-place operation.
clean_df = df.dropna(how="all")The how="all" option removes a row only if all its values are missing.
clean_df = df.dropna(subset=["Name", "Age"])This removes rows where either Name or Age is missing. Missing values in other columns do not determine whether the row is removed.
clean_df = df.dropna(axis=1)Using axis=1 removes columns containing missing values. This can be useful for small, unreliable columns but may discard important features.
clean_df = df.dropna(thresh=3)The thresh=3 argument retains rows containing at least three non-missing values. This allows partially complete rows to remain in the dataset.
Instead of deleting incomplete records, you can replace missing values with suitable alternatives. The correct strategy depends on the meaning of the column and the reason values are missing.
filled_df = df.fillna(0)This replaces missing values throughout the DataFrame with zero. However, zero is not a suitable replacement for every column. For example, a missing person's age should not automatically become zero.
df["City"] = df["City"].fillna("Unknown")
print(df)Replacing missing categorical values with a label such as "Unknown" can preserve the fact that the original information was unavailable.
mean_age = df["Age"].mean()
df["Age"] = df["Age"].fillna(mean_age)The mean is the arithmetic average. It can be appropriate for reasonably symmetric numeric distributions without extreme outliers. It is sensitive to unusually large or small values.
median_salary = df["Salary"].median()
df["Salary"] = df["Salary"].fillna(median_salary)The median is the middle value when observations are sorted. It is often more robust than the mean when numeric data contains outliers or has a skewed distribution.
mode_city = df["City"].mode()
if not mode_city.empty:
df["City"] = df["City"].fillna(mode_city.iloc[0])The mode is the most frequently occurring value. It is often used for categorical columns, but it may overrepresent the most common category. If no meaningful mode exists, consider an explicit missing category instead.
df = df.fillna({
"Name": "Unknown",
"Age": df["Age"].median(),
"City": "Unknown",
"Salary": df["Salary"].median()
})This approach allows each column to use a replacement suited to its data type and meaning.
Forward fill and backward fill use neighboring values to fill missing entries. They are especially useful for ordered or time-series data when carrying nearby observations forward or backward is logically justified.
df["Temperature"] = df["Temperature"].ffill()Forward fill uses the previous non-missing value. For example, if a temperature reading is missing at 10:05, the previous available reading may be used when appropriate.
df["Temperature"] = df["Temperature"].bfill()Backward fill uses the next non-missing value. This can be useful for some historical records, but it can introduce future information into predictive datasets if applied carelessly.
df["Temperature"] = df["Temperature"].ffill(limit=2)The limit restricts forward filling to at most two consecutive missing entries per uninterrupted run. Always sort time-series data by its time column before using these methods.
Interpolation estimates missing numeric values from surrounding observations. It can be useful for continuous measurements that change gradually, such as temperature or sensor readings.
df["Temperature"] = df["Temperature"].interpolate(method="linear")Linear interpolation estimates a missing value based on the values before and after it. The method should only be used when the data structure and assumptions justify it.
For time-series data, interpolation can also use time information when the index is a suitable datetime index:
df = df.sort_index()
df["Temperature"] = df["Temperature"].interpolate(method="time")Interpolation does not automatically make a dataset correct. Check whether the estimated values are realistic and whether the method is suitable for the domain.
Some datasets represent missing information using strings such as "N/A", "NA", "null", "None", or empty text. These may not automatically be recognized as missing values in every import or data-processing workflow.
import pandas as pd
df = df.replace(
["N/A", "NA", "null", "None", ""],
pd.NA
)Only replace strings that genuinely represent missing information in your dataset. For example, the text "None" may be a legitimate category in a particular application.
df["City"] = df["City"].str.strip()Whitespace can cause values that appear identical to be treated as different strings. For example, "Mumbai" and " Mumbai " are different before cleaning.
df["City"] = df["City"].str.strip().str.title()This produces consistent title casing for many city names. Be careful with abbreviations, brand names, and languages where title casing may not be appropriate.
A duplicate occurs when a row or a selected set of column values repeats. Duplicates can result from repeated imports, data-entry mistakes, merging files, or systems that submit the same record multiple times.
Not every repeated value is a duplicate. Two customers may share the same city, and two products may have the same price. Duplicate detection should reflect the business meaning of the record.
duplicate_rows = df.duplicated()
print(duplicate_rows)The duplicated() method returns a Boolean Series. By default, the first occurrence of a row is marked False, while later identical occurrences are marked True.
duplicate_count = df.duplicated().sum()
print("Duplicate rows:", duplicate_count)duplicates = df[df.duplicated(keep=False)]
print(duplicates)The keep=False option marks every member of a duplicated group as a duplicate, including its first occurrence. This is useful when investigating all records involved.
duplicate_emails = df[df.duplicated(subset=["Email"], keep=False)]
print(duplicate_emails)Here, records are considered duplicates when their email values match, even if other columns differ. Choose the subset according to a reliable identifier or a business rule.
The drop_duplicates() method removes repeated rows or repeated combinations of selected column values.
clean_df = df.drop_duplicates()
print(clean_df)By default, pandas retains the first occurrence of an identical row and removes later occurrences.
clean_df = df.drop_duplicates(keep="last")This retains the last occurrence of each duplicated record. It can be useful when the latest row is the preferred version, provided the row order has a meaningful interpretation.
clean_df = df.drop_duplicates(keep=False)This removes every row belonging to a duplicated group. Use it only when all such groups should be excluded rather than reduced to a single record.
clean_df = df.drop_duplicates(subset=["Email"])Only the selected columns are used to determine duplication. If email addresses should be unique, this can retain one record per email address, but you should decide which record to retain when the associated information differs.
df = df.sort_values("UpdatedAt")
df = df.drop_duplicates(subset=["CustomerID"], keep="last")This pattern retains the last record for each customer after sorting by an update timestamp. Confirm that timestamps are valid and sorted in the intended order before applying the operation.
Records may appear different because of whitespace, letter case, or inconsistent formatting. Normalizing these values before checking duplicates can reveal repeated records that would otherwise be missed.
df["Email"] = df["Email"].str.strip().str.lower()
df["Name"] = df["Name"].str.strip()
df = df.drop_duplicates(subset=["Email"])Lowercasing email addresses is commonly used for matching in data-cleaning workflows, but identity and uniqueness rules should follow the requirements of the source system. Avoid removing records solely because names are similar.
Correct data types make calculations and comparisons more reliable. For example, a salary stored as text cannot always be used directly for numeric calculations.
df["Salary"] = pd.to_numeric(df["Salary"], errors="coerce")The errors="coerce" argument converts values that cannot be interpreted as numbers into missing values. Inspect those newly missing values to determine whether they indicate invalid input or a different data format.
df["CustomerID"] = df["CustomerID"].astype("string")Identifiers such as customer IDs and postal codes may be better represented as strings, especially when leading zeros are significant.
df["OrderDate"] = pd.to_datetime(
df["OrderDate"],
errors="coerce"
)Invalid date strings become missing datetime values. Check these rows before deciding whether to correct, replace, or remove them.
Data cleaning should include validation. After modifying a dataset, verify that the intended changes occurred and that important information has not been accidentally lost.
print(df.isna().sum())print("Duplicate rows:", df.duplicated().sum())print(df["City"].value_counts(dropna=False))
print(df["City"].unique())value_counts() shows how frequently values occur, while unique() displays distinct values. These methods help identify unexpected categories and inconsistent labels.
invalid_ages = df[(df["Age"] < 0) | (df["Age"] > 120)]
print(invalid_ages)Range rules must be appropriate for the application. An unusual value should be investigated before being removed or changed.
original_rows = len(original_df)
cleaned_rows = len(df)
print("Original rows:", original_rows)
print("Cleaned rows:", cleaned_rows)
print("Rows removed:", original_rows - cleaned_rows)Keeping an original copy allows you to measure how much the cleaning process changed the dataset.
The following example demonstrates a repeatable workflow for identifying missing information, standardizing text, removing exact duplicate rows, and validating the cleaned dataset.
import pandas as pd
data = {
"CustomerID": [101, 102, 103, 103, 104, 105],
"Name": ["Aarav", "Diya", " Kabir ", " Kabir ", None, "Meera"],
"Age": [25, 30, None, None, 42, 29],
"City": ["Mumbai", "Pune", "Mumbai", "Mumbai", None, "Delhi"],
"Email": [
"[email protected]",
"[email protected]",
"[email protected]",
"[email protected]",
None,
"[email protected]"
]
}
original_df = pd.DataFrame(data)
df = original_df.copy()
# Step 1: Inspect the original data
print("Original data:")
print(df)
print("\\nMissing values:")
print(df.isna().sum())
# Step 2: Standardize text columns
df["Name"] = df["Name"].str.strip()
df["City"] = df["City"].str.strip().str.title()
df["Email"] = df["Email"].str.strip().str.lower()
# Step 3: Remove exact duplicate rows
df = df.drop_duplicates()
# Step 4: Fill missing numeric values using the median
df["Age"] = df["Age"].fillna(df["Age"].median())
# Step 5: Preserve unknown information explicitly
df["Name"] = df["Name"].fillna("Unknown")
df["City"] = df["City"].fillna("Unknown")
# Step 6: Check the results
print("\\nCleaned data:")
print(df)
print("\\nRemaining missing values:")
print(df.isna().sum())
print("\\nRemaining exact duplicate rows:")
print(df.duplicated().sum())Important: The example removes exact duplicate rows, but it does not automatically remove records sharing a CustomerID or email. Those records may require investigation to determine whether they represent duplicate customers, updated records, or legitimate transactions.
When the business rule states that each customer should have one record, inspect repeated identifiers and decide which record is authoritative instead of blindly deleting rows.
# Inspect repeated customer IDs
repeated_ids = df[
df.duplicated(subset=["CustomerID"], keep=False)
].sort_values("CustomerID")
print(repeated_ids)
# If the latest record should be retained, sort using a reliable
# timestamp column first:
#
# df["UpdatedAt"] = pd.to_datetime(df["UpdatedAt"], errors="coerce")
# df = df.sort_values("UpdatedAt")
# df = df.drop_duplicates(subset=["CustomerID"], keep="last")Do not use this rule unless the identifier is reliable and the business requirements confirm that only one row per identifier should remain.
| Problem | Possible Cause | Solution |
|---|---|---|
| Missing values are not detected | Missing information is stored as text. | Inspect unique values and replace known missing markers with pd.NA. |
| Too many rows disappear | dropna() removes rows containing any missing value. |
Use subset, thresh, or a justified imputation strategy. |
| Duplicate rows remain | Rows differ in one or more columns. | Inspect duplicates using a suitable subset. |
| Legitimate records are deleted | Repeated values were assumed to be duplicates. | Use reliable identifiers and domain-specific rules. |
| Numeric conversion produces missing values | Text contains symbols or invalid numbers. | Inspect failed conversions and clean formats before conversion. |
| Imputed values look unrealistic | The replacement method does not suit the data distribution. | Review distributions, outliers, and domain constraints. |
| Changes do not appear in the original DataFrame | A cleaned result was assigned to a different variable. | Use the returned DataFrame or explicitly assign it back. |
Create a DataFrame containing customer names, ages, and email addresses. Count the missing values in each column and calculate their percentages.
Create a dataset of product prices with missing entries. Compare filling the missing prices with the mean and median. Explain which method is more appropriate when the data contains extreme prices.
Create a dataset containing repeated employee records. Use duplicated() to identify duplicates and drop_duplicates() to remove exact repeats.
Use values such as "mumbai", "Mumbai", and " Mumbai ". Standardize them and count the distinct city names before and after cleaning.
Write a Python program that reports the number of rows, number of columns, missing values per column, missing-value percentages, duplicate row count, and data types. Save the cleaned dataset to a CSV file.
Project objective: Clean a customer dataset before preparing it for analysis or reporting.
import pandas as pd
df = pd.read_csv("customers.csv")
original_df = df.copy()
# Standardize text fields when present
if "Name" in df.columns:
df["Name"] = df["Name"].str.strip()
if "Email" in df.columns:
df["Email"] = df["Email"].str.strip().str.lower()
# Remove exact duplicate rows
df = df.drop_duplicates()
# Generate a basic quality report
report = pd.DataFrame({
"Missing Count": df.isna().sum(),
"Missing Percentage": (df.isna().mean() * 100).round(2),
"Data Type": df.dtypes.astype(str)
})
print(report)
print("Rows before cleaning:", len(original_df))
print("Rows after cleaning:", len(df))
df.to_csv("customers_cleaned.csv", index=False)
report.to_csv("customer_quality_report.csv")
print("Cleaning and export completed.")This is a starting point, not a universal cleaning policy. Add column-specific imputation, validation, and duplicate handling based on the real dataset's requirements.
Answer: Data cleaning is the process of detecting and correcting incomplete, inaccurate, inconsistent, duplicated, or incorrectly formatted data to improve data quality.
Answer: isna() and isnull() identify missing values. Methods such as sum() and mean() can then summarize their counts or percentages.
Answer: dropna() removes rows or columns based on missing-value conditions, while fillna() replaces missing values with specified values or strategies.
Answer: duplicated() identifies duplicate rows or values according to selected columns. drop_duplicates() returns a DataFrame with the selected duplicate records removed.
Answer: The mean is often appropriate for relatively symmetric numeric data without extreme outliers. The median is more robust to outliers and is often preferred for skewed distributions.
Answer: It retains the first occurrence of a duplicate group and removes later occurrences. This is the default behavior.
Answer: The subset parameter specifies the columns used to determine whether records are duplicates.
Answer: Repeated values may represent legitimate transactions or separate people. Deleting them without understanding the business rules can remove valid data.
Answer: Forward filling replaces a missing value with the preceding available value. It is commonly used for ordered or time-series data when appropriate.
Answer: String operations such as str.strip(), str.lower(), and str.title() can standardize whitespace and letter case where suitable.
Answer: It converts values into numeric types. With errors="coerce", values that cannot be converted become missing values.
Answer: Validation confirms that cleaning rules worked as expected and checks that missing values, duplicates, invalid types, or inconsistent records have been handled appropriately.
isna() and isnull() to detect missing values.dropna() to remove incomplete records when justified.fillna() for appropriate imputation strategies.ffill(), bfill(), or interpolation only when suitable.duplicated() to detect repeated records.drop_duplicates() with deliberate duplicate rules.Cleaning data and handling missing or duplicate values are essential skills for Python developers, data analysts, and machine learning practitioners. Pandas makes these tasks manageable through methods such as isna(), fillna(), dropna(), duplicated(), and drop_duplicates().
The most important principle is to clean data according to its meaning, not simply to remove every unusual value. A reliable workflow includes inspecting the data, applying justified transformations, validating the results, and preserving the original information so that the process remains transparent and reproducible.
