Not every missing value is a null. In survey, clinical, and administrative data, a missing value is often recorded as a code, such as -99 for “not asked” or -98 for “the respondent refused”. If you treat these codes as ordinary numbers, they will distort any average or sum you compute. If you recognize them for what they are, they tell you something useful, because the reason a value is missing can matter as much as the fact that it is missing.
This chapter shows how to validate coded missing values. In Pointblank, you describe a column’s missing-value codes once, in a MissingSpec, and then you can ask two kinds of questions. The first is how much of the column is missing, counting plain nulls, codes, or both. The col_pct_null() and col_pct_missing() methods handle that. The second is whether the codes themselves are used correctly, which is what col_missing_coded(), col_missing_only_coded(), and col_missing_consistent() check.
We use a small survey table throughout. It has ten respondents and a mix of real values, documented missing codes, one plain null, and one undocumented code, so each check has something to find.
survey = pl.DataFrame({
"respondent_id": list(range(1, 11)),
"age": [34, -98, 41, None, 29, -99, 55, 38, -97, 45],
"score": [10, 20, -95, 30, -99, 40, 50, 60, 70, 80],
"income_source": [1200, -99, 3400, -99, 2100, 5000, -99, 1800, 2600, 3100],
"income_amount": [1200, -99, 3400, 900, 2100, 5000, -99, 1800, 2600, 3100],
})
pb.preview(survey, n_head=10, n_tail=0)PolarsRows10Columns5 |
|||||
The age column has real ages alongside the codes -98, -99, and -97, plus one plain null for respondent 4. The score column has a -95 that is not a documented code. We will use the two income columns to check a skip pattern later on. First, though, we need to tell Pointblank what the codes mean.
A null only tells you that a value is absent. A missing-value code tells you why it is absent, and in many fields that difference matters. An income left blank because the question was never asked is not the same as an income left blank because the respondent refused to answer, and an analysis that treats them the same throws away information that someone deliberately recorded. Statistical software has supported this idea for a long time. SAS, SPSS, and Stata each have their own conventions for special missing values, and clinical data standards define a detailed set of “null flavors”.
SAS reserves a set of special missing values: a plain ., plus ._ and .A through .Z, each of which can stand for a different reason. SPSS lets a variable declare specific values, often negative numbers like -9, as user-missing, separate from its system-missing blank. Stata has .a through .z as extended missing values in addition to plain .. Clinical data goes furthest, with the CDISC and HL7 null flavors naming reasons such as “not asked” or “unknown”. Each of these schemes keeps the reason a value is missing in the data, and a MissingSpec can represent any of them.
The trouble is that these codes are usually stored as ordinary numbers. A -99 in an age column will pull an average down, and a range check that expects ages from 0 to 120 will flag it as an error rather than recognizing it as a documented non-response. To validate coded missing values properly, you first tell Pointblank which values are codes, so it can treat them separately from the real data.
MissingSpecA MissingSpec describes a column’s missing-value codes. The main part is the reasons= mapping, which pairs each code with the reason it stands for. You can also group reasons into categories=, and the null_is_missing= flag controls whether a plain null counts as missing along with the codes (it does by default). The spec for our survey lists three codes and groups two of them.
spec = pb.MissingSpec(
reasons={-99: "not_asked", -98: "refused", -97: "dont_know"},
categories={"item_nonresponse": ["refused", "dont_know"]},
)This says that -99, -98, and -97 are documented missing codes with the given meanings, and that “refused” and “dont_know” together form a category called item_nonresponse. Survey researchers often care about this grouping, because both reasons mean the respondent was asked but chose not to answer, which is different from never being asked.
With a spec in hand, we can go back to the range check problem from the previous section. Most of the col_vals_* methods from Chapter 6 accept a missing= argument, and passing the spec tells the check to set the codes aside. Here is an age range check on the survey, first without the spec and then with it.
(
pb.Validate(data=survey)
.col_vals_between(columns="age", left=0, right=120)
.col_vals_between(columns="age", left=0, right=120, missing=spec)
.interrogate()
)Without the spec, the first step has six passes and four failures: the three codes, which fall outside the range, and the null. With missing=spec, all ten rows pass. The codes are recognized as documented missing values, and so is the null, since null_is_missing= defaults to True. The range check can now focus on the real ages, and the rest of this chapter covers how to check the missing values themselves.
Since some fields follow well-known standards for missing-value codes, Pointblank can also build a spec for you. The MissingSpec.from_cdisc() constructor produces the HL7 and CDISC null flavors, and MissingSpec.from_sas() and MissingSpec.from_spss() produce the conventions of those statistical packages. For example, if a file exported from SPSS declares certain negative numbers as user-missing, from_spss() turns that declaration into a spec.
spss_spec = pb.MissingSpec.from_spss(missing_values=[-1, -2, -3])
spss_spec.reasons{-1: 'missing_1', -2: 'missing_2', -3: 'missing_3'}Each declared value gets a generated reason label ("missing_1", "missing_2", and "missing_3"). The from_sas() constructor works the same way for the SAS special missing values, mapping . and ._ to "system_missing" and .A through .Z to "user_missing_a" through "user_missing_z". The CDISC version is covered in Chapter 26. For the rest of this chapter we use our own spec, since the survey uses its own codes rather than a package convention.
The first question about a column is usually how much of it is missing. Once codes are involved, there are two ways to count. One is the plain null rate, which ignores codes. The other counts codes and nulls together. Pointblank has a method for each, and both produce a single test unit for the whole column.
col_pct_nullThe col_pct_null() method checks the proportion of nulls against an expected value p=, with an optional tol= for a tolerance. It ignores the codes completely and counts only nulls. The age column has one null out of ten rows, a rate of 0.1.
(
pb.Validate(data=survey)
.col_pct_null(columns="age", p=0.1)
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C | 1 |
col_pct_null()
|
✓ | 1 | 1 1.00 |
0 0.00 |
— | — | — | — |
The single test unit passes, because the null rate is exactly 0.1. Checking for a specific null rate might seem odd, since we usually want fewer nulls, not a particular number. For an optional field, though, a stable rate is often what you want to confirm. If about 10 percent of respondents normally skip a question and suddenly 80 percent do, something has changed in how the data is collected. If you want no nulls at all, use p=0.0. That check would fail here, since the column has one null.
An exact rate is usually too strict, because a real column changes a little from run to run without anything being wrong. The tol= argument turns the expected value into a range, and any rate within tol of p passes. With a tolerance of 0.05 around 0.1, the check passes easily.
(
pb.Validate(data=survey)
.col_pct_null(columns="age", p=0.1, tol=0.05)
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C | 1 |
col_pct_null()
|
✓ | 1 | 1 1.00 |
0 0.00 |
— | — | — | — |
The check passes, and it would keep passing as long as the null rate stayed between 0.05 and 0.15. If we instead expected a rate of 0.2 with the same tolerance, using col_pct_null(columns="age", p=0.2, tol=0.05), the check would fail, because 0.1 is below the lower end of the range at 0.15.
col_pct_missingThe col_pct_missing() method counts both codes and nulls. Given a MissingSpec, it counts the coded values and, because null_is_missing= is True by default, the nulls as well. It then checks that the combined rate is at or below max_pct=. The age column has three codes and one null, so four out of ten values are missing.
(
pb.Validate(data=survey)
.col_pct_missing(columns="age", missing=spec, max_pct=0.5)
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C | 1 |
col_pct_missing()
|
✓ | 1 | 1 1.00 |
0 0.00 |
— | — | — | — |
The check passes, because a missing rate of 0.4 is below the 0.5 limit. A stricter max_pct=0.3 would fail. This method can also count a single reason or category. Passing reason="refused" counts only the -98 values, a rate of 0.1, which passes with max_pct=0.2. Passing category="item_nonresponse" counts the refusals and don’t-knows together.
(
pb.Validate(data=survey)
.col_pct_missing(columns="age", missing=spec, category="item_nonresponse", max_pct=0.15)
.interrogate()
)| STEP | COLUMNS | VALUES | TBL | EVAL | UNITS | PASS | FAIL | W | E | C | EXT | ||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| #4CA64C66 | 1 |
col_pct_missing()
|
✓ | 1 | 0 0.00 |
1 1.00 |
— | — | — | — |
This one fails. There is one refusal and one don’t-know, so the rate is 0.2, which is above the 0.15 limit. Raising the limit to max_pct=0.25 would let it pass. Checking one reason or category at a time lets you write rules like “no more than 15 percent of answers may be refusals”, which is more specific than a single overall missing rate.
Knowing how much is missing is only half the job. The other half is checking that missing values are recorded correctly. The three methods in this section work row by row, so each produces one test unit per row and shows you exactly which records break the rules.
col_missing_codedThe col_missing_coded() method checks that every missing value uses a documented code. A real value passes and a documented code passes, but a plain null fails, because a null is a missing value with no reason attached. If your data is supposed to code every missing value, a null means someone skipped that step.
(
pb.Validate(data=survey)
.col_missing_coded(columns="age", missing=spec)
.interrogate()
)Nine of the ten rows pass and one fails. The failing row is respondent 4, whose age is a plain null rather than one of the documented codes. Use this check when your rule is that every missing value must have a reason.
col_missing_only_codedThe col_missing_only_coded() method checks something different. It asks whether every value is either a documented code or a valid real value. You define what counts as valid with an allowed= set or a min_val= and max_val= range, and anything that is neither a documented code nor a valid value fails. The score column has a -95 that is not in the spec.
(
pb.Validate(data=survey)
.col_missing_only_coded(columns="score", missing=spec, min_val=0, max_val=100)
.interrogate()
)Nine rows pass and one fails. The failure is respondent 3, whose score of -95 is neither a documented code nor a value between 0 and 100. The -99 for respondent 5 passes, because it is a documented code. Undocumented codes like -95 often appear when someone upstream starts using a new missing-value code without adding it to the codebook, and this check catches them before they are mistaken for real data.
A range works well for a continuous measure, but when a value can only take certain specific values, an explicit set is a better fit. The allowed= argument takes a list of valid values in place of min_val= and max_val=. If the score can only be 10 through 80 in steps of 10, we can list those values.
(
pb.Validate(data=survey)
.col_missing_only_coded(
columns="score",
missing=spec,
allowed=[10, 20, 30, 40, 50, 60, 70, 80],
)
.interrogate()
)The result is the same, nine passes and one failure, and the -95 is again the one that fails. The difference is that this version also rejects real values that are not on the list. A score of 35, for example, would fail here but pass with the 0 to 100 range. Choosing between a range and an allowed= set is the same decision as choosing between col_vals_between() and col_vals_in_set() in Chapter 6.
col_missing_consistentSurveys often have skip patterns, where the answer to one question decides whether a later question is asked at all. If a respondent was never asked about income, then both the income source and the income amount should be missing for the same reason. A record where one is coded as not asked but the other has a value points to a data-entry or routing error. The col_missing_consistent() method checks this across a set of related columns for a given reason. A row passes only when the columns agree, meaning either all of them are missing for that reason or none of them are.
(
pb.Validate(data=survey)
.col_missing_consistent(
columns=["income_source", "income_amount"],
missing=spec,
when_reason="not_asked",
)
.interrogate()
)Nine rows pass and one fails. The failure is respondent 4, whose income_source is coded -99 (“not asked”) while income_amount has a real value of 900. That cannot happen if the income questions were skipped together. Respondents 2 and 7 pass, because both of their income columns are -99.
A survey quality plan usually combines several of these checks, covering both how much is missing and whether the codes are used correctly. The plan below puts the checks from this chapter into a single interrogation.
(
pb.Validate(data=survey, tbl_name="survey")
.col_pct_missing(columns="age", missing=spec, max_pct=0.5)
.col_missing_coded(columns="age", missing=spec)
.col_missing_only_coded(columns="score", missing=spec, min_val=0, max_val=100)
.col_missing_consistent(
columns=["income_source", "income_amount"], missing=spec, when_reason="not_asked"
)
.interrogate()
)The overall missing rate for age is within the limit, but one age is a null that should have been coded, one score has an undocumented code, and one respondent’s income answers break the skip pattern. Because these are separate steps, the report shows each problem on its own line instead of combining them into one count. The three row-level methods also accept the segments= argument, so you could run the same checks separately for each survey wave or region, as described in Chapter 11.
Coded missing values are missing values that carry a reason, stored as codes like -99 for “not asked”. Validating them starts with describing the codes in a MissingSpec, which names each code, can group codes into categories, and decides whether plain nulls count as missing. You can write one by hand or generate one with MissingSpec.from_cdisc(), from_sas(), or from_spss(). Passing the spec to a column check through missing= keeps the codes from being treated as bad values.
The col_pct_null() method checks the plain null rate against an expected value, and col_pct_missing() checks the combined rate of codes and nulls, optionally for a single reason or category. The col_missing_coded() method requires every missing value to use a documented code, col_missing_only_coded() catches undocumented codes and invalid values, and col_missing_consistent() checks skip patterns across related columns. These methods come up again in the healthcare scenario of Chapter 31. The next chapter, Chapter 11, shows how to run checks separately for each group in a table.