Pandas Data Cleaning and String Operations

Use this guide to inspect and clean Pandas data, standardise text and validate fields before analysis. Choose a task below or follow the complete order-cleaning workflow. Preserve the source and review uncertain values before removing or replacing them.

Choose a cleaning task 🔝

Start with the problem in your data. Inspect the source, define valid values, make changes on a copy and compare the result. The tutorials below explain each operation; the workflow shows how to combine them. Text manipulation and text inspection belong here alongside missing values and duplicates. For statistics and charts, continue to Data Analysis.

Inspect completeness and data types 🔝

• info() — inspect columns, types and non-missing counts
• dtypes — inspect the dtype of each column
• DataFrame.count() — count non-missing values, rather than pattern occurrences
• columns — inspect column labels before selecting or renaming fields

Handle missing values 🔝

• fillna() — fill missing values using a justified rule
• dropna() — remove rows or columns when required values are absent
• dropna(thresh=...) — require a minimum number of non-missing fields
• notnull() — identify present values
• isnull() / isna() — find missing values and audit incomplete records

Missing values may be represented by None, NaN, pd.NA or NaT depending on dtype. Use isna()/notna() to detect them. Nullable Int64 supports integer values with missing entries. Empty strings and whitespace are not automatically missing.

Remove selected rows and columns 🔝

Choose a removal method according to the reason for exclusion. Keep a review copy before deleting uncertain records, and compare the remaining rows and columns with your intended result.

• drop() — remove row or column labels, handle missing labels, apply conditional deletion rules and distinguish labels from positions.
• Filter rows — retain records that meet explicit Boolean conditions.
• dropna() — remove records or fields according to missing-value requirements.
• drop_duplicates() — remove repeated records using a defined key and retention rule.

drop() removes labels; it does not itself identify missing values or duplicates. Use loc for label-based selection and iloc for positional selection.

Review duplicates before removing them 🔝

• DataFrame.duplicated() — flag repeated rows or business keys
• DataFrame.drop_duplicates() — keep records using an explicit subset and keep policy
• Series.duplicated() — flag repeated values in a Series
• Series.drop_duplicates() — remove repeated Series values

Two orders with the same product are not necessarily duplicate orders. Choose the business key and check conflicting values first.

Clean and standardise text 🔝

• str.replace() — replace literal text or regex matches
• Case conversion — standardise case for comparisons while preserving display text

Use str.strip() for surrounding whitespace. Keep original identifiers and names where changes could lose meaning. A blank value requires a separate missing-value policy.

Inspect strings and validate fields 🔝

• str.len() — measure lengths to detect blank or malformed fields
• str.contains() — find literal text or regex patterns
• str.count() — count pattern occurrences within each value
• str.contains().sum() — count rows matching a condition
• Email validation — inspect address formats

DataFrame.count() counts present values; str.count() counts regex matches inside strings. Neither confirms that a field meets your business rules. A syntactically plausible email does not establish deliverability.

Extract, combine and format string fields 🔝

• str.split() — separate combined fields and validate the pieces
• str.slice() — extract a known character range
• str.cat() — combine text with separators and a missing-value policy
• str.zfill() — pad identifiers to a required width

These are existing tutorials; use them according to the data specification. Keep codes as text when leading zeros matter. Padding cannot reconstruct information already lost during numeric import.

Convert types and organise columns 🔝

• astype() — convert values after validating their format
• select_dtypes() — select columns by dtype
• to_datetime() — parse date values and review failures
• apply() — build custom review reasons from multiple fields
• mask() — replace invalid values using explicit conditions
• where() — keep valid values and replace values outside your rules
• rename() — standardise selected column and index labels
• add_prefix() — add a prefix to labels
• add_suffix() — add a suffix to labels
• Categorical data — define allowed labels and their order, review unknown values and preserve reporting categories.

For inconsistent numeric text, to_numeric(errors="coerce") exposes invalid values as missing; review these before filling or deleting. Label changes do not clean the values stored in columns.

Workflow: inspect a synthetic order extract 🔝

This sample has text variants, blank products, an invalid amount and a repeated order. The rules are: a non-blank product, a non-negative integer amount and one record per order ID. Keep the raw data for review.

import pandas as pd
raw = pd.DataFrame({
    "order_id": [101, 102, 102, 103, 104, 105],
    "product": [" Notebook ", "PEN", "PEN", "  ", None, "Café mug"],
    "amount": ["1,200", "50", "50", "bad", "70", "90"]
})
df = raw.copy()
print(df)
print("Present values:")
print(df.count())
print("Types:")
print(df.dtypes)

Expected output

   order_id     product amount
0       101   Notebook   1,200
1       102         PEN     50
2       102         PEN     50
3       103                bad
4       104         NaN     70
5       105    Café mug     90
Present values:
order_id    6
product     5
amount      6
dtype: int64
Types:
order_id    int64
product       str
amount        str
dtype: object

Normalise text without losing the source 🔝

Trim surrounding spaces, treat blanks as missing and create a separate case-normalised comparison key. Keep the readable product spelling.

product = df["product"].astype("string").str.strip()
df["product"] = product.mask(product.eq(""), pd.NA)
df["product_key"] = df["product"].str.casefold()
print(df[["product", "product_key"]])
assert df["product"].isna().sum() == 2
assert df.loc[5, "product"] == "Café mug"

Expected output

    product product_key
0  Notebook    notebook
1       PEN         pen
2       PEN         pen
3      <NA>        <NA>
4      <NA>        <NA>
5  Café mug    café mug

Remove grouping commas and validate integer amounts 🔝

The original comma-removal idea is suitable only for a known format. Here commas are thousands separators and only whole, non-negative amounts are accepted; decimal-comma formats need another rule. Vectorised replacement handles missing text without a Python lambda.

amount_text = df["amount"].astype("string").str.replace(",", "", regex=False)
numbers = pd.to_numeric(amount_text, errors="coerce")
amount_ok = (numbers.notna() & numbers.ge(0) & numbers.mod(1).eq(0)).fillna(False)
df["amount"] = numbers.where(amount_ok).astype("Int64")
print(df[["order_id", "amount"]])
assert df["amount"].isna().sum() == 1
assert df.loc[0, "amount"] == 1200

Expected output

   order_id  amount
0       101    1200
1       102      50
2       102      50
3       103    <NA>
4       104      70
5       105      90

Review missing fields before selecting valid rows 🔝

Reason counts can overlap. Do not sum them to calculate rejected rows. This example keeps rejected records separately instead of inventing amounts or product names.

bad_product = df["product"].isna()
bad_amount = df["amount"].isna()
valid = ~(bad_product | bad_amount)
review = df.loc[~valid].copy()
accepted = df.loc[valid].copy()
print("Missing product:", int(bad_product.sum()))
print("Invalid or missing amount:", int(bad_amount.sum()))
print("Rejected rows:", len(review))
print(review)
assert len(review) == 2

Expected output

Missing product: 2
Invalid or missing amount: 1
Rejected rows: 2
   order_id product  amount product_key
3       103    <NA>    <NA>        <NA>
4       104    <NA>      70        <NA>

Check duplicate keys and retain one consistent record 🔝

For each repeated key, inspect whether other values conflict. This sample has identical repeated orders; keeping the first is justified by that check. Conflicting records should be resolved using a business rule.

conflicts = accepted.groupby("order_id")[["product_key", "amount"]].nunique(dropna=False).gt(1).any(axis=1)
assert not conflicts.any(), "Resolve conflicting orders before deduplicating"
print("Repeated keys:")
print(accepted.loc[accepted.duplicated("order_id", keep=False)])
clean = accepted.drop_duplicates(subset=["order_id"], keep="first").copy()
print("Clean orders:")
print(clean)
assert clean["order_id"].tolist() == [101, 102, 105]

Expected output

Repeated keys:
   order_id product  amount product_key
1       102     PEN      50         pen
2       102     PEN      50         pen
Clean orders:
   order_id   product  amount product_key
0       101  Notebook    1200    notebook
1       102       PEN      50         pen
5       105  Café mug      90    café mug

Validate and export the result 🔝

Check the agreed rules after transformation. Compare counts with the raw extract and retain rejected rows for audit. See Excel export and CSV input.

assert clean["order_id"].is_unique
assert clean[["product", "amount"]].notna().all().all()
assert clean["amount"].ge(0).all()
print("Raw rows:", len(raw), "Review rows:", len(review), "Clean rows:", len(clean))
print(clean.to_csv(index=False).rstrip())
# Optional file export in Colab:
# clean.to_csv("clean_orders.csv", index=False)

Expected output

Raw rows: 6 Review rows: 2 Clean rows: 3
order_id,product,amount,product_key

101,Notebook,1200,notebook

102,PEN,50,pen

105,Café mug,90,café mug

Preserve valid Unicode text 🔝

The pattern [^\x00-\x7F] selects non-ASCII characters, not invalid UTF-8. Removing it globally can damage names and international text. If an external system explicitly requires ASCII, isolate that conversion, keep the source and inspect the loss.

labels = pd.Series(["Café", "Notebook", "東京"], dtype="string")
ascii_only = labels.str.replace(r"[^\x00-\x7F]+", "", regex=True)
print(pd.DataFrame({"original": labels, "ASCII-only demonstration": ascii_only}))
assert labels.iloc[0] == "Café"

Expected output

   original ASCII-only demonstration
0      Café                      Caf
1  Notebook                 Notebook
2        東京

Practice: change the cleaning rules 🔝

Add an order with a negative amount and another repeated order ID with a different amount. Predict which rows need review. Change the workflow to report conflicting IDs instead of stopping at the assertion; resolve them before exporting. Do not silently keep the first conflicting record.

Practice in Google Colab 🔝

Open in Google Colab View on GitHub
Run the examples in order, change the sample orders and inspect the cleaning decisions. Save your own copy to keep edits. All sample tables are included in the notebook.

Continue to Pandas Data Analysis or the Pandas tutorial index.

References: Pandas missing data and working with text.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: