Data Profiling, Validation & Cleaning

Field data is produced by people and devices under pressure: enumerators typing prices on a phone at a busy market, sensors dropping packets, spreadsheets merged by hand. Every one of those steps introduces a characteristic kind of error, and a data engineer's first job is to name them before fixing anything:

The point of the table is that each error type has a different fix. Treating them all as "dirty data, drop it" throws away information and hides the real problem from whoever produced the data.

Profiling measures the data without changing it: row count, column types, null rate per column, number of distinct values (cardinality), min/max and quantiles. A profile turns "the data looks messy" into numbers like 4.2% of prices are missing and 38 rows are negative — numbers you can put in a report and check again next week.

Validation checks hard rules — the data contract: required columns exist, date parses as ISO-8601, price_ugx > 0, rainfall_mm is between 0 and 500, and (date, market, commodity) is unique. Validation answers "may this data enter the pipeline?" A failed contract should stop the pipeline loudly rather than let bad rows leak downstream.

Cleaning applies deterministic, documented fixes: standardise strings (strip().title()), parse dates, drop rows missing any part of the business key, de-duplicate on that key, cap or reject impossible values, and impute the rest.

Doing these in order matters. If you clean before profiling, you can't say how dirty the data was. If you validate before standardising strings, " owino" fails a membership check it should pass.

Imputation replaces a missing value with a best guess. The question is which guess.

• The global mean mixes every commodity together. Beans (~3,800 UGX/kg) and maize (~1,800 UGX/kg) average to a price that is wrong for both.
• The mean is also sensitive to outliers. One typo of 97,000 instead of 9,700 drags it far from the typical value.
• The median is the middle value of the sorted data, so a single extreme value can't move it much — it is robust. Computing it per commodity (groupby("commodity")) keeps each commodity's own price level.

df["price_ugx"] = df.groupby("commodity")["price_ugx"].transform(lambda s: s.fillna(s.median()))

Imputation is still a guess. Good pipelines add a flag column (e.g. price_imputed = True) so analysts and models can tell real observations from filled ones.

The rule is simple: never modify data/raw/. Read from it; write cleaned outputs to data/processed/.

Why it matters in practice:

1. Reproducibility. Anyone can rerun the pipeline from the same inputs and get the same outputs.
2. Recoverability. Two weeks later you discover the cleaning script dropped valid rows. If you overwrote the raw file, that data is gone. If you didn't, you fix the script and rerun.
3. Auditability. You can always show exactly what came in and what each step did to it.

In exams, pair this with the IQR rule: values outside [Q1 − 1.5·IQR, Q3 + 1.5·IQR] (IQR = Q3 − Q1) are flagged as outliers. Whether you drop, cap or keep them is a decision you justify — not something the formula decides for you.