Browse cheat sheets · Python
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
- Problem
- Quick answer
- Code / commands
- 1. Inspect DataFrame data types
- 2. Convert to pandas nullable dtypes
- 3. Convert a column explicitly with astype()
- 4. Preserve identifiers as strings
- 5. Convert numeric text safely
- 6. Convert to nullable integers
- 7. Convert to nullable floats
- 8. Convert to nullable booleans
- 9. Convert dates and timestamps
- 10. Parse a known datetime format explicitly
- 11. Convert text to pandas string dtype
- 12. Convert low-cardinality text to categorical
- 13. Define an ordered categorical type
- 14. Check which columns have generic object dtype
- 15. Detect mixed Python value types inside a column
- 16. Find values that fail numeric conversion
- 17. Remove known formatting before numeric conversion
- 18. Convert percentage strings
- 19. Convert integer-like floats carefully
- 20. Convert keys on both sides before a merge
- 21. Convert with a schema map
- 22. Validate resulting dtypes
- 23. Compare memory before and after conversion
- 24. Avoid unsafe generic coercion
- 25. Convert several columns consistently
- 26. Preserve decimal precision when exact arithmetic matters
- What the result means
- What to look for
- Identifier became numeric
- Numeric conversion creates new nulls
- Integer column becomes float
- Boolean conversion behaves strangely
- Merge keys have different dtypes
- Memory is unexpectedly high
- convert_dtypes() chooses an unexpected result
- Production notes
- Schema should be explicit at system boundaries
- Conversion failures are data-quality metrics
- Nullable dtypes are useful but not identical to NumPy dtypes
- astype() can raise on invalid values
- Categories need stable semantics
- Type conversion can change values
- Large conversions allocate memory
- Common mistakes
- 1. Treating IDs as numbers
- 2. Using astype(int) on nullable data
- 3. Using errors="coerce" without checking failures
- 4. Assuming object means string
- 5. Using bool() on text
- 6. Converting money to float without considering precision
- 7. Using convert_dtypes() as the full schema contract
- 8. Converting merge keys independently without checking semantics
- Next step
- 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, or1 - 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:
objectcolumns- 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
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.