F3.5 · LESSON 5 OF 6

Tables and missing values

Use array shapes, broadcasting and reductions to express a small data transformation.

THE IDEA

A table with names and types

A NumPy array has one type and no labels. A pandas DataFrame is a table whose columns have names and can have different types: text in one column, whole numbers in another, decimals in a third. Real data is also incomplete. pandas marks a missing number as NaN ("not a number"), and most column operations skip it.

Missing is a value of its own

>>> import numpy as np, pandas as pd
>>> df["salary"]          # Ana 52, Ben missing, Cy 61, Dee 48, Eli missing, Fay 75
>>> df["salary"].isna()   # True where missing
>>> np.nan == np.nan
False                      # NaN is not equal to anything, even itself: use isna(), never ==

skipna=True (the default for .mean())

name	age	salary (k)
Ana	34	52
Ben	29	NaN
Cy	NaN	61
Dee	41	48
Eli	38	NaN
Fay	NaN	75

d = df len(d) = 6 d["age"].mean() = 35.5 d["salary"].mean() = 59

Red cells are missing. Blue cells were filled in. Struck-out rows were dropped. Compare the salary average across the options: only some of them answer the question you asked.

Red cells are missing. Pick how to treat them and compare the row count and the two averages. Switch skipna off to see how one NaN poisons a mean.

Missing is not zero, and dropping is a choice

Averaging the four observed salaries gives 59. Filling the missing ones with 0 first gives 39.33: the two people who did not report a salary are now counted as earning nothing. Dropping every row with any missing cell leaves 2 of 6 rows, so you answered the question about a different, smaller group.

Predict first. You run df.dropna() on the table above. How many rows are left?

A common safer pattern

Fill, but remember what was invented

out = df.copy()                                  # never edit the caller's frame
out["salary_was_missing"] = out["salary"].isna() # record before filling
out["salary"] = out["salary"].fillna(out["salary"].median())

The flag column lets a later step (or a model) tell observed values from guessed ones.

Chapter context

A tensor is a multidimensional array with a shape and element type. Many AI bugs come from doing a valid operation along the wrong axis. Write the intended dimensions before trying to shorten the code.

TRY IT YOURSELF

missing_counts(df) returns a dict mapping each column name to the number of missing cells, as plain ints.
mean_observed(df, col) is the mean of the observed values. fill_median(df, col) returns a NEW frame with missing values replaced by the median and a boolean column named col + "_was_missing".
drop_incomplete(df, cols) keeps rows where all cols are observed and resets the index. share_dropped(df, cols) is the fraction of rows removed (0.0 for an empty frame). pandas may print a harmless Pyarrow notice when it is imported.

import numpy as np
import pandas as pd

def missing_counts(df):
    pass

def mean_observed(df, col):
    pass

def fill_median(df, col):
    # new DataFrame; the original must not change
    pass

def drop_incomplete(df, cols):
    pass

def share_dropped(df, cols):
    pass

df = pd.DataFrame({"name": ["Ana", "Ben", "Cy"], "salary": [52.0, np.nan, 61.0]})
print(missing_counts(df))

Ctrl/⌘ + Enter runs it

Stuck, or something not working? You can come back to it any time.

CHECK YOUR UNDERSTANDING

Why is a missing salary not the same as a zero salary?

AZero is an observed amount; missing means the amount is unknown.BZero is a rounded value, while missing means the value was larger than the table can store.CThey are the same, because pandas converts every missing value to zero before computing.

Chapter reference: math, worked example & code

This section belongs to the whole chapter, so it is the same on every lesson in it and may cover a different lesson's topic. The code for the experiment you just ran is inside the experiment itself.

Math and notation

For a data matrix $X\in\mathbb R^{n\times d}$ , feature means are

\mu_j=\frac1n\sum_{i=1}^n X_{ij},\qquad Z_{ij}=X_{ij}-\mu_j.

Here i indexes examples and j indexes features. The mean vector has d entries, so subtracting it from X broadcasts along rows.

Worked example

For X = [[1, 2], [3, 4]], the feature means are [2, 3]. Centering produces [[-1, -1], [1, 1]]. Each centered column has mean zero. Computing row means instead would answer a different question even though the operation is valid.

Reference code

The following example uses the Python standard library and does not call a remote service.

X = [[1, 2], [3, 4]]
means = [sum(row[j] for row in X) / len(X) for j in range(2)]
centered = [[x - m for x, m in zip(row, means)] for row in X]
assert means == [2, 3]
assert centered == [[-1, -1], [1, 1]]

Python examples are provided for study and local execution; this page does not execute Python.

Common mistakes

Broadcasting is a shape rule, not permission to ignore axis meanings. Vectorized code can create large intermediate arrays. Normalize training and test data with training statistics only.