Text To Numeric

Text to Numeric

Parse formatted numeric strings into proper numeric types, capturing the format metadata separately.

When to invoke

• A column contains values like $4.27, 1,234.56, €1.2M, 3.5%, (500) (accounting negative), 2.5K.
• Downstream analysis needs the column as float / int.

Procedure

1. Load dataset and identify the target column(s). If the user hasn't specified, list columns where dtype == object but >80% of values look numeric after stripping symbols.
2. Profile the column — sample 20 distinct values and detect format:

• Currency symbols: $, €, £, ¥, ₪, ₹, etc. → record the detected currency.
• Thousands separators: , (US), . (EU), space (FR/scientific), ' (CH).
• Decimal marker: . or , — infer from context; ask if ambiguous.
• Scale suffixes: K, M, B, T (multiply accordingly).
• Percentage: % → divide by 100 OR keep as-is; ask the user.
• Accounting negatives: (500) → -500.
• Unicode minus: − → -.

1. Confirm the detected format with the user before applying, especially the decimal/thousands convention and percentage handling.
2. Parse:

• Strip currency symbols, whitespace, and thousands separators.
• Apply scale suffix multipliers.
• Convert accounting negatives.
• Cast to float; downcast to int if all values are whole numbers.

1. Write two columns (default):

• Original column preserved (renamed to _raw if the user wants a clean replacement) OR overwritten.
• New numeric column _numeric (or same name).

1. Record metadata in the data dictionary:

• Original format (e.g. "US currency, $ prefix, comma thousands, dot decimal").
• Detected currency ISO 4217 code if present.
• Scale convention applied.
• Percentage handling decision.
• If no data dictionary exists in the dataset's folder, create one via the add-data-dictionary skill.

1. Report unparseable rows — list values that failed parsing with their row indices. Leave null in numeric column; do not drop.
2. Write output with _numeric suffix.

Dependencies

pip install pandas

Optional: babel.numbers.parse_decimal for locale-aware parsing.

Edge cases

• Mixed currencies in one column (e.g. $5, €4, £3) — extract currency per row into a separate currency column, convert numeric without the symbol. Do not attempt FX conversion; that is a separate operation.
• Ranges ("100-200", "5–10") — flag and ask user (split into _min/_max, take midpoint, or leave as text).
• Approximations ("~500", "1M") — strip qualifier, record in a qualifier column, convert the numeric portion.
• Scientific notation — pandas handles natively.
• NaN sentinels ("N/A", "-", "null", "") — convert to NaN; list the sentinels detected.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: danielrosehill
• Source: danielrosehill/Claude-Data-Wrangler-plugin
• License: MIT

Install and usage instructions live in the source repository linked above.