skip to content

Missing Data

pandas marks missing values as NaN, NaT, None or pd.NA by dtype, and fills or drops them with fillna, ffill, interpolate and dropna. Interviewers probe both halves.

on this pageshow

explore

questions

10

In pandas, how do you count the missing values in each DataFrame column, and how do dropna() and fillna(0) treat them differently?

level: juniorimportance: must knowfreq 80%

answer

  1. a boolean frame, then a reduction
  2. True counts as one
  3. sum for counts, mean for share
  4. remove rows versus write a value

basics

~10 s

df.isna().sum() counts the missing cells in each column and df.isna().mean() gives their share. dropna() removes every row holding any missing value, while fillna(0) keeps every row and writes 0 into each gap.

solid answer

~40 s

`df.isna()` returns a boolean DataFrame of the same shape, `True` where a cell is missing, so `df.isna().sum()` is a Series of gap counts per column and `df.isna().mean()` is the fraction missing per column; `df.count()` gives the complement, the non-missing count. `notna()` is the inverse mask, and `df[df.isna().any(axis=1)]` pulls out the rows with at least one gap. The two first-line fixes change different things: `dropna()` with its defaults (`axis=0`, `how='any'`) returns a shorter frame without any row that has a gap, keeping the original index labels, while `fillna(0)` returns a frame of the same shape with every gap replaced by 0. Neither touches the original unless you reassign the result.

code

python · 19 lines
python
import numpy as np
import pandas as pd

readings = pd.DataFrame(
    {
        "sensor": ["s1", "s1", "s2", "s2", "s3"],
        "temp_c": [21.4, np.nan, 19.8, np.nan, 22.1],
        "humidity": [40.0, 41.0, np.nan, np.nan, 38.0],
    }
)

print(readings.isna().sum())          # gaps per column
print(readings.isna().mean())         # share of gaps per column
print(readings[readings.isna().any(axis=1)].index.tolist())

dropped = readings.dropna()           # rows with any gap are removed
filled = readings.fillna(0)           # every gap becomes 0
print(dropped.shape, filled.shape)
print(filled.dtypes)

go deeper

for a junior

Recall the two reductions, isna().sum() for counts and isna().mean() for shares, and say plainly that dropna() removes rows while fillna(0) keeps them and writes zeros.

for a middle

Explain why a boolean mask sums to a count, that dropna keeps the original index labels, and how one mostly empty column can make the default dropna return no rows.

for a senior

Show the habit of measuring before and after a cleaning step, comparing row counts and per-column shares, so a silent mass deletion or a column filled from near-empty is caught in review.

for a principal

Treat gap counts as a data-quality signal the team tracks over time, so a jump in a column's missing share is noticed upstream before any fill or drop hides it.

## What pandas reports as missing pandas does not have a single missing value. A float column holds `NaN`, a datetime column holds `NaT`, an object column can hold `None`, and the nullable extension dtypes hold `pd.NA`. Which marker lands in which dtype is a subject of its own, covered in the lesson on NA-aware dtypes. For counting, what matters is that **`isna()`** and its inverse **`notna()`** recognise all of them, so you never test a column with `== np.nan` (a comparison with `NaN` is always `False`). - `df.isna()` returns a **boolean DataFrame** with the same index and columns as `df`. - `df.notna()` returns the opposite mask: `True` where a value is present. - `pd.isna(obj)` is the function form and works on a scalar, a Series or a DataFrame. ## Counting gaps per column Because a boolean column sums as integers (`True` is 1, `False` is 0), a reduction over the mask turns it into counts: - `df.isna().sum()` is a **Series indexed by column name** holding the number of missing cells in each column. - `df.isna().mean()` is the same Series divided by the row count: the **share** of each column that is missing, which is easier to compare across tables of different lengths. - `df.isna().sum().sum()` collapses everything into one integer, the total number of missing cells. - `df.count()` counts the opposite, the **non-missing** cells per column, so `len(df) - df.count()` equals `df.isna().sum()`. - `df.isna().any(axis=1)` is a boolean Series over rows, `True` for each row with at least one gap; `df[df.isna().any(axis=1)]` shows those rows so you can look at them before deciding anything. The same calls work on a single column: `df['temp_c'].isna().sum()` returns one integer rather than a Series, and `df['temp_c'].isna().mean()` one float. A common screening follow-up is to rank columns by how incomplete they are, which is `df.isna().mean().sort_values(ascending=False)`, a one-line view of where the data is thinnest. `df.info()` prints the non-null count for every column alongside its dtype, which is a quick first look, but the `isna()` reductions return objects you can sort, filter and assert on in code. ## dropna: removing what has gaps `DataFrame.dropna()` removes labels that contain missing values. Its defaults are `axis=0` (remove **rows**) and `how='any'` (a row goes if **any** of its cells is missing). It returns a new DataFrame and leaves `df` as it was. Three consequences surprise people: 1. The surviving rows keep their **original index labels**, so the index has holes; pass `ignore_index=True` or call `reset_index(drop=True)` if you need 0 to n-1. 2. One sparse column can empty the whole result: if a `notes` column is missing everywhere, `dropna()` returns zero rows. 3. The values in the other columns of a dropped row go too, even though they were present. The arguments that narrow this (`how='all'`, `thresh`, `subset`, `axis='columns'`) are what an interviewer asks about next, and they deserve their own treatment. ## fillna(0): writing a value into every gap `DataFrame.fillna(0)` returns a frame of the **same shape** in which every missing cell holds 0. Nothing is removed, so row counts and the index are preserved. The column keeps its dtype when 0 fits it (a float column stays `float64` and shows `0.0`). The cost is that 0 now looks like a real measurement: a sum is unchanged but a mean, a minimum or a count of non-missing values can change. Whether a zero is an honest stand-in for absence is a data decision, discussed in the library-neutral material on missing values; what pandas guarantees is only that every gap is now filled. When different columns need different values, `fillna` also accepts a per-column dict, which is the safer form on a frame that mixes numbers and text. ## Side by side | Call | Rows in result | Shape of result | What happens to a gap | |---|---|---|---| | `df.isna().sum()` | one entry per column | Series of integers | counted, not changed | | `df.isna().mean()` | one entry per column | Series of floats between 0 and 1 | measured as a share | | `df.dropna()` | only rows with no gap | DataFrame, same columns | its whole row is removed | | `df.fillna(0)` | all rows | DataFrame, same shape | replaced by 0 | ## A habit worth showing in an interview Count first, then act: print `df.isna().sum()` and `df.isna().mean()` before and after the cleaning step, and compare `len(df)` before and after any `dropna()`. A cleaning step that silently drops 40 percent of the rows, or fills a column that was 90 percent empty, is visible in those few numbers and invisible in `df.head()`.

  • How would you list only the columns where more than 20 percent of the values are missing?
    Build the share once and filter the resulting Series: `share = df.isna().mean()` then `share[share > 0.2].index.tolist()`. Because `isna().mean()` is indexed by column name, the filter returns column labels directly, and sorting it with `sort_values(ascending=False)` puts the worst columns first.
  • After df.dropna(), why might df.loc[3] raise a KeyError even though the frame still has more than three rows?
    `dropna()` keeps the surviving rows' original index labels. If the row labelled 3 had a gap it is gone, and `loc` looks up labels, not positions, so the label no longer exists. Use `iloc[3]` for the fourth row, or pass `ignore_index=True` to `dropna()` to relabel the result 0 to n-1.
  • Does df.fillna(0) change df itself?
    No. `fillna` returns a new object and leaves `df` unchanged, so the result has to be assigned, as in `df = df.fillna(0)`. An `inplace=True` form also exists, but reassigning the result is the clearer, idiomatic form.

  • Testing for gaps with df == np.nan, which is never True.
  • Believing df.isna().sum() returns one total for the whole frame.
  • Assuming dropna() only removes rows where every value is missing.
  • Thinking fillna(0) changes df without assigning the result.
  • Expecting dropna() to renumber the index from zero automatically.
open as a page

In pandas 3.0, which missing marker (NaN, NaT, None or pd.NA) does each dtype hold, and why test for them with isna rather than ==?

level: juniorimportance: must knowfreq 62%

basics

~20 s

NumPy float64 and the pandas 3.0 str dtype hold NaN, datetime64 and timedelta64 hold NaT, object holds what was stored (often None), and Int64, Float64, boolean and string hold pd.NA. pd.isna detects all four; == finds none.

open as a page

A pandas Series of minute-level sensor temperatures has three-minute dropouts and one three-hour outage; what does ffill(limit=5) do to each?

level: middleimportance: must knowfreq 60%

basics

~20 s

ffill copies the last valid reading forward, so each three-minute dropout is filled completely. limit=5 caps each gap at five filled rows, so the outage gets five copies at its start and its remaining rows stay NaN.

open as a page

Why does a pandas int64 column of clinic visit counts turn float64 once a value goes missing, and how does Int64 keep it integer?

level: middleimportance: must knowfreq 68%

basics

~20 s

NumPy's int64 has no way to store a missing value, so pandas writes the gap as NaN and upcasts the whole column to float64. The nullable Int64 dtype keeps int64 values plus a separate mask and shows the gap as pd.NA.

open as a page

In pandas, how do DataFrame.dropna's how, thresh, subset and axis arguments decide which rows or columns are removed?

level: middleimportance: should knowfreq 55%

basics

~20 s

dropna removes rows by default when any cell is missing. how='all' drops only fully empty rows, thresh=n keeps rows with at least n non-missing values, subset limits which columns are checked, and axis='columns' drops columns instead.

open as a page

In pandas 3.0, what can DataFrame.fillna take as its value, and why fill a mixed-type frame with a per-column dict?

level: middleimportance: should knowfreq 50%

basics

~20 s

fillna takes a scalar, a dict or Series keyed by column, or an aligned DataFrame. A dict fills only the columns it names, so a text column never receives a numeric 0, which in pandas 3.0 turns a str column into object.

open as a page

In pandas, why does sum() over an all-NaN column return 0 while mean() returns NaN, and what do skipna and min_count change?

level: middleimportance: should knowfreq 45%

basics

~20 s

Reductions skip missing values by default (skipna=True), so an all-NaN column is summed as an empty sum, 0, while its mean has no values to divide and is NaN. sum(min_count=1) returns NaN instead; skipna=False makes any gap produce NaN.

open as a page

In pandas, why does interpolate() on sensor readings with an irregular DatetimeIndex give different values from interpolate(method='time'), and what do limit, limit_direction and limit_area control?

level: seniorimportance: should knowfreq 40%

basics

~20 s

The default method='linear' ignores the index and treats rows as equally spaced; method='time' weights estimates by elapsed time on a DatetimeIndex. limit caps consecutive fills, limit_direction picks which ends fill, and limit_area restricts filling to inside or outside the readings.

open as a page

A pandas boolean consent column holds pd.NA for unknown patients; why do those rows appear in neither df[df['consent']] nor df[~df['consent']]?

level: seniorimportance: should knowfreq 34%

basics

~20 s

In the nullable boolean dtype, ~ maps pd.NA to pd.NA, and indexing with a boolean-dtype mask treats pd.NA as False. Unknown-consent rows therefore drop out of both selections; route them explicitly with isna() or by filling the mask.

open as a page

In pandas 3.0, why does 0/0 in a Float64 column now count as missing, when pandas 2.x kept a NaN that isna() ignored?

level: seniorimportance: nice to knowfreq 24%

basics

~20 s

In pandas 3.0 the nullable Float64 and pyarrow float dtypes treat NaN as pd.NA everywhere, so a NaN produced by arithmetic is stored as pd.NA and isna() counts it. pandas 2.x kept such a NaN as an ordinary, non-missing value.

open as a page