Browse cheat sheets · Python

Technology: PythonCategory: Data CleaningDifficulty: IntermediateUpdated

pandas Data Types and Conversions

Inspect and convert pandas data types safely, preserve nullability, detect failed conversions, and avoid silent schema changes in production data pipelines.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Inspect DataFrame data types
  5. 2. Convert to pandas nullable dtypes
  6. 3. Convert a column explicitly with astype()
  7. 4. Preserve identifiers as strings
  8. 5. Convert numeric text safely
  9. 6. Convert to nullable integers
  10. 7. Convert to nullable floats
  11. 8. Convert to nullable booleans
  12. 9. Convert dates and timestamps
  13. 10. Parse a known datetime format explicitly
  14. 11. Convert text to pandas string dtype
  15. 12. Convert low-cardinality text to categorical
  16. 13. Define an ordered categorical type
  17. 14. Check which columns have generic object dtype
  18. 15. Detect mixed Python value types inside a column
  19. 16. Find values that fail numeric conversion
  20. 17. Remove known formatting before numeric conversion
  21. 18. Convert percentage strings
  22. 19. Convert integer-like floats carefully
  23. 20. Convert keys on both sides before a merge
  24. 21. Convert with a schema map
  25. 22. Validate resulting dtypes
  26. 23. Compare memory before and after conversion
  27. 24. Avoid unsafe generic coercion
  28. 25. Convert several columns consistently
  29. 26. Preserve decimal precision when exact arithmetic matters
  30. What the result means
  31. What to look for
  32. Identifier became numeric
  33. Numeric conversion creates new nulls
  34. Integer column becomes float
  35. Boolean conversion behaves strangely
  36. Merge keys have different dtypes
  37. Memory is unexpectedly high
  38. convert_dtypes() chooses an unexpected result
  39. Production notes
  40. Schema should be explicit at system boundaries
  41. Conversion failures are data-quality metrics
  42. Nullable dtypes are useful but not identical to NumPy dtypes
  43. astype() can raise on invalid values
  44. Categories need stable semantics
  45. Type conversion can change values
  46. Large conversions allocate memory
  47. Common mistakes
  48. 1. Treating IDs as numbers
  49. 2. Using astype(int) on nullable data
  50. 3. Using errors="coerce" without checking failures
  51. 4. Assuming object means string
  52. 5. Using bool() on text
  53. 6. Converting money to float without considering precision
  54. 7. Using convert_dtypes() as the full schema contract
  55. 8. Converting merge keys independently without checking semantics
  56. Next step
  57. Related deep dives

Problem

A pandas DataFrame can load successfully and still have the wrong schema.

Common production problems include:

  • identifiers inferred as integers
  • numeric columns loaded as strings
  • dates left as text
  • booleans encoded as "Y", "N", 0, or 1
  • integer columns converted to floating point because they contain missing values
  • mixed object columns hiding inconsistent source data
  • failed conversions silently becoming missing values
  • joins failing because key columns use incompatible dtypes

The goal is to make type conversion explicit, inspect failures, and preserve business meaning.

Quick answer

Start by inspecting the current schema:

print(df.dtypes)
df.info()

Use convert_dtypes() when you want pandas nullable dtypes:

df = df.convert_dtypes()

For deliberate conversions, prefer type-specific functions:

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

df["amount"] = pd.to_numeric(
    df["amount"],
    errors="coerce",
)

df["event_time"] = pd.to_datetime(
    df["event_time"],
    errors="coerce",
    utc=True,
)

Always inspect values that fail conversion before overwriting important source columns.

Code / commands

1. Inspect DataFrame data types

print(df.dtypes)

For more context:

df.info()

Use this immediately after reading CSV, JSON, SQL results, or other loosely typed inputs.


2. Convert to pandas nullable dtypes

df = df.convert_dtypes()

This can convert compatible columns to pandas nullable types such as:

Int64
Float64
boolean
string

Use this as a cleanup step when nullable semantics are desirable.

Do not assume it understands your business schema perfectly.

An identifier containing only digits may still require an explicit string conversion.


3. Convert a column explicitly with astype()

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

Use astype() when the source values are already valid for the target type.

Example:

df["quantity"] = (
    df["quantity"]
    .astype("Int64")
)

If invalid values may exist, inspect them first or use a parsing function such as pd.to_numeric().


4. Preserve identifiers as strings

Identifiers should usually remain strings even when they contain only digits.

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

Examples of identifiers that should commonly stay textual:

  • customer IDs
  • account numbers
  • ZIP/postal codes
  • phone numbers
  • SKUs
  • external reference IDs

Converting them to numbers can remove leading zeros or imply arithmetic meaning that does not exist.


5. Convert numeric text safely

parsed_amount = pd.to_numeric(
    df["amount"],
    errors="coerce",
)

Inspect failed conversions:

failed = df.loc[
    df["amount"].notna()
    & parsed_amount.isna()
]

print(failed)

Then assign only after validation:

df["amount"] = parsed_amount

This avoids hiding invalid source values.


6. Convert to nullable integers

If missing integer values are valid:

df["quantity"] = (
    pd.to_numeric(
        df["quantity"],
        errors="coerce",
    )
    .astype("Int64")
)

Use pandas Int64, not NumPy int64, when the column must represent both integers and missing values.


7. Convert to nullable floats

df["amount"] = (
    pd.to_numeric(
        df["amount"],
        errors="coerce",
    )
    .astype("Float64")
)

This gives pandas nullable floating-point semantics.

Use standard float64 when nullable extension behavior is not needed.


8. Convert to nullable booleans

If the source already contains boolean-like values:

df["is_active"] = (
    df["is_active"]
    .astype("boolean")
)

If the source uses text values, map them explicitly.

boolean_map = {
    "Y": True,
    "N": False,
}

mapped = df["is_active"].map(boolean_map)

invalid = df.loc[
    df["is_active"].notna()
    & mapped.isna()
]

print(invalid)

Then:

df["is_active"] = (
    mapped.astype("boolean")
)

Do not use Python truthiness to convert arbitrary strings.

For example:

bool("False")

is True because the string is non-empty.


9. Convert dates and timestamps

parsed_time = pd.to_datetime(
    df["event_time"],
    errors="coerce",
    utc=True,
)

Inspect failures:

failed = df.loc[
    df["event_time"].notna()
    & parsed_time.isna()
]

print(failed)

Then assign:

df["event_time"] = parsed_time

Use utc=True only when UTC normalization matches the source semantics.


10. Parse a known datetime format explicitly

df["event_date"] = pd.to_datetime(
    df["event_date"],
    format="%Y-%m-%d",
    errors="coerce",
)

Explicit formats improve clarity and make unexpected source changes easier to detect.


11. Convert text to pandas string dtype

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

Prefer pandas string dtype over generic object for text columns when practical.

It gives more consistent missing-value behavior.


12. Convert low-cardinality text to categorical

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

This can reduce memory for repeated categorical values.

Good candidates include:

  • status
  • country code
  • channel
  • product type

Poor candidates include:

  • unique IDs
  • free-form text
  • high-cardinality transaction references

Measure rather than assuming a memory benefit.


13. Define an ordered categorical type

from pandas.api.types import CategoricalDtype

priority_type = CategoricalDtype(
    categories=[
        "low",
        "medium",
        "high",
        "critical",
    ],
    ordered=True,
)

df["priority"] = (
    df["priority"]
    .astype(priority_type)
)

Now sorting respects business order rather than alphabetical order.


14. Check which columns have generic object dtype

object_cols = (
    df.select_dtypes(include="object")
    .columns
)

print(object_cols.tolist())

object can hide:

  • strings
  • Python objects
  • mixed values
  • inconsistent source types

Inspect these columns deliberately.


15. Detect mixed Python value types inside a column

type_counts = (
    df["value"]
    .dropna()
    .map(type)
    .value_counts()
)

print(type_counts)

This is useful when an object column behaves inconsistently.

Example output might reveal a mix of:

str
int
float

That is usually a signal to normalize the source data.


16. Find values that fail numeric conversion

parsed = pd.to_numeric(
    df["amount"],
    errors="coerce",
)

bad_values = df.loc[
    df["amount"].notna()
    & parsed.isna(),
    "amount",
]

print(
    bad_values
    .value_counts()
    .head(20)
)

Use this to identify patterns such as:

  • currency symbols
  • commas
  • text labels
  • sentinel values
  • malformed input

17. Remove known formatting before numeric conversion

Example source values:

"$1,250.50"
"$99.00"

Normalize explicitly:

clean_amount = (
    df["amount"]
    .astype("string")
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
)

parsed_amount = pd.to_numeric(
    clean_amount,
    errors="coerce",
)

Do not strip characters blindly unless they are documented formatting.


18. Convert percentage strings

Example:

"15.5%"
"8%"

Parse:

pct = (
    df["discount_pct"]
    .astype("string")
    .str.rstrip("%")
)

df["discount_rate"] = (
    pd.to_numeric(
        pct,
        errors="coerce",
    )
    / 100
)

Now:

15.5% -> 0.155

Make the unit explicit in the output column name.


19. Convert integer-like floats carefully

Suppose:

1.0
2.0
NaN

should represent integer counts.

Validate first:

values = pd.to_numeric(
    df["quantity"],
    errors="coerce",
)

non_integer = values.loc[
    values.notna()
    & (values % 1 != 0)
]

print(non_integer)

If none exist:

df["quantity"] = (
    values.astype("Int64")
)

Do not truncate decimals silently.


20. Convert keys on both sides before a merge

Inspect first:

print(left["customer_id"].dtype)
print(right["customer_id"].dtype)

Normalize consistently:

left["customer_id"] = (
    left["customer_id"]
    .astype("string")
)

right["customer_id"] = (
    right["customer_id"]
    .astype("string")
)

If formatting may differ:

left["customer_id"] = (
    left["customer_id"]
    .str.strip()
)

right["customer_id"] = (
    right["customer_id"]
    .str.strip()
)

Only normalize casing or formatting when the identifier semantics allow it.


21. Convert with a schema map

For known schemas:

schema = {
    "order_id": "string",
    "customer_id": "string",
    "quantity": "Int64",
    "amount": "Float64",
    "is_active": "boolean",
}

df = df.astype(schema)

Use this only when values are already compatible.

If raw data may contain invalid text, parse those columns first.


22. Validate resulting dtypes

expected = {
    "order_id": "string",
    "customer_id": "string",
    "quantity": "Int64",
    "amount": "Float64",
}

for column, dtype in expected.items():
    actual = str(df[column].dtype)

    if actual != dtype:
        raise TypeError(
            f"{column}: expected {dtype}, got {actual}"
        )

Schema validation is useful in production ingestion pipelines.

For larger systems, use a dedicated schema or data-validation framework.


23. Compare memory before and after conversion

before = df.memory_usage(
    deep=True
).sum()

Convert:

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

Measure again:

after = df.memory_usage(
    deep=True
).sum()

print("Before:", before)
print("After:", after)

Use actual measurements before adopting dtype changes purely for memory reasons.


24. Avoid unsafe generic coercion

This is concise:

df["value"] = pd.to_numeric(
    df["value"],
    errors="coerce",
)

But every invalid value becomes missing.

Always measure the impact:

before_nulls = df["value"].isna().sum()

parsed = pd.to_numeric(
    df["value"],
    errors="coerce",
)

after_nulls = parsed.isna().sum()

print(
    "New nulls:",
    after_nulls - before_nulls,
)

Unexpected new nulls are a data-quality signal.


25. Convert several columns consistently

numeric_cols = [
    "amount",
    "tax",
    "discount",
]

for col in numeric_cols:
    df[col] = pd.to_numeric(
        df[col],
        errors="coerce",
    )

For production pipelines, capture conversion-failure counts per column before assignment.


26. Preserve decimal precision when exact arithmetic matters

Binary floating-point is not appropriate for every financial calculation.

For exact decimal semantics:

from decimal import Decimal

df["amount_decimal"] = (
    df["amount_text"]
    .map(
        lambda x: (
            Decimal(x)
            if pd.notna(x)
            else None
        )
    )
)

This produces Python objects and is slower than native numeric dtypes.

For large analytical workloads, consider using a storage/execution engine with native decimal types rather than relying on object-backed Python Decimal columns in pandas.

What the result means

dtype / function Meaning
object Generic Python-object storage; often strings or mixed values.
string pandas nullable string dtype.
Int64 pandas nullable 64-bit integer dtype.
Float64 pandas nullable floating-point dtype.
boolean pandas nullable boolean dtype.
category Encoded categorical values, useful for repeated low-cardinality data.
datetime64[ns] timezone-naive pandas/NumPy datetime.
datetime64[ns, UTC] timezone-aware datetime in UTC.
astype() Explicit cast; generally expects compatible values.
convert_dtypes() Infers suitable pandas nullable dtypes.
pd.to_numeric() Parses numeric values with configurable error handling.
pd.to_datetime() Parses dates/timestamps.

The most important distinction is:

storage type != business meaning

An integer-looking value may still be an identifier.

A string may really represent a timestamp.

Type decisions should follow the data contract.

What to look for

Identifier became numeric

Check whether leading zeros were lost.

If yes, recover from the original source if possible and read it as text next time.

Numeric conversion creates new nulls

Those values failed parsing.

Inspect them before continuing.

Integer column becomes float

This commonly happens when NumPy integer data must represent missing values.

Use pandas nullable Int64 when integer semantics must be preserved.

Boolean conversion behaves strangely

Text values such as:

"False"
"0"
"N"

should be mapped explicitly.

Do not rely on Python truthiness.

Merge keys have different dtypes

Normalize both sides before merging.

Also inspect formatting differences.

Memory is unexpectedly high

Check:

  • object columns
  • repeated strings
  • unnecessary high-precision numeric types
  • columns that could safely be categorical

Measure before and after changes.

convert_dtypes() chooses an unexpected result

It infers from observed values, not your business contract.

Override important columns explicitly.

Production notes

Schema should be explicit at system boundaries

Define expected types when reading:

  • CSV
  • APIs
  • SQL extracts
  • message payloads
  • external files

Do not rely only on inference.

Conversion failures are data-quality metrics

Track:

  • number of failed numeric parses
  • number of failed dates
  • invalid boolean codes
  • unexpected category values

These should not disappear silently.

Nullable dtypes are useful but not identical to NumPy dtypes

Examples:

Int64 != int64
Float64 != float64
boolean != bool

Nullable pandas extension dtypes support missing values differently.

Test downstream compatibility when exporting or integrating with other libraries.

astype() can raise on invalid values

That is often desirable in controlled pipelines.

If malformed raw data is expected, parse first, inspect failures, and then cast.

Categories need stable semantics

A categorical dtype is useful for low-cardinality data, but categories can differ between DataFrames.

Be careful when:

  • concatenating categoricals
  • comparing ordered categories
  • writing to external systems

Type conversion can change values

Examples:

  • floats to integers can truncate or fail
  • timestamps can shift during timezone conversion
  • strings to numbers can lose formatting
  • binary floats can introduce precision behavior

Validate before and after.

Large conversions allocate memory

Casting a large column may create a new array.

On memory-constrained workloads:

  • convert only necessary columns
  • avoid repeated conversions
  • release unused intermediates
  • consider processing upstream in SQL/Spark when appropriate

Common mistakes

1. Treating IDs as numbers

Identifiers are labels, not quantities.

Keep them as strings unless arithmetic has meaning.

2. Using astype(int) on nullable data

This can fail when missing values exist.

Use nullable Int64 when appropriate.

3. Using errors="coerce" without checking failures

This can turn bad source values into silent missing values.

Track newly created nulls.

4. Assuming object means string

An object column may contain mixed Python types.

Inspect actual values.

5. Using bool() on text

This is wrong for values such as:

bool("False")  # True

Map source values explicitly.

6. Converting money to float without considering precision

Binary floating point may be inappropriate for exact financial calculations.

Choose the numeric model based on requirements.

7. Using convert_dtypes() as the full schema contract

It is an inference helper, not a substitute for explicit business schema.

8. Converting merge keys independently without checking semantics

Two columns can both become strings and still fail to match because of:

  • whitespace
  • leading zeros
  • casing
  • formatting rules

Next step

Use the type issue to decide what comes next.

  • Numeric parse failures → inspect raw values and define cleanup rules.
  • Dates remain strings → parse with explicit datetime rules.
  • Identifiers lost formatting → reload from source with string dtype if possible.
  • Nullable integers became floats → move to pandas Int64.
  • Booleans come from codes/text → map allowed source values explicitly.
  • Merge keys do not match → normalize dtype and formatting on both sides.
  • Object columns dominate memory → inspect whether string or category is appropriate.
  • Schema is business-critical → add explicit schema validation at ingestion.
  • Conversions are expensive on very large data → push typing closer to the data source or a distributed execution engine.

← All cheat sheets