How to convert mixed text to numeric values safely in Pandas (with Code)

Editorial · Published 8 November 2026

The Short Answer (TL;DR)

Use to_numeric with errors="coerce" only alongside an audit that preserves the source text and distinguishes missing input from failed conversion. Define the accepted numeric syntax, reject nonfinite or out-of-range results explicitly, and keep rejected values visible instead of silently replacing them with zero.

import pandas as pd
raw = pd.Series(["12.5", "bad", None],dtype="string"); number = pd.to_numeric(raw,errors="coerce")
print(pd.DataFrame({"raw":raw,"number":number,"failed":raw.notna() & number.isna()}))

Detailed Implementation

This example uses synthetic data and does not need a network connection. Install the recorded package versions in a separate Python environment before running it.

python -m pip install numpy==2.5.4 pandas==3.0.6

from pathlib import Path
import numpy as np
import pandas as pd

def audit_numeric_text(raw):
    if not isinstance(raw,pd.Series):
        raise TypeError("Supply a Series of text or missing scalars.")
    if not all(isinstance(x,str) for x in raw.dropna()):
        raise TypeError("Nonmissing inputs must be text; do not stringify objects implicitly.")
    text = raw.astype("string").str.strip()
    source_missing = raw.isna()
    blank = text.eq("").fillna(False)
    pattern = r"[+-]?(?:[0-9]+(?:\.[0-9]*)?|\.[0-9]+)(?:[eE][+-]?[0-9]+)?"
    syntax_ok = text.str.fullmatch(pattern,na=False)
    parsed = pd.to_numeric(text.where(syntax_ok),errors="coerce",dtype_backend="numpy_nullable")
    values = parsed.to_numpy(dtype=float,na_value=np.nan)
    # This synthetic measurement contract allows finite values from -1e6 to 1e6.
    in_range = pd.Series(np.isfinite(values) & (np.abs(values)<=1e6),index=raw.index)
    invalid_token = ~(source_missing | blank | syntax_ok)
    out_of_range = syntax_ok & ~in_range
    number = parsed.where(syntax_ok & in_range).astype("Float64")
    status = pd.Series("accepted",index=raw.index,dtype="string")
    status.loc[source_missing] = "source_missing"
    status.loc[blank] = "blank"
    status.loc[invalid_token] = "invalid_token"
    status.loc[out_of_range] = "out_of_range"
    return pd.DataFrame({"raw":raw.copy(),"normalized":text,"number":number,"status":status})

# Synthetic measurement strings, not currency amounts or identifiers.
raw = pd.Series([" 12.5 ","-3","1e2",None,"","bad","1,200","inf",
                 "1e999","1000001","+.5","0"],
                index=[8,8,2,4,4,9,1,3,3,5,6,6],dtype="string")
result = audit_numeric_text(raw)
report = result.to_string(index=True,na_rep="NA")+"\n"
Path("numeric-text-audit.txt").write_text(report,encoding="utf-8")
print(report)

Five rows are accepted, three have invalid tokens, two are out of range, one was absent and one is blank. The five accepted numbers sum to 110; all twelve raw values and duplicate index labels remain in the audit.

What is the input contract?

The full example accepts strings or missing scalar values in a Series. It trims surrounding whitespace in a separate normalized column and retains the untouched source text. The grammar permits ASCII decimal numbers with an optional sign and exponent, including +.5 and 1e2. Commas, currency symbols, unit suffixes and infinity words are rejected. These are choices for the synthetic measurement field, not a universal definition of numeric data. Locale-specific numbers need an explicit source-specific parser.

Why is errors="coerce" insufficient on its own?

Coercion can make a failed conversion look like an originally absent value. The audit therefore separates source_missing, blank, invalid_token, out_of_range and accepted. The twelve-row fixture has one absent value, one blank, three invalid tokens and two syntactically numeric values outside the permitted range. Only five rows contain accepted numbers. This separation supports a decision about fixing or excluding records without pretending that a parse failure is a measured zero.

How are infinity and overflow handled?

The syntax check rejects the word inf. A token such as 1e999 passes the decimal grammar but cannot produce an accepted finite floating-point value, so it is flagged out_of_range. The finite value 1000001 is also rejected because this example permits magnitudes only up to one million. A successful library conversion is not sufficient for a valid domain value. Change that bound only when the measurement definition supports a different range.

Which values and labels remain unchanged?

The result has the same twelve rows and the same index labels, including duplicates. Raw text is copied into the report, normalized text is separate, and rejected numbers remain missing. Accepted values are 12.5, -3, 100, 0.5 and 0; their synthetic sum is 110. Each row receives exactly one status. Keeping the raw field allows a later correction to be checked against its source, while the status makes automated exclusions visible.

When should another numeric representation be used?

The output uses nullable Float64 for bounded measurements. Binary floating point does not exactly represent every decimal fraction, and coercing identifiers into numeric values can lose leading zeros or precision. Use text for identifiers and a separately designed decimal or integer-minor-unit contract for exact money. The example does not strip commas or symbols by replacement: indiscriminate cleanup could reinterpret a decimal separator or turn a malformed value into a different valid number.

How should conversion quality be reported?

Report the counts for each status and inspect rejected tokens before analysis. Among the ten nonmissing, nonblank inputs here, five are rejected, giving a 50 percent rejection rate. That rate is a property of the constructed fixture and the stated syntax and range rules. It does not estimate a population error rate, demonstrate missing-at-random behavior or establish that the five accepted measurements are accurate. Never fill conversion failures automatically just to make the output complete.

Mathematical Context

Let E count source values that are neither missing nor blank, and let F count invalid-token or out-of-range results within those eligible values. The conversion rejection rate is F/E when E is positive. Here E=10 and F=5, so the rate is 0.5. With no eligible values the rate is undefined rather than zero. Accepted numeric values have the source measurement units; statuses and rejection rates are dimensionless. A finite range check evaluates the chosen data contract and is separate from a statistical outlier test or a measurement-error model.

\[ r=\frac{F}{E},\qquad E>0 \]

Reproducibility

Executed with Python 3.12.14; numpy 2.5.4, pandas 3.0.6. Automated checks confirm the stated output properties.