🔧 Fix Air quality Data cleaning Air Quality Data · step 18 Notebook By · Updated

Air Quality Data Gaps and Negative Values

On this page

Problem statement

The annual means look slightly off. A rural station reads higher than it should next to its neighbours; a PM2.5 series has a few hundred negative hours that somebody wants to delete; one suburban site has a mean that is suspiciously low and turns out to be built from four months of data. None of these raises an error. Each is a decision about which hours count, made implicitly by a filter or a default, and each biases the mean in a predictable direction.

The fix is to make the decisions explicit: which validity codes to keep, what to do with instrument noise below zero, and how much of the year a mean needs before it means anything.

Quick answer

Three rules cover most of it:

  1. Keep every hour flagged valid, including "valid, below detection limit". In EEA data that is Validity > 0, not Validity == 1.
  2. Keep negative values in means; clip only for display or index lookups. Deleting them biases the mean upwards.
  3. Require data capture — 90% of hours for an EU annual statistic, 75% (18 of 24 hours) for a daily mean — and report capture next to every mean.
import io, requests, pandas as pd

API = "https://eeadmz1-downloads-api-appservice.azurewebsites.net/ParquetFile/urls"
META = "https://discomap.eea.europa.eu/map/fme/metadata/PanEuropean_metadata.csv"
r = requests.post(API, json={"countries": ["DE"], "cities": ["Berlin"], "pollutants": ["NO2"],
                             "dataset": 2, "dateTimeStart": "2023-01-01T00:00:00Z",
                             "dateTimeEnd": "2023-12-31T23:00:00Z", "aggregationType": "hour"},
                  timeout=120)
meta = pd.read_csv(META, sep="\t", low_memory=False,
                   storage_options={"User-Agent": "spatialworkflow-examples/1.0"}).drop_duplicates("SamplingPoint")   # EEA refuses Python's default agent
rows = []
for url in [u.strip() for u in r.text.splitlines()[1:] if u.strip()]:
    d = pd.read_parquet(io.BytesIO(requests.get(url, timeout=120).content))
    d["Value"] = pd.to_numeric(d.Value, errors="coerce")
    y = d[(d.Start >= "2023-01-01") & (d.Start < "2024-01-01")]
    if y.empty:
        continue
    sp = d.Samplingpoint.iloc[0].split("/")[1]
    rows.append({"point": sp[7:14], "area": meta.set_index("SamplingPoint").loc[sp, "AirQualityStationArea"],
                 "share_code_2": (y.Validity == 2).mean(),
                 "mean_valid": y[y.Validity > 0].Value.mean(),
                 "mean_code_1_only": y[y.Validity == 1].Value.mean()})
t = pd.DataFrame(rows)
t["bias"] = t.mean_code_1_only - t.mean_valid
print(t.groupby("area")[["share_code_2", "mean_valid", "mean_code_1_only", "bias"]]
        .mean().round(2).to_string())
                share_code_2  mean_valid  mean_code_1_only  bias
area                                                            
rural-nearcity          0.24        7.52              9.31  1.79
suburban                0.16        8.79             10.07  1.29
urban                   0.01       20.67             20.84  0.16

Hours below the detection limit are the cleanest hours, and they are concentrated at the cleanest stations: a quarter of the year at the rural stations, 1% in the city. Dropping them lifts the rural means by 1.8 µg/m³ — 24% — and the urban ones by 0.2, so the urban–rural gradient looks shallower than it is.

List flags, filter on validity, keep negatives, compute capture

1. List the flags and their meanings

EEA Validity: 1 valid, 2 valid below detection limit, 3 valid below detection limit with a substituted value, −1 not valid, −99 not valid because of maintenance. AURN: R ratified, P provisional. Read the codes before choosing a filter.

2. Filter on validity, not on value

Remove rows the provider marks invalid. Do not add your own value-based filters — "drop anything below zero", "drop values over 500" — without a documented reason.

3. Keep negative values in averages

Instruments measuring near zero report noise in both directions. The negative half is as real as the positive half; removing it removes only one side of the noise.

4. Compute capture per period

For each station and year: valid hours ÷ hours in the year. For each day: valid hours ÷ 24. Drop or flag periods below the threshold.

5. Check when the gaps are

Four missing weeks in winter bias an NO2 mean down; the same four weeks in summer bias it up. Report the months covered, not only the percentage.

EEA validity codes and AURN status flags with what each means and whether to keep it. keep everything marked validflagmeaningkeep?EEA 1validyesEEA 2valid, below detection limityesEEA 3valid, substituted valueyes, notedEEA −1not validnoEEA −99maintenance or calibrationnoAURN R / Pratified / provisionalyes; recheck PFilter on the provider’s flags, not on your own value thresholds spatialworkflow.io
Keep everything the provider marks valid — including valid-but-below-detection.

Negative PM2.5 hours and a third-of-a-year mean

Example 1 — negative PM2.5 hours

def aurn(site, col):
    text = requests.get(f"https://uk-air.defra.gov.uk/datastore/data_files/site_data/"
                        f"{site}_2023.csv", timeout=120).text
    d = pd.read_csv(io.StringIO(text), skiprows=4, low_memory=False)
    s = pd.Series(pd.to_numeric(d[col], errors="coerce").to_numpy(),
                  index=pd.to_datetime(d.Date, dayfirst=True))
    return s, d.iloc[:, d.columns.get_loc(col) + 2].dropna().iloc[0]

pm, unit = aurn("HORS", "PM<sub>2.5</sub> particulate matter (Hourly measured)")
neg = pm[pm < 0]
print(f"London Westminster PM2.5 ({unit}): {len(neg)} negative hours, "
      f"min {neg.min():.1f}, median of negatives {neg.median():.1f} µg/m³")
print(f"annual mean, as ratified:     {pm.mean():.2f}")
print(f"annual mean, negatives → 0:   {pm.clip(lower=0).mean():.2f}")
print(f"annual mean, negatives dropped: {pm[pm >= 0].mean():.2f}")
London Westminster PM2.5 (ugm-3 (BAM)): 159 negative hours, min -4.0, median of negatives -1.0 µg/m³
annual mean, as ratified:     7.81
annual mean, negatives → 0:   7.83
annual mean, negatives dropped: 7.98

The differences are fractions of a µg/m³ — and at the WHO guideline of 5 µg/m³, a fraction of a µg/m³ is several percent of the number being tested. The ratified mean already includes the negatives; that is what the data provider considers the best estimate.

Example 2 — a mean built from a third of the year

no2, _ = aurn("LON6", "Nitrogen dioxide")
ref, _ = aurn("KC1", "Nitrogen dioxide")
capture = no2.notna().groupby(no2.index.month).mean()
print(f"London Eltham NO2 capture: {no2.notna().mean():.0%} overall")
print("by month:", " ".join(f"{m}:{c:.0%}" for m, c in capture.items()))
both = pd.DataFrame({"eltham": no2, "kensington": ref}).dropna()
print(f"\nEltham mean over its {len(both):,} valid hours: {both.eltham.mean():.1f} µg/m³")
print(f"North Kensington over the same hours: {both.kensington.mean():.1f}; "
      f"over the full year: {ref.mean():.1f}")
London Eltham NO2 capture: 33% overall
by month: 1:100% 2:0% 3:0% 4:85% 5:99% 6:100% 7:11% 8:0% 9:0% 10:0% 11:0% 12:0%

Eltham mean over its 2,887 valid hours: 11.0 µg/m³
North Kensington over the same hours: 17.2; over the full year: 18.1

Eltham measured in January and from April to early July, and nothing from August on. The reference station shows how unrepresentative those hours are: North Kensington reads 17.2 over them against 18.1 for the whole year, 5% lower. Scaling Eltham by that ratio gives about 11.6 µg/m³ — a documented correction, resting on one reference station. A mean on 33% capture should not be published without it, or without saying what it rests on.

Example 3 — capture-aware annual table

out = []
for site in ("MY1", "KC1", "CLL2", "HORS", "LON6"):
    s, _ = aurn(site, "Nitrogen dioxide")
    cap = s.notna().mean()
    out.append({"site": site, "capture": f"{cap:.0%}", "mean": round(s.mean(), 1),
                "valid for an annual statistic": cap >= 0.9})
print(pd.DataFrame(out).to_string(index=False))
site capture  mean  valid for an annual statistic
 MY1     95%  41.6                           True
 KC1     99%  18.1                           True
CLL2     99%  24.2                           True
HORS     99%  21.8                           True
LON6     33%  11.0                          False

The column on the right is what turns a table of numbers into a table of findings.

Upward bias in Berlin annual NO2 means from dropping below-detection-limit hours, by station area. Validity == 1 instead of > 0rural near city · 24% code 2+1.79 (+24%)suburban · 16% code 2+1.29 (+15%)urban · 1% code 2+0.16 (+1%)01 µg/m³2 µg/m³The cleanest stations lose their cleanest hours spatialworkflow.io
Dropping validity code 2 biases the cleanest stations the most.

Detection limits and capture thresholds

Why "below detection limit" is data

A detection limit says the instrument cannot distinguish the concentration from zero with confidence. It does not say the hour is missing: the air was clean, and the reported value (or a substituted one) is the best estimate. Treating those hours as gaps removes exactly the information that distinguishes a clean site from a moderately polluted one.

Why negative values are kept

Beta attenuation monitors and some gas analysers subtract a baseline; at low concentrations the noise straddles zero. The expected value of the noise is zero only if both sides are kept. Clipping to zero or deleting negatives makes the noise positive on average, which is a bias — and it is largest at clean sites, where most readings are near zero.

Why capture thresholds exist

A mean from part of the year inherits the seasonality of the part. For NO2, with a strong winter peak, a year missing its winter reads low. The EU's 90% rule (with allowances for calibration) makes annual statistics comparable between stations and years; below it, a value is at best indicative.

Months with NO2 data at London Eltham in 2023. London Eltham NO2, 2023 — 33% captureJanuarymeasured, 100%February–Marchno dataApril–Junemeasured, 85–100%July11%August–Decemberno dataA reference station reads 5% lower over these hours than over the year spatialworkflow.io
Report the months covered, not only the percentage.

Provisional data, instrument changes and imputation

  • Provisional data can later be ratified with changes — or deleted; recompute when the ratified set is published.
  • Instrument changes mid-year can show up as a step rather than a gap; check the unit or method field by month.
  • Validity 3 in EEA data (substituted value) should be kept but noted; the substituted value is usually half the detection limit.
  • Daily files from the EEA carry their own capture statistics; use them rather than recomputing from hourly when available.
  • Imputation is for modelling inputs, not for official statistics; never publish an imputed annual mean as a measured one.

FAQ

Should I delete negative air quality values?

No. Keep them in means; they are instrument noise around a low true value. Clip only for display or index lookups.

Which EEA validity codes are valid?

Codes 1, 2 and 3. Drop −1 and −99.

What data capture is needed for an annual mean?

90% of hours for EU compliance statistics (allowing for maintenance); many analyses flag anything below 75%.

What does "below detection limit" mean?

The concentration was too low to measure precisely. It is a valid, clean-air hour, not a missing one.

How do I handle a station with a big gap?

Report the capture and the months covered; if you must estimate, scale by a nearby reference station and say so.

Does it matter when in the year the gaps are?

Yes. Pollutants are seasonal, so a gap in winter and a gap in summer bias the mean in opposite directions.