Cleaning Scientific Data · Lesson 2 of 6
Identifying Missing Values
~16 min
The Concept
Bluewater Basin's data logger at station RG-03 (Rain Gauge 3, in the agricultural zone) went offline for eleven days in August, a circuit board failure. When it came back online, the device resumed recording as if nothing had happened. There is no record of the gap in the CSV file: the timestamps simply jump from August 4 to August 15.
If you calculate August's total rainfall from this file without noticing the gap, you'll undercount rainfall by roughly 30% for that month, the highest-rainfall month of the year. Every downstream analysis depending on August precipitation data will be quietly, invisibly wrong.
Missing values in sensor data come in two forms. Explicit gaps (missing spots that are clearly labeled as missing) are flagged by a sentinel (-9999, NaN, 'NULL'), easy to find, easy to handle. Implicit gaps (missing spots with no label at all) are simply absent: the timestamps show a jump, and nothing in the file marks it as a problem. Implicit gaps are far more dangerous because automated processing ignores them.
The standard practice is to check the expected number of records against the actual number before doing anything else with a dataset. If your sensor samples every 15 minutes for 30 days, you expect 2,880 records. If you have 2,614, there are 266 missing, roughly 11 days, and you need to find out when.
The Analogy
It is like checking attendance in a classroom. If your register clearly says 'absent' next to a name, that is easy to spot. But if a page of the register is simply torn out and nobody notices, you might think every kid showed up every day. Implicit gaps are the torn-out page.
Code (optional)
import pandas as pd
df = pd.read_csv("RG03_rainfall.csv", parse_dates=["timestamp"])
df = df.set_index("timestamp").sort_index()
# Expected: one record every 15 minutes
expected_freq = "15min"
full_index = pd.date_range(df.index.min(), df.index.max(), freq=expected_freq)
# Find which timestamps are present vs absent
missing_timestamps = full_index.difference(df.index)
print(f"Expected records: {len(full_index)}")
print(f"Actual records: {len(df)}")
print(f"Missing records: {len(missing_timestamps)}")
# Find the start and end of each continuous gap
gaps = pd.Series(missing_timestamps).diff()
gap_starts = missing_timestamps[gaps > pd.Timedelta("30min")]
print("\nGap starts:", gap_starts.values[:5])Line by line
- We parse the timestamp column as actual datetime objects, not strings, so we can do time arithmetic.
- pd.date_range generates the complete set of timestamps we'd expect if the sensor had worked perfectly.
- Taking the difference tells us exactly which timestamps are absent, these are implicit gaps.
- Grouping consecutive missing timestamps finds where each gap starts and ends, so we can decide how to handle each one.
Why Real Researchers Care
Gap-checking is one of the first automated steps in every professional environmental data pipeline. Tools like the USGS's AQUARIUS platform and the international CUAHSI HydroShare network require gap logs as part of data submission, without them, other researchers downloading the dataset can't know what's missing.
Quick Check
Q1. A sensor file has 2,880 expected records but only 2,614 actual records. What's the most important next step?
Your Goal
Bluewater Basin's water level sensor at the streamflow gauge has 30-day records sampled every 5 minutes. How many records do you expect? If you receive 7,840 records, how many are missing, and what's the approximate total duration of missing data?
Hint: Total expected = (60/5) × 24 × 30. Missing duration in hours = missing_count × 5 / 60.
Teach It Back
Explain the difference between an explicit and an implicit data gap, and why implicit gaps are more dangerous, in plain language.