Data Cleaning -

MISSING DATA

Lecture 11

CMSC 320: Introduction to Data Science

• Fardina F. Alam

So far, we have talked about

Outlier data is a numeric value that is much larger or smaller than other values in the same feature. Outlier data is usually defined as two or three standard deviations from the feature mean.

Duplicate data are two or more identical instances in a dataset. Duplicate instances are usually erroneous and should be removed.

​

NEXT: Missing data

Missing data refers to instances in a dataset where values are not recorded or incomplete. In databases, it's represented as NULL, while in Python, it appears as NaN, NaT, None, or blank values.

2

Dirty Data

Missing, outlier, and duplicate data are collectively called dirty data. (Ref: Zybook 5.4)

​

​

3

Dirty data creates bias and inefficiencies in data analysis.

Recap from last lecture: How to handle Outlier Data

Winsorization: Instead of removing the outlier, replace extreme values with a chosen percentile value.

Example:

Original salaries:

[40,000, 45,000, 50,000, 55,000, 500,000]

Using Winsorization (95th percentile):

[40,000, 45,000, 50,000, 55,000, 70,000]

The extreme value 500,000 is replaced with the percentile cutoff (70,000).

Why use it?

• Reduces the impact of extreme values
• Keeps all observations in the dataset
• Helps create more stable machine learning models

Python: Use the winsorize function from the scipy.stats.mstats module.

How to handle Outlier Data: Replacing Outlier

​

Courtesy: Breakthrough Tech ML course

Winsorization keeps the outlier in the dataset but limits its effect by replacing it with a less extreme value

Scipy to winsorize data (cap extreme values)

Next Topic: Missing Data

Check Missing Data

df.isnull().any().any()

Back to: Missing Data

• Non-responses on a survey
• Sensor malfunction
• Data not provided by a third party
• Some idiot forgot to enter it

8

Why is our data missing?

Types of Missing Data (called “Mechanisms”)

1. Missing completely at random (MCAR)

​

​

1. Missing at random (MAR)

​

​

1. Missing not at random (MNAR)

​

Missing data can be categorized into 3 categories:

Types of Missing Data (called “Mechanisms”)

Missing completely at random (MCAR)

• There may not be a pattern as to why the value is missing.
• has no relationship with any values, observed or missing; it's just random chance.
• The reason is unrelated to the dataset.
• Ex. a weighing scale runs out of batteries

Missing at random (MAR)

• The value is missing due to other observed data (but not to the missing values themselves).
• The reason can be described by data in another variable in the dataset (knowing other data helps explain the gaps).
• Ex. when on a soft surface, a weighing scale produces more missing values than when on a hard surface

Missing not at random (MNAR)

• The missing value is related to the value itself ()the reason for the missing data depends on the values that are missing.
• The missing value is not dependent on other variables in the dataset.
• Ex. a weighing scale mechanism wears out over time, producing more missing data as time progresses

*Observed mean: already collected

Example: MCAR vs MAR vs MNAR

1. Missing Completely at Random (MCAR): A researcher loses some survey responses due to a technical glitch in the data collection software, with no pattern in which responses are lost.

​

1. Missing at Random (MAR): In a longitudinal study, younger participants are more likely to skip questions about their income, but researchers can account for age since it's recorded.

​

1. Missing Not at Random (MNAR): Students who receive low grades on an exam choose not to report their scores, leading to missing data that is directly tied to their performance.

11

*Observed mean: already collected

1. MCAR: Data missing Completely at Random

​

12

"Example 1": Income and Age are missing due to random technical errors (unsaved forms), with no relation to the observed data (Age, Gender, Favorite Color) or the actual missing values.

"Example 2": Imagine a big test where each student gets different random questions.

Missingness is entirely random and unrelated to any variable.

13

Notice: In this example, each student receives a random subset of questions, so the missing questions are randomly distributed and not influenced by student characteristics or any other observed data.

1. MCAR:Understanding Characteristics

• Missing values occur randomly
• No pattern in what is missing
• Missingness is unrelated to any variable
• Does not introduce systematic bias
• Standard statistical methods remain valid

Ref: https://www.publichealth.columbia.edu/research/population-health-methods/missing-data-and-multiple-imputation

2. MAR: Data missing Completely at Random

​

14

The missingness is related to some of the observed data but not the missing data itself

2. MAR:Understanding Characteristics

​

• Depends on observed variables�
• Not dependent on the missing value itself�
• Can be estimated using observed data (e.g., regression, imputation).

Example 1: In a medical study, older patients are more likely to skip mental health questions. The missingness depends on age (an observed variable), not on their actual mental health condition.

Younger people (e.g., Age < 30) are less likely to report income.

​

3. MNAR: Data Missing Not at Random (MNAR)

​

When data is not missing at random, the chance of it being missing depends on the missing values themselves, not just the observed data.

16

3. MNAR:Understanding Characteristics

​

• Missingness depends on the actual missing value itself.�
• Introduces systematic bias (certain types of values are more likely to be missing).�
• Cannot be explained by observed data.�
• Requires specialized methods (e.g., sensitivity analysis, advanced modeling).

This is the most challenging type of missing data to handle, as the missingness is related to the unobserved (missing data) data itself!

Example 1: In a financial survey, people with very high incomes are less likely to report their income because they prefer to keep it confidential.

​

​

People with very high or very low incomes are less likely to report income.

​

​

(1) MCAR: Data Missing at Completely Random

18

HOW TO HANDLE

Data Missing at Random

Solution 1: Drop it

For data that is categorical, it’s fine! “Missing” can just become a new category.

Ex: If our category is major, we could have { “Computer Science”, “Philosophy”, “Did not answer” }

For numerical data, most algorithms we use cannot accept NaN. So what do we do?

19

If 1% of your data has missing rows and you have a terabyte of it, just drop all rows that have missing stuff.

df.dropna()

When Is It Safe to Drop Missing Data?

No strict cutoff; depends on context.

Why no fixed rule?

• MCAR → unbiased but reduces precision
• Depends on sample size, key variables, and analysis type

Practical Checklist Before Dropping Data

1. Quantify missingness (overall, per variable, by subgroup)
2. Identify mechanism (MCAR, MAR, MNAR)
• Little’s MCAR Test → tests whether missingness patterns differ across groups (Chi-square test).�
• Distribution checks → compare observed vs. missing groups on key variables.

​

1. Compare deletion vs. imputation results
2. Report decisions transparently: Document % dropped, justification, and chosen method in your report.

More Strategies to Handle (MCAR) data

Relatively straightforward as the missingness is random and unrelated to any data, so the observed data remains representative and unbiased.

​

Strategies:

• Listwise Deletion: Remove rows with missing values.
• Use when missingness is small (e.g., < 5%). df_cleaned = df.dropna()�
• Pairwise Deletion: Use all available data for each calculation.
• Example: df.corr()�
• Simple Imputation: Replace missing values with mean, median, or mode (e.g., hot/cold deck).�
• Multiple Imputation Create multiple imputed datasets and combine results to account for uncertainty.
• from sklearn.impute import IterativeImputer

Solution 2: Listwise or Pairwise Deletion

Pairwise Deletion: Lets you keep more of your data by only removing only the data points that are missing from any analyses. It will create an uneven sample size for each of the variables but helpful when having a small sample or a large proportion of missing values for some variables.

​

22

Listwise Deletion: Remove any entire rows with missing data. This works well when the proportion of missing data is small.

train_1 = train.copy()

train_1.dropna()

All methods in pandas like mean, sum, etc. intrinsically skip missing values.

train_2 = train.copy()

train_2['Item_Weight'].mean()

Solution 3(A): Apply Imputation!

​

​

Imputation is the process of replacing missing values with estimates (new values).

Data may be imputed in several ways:

​

23

Examples:

Mean Imputation: Set the value to the mean of that column

For example, if a height is missing, fill in 5’5’’

Mode Imputation: Fill in with the most common value

• If someone in this class is missing their major, fill in “Computer Science”

When to use Mean vs. Median vs. Mode

• Mean: Prefer if data is numeric and not skewed.
• Median: Prefer if data is numeric and skewed.
• Mode: Prefer if the data is a string(object).

​

Notes: Mode can be used for numeric data, but it is more appropriate for categorical variables or when a clear most frequent value exists.

​

It's generally advisable: Use Mean/Median/Mode Imputation when no more than 5% of the variable contains missing data.

Solution 3(B): Hot-Deck Imputation

Fill missing values using data from similar rows in the same dataset.

How It Works

• Identify a similar record (based on relevant characteristics).
• Copy the value from that record.
• Optionally, average values from multiple similar records.�

Example If a student is missing a grade for CMSC320, find a student with similar academic history and use their CMSC320 grade.

Age Example: Identify similar individuals (e.g., based on gender or other characteristics). Use a similar person (donor) to fill the missing age.

Solution 3(C): Multiple Imputation (MI)

(common technique for MAR)

​

1. Create multiple copies of the dataset.

Generate plausible values for each missing value (imputation step).

2. Analyze each dataset separately (analysis step).

3. Combine results to reflect uncertainty (pooling step).�

Goal: Produce valid statistical inference despite missing data.�

Advantages

• Preserves sample size (no data loss).
• Adjusts standard errors to reflect uncertainty.
• Flexible and widely applicable.

Example Use Age and Gender to predict missing Income values multiple times, then combine the results

Multiple Imputation: Example

​

Scenario: You survey people about height and weight, but some participants skip one or both questions.

Example: If weight is missing for taller individuals, use height and gender to predict a range of plausible weights in each dataset.

​

​

​

​

​

​

​

Multiple Imputation

• Instead of filling in one guess, create multiple copies of the dataset. In each copy, use statistical models (e.g., regression) to generate plausible values for missing data.
• For each missing value, generate a distribution and sample from it.
• Analyze each completed dataset separately.
• Combine (pool) the results across datasets to reflect uncertainty.

Remember: We do not pool into a single imputed value.� We pool the analysis results, not the raw filled-in values.

The goal is valid inference ; not just one final “best guess.”

The fancyimpute library in Python is commonly used for multiple imputation.

How many imputations for MI?

• Rule of thumb: you can get a reasonable value with just 3
• For this class, 3 is fine

​

• Higher imputations can improve bias and precision, but computational and memory costs increase as the number goes up
• Standard errors tend to decrease as the number of imputed datasets increases

​

• In general, if you can afford the cost, use the percentage of missing data for the number of imputations
• Ex. if 6% of the data is missing, do 6 imputations

(2) MAR: Data Missing at Random

30

HOW TO HANDLE

Handling Missing Completely at Random (MAR) data

​

Strategies:

​

• Listwise Deletion
• Pairwise Deletion
• Simple Imputation
• Multiple Imputation
• Regression Imputation
• KNN Imputation
• Machine Learning Models: Use algorithms like Random Forest, XGBoost, or Deep Learning to predict missing values based on observed data. Pros: Can capture complex, non-linear relationships. Cons: Requires careful tuning and validation.
• Bayesian Imputation

For MAR: Regression Imputation

Use a regression model to predict missing values based on other observed variables.

​

Example: Predict missing Income values using Age and Education.

​

• Pros: More accurate than mean/median imputation.
• Cons: Assumes a linear relationship and underestimates variability.

​

For MAR: K-Nearest Neighbors (KNN) Imputation

Replace missing values with the average of the nearest neighbors in the dataset.

​

• Pros: Captures local patterns in the data.
• Cons: Computationally expensive for large datasets.

​

from sklearn.impute import KNNImputer

imputer = KNNImputer(n_neighbors=3, weights='distance')

​

​

https://www.geeksforgeeks.org/handling-missing-data-with-knn-imputer/#

(3) MNAR: Data Missing Not at Random

​

When data is not missing at random, the chance of it being missing depends on the missing values themselves, not just the observed data.

34

HOW TO HANDLE

Different Categories of MNAR

Data not missing at random is subject to some sort of systematic bias

1. Missing rows in series
• Data may be missing in consecutive rows or specific periods (e.g., weekends or certain months).
• Ex: Sensor data missing during weekends because the sensor is turned off.
2. Missing based on value
• Higher or lower values might lead to non-disclosure (e.g., individuals with high incomes may avoid reporting their income).
• Ex: Students with low grades may choose not to report their scores.
3. Boundary Conditions
• Data may be missing when it hits certain thresholds (e.g., temperature data missing below a minimum or above a maximum).
• Ex: Stock price data missing during extreme market conditions.

​

​

35

1. Handling missing rows in series Imputation Using Framing Data

36

We can use information from similar time periods or frames to estimate or impute missing income values over time.

​

E.g,. Look at income patterns from similar months in the past

Imputation using framing data means filling in those missing values based on similar information from other observations.

Use domain knowledge or historical data to impute missing values. Example: Use data from the same time period in previous weeks or months to fill in missing values.

B. Handling Data Missing Based on Value

• Sensitivity Analysis: explores how your results might change based on different assumptions about the missing data.
• Assume different scenarios for he missing data (e.g., low, medium, high values for missing incomes) and observe how these assumptions affect the overall analysis under each scenario.
• Pros: Provides a range of possible outcomes. Cons: Does not provide a single "correct" answer

​

• Pattern Mixture Models: create separate models for groups with and without missing data to understand the impact of missingness.
• In a survey with missing income data, create two groups: Group 1 (answered all questions) and Group 2 (skipped the income question). Analyze each group separately and then combine the results to understand overall trends.

37

Bayesian Imputation (MAR and MNAR)

38

A statistical technique used for imputing missing data in a dataset using Bayesian methods.

• Bayesian statistics is based on Bayes' Theorem, which allows for the estimation of unknown parameters by combining prior information (prior beliefs) and observed data.
• Multiple Possibilities: Unlike simpler imputation methods that rely on point estimates, with Bayesian imputation we think about many values (a range of possible values) for missing data, not just one guess (single estimate point).
• Provides a probabilistic framework for estimating missing values.

Bayesian Imputation

For categorical data, for example:

​

​

​

​

​

​

Evaluate each of these for each feature and assign!

X_missing = The missing value that we’re trying to impute

X_observed= The data that we observed previously

Bayesian Imputation: example

Suppose you have a dataset about fruits with two features: color and shape. You want to impute the missing values for the "Type of Fruit" category.

40

You want to fill in the missing 'Type of Fruit' values using Bayesian imputation, which involves estimating the probabilities of each fruit type based on the observed data.

Expert Input

Use domain knowledge to make informed assumptions about the missing data.

​

Example: If high-income individuals are less likely to report their income, manually adjust the imputed values.

C. Handling Missing Data: Dealing With Boundary Conditions

Depends on the circumstances.

1. Drop everything on the boundary and only work with the stuff within (if you want to just do predictions within the boundary)
2. Extrapolate the same distribution outside the boundary
3. Get more data

42

Note: Extrapolation means using existing data to make predictions or estimates for values that are outside the range of the data you have.

Example: Dealing With Boundary Conditions

Example: Temperature Sensor Data: Imagine you have a dataset that records outdoor temperatures (in degrees Celsius) over time. However, the sensor used to collect data has a known boundary condition—it can't measure temperatures below -10°C or above 40°C accurately.

43

Solution 1: Exclude Data at the Boundary: Exclude temperatures outside the range of -10°C to 40°C if you're focusing on normal weather conditions.

Solution 2: Extrapolate Data: Use statistical methods to estimate temperatures beyond -10°C and 40°C for predictions outside sensor limits.

Solution 3: Acquire More Data: Use sensors that measure extreme temperatures if you need accurate readings beyond -10°C and 40°C.

Note: Extrapolation means using existing data to make predictions or estimates for values that are outside the range of the data you have.

Incorrect Data

44

NEXT TOPIC

Incorrect Data

What does it mean for data to be incorrect?

45

• People lied to you → Response Bias (Human Error)
• Your instrument broke → Instrument / Measurement Error
• You’ve been recording the wrong metrics → Instrument / Measurement Error
• You have two identical entries with different values → Instrument / Measurement Error
• Illegal values, Values outside allowed range → Data Entry Errors
• Unclear default values → Default / System Errors

Types of Incorrect Data:

Detecting incorrect data involves looking for anomalies or patterns that deviate from what's expected or reasonable in your dataset.

Detecting Incorrect Data

46

Attractors: Check for unusual spikes or concentrations of data points in specific categories or values.

Example: A disproportionate number of individuals report height as exactly 6’0”, suggesting rounding rather than true distribution.

Discontinuities: Look for abrupt changes/ shift or discontinuities in your data.

​

Example : Once the limit for how much weed constituted a felony changed, there was a significant discontinuity centered around that threshold (People might adjust their behavior based on the new legal limit).

How do we find data that’s incorrect?

Detecting Incorrect Data: Implausible Modes

Examples:

• Many records at latitude/longitude (0,0)
• Large number of default timestamps:
• 2000-01-01 00:00:00
• 1970-01-01 00:00:00 UTC (Unix epoch)
• 1900-01-01 (Excel default)

​

• Huge number of unexpected zeros
• Implausible values (e.g., age = 150)

• Modes that don’t make sense: Identify modes (frequent values) in your data that don't align with the expected patterns.

Detecting Incorrect Data: Boundary Conditions

Examples:

• Playing video games for 1,000,000 hours�
• Negative ages�
• Implausible income or GPA values�

These often indicate data entry errors or coding issues.

48

Data Outside Valid Bounds Check for values that fall outside the logical or possible range.

Boundary Conditions (Artificial Caps)

Sometimes limits are created by systems, not reality.

Instrument Limits

• Measurement device has a maximum value.
• Example: Thermometer capped at 100°.

Database Constraints

• Database restricts maximum storable values.
• Example: Values cannot exceed 1 × 10¹⁰⁰.�

Survey Limits

• Response options capped at a maximum value.
• Example: Income choices only go up to $10M.

Survey only allowed ages up to 24

The radiation sensor cannot measure above 120 units

Detecting Incorrect Data: Instrument Error

Sometimes data problems occur because the measurement device itself is failing or miscalibrated.

• A scale is incorrectly tared: weighing scale is not set to zero correctly
• A sensor begins to lose sensitivity and so reports lower readings before being replaced
• A microphone begins to pick up more noise.

How to repair:

• Review historical data from the same or similar sensors.
• Compare past and current distributions.�Adjust or recalibrate current data to align with historical patterns.

Important: This approach only works if the type of error is clearly understood.

50

Problems or mistakes that can happen with measurement instruments or devices

This adjustment might involve shifting values, scaling them, or applying other corrective measures.

How to Deal with Incorrect Data

​

• Validation Rules: Enforce expected formats and ranges (e.g., valid dates, numeric bounds). Flag invalid or impossible entries.
• Statistical Analysis: Use descriptive statistics to detect anomalies or outliers.
• Consistency Checks Compare related fields for logical consistency.� (e.g., birthdate aligns with reported age)�
• Visual Inspection Use histograms, scatter plots, and boxplots to spot irregular patterns.
• Text Cleaning (Regex) Use regular expressions to detect and clean malformed text.
• Automated Tools Use libraries like Pandas, DataPrep, ydata-profiling to detect common issues.
• Removing duplicates, Correcting inconsistent data, Handling outliers and Formatting data.

​

Great Examples of Lies

52

SUMMARY

​

Messy Data: can be due to Missing values

Combined columns carefully �

Multiple joined tables, carefully, ensuring file formats match.�

Changing labels or formats: Adapt to changes in labeling schemes

Data Cleaning Essentials

• Fix obvious issues → use apply() for simple corrections�
• Correct data types → use astype() or conversions�
• Merge carefully → ensure matching keys and formats�
• Handle evolving labels → split dataset or standardize values�
• Remove duplicates → exact or subtle�
• Detect outliers → e.g., z-scores�
• Impute missing data → mean, median, mode, or advanced methods�
• Check boundary conditions → instrument, database, or survey limits�

SUMMARY

—-THE END—-

Detecting Incorrect Data: Identify issues like attractors, discontinuities, strange modes, and data outside valid bounds.

Repairing Instrument Errors: Correct data from malfunctioning sensors based on past data or similar sensors.

Signs of Boundary Conditions: Look for sudden changes or gaps in data and observe patterns in visualizations.

Data cleaning is a crucial step in data analysis to ensure the accuracy and reliability of your results.

54