Judging Missing Values, Duplicates, and Transformations

After finishing this topic

You will be able to distinguish missing values, duplicate rows, and unit conversions as distinct data quality problems. You will learn how to avoid asserting MCAR, MAR, or MNAR from the observed table alone, and how to document which handling assumptions you used.

Starting point: what does a single blank cell mean?

A blank cell in a table can express measurement failure, not yet measured, not applicable, or a missed entry. The number 0 may also be a measured 0, or a value that could not be measured being arbitrarily filled in as 0. Missing-data handling is therefore not the work of eliminating blanks, but the work of confirming why a value is absent and what role it plays in the analysis.

Separating missingness, duplication, and unit errors

Missingness

Missingness is the state in which an expected value was not recorded. If you do not know why the missingness occurred, simple imputation can change the research question. Do not settle the missingness mechanism from the observed data alone; check the measurement logs and the collection procedure.

Duplication

Duplication may mean that the same row was repeated, but it may also be that legitimate observations from multiple time points were entered under the same ID. Do not delete rows on the sole fact that unit_id is duplicated. Define whether something is a duplicate on the basis of the expected observational unit, time point, and repeat keys.

Transformation

Transformation is work that changes the representation or scale of values, such as unit changes, log transformation, normalization, and categorization. If you do not record the meaning before and after the transformation and when it was applied, it becomes difficult to return results to the original units or to compare them with other analyses.

Counting the state first with Python

import pandas as pd

missing = data.isna().sum()
duplicate_key = data.duplicated(
    subset=["condition", "outcome"], keep=False
)
profile = pd.DataFrame({
    "missing": missing,
    "dtype": data.dtypes.astype(str),
})
print(profile)
print("candidate duplicate rows:", int(duplicate_key.sum()))

isna() counts values that are represented as missing, but it does not tell you why the missingness occurred. Likewise, duplicated() only flags candidates where the specified combination of columns repeats; it does not judge whether such a row is an erroneous copy or a legitimate repeat. You must first define the unit of analysis and the repetition key, and then choose the subset.

Recording your missingness assumptions

When handling missing data, distinguish the following.

• Missingness rate: which columns and units are empty, and by how much
• Generating process: whether it arose during measurement, transfer, or filtering
• Analytic assumptions: how the absence of a given value might connect to the results
• Handling rules: whether you excluded, imputed, created a separate category, or used model-based handling
• Sensitivity: how much the conclusion changes under other reasonable handling choices

Attaching the labels MCAR, MAR, or MNAR does not by itself prove an assumption. Distinguish patterns visible in the data from knowledge of the collection process, and leave what you could not confirm as uncertainty.

Records that compare before and after handling

After handling missingness or duplicates, do not quietly change the row count of the raw data; instead, leave a summary like the following.

If the row count drops after processing, check which units disappeared. If missingness was reduced more in particular conditions or batches, the exclusions may change the comparison structure of your results. In that case, avoid saying "the data became clean because missing values were removed"; instead, report which observations were dropped from the analysis and for what reason.

Not treating one handling method as the correct answer

Whether to exclude or impute missing values, and whether to keep or remove duplicate candidates, depends on the data-generating process and the question. Before deciding on handling rules, distinguish at least the following two.

• Rules that correct data errors: fixing mistakes in entry, units, or IDs
• Rules that add analytical assumptions: treating unobserved values in a particular way

The first also requires justification and preservation of the original, and the second can affect the uncertainty of the results, so it requires a more explicit record of assumptions. If applying different reasonable handling rules to the same data changes the interpretation, do not hide that difference; leave it as a sensitivity result.

Transformations are part of provenance

For example, if you converted millimeters to micrometers, record the conversion formula, the original unit, the new unit, and the columns and version to which it was applied. Operations that change the interpretation and distribution of values, such as log transformation, must also leave behind the rule for handling 0 and whether the transformation is invertible.

Rather than overwriting the original column, separating them as value_raw and value_transformed lets you trace calculation errors and compare before and after the transformation. The very fact that you chose a particular transformation is a judgment about the research question and the data-generating process, so do not leave only the result.

Fixing handling rules together with code

If judgments are left only as sentences, it is hard to apply the same rules again later. In code, explicitly print the row count before processing and after processing, the number of missing values per column, and the criteria for duplicate candidates. If you save the processed result as a new output instead of overwriting the original file, you can compare at which step the values changed when an error occurs.

For example, even if you decide to exclude missing outcome values, the following two statements describe different operations.

drop rows where outcome is missing
drop rows where any field is missing

The first excludes only the rows for which outcome cannot be computed, whereas the second also removes rows in which the contextual columns needed for the analysis are empty. If you do not write down which rule you applied, the processing cannot be reconstructed from the row count of the result table alone.

Units and interpretation before and after transformation

Unit conversion does not merely change the numbers; it changes the units in which the results are read. A log transformation can make differences between values no longer readable in the same way as differences in the original units. Therefore, when reporting means or dispersion after transformation, also state "on which scale was this value computed?"

Categorization is also a transformation. If you convert a continuous measurement into two groups using a threshold, information near the boundary is lost, and the choice of threshold can affect the results. This installment does not prescribe which transformation you should perform; instead, it applies the principle that the purpose, rules, and impact of a transformation must be recorded.

Auditable records of data quality

Quality check results are easier to read when divided into the following three layers.

• Observation: there were 3 missing-value candidates in outcome.
• Judgment: the measurement log could not be checked, so the cause was not determined.
• Handling: they were excluded from this analysis, and the row counts before and after exclusion were recorded in the manifest.

By not mixing observation with judgment, you can avoid poorly supported statements such as "the missingness was random." Even if a later analysis chooses a different handling rule, the original observations and the handling history remain comparable.

Returning to the Research Question for Interpretation

Checking missingness, duplicates, and transformations does not make the data complete. It does, however, make it clearer which values are actual measurements and which are the results of processing, and which rows are the unit of analysis. Without this information, it is hard to explain which inputs a mean or a model result depends on.

The choice of missing-data handling can affect the uncertainty of the results. So do not hide the handling method; report it together with the observational unit that the research question requires.

The goal of data quality work is not to eliminate every value that is inconvenient for the analysis. It is to preserve how values were generated and what the observational unit is, while explaining which data were actually used for the question. Even if the table looks tidier after processing, if observations under a particular condition were systematically dropped, the meaning of the comparison can change.

Common Failures and How to Check for Them

• Do not replace all missing values with 0.
• Do not immediately treat duplicate IDs as duplicate rows.
• Do not keep only transformed values and discard the original units.
• Do not conclude MCAR, MAR, or MNAR from the missingness pattern alone.
• Do not record handling rules only in the results table while omitting them from provenance.

Key Takeaways

• Missingness is a state of having no value, not a marker that tells you its cause.
• Duplicates should be judged only after defining the unit of analysis and the repeat keys.
• Transformation changes not only how a value is expressed but also its interpretation and provenance.
• MCAR, MAR, and MNAR are assumptions and are not automatically settled by a single observed table.
• Recording the before and after of handling, the rules, and the versions together is what makes results reviewable.

On to the Next Topic

Now that we have checked the shape and quality of the data, the next module covers probability models and the data-generating process. We extend the question to what structure the observed values may have come from.

References

• pandas User Guide: https://pandas.pydata.org/docs/user_guide/index.html
• ASA GAISE Reports: https://www.amstat.org/education/guidelines-for-assessment-and-instruction-in-statistics-education-%28gaise%29-reports

The tables, code, and handling questions in this installment are educational material composed independently by BioStatPy.