Definition
Our air-quality sensors went offline for a week in July, one reported 999 for three hours, and timestamps switched from 24-hour to 12-hour format halfway through. Real data is messy. Collecting and cleaning it usually takes more time than building the model, and decides how good the model can be.
Data collection gathers the raw data a project needs from sources such as public portals, sensors, surveys, APIs or existing records, while noting where it came from, its licence and any consent required. Data cleaning turns raw data into a reliable table: fixing types and timestamps, removing duplicates, detecting and handling missing values and impossible readings, aligning different sources to the same time steps, and creating features. Every decision is recorded so that it can be repeated on new data and explained to others.
In simple words
Cooking with vegetables from the market means sorting out the rotten ones, washing off the mud and cutting everything to size before it goes in the pot. Data cleaning is the same preparation for a model.
Overview
- A capstone project's quality depends most on its data.
- Data can come from public datasets, surveys, sensors, web APIs or your own records.
- Collection must respect consent, privacy, licences and website terms.
- Cleaning fixes missing values, duplicates, wrong types, inconsistent labels and outliers.
- Data scientists often say cleaning takes most of a project's time.
- Every cleaning step should be written in code and recorded, so it can be repeated.
Why it matters
"Garbage in, garbage out": a model trained on faulty readings learns faults. Careful cleaning often improves results more than a fancier algorithm, and documented steps make the project trustworthy and repeatable.
Key terms
- Data source
- Where data comes from, such as a government portal, a sensor network or a survey.
- Resampling
- Changing the time step of data, such as averaging hourly readings into daily values.
- Interpolation
- Estimating a missing value from neighbouring values in time.
- Outlier
- A value far from the rest; it may be a real event or an error.
- Feature engineering
- Creating useful inputs, such as yesterday's PM2.5 or the month.
- Data leakage
- Accidentally using information that would not be available at prediction time.
Types
There are several types of data source and several types of cleaning task.
1. Public and open datasets
Ready-made datasets published for anyone to use.
- How it works: Download from portals such as data.gov.in, Kaggle or the UCI repository, then read the licence and documentation.
- Example: Using rainfall and crop-yield data from data.gov.in for a farming project.
- When to use it: Use them when they match your problem; they save time and allow comparison with others' results.
2. Primary data: surveys, sensors and manual collection
Data you gather yourself for the project.
- How it works: Design questions or sensors carefully, collect consent, pilot on a few people and record how data was gathered.
- Example: Recording air-quality readings near a school every hour for a month.
- When to use it: Use it when no suitable dataset exists or your local context matters.
3. Handling missing values and duplicates
Finding and fixing empty cells and repeated rows.
- How it works: Check df.isna().sum() and df.duplicated(); drop, fill with the median or mode, or add a "was missing" flag.
- Example: Filling 12 missing ages with the median age and removing 30 duplicate survey responses.
- When to use it: Use it on every dataset before modelling, noting how many rows were changed.
4. Fixing types, formats and inconsistent labels
Making sure each column has the right type and consistent values.
- How it works: Convert text numbers and dates with pd.to_numeric and pd.to_datetime; standardise spellings and capitalisation.
- Example: Merging "Delhi", "delhi" and "New Delhi " into one city label.
- When to use it: Use it whenever data comes from forms or several sources.
5. Outliers and validity checks
Finding values that are extreme or impossible.
- How it works: Use summary statistics, box plots and rules such as "age between 0 and 120"; fix errors and keep genuine extremes.
- Example: Finding a recorded height of 1,700 cm that should be 170 cm.
- When to use it: Use it before training, because a few bad values can distort a whole model.
Explained in detail
Collecting data for the air-quality project
India's Central Pollution Control Board publishes readings from continuous monitoring stations, and state pollution boards and portals provide historical data; weather data such as temperature, wind speed, humidity and rainfall is available from the India Meteorological Department and open weather services. For each source we record its URL, the date downloaded, the licence or terms of use, the station locations and the units. We also note gaps: stations are often concentrated in wealthier central areas, so the data may say little about pollution in industrial outskirts or villages. Where we would need to collect data from people, such as surveys of school staff, we obtain informed consent and collect only what is needed.
A cleaning checklist
| Problem | Example | Fix |
|---|---|---|
| Wrong types | Dates stored as text; numbers with commas | Parse dates and convert to numbers |
| Duplicates | The same hour recorded twice after a re-upload | Drop exact duplicates by timestamp and station |
| Impossible values | PM2.5 of 999 or negative values from a faulty sensor | Mark as missing; keep real high episodes |
| Missing values | A station offline for a week | Interpolate short gaps; leave long gaps missing or drop those days |
| Different time steps | Hourly pollution, daily weather | Resample to daily averages and join on date |
| Leakage risk | Using tomorrow's wind to predict tomorrow's PM2.5 | Use only forecasts or values known by 6 p.m. today |
Features for forecasting
For a next-day forecast, useful features include today's PM2.5 and the average of the last three days, because pollution persists; today's wind speed and humidity, because calm, humid winter air traps particles; the month or season; and whether tomorrow is a known high-risk day, such as Diwali. Distinguishing real extreme events from sensor errors is important: a sudden spike to 400 on Diwali night is real and is exactly what the model must learn, while a flat value of 999 for three hours is a fault. Plot the data before and after each cleaning step, count how many values each step changes, and keep the cleaning code in a script or notebook so the whole process can be rerun when new data arrives.
How it works
- Find sources, record their URLs, licences, units and gaps.
- Load the data and fix types, timestamps and duplicates.
- Mark impossible values as missing; keep genuine extremes.
- Fill short gaps, align time steps and join sources.
- Create features using only information available at prediction time, and save the cleaning script.
In code
import numpy as np
import pandas as pd
rng = np.random.default_rng(7)
hours = pd.date_range("2025-01-01", periods=24 * 20, freq="h")
base = 120 + 40 * np.sin(np.arange(len(hours)) / 24 * 2 * np.pi / 7) # weekly rhythm
raw = pd.DataFrame({"time": hours.astype(str), "pm25": (base + rng.normal(0, 15, len(hours))).round(1)})
raw.loc[50:52, "pm25"] = 999 # a stuck sensor
raw.loc[200:247, "pm25"] = np.nan # two days offline
raw.loc[300, "pm25"] = -5 # an impossible negative
raw = pd.concat([raw, raw.iloc[[10, 11]]]) # duplicated rows
print("Raw rows:", len(raw))
df = raw.drop_duplicates().copy()
df["time"] = pd.to_datetime(df["time"])
bad = (df["pm25"] < 0) | (df["pm25"] >= 999)
print("Impossible readings marked missing:", int(bad.sum()))
df.loc[bad, "pm25"] = np.nan
print("Missing hourly values:", int(df["pm25"].isna().sum()))
df = df.set_index("time").sort_index()
df["pm25"] = df["pm25"].interpolate(limit=6) # fill gaps up to 6 hours only
daily = df["pm25"].resample("D").agg(["mean", "count"])
daily.loc[daily["count"] < 18, "mean"] = np.nan # need 18+ hours for a valid day
print("Days kept:", int(daily["mean"].notna().sum()), "of", len(daily))
features = pd.DataFrame({"pm25_today": daily["mean"]})
features["pm25_3day"] = features["pm25_today"].rolling(3).mean()
features["target_tomorrow"] = features["pm25_today"].shift(-1) # what we predict
print(features.dropna().round(1).head(4))Output
Raw rows: 482
Impossible readings marked missing: 4
Missing hourly values: 52
Days kept: 17 of 20
pm25_today pm25_3day target_tomorrow
time
2025-01-03 153.2 145.6 122.9
2025-01-04 122.9 143.2 87.1
2025-01-05 87.1 121.1 78.3
2025-01-06 78.3 96.1 98.9Try it yourself
The Diwali spike
While cleaning, the team finds PM2.5 readings above 400 on one November night.
Keep, fix or remove?
Decide for each data problem.
A reading of −12
A two-hour gap in hourly readings
Dates written as text "03-01-2025"
The same hour recorded twice
PM2.5 of 420 on Diwali night, confirmed by nearby stations
Common mistakes
- Deleting all extreme values, including real events.
- Filling gaps with information from the future.
- Cleaning by hand in a spreadsheet with no record of the steps.
Check yourself
Summary
- Collect data from documented sources, noting licences, units, consent and gaps.
- Fix types and duplicates, mark impossible values, fill short gaps and align time steps.
- Keep real extremes, avoid leakage, and script every step so it can be repeated.
Finished reading?
Sources: pandas: time series and date functionality · Central Pollution Control Board: National Air Quality Index