Data type conversion

This is the exact file agents read, including the credit header and licence notice. Save it as data-type-conversion/SKILL.md.

---
name: data-type-conversion
description: Convert columns to the correct, intended data type so numbers compute, dates sort, booleans filter and categories group, across pandas, SQL and R. Covers columns read as text that should be numeric, the astype versus to_numeric choice, errors="coerce", nullable integers (Int64) when NaN is present, the boolean-from-string trap where "False" becomes True, category dtype for low-cardinality columns, downcasting for memory, float precision for money, and verifying every conversion by counting what failed. Use it when someone says "convert this column", "it's stored as text", "astype", "to_numeric", "cannot convert float NaN to integer", "change the dtype", "my numbers won't add up", "True/False is a string", or "shrink the memory". Do NOT use it for parsing messy date and time strings (see date-parsing), for standardising inconsistent text values before you map them (see text-standardisation), for filling the gaps themselves (see handling-missing-data), or for designing a database schema.
license: Free to use and adapt with credit to Afeez Adebayo (https://afeezadebayo.com). Link back to this skill at https://afeezadebayo.com/skills-md/data/data-cleaning-preparation/data-type-conversion/ and to https://www.linkedin.com/in/afeez-adebayo/.
---

# Data type conversion

## 1. When to use this skill, and when not to

Use this skill when a column holds the right values in the wrong type, and you need to change the type before analysis, joining, sorting or modelling. The common signs are arithmetic that concatenates instead of adding, a sort that puts "100" before "20", a filter on True that returns everything, a group-by that treats "Yes" and "yes " as two groups, or a file that uses far more memory than it should.

Use it for the decision, not just the one-line cast. The point of the skill is to pick the safe conversion, handle the values that will not convert, keep missing values intact and prove afterwards that nothing changed by accident.

Do not use it for four neighbouring jobs. Parsing dates and times out of messy strings belongs in date-parsing, because dates have their own ambiguity and timezone rules. Standardising inconsistent text ("USA", "U.S.", "United States") belongs in text-standardisation; do that first, then convert the cleaned column. Deciding what to do about the gaps themselves belongs in handling-missing-data. Designing the nullability and types of a database schema is a modelling task, not a cleaning one.

## 2. Inputs the agent must have before starting

Ask for anything on this list that is missing. Do not convert on assumption.

- The tool and version. pandas 1.x and 2.x differ on nullable types and on `errors="ignore"`. Say which is in use.
- The current dtype of each column to convert, and the type it should become. "Should be an integer" and "should be a whole number that can be missing" lead to different casts.
- The real meaning of a non-numeric token. A blank, "N/A", "-", "unknown" or "-999" in a number column may be a missing value, not a parse error. Confirm before coercing it to NaN.
- For money and identifiers, the precision rule. Account numbers, phone numbers and product codes that start with zero must stay text. Currency needs a fixed-precision plan.
- Whether rows that fail to convert should be dropped, flagged or investigated. The default is to flag and report, never to drop silently.

If the dataset is unlabelled, profile first: print the dtype, three sample values and the count of values that fail a trial conversion for each target column, then confirm the plan.

## 3. The conversion workflow, step by step

1. Profile the column before touching it. Record the current dtype, the number of rows, the number of distinct values, and the first few values. A column of numbers stored as text almost always hides a stray character: a thousands separator, a currency symbol, a trailing space or a footnote mark.

2. Decide the target type from the column's job, not its current appearance.
   - If the values are measured quantities you will compute with, target a numeric type (integer or float).
   - If a "number" is really a label you will never add up (postcode, ID, phone, year-as-category), keep it as text or category.
   - If the column is a flag, target boolean.
   - If the column has fewer than roughly half as many distinct values as rows, and the values repeat, consider the category type for memory and speed.
   - If the column is a date or time, stop and use date-parsing.

3. Clean the blockers before casting, not during. Strip whitespace, remove thousands separators, currency symbols and percent signs, and map the agreed missing tokens to a true null. Keep this as a separate, inspectable step so you can see what you removed.

4. Choose the conversion function by how forgiving you need to be.
   - For a direct, all-or-nothing cast where every value is already clean, use the native cast (`astype` in pandas, `CAST` in SQL, `as.numeric` in R).
   - For a column that may contain values that cannot convert, use the coercing path (`to_numeric(errors="coerce")` in pandas, `TRY_CAST` or `SAFE_CAST` in SQL) so bad values become null instead of raising, then count them.

5. Handle the integer-with-missing case explicitly. A plain integer type cannot hold a missing value. In pandas, `astype(int)` on a column with any NaN raises "Cannot convert non-finite values (NA or inf) to integer", and `to_numeric(errors="coerce")` returns float64 even when every value is a whole number, because NaN is a float. If the column is conceptually an integer that can be missing, convert to the nullable integer type `Int64` (capital I), not `int`. Do not reach for `fillna(0)` to force the cast, because zero is a real value and inventing it distorts counts and averages.

6. Convert booleans by an explicit map, never by a blind cast. `astype(bool)` on text treats every non-empty string as True, so the string "False" becomes True and "0" becomes True. Build a dictionary ({"true": True, "yes": True, "1": True, "false": False, "no": False, "0": False}, applied to the lower-cased, stripped value) and map against it. Any value not in the map should become null and be reported, not guessed.

7. Protect precision for money and large numbers. Never store currency as a binary float, because 0.1 plus 0.2 is not 0.3 in float arithmetic and the error compounds over a sum. Store money as integer minor units (pence, cents) or as a fixed-precision decimal type. Very large integers beyond the 64-bit range will silently become floats and lose their last digits; keep them as strings unless you need to compute with them.

8. Apply the smallest type that fits, only when memory is the goal. Downcast a clean numeric column with the integer or float downcast option, or move a repetitive text column to the category type. Measure memory before and after so the saving is real; do not downcast by reflex, because it can cost precision and interferes with later joins.

9. Order the categories when the values have a rank. A plain category treats "small", "medium" and "large" as unordered labels, so a comparison like `size > "medium"` fails and a sort is alphabetical. When a column is ordinal, declare the order explicitly (an ordered `CategoricalDtype` in pandas, an ordered `factor` with `levels=` in R) so comparison and sorting follow the real sequence rather than the alphabet. Leave nominal categories (region, colour) unordered.

10. Prefer vectorised conversion over row-by-row work. A column-wide cast or `to_numeric` runs in one compiled pass; a per-row `apply` with a Python function over the same column can be ten to a hundred times slower and is easy to get subtly wrong. Reach for `apply` only when no vectorised method exists, and even then convert the column once rather than inside a loop.

11. Verify every conversion and report. For each converted column, confirm the new dtype, count how many values became null during the conversion, and compare that count against the number of recognised missing tokens you expected. If more values failed than you expected, stop and show examples. For a high-stakes column, run a round-trip check: cast the new column back to text and compare it against the cleaned original, so a silent truncation or rounding shows up as a mismatch. State the summary in plain terms: "price converted from text to Int64, 14 of 10,000 values could not parse and are now null, all 14 contained the footnote mark '*'."

## 4. Worked example, end to end

A sales export has a `price` column read as text, a `vip` column of flags, and a `region` column that repeats across 50,000 rows.

The obvious move, `df["price"].astype(int)`, raises immediately because some cells are blank and one is "1,299". The careful route is to profile first: `price` is object dtype, 50,000 rows, with values like "1,299", "899.00", "", and "N/A". So clean, then coerce, then type for missingness:

```python
cleaned = (df["price"].astype("string")
           .str.replace(",", "", regex=False)
           .str.strip()
           .replace({"N/A": pd.NA, "": pd.NA}))
df["price"] = pd.to_numeric(cleaned, errors="coerce")
failed = df["price"].isna().sum()
```

`failed` is 37. The 37 are the blanks and "N/A" values we expected, so this is missingness, not a parse error. Because price is money, we do not keep it as a float with rounding risk; we store pence as a nullable integer:

```python
df["price_pence"] = (df["price"] * 100).round().astype("Int64")
```

For `vip`, a blind `astype(bool)` would mark the 1,200 rows that literally say "False" as True. Instead:

```python
truth = {"yes": True, "true": True, "1": True, "y": True,
         "no": False, "false": False, "0": False, "n": False}
key = df["vip"].astype("string").str.strip().str.lower()
df["vip"] = key.map(truth).astype("boolean")
unmapped = key[~key.isin(truth) & key.notna()].unique()
```

`unmapped` returns `["maybe"]`, a value nobody anticipated. We report it rather than coercing it. For `region`, the category type cuts memory because 50,000 rows hold only six distinct regions:

```python
before = df["region"].memory_usage(deep=True)
df["region"] = df["region"].astype("category")
after = df["region"].memory_usage(deep=True)
```

Memory drops from about 3.1 MB to about 50 KB. The handover states each change, the failure counts, and the one value ("maybe") that needs a human decision.

## 5. Templates you can use verbatim

Profiling prompt:

> Before converting, print for each target column: current dtype, row count, distinct count, five sample values, and the number of values that fail a trial `to_numeric(errors="coerce")`. Do not convert yet. Tell me which columns look like IDs or money that should stay text.

Safe numeric conversion (pandas):

```python
s = (df[col].astype("string").str.strip()
     .str.replace(r"[,£$%]", "", regex=True)
     .replace({"N/A": pd.NA, "-": pd.NA, "": pd.NA}))
out = pd.to_numeric(s, errors="coerce")
# integer that can be missing:
out = out.astype("Int64") if (out.dropna() % 1 == 0).all() else out
```

Safe conversion (SQL):

```sql
SELECT id,
       SAFE_CAST(REPLACE(price, ',', '') AS NUMERIC) AS price_num
FROM sales;  -- BigQuery; use TRY_CAST in SQL Server, TRY_CONVERT elsewhere
```

Post-conversion report:

> For each converted column report: old dtype, new dtype, count of values coerced to null, and whether that equals the expected count of missing tokens. List any value that could not be mapped.

## 6. Failure modes and anti-patterns

- Forcing an integer cast through a missing value with `fillna(0)`. The rows convert, but zero is now mixed in with real quantities and every count and average is wrong. Spot it when a total is suspiciously round or a minimum is zero. Use `Int64` instead and keep the null.
- Casting a boolean from text with `astype(bool)`. Every non-empty string becomes True, so "False", "no" and "0" all read as True. Spot it when almost every row is True. Map explicitly against a truth dictionary.
- Storing money as float. Sums drift by fractions of a penny and reconciliations fail. Spot it when a total disagrees with the source by a tiny amount. Store minor units as integers or use a decimal type.
- Letting `to_numeric(errors="coerce")` run without counting the nulls it created. Bad values vanish into NaN and the row survives looking clean. Always compare the new null count against what you expected, and show examples when they differ.
- Converting an ID or postcode to a number. Leading zeros disappear ("007" becomes 7) and long codes lose their last digits past the 64-bit limit. Spot it when codes are shorter than they should be. Keep identifiers as text.

## 7. Quality bar before handover

- Every converted column has its old dtype, new dtype and coerced-null count reported.
- No conversion used `fillna` to force an integer cast; integers that can be missing use a nullable type.
- Booleans were mapped from an explicit dictionary, and any unmapped value is listed, not guessed.
- Money is stored as integer minor units or a decimal type, never a binary float.
- Identifiers, postcodes and phone numbers remain text, with leading zeros intact.
- Any downcasting or category conversion names the memory before and after.
- The original column is preserved, or the change is reversible, until the person confirms the result.

## 8. Guardrails

- Irreversible steps need confirmation. Overwriting a source column, dropping rows that failed to convert, or saving over the input file are bulk edits; default to writing a new column or a new file and ask before replacing the original.
- Never invent values to make a cast succeed. A value that will not convert is either a missing value to null out and count, or a real value the plan missed; it is never a silent zero.
- Do not fabricate tool behaviour or version limits. Where behaviour is version-dependent (nullable types, `errors="ignore"` removal), verify against the installed version before relying on it.
- PII and credentials: ID numbers, card numbers and phone numbers often arrive in columns people want to "convert to numbers". Keep them as text, never log their values, and never paste them into an external service to clean them.
- This skill changes types, not meaning. It will not decide business rules (what counts as VIP, which currency a column is in); it surfaces those for a human.
- Cost: type conversion runs locally and spends no API credits or tokens on the data itself. The cheaper and faster path is always the vectorised cast over a per-row `apply`; if a file is too large to hold in memory, convert in chunks or push the cast into the database with `TRY_CAST` rather than loading everything.

## 9. Tool-specific appendix (pandas)

Fenced off from the tool-agnostic core above.

- `astype` is a strict, all-or-nothing cast; one bad value raises. `to_numeric` with `errors="coerce"` is the forgiving path and turns failures into NaN. `errors="ignore"` returned the input unchanged and is removed in pandas 3.0; do not rely on it.
- `pd.to_numeric` returns float64 when any value is missing, because NaN is a float. To keep whole numbers, chain `.astype("Int64")`, the NumPy-nullable integer, which holds `pd.NA`.
- `convert_dtypes()` infers the best nullable dtype per column and is a reasonable first pass, but check its choices; it can pick a wider type than you want.
- `downcast="integer"` or `downcast="float"` on `to_numeric` shrinks a clean numeric column to the smallest safe dtype. `dtype_backend="numpy_nullable"` or `"pyarrow"` (pandas 2.0+) returns nullable results where coerced failures become `pd.NA` rather than `NaN`.
- The `category` dtype stores repeated strings as integer codes against a lookup. It saves memory on low-cardinality columns and speeds group-by, but adding a value outside the known categories needs `cat.add_categories` first.
- For booleans use the nullable `"boolean"` dtype so a missing flag stays missing rather than defaulting to False.
- SQL equivalents: `TRY_CAST` / `TRY_CONVERT` (SQL Server), `SAFE_CAST` (BigQuery), `::numeric` with a cleaning step (PostgreSQL). R equivalents: `as.numeric`, `as.integer`, `as.logical` (map explicitly, as `as.logical("False")` is NA, not FALSE), and `factor` for categories.

## Sources

- pandas, pandas.to_numeric API reference, last verified 1 November 2026: https://pandas.pydata.org/docs/reference/api/pandas.to_numeric.html
- pandas, pandas.to_datetime API reference (for the numeric-with-format deprecation and nullable behaviour), last verified 1 November 2026: https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html
- GeeksforGeeks, How to Fix: ValueError: cannot convert float NaN to integer, last verified 1 November 2026: https://www.geeksforgeeks.org/
- Anthropic, Equipping agents for the real world with Agent Skills, last verified 1 November 2026: https://www.anthropic.com/engineering/equipping-agents-for-the-real-world-with-agent-skills
- Kanaries, pandas string operations reference, last verified 1 November 2026: https://docs.kanaries.net/topics/Pandas/pandas-string-operations