Working with Text Data in pandas

For text columns, choose a dtype deliberately rather than letting an import happen to produce object. An object column can contain strings, numbers, lists, and missing values in the same array. That flexibility is useful when the data is genuinely heterogeneous, but it also makes assumptions about every element your problem.

import pandas as pd

labels = pd.Series(["open", "closed", None], dtype="string")
print(labels.dtype)

Pandas’ nullable string dtype is designed for text and represents missing values with pd.NA. That gives the column consistent text semantics while preserving missingness. For a column this is supposed to contain text, make that contract explicit when constructing it or reading it:

df = pd.read_csv(
    "events.csv",
    dtype={"status": "string"},
)

df["status"] = df["status"].astype("string")

For larger datasets, you can choose the storage backend explicitly. string[python] stores Python string objects; string[pyarrow] uses Arrow-backed storage and may reduce memory use or accelerate supported operations. The benefit depends on the data, pandas and PyArrow versions, and the operations your code performs.

text_python = df["status"].astype("string[python]")
text_arrow = df["status"].astype("string[pyarrow]")

Arrow-backed strings require PyArrow, and not every operation has identical support or performance across backends. If a project depends on predictable behavior, pin the relevant package versions and specify the storage backend rather than relying on defaults. Check both memory use and the actual transformations in your workload:

for name, values in {
    "python": text_python,
    "arrow": text_arrow,
}.items():
    print(name, values.memory_usage(deep=True))
    print(values.str.contains("open", na=False).sum())

Selecting and transforming text with string methods

The .str accessor applies string operations element by element without a Python loop. It does not change the original Series; assign the result when the transformation is part of the data pipeline. A common first step is to normalize whitespace and case before comparing values:

status = df["status"].str.strip().str.casefold()
is_open = status.eq("open")

casefold is intended for caseless matching and handles more Unicode case distinctions than lower. Neither operation should be applied indiscriminately: case can be meaningful in identifiers, and trimming can alter data if spaces are significant.

Use contains for substring tests. Its default interpretation is a regular expression, so pass regex=False when the search string is literal; otherwise characters such as a period or plus sign have pattern meaning. Specify missing-value behavior when the result will be used as a Boolean mask:

notes = df["notes"].astype("string")

mentions_error = notes.str.contains(
    "error",
    case=False,
    regex=False,
    na=False,
)
errors = df.loc[mentions_error]

For a regular-expression search, make the pattern explicit and use extract when the matched text is data you need, not merely a condition. Capture groups become output columns, which makes parsing easier to inspect and validate:

parts = df["reference"].str.extract(
    r"^(?P[A-Z]{2})-(?Pd+)$"
)

valid = parts["prefix"].notna()
df.loc[valid, "reference_prefix"] = parts.loc[valid, "prefix"]
df.loc[valid, "reference_number"] = parts.loc[valid, "number"]

Splitting is useful when a field contains a known delimiter. With expand=True, the result is a DataFrame; limit the number of splits when the remainder belongs in one field. Rows with fewer pieces receive missing values, so downstream code must not assume every component exists:

names = df["contact_name"].str.split(",", n=1, expand=True)
df["last_name"] = names[0].str.strip()
df["first_name"] = names[1].str.strip()

Use replace for substitutions, distinguishing literal replacement from regular-expression replacement just as with contains. For example, removing a literal prefix is different from replacing every character matched by a pattern:

codes = df["product_code"].str.replace(
    "SKU-",
    "",
    regex=False,
)

digits_only = df["phone"].str.replace(
    r"D+",
    "",
    regex=True,
)

String methods can also perform indexing and slicing, but slicing is positional, not semantic. If a value has a stable format, a slice may be appropriate; if its format can vary, parse it with a delimiter or a validated pattern instead. For example, str.slice can select a fixed-width prefix while preserving missing values:

prefix = df["account_id"].str.slice(0, 3)

When a transformation has several stages, keep intermediate results if they clarify the assumptions. This makes it possible to check which values failed parsing instead of silently turning malformed input into plausible output:

raw = df["measurement"].astype("string").str.strip()
match = raw.str.extract(
    r"^(?Pd+(?:.d+)?)s*(?P[A-Za-z]+)$"
)

df["measurement_value"] = pd.to_numeric(
    match["value"],
    errors="coerce",
)
df["measurement_unit"] = match["unit"].str.casefold()
unparsed = raw.notna() & match["value"].isna()

Handling missing values and inconsistent text

Missing text is not the same as empty text. A nullable string Series preserves missing values through operations such as strip and casefold; an empty string remains an empty string. Avoid converting a column with astype(str), which can turn missing values into ordinary text such as "nan" or "". Use the nullable string dtype when text semantics are intended:

raw = df["status"].astype("string")
clean = raw.str.strip().str.casefold()

Comparisons on nullable strings can themselves produce missing Boolean values. A mask that will select rows should state how those values are handled, rather than leaving the policy implicit:

is_open = clean.eq("open")
open_rows = df.loc[is_open.fillna(False)]

Normalize known variations before testing or grouping. Trimming and case normalization handle superficial differences; an explicit alias table handles differences in vocabulary. Keep the raw column if the original spelling may be needed for auditing:

raw = df["status"].astype("string")
normalized = raw.str.strip().str.casefold()

aliases = {
    "in progress": "open",
    "active": "open",
    "complete": "closed",
    "done": "closed",
}
canonical = normalized.replace(aliases)

Decide which tokens represent missing data as part of the import or cleaning policy. A blank field, "n/a", and "unknown" may all mean different things in a particular dataset. Convert only the tokens that the data contract identifies as missing:

canonical = canonical.replace({
    "": pd.NA,
    "n/a": pd.NA,
    "not provided": pd.NA,
})

When reading a CSV file, pandas recognizes some missing-value spellings by default. If a token such as "NA" is a valid label in the source, configure parsing so that it isn’t silently converted to missing data. Conversely, use na_values to declare source-specific missing tokens deliberately:

df = pd.read_csv(
    "events.csv",
    dtype={"status": "string"},
    keep_default_na=False,
    na_values={"status": ["", "not provided"]},
)

Filling missing values is a semantic operation, not merely a way to make a string method convenient. Replacing missing values with an empty string can be appropriate for presentation or for a specific comparison, but it erases the distinction between absent data and a value this is present but empty:

display_status = df["status"].fillna("")

Validate normalized values against the domain expected by the application. This catches misspellings and newly introduced source values without confusing them with missing data:

allowed = {"open", "closed"}
unexpected = canonical.notna() & ~canonical.isin(allowed)

bad_rows = df.loc[unexpected, ["status"]]
df["status_clean"] = canonical.where(~unexpected)

Combining text operations into reliable workflows

A reliable text workflow separates normalization, canonicalization, validation, and assignment. Keep the source column unchanged until the derived values pass validation; otherwise a mistaken rule can destroy the evidence needed to diagnose it. Small functions make each policy explicit and simpler to test:

def normalize_status(values):
    text = values.astype("string")
    return text.str.strip().str.casefold().replace({
        "": pd.NA,
        "n/a": pd.NA,
        "not provided": pd.NA,
    })


def canonicalize_status(values):
    return values.replace({
        "in progress": "open",
        "active": "open",
        "complete": "closed",
        "done": "closed",
    })

Apply the stages to a temporary Series, then inspect values outside the accepted domain. The Boolean mask deliberately excludes missing values, so absent statuses are handled separately from unexpected spellings:

raw = df["status"].astype("string")
normalized = normalize_status(raw)
canonical = canonicalize_status(normalized)

allowed = {"open", "closed"}
unexpected = canonical.notna() & ~canonical.isin(allowed)

if unexpected.any():
    print(df.loc[unexpected, ["status"]].value_counts(dropna=False))

Choose an explicit response to unexpected values. A batch import may reject the data, while an interactive cleaning task may retain the original values in a review table. If processing continues, avoid silently treating an unrecognized label as missing:

if unexpected.any():
    raise ValueError(
        f"Unexpected status values: "
        f"{canonical.loc[unexpected].unique().tolist()}"
    )

df["status_clean"] = canonical

When several columns depend on the same text field, derive them from the same normalized intermediate value. This prevents slightly different cleanup rules from producing inconsistent results across filters, reports, and parsed fields:

raw = df["measurement"].astype("string")
normalized = raw.str.strip()

parts = normalized.str.extract(
    r"^(?Pd+(?:.d+)?)s*(?P[A-Za-z]+)$"
)

value = pd.to_numeric(parts["value"], errors="coerce")
unit = parts["unit"].str.casefold()

invalid = normalized.notna() & parts["value"].isna()

Check that parsing failures are acceptable before assigning the results. The mask distinguishes malformed nonmissing input from genuinely missing input, and the extracted Series retain the original index for aligned assignment:

if invalid.any():
    print(df.loc[invalid, ["measurement"]])

df["measurement_value"] = value
df["measurement_unit"] = unit

For reusable pipelines, pipe can name transformations without hiding their order. Each function should accept and return a Series with its index intact; that preserves alignment when the result is assigned back to the DataFrame:

def clean_status(values):
    return (
        values.astype("string")
        .str.strip()
        .str.casefold()
        .replace({
            "": pd.NA,
            "n/a": pd.NA,
            "in progress": "open",
            "active": "open",
            "complete": "closed",
            "done": "closed",
        })
    )


df["status_clean"] = df["status"].pipe(clean_status)