Assignment Sample: Data Cleaning Pipeline for UK Weather Records

Model Answer

Introduction

Raw environmental sensor data is rarely analysis-ready: automatic weather stations routinely produce missing readings during power or transmission faults, duplicated rows when a logger re-sends a batch, and physically implausible values when a sensor drifts or fails outright (Rahm and Do, 2000). Analysing such data without first cleaning it risks badly distorted summary statistics, since a single faulty reading can shift a mean far more than it would a median. This assignment designs and documents a Python data-cleaning pipeline, built with the pandas library, for a sample of daily temperature and rainfall records from a UK weather station. Each cleaning step is justified with a worked numerical calculation, so that the effect of the step on the underlying statistics can be verified rather than simply asserted. The dataset used throughout is a single UK station rather than a national network, deliberately kept small enough that every intermediate calculation in the sections below can be checked by hand against the value pandas would compute, a habit worth building before a pipeline is trusted at a larger scale (VanderPlas, 2016).

Approach and Method

The pipeline follows five sequential stages, a structure broadly consistent with established data-cleaning process models (Kelleher and Tierney, 2018): (1) parse and standardise the raw file, converting date strings to datetime objects and readings to numeric types; (2) remove exact duplicate rows; (3) apply a physical plausibility check, using domain-known bounds for UK daily temperature and rainfall, and convert any value outside those bounds to a missing value rather than deleting the row outright, so the date is preserved; (4) impute missing values, including those newly created in step 3, using a centred rolling median; and (5) apply a statistical outlier check using the interquartile range (IQR) to flag values that are unusual but not physically impossible, for manual review rather than automatic correction. Steps 3 and 5 are kept deliberately separate because they answer different questions: a physical bound check asks “is this value possible?”, while an IQR check asks “is this value typical, given the rest of the sample?” (Tukey, 1977). This ordering also reflects a broader principle in defensive data engineering: domain knowledge, such as the fact that a daily mean temperature cannot exceed 35°C in the UK, should always be applied before purely statistical rules, because a statistical method such as the IQR has no concept of physical plausibility and would, on its own, be equally willing to flag a genuinely hot day as it would a faulty sensor (Met Office, 2023).

Worked Analysis: Missing Values and the Physical Bound Check

Consider ten consecutive daily mean temperature readings (°C) from the station: Day 1 = 12.4, Day 2 = 13.1, Day 3 = 12.8, Day 4 = missing (logging gap), Day 5 = 13.5, Day 6 = 12.9, Day 7 = 45.7, Day 8 = 13.0, Day 9 = 12.6, Day 10 = 13.2. Day 7’s reading of 45.7°C exceeds a plausible UK daily mean temperature bound of −10°C to 35°C by a wide margin and is treated as a sensor fault rather than a genuine record; it is therefore converted to missing.

Both missing values are then imputed using a centred rolling median with a window of the four nearest valid neighbours. For Day 4, the neighbours are Day 2, 3, 5 and 6: [13.1, 12.8, 13.5, 12.9]. Sorted, this is [12.8, 12.9, 13.1, 13.5]; with an even count of four values, the median is the mean of the two central values: (12.9 + 13.1) ÷ 2 = 13.0°C. For Day 7, the neighbours are Day 5, 6, 8 and 9: [13.5, 12.9, 13.0, 12.6]. Sorted, [12.6, 12.9, 13.0, 13.5], median = (12.9 + 13.0) ÷ 2 = 12.95°C. A rolling median is used rather than the column mean because it is far less sensitive to any remaining nearby anomalies and better preserves short-term local trends in the time series (McKinney, 2022). The advantage is not merely theoretical: had a rolling mean of the same four Day 7 neighbours been used instead of the median, the result would have been identical here, (13.5 + 12.9 + 13.0 + 12.6) ÷ 4 = 12.95°C, because none of the four neighbours is itself extreme; the difference becomes material only when a second anomalous value falls inside the imputation window, in which case a mean would be pulled towards it while a median would largely ignore it. Because sensor faults are rarely perfectly isolated in real station data, the median is the safer general-purpose default even when, as here, both methods happen to agree.

The effect of these two steps on the summary statistic is substantial. The raw mean of the nine non-missing values, including the 45.7°C fault, is (12.4 + 13.1 + 12.8 + 13.5 + 12.9 + 45.7 + 13.0 + 12.6 + 13.2) ÷ 9 = 149.2 ÷ 9 = 16.58°C. After imputation, the cleaned mean of all ten values is (12.4 + 13.1 + 12.8 + 13.0 + 13.5 + 12.9 + 12.95 + 13.0 + 12.6 + 13.2) ÷ 10 = 129.45 ÷ 10 = 12.95°C. The single sensor fault had inflated the raw mean by 3.63°C (16.58 − 12.95), which demonstrates why an unchecked mean is unsafe to report directly from raw sensor data.

Worked Analysis: Statistical Outlier Detection With the IQR Method

A separate rainfall sample (mm/day) is used to illustrate the IQR method, since rainfall values are all physically plausible (no reading violates a hard bound) but one is still statistically unusual: [0.0, 0.2, 0.4, 0.6, 0.8, 1.0, 1.2, 1.6, 2.0, 2.4, 18.5], already sorted, n = 11. The median (Q2) is the 6th of 11 sorted values = 1.0. Q1 is the median of the lower five values [0.0, 0.2, 0.4, 0.6, 0.8], the 3rd value = 0.4. Q3 is the median of the upper five values [1.2, 1.6, 2.0, 2.4, 18.5], the 3rd value = 2.0. The interquartile range is therefore IQR = Q3 − Q1 = 2.0 − 0.4 = 1.6.

Tukey’s rule flags any value above Q3 + 1.5×IQR or below Q1 − 1.5×IQR as a statistical outlier (Tukey, 1977). The upper fence is 2.0 + (1.5 × 1.6) = 2.0 + 2.4 = 4.4 mm. The final reading of 18.5 mm exceeds this fence and is flagged. Critically, this value is not automatically deleted or overwritten: because rainfall genuinely can spike far above a typical day during a storm, the pipeline logs the flag for manual review against nearby station or radar data rather than silently imputing it, which would risk discarding a real extreme-weather event (Chapman et al., 2000). For completeness, the lower fence is Q1 − 1.5×IQR = 0.4 − 2.4 = −2.0 mm; since rainfall cannot be negative, no value in this sample could ever trigger the lower bound, illustrating that the two fences are not always equally meaningful for every variable. An alternative z-score method, which flags values more than roughly three standard deviations from the mean, was considered but rejected here: rainfall distributions are strongly right-skewed rather than approximately normal, so the mean and standard deviation are themselves distorted by the same extreme values the method is trying to detect, whereas the median-based IQR approach is robust to this distortion by construction (Wickham and Grolemund, 2017).

Worked Analysis: Detecting the Duplicated Row

The raw file also contained one duplicated record: a second copy of the Day 5 row (13.5°C, 0.8 mm rainfall) appeared later in the file, identical in every column to the first. Testing for duplication on the whole row, rather than on the date column alone, matters here: two different stations reporting the same date would look identical on a date-only check, whereas a full-row comparison correctly identifies this case as a duplicate transmission only because every field, including both readings, matched exactly. Using pandas’ drop_duplicates() on the full row, the repeated Day 5 entry was removed, reducing the raw table from eleven rows to the ten values used in the worked mean calculation above. Left uncorrected, the duplicate would have doubled Day 5’s contribution to any subsequent daily mean or trend calculation, silently biasing the result towards that day’s specific weather without a corresponding increase in genuine sample size. It is worth distinguishing this case from a superficially similar one: two neighbouring stations reporting the same date with slightly different temperature and rainfall values are not duplicates and must not be dropped, since each row then represents genuinely independent information. Full-row equality, rather than a match on the date column alone, is therefore the correct duplication test specifically because a true duplicate is expected to be identical in every field, not merely in the timestamp it shares with other legitimate records.

Implementation Overview

The core of the pipeline, implemented with pandas, is summarised below in simplified form:

df = pd.read_csv("station_raw.csv", parse_dates=["date"])
df = df.drop_duplicates(subset=["date"])

TEMP_MIN, TEMP_MAX = -10.0, 35.0
implausible = ~df["temp_c"].between(TEMP_MIN, TEMP_MAX)
df.loc[implausible, "temp_c"] = pd.NA

df["temp_c"] = df["temp_c"].fillna(
    df["temp_c"].rolling(window=5, center=True, min_periods=1).median()
)

q1, q3 = df["rainfall_mm"].quantile([0.25, 0.75])
iqr = q3 - q1
upper_fence = q3 + 1.5 * iqr
df["rainfall_flag"] = df["rainfall_mm"] > upper_fence

Each stage writes its own boolean flag column (for example rainfall_flag) rather than deleting rows in place, so that every automated decision remains traceable and reversible during quality review – a practice recommended for reproducible data-cleaning workflows (McKinney, 2022; Wickham, 2014). Vectorised pandas operations such as between() and rolling() are used throughout in preference to explicit Python for loops, both because they are considerably faster on realistic station-sized datasets and because they make the cleaning logic easier to read as a short sequence of named steps rather than as nested loop bodies (VanderPlas, 2016).

Before/After Data Quality Summary

Table 1. Summary of cleaning actions and resulting means for the two worked samples.

Evaluation and Discussion

The two worked examples show why a single blanket rule is unsuitable for cleaning environmental sensor data. A hard physical bound is appropriate for temperature because a value such as 45.7°C in a UK context is essentially certain to be a sensor fault rather than a genuine reading, so automatic correction is defensible. Rainfall, by contrast, has no equivalent hard ceiling – a genuinely extreme storm can legitimately produce a very high reading – so the IQR method is used only to flag, not to silently correct, preserving analyst judgement for borderline cases (Rahm and Do, 2000). A limitation of the rolling-median imputation approach used here is that it assumes short-term temporal continuity, which holds reasonably well for temperature over a few days but would be less reliable for a variable with more day-to-day volatility or during a rapidly changing weather system; a more robust production pipeline might instead compare readings against a neighbouring station or a numerical weather model as an additional cross-check (Kelleher and Tierney, 2018).

A further consideration, not shown in the code excerpt above for brevity, is validation: a production-grade version of this pipeline would include automated tests, for example using pytest, that assert no unexpected missing values remain after imputation, that every flagged row is logged with a reason code rather than silently dropped, and that the final row count matches the expected reduction after de-duplication. Such tests turn the cleaning rules documented in this assignment into checks that would fail loudly if a future change to the raw file’s format broke an assumption, rather than allowing a subtly corrupted dataset to reach downstream analysis unnoticed. A further limitation worth acknowledging is the fixed rolling-window size of five days used for imputation: too narrow a window risks having too few valid neighbours around a genuine gap in the data, while too wide a window risks smoothing over real short-term weather variation rather than only correcting faults. The value chosen here was a pragmatic compromise for daily station data and would need re-testing against a longer historical record before being adopted as a fixed default for a production pipeline (Kelleher and Tierney, 2018).

Conclusion

This assignment has designed a five-stage Python data-cleaning pipeline for UK weather station data and justified each stage with a fully worked numerical example. A physical bound check correctly identified and removed a 45.7°C sensor fault, after which centred rolling-median imputation restored the daily mean from a distorted 16.58°C to a credible 12.95°C. A separate IQR calculation on rainfall data correctly derived Q1 = 0.4, Q3 = 2.0 and an upper fence of 4.4 mm, flagging an 18.5 mm reading for manual review rather than automatic deletion. Together, these worked calculations demonstrate that effective data cleaning depends on distinguishing physically impossible values, which can be corrected automatically, from statistically unusual but genuinely possible values, which should be flagged for human judgement.

References

• Chapman, P., Clinton, J., Kerber, R., Khabaza, T., Reinartz, T., Shearer, C. and Wirth, R. (2000) CRISP-DM 1.0: Step-by-Step Data Mining Guide. Chicago: SPSS Inc.
• Kelleher, J.D. and Tierney, B. (2018) Data Science. Cambridge, MA: MIT Press.
• McKinney, W. (2022) Python for Data Analysis. 3rd edn. Sebastopol: O’Reilly Media.
• Met Office (2023) UK Climate Averages and Observational Data. Exeter: Met Office.
• Rahm, E. and Do, H.H. (2000) ‘Data cleaning: problems and current approaches’, IEEE Data Engineering Bulletin, 23(4), pp. 3–13.
• Tukey, J.W. (1977) Exploratory Data Analysis. Reading, MA: Addison-Wesley.
• VanderPlas, J. (2016) Python Data Science Handbook. Sebastopol: O’Reilly Media.
• Van Rossum, G. and Drake, F.L. (2009) Python 3 Reference Manual. Scotts Valley: CreateSpace.
• Wickham, H. (2014) ‘Tidy data’, Journal of Statistical Software, 59(10), pp. 1–23.
• Wickham, H. and Grolemund, G. (2017) R for Data Science. Sebastopol: O’Reilly Media.