Data Cleaning in Python: A Practical Workflow for Messy Datasets

Data cleaning in Python is the process of turning raw records into a dataset whose structure, values, and limitations are understood well enough for analysis. A practical workflow is not simply “drop nulls and remove duplicates.” It preserves the raw source, defines what one row should represent, profiles the data before changing it, standardizes labels and types, resolves missingness and duplication with business context, flags impossible values, validates joins, and records every important decision. The result should be reproducible, reviewable, and fit for the question you intend to answer.

This skill sits between data collection and every later step in the broader Data Science & AI career guide. Statistics cannot repair a misparsed date. A chart cannot explain duplicated revenue. A machine-learning model cannot distinguish a genuine zero from a missing value that was filled carelessly. Good cleaning protects the meaning of the data before more sophisticated tools make the mistakes harder to see.

The guide below uses a fictional retail-orders dataset to show an end-to-end pandas workflow. The examples are deliberately practical: messy column names, identifiers with leading zeros, currency symbols, mixed date formats, inconsistent categories, duplicate order records, missing customer attributes, implausible quantities, and unmatched lookup keys. The objective is not to make every value look tidy. It is to produce a trustworthy analytical table while keeping evidence of what was changed, excluded, or left unresolved.

Key Takeaways

What Data Cleaning Actually Means

Data cleaning is often described as correcting errors, but that definition is too narrow. A dataset can contain no obvious typing mistakes and still be unfit for its intended use. It may omit a customer segment, mix two definitions of revenue, combine local times without time-zone information, or repeat one order across several source systems. Conversely, an unusual value is not necessarily dirty. A very large transaction, a customer with no surname, or a product sold once may be rare but real.

A useful standard is “fitness for purpose.” The UK Government Data Quality Framework emphasizes that quality depends on the intended use and should be assessed across the data lifecycle. It also treats data quality as broader than one cleaning pass: teams should document limitations, fix causes near the source where possible, and continue monitoring important data.

This distinction prevents overcleaning. The goal is not uniformity at any cost. The goal is to identify which imperfections threaten the intended decision, correct what can be corrected defensibly, and communicate what remains. A forecasting dataset, a financial report, and a marketing contact list may require different thresholds even when they begin from the same source tables.

For readers who want the wider sequence after preparation, Refonte Learning’s article on the end-to-end data science workflow explains how cleaned data moves into exploration, modeling, communication, and action. The present article deliberately stays focused on the cleaning layer.

Preserve the Raw Data and Write a Data Contract First

The safest cleaning workflow has at least three layers: an immutable raw source, a working transformation layer, and a clean output. Never edit the only copy of a CSV in a spreadsheet and save over it. Once the raw evidence is gone, you cannot determine whether a surprising value came from the source or from your own transformation. You also cannot rerun the process when a rule changes.

Core rule: keep raw data immutable. Cleaning code should create a new output and a separate exception report, not rewrite the source in place.

A data contract describes what the dataset is supposed to contain. It does not have to be a formal legal document. For a portfolio project, one page is enough. State the grain, keys, required fields, valid categories, units, date meanings, expected update frequency, and the rules that determine whether a record can enter the final analytical table.

The contract also helps you decide whether a value is dirty. A quantity of zero may be invalid for a completed sale but legitimate for a cancelled line. A blank shipping date may be expected for a digital product or a pending order. The same value can have different meanings depending on status and process stage.

A simple project folder

data-cleaning-project/
|-- data/
|   |-- raw/
|   |   |-- orders.csv
|   |   -- customers.csv
|   |-- clean/
|   |   |-- orders_clean.parquet
|   |   -- orders_exceptions.csv
|-- notebooks/
|   -- 01_profile_and_explore.ipynb
|-- src/
|   |-- cleaning.py
|   -- validation.py
|-- tests/
|   -- test_cleaning.py
|-- reports/
|   |-- quality_before.csv
|   -- quality_after.csv
|-- data_contract.md
`-- README.md

This structure separates exploration from reusable logic. The notebook helps you understand the problems. The functions and tests make the final process repeatable. The clean folder contains outputs, not hand-edited replacements for the raw files.

The Practical Python Data-Cleaning Workflow

The following sequence is designed to reduce accidental information loss. You may return to an earlier step when a later check reveals a new problem, but the order is still useful because it separates observation from intervention.

1. Load Data Safely Without Hiding Problems

Import settings are part of data cleaning. A careless read can remove leading zeros from identifiers, interpret a legitimate code such as “NA” as missing, misread commas inside quoted text, or infer one column as a mixture of numbers and strings. The first objective is therefore not maximum convenience. It is a transparent import that preserves the source as faithfully as possible.

The official pandas read_csv documentation exposes controls for data types, missing-value tokens, date parsing, encodings, bad lines, column selection, chunked reading, and other import behavior. Use those controls deliberately instead of accepting every inferred default.

from pathlib import Path
import pandas as pd

RAW_DIR = Path("data/raw")

id_types = {
    "Order ID": "string",
    "Customer ID": "string",
    "Product ID": "string",
    "Postal Code": "string",
}

orders_raw = pd.read_csv(
    RAW_DIR / "orders.csv",
    dtype=id_types,
    na_values=["", "N/A", "NULL"],
    keep_default_na=True,
    on_bad_lines="error",
    encoding="utf-8",
    low_memory=False,
)

orders = orders_raw.copy()
print(orders.shape)
print(orders.head(3))
print(orders.tail(3))

Identifiers deserve explicit string types because arithmetic is not their purpose. The postal code “00501” should not become 501. A product code such as “0007-A” should not be partly numeric. The same principle applies to phone numbers, account numbers, and labels that happen to contain digits.

Missing tokens should also be field-aware. “NA” may mean not available in one column and Namibia in a country-code column. A dash may represent missing data, zero, or an intentional separator. It is often safer to import ambiguous tokens as text, inspect them, and normalize them only after you understand the column.

Do not skip malformed lines silently just to make the file load. If you use a warning or callable handler, record how many lines were affected and save the rejected records. A clean DataFrame with unexplained missing rows is not a successful import.

Immediate import checks

• Confirm the number of rows and columns against the source documentation or previous delivery.

• Inspect the first, last, and random rows, because sorted files can hide problems at one end.

• Run DataFrame.info() and inspect object or string columns that should be numeric or datetime.

• Check whether the expected key columns exist and whether any column names repeat.

• Record the source filename, retrieval date, and any extraction filters used upstream.

2. Profile the Dataset Before Fixing It

Profiling creates a baseline. Without it, you cannot tell whether cleaning improved the dataset or merely changed it. At minimum, record each column’s type, missing count, missing percentage, number of distinct values, example values, and relevant minimum or maximum. Add dataset-level measures such as row count, exact duplicates, duplicate keys, and memory use.

def profile_frame(df: pd.DataFrame) -> pd.DataFrame:
    rows = len(df)
    profile = pd.DataFrame({
        "dtype": df.dtypes.astype(str),
        "missing_count": df.isna().sum(),
        "missing_pct": (df.isna().mean() * 100).round(2),
        "unique_count": df.nunique(dropna=False),
    })

    def examples(series: pd.Series) -> str:
        values = series.dropna().astype("string").unique()[:5]
        return " | ".join(map(str, values))

    profile["examples"] = [examples(df[col]) for col in df.columns]
    profile["row_count"] = rows
    return profile.reset_index(names="column")

quality_before = profile_frame(orders)
quality_before.to_csv("reports/quality_before.csv", index=False)

print("exact duplicate rows:", orders.duplicated().sum())
print("duplicate order-line keys:",
      orders.duplicated(["Order ID", "Line Number"]).sum())

DataFrame.describe() is useful, but it is not a complete quality audit. It can show numerical ranges and percentiles, yet it will not tell you whether customer IDs are duplicated, whether categories differ only by whitespace, whether a date column contains mixed conventions, or whether a missing-value spike began after a particular source-system release.

Profile patterns, not only totals

Overall missingness can hide a systematic problem. A column may be 5% missing overall but 80% missing for one country, one acquisition channel, or one month. That pattern can change the analysis even when the headline percentage appears small.

missing_by_month = (
    orders
    .assign(source_month=lambda x: x["Source Month"].astype("string"))
    .groupby("source_month", dropna=False)["Customer ID"]
    .agg(rows="size", missing=lambda s: s.isna().sum())
    .assign(missing_pct=lambda x: 100 * x["missing"] / x["rows"])
)

print(missing_by_month)

Keep a few representative anomalies before changing the data. Those examples become test cases later. If “ U.S.A. ”, “us”, and “United States” appear in the country field, record them. If a price contains “$1,299.00” and another uses “1.299,00”, do not build one replacement rule until you know which locale produced each format.

3. Standardize Column Names and Text Carefully

Consistent labels make code easier to read and reduce fragile references. A common convention is lowercase snake case: spaces and punctuation become underscores, leading and trailing underscores are removed, and duplicate normalized names cause an error. The collision check matters. “Order ID” and “Order-ID” would both become order_id; silently keeping both would create ambiguity.

import re


def normalize_column_name(name: object) -> str:
    text = str(name).strip().casefold()
    text = re.sub(r"[^0-9a-z]+", "_", text)
    return text.strip("_")

new_columns = [normalize_column_name(col) for col in orders.columns]

if len(new_columns) != len(set(new_columns)):
    raise ValueError("Column-name normalization created duplicates")

column_map = dict(zip(orders.columns, new_columns))
orders = orders.rename(columns=column_map)
print(column_map)

Text values need more restraint. Trimming surrounding whitespace and collapsing repeated spaces are usually safe for controlled categories. Converting every string to lowercase is not. Free-text comments, product names, addresses, case-sensitive codes, and names can lose information. Apply normalization to columns with a defined vocabulary, and preserve the original text when matching is uncertain.

text_columns = ["status", "country", "acquisition_channel"]

for col in text_columns:
    orders.loc[:, col] = (
        orders[col]
        .astype("string")
        .str.strip()
        .str.replace(r"\s+", " ", regex=True)
    )

orders.loc[:, "status"] = orders["status"].str.casefold()

Modern pandas uses Copy-on-Write behavior, so chained assignment is not a reliable update pattern. The pandas Copy-on-Write guide recommends modifying the DataFrame directly with .loc rather than expressions such as df["status"][mask] = value.

Keep normalization mappings in code or configuration rather than in undocumented notebook cells. A reviewer should be able to see that “U.S.” and “USA” map to “United States,” and should also be able to inspect values that did not match the approved mapping.

4. Parse Numbers, Dates, and Booleans Explicitly

A column stored as text is not automatically dirty, but it limits valid operations. Prices cannot be summed safely, dates cannot be ordered chronologically, and boolean flags may contain several representations. Conversion should be explicit and auditable: keep the original value long enough to identify failures, then create a typed column and a failure report.

# Preserve raw text for audit
orders["unit_price_raw"] = orders["unit_price"]
orders["order_date_raw"] = orders["order_date"]

price_text = (
    orders["unit_price"]
    .astype("string")
    .str.strip()
    .str.replace(r"^\$", "", regex=True)
    .str.replace(",", "", regex=False)
)
orders["unit_price"] = pd.to_numeric(price_text, errors="coerce")

orders["order_date"] = pd.to_datetime(
    orders["order_date"],
    format="mixed",
    errors="coerce",
    utc=True,
)

price_failures = orders.loc[
    orders["unit_price_raw"].notna() & orders["unit_price"].isna(),
    ["order_id", "unit_price_raw"],
]

date_failures = orders.loc[
    orders["order_date_raw"].notna() & orders["order_date"].isna(),
    ["order_id", "order_date_raw"],
]

The currency example assumes a US-style source. It would be wrong for a European file where a comma is the decimal separator. In a real pipeline, parse each source using its documented locale or currency rules before concatenating the data. A cleaning function that works for one export should not be generalized to every export without tests.

The pandas to_datetime reference supports explicit formats, mixed-format parsing, error handling, units for numeric timestamps, and UTC conversion. Mixed parsing is convenient but risky for ambiguous strings such as 03/04/2026. Prefer a known format per source whenever possible, and treat coercion as a way to surface failures, not as proof that the remaining dates are correct.

After conversion, inspect the failure rows and the resulting range. A date can parse successfully and still be wrong. A birth date in the future, an order from 1900, or a timestamp shifted by a time-zone assumption is valid syntax but invalid meaning. Type conversion and business validation are separate steps.

5. Handle Missing Values as a Decision, Not a Reflex

Missing values are among the most consequential cleaning decisions because the absence may carry information. A blank can mean the value was not collected, the field does not apply, the customer refused to answer, the source system failed, or the event has not happened yet. Filling all blanks with zero or the column mean erases those distinctions.

The pandas missing-data guide explains how pandas represents and propagates missing values across nullable types. The technical methods are straightforward; the difficult part is choosing an action that preserves the meaning of the analysis.

Normalize missing tokens by column

Do not apply one universal list to every field. The string “unknown” may be an approved category in one column and a data-quality failure in another. Define token mappings for the columns whose semantics you understand.

missing_tokens = {
    "customer_email": ["", "n/a", "none", "null"],
    "country": ["", "not provided"],
    "coupon_code": ["", "no coupon"],
}

for col, tokens in missing_tokens.items():
    normalized = orders[col].astype("string").str.strip().str.casefold()
    orders.loc[normalized.isin(tokens), col] = pd.NA

missing_report = (
    orders.isna().mean()
    .mul(100)
    .round(2)
    .sort_values(ascending=False)
    .rename("missing_pct")
)
print(missing_report)

Measure missingness by time and group

A missingness indicator can reveal a source-system change, geography-specific collection issue, or selection bias. Compare the rate across months, channels, statuses, and other important segments before choosing an imputation or deletion policy. If missingness predicts the outcome, that may be useful for prediction but also a warning that the model is learning a process artifact.

Imputation belongs inside the modeling pipeline

For predictive work, scikit-learn documents simple and multivariate imputation methods, including missingness indicators. More importantly, its common pitfalls guide warns that preprocessing must be learned from training data only. A median calculated from the full dataset leaks information from the test set even though the step looks like ordinary cleaning.

For reporting or descriptive analysis, imputation may be inappropriate altogether. Showing the observed values and the missing count can be more honest than creating synthetic values. The right decision depends on the purpose, not on a rule that every DataFrame must contain no nulls.

6. Detect and Resolve Duplicates at the Correct Grain

A duplicate is defined relative to the intended entity or event. Two identical rows may be accidental exports, but they may also be legitimate repeated measurements. Two rows with the same order ID may be record versions, split shipments, or a broken key. Before deleting anything, define what should be unique and why.

exact_duplicate_mask = orders.duplicated(keep=False)
exact_duplicates = orders.loc[exact_duplicate_mask].sort_values("order_id")

key_columns = ["order_id", "line_number"]
key_duplicate_mask = orders.duplicated(key_columns, keep=False)
key_duplicates = orders.loc[key_duplicate_mask].sort_values(
    key_columns + ["updated_at"]
)

print("exact duplicate rows:", exact_duplicate_mask.sum())
print("rows in duplicate key groups:", key_duplicate_mask.sum())

Use a deterministic retention rule

If the source contains record versions, sort by an authoritative update timestamp and source priority, then retain the version that the contract specifies. Keep the removed versions in an audit table. “Keep last” is only defensible when “last” has a defined meaning and the timestamp itself is trustworthy.

source_priority = {"billing_system": 2, "legacy_export": 1}
orders["source_priority"] = orders["source_system"].map(source_priority)

orders = orders.sort_values(
    ["order_id", "line_number", "updated_at", "source_priority"]
)

versions = orders.loc[
    orders.duplicated(["order_id", "line_number"], keep=False)
].copy()

orders = orders.drop_duplicates(
    ["order_id", "line_number"],
    keep="last",
)

if orders.duplicated(["order_id", "line_number"]).any():
    raise ValueError("Order-line key is still duplicated")

When the correct row cannot be determined, do not guess. Quarantine the duplicate group and report the unresolved count. A smaller clean dataset with transparent exceptions is often more trustworthy than a complete-looking dataset built from arbitrary choices.

7. Standardize Categories and Apply Business Rules

Category cleaning should be based on an approved vocabulary or reference table. Whitespace and case variations are easy. Synonyms, abbreviations, and spelling errors require a mapping. Fuzzy matching can suggest candidates, but it should not silently decide that two customer names, products, or locations are the same.

country_map = {
    "us": "United States",
    "u.s.": "United States",
    "usa": "United States",
    "united states": "United States",
    "uk": "United Kingdom",
    "u.k.": "United Kingdom",
    "united kingdom": "United Kingdom",
}

country_key = orders["country"].astype("string").str.strip().str.casefold()
orders["country_clean"] = country_key.map(country_map)

unmapped_countries = (
    orders.loc[country_key.notna() & orders["country_clean"].isna(), "country"]
    .value_counts(dropna=False)
)
print(unmapped_countries)

The unmapped values are part of the output, not an inconvenience to hide. They tell you whether the vocabulary is incomplete, a new market appeared, or the field contains values that do not belong there. Update the mapping through review and version control rather than by editing values manually in the final file.

Build issue flags before deleting rows

Business rules translate the data contract into executable checks. A completed order may require a customer, product, positive quantity, nonnegative price, allowed status, and timestamp no later than the extraction cutoff. Some failures should block the row from analysis; others should remain as warnings.

ALLOWED_STATUS = {"pending", "completed", "cancelled", "refunded"}

orders["issue_missing_order_id"] = orders["order_id"].isna()
orders["issue_missing_product_id"] = orders["product_id"].isna()
orders["issue_invalid_status"] = ~orders["status"].isin(ALLOWED_STATUS)
orders["issue_invalid_quantity"] = orders["quantity"].isna() | (orders["quantity"] <= 0)
orders["issue_negative_price"] = orders["unit_price"].notna() & (orders["unit_price"] < 0)
orders["issue_invalid_discount"] = (
    orders["discount_pct"].notna()
    & ~orders["discount_pct"].between(0, 1, inclusive="both")
)

issue_columns = [c for c in orders.columns if c.startswith("issue_")]
orders["issue_count"] = orders[issue_columns].sum(axis=1)

exceptions = orders.loc[orders["issue_count"] > 0].copy()
clean_candidates = orders.loc[orders["issue_count"] == 0].copy()

Issue columns make the process explainable. A row can fail more than one rule, and the exception file can show exactly why. They also make quality metrics easy to monitor over time: the percentage of rows with invalid quantities, unknown statuses, or missing keys can be compared across deliveries.

Rules should reflect combinations, not only individual columns. A blank shipment date may be valid for a pending order but not for a completed physical shipment. A refund amount may be negative by design. Contextual rules are often more useful than generic “no nulls” checks.

8. Treat Outliers as Questions, Not Automatic Deletions

Outliers are observations far from the rest of the data, but distance does not establish error. An extreme order total may be a corporate purchase, a unit mistake, a duplicated quantity, fraud, or a valid rare event. The cleaning task is to classify the case, not to make the distribution look normal.

Statistical rules such as the interquartile range are useful screens. They are not universal deletion rules. A threshold derived from the current dataset can also change when the dataset changes, so keep the flag separate from the business decision.

q1 = clean_candidates["line_total"].quantile(0.25)
q3 = clean_candidates["line_total"].quantile(0.75)
iqr = q3 - q1

lower = q1 - 1.5 iqr
upper = q3 + 1.5 iqr

clean_candidates["is_iqr_outlier"] = ~clean_candidates["line_total"].between(
    lower, upper, inclusive="both"
)

outlier_review = clean_candidates.loc[
    clean_candidates["is_iqr_outlier"],
    ["order_id", "product_id", "quantity", "unit_price", "line_total"],
]

Review extreme values with domain information. Compare quantity and unit price separately, inspect the customer and product, and check whether the event is repeated. If the value is retained, use medians, percentiles, log scales, robust losses, or segmented reporting when those methods better match the question. If it is excluded, document the exact rule and show how conclusions change.

9. Merge Datasets Without Creating New Errors

Many cleaning pipelines integrate orders with customers, products, campaigns, or lookup tables. A merge can silently duplicate rows, drop entities, match missing keys, or create contradictory columns. Treat every join as a quality checkpoint.

Check the keys first

• Make sure key columns use compatible data types and consistent formatting.

• Measure missing keys on both sides before the merge.

• Check whether the lookup side is unique at the expected key.

• State the expected relationship: one-to-one, many-to-one, one-to-many, or many-to-many.

• Record the expected output grain and acceptable unmatched rate.

if not customers["customer_id"].is_unique:
    duplicate_customers = customers.loc[
        customers.duplicated("customer_id", keep=False)
    ]
    raise ValueError(
        f"Customer lookup is not unique: {len(duplicate_customers)} rows"
    )

merged = clean_candidates.merge(
    customers[["customer_id", "customer_segment", "country"]],
    how="left",
    on="customer_id",
    validate="many_to_one",
    indicator=True,
)

unmatched = merged.loc[merged["_merge"] != "both"].copy()
match_rate = (merged["_merge"] == "both").mean()
print(f"customer match rate: {match_rate:.2%}")

The pandas merge reference supports validation of expected key relationships and an indicator column showing whether a row came from the left table, right table, or both. It also warns that null keys can match one another in pandas, unlike usual SQL join behavior, so missing keys should be checked before merging.

Reconcile after the merge

Compare the output row count with the input row count, count distinct business keys, and reconcile important totals. If a many-to-one customer lookup is expected, order-line count and total line value should not change. A change indicates duplicated lookup keys, unexpected filtering, or a different grain than the contract promised.

before_rows = len(clean_candidates)
before_total = clean_candidates["line_total"].sum(min_count=1)

after_rows = len(merged)
after_total = merged["line_total"].sum(min_count=1)

if after_rows != before_rows:
    raise ValueError("Merge changed the order-line row count")

if not pd.isna(before_total) and not pd.isna(after_total):
    if abs(after_total - before_total) > 0.01:
        raise ValueError("Merge changed the line-total reconciliation")

10. Separate Data Cleaning From Model Preprocessing

Some transformations are objective cleaning rules: parsing a currency column, fixing an authoritative category mapping, removing a confirmed duplicate export, or rejecting an impossible negative quantity. Other transformations are model-dependent: filling age with the training median, scaling a feature, one-hot encoding categories, selecting features, or learning an outlier threshold. The second group must be fitted inside the model-validation process.

This separation prevents leakage. If the entire dataset is used to calculate an imputation value, category frequency, scaling mean, or feature-selection threshold before the split, information from the test set influences the training process. The model may appear more accurate than it will be on future data.

scikit-learn’s ColumnTransformer documentation shows how different transformations can be applied to numeric and categorical columns within one pipeline. Pipelines fit each preprocessing step on training folds and apply the learned transformation to validation or test data consistently.

from sklearn.compose import ColumnTransformer
from sklearn.impute import SimpleImputer
from sklearn.linear_model import LogisticRegression
from sklearn.pipeline import Pipeline
from sklearn.preprocessing import OneHotEncoder, StandardScaler

numeric_features = ["quantity", "unit_price", "discount_pct"]
categorical_features = ["country_clean", "acquisition_channel"]

numeric_pipeline = Pipeline([
    ("imputer", SimpleImputer(strategy="median", add_indicator=True)),
    ("scale", StandardScaler()),
])

categorical_pipeline = Pipeline([
    ("imputer", SimpleImputer(strategy="most_frequent")),
    ("encode", OneHotEncoder(handle_unknown="ignore")),
])

preprocess = ColumnTransformer([
    ("numeric", numeric_pipeline, numeric_features),
    ("categorical", categorical_pipeline, categorical_features),
])

model = Pipeline([
    ("preprocess", preprocess),
    ("classifier", LogisticRegression(max_iter=1000)),
])

# Fit model only on training data:
# model.fit(X_train, y_train)
# predictions = model.predict(X_test)

A time-based problem needs another safeguard: training records must occur before validation or test records. Random splitting can leak future customer behavior into the past. The cleaning contract should record which fields were available at prediction time and which were created later in the process.

11. Turn Notebook Cleaning Into Reusable, Testable Code

Exploration belongs in a notebook, but the final cleaning logic should be callable without manually rerunning cells in the right order. Small functions make assumptions visible, simplify testing, and let you reuse the same rules for the next file. pandas DataFrame.pipe can connect these functions into a readable chain.

def clean_column_names(df: pd.DataFrame) -> pd.DataFrame:
    result = df.rename(columns={c: normalize_column_name(c) for c in df.columns})
    if result.columns.duplicated().any():
        raise ValueError("Duplicate columns after normalization")
    return result


def parse_order_fields(df: pd.DataFrame) -> pd.DataFrame:
    result = df.copy()
    result["quantity"] = pd.to_numeric(result["quantity"], errors="coerce")
    result["discount_pct"] = pd.to_numeric(
        result["discount_pct"].astype("string").str.rstrip("%"),
        errors="coerce",
    )
    percent_mask = result["discount_pct"] > 1
    result.loc[percent_mask, "discount_pct"] /= 100
    result["order_date"] = pd.to_datetime(
        result["order_date"], format="mixed", errors="coerce", utc=True
    )
    return result


def add_line_total(df: pd.DataFrame) -> pd.DataFrame:
    return df.assign(
        line_total=lambda x: (
            x["quantity"]
            x["unit_price"]
            (1 - x["discount_pct"].fillna(0))
        )
    )

orders_clean = (
    orders_raw
    .pipe(clean_column_names)
    .pipe(parse_order_fields)
    .pipe(add_line_total)
)

Keep configuration outside the function when it may change: approved country mappings, allowed statuses, source priorities, valid ranges, and extraction cutoffs. A configuration file or clearly named dictionary is easier to review than values scattered through notebook cells.

Validate the output with explicit checks

def validate_clean_orders(df: pd.DataFrame) -> None:
    required = {
        "order_id", "line_number", "order_date", "product_id",
        "quantity", "unit_price", "status", "line_total",
    }
    missing_columns = required - set(df.columns)
    if missing_columns:
        raise ValueError(f"Missing required columns: {sorted(missing_columns)}")

    if df[["order_id", "line_number"]].duplicated().any():
        raise ValueError("order_id + line_number is not unique")

    if df["order_id"].isna().any() or df["product_id"].isna().any():
        raise ValueError("Required identifiers contain missing values")

    if not df["quantity"].gt(0).all():
        raise ValueError("Quantity must be positive")

    if not df["unit_price"].ge(0).all():
        raise ValueError("Unit price must be nonnegative")

    if not df["discount_pct"].dropna().between(0, 1).all():
        raise ValueError("Discount must be between 0 and 1")

validate_clean_orders(orders_clean)

These checks are intentionally simple. For larger projects, schema-validation tools such as Pandera or Great Expectations can express reusable rules, produce structured failures, and integrate with pipelines. The tool is secondary to the discipline: critical assumptions should be executable, not left only in prose.

Publish both clean data and quality evidence

A Portfolio Project: Clean a Messy Retail Dataset

A strong beginner cleaning project can be more persuasive than another polished model on a perfectly prepared dataset. It shows that you can discover problems, make defensible decisions, write reproducible transformations, and communicate uncertainty. Use a public dataset, a synthetic dataset, or a dataset you are authorized to share. Never publish confidential records or personal identifiers.

Project brief

Imagine that a retailer provides monthly order exports from two systems plus a customer lookup. The files contain mixed headers, leading-zero IDs, inconsistent status and country values, duplicate record versions, currency strings, percentage formats, missing customer IDs, future dates, negative prices, extreme quantities, and customer keys that do not match the lookup. Your task is to produce a reporting-ready order-line table and a quality memo.

Suggested workflow

1.     Load each source separately with identifiers preserved as strings and bad-line handling set to fail loudly.

2.     Create a source-level profile before concatenating files, so differences between systems remain visible.

3.     Standardize headers and controlled categories, then retain unmapped values for review.

4.     Parse currency, percentages, booleans, and dates using source-specific rules; save parse failures.

5.     Resolve exact and key duplicates with a documented version rule and preserve discarded versions.

6.     Apply business-rule flags and separate valid candidates from exceptions without erasing the issue columns.

7.     Merge the customer lookup with many-to-one validation, report unmatched keys, and reconcile totals.

8.     Export the clean table, exception table, before-and-after profile, and a memo describing unresolved risks.

For additional starter datasets and project formats, the beginner Python projects for data science article offers broader ideas that can be adapted into a deliberately messy cleaning exercise.

When you publish the work, use the structure recommended in Refonte Learning’s guide to building a data science portfolio that gets you hired: explain the problem, show the code, document the decisions, and focus on complete evidence rather than a large number of shallow projects.

A four-week practice plan

This month-long project fits naturally inside the data science roadmap from beginner to job-ready, where Python, statistics, SQL, projects, and communication develop together rather than as isolated subjects.

Common Data-Cleaning Mistakes to Avoid

Overwriting the raw file

This destroys provenance and makes the work difficult to reproduce. Keep immutable raw inputs and write versioned outputs.

Calling dropna() on the whole DataFrame

A single optional field can remove an otherwise useful row. Choose required subsets and inspect the lost population before dropping records.

Filling every numeric null with zero or the mean

Zero may be a real value, while the mean can distort distributions and relationships. Use field-specific reasoning and fit model imputers on training data only.

Using drop_duplicates() without defining the grain

Repeated rows can be versions or legitimate events. State the key, sort order, and retention rule, and keep an audit of removed records.

Coercing errors without reviewing failures

errors="coerce" is useful because it creates missing values for failed parsing, but the failures must be counted and inspected. Otherwise invalid text simply disappears into nulls.

Lowercasing every text field

Controlled categories benefit from normalization. Names, free text, identifiers, and case-sensitive codes may lose information. Preserve original values when meaning is uncertain.

Deleting statistical outliers automatically

A boxplot flag is a prompt for investigation, not proof of error. Keep valid rare events and test how influential cases affect the conclusion.

Merging without validate and indicator

A join can multiply rows or hide unmatched keys while still producing a plausible table. Declare the expected relationship and reconcile the output.

Cleaning before understanding units and time zones

A number can be dollars, cents, kilograms, percentages, or a code. A timestamp can be local, UTC, event time, or ingestion time. Converting without metadata can create precise-looking errors.

Using AI-generated cleaning code without testing it

Generative tools can propose useful code, but they do not know the real grain, business rules, source history, or privacy restrictions unless those facts are supplied. Review every mapping, threshold, and deletion rule against known cases.

How Refonte Learning Fits Into This Data-Cleaning Roadmap

As of July 2026, the official page for Refonte Learning's Data Science & AI program lists Python, Jupyter Notebook, pandas, NumPy, Matplotlib, scikit-learn, and TensorFlow in its accessible FAQ tool list. The page also names Python Data Science, exploratory data analysis and visualization, statistical modelling, machine learning, and application to industry projects among its visible competencies.

Those areas depend on trustworthy preparation, but the public page does not expose a detailed data-cleaning syllabus. Prospective learners should request the current syllabus and ask whether projects require them to profile raw data, handle missing values and duplicates, standardize categories, validate joins, document exceptions, and prevent preprocessing leakage. These questions help distinguish a demonstration with pre-cleaned files from genuine practice with imperfect data.

A structured program can be useful when it sequences concepts, supplies feedback, and requires complete project evidence. Self-study can also work when you preserve raw data, write explicit rules, test edge cases, and seek review. In either route, the outcome should be more than a tidy notebook: it should be a cleaning process another person can rerun, inspect, and challenge.

Frequently Asked Questions

What is data cleaning in Python?

It is the process of using Python tools, commonly pandas, to inspect and prepare data for a defined use. Typical work includes preserving raw inputs, profiling schema and quality, standardizing labels, converting types, handling missing values and duplicates, applying business rules, reviewing outliers, validating joins, and documenting the result.

Is data cleaning the same as data preprocessing?

The terms overlap, but a useful distinction is that cleaning corrects or documents source-quality issues, while model preprocessing transforms valid data for an algorithm. Imputation, scaling, encoding, and feature selection may belong inside a fitted machine-learning pipeline even after the analytical dataset is considered clean.

Should I drop rows that contain missing values?

Only after checking which fields are missing, why they are missing, how many rows would be lost, and whether the loss is concentrated in a subgroup. Dropping can be reasonable when a required field is absent and the affected population is small and non-systematic. It is not a universal solution.

What is the best way to fill missing values in pandas?

There is no single best method. A constant “Unknown” category may suit a reporting field, a median imputer may suit a skewed numerical model feature, and leaving the value missing may be most honest for descriptive analysis. The method should reflect the cause of missingness and must be learned from training data when used in predictive modeling.

How do I know whether duplicate rows should be removed?

Define what one row represents and which columns form the business key. Then inspect duplicate groups for timestamps, source systems, version fields, or legitimate repeated events. Remove only when the rows represent the same real-world event and the retention rule is defensible.

How should I clean dates with multiple formats?

Identify formats by source whenever possible and parse them separately with explicit rules. Mixed-format parsing can help surface failures, but ambiguous day and month values require metadata or domain confirmation. Convert time zones consistently and keep the original text until validation is complete.

Should I remove outliers before analysis?

Not automatically. First determine whether the value is impossible, incorrectly parsed, part of a different population, or a valid rare event. Use domain rules, plots, source verification, and sensitivity analysis. If you exclude it, record the rule and show how the decision affects results.

How can I clean a dataset that is too large for memory?

Push filtering and aggregation into SQL when the data lives in a database, read files in chunks, select only needed columns, use efficient dtypes and columnar formats such as Parquet, or move to tools designed for larger-than-memory processing. The same contract, profiling, validation, and reconciliation principles still apply.

Can generative AI clean a dataset for me?

It can help draft profiling code, mappings, regular expressions, tests, or explanations. It cannot independently verify what a value should mean, whether two records are the same entity, or whether a business rule is legitimate. Use generated code as a proposal, test it on known edge cases, and never provide sensitive data to an unapproved service.

What should a data-cleaning portfolio project show?

Show the raw-data problem, the contract, the baseline profile, transformation code, parse failures, duplicate logic, exception records, validation checks, before-and-after metrics, and a short memo explaining unresolved issues. Reviewers should be able to see not only that the data became cleaner, but why the result deserves trust.

How long does it take to learn practical data cleaning in Python?

A beginner can learn the main pandas operations in several weeks. Reliable judgment takes longer because it comes from repeated exposure to different sources, ambiguous definitions, and edge cases. One complete four-week project is a strong first cycle; continue deepening the same habits through every later analysis or model.

Conclusion

Data cleaning in Python is not a cosmetic step between downloading a file and building a chart. It is the work of preserving meaning. A dependable workflow keeps raw data immutable, defines the row grain and contract, profiles before changing, parses types explicitly, treats missingness and duplication as contextual decisions, flags invalid values, investigates outliers, validates merges, and records exceptions.

The strongest cleaning code is not the shortest. It is the code that another person can rerun and audit. Keep before-and-after quality reports, reconcile row counts and totals, write tests for critical rules, and separate analytical cleaning from transformations that must be fitted inside a model pipeline. Those habits make every later statistical result, dashboard, and machine-learning model more credible.

Practice the workflow on one deliberately messy dataset and publish the full evidence, not only the final clean table. Whether you study independently or through a structured route such as Refonte Learning, the goal is the same: turn imperfect records into a documented data product whose strengths, limitations, and decisions are clear.