Data Cleaning in Data Mining

A model built on dirty data does not produce a wrong answer that announces itself — it produces a confident, well-formatted, entirely wrong answer. This is what makes data cleaning one of the highest-leverage steps in the whole data mining process: the algorithm applied afterward can be flawless, and the result will still be unreliable if the data feeding it contains duplicates, inconsistent labels, undetected errors, or missing values that were filled in carelessly.

Data cleaning is the process of detecting and correcting problems in a dataset — records that are inaccurate, incomplete, duplicated, inconsistently formatted, or irrelevant to the analysis — before that dataset is used for modeling.

A Worked Example: One Messy Table

The clearest way to see what data cleaning actually does is to walk through it on a small, realistic table of customer records:

import pandas as pd
import numpy as np

data = {
    "customer_id": [101, 102, 103, 103, 104, 105, 106],
    "age": [34, 29, np.nan, np.nan, 45, 214, 31],
    "city": ["Mumbai", "delhi", "Mumbai", "Mumbai", "N/A", "Bangalore", "Not Applicable"],
    "purchase_amount": [1200.50, 850.00, 630.00, 630.00, 2100.00, 500.00, np.nan],
}
df = pd.DataFrame(data)
print(df)

Output:

   customer_id    age            city  purchase_amount
0          101   34.0          Mumbai           1200.5
1          102   29.0           delhi            850.0
2          103    NaN          Mumbai            630.0
3          103    NaN          Mumbai            630.0
4          104   45.0             N/A           2100.0
5          105  214.0       Bangalore            500.0
6          106   31.0  Not Applicable              NaN

This one small table already contains every major category of problem data cleaning exists to fix: a duplicated customer, inconsistent text labels for the same thing, an impossible value, and missing entries. Each is handled differently, and the order matters.

Step 1: Remove Duplicate or Irrelevant Records

Row 3 is an exact duplicate of row 2 (customer_id 103, same age, same city, same purchase amount). Duplicates like this typically enter a dataset during collection, scraping, or when merging records from multiple source systems — the same customer captured twice by two different systems being combined.

df1 = df.drop_duplicates(subset='customer_id', keep='first')
print(len(df1), "rows remain")

6 rows remain

Leaving the duplicate in would silently double-count that customer's purchase amount in any aggregate calculation — a small, easy-to-miss error that compounds across every summary statistic computed afterward.

Step 2: Fix Structural Errors

The city column contains four different-looking representations of what should be at most three real categories: "Mumbai" is consistent, but "delhi" should match "Delhi", and "N/A" and "Not Applicable" both mean "missing" but are stored as text rather than as an actual missing value:

df1['city'] = df1['city'].replace({
    'N/A': None,
    'Not Applicable': None,
    'delhi': 'Delhi',
})

   customer_id       city
0          101     Mumbai
1          102      Delhi
2          103     Mumbai
4          104       None
5          105  Bangalore
6          106       None

Left unfixed, a system grouping records by city would treat "delhi" and "Delhi" as two separate categories, silently splitting one city's customers into two undercounted groups — and it would treat "N/A" and "Not Applicable" as two more real (but meaningless) cities, rather than as missing data.

Step 3: Handle Outliers — Investigate Before Deciding

Row 5 records an age of 214, which is not a real human age under any circumstance — it is almost certainly a data entry error, perhaps a stray extra digit. Computing the average age before and after addressing it shows exactly how much damage one bad value does:

before = df1['age'].mean()
df1.loc[df1['age'] > 120, 'age'] = np.nan
after = df1['age'].mean()
print(f"mean age with the error included: {before:.1f}")
print(f"mean age with the error treated as invalid: {after:.1f}")

mean age with the error included: 70.6
mean age with the error treated as invalid: 34.8

One bad value out of six pulled the average age from a realistic 34.8 up to a nonsensical 70.6 — more than double. This is the general danger of outliers in a small dataset: with few records to average over, a single corrupted value has an outsized effect on every summary statistic computed from the column. Not every outlier is an error, though — before deciding to remove one, it needs to be checked against what is actually plausible for that field (a domain rule: human age cannot exceed roughly 120) rather than removed automatically just for being extreme.

Step 4: Handle Missing Data

After step 3, the age column has three missing values (the original two, plus the corrected 214). A common approach is to fill missing numeric values with the column's median rather than its mean, specifically because the median is far less sensitive to whatever outliers remain:

median_age = df1['age'].median()
df1['age'] = df1['age'].fillna(median_age)
print("median used to fill:", median_age)

median used to fill: 32.5

Filling with the median (32.5) rather than the mean is a deliberate choice here: the mean is still influenced by any remaining skew in the data, while the median — the middle value when the data is sorted — is stable regardless of how extreme the tails are. This is not free of assumptions, though: filling a missing value with any single number for the whole dataset understates how much the true value could actually vary, so this technique is a practical compromise, not a way to recover information that was never collected.

The purchase_amount for customer 106 is still missing after this pass. Dropping that entire row is one option, since a single record with one missing field usually costs less information than inventing a purchase amount for a field this important to keep. Which choice is right depends on how much the field matters to the analysis and how many records would be lost.

Techniques for Data Cleaning, Summarized

The steps above are examples of five general-purpose techniques used across most data cleaning work:

• Ignore the record. Drop rows with missing or invalid values, typically when several fields in that row are compromised at once, making the record unreliable as a whole.
• Fill missing values. Replace them with the column's mean, median, most frequent value, or an estimate predicted from the record's other attributes — median is generally safer than mean when outliers are present, exactly as shown above.
• Binning. Sort values into groups and smooth each group toward a representative value (such as the bin's mean or boundary), which reduces the effect of small, noisy fluctuations in numeric data.
• Regression-based imputation. Predict a missing value from its relationship with other variables — simple linear regression when one other variable is informative, multiple regression when several are.
• Clustering-based detection. Group similar records together; a record that does not fit well into any cluster is a candidate outlier worth checking, using the same investigate-before-removing discipline as Step 3 above.

Where Data Cleaning Fits in Larger Data Tasks

• Data integration — when records from multiple source systems are combined into one dataset, cleaning is what makes categories, units, and formats consistent across the merged sources (exactly what Step 2 did for the city column above).
• ETL pipelines — during Extract, Transform, Load processing, cleaning ensures that only validated data reaches the reporting or analysis layer, rather than propagating an upstream system's data-entry mistakes downstream.
• Data migration — when data moves from one system or format to another, cleaning preserves correct structure and consistency through the move.

Judging Whether Data Is Actually Clean

Cleaned data can be evaluated against a short list of properties, each of which maps to a step above:

A dataset can score well on one of these and poorly on another — the customer table above was reasonably accurate and consistent to start, but failed uniqueness and completeness. Data cleaning is the work of bringing all four properties up together, not fixing whichever problem happens to be most visib

A model built on dirty data does not produce a wrong answer that announces itself — it produces a confident, well-formatted, entirely wrong answer. This is what makes data cleaning one of the highest-leverage steps in the whole data mining process: the algorithm applied afterward can be flawless, and the result will still be unreliable if the data feeding it contains duplicates, inconsistent labels, undetected errors, or missing values that were filled in carelessly.

Data cleaning is the process of detecting and correcting problems in a dataset — records that are inaccurate, incomplete, duplicated, inconsistently formatted, or irrelevant to the analysis — before that dataset is used for modeling.

A Worked Example: One Messy Table

The clearest way to see what data cleaning actually does is to walk through it on a small, realistic table of customer records:

import pandas as pd
import numpy as np

data = {
    "customer_id": [101, 102, 103, 103, 104, 105, 106],
    "age": [34, 29, np.nan, np.nan, 45, 214, 31],
    "city": ["Mumbai", "delhi", "Mumbai", "Mumbai", "N/A", "Bangalore", "Not Applicable"],
    "purchase_amount": [1200.50, 850.00, 630.00, 630.00, 2100.00, 500.00, np.nan],
}
df = pd.DataFrame(data)
print(df)

Output:

   customer_id    age            city  purchase_amount
0          101   34.0          Mumbai           1200.5
1          102   29.0           delhi            850.0
2          103    NaN          Mumbai            630.0
3          103    NaN          Mumbai            630.0
4          104   45.0             N/A           2100.0
5          105  214.0       Bangalore            500.0
6          106   31.0  Not Applicable              NaN

This one small table already contains every major category of problem data cleaning exists to fix: a duplicated customer, inconsistent text labels for the same thing, an impossible value, and missing entries. Each is handled differently, and the order matters.

Step 1: Remove Duplicate or Irrelevant Records

Row 3 is an exact duplicate of row 2 (customer_id 103, same age, same city, same purchase amount). Duplicates like this typically enter a dataset during collection, scraping, or when merging records from multiple source systems — the same customer captured twice by two different systems being combined.

df1 = df.drop_duplicates(subset='customer_id', keep='first')
print(len(df1), "rows remain")

6 rows remain

Leaving the duplicate in would silently double-count that customer's purchase amount in any aggregate calculation — a small, easy-to-miss error that compounds across every summary statistic computed afterward.

Step 2: Fix Structural Errors

The city column contains four different-looking representations of what should be at most three real categories: "Mumbai" is consistent, but "delhi" should match "Delhi", and "N/A" and "Not Applicable" both mean "missing" but are stored as text rather than as an actual missing value:

df1['city'] = df1['city'].replace({
    'N/A': None,
    'Not Applicable': None,
    'delhi': 'Delhi',
})

   customer_id       city
0          101     Mumbai
1          102      Delhi
2          103     Mumbai
4          104       None
5          105  Bangalore
6          106       None

Left unfixed, a system grouping records by city would treat "delhi" and "Delhi" as two separate categories, silently splitting one city's customers into two undercounted groups — and it would treat "N/A" and "Not Applicable" as two more real (but meaningless) cities, rather than as missing data.

Step 3: Handle Outliers — Investigate Before Deciding

Row 5 records an age of 214, which is not a real human age under any circumstance — it is almost certainly a data entry error, perhaps a stray extra digit. Computing the average age before and after addressing it shows exactly how much damage one bad value does:

before = df1['age'].mean()
df1.loc[df1['age'] > 120, 'age'] = np.nan
after = df1['age'].mean()
print(f"mean age with the error included: {before:.1f}")
print(f"mean age with the error treated as invalid: {after:.1f}")

mean age with the error included: 70.6
mean age with the error treated as invalid: 34.8

One bad value out of six pulled the average age from a realistic 34.8 up to a nonsensical 70.6 — more than double. This is the general danger of outliers in a small dataset: with few records to average over, a single corrupted value has an outsized effect on every summary statistic computed from the column. Not every outlier is an error, though — before deciding to remove one, it needs to be checked against what is actually plausible for that field (a domain rule: human age cannot exceed roughly 120) rather than removed automatically just for being extreme.

Step 4: Handle Missing Data

After step 3, the age column has three missing values (the original two, plus the corrected 214). A common approach is to fill missing numeric values with the column's median rather than its mean, specifically because the median is far less sensitive to whatever outliers remain:

median_age = df1['age'].median()
df1['age'] = df1['age'].fillna(median_age)
print("median used to fill:", median_age)

median used to fill: 32.5

Filling with the median (32.5) rather than the mean is a deliberate choice here: the mean is still influenced by any remaining skew in the data, while the median — the middle value when the data is sorted — is stable regardless of how extreme the tails are. This is not free of assumptions, though: filling a missing value with any single number for the whole dataset understates how much the true value could actually vary, so this technique is a practical compromise, not a way to recover information that was never collected.

The purchase_amount for customer 106 is still missing after this pass. Dropping that entire row is one option, since a single record with one missing field usually costs less information than inventing a purchase amount for a field this important to keep. Which choice is right depends on how much the field matters to the analysis and how many records would be lost.

Techniques for Data Cleaning, Summarized

The steps above are examples of five general-purpose techniques used across most data cleaning work:

• Ignore the record. Drop rows with missing or invalid values, typically when several fields in that row are compromised at once, making the record unreliable as a whole.
• Fill missing values. Replace them with the column's mean, median, most frequent value, or an estimate predicted from the record's other attributes — median is generally safer than mean when outliers are present, exactly as shown above.
• Binning. Sort values into groups and smooth each group toward a representative value (such as the bin's mean or boundary), which reduces the effect of small, noisy fluctuations in numeric data.
• Regression-based imputation. Predict a missing value from its relationship with other variables — simple linear regression when one other variable is informative, multiple regression when several are.
• Clustering-based detection. Group similar records together; a record that does not fit well into any cluster is a candidate outlier worth checking, using the same investigate-before-removing discipline as Step 3 above.

Where Data Cleaning Fits in Larger Data Tasks

• Data integration — when records from multiple source systems are combined into one dataset, cleaning is what makes categories, units, and formats consistent across the merged sources (exactly what Step 2 did for the city column above).
• ETL pipelines — during Extract, Transform, Load processing, cleaning ensures that only validated data reaches the reporting or analysis layer, rather than propagating an upstream system's data-entry mistakes downstream.
• Data migration — when data moves from one system or format to another, cleaning preserves correct structure and consistency through the move.

Judging Whether Data Is Actually Clean

Cleaned data can be evaluated against a short list of properties, each of which maps to a step above:

A dataset can score well on one of these and poorly on another — the customer table above was reasonably accurate and consistent to start, but failed uniqueness and completeness. Data cleaning is the work of bringing all four properties up together, not fixing whichever problem happens to be most visible.