4.2: Handling Missing Data (NULL Values)

Handling Missing Data

One of the first challenges in data cleaning is dealing with missing or incomplete data. In databases, a missing value is often represented as NULL. It’s important to understand that NULL is not the same as zero or an empty string – rather, it signifies the absence of any value. In other words, NULL means we do not have data for that field. This distinction matters because calculations or comparisons behave differently with NULLs: for example, NULL in a numeric column will be excluded from an AVG() calculation, and any comparison with NULL (e.g. column = NULL) will return false – you must use IS NULL to check for missing values.

Why do missing values occur? They can result from various reasons: data entry omissions, merging data from an incomplete source, sensor failures, or fields that are optional by business logic. Data professionals categorize missing data mechanisms as: MCAR (Missing Completely at Random), MAR (Missing at Random), and MNAR (Missing Not at Random) [4]. In brief, MCAR means the missingness has no systematic relationship to any values, MAR means it relates to observed data (some pattern in known variables), and MNAR means it relates to the value that is missing itself. For instance, if data are missing completely at random, dropping those records might not bias the analysis; but if data are systematically missing (say high-income individuals opting out of a survey question), simply dropping them could skew results.

Identifying NULLs: The first step is to profile the dataset to find how many and which records have NULL values. This can be done in SQL with queries like: SELECT COUNT(*) FROM table WHERE column IS NULL; for each column of interest. Profiling tools and data preparation software can also visually highlight missing data. Some datasets might have sentinel values indicating missing data (e.g. "N/A", or 0 in a field where 0 is not valid) – those should be translated to actual NULLs for consistency.

Handling NULLs: Once identified, there are a few main strategies to handle missing data in preparation:

• Omission (Deletion): If the records with missing values are few and not critical, one can simply exclude those records or fields from the analysis. This is also called listwise deletion. For example, if 2% of rows have a NULL in a field that is needed for a particular analysis, it might be acceptable to drop those 2% of rows. However, if data is missing not at random, this can introduce bias by systematically excluding certain cases.

• Imputation (Filling in): This means populating a missing value with a reasonable estimate. Simple imputation can use a constant (like 0 or "Unknown"), or more sophisticated methods like using the mean or median of that field (for numeric data). For categorical data, one might use the most common category or introduce a new category like "Missing". More advanced approaches include k-Nearest Neighbors or regression models to predict missing values, or even multiple imputation techniques which create several possible values and average results for robustness. Each method has trade-offs – for instance, filling all missing ages with the average age preserves the mean but underestimates variability.

• Business-Rule Default: In some cases, a missing value can be filled with a domain-specific default. For example, if a DiscountCode field is NULL, the business rule might be to treat it as "No Discount". As another example, missing values in a LastPurchaseDate could be interpreted as “no purchases made” if that fits the context. This requires understanding the domain and should be documented as an assumption.

• Flagging and Special Handling: Sometimes neither dropping nor simple imputation is ideal – the fact that a value is missing could itself be informative (e.g., non-response in a survey might correlate with an outcome). In such cases, one might create a separate indicator column (e.g. field_missing = 1) to flag that the value was missing, and then proceed with a default or imputed value. This retains information about the missingness pattern.

Figure \(\PageIndex{1}\): Summarizes the four main approaches for addressing missing values — omission, imputation, business-rule defaults, and flagging — along with when each is most appropriate. (CC BY 4.0; Felix Amoruwa) Access to a detailed description of Figure \(\PageIndex{1}\)

Regardless of the strategy, differentiating between required vs. optional (nullable) fields is critical. A required field is one that by definition should never be NULL in a valid dataset – for example, a primary key or an order date in a transactions table. If a required field is NULL, that is a serious data quality error that often must be corrected at the source. On the other hand, nullable fields are allowed to be blank (NULL) by design – for example, an optional middle name for a customer. For nullable fields, the presence of NULL is not inherently an error, but still needs consideration in analysis (e.g., count them carefully, decide how to display or filter them). Part of data cleaning is verifying these expectations: are NULLs only present in fields that are supposed to be optional? If not, we have an issue to address.

In SQL, enforcing the distinction can be done with schema constraints (marking a column NOT NULL if it should never be missing). If you are cleaning data that comes from a less controlled source (like CSV files, or a data lake), an analyst would be expected to apply those business rules to catch unexpected NULLs. For example, you might run a query: SELECT * FROM Orders WHERE OrderDate IS NULL; – if any results appear, those are orders missing a date, which might be invalid data according to business logic. Such records could be removed or fixed (perhaps by retrieving the date from another system, if possible).

Example: Suppose we have a customer dataset with a PhoneNumber field that is often NULL because not all customers provide a phone. That field is intended to be optional. However, the CustomerID field is a unique identifier that should never be NULL (required). During data cleaning, we find two records with CustomerID = NULL – this violates our expectation. The best practice is to have prevented that via a constraint when the data was stored, but given we have it, we must handle it. We might attempt to trace back and fill those IDs if they can be determined; if not, those records might have to be discarded as unusable because without an ID we cannot reliably tie them to anything. In contrast, for the PhoneNumber NULLs (optional field), we might leave them as NULL but ensure any analysis (like contacting customers) accounts for the fact that some have no phone. If we needed a phone number for an operation, we might substitute a placeholder or use an alternate contact method for those entries.

In summary, dealing with missing data requires careful thinking: identify the NULLs, decide if they are allowed, and choose a strategy (drop, fill, or flag) that best preserves the integrity of the analysis. Always document what you do with missing values, since it can impact results and interpretations.