Data Cleaning & Missing Values

Why Data is Never Perfect

In textbooks, data arrives in tidy tables with no missing values, correct types, and sensible ranges. In practice, data is collected by humans using imperfect systems under time pressure, and it shows. Before you can analyse anything, you need to understand the ways your data has been corrupted in transit from the world to your spreadsheet.

The categories of dirty data you will encounter in practice:

1Missing values — fields left blank, encoded as "N/A", "Not given", 0 where 0 means "no data", or genuinely absent. The most common problem, and the most nuanced to handle.
2Wrong types — a numeric column stored as strings because one cell contained text. A date stored as a plain number. A boolean stored as "Yes"/"No" instead of True/False. Type errors cause silent failures.
3Inconsistent formats — "New York", "new york", "NY", "N.Y." all mean the same thing but count as four different values in a groupby operation. Currency with and without dollar signs. Dates as MM/DD/YYYY and DD-MM-YYYY in the same column.
4Duplicates — the same record entered twice, or two systems contributing the same event. Duplicates inflate counts, distort distributions, and make models overfit to specific examples.
5Outliers and impossible values — an age of -3, a delivery time of 0 minutes, an order cost of $0.00. Some are data entry errors; some are genuine extremes. Both require investigation.

The Cost of Not Cleaning Dirty data doesn't just produce wrong answers — it often produces plausible-looking wrong answers. A correlation calculated on a column with mixed text and numbers will return NaN or an error. But a mean calculated on a column where missing values were encoded as 0 will return a number — just the wrong one. Silent errors are the most dangerous kind.

Types of Missing Data

Not all missing data is the same. The statistical literature distinguishes three types, and the distinction matters enormously for how you handle them.

MCAR

Missing Completely At Random

The probability of a value being missing has nothing to do with any variable in the dataset. A sensor randomly drops data packets. A survey question is accidentally skipped. The missingness is pure noise.

MAR

Missing At Random

The probability of missing depends on other observed variables, but not on the missing value itself. Younger respondents may skip an income question — but whether they skip it doesn't depend on their actual income.

MNAR

Missing Not At Random

The probability of missing depends on the value that would have been recorded. High-income people skip the income question because they don't want to disclose a high income. The absence itself is data.

Why the Type Matters

For MCAR data, you can safely delete missing rows or impute — the missing values are random noise, and removing them doesn't bias your analysis.

For MAR data, you need to be careful about which rows you delete. If younger respondents disproportionately skip the income question, deleting those rows underrepresents young people in your income analysis. Model-based imputation (using other variables to predict the missing value) is often appropriate.

For MNAR data, the absence itself carries information, and imputing or deleting can seriously bias your results. The right approach is often to keep the missingness as its own category — a "no answer" group that is meaningfully different from the answer groups.

FoodHub Ratings: A Classic MNAR Case In the FoodHub dataset, 39% of ratings are missing. Why would a customer not leave a rating? Almost certainly because the experience was unremarkable or bad — satisfied customers are more motivated to rate positively than dissatisfied customers are to rate negatively, and a truly great experience prompts a rating more reliably than a mediocre one. The missing ratings are NOT random. They cluster disproportionately among certain experience types. Treating them as random would bias every rating-based analysis.

Strategies for Missing Values

Once you know what type of missingness you're dealing with, you can choose a strategy. There is no universal right answer — the choice depends on the type of missingness, the proportion missing, and what you plan to do with the data.

Strategy	When to Use	When NOT to Use
Delete rows	MCAR, missing < 1–2% of rows, the column is critical	MNAR, high proportion missing, small dataset
Impute with mean/median	MCAR/MAR, numeric column, roughly symmetric distribution, < 10% missing	MNAR, skewed distributions, when preserving variance matters
Impute with mode	Categorical columns, MCAR/MAR	When the mode is rare or multiple modes exist
Model-based imputation	MAR, when other variables can predict the missing one, larger datasets	When the imputation model would itself have missing values; small datasets
Keep as separate category	MNAR, categorical columns, when "not answered" is meaningful	When missingness truly is random and the separate category would confuse analysis
Leave as NaN	When you plan to use algorithms that handle NaN natively (e.g., XGBoost), or when the column is not used in a specific analysis	When feeding into algorithms that will error on NaN (e.g., scikit-learn's standard estimators)

Type Conversion: When Text Masquerades as Numbers

One of the most common data cleaning problems is a column that should be numeric but is stored as a string — usually because one or more cells contain text. Pandas infers column types when loading a CSV: if even a single cell in a column contains a non-numeric value, the entire column becomes object (string) dtype.

When this happens, arithmetic operations on that column return errors or nonsense. You cannot compute the mean of a column that pandas thinks is text. You cannot plot a histogram. You cannot compare values. The column is effectively unusable until you fix the type.

# Detect the problem
df['rating'].dtype          # Returns: object (should be float64)
df['rating'].value_counts() # Reveals: 736 rows say 'Not given'

# Step 1: Replace the text sentinel with NaN
df['rating'] = df['rating'].replace('Not given', np.nan)

# Step 2: Convert to numeric
df['rating'] = pd.to_numeric(df['rating'])

# Verify
df['rating'].dtype          # Now: float64
df['rating'].isnull().sum() # Now shows 736 missing values correctly

NaN vs Zero: A Critical Distinction

When converting missing text to a numeric representation, there are two candidates: NaN (Not a Number — Python's representation of "no value") and 0. These are not interchangeable, and choosing the wrong one causes serious analytical errors.

NaN means "we don't know." Pandas automatically excludes NaN values from calculations like .mean(), .std(), and .corr(). This is the correct behaviour — you want to calculate the average rating among orders that received ratings, not among all orders including those where no rating was given.

Zero means "the value is zero." If you replace "Not given" with 0, you are asserting that an unrated order has a rating of zero — the minimum possible. This is almost certainly false, and it will drag your average rating down significantly. In FoodHub, the actual mean rating (for rated orders) is 4.34 out of 5. If you imputed missing ratings as 0, the overall "mean" would be approximately 2.44 — a completely false picture of customer satisfaction.

The Zero Imputation Trap Never replace missing numeric values with 0 unless you have domain knowledge confirming that a missing value genuinely means zero. In most cases — including FoodHub ratings — it does not.

The "Not Given" Decision in FoodHub — A Case Study

In the FoodHub dataset, 736 out of 1,898 orders (38.8%) have rating = 'Not given'. This is a large proportion — nearly four in ten orders. How should we handle it?

Let's walk through the options and why we rejected each except the last:

Option A: Delete those 736 rows

We would lose 39% of our data. More importantly, if the missing ratings are MNAR (and we believe they are), the 736 deleted rows are systematically different from the 1,162 retained rows. Our analysis would reflect only the experience of customers who chose to rate — a biased sample.

Option B: Impute with mean rating (4.34)

This would be deceptive. We're asserting that orders with no rating had exactly average satisfaction. This artificially compresses variance in the ratings column — we'd see less spread than actually exists. It would also inflate our apparent confidence in customer satisfaction levels. The mean is not "neutral" when applied to MNAR data; it's wrong.

Option C: Impute with median or mode

Similar problem. Whether we impute 4.34 (mean), 4.5 (median), or 5.0 (mode), we are fabricating data for 736 orders. All three choices introduce bias in different directions.

Option D: Leave as NaN (our choice)

We replace "Not given" with NaN and leave it. Rating-based analyses (correlation, distribution) automatically exclude these rows. When we report the average rating, we clearly state it's the average among rated orders. The absence of a rating is preserved as information — and in the hypothesis testing section, we can even analyse the pattern of missingness itself.

The goal of data cleaning is not to eliminate missing values — it is to handle them in a way that preserves the truth of what the data is telling you. Sometimes the most honest answer is: "We don't know the rating for 39% of orders, and we should not pretend otherwise."

Data Validation Checklist

Before any analysis, run through this checklist. It takes about ten minutes and prevents hours of backtracking.

▶Row and column counts: df.shape. Do these match what you expected from the data source?
▶Column names: df.columns. Are there unexpected spaces, special characters, or inconsistent capitalisation?
▶Data types: df.dtypes. Are numeric columns numeric? Are date columns datetimes or strings? Are booleans booleans?
▶Null counts: df.isnull().sum(). Which columns have missing values, and how many? What percentage of each column is missing?
▶Unique values for categoricals: df['col'].value_counts(). Are there unexpected categories? Typos? Values that should be the same but aren't ("NYC" vs "New York City")?
▶Value ranges for numerics: df.describe(). Are minimum and maximum values plausible? Is there a 0 where 0 means "missing"? A negative value where only positive makes sense?
▶Duplicate rows: df.duplicated().sum(). If duplicates exist, are they intentional (e.g., two orders at the same time) or errors?
▶Logical consistency: For FoodHub: total_time should equal food_prep_time + delivery_time. Cross-check computed columns against their components.

# A quick diagnostic sweep in Python/pandas

print(df.shape)
print(df.dtypes)
print(df.isnull().sum())
print(df.duplicated().sum())
print(df.describe())

# For each categorical column:
for col in df.select_dtypes('object').columns:
    print(f"\n{col}:")
    print(df[col].value_counts())

In the FoodHub Project

The FoodHub dataset required one major cleaning step: replacing 'Not given' in the rating column with NaN, then converting the column from object to float64 dtype. No other columns had missing values. The decision to use NaN rather than imputation was deliberate — ratings are MNAR, and any imputed value would misrepresent the distribution. All subsequent rating analyses are performed on the 1,162 rated orders only, with the 736 unrated orders excluded from those calculations but retained in the full dataset for non-rating analyses. See the full data preparation at the FoodHub project page.

← What is EDA? Next: Descriptive Statistics →