Data Cleaning Techniques

Jr Codex Data Science Notes

Level: Intermediate Prerequisites: Chapter 2 Time to complete: ~25 minutes

Table of Contents

1. Why Data Cleaning Takes So Long
2. Inconsistent Text Formatting
3. Fixing Data Types
4. Parsing Dates
5. Renaming & Reordering Columns
6. Validating Data Against Expected Rules
7. Building a Repeatable Cleaning Pipeline
8. Summary & Next Steps

1. Why Data Cleaning Takes So Long

The oft-quoted (and roughly accurate) claim in data science: data cleaning takes up 60-80% of a real project's time. Data entered by humans, merged from multiple systems, or scraped from the web arrives full of inconsistencies that would break any analysis run on it directly.

Common Real-World Data Problems
─────────────────────────────────────────
  "New York", "new york", "NYC", "New York City"    ← same value, 4 spellings
  " 25 ", "25", "twenty-five"                          ← inconsistent formatting
  "2024-01-15", "01/15/2024", "Jan 15, 2024"             ← inconsistent date formats
  age = -5, age = 200                                       ← impossible values
  price = "N/A", price = "", price = None                     ← missing data, 3 different ways
─────────────────────────────────────────

2. Inconsistent Text Formatting

import pandas as pd
 
df = pd.DataFrame({
    "city": [" New York", "new york", "NEW YORK", "Boston ", "BOSTON"],
})
 
# Standardize case and whitespace
df["city_clean"] = df["city"].str.strip().str.lower()
print(df["city_clean"].unique())      # ['new york' 'boston'] — collapsed from 5 variants to 2
 
# Standardize further with a mapping, for known synonyms
city_mapping = {"nyc": "new york", "new york city": "new york"}
df["city_clean"] = df["city_clean"].replace(city_mapping)

# Removing unwanted characters (e.g. from scraped or manually entered data)
prices = pd.Series(["$45.00", "$1,200.50", "$8.99"])
prices_clean = prices.str.replace("[$,]", "", regex=True).astype(float)
print(prices_clean)      # [45.0, 1200.5, 8.99]

Every one of these .str methods leans on Module 1, Chapter 4's string methods from the Python Notes — pandas simply applies them across an entire column at once instead of one string at a time.

3. Fixing Data Types

Data loaded from CSV or scraped from the web frequently arrives as the wrong type — most often, numbers stuck as strings.

import pandas as pd
 
df = pd.DataFrame({
    "price": ["19.99", "25.50", "N/A", "8.00"],
    "quantity": ["3", "5", "2", "invalid"],
})
 
print(df.dtypes)      # both columns are 'object' (string) — not usable for math yet
 
# pd.to_numeric() converts, with control over what happens to bad values
df["price_clean"] = pd.to_numeric(df["price"], errors="coerce")     # "N/A" becomes NaN
df["quantity_clean"] = pd.to_numeric(df["quantity"], errors="coerce")
 
print(df)
#    price quantity  price_clean  quantity_clean
# 0  19.99        3        19.99             3.0
# 1  25.50        5        25.50             5.0
# 2    N/A        2          NaN             2.0
# 3   8.00  invalid         8.00             NaN

errors= Parameter — the Key Decision
─────────────────────────────────────────
  errors="raise"    → (default) STOPS immediately on any bad value — safest for validation
  errors="coerce"     → converts what it can, turns anything unparseable into NaN
  errors="ignore"       → leaves the ENTIRE column untouched if ANY value fails (rarely useful)
─────────────────────────────────────────

4. Parsing Dates

Dates are a frequent source of subtle bugs — the same date can be written many ways, and ambiguous formats (01/02/2024 — January 2nd, or February 1st?) can silently misparse.

import pandas as pd
 
df = pd.DataFrame({
    "date_str": ["2024-01-15", "01/16/2024", "Jan 17, 2024"],
})
 
df["date"] = pd.to_datetime(df["date_str"])      # pandas infers the format automatically, in most cases
print(df["date"])
print(df["date"].dtype)      # datetime64[ns] — now a REAL date type, not a string
 
# Once parsed, dates unlock useful operations:
df["year"] = df["date"].dt.year
df["day_of_week"] = df["date"].dt.day_name()
df["days_since"] = (pd.Timestamp.now() - df["date"]).dt.days

# Being EXPLICIT about the format avoids ambiguity and is faster on large datasets
df["date"] = pd.to_datetime(df["date_str"], format="%Y-%m-%d", errors="coerce")

Best practice: when you know the source format, always pass format= explicitly — relying on automatic inference is convenient for quick exploration, but can silently misinterpret ambiguous dates (like 01/02/2024) on inconsistent real-world data.

5. Renaming & Reordering Columns

Small, but this is what makes a dataset pleasant (or painful) to work with for the rest of a project.

import pandas as pd
 
df = pd.DataFrame({
    "Customer Name": ["Alice", "Bob"],
    "  Total_Spend  ": [150.0, 200.0],
    "signupDate": ["2024-01-01", "2024-02-15"],
})
 
# Standardize to snake_case, consistent with Python's own naming convention (Python Notes, Module 1)
df.columns = (
    df.columns
    .str.strip()
    .str.lower()
    .str.replace(" ", "_")
)
print(df.columns)      # Index(['customer_name', 'total_spend', 'signupdate'], dtype='object')
 
# Rename specific columns explicitly
df = df.rename(columns={"signupdate": "signup_date"})

6. Validating Data Against Expected Rules

Cleaning isn't just fixing formatting — it's also catching values that are impossible given what the column represents.

import pandas as pd
 
df = pd.DataFrame({
    "age": [25, -5, 34, 200, 45],
    "email": ["a@x.com", "invalid_email", "b@x.com", "", "c@x.com"],
})
 
# Flag impossible ages
invalid_ages = df[(df["age"] < 0) | (df["age"] > 120)]
print(invalid_ages)      # rows with age=-5 and age=200
 
# Flag malformed emails with a simple check
invalid_emails = df[~df["email"].str.contains("@", na=False)]
print(invalid_emails)      # rows with "invalid_email" and ""

Cleaning Decision, Once Invalid Data Is Found
─────────────────────────────────────────
  1. Fix it — if the correct value can be confidently inferred
  2. Remove the row — if the record is unsalvageable and not critical
  3. Flag and keep — mark it (e.g. a new is_valid column) for downstream awareness
  4. Set to missing (NaN) — treat as unknown, handle via Chapter 4's missing-data techniques
─────────────────────────────────────────

There's no universal right answer here — the correct choice depends on what the data will be used for, and losing real records silently is often worse than leaving a visible gap.

7. Building a Repeatable Cleaning Pipeline

For any real project, wrap cleaning steps into a function — directly applying Python Notes, Module 2's function-writing principles — so the exact same cleaning logic can run consistently on new data later.

import pandas as pd
 
def clean_customer_data(df: pd.DataFrame) -> pd.DataFrame:
    """Apply standard cleaning steps to raw customer data."""
    df = df.copy()      # never mutate the caller's original DataFrame
 
    # Standardize column names
    df.columns = df.columns.str.strip().str.lower().str.replace(" ", "_")
 
    # Clean text fields
    if "city" in df.columns:
        df["city"] = df["city"].str.strip().str.lower()
 
    # Fix types
    if "signup_date" in df.columns:
        df["signup_date"] = pd.to_datetime(df["signup_date"], errors="coerce")
 
    if "age" in df.columns:
        df["age"] = pd.to_numeric(df["age"], errors="coerce")
        df.loc[(df["age"] < 0) | (df["age"] > 120), "age"] = None      # invalidate impossible ages
 
    return df
 
raw_df = pd.read_csv("raw_customers.csv")
clean_df = clean_customer_data(raw_df)

Writing cleaning as a function (rather than one-off notebook cells run in whatever order) means the exact same logic can be re-applied to tomorrow's data dump — and it's directly testable with pytest (Python Notes, Module 5), the same way any other function is.

8. Summary & Next Steps

Key Takeaways

• Data cleaning routinely consumes the majority of a real project's time — inconsistent text, wrong types, and ambiguous dates are the most common culprits.
• .str methods and pd.to_numeric(..., errors="coerce") are the standard tools for fixing text and numeric formatting issues.
• Always parse dates explicitly with pd.to_datetime(..., format=...) when the source format is known, to avoid silent misparsing of ambiguous dates.
• Validation isn't just formatting — check for impossible values (negative ages, malformed emails) and decide deliberately whether to fix, remove, flag, or null them.
• Wrap cleaning logic in a reusable function so it can be applied consistently to new data and tested like any other code.

Concept Check

1. Why is errors="coerce" often preferred over the default errors="raise" when converting a messy column to numeric?
2. Why should dates be parsed with an explicit format= string when possible, rather than relying on automatic inference?
3. What are the four general options once you've identified an invalid value in a dataset?

Next Chapter

→ Chapter 4: Handling Missing & Duplicate Data

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index