Data Cleaning: 80% of Data Scientist's Job (Explained)

The most common quote from data scientists: "I spend 80% of my time cleaning data and 20% building models."

Treat that 80% as a folk figure rather than a precise measurement. It is a contested survey statistic: a 2016 CrowdFlower survey of about 80 data scientists, reported by Forbes, found 60% of time went to cleaning and organizing data and 19% to collecting data sets (together about 80%), while Anaconda's 2020 State of Data Science survey of 2,360 respondents found about 45% of time went to loading and cleaning data. The exact share varies by role and company, but every survey agrees that data preparation takes a large share of the job.

That might sound frustrating, but it's the reality — and it's actually a good thing to understand before you start learning data science. This guide explains why data cleaning takes so long, what you're actually doing, and how to do it efficiently with Python.

---

What is Data Cleaning?

Data cleaning (also called data wrangling or data preprocessing) is the process of transforming messy, incomplete, or inconsistent raw data into a format that's suitable for analysis or machine learning.

Real data comes from humans, sensors, legacy databases, and manual entry. All of these introduce errors. Before your model can learn anything useful, the data needs to be clean.

---

The Messy Data Reality

Here's an example of what raw customer data actually looks like when you first load it:

Name: "john doe" → Should be "John Doe"
Age: "thirty-five" → Should be 35 (integer)
Salary: "-50,000" → Negative? Comma in number?
Email: "john @gmail.com" → Space in email — invalid
Join Date: "05/13/26" → Is that 2026 or 1926?
Country: "US", "USA", "United States" → Three formats, same meaning
Phone: null → Missing
Duplicate row: 2x → Same person entered twice

Before you can analyze anything, every one of these problems needs a decision. That's data cleaning.

---

The 7 Most Common Data Problems

1. Missing Values (Nulls)

The most common problem. A user didn't fill in their phone number. A sensor dropped a reading. A database migration left fields blank.

import pandas as pd

df = pd.read_csv('customers.csv')

# Find missing values
print(df.isnull().sum())

# Percentage missing per column
print(df.isnull().mean() * 100)

Solutions: Delete rows (if few), fill with mean/median, or use imputation. See How to Handle Missing Data for a full guide.

2. Duplicates

Data gets entered twice, merged from multiple sources, or accidentally duplicated during ETL.

# Find duplicates
print(df.duplicated().sum())

# See which rows are duplicates
print(df[df.duplicated()])

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

# Remove duplicates based on specific columns
df = df.drop_duplicates(subset=['email'])

3. Wrong Data Types

Numbers stored as strings, dates as objects, booleans as 0/1 integers.

# Check types
print(df.dtypes)

# Fix types
df['age'] = df['age'].astype(int)
df['salary'] = df['salary'].astype(float)
df['signup_date'] = pd.to_datetime(df['signup_date'])
df['is_active'] = df['is_active'].astype(bool)

4. Formatting Issues

Inconsistent capitalization, whitespace, phone number formats, country codes.

# Fix text
df['name'] = df['name'].str.title() # "john doe" → "John Doe"
df['email'] = df['email'].str.lower().str.strip() # Remove spaces, lowercase
df['phone'] = df['phone'].str.replace(r'\D', '', regex=True) # Remove non-digits

# Standardize country codes
df['country'] = df['country'].map({
 'US': 'USA',
 'U.S.': 'USA',
 'United States': 'USA',
 'USA': 'USA',
 'UK': 'GBR',
 'United Kingdom': 'GBR'
})

5. Outliers

A salary of $10,000,000 when most are $50,000-$200,000. An age of 300. A transaction of -$999,999.

import matplotlib.pyplot as plt

# Visualize distribution to spot outliers
df['salary'].hist(bins=50)
plt.show()

# Statistical approach: IQR method
Q1 = df['salary'].quantile(0.25)
Q3 = df['salary'].quantile(0.75)
IQR = Q3 - Q1

lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

# Filter to valid range
df_clean = df[(df['salary'] >= lower_bound) & (df['salary'] <= upper_bound)]

# How many outliers removed?
print(f"Removed {len(df) - len(df_clean)} outliers")

Note: Outliers aren't always errors. A legitimate million-dollar sale is a real data point. Decide based on domain knowledge whether to remove, keep, or cap outliers.

6. Inconsistent Categories

"Yes", "YES", "yes", "Y", "1", "True" — all meaning the same thing.

# Check unique values in categorical columns
print(df['gender'].value_counts())
# Output: Male: 450, male: 120, M: 80, MALE: 40

# Standardize
df['gender'] = df['gender'].str.lower().str.strip()
df['gender'] = df['gender'].map({
 'male': 'Male',
 'm': 'Male',
 'female': 'Female',
 'f': 'Female'
})

7. Invalid Values

A negative age. An email without "@". A future birthdate.

# Remove invalid emails
valid_email = df['email'].str.contains('@', na=False)
df = df[valid_email]

# Remove negative ages
df = df[df['age'] > 0]
df = df[df['age'] < 120] # Reasonable upper bound

# Assert on key constraints
assert (df['age'] > 0).all(), "Negative ages found!"
assert df['email'].str.contains('@').all(), "Invalid emails found!"

---

Why It Takes So Much of Your Time

Here's an illustrative (not surveyed) breakdown of a 100-hour data science project. Cleaning alone is 40 hours, but all the preparation work before modeling (cleaning, exploration and feature engineering) adds up to 75 hours, which is how the folk "80%" figure arises when "cleaning" is used loosely for all data preparation:

Total: 100 hours

Cleaning & preprocessing: 40 hours (40%)
Exploration & understanding: 25 hours (25%)
Feature engineering: 10 hours (10%)
Model building & training: 8 hours (8%)
Model tuning: 7 hours (7%)
Deployment & documentation: 5 hours (5%)
Presentation: 5 hours (5%)

The model itself might take 8 hours. Everything before it takes 75+.

Why? Because:

• You don't know what's wrong until you look
• Every decision (delete this row vs fill it) requires judgment
• Business context matters: what does a null salary mean in this context?
• You find new problems after cleaning others
• Domain experts need to review your decisions

---

A Complete Data Cleaning Workflow

import pandas as pd
import numpy as np

# 1. Load and inspect
df = pd.read_csv('raw_data.csv')

print("Shape:", df.shape)
print("\nTypes:\n", df.dtypes)
print("\nMissing:\n", df.isnull().sum())
print("\nStats:\n", df.describe())
df.head()

# 2. Fix types
df['age'] = pd.to_numeric(df['age'], errors='coerce') # Non-numeric → NaN
df['date'] = pd.to_datetime(df['date'], errors='coerce')
df['salary'] = df['salary'].str.replace(',', '').astype(float)

# 3. Remove duplicates
df = df.drop_duplicates(subset=['email'])

# 4. Handle missing values
df['age'] = df['age'].fillna(df['age'].median())
df = df.dropna(subset=['email', 'user_id']) # Can't impute these

# 5. Fix formatting
df['email'] = df['email'].str.lower().str.strip()
df['name'] = df['name'].str.title()

# 6. Remove outliers
Q1, Q3 = df['salary'].quantile([0.25, 0.75])
IQR = Q3 - Q1
df = df[(df['salary'] >= Q1 - 1.5*IQR) & (df['salary'] <= Q3 + 1.5*IQR)]

# 7. Validate
assert df.isnull().sum().sum() == 0, "Still have nulls!"
assert df.duplicated().sum() == 0, "Still have duplicates!"

# 8. Save
df.to_csv('clean_data.csv', index=False)
print("Done! Clean shape:", df.shape)

---

Pandas Quick Reference

# Inspect
df.info() # Types + non-null counts
df.describe() # Stats for numeric columns
df.isnull().sum() # Count missing per column
df.duplicated().sum() # Count duplicate rows
df.nunique() # Unique values per column
df['col'].value_counts() # Frequency of each value

# Clean
df.dropna() # Remove rows with any null
df.drop_duplicates() # Remove duplicate rows
df['col'].fillna(val) # Fill nulls with value
df['col'].astype(int) # Change type
df['col'].str.lower() # Lowercase strings
df['col'].str.strip() # Remove whitespace
df['col'].str.replace('old', 'new') # Replace text

---

Tools Beyond Pandas

Tool	Use Case
Pandas	Standard Python data cleaning
Great Expectations	Data validation and testing
dbt	SQL-based cleaning in data pipelines
Apache Spark	Big data (millions+ rows)
Trifacta	Visual data cleaning, enterprise
OpenRefine	Spreadsheet-like cleaning tool

For most data science work, Pandas covers 95% of what you need.

---

How to Get Faster at It

1. Write reusable functions

Stop copy-pasting the same cleaning code. Build a library:

def clean_emails(series):
 return series.str.lower().str.strip()

def remove_outliers(series, factor=1.5):
 Q1, Q3 = series.quantile([0.25, 0.75])
 IQR = Q3 - Q1
 return series[(series >= Q1 - factor*IQR) & (series <= Q3 + factor*IQR)]

2. Explore before cleaning

Don't clean blindly. Understand what you have first. A profiling report tells you everything:

# pip install ydata-profiling
from ydata_profiling import ProfileReport

report = ProfileReport(df)
report.to_file("report.html")

3. Document your decisions

Write a comment or README explaining every major cleaning decision. You'll forget why you deleted those 200 rows in three weeks.

---

Data Quality Frameworks and Validation Rules

Professional data pipelines implement validation frameworks that define what "clean" means before data arrives. This prevents surprises downstream.

Great Expectations is an open-source Python library for data validation. You write expectations, then validate incoming data against them:

import great_expectations as gx

context = gx.get_context()
df = pd.read_csv('new_data.csv')

expectations = [
 'column_values_should_be_null' <= 5%,  # Allow 5% nulls
 'column_values_should_be_of_type[integer]' for 'age',
 'column_values_should_be_between[0, 120]' for 'age',
 'column_values_should_be_unique' for 'email',  # No duplicates
]

suite = context.add_expectation_suite('my_suite', expectations)
context.run_validation_operator(suite, df)

If validation fails, the pipeline stops. No bad data reaches your model.

Common validation rules:

• Type checks: Column X must be numeric, date, string, etc.
• Range checks: Age between 0 and 120; salary > 0.
• Uniqueness: Email should be unique per record.
• Referential integrity: Every order_id must exist in the orders table.
• Temporal checks: Order date ≤ today.
• Pattern checks: Phone number matches ^\d{10}$.

The breakdown we showed assumes you didn't have validation rules. With them, cleaning time typically shrinks because you catch problems systematically instead of discovering them mid-analysis.

The Data Pipeline Lifecycle

In production, data flows through stages, and cleaning happens at each:

Raw data → Ingestion → Validation → Transformation → Storage → Analysis
   ↓          ↓           ↓             ↓              ↓          ↓
  CSV,       ETL        Great          Pandas,       Data      Models,
  API        extract    Expectations   dbt, SQL      warehouse reports
  Stream

Ingestion: Extract data from source (database, API, file). Minimal transformation yet.

Validation: Apply Great Expectations or similar. Reject invalid data before it enters the warehouse.

Transformation: Heavy lifting. Fix types, handle nulls, join tables, compute derived columns.

Storage: Load into data warehouse (Snowflake, BigQuery, Postgres). Data here is "gold standard" for downstream use.

Analysis: Data scientists work with the warehouse data (already clean). Much of the effort shifts to exploratory analysis and feature engineering, not cleaning.

This separation is crucial. Many teams do all cleaning on-the-fly during analysis. Professional teams clean in the pipeline, so analysts inherit clean data.

Detecting Data Quality Issues Programmatically

Beyond manual inspection, automated detection catches systematic problems:

# 1. Identify columns with high null rates
null_rate = df.isnull().sum() / len(df)
high_null_cols = null_rate[null_rate > 0.5].index
print(f"Columns >50% null: {list(high_null_cols)}")

# 2. Find columns with very low cardinality (possibly redundant)
for col in df.columns:
    unique = df[col].nunique()
    cardinality = unique / len(df)
    if cardinality < 0.001:
        print(f"{col}: {unique} unique values ({cardinality*100:.2f}%)")

# 3. Detect outliers via Z-score
from scipy import stats
z_scores = np.abs(stats.zscore(df[['salary', 'age', 'tenure']]))
outliers = (z_scores > 3).any(axis=1)
print(f"Outliers (|z| > 3): {outliers.sum()} rows")

# 4. Find quasi-duplicates (same record, slightly different)
from fuzzywuzzy import fuzz
# Compare rows; flag if similarity > 90%

# 5. Check for data leakage (target variable in features)
# Look for columns with suspiciously high correlation to target
correlations = df.corr()[target_col].sort_values()
print(correlations)

These checks take minutes but catch hours of downstream pain.

Common Industry Pitfalls and Solutions

Problem 1: Cleaning in notebooks, not pipelines.
Your colleague runs a Jupyter notebook, cleans data, builds a model. Model is live. Six months later, source data changes format; model breaks. The cleaning code was in the notebook, never version-controlled or tested.

Solution: Put cleaning in a dbt project or Python module. Test it. Version control it. Run it in production every time data arrives.

Problem 2: Dropping rows too aggressively.
You have 100K rows, find 500 with invalid emails (missing @), and drop them. Analysis proceeds on 99.5K rows. But those 500 rows represent a segment of users (email-optional signup). Deleting them biases your model.

Solution: Keep invalid data, flag it, and stratify by validity in analysis. Only drop after understanding the impact.

Problem 3: Imputing without documenting.
You fill missing ages with the median (35). A year later, you can't remember which ages were real vs. imputed. When age correlates with outcome, you can't tell if it's real or artifact.

Solution: Create a flag column: age_imputed = df['age'].isna(). Impute, then keep the flag. Later, you can analyze with/without imputed rows.

Problem 4: Inconsistent cleaning across splits.
You clean the training set one way, the test set another (or not at all). Model performs great on test, fails in production on new data.

Solution: Clean the entire dataset first, then split. Never clean train and test separately.

---

Frequently Asked Questions

Q: Should I clean before or after exploratory data analysis?
A: Do a quick exploration first (df.describe(), df.isnull().sum()) to understand what's wrong, then clean systematically. Blind cleaning can waste time fixing non-issues. The 10-minute exploratory step saves hours.

Q: How clean does data need to be before I start modeling?
A: No nulls in critical columns (IDs, target variable), correct data types, no obvious duplicates, no known invalid values. Perfection is impossible and often not needed. Aim for "good enough for analysis," not "perfectly clean."

Q: What's the fastest way to clean a large dataset?
A: (1) Write reusable cleaning functions instead of one-off code. (2) Use profiling tools (Great Expectations, ydata-profiling) to spot issues in seconds. (3) Document decisions so you don't re-clean the same data. (4) Use vectorized Pandas operations, not loops.

Q: Should data cleaning happen once or continuously in production?
A: Continuously. New data arrives daily; cleaning must happen in the pipeline every time. Build cleaning as part of your ETL (extract-transform-load), not as a one-time script. Data quality degrades over time as source systems change.

Q: What's the difference between cleaning and feature engineering?
A: Cleaning fixes errors (wrong types, nulls, duplicates). Feature engineering creates new columns (e.g., age_group from age, day_of_week from date). Cleaning makes data valid; feature engineering makes it useful for modeling.

Q: How do I avoid deleting important data while cleaning?
A: Before deleting any rows, understand why they're invalid and whether that segment matters. Use flag columns to mark invalid records. Later, you can rerun analysis with/without them to measure impact. Never silently drop data.

---

Advanced Cleaning Patterns for Specific Data Types

Different data types require different cleaning strategies.

Numerical Data

Handling negative values:

# Salary shouldn't be negative
df = df[df['salary'] > 0]

# But refunds or debits might be legitimately negative
df = df[(df['amount'] > -10000) & (df['amount'] < 100000)]

Handling currency and formatting:

# Remove currency symbols and commas
df['price'] = df['price'].str.replace('$', '').str.replace(',', '').astype(float)

# Handle negative values represented as parentheses: ($1,234.56)
df['amount'] = df['amount'].str.replace(r'\((.*)\)', r'-\1', regex=True).astype(float)

Handling percentages:

# "95%" should become 0.95, not 95
df['completion_rate'] = df['completion_rate'].str.rstrip('%').astype(float) / 100

Date and Time Data

Handling ambiguous dates:

# Is 01/02/03 January 2nd or February 1st?
# Specify format explicitly
df['date'] = pd.to_datetime(df['date'], format='%d/%m/%y')  # day/month/year

# Handle mixed formats (some rows might be "2023-01-15", others "01/15/2023")
df['date'] = pd.to_datetime(df['date'], errors='coerce')  # Coerce ambiguous to NaT

Handling timezones:

# Localize to UTC, then convert to desired timezone
df['timestamp'] = pd.to_datetime(df['timestamp'], utc=True)
df['timestamp'] = df['timestamp'].dt.tz_convert('US/Eastern')

Handling duration and intervals:

# "3 hours 15 minutes" → seconds
def parse_duration(s):
    parts = s.split()
    hours = int(parts[0]) if 'hours' in s else 0
    minutes = int(parts[2]) if 'minutes' in s else 0
    return (hours * 3600) + (minutes * 60)

df['duration_seconds'] = df['duration_text'].apply(parse_duration)

Categorical Data

Handling rare categories:

# "Alabama" appears 5 times; 50,000 other states
# Lump rare categories
value_counts = df['state'].value_counts()
rare = value_counts[value_counts < 10].index
df['state'] = df['state'].apply(lambda x: 'Other' if x in rare else x)

Handling open-ended text (like "Other, please specify"):

# If "Other" category has text in a notes column, extract it
df['category'] = df['category'].where(df['category'] != 'Other', df['notes'])

Text Data

Handling HTML and special characters:

import html

# Decode HTML entities: "&" → "&"
df['description'] = df['description'].apply(html.unescape)

# Remove special characters but keep alphanumeric and spaces
df['text'] = df['text'].str.replace(r'[^\w\s]', '', regex=True)

Handling multiple spellings:

# "color" vs "colour"
replacements = {
    'color': 'colour',
    'gray': 'grey',
    'center': 'centre'
}
df['text'] = df['text'].replace(replacements, regex=True)

Integrating Cleaning into Data Pipelines

In mature organizations, cleaning isn't a one-time Jupyter notebook. It's orchestrated as a reusable, tested, versioned artifact.

dbt (Data Build Tool) for SQL-Based Cleaning

dbt is popular for SQL-based pipelines:

-- models/staging/stg_customers.sql
{{ config(materialized='table') }}

with customers as (
    select
        customer_id,
        lower(trim(email)) as email,
        initcap(name) as name,
        case
            when age < 0 or age > 120 then null
            else age
        end as age,
        case
            when country in ('US', 'USA', 'United States') then 'USA'
            when country in ('UK', 'United Kingdom') then 'GBR'
            else country
        end as country_code
    from raw.customers
    where email like '%@%.%'  -- Basic email validation
        and customer_id is not null
)
select * from customers

Run dbt run and the table is transformed, tested, versioned, and documented. No notebooks, no surprises.

Python-Based Pipelines with Prefect/Airflow

from prefect import flow, task

@task
def extract_data():
    df = pd.read_csv('raw_data.csv')
    return df

@task
def validate_data(df):
    assert df.shape[0] > 0, "Empty dataframe"
    assert df['age'].max() < 120, "Invalid ages"
    return df

@task
def clean_data(df):
    df['email'] = df['email'].str.lower().str.strip()
    df['age'] = df['age'].fillna(df['age'].median())
    df = df.drop_duplicates(subset=['email'])
    return df

@flow
def cleaning_pipeline():
    df = extract_data()
    df = validate_data(df)
    df = clean_data(df)
    df.to_parquet('clean_data.parquet')

if __name__ == '__main__':
    cleaning_pipeline()

Runs on a schedule (daily), logs all transformations, sends alerts on failure, and maintains history.

Testing Your Cleaning Code

Professional pipelines include tests:

import pytest

def test_email_cleaning():
    df = pd.DataFrame({'email': [' TEST@GMAIL.COM ', 'invalid']})
    df['email'] = df['email'].str.lower().str.strip()
    assert df['email'].iloc[0] == 'test@gmail.com'

def test_no_duplicates():
    df = pd.DataFrame({'id': [1, 1, 2]})
    df = df.drop_duplicates()
    assert len(df) == 2

def test_age_validation():
    df = pd.DataFrame({'age': [-5, 0, 25, 150]})
    df = df[(df['age'] > 0) & (df['age'] < 120)]
    assert len(df) == 1

if __name__ == '__main__':
    pytest.main([__file__])

Tests run every time code changes, ensuring cleaning doesn't break.

Measuring Data Quality

Modern organizations track data quality with metrics:

Completeness: % of non-null values per column.

completeness = (1 - df.isnull().sum() / len(df)) * 100

Accuracy: % of values that pass validation rules.

valid_emails = df['email'].str.contains(r'^[\w\.\-]+@[\w\.\-]+\.\w+$').sum()
accuracy = (valid_emails / len(df)) * 100

Consistency: % of rows that match expected patterns.

consistent = (df['age'] >= 0) & (df['age'] <= 120)
consistency = (consistent.sum() / len(df)) * 100

Timeliness: How recent is the data?

df['freshness_days'] = (pd.Timestamp.now() - df['updated_at']).dt.days

Track these over time. A dashboard showing "data completeness: 95% → 92% → 87%" is an early warning that source systems are degrading.

Related Articles

• How to Handle Missing Data — Imputation Strategies
• SQL for Data Science
• Python for Data Science: Essential Libraries and Workflows

---

The takeaway

Data cleaning is not optional — it's foundational. Models trained on dirty data fail. Models trained on well-cleaned data work reliably. The large share of time it takes isn't wasted; it's the difference between working systems and impressive prototypes. Master Pandas, write reusable functions, use validation frameworks, and document every decision. The time you invest in cleaning pays back 10× in model reliability and maintenance.

Last verified: May 2026