Browse cheat sheets · Python
Debug Common pandas Problems
Diagnose common pandas failures involving dtypes, missing values, joins, chained assignment, duplicate data, datetime errors, memory pressure, and unexpected row-count changes.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Debug KeyError for a missing column
- 2. Debug wrong capitalization or renamed columns
- 3. Debug numeric conversion failures
- 4. Debug unexpected nulls after conversion
- 5. Debug datetime parsing failures
- 6. Debug timezone-aware vs timezone-naive comparison errors
- 7. Debug filters returning no rows
- 8. Debug boolean filter errors
- 9. Debug chained-assignment problems
- 10. Debug unexpected row growth after a merge
- 11. Debug missing matches after a merge
- 12. Debug duplicate rows
- 13. Debug inflated totals after a join
- 14. Debug unexpected dtype changes after CSV ingestion
- 15. Debug integer columns becoming floats
- 16. Debug object columns that should be numeric
- 17. Debug ValueError: cannot convert float NaN to integer
- 18. Debug SettingWithCopyWarning / copy-on-write confusion
- 19. Debug groupby() totals that look wrong
- 20. Debug missing groups in groupby()
- 21. Debug wrong “latest row” logic
- 22. Debug slow .apply()
- 23. Debug memory usage
- 24. Reduce memory before a large merge
- 25. Debug unexpectedly slow sorting
- 26. Debug CSV rows shifting into wrong columns
- 27. Debug malformed CSV lines
- 28. Debug unexpected JSON shape
- 29. Debug results by comparing invariants
- 30. Build a reusable DataFrame diagnostic helper
- What the result means
- What to look for
- Row count changes unexpectedly
- Null count increases unexpectedly
- Dtype changes unexpectedly
- Totals change after enrichment
- Data looks correct in head() but metrics are wrong
- One workaround “fixes” the error but hides the reason
- Production notes
- Debug with small, representative slices
- Track schema and row-count changes
- Avoid mutating many things at once
- Preserve bad records for analysis
- Exceptions can be safer than silent coercion
- pandas is an in-memory tool
- Common mistakes
- 1. Fixing symptoms instead of causes
- 2. Debugging only the final DataFrame
- 3. Trusting head()
- 4. Ignoring business grain
- 5. Ignoring dtypes
- 6. Suppressing warnings blindly
- 7. Using errors="coerce" without measuring impact
- 8. Dropping malformed input rows automatically
- 9. Debugging performance before correctness
- Next step
- Related deep dives
Problem
A pandas workflow fails, produces the wrong row count, changes dtypes unexpectedly, creates missing values, or returns results that do not match the business logic.
Common symptoms include:
KeyErrorValueErrorduring conversion- merge/join failures
- unexpected duplicate rows
- unexpected nulls
- chained-assignment warnings
- datetime comparison errors
- memory errors
objectdtype where numeric/date data was expected- totals changing after a merge
- filtering returning zero rows
The goal is to isolate the failure quickly without masking the underlying data problem.
Quick answer
Start with a compact diagnostic pass:
print("Shape:", df.shape)
print("\nColumns:")
print(df.columns.tolist())
print("\nDtypes:")
print(df.dtypes)
print("\nNulls:")
print(
df.isna()
.sum()
.sort_values(ascending=False)
.head(20)
)
print("\nDuplicates:")
print(df.duplicated().sum())
print("\nSample:")
print(df.head())
Then ask:
Did row count change?
Did dtype change?
Did null count change?
Did key uniqueness change?
Did the operation preserve the intended grain?
Most pandas bugs become easier once you compare the DataFrame immediately before and after the failing transformation.
Code / commands
1. Debug KeyError for a missing column
Check exact column names:
print(df.columns.tolist())
Look for whitespace:
for col in df.columns:
print(repr(col))
If the source contains accidental whitespace:
df.columns = (
df.columns
.str.strip()
)
Do not normalize column names blindly if external contracts depend on exact names.
2. Debug wrong capitalization or renamed columns
Check:
print(df.columns)
Case differences matter:
CustomerID
customerid
customer_id
are different column names.
If you need normalized names, do it once at ingestion and make that schema explicit.
3. Debug numeric conversion failures
Instead of:
df["amount"] = df["amount"].astype(float)
use:
parsed = pd.to_numeric(
df["amount"],
errors="coerce",
)
Find values that failed:
failed = df.loc[
df["amount"].notna()
& parsed.isna(),
"amount",
]
print(
failed
.value_counts()
.head(20)
)
Typical causes:
- commas
- currency symbols
- text labels
- empty strings
- sentinel values
- malformed source records
4. Debug unexpected nulls after conversion
Measure before and after:
before_nulls = df["amount"].isna().sum()
parsed = pd.to_numeric(
df["amount"],
errors="coerce",
)
after_nulls = parsed.isna().sum()
print(
"New nulls:",
after_nulls - before_nulls,
)
Unexpected new nulls mean source values failed conversion.
Do not silently continue.
5. Debug datetime parsing failures
Parse into a temporary Series:
parsed = pd.to_datetime(
df["event_time"],
errors="coerce",
)
Find failures:
failed = df.loc[
df["event_time"].notna()
& parsed.isna(),
"event_time",
]
print(
failed
.value_counts()
.head(20)
)
Check for:
- mixed formats
- invalid dates
- timezone text
- day/month ambiguity
- unexpected source changes
6. Debug timezone-aware vs timezone-naive comparison errors
Inspect both sides:
print(df["event_time"].dtype)
print(start_time)
print(start_time.tzinfo)
If the Series is UTC-aware:
df["event_time"] = pd.to_datetime(
df["event_time"],
utc=True,
errors="coerce",
)
Then use an aware comparison boundary:
start_time = pd.Timestamp(
"2026-10-01",
tz="UTC",
)
Do not compare aware and naive timestamps without deciding what timezone the naive value represents.
7. Debug filters returning no rows
Start by checking actual values:
print(
df["status"]
.value_counts(dropna=False)
.head(20)
)
Inspect normalized values:
normalized = (
df["status"]
.astype("string")
.str.strip()
.str.casefold()
)
print(
normalized
.value_counts(dropna=False)
.head(20)
)
Common causes:
- whitespace
- capitalization
- nulls
- dtype mismatch
- unexpected codes
- incorrect date boundaries
8. Debug boolean filter errors
Incorrect:
df[
df["amount"] > 100
and df["status"] == "active"
]
Correct:
df.loc[
(df["amount"] > 100)
& (df["status"] == "active")
]
For pandas Series, use:
&|~
and put each condition in parentheses.
9. Debug chained-assignment problems
Risky:
df[df["amount"] > 100]["flag"] = True
Use:
df.loc[
df["amount"] > 100,
"flag",
] = True
If you intentionally want a separate filtered DataFrame:
subset = df.loc[
df["amount"] > 100
].copy()
subset["flag"] = True
Make ownership explicit instead of relying on ambiguous views/copies.
10. Debug unexpected row growth after a merge
Measure before and after:
before = len(left)
result = left.merge(
right,
on="customer_id",
how="left",
)
after = len(result)
print("Before:", before)
print("After:", after)
print("Added rows:", after - before)
If a left enrichment join grows unexpectedly, inspect duplicates on the right:
duplicate_right_keys = right.loc[
right["customer_id"]
.duplicated(keep=False)
].sort_values("customer_id")
print(duplicate_right_keys)
Use cardinality validation:
result = left.merge(
right,
on="customer_id",
how="left",
validate="many_to_one",
)
11. Debug missing matches after a merge
Use indicator=True:
result = left.merge(
right,
on="customer_id",
how="left",
indicator=True,
)
Inspect unmatched rows:
unmatched = result.loc[
result["_merge"] == "left_only"
]
print(unmatched.head(50))
Then compare key dtypes:
print(left["customer_id"].dtype)
print(right["customer_id"].dtype)
Check formatting:
print(
left["customer_id"]
.astype("string")
.str.strip()
.head()
)
12. Debug duplicate rows
Check full-row duplicates:
print(df.duplicated().sum())
Check business-key duplicates:
key_cols = [
"customer_id",
"order_id",
]
duplicates = df.loc[
df.duplicated(
subset=key_cols,
keep=False,
)
].sort_values(key_cols)
print(duplicates)
Do not call drop_duplicates() before defining what the business key should be.
13. Debug inflated totals after a join
Before the join:
source_total = left["amount"].sum()
After:
result_total = result["amount"].sum()
print("Before:", source_total)
print("After:", result_total)
If the total increased, inspect:
- duplicate right-side keys
- wrong composite key
- many-to-many expansion
- duplicated left rows
Do not group the total back down until the row multiplication is understood.
14. Debug unexpected dtype changes after CSV ingestion
Inspect:
print(df.dtypes)
If an identifier became numeric, reload with an explicit dtype:
df = pd.read_csv(
"orders.csv",
dtype={
"customer_id": "string",
},
)
CSV has no embedded schema.
Inference is not a data contract.
15. Debug integer columns becoming floats
This often happens because missing values cannot be represented by NumPy integer dtype.
Use pandas nullable integers:
df["quantity"] = (
pd.to_numeric(
df["quantity"],
errors="coerce",
)
.astype("Int64")
)
Now missing values can coexist with integer semantics.
16. Debug object columns that should be numeric
Inspect Python value types:
print(
df["amount"]
.dropna()
.map(type)
.value_counts()
)
Then inspect unique bad values through conversion:
parsed = pd.to_numeric(
df["amount"],
errors="coerce",
)
bad = df.loc[
df["amount"].notna()
& parsed.isna(),
"amount",
]
print(
bad
.value_counts()
.head(20)
)
17. Debug ValueError: cannot convert float NaN to integer
Do not use:
df["quantity"].astype(int)
if nulls exist.
Use:
df["quantity"] = (
df["quantity"]
.astype("Int64")
)
or decide first whether missing values should be filled or rejected.
18. Debug SettingWithCopyWarning / copy-on-write confusion
Prefer explicit selection and assignment:
mask = df["status"].eq("active")
df.loc[
mask,
"flag",
] = True
For independent subsets:
active = df.loc[
mask
].copy()
Modern pandas increasingly uses copy-on-write semantics, but explicit .loc[] and .copy() still make intent clearer and avoid fragile code.
19. Debug groupby() totals that look wrong
Check input grain first.
print(
df.duplicated(
subset=["order_id"]
).sum()
)
Then compare row count and distinct count:
print("Rows:", len(df))
print(
"Unique orders:",
df["order_id"].nunique(),
)
If one order appears multiple times because of a prior join, a simple sum can double-count revenue.
20. Debug missing groups in groupby()
Null grouping keys are commonly the cause.
Compare:
summary_default = (
df.groupby("country")
.size()
)
with:
summary_with_nulls = (
df.groupby(
"country",
dropna=False,
)
.size()
)
If totals now reconcile, missing grouping keys were previously excluded.
21. Debug wrong “latest row” logic
Bad pattern:
latest = (
df.groupby("customer_id")
.max()
)
This can combine maxima from different rows.
Correct:
latest = (
df.sort_values(
[
"customer_id",
"event_time",
"event_id",
],
ascending=[
True,
False,
False,
],
)
.drop_duplicates(
subset=["customer_id"],
keep="first",
)
)
Define a deterministic tie-breaker.
22. Debug slow .apply()
Look for row-wise code such as:
df["result"] = df.apply(
expensive_function,
axis=1,
)
Try vectorized alternatives first.
Example:
df["result"] = (
df["amount"]
* df["quantity"]
)
For conditions:
df["is_large"] = (
df["amount"] > 1000
)
Use .apply() when the logic genuinely cannot be expressed vectorially.
23. Debug memory usage
Start with:
df.info(
memory_usage="deep",
)
Per column:
memory = (
df.memory_usage(
deep=True,
)
.sort_values(
ascending=False,
)
)
print(memory)
Look for:
- large object/string columns
- unused columns
- high-cardinality text
- duplicated intermediate DataFrames
24. Reduce memory before a large merge
Select only required columns:
left_small = left[
[
"customer_id",
"order_id",
"amount",
]
]
right_small = right[
[
"customer_id",
"segment",
]
]
Then merge:
result = left_small.merge(
right_small,
on="customer_id",
how="left",
validate="many_to_one",
)
Avoid carrying dozens of unused columns through expensive operations.
25. Debug unexpectedly slow sorting
If you only need top rows:
top_10 = df.nlargest(
10,
"revenue",
)
instead of:
top_10 = (
df.sort_values(
"revenue",
ascending=False,
)
.head(10)
)
For grouped top-N, sort only after filtering and selecting necessary columns.
26. Debug CSV rows shifting into wrong columns
Inspect the raw file and delimiter assumptions.
Try:
df = pd.read_csv(
"data.csv",
sep=",",
)
If the source uses another delimiter:
df = pd.read_csv(
"data.csv",
sep=";",
)
Also investigate:
- embedded delimiters
- quoting
- malformed rows
- encoding
Do not use permissive parsing to hide corrupt source records without measuring what was skipped or changed.
27. Debug malformed CSV lines
If you temporarily need to identify parsing issues, isolate the bad file rather than silently accepting data loss.
A diagnostic read may use:
df = pd.read_csv(
"data.csv",
on_bad_lines="warn",
)
This is useful during investigation.
For production, decide deliberately whether malformed records should:
- fail the load
- be quarantined
- be repaired upstream
Do not make skip the default just to keep a pipeline green.
28. Debug unexpected JSON shape
Inspect the raw structure before normalization.
print(type(payload))
If nested:
df = pd.json_normalize(
payload["results"]
)
If arrays expand to multiple rows, verify the intended grain.
For example:
one row per order
is different from:
one row per order item
29. Debug results by comparing invariants
Before a transformation:
before = {
"rows": len(df),
"orders": df["order_id"].nunique(),
"revenue": df["amount"].sum(),
}
After:
after = {
"rows": len(result),
"orders": result["order_id"].nunique(),
"revenue": result["amount"].sum(),
}
print("Before:", before)
print("After:", after)
Choose invariants based on the operation.
Examples:
- left enrichment join → row count and totals may need to stay the same
- deduplication → row count changes intentionally
- filtering → totals should decrease predictably
- aggregation → row count changes, but totals may reconcile
30. Build a reusable DataFrame diagnostic helper
def diagnose_df(
df: pd.DataFrame,
key_cols: list[str] | None = None,
) -> None:
print("Shape:", df.shape)
print("\nDtypes:")
print(df.dtypes)
print("\nNulls:")
print(
df.isna()
.sum()
.sort_values(
ascending=False,
)
.head(20)
)
print(
"\nDuplicate rows:",
df.duplicated().sum(),
)
if key_cols:
print(
"\nDuplicate keys:",
df.duplicated(
subset=key_cols
).sum(),
)
print("\nSample:")
print(df.head())
Use helpers like this for local troubleshooting.
For production pipelines, prefer structured logs and data-quality metrics.
What the result means
A pandas bug usually falls into one of these categories:
| Symptom | Likely area |
|---|---|
KeyError |
schema / column naming |
| conversion exception | incompatible raw values |
| new nulls | parse or join failure |
| extra rows after merge | wrong cardinality / duplicate keys |
| missing rows after merge | unmatched keys / inner join behavior |
| wrong totals | duplicate grain / aggregation / join expansion |
| wrong “latest” row | sort/tie-break logic |
object dtype |
mixed or loosely typed source data |
| chained-assignment warning | ambiguous mutation path |
| datetime comparison error | timezone or dtype mismatch |
| memory pressure | oversized frame/intermediate/object columns |
slow .apply() |
row-wise Python execution |
The important debugging pattern is:
inspect
→ isolate
→ compare before/after
→ validate assumptions
→ change one thing
→ re-check
What to look for
Row count changes unexpectedly
This is one of the strongest signals of a logic problem.
Check:
- merge cardinality
- filters
- duplicate keys
- explode/normalization
- deduplication
Null count increases unexpectedly
Check:
- failed conversions
- unmatched joins
- missing source columns
- parsing rules
Dtype changes unexpectedly
Check:
- CSV inference
- missing values
- concatenation
- merges
- conversion logic
Totals change after enrichment
A many-to-many or one-to-many expansion is likely.
Validate join keys before continuing.
Data looks correct in head() but metrics are wrong
head() is not representative.
Use:
- counts
- distinct counts
- null summaries
- distributions
- reconciliation totals
One workaround “fixes” the error but hides the reason
Examples:
errors="coerce"
drop_duplicates()
fillna(0)
on_bad_lines="skip"
These can be useful tools, but they should not become automatic error suppression.
Production notes
Debug with small, representative slices
For expensive workflows:
sample = df.sample(
min(10_000, len(df)),
random_state=42,
)
Use samples for iteration, then validate the final logic on full data.
Do not use a sample to prove uniqueness or complete data quality.
Track schema and row-count changes
Production pipelines should record at least:
- input row count
- output row count
- key distinct count
- important null counts
- important business totals
- schema/dtypes
This turns silent data drift into observable behavior.
Avoid mutating many things at once
Bad debugging pattern:
clean strings
convert types
drop nulls
merge
dedupe
aggregate
all in one chain.
During investigation, split transformations and validate after each stage.
Preserve bad records for analysis
When possible, quarantine invalid rows instead of discarding them.
Example:
valid = parsed.notna()
good_rows = df.loc[valid].copy()
bad_rows = df.loc[~valid].copy()
This preserves evidence.
Exceptions can be safer than silent coercion
For strict production contracts, allowing a pipeline to fail may be better than silently changing bad data.
Choose error handling based on the reliability requirement.
pandas is an in-memory tool
If the problem is mainly memory or scale, the solution may not be another pandas trick.
Consider:
- SQL
- DuckDB
- PyArrow
- Spark
- PySpark
- a database/data warehouse
depending on workload and architecture.
Common mistakes
1. Fixing symptoms instead of causes
Examples:
drop_duplicates()
fillna(0)
astype(str)
can hide deeper data problems.
Understand why the data is wrong first.
2. Debugging only the final DataFrame
Inspect intermediate stages.
The transformation that first changes row count, dtype, nulls, or grain is usually where the bug starts.
3. Trusting head()
A few rows cannot validate a dataset.
Use aggregate diagnostics.
4. Ignoring business grain
Many pandas bugs are really grain problems.
Define:
What should one row represent?
before merges, aggregations, or deduplication.
5. Ignoring dtypes
A string-vs-integer mismatch can break joins and filters without obvious visual differences.
6. Suppressing warnings blindly
Warnings often reveal fragile behavior.
Understand them before disabling them.
7. Using errors="coerce" without measuring impact
It can turn invalid values into missing values silently.
Track newly created nulls.
8. Dropping malformed input rows automatically
Skipping bad lines can make row counts and totals wrong while the pipeline still appears successful.
9. Debugging performance before correctness
First ensure the result is correct.
Then optimize.
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.