5.3 Strings, Categories, Dates, and Units

Entity resolution showed why surface form and meaning must be separated. " North ", "NORTH", and a full-width Unicode spelling may represent the same zone, while two similar customer names may still represent different people. This section builds reversible normalization pipelines: preserve raw evidence, create comparison keys, apply approved domain mappings, and validate the result.

Normalize text in observable stages

Start with a dedicated string dtype rather than assuming object means text:

zone_raw = orders["zone"].astype("string")

pandas 3.x changed default string inference compared with older releases. Explicit dtypes and pd.api.types.is_string_dtype make intent clearer across boundaries and versions.

A mechanical normalization pipeline might be:

orders["zone_raw"] = orders["zone"]

orders["zone_key"] = (
    orders["zone_raw"]
        .astype("string")
        .str.normalize("NFKC")
        .str.strip()
        .str.replace(r"\s+", " ", regex=True)
        .str.casefold()
)

Each stage has a narrow purpose:

• Unicode NFKC normalization makes compatibility forms, such as full-width Latin letters, comparable.

• strip removes outer whitespace; the regular expression compresses internal whitespace runs.

• casefold provides stronger Unicode-aware caseless comparison than simple lowercase conversion.

These are comparison choices, not universally safe display transformations. NFKC can collapse distinctions that matter in some identifiers, languages, or scientific notation. Preserve the original and test collisions:

collisions = pd.crosstab(
    orders["zone_key"],
    orders["zone_raw"],
    dropna=False,
)

Avoid deleting punctuation, accents, or non-ASCII characters indiscriminately. That can erase meaningful distinctions, damage names, and manufacture matches.

Apply business mappings explicitly

Mechanical normalization cannot know that "n." means North in this dataset. Use a versioned mapping approved by the domain owner:

zone_mapping = {
    "north": "North",
    "n.": "North",
    "south": "South",
    "s.": "South",
}

orders["zone_clean"] = (
    orders["zone_key"]
        .map(zone_mapping)
        .astype("string")
)

.map returns missing values for unknown keys. That is useful: unexpected categories remain visible instead of being silently forced into an “other” bucket.

unmapped = (
    orders.loc[orders["zone_clean"].isna(), ["zone_raw", "zone_key"]]
          .drop_duplicates()
)

Review unmapped values, update the controlled mapping, and record its version. Do not use fuzzy matching to auto-correct categories unless false corrections are measured and reversible.

Category dtype encodes a controlled domain

A categorical dtype is useful when a column draws from a small, defined set or follows a non-lexical order:

service_type = pd.CategoricalDtype(
    categories=["standard", "priority", "express"],
    ordered=True,
)

orders["service_level"] = orders["service_level"].astype(service_type)

Now sorting follows the declared service order rather than alphabetic order. Values outside the category set become missing during conversion, so inspect failures before trusting the column:

failed = (
    orders["service_level_raw"].notna()
    & orders["service_level"].isna()
)

Categories are not merely a compression trick. They are part of the schema and must be updated when the business adds a valid level. Removing “unused” categories can also be wrong when the schema permits a category that happens not to appear in one sample.

Parse dates with a declared format and error policy

Keep the raw timestamp beside its parsed result:

orders["ordered_at_raw"] = orders["ordered_at"]

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

When one exact format is expected, prefer it over format="mixed":

parsed_date = pd.to_datetime(
    orders["order_date_raw"],
    format="%Y-%m-%d",
    errors="coerce",
)

errors="coerce" turns invalid values into NaT, making them easy to locate. It does not make them valid or explain why they failed:

parse_failed = (
    orders["order_date_raw"].notna()
    & parsed_date.isna()
)

failed_examples = orders.loc[parse_failed, "order_date_raw"]

Use errors="raise" at a strict ingestion boundary where one malformed value should stop the pipeline. Use coercion when the contract includes quarantine and review.

Ambiguous strings such as 03/04/2026 need a source-specific convention. Do not infer day-first versus month-first from whichever interpretation succeeds. Prefer ISO 8601 exchange formats and explicit format strings.

utc=True converts timezone-aware inputs to a common UTC representation. A timestamp without a zone still needs an assumption about its source timezone before conversion. Store local business date separately when day-boundary reporting depends on local time.

Convert units from labels, never from magnitude guesses

Mixed units can create values that look like outliers. Preserve the original value and unit, then map explicit conversion factors:

to_km = {
    "km": 1.0,
    "m": 0.001,
    "mi": 1.609344,
}

factor = shipments["distance_unit"].map(to_km)
unknown_unit = factor.isna() & shipments["distance_unit"].notna()

if unknown_unit.any():
    raise ValueError(
        shipments.loc[unknown_unit, "distance_unit"].unique().tolist()
    )

shipments["distance_km"] = shipments["distance_value"] * factor

Do not assume a value larger than 100 must be meters. A 120-kilometer delivery is possible, while 3 miles is not 3 kilometers. Unit meaning comes from metadata.

For temperatures, conversion may require both scale and offset:

For currencies, conversion also requires an exchange-rate source and effective timestamp. A bare conversion factor without provenance is not reproducible.

Validate normalized outputs without discarding raw evidence

After normalization, check:

• Raw non-missing values that became missing.

• Unknown categories and units.

• Many-to-one collisions between raw and normalized text.

• Date ranges, timezone consistency, and parse failures.

• Unit-converted ranges and reversibility within declared rounding tolerance.

• Mapping and conversion-rule versions.

The next section uses these normalized representations to investigate outliers. A value may be statistically extreme because it is wrong, because its unit was misread, or because it records a rare but important event.