Browse cheat sheets · Python

Technology: PythonCategory: TroubleshootingDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Debug KeyError for a missing column
  5. 2. Debug wrong capitalization or renamed columns
  6. 3. Debug numeric conversion failures
  7. 4. Debug unexpected nulls after conversion
  8. 5. Debug datetime parsing failures
  9. 6. Debug timezone-aware vs timezone-naive comparison errors
  10. 7. Debug filters returning no rows
  11. 8. Debug boolean filter errors
  12. 9. Debug chained-assignment problems
  13. 10. Debug unexpected row growth after a merge
  14. 11. Debug missing matches after a merge
  15. 12. Debug duplicate rows
  16. 13. Debug inflated totals after a join
  17. 14. Debug unexpected dtype changes after CSV ingestion
  18. 15. Debug integer columns becoming floats
  19. 16. Debug object columns that should be numeric
  20. 17. Debug ValueError: cannot convert float NaN to integer
  21. 18. Debug SettingWithCopyWarning / copy-on-write confusion
  22. 19. Debug groupby() totals that look wrong
  23. 20. Debug missing groups in groupby()
  24. 21. Debug wrong “latest row” logic
  25. 22. Debug slow .apply()
  26. 23. Debug memory usage
  27. 24. Reduce memory before a large merge
  28. 25. Debug unexpectedly slow sorting
  29. 26. Debug CSV rows shifting into wrong columns
  30. 27. Debug malformed CSV lines
  31. 28. Debug unexpected JSON shape
  32. 29. Debug results by comparing invariants
  33. 30. Build a reusable DataFrame diagnostic helper
  34. What the result means
  35. What to look for
  36. Row count changes unexpectedly
  37. Null count increases unexpectedly
  38. Dtype changes unexpectedly
  39. Totals change after enrichment
  40. Data looks correct in head() but metrics are wrong
  41. One workaround “fixes” the error but hides the reason
  42. Production notes
  43. Debug with small, representative slices
  44. Track schema and row-count changes
  45. Avoid mutating many things at once
  46. Preserve bad records for analysis
  47. Exceptions can be safer than silent coercion
  48. pandas is an in-memory tool
  49. Common mistakes
  50. 1. Fixing symptoms instead of causes
  51. 2. Debugging only the final DataFrame
  52. 3. Trusting head()
  53. 4. Ignoring business grain
  54. 5. Ignoring dtypes
  55. 6. Suppressing warnings blindly
  56. 7. Using errors="coerce" without measuring impact
  57. 8. Dropping malformed input rows automatically
  58. 9. Debugging performance before correctness
  59. Next step
  60. 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:

  • KeyError
  • ValueError during conversion
  • merge/join failures
  • unexpected duplicate rows
  • unexpected nulls
  • chained-assignment warnings
  • datetime comparison errors
  • memory errors
  • object dtype 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.

Next step

Use the symptom to choose the next path.

  • Missing column / KeyError → inspect exact schema and column naming.
  • Conversion failure → isolate invalid raw values before casting.
  • New nulls after parsing → inspect failed conversions.
  • Rows multiply after merge → validate join cardinality and duplicate keys.
  • Rows disappear after merge → inspect unmatched keys with indicator=True.
  • Totals inflate → check grain and one-to-many/many-to-many expansion.
  • Wrong latest/top row → define complete deterministic sort and tie-break logic.
  • Datetime comparison fails → normalize dtype and timezone semantics.
  • Memory pressure → project fewer columns, filter earlier, and reduce intermediates.
  • Correctness is proven but performance is poor → profile the expensive operation and consider a larger execution engine.

← All cheat sheets