Cleaning Scientific Data · Lesson 4 of 6

Handling Missing Data

~16 min

The Concept

You've identified all the gaps in Bluewater Basin's rainfall records, now you have to decide what to do with them. There are five options on the whiteboard: delete the rows, fill with zeros, fill with the monthly mean, interpolate from nearby stations, or mark them as missing and leave them out of calculations that require complete records.

Each option is right for some questions and catastrophically wrong for others. Filling a dry-season gap with the monthly mean is reasonable for annual totals; it's disastrous for studying short-duration rainfall events.

The right approach to missing data depends on why the data is missing and what you're going to do with it. Missing completely at random, the sensor failed for no reason related to what it was measuring, is the safest case; simple methods like mean imputation introduce less bias. Missing not at random, the sensor failed during the most extreme events, exactly when you most needed it, is the dangerous case; any imputation introduces systematic error.

Linear interpolation (guessing a missing value by drawing a straight line between the point before and the point after it) fills a gap by assuming the variable changed smoothly between the last known value before the gap and the first known value after it. It's appropriate for short gaps in slowly-changing variables (groundwater level) and terrible for spiky, event-driven variables (rainfall).

The Analogy

It is like guessing your friend's height on their tenth birthday if you only know their height at nine and eleven. Drawing a straight line between those two points is a reasonable guess for something that grows steadily. It would be a terrible guess for something spiky, like guessing how much ice cream they ate on a random Tuesday just from what they ate on Monday and Wednesday.

The Math (optional)

Linear interpolation across a gap:

Linear gap interpolation

x_t = x_{t_0} + (x_{t_1} - x_{t_0}) \cdot \frac{t - t_0}{t_1 - t_0}

x_{t₀} and x_{t₁} are the known values before and after the gap; t₀ and t₁ are their timestamps; t is the timestamp of the missing value you want to fill. The formula linearly weights between the two known values based on time position.

Code (optional)

handle_missing.pypython

import pandas as pd
import numpy as np

df = pd.read_csv("GW14_water_level.csv", parse_dates=["timestamp"])
df = df.set_index("timestamp").sort_index()

# Replace sentinel values with real NaN
df["level_m"] = df["level_m"].replace(-9999, np.nan)

# Strategy 1: flag only (don't fill), safest for event-driven variables
df["level_flag"] = df["level_m"].isna().map({True: "MISSING", False: "OK"})

# Strategy 2: linear interpolation, reasonable for slowly-varying water level
# Only interpolate gaps up to 2 hours (8 × 15-min records)
df["level_interpolated"] = df["level_m"].interpolate(
    method="time", limit=8
)

# Document what we did
n_filled = df["level_m"].isna().sum()
print(f"NaN values: {n_filled}")
print(f"Interpolated (limit 8 records): {df['level_interpolated'].notna().sum() - df['level_m'].notna().sum()}")

Line by line

replace(-9999, np.nan) converts sentinel flags to proper NaN values, the standard 'not a number' marker Python's data tools recognise.
We create a flag column first, marking which values are real vs missing, before modifying any numbers.
interpolate(method='time') fills gaps using timestamps rather than row position, so irregularly-spaced records are handled correctly.
limit=8 caps interpolation at 8 steps (2 hours), longer gaps remain NaN, because interpolating a 3-day gap in water level would be scientific fiction.

Why Real Researchers Care

Multiple imputation, generating several plausible filled datasets and analysing each, then combining results, is the gold standard for handling missing data in peer-reviewed research. For environmental monitoring, simpler methods are accepted when gaps are short and clearly random, but the gap-filling method must always be documented in the paper's data methods section.

Quick Check

Q1. A rainfall gauge has a 3-day gap in August, a potentially high-rainfall month. Which gap-filling method introduces the least bias?

Your Goal

Bluewater Basin's well GW-14 water level record has a 4-hour gap (16 records at 15-min intervals) between 14:00 and 18:00. The reading at 14:00 is 3.42 m and at 18:00 is 3.38 m. Compute the interpolated value at 16:00 (exactly midway through the gap).

Hint: t − t₀ = 2 hrs, t₁ − t₀ = 4 hrs. The interpolated value is at the halfway point.

Teach It Back

Explain why filling a rainfall gap with the monthly mean is appropriate for some analyses but wrong for others. Give one specific example of each.

Previous lesson