Missing data and dtypes

Your join looked perfect. Then you sorted by date and half the column refused to sort. Revenue summed fine until one blank cell turned the whole series into text. Someone filled missing regions with the word Unknown, someone else used a blank, and a third export used -999 because a legacy system hated nulls. Welcome to missing data and dtypes: the quiet wreckers of “simple” Python analytics.

This is Part 7 of Python for analytics. You already merged tables in Part 6. Now you make those tables honest about empty values and column types so filters, groupbys, and exports stop lying in polite ways.

What you’ll learn

How pandas represents missing values, and why empty strings are not the same as NaN
When to fill, when to drop, and when to leave nulls alone
Safe casting with to_numeric, to_datetime, and careful astype
Sentinel values like -999 and how to convert them on purpose
A symptom-to-fix table you can keep beside your notebook

Missing is a fact, not a personal failure

Operational data is incomplete. Forms skip fields. APIs omit keys. Spreadsheets store “N/A” as text. If you treat every null as a bug to delete, you will bias every rate you calculate. If you treat every null as zero, you will invent revenue and invent attendance. The adult move is to name the missingness, measure it, and choose a policy that matches the decision.

That is the same spirit as good-enough data in Analytics foundations: perfect completeness is not the goal. Documented handling is. Your spreadsheet habits from From spreadsheets to real data still apply. Blank cells that look empty may be spaces. Excel date serials may arrive as numbers. Python will not read your mind. It will read the bytes.

What “null” looks like in pandas

In modern pandas you will mostly see NaN (not a number) for floating missing values, NaT (not a time) for datetimes, and sometimes <NA> for pandas’ nullable dtypes. For analytics day-to-day, the practical rules are:

pd.isna(x) is true for missing values you should treat as missing
An empty string "" is not automatically NaN
The string "None" or "null" is also not NaN unless you map it
Numeric sentinels like -999 are real numbers until you replace them

Think of four buckets arriving from the wild: true nulls, empty strings, labeled placeholders ("Unknown"), and numeric sentinels. Your job is to map the ones that mean “no value” into a consistent missing marker before you model or plot.

Profile before you “fix”

Never start with dropna() as a reflex. Start with counts.

import pandas as pd
import numpy as np

df = pd.DataFrame(
    {
        "customer_id": [1, 2, 3, 4, 5],
        "email": ["a@x.com", "", "c@x.com", None, "e@x.com"],
        "region": ["East", "West", "Unknown", "East", None],
        "amount": ["40.5", "12", "n/a", "30", "-999"],
        "signup_date": ["2024-01-03", "2024/02/10", "", "not a date", "2024-05-01"],
    }
)

print(df.isna().sum())
print((df == "").sum())
print(df.dtypes)

Example output:

Example output: missing counts and coerce

You will often see email and signup_date as object (string-ish) columns. Missingness is split between None/NaN and "". The amount column is text because of "n/a". If you cast carelessly, you lose rows or invent zeros. Profiling first keeps you honest.

Empty string versus NaN

Filters behave differently. df[df["email"].isna()] will not catch "". Groupby on region will treat "Unknown" as a real category and NaN as missing (often excluded from groups depending on settings). Standardize early:

# Strip whitespace, then turn pure blanks into true missing
df["email"] = df["email"].replace(r"^\s*$", pd.NA, regex=True)

# Map explicit placeholders used by your systems
df["region"] = df["region"].replace(
    {
        "Unknown": pd.NA,
        "N/A": pd.NA,
        "n/a": pd.NA,
        "null": pd.NA,
    }
)

print(df[["email", "region"]])
print(df[["email", "region"]].isna().sum())

Write down the placeholder list for your company. Marketing might use "TBD". Finance might use "#N/A" from spreadsheet exports. One shared mapping function beats five notebooks with five opinions.

Fill carefully. Drop carefully. Leave carefully.

Three legitimate policies:

Leave null: best default for optional attributes (middle name, secondary phone). Charts and rates should account for “known vs unknown.”
Fill: only when the business rule is clear (missing discount means 0, missing boolean flag means False after product confirms it).
Drop rows or columns: when the field is required for the question (cannot compute conversion without a signup timestamp) and the miss rate is small enough not to bias the answer.

# Example policies (illustrative, not universal laws)
df["discount_rate"] = df.get("discount_rate", 0)  # if column exists elsewhere
# Prefer explicit fill after you create/clean the column:
# df["discount_rate"] = df["discount_rate"].fillna(0)

# Drop rows only when the analysis requires the field
need_email = df.dropna(subset=["email"])
print("kept", len(need_email), "of", len(df))

# Drop a column that is 95% empty and unused in the question
# df = df.drop(columns=["legacy_field"])

Always log how many rows you drop. “I cleaned the file” is not a method. “Dropped 12 of 10,400 rows missing signup_date (0.12%)” is a method. If you drop 30% of rows because dates failed to parse, you do not have a cleaning step. You have a data quality incident.

Cast numbers safely

astype(float) is a blunt instrument. One bad token and the whole cast fails. Prefer pd.to_numeric with errors="coerce", which turns unparseable values into NaN so you can count them.

df["amount_raw"] = df["amount"]

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

# Legacy sentinel: -999 means missing, not a refund of nine hundred ninety-nine
df.loc[df["amount"] == -999, "amount"] = pd.NA

bad_amount = df["amount"].isna().sum()
print("amount nulls after cast", bad_amount)
print(df[["amount_raw", "amount"]])

Example:

coerce and blank cleanup

errors="coerce" is not free. It will also turn typos into nulls. That is usually what you want for analytics, as long as you inspect the null pile. For strict pipelines where a bad value should fail the job, use errors="raise" in validation environments.

Cast dates safely

Dates are the other classic trap. Mixed formats, blank strings, and words like "pending" show up in the same column. Use pd.to_datetime with coercion, then measure the damage.

df["signup_date"] = pd.to_datetime(
    df["signup_date"],
    errors="coerce",
    format="mixed",  # pandas 2.x helper for mixed ISO-ish inputs
)

print(df["signup_date"])
print("unparsed dates", df["signup_date"].isna().sum())

Example:

Datetime coerce

If your pandas version is older and format="mixed" is unavailable, parse without a format and still use errors="coerce", then spot-check. For warehouse-friendly exports later (Part 9), prefer timezone-aware policy agreed with your team, or at least ISO dates at day grain when time-of-day does not matter.

Never use astype("datetime64[ns]") on dirty strings as your first move. Get to a datetime via to_datetime, then, if you need a specific dtype, cast the clean result.

Worked mini cleanup

Put the pieces together on the toy frame so you see a full pass top to bottom.

def clean_customers(df: pd.DataFrame) -> pd.DataFrame:
    out = df.copy()

    # 1) Normalize text nulls
    for col in ["email", "region"]:
        out[col] = out[col].replace(r"^\s*$", pd.NA, regex=True)
    out["region"] = out["region"].replace({"Unknown": pd.NA, "N/A": pd.NA})

    # 2) Numbers
    out["amount"] = pd.to_numeric(out["amount"], errors="coerce")
    out.loc[out["amount"] == -999, "amount"] = pd.NA

    # 3) Dates
    out["signup_date"] = pd.to_datetime(out["signup_date"], errors="coerce", format="mixed")

    # 4) Report
    report = {
        "rows": len(out),
        "email_nulls": int(out["email"].isna().sum()),
        "region_nulls": int(out["region"].isna().sum()),
        "amount_nulls": int(out["amount"].isna().sum()),
        "signup_nulls": int(out["signup_date"].isna().sum()),
        "dtypes": out.dtypes.astype(str).to_dict(),
    }
    print(report)
    return out

clean = clean_customers(df)
print(clean)

Notice the function returns a new frame and prints a small report. That habit feeds Part 8’s pipeline mindset: small steps, visible checks, no mystery cells.

Symptom to fix

Symptom	Likely cause	Fix to try
Sum fails or concatenation happens	Numbers stored as text	`pd.to_numeric(..., errors="coerce")`
Sort order looks alphabetical for dates	Dates still strings	`pd.to_datetime(..., errors="coerce")`
Filter for missing misses blanks	Empty string vs NaN	Replace `""` with `pd.NA`
Join matches nobody	dtype mismatch on key (int vs str)	Cast both keys the same way
Mean looks impossibly low	Sentinel like -999 included	Map sentinel to NA before agg
Groupby grows an “nan” bucket of strings	Literal `"nan"` text	Replace string placeholders
Half the rows vanish after dropna	Over-broad drop	`subset=[...]` only required columns

A workplace story: the discount that was not zero

Ops exports weekly orders. The discount column is blank for most rows because the POS only writes a value when a promo fires. An eager analyst fills null discounts with 0, computes average discount rate, and reports “almost no discounting this quarter.” Leadership freezes a campaign. Two days later someone notices that blank also means “promo engine offline for half the stores,” not “full price.” The fill was mathematically tidy and economically wrong.

The better path would have been three columns or three statuses in the handoff note: known zero discount, known positive discount, and unknown. Unknown stays null. Rates are computed on known rows, with a coverage percentage printed next to the metric: “average discount 4.2% on 61% of orders where discount status is known; 39% unknown due to store sync gaps.” That sentence is longer than a single number. It is also honest enough to keep a campaign from dying on a dtype convenience.

Use the same discipline for dates. A null ship date is not “shipped on day zero.” A null churn date is not “still active” unless your product definition says so. Write the business translation of null in one line before you fill, drop, or plot.

Common mistakes

Filling with zero by default. Zero is a real number. Missing is not a sale of zero dollars unless the business says so.
Dropping first, asking questions never. Measure miss rates and whether missingness correlates with segment or channel.
Using astype on dirty columns. Prefer coerce-and-count patterns for inbound mess.
Silently coercing and ignoring the null pile. Coercion without a report hides bad source data.
Comparing with == None. Use isna() for missing tests in pandas.
Forgetting that boolean columns may arrive as "true"/"false" text. Map deliberately before astype("boolean").

Practice and next step

Export a small CSV from a tool you use weekly. Profile nulls, blanks, and dtypes. Write a ten-line cleaning function that only does three things: normalize blanks, coerce one numeric column, coerce one date column. Print a before/after null report. That is enough practice to make Part 8 feel natural.

Part 8 chains these steps into a light cleaning pipeline you can rerun top to bottom. If you still think in SQL for quality checks, the SQL series pairs well with the same “profile then fix” mindset. Browse more paths on the Learn hub.

Quick recap

Empty strings, placeholders, sentinels, and true nulls are different; unify them on purpose.
Profile with isna, blank counts, and dtypes before fill or drop.
Use to_numeric and to_datetime with errors="coerce", then measure what became null.
Fill only with business rules; drop with subset and row-loss reporting.
Clean dtypes early so merges and groupbys in this series behave.

Sources

pandas working with missing data: https://pandas.pydata.org/docs/user_guide/missing_data.html
pandas to_numeric: https://pandas.pydata.org/docs/reference/api/pandas.to_numeric.html
pandas to_datetime: https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html
Python docs, built-in types overview: https://docs.python.org/3/library/stdtypes.html
Analytics Made Simple, Learn hub: https://analyticsmadesimple.com/learn/