Make messy data useful
Clean tables, combine records, and answer useful questions with pandas. This module has 3 lessons and takes about 1h 15m. Each lesson has a practice exercise that runs real Python in your browser and a short quiz.
Start module 08 in the Learning LabClean a small transaction table
Turn synthetic transaction text into a clean numeric column without hiding missing values.
A pandas DataFrame is a table with named columns. Select a column with df['amount']. Real tables often contain extra spaces, duplicate records, and values that are not numbers. Inspect these problems before calculating a total.
pd.to_numeric(..., errors='coerce') turns invalid entries into missing values. Here we remove a duplicate transaction and keep missing amounts visible. Missing is different from zero. These synthetic transactions teach data cleaning, not financial decisions.
See it in action
import pandas as pd
# Synthetic transactions for a data-cleaning exercise.
df = pd.DataFrame({
"id": [1, 2, 2, 3, 4],
"amount": ["100", " 250 ", " 250 ", "unknown", "75"],
})
df = df.drop_duplicates(subset="id").copy()
df["amount"] = pd.to_numeric(df["amount"].str.strip(), errors="coerce")
print(df.to_string(index=False))
print("Missing amounts:", df["amount"].isna().sum())
print("Known total:", df["amount"].sum())Clean visibly: count duplicates and missing values before trusting a total.
Join meters to sites
Combine two synthetic tables and calculate energy use per site.
Meter readings and site names may live in separate tables. A merge matches rows using a shared key, such as meter_id. A left join keeps every reading, including readings with no known site.
validate='many_to_one' checks that each meter id has at most one site record. Without that check, duplicate lookup records can silently multiply energy totals. After joining, use groupby to collect rows by site and sum their kWh.
See it in action
import pandas as pd
# Synthetic meter readings and site lookup.
readings = pd.DataFrame({"meter_id": [1, 1, 2, 3], "kwh": [12, 10, 8, 6]})
sites = pd.DataFrame({"meter_id": [1, 2], "site": ["Workshop", "Library"]})
joined = readings.merge(sites, on="meter_id", how="left", validate="many_to_one")
print(joined.to_string(index=False))
print("Unmatched readings:", joined["site"].isna().sum())
summary = joined.groupby("site", dropna=False)["kwh"].sum()
print(summary)Check join keys and unmatched rows before aggregating merged data.
Ask better questions of a table
Summarize synthetic appointment waiting times by service and inspect the sample counts.
Start exploration with a clear question: which service has the longest typical wait in this sample? groupby().agg() can calculate count, mean, and median together. The median is the middle value, so one long wait affects it less than the mean.
Keep the count beside each summary. Two appointments give much less evidence than two hundred. These invented records are for learning only. A difference in this small table does not establish service quality or a cause of delays.
See it in action
import pandas as pd
# Synthetic appointments. No patient records or medical guidance.
visits = pd.DataFrame({
"service": ["Lab", "Lab", "Lab", "Imaging", "Imaging", "Imaging"],
"wait_min": [10, 12, 70, 20, 22, 24],
})
summary = visits.groupby("service")["wait_min"].agg(["count", "mean", "median"])
print(summary.round(1))
print("Long waits:")
print(visits.loc[visits["wait_min"] > 30].to_string(index=False))Pair a useful summary with sample counts and a clear question.
Free, open and run by a nonprofit.
This page is part of Python to AI, a free course of 20 modules and 60 lessons in the engineering.fit( ) Learning Lab. The Learning Lab is built and run by engineering.fit( ), a nonprofit founded by Abdullateef Opeyemi Bakare that helps African engineering students gain research experience. You do not need an account or pay a fee to learn.