Missing data, in depth

The larger set

From here until the end of the course, the standard set is the funcionarios_100.csv: 104 rows, dirtier than the 15 from the Basic level (repeated ids, more missing, dates in two formats). A function combines the cleaning from chapters 10 and 11, and the last record of each id wins, because the repeated ones in the file are updates:

import numpy as np
import pandas as pd

pd.set_option("display.width", 170)
pd.set_option("display.max_columns", 20)

CANONICO = {"engenharia": "Engenharia", "marketing": "Marketing", "finanças": "Finanças",
            "ciência de dados": "Ciência de Dados", "rh": "RH"}


def carregar_100() -> pd.DataFrame:
    df = pd.read_csv("dados/funcionarios_100.csv")
    df["salario"] = pd.to_numeric(df["salario"].str.replace("R$ ", "", regex=False))
    df["departamento"] = df["departamento"].str.strip().str.lower().map(CANONICO)
    return df.drop_duplicates("id_funcionario", keep="last").reset_index(drop=True)


df = carregar_100()
print(df.shape)
print(df.isna().sum()[lambda s: s > 0].to_dict())

(100, 10)
{'departamento': 3, 'cidade': 5, 'idade': 4, 'salario': 7}

Fill by group

The median global treats an engineer and a person from HR as equals. But the salary depends on the department, and the median of the group itself is a much better estimate. The transform returns, for each row, the median of its department, and the fillna accepts this Series whole:

ausentes = df["salario"].isna()
global_ = df["salario"].fillna(df["salario"].median())
por_depto = df.groupby("departamento")["salario"].transform("median")
do_grupo = df["salario"].fillna(por_depto)

print(int(ausentes.sum()), round(float(global_[ausentes].mean())), round(float(do_grupo[ausentes].mean())))
print(int(do_grupo.isna().sum()))

7 9110 8911
0

The estimates differ: those without salary received, on average, different values by the two methods (the global median pulls everyone to the same number). In this set, no row remained missing (the 0), but that is luck of the set. Just one person without salary and without a department for the calculation to fail, because they do not belong to any group (the groupby discards the missing key, as we saw in chapter 13):

pequeno = pd.DataFrame({"depto": ["A", "A", "B", None], "salario": [10.0, np.nan, 20.0, np.nan]})
por_grupo = pequeno.groupby("depto")["salario"].transform("median")
print(pequeno["salario"].fillna(por_grupo).tolist())
print(pequeno["salario"].fillna(por_grupo).fillna(pequeno["salario"].median()).tolist())

[10.0, 10.0, 20.0, nan]
[10.0, 10.0, 20.0, 15.0]

The first result filled the A (with 10), but left the last one missing. The second step, with the global median as reserve, closes the gap. That's why the complete pattern always has two steps: the group and then the reserve.

Interpolate

In data with ) or the next one ( (measurements, prices over the days), the interpolate estimates the missing value by drawing a line between the neighbors, which is more faithful than repeating the previous value:

medicoes = pd.Series([10.0, np.nan, np.nan, 16.0, np.nan, 20.0])
print(medicoes.interpolate().tolist())
print(medicoes.ffill().tolist())
print(medicoes.interpolate(limit=1).tolist())

[10.0, 12.0, 14.0, 16.0, 18.0, 20.0]
[10.0, 10.0, 10.0, 16.0, 16.0, 20.0]
[10.0, 12.0, nan, 16.0, 18.0, 20.0]

The limit=1 prevents the estimate from crossing a long gap: filling in two consecutive days without data is acceptable, and filling in two weeks is inventing.

Discard with criteria

The dropna(thresh=n) keeps the lines with at least n filled values, which is finer than "any missing" and "all missing":

print(len(df), len(df.dropna()), len(df.dropna(thresh=9)), len(df.dropna(thresh=10)))
completas = df.notna().all(axis=1).mean()
print(round(float(completas), 2))

100 81 100 81
0.81

Types that hold the missing

As we saw in chapter 3, the NaN forces a column of integers to become decimal. The types nullable (Int64, Float64, boolean, string) have their own marker, the pd.NA, and maintain the type:

idade = df["idade"].astype("Int64")
print(idade.dtype, idade.isna().sum(), idade.head(3).tolist())
print(df[["idade", "salario"]].convert_dtypes().dtypes.to_dict())

Int64 4 [32, 37, 36]
{'idade': Int64Dtype(), 'salario': Int64Dtype()}

The convert_dtypes() chooses the most suitable nullable type for each column. The pd.NA has its own logic, stricter than that of NaN:

print(pd.NA + 1, pd.NA == 1, True | pd.NA, False & pd.NA)
try:
    bool(pd.NA)
except TypeError as erro:
    print("TypeError:", erro)

<NA> <NA> True False
TypeError: boolean value of NA is ambiguous

An account with pd.NA is NA. A comparison with it is NA. But True | NA is true, because the result does not depend on the unknown, and False & NA is false for the same reason. And using a NA as a condition of a if is an explicit error, instead of deciding alone: Pandas refuses to guess.