Cleaning, deduping, reshaping: the unglamorous 80% of the job.
Code with its results
A notebook-style walk through the idea — every output shown is the
real result of the code above it.
Cleaning a small messy dataset with the standard library
Load a messy in-memory table, drop missing values, normalize types and formats, deduplicate on a key, and group into a summary, using only the Python standard library.
Real output Every Out block below was produced by
running the code above it. You can copy these cells into your own python3 and run them top to bottom to see the same numbers.
We start from a tiny messy table held in memory as a list of dicts and fix it one step at a time. Everything uses only the standard library: datetime to parse dates and statistics for the average, so no data libraries are assumed.
Some rows are missing a temperature. Take the simplest honest choice: drop any row whose temp is empty. The repr shows the stray casing and whitespace still waiting to be fixed.
In [2]
kept = [row for row in raw if row["temp"] != ""]
for row in kept:
print(row["id"], repr(row["city"]), row["temp"])
print("rows after dropping missing temp:", len(kept))
Now make equal things equal: trim and title-case the city, turn the temperature text into a real number, and rewrite every date into one canonical YYYY-MM-DD form.
In [3]
from datetime import datetime
def canon_date(text):
text = text.strip()
if "/" in text:
return datetime.strptime(text, "%m/%d/%Y").strftime("%Y-%m-%d")
return text
clean = []
for row in kept:
city = row["city"].strip().title()
temp = int(row["temp"])
date = canon_date(row["date"])
fixed = {"id": row["id"], "city": city, "temp": temp, "date": date}
clean.append(fixed)
for row in clean:
print(row)
Two rows now describe the same record on id 1. Deduplicate by keeping the first row seen for each id.
In [4]
seen = {}
for row in clean:
if row["id"] not in seen:
seen[row["id"]] = row
deduped = list(seen.values())
print("rows before dedup:", len(clean))
print("rows after dedup on id:", len(deduped))
for row in deduped:
print(row["id"], row["city"], row["temp"])
Out [4]
rows before dedup: 4
rows after dedup on id: 3
1 Austin 78
3 Dallas 81
4 Dallas 80
Finally, reshape by grouping: gather the rows for each city and collapse them into one summary row with a count and an average temperature.
In [5]
import statistics
groups = {}
for row in deduped:
groups.setdefault(row["city"], []).append(row["temp"])
for city in sorted(groups):
temps = groups[city]
print(city, "count", len(temps), "mean_temp", statistics.mean(temps))
From a messy pile to a clean, deduplicated, summarized table using only the standard library. That tidy table, not the raw file, is what any later step should build on.
The same ideas, as prose
These are the exact fragments the model serves — also available as an
ordered study guide.
The unglamorous eighty percent
The same small table before and after wrangling: on the left, mixed casing, a missing value, two date formats, and a duplicate row; on the right, one consistent, complete, deduplicated table.
Before a dataset can answer a question, someone has to make it fit to be asked. That preparation — cleaning up mistakes, removing duplicates, and reshaping the layout — is data wrangling, and it is the unglamorous majority of most real work with data.
The raw material almost never arrives ready. Values are misspelled or missing, the same thing is recorded two different ways, dates come in three formats, and a stray copy of a row sneaks in twice. Wrangling is the patient work of turning that pile into a clean, consistent table, like the prep work before cooking: washing, chopping, and measuring everything so the actual cooking goes smoothly, so that everything downstream is working from something trustworthy.
You will hear that this is eighty percent of the job. Treat the number as an industry aphorism, not a measured constant: surveys over the years have put the share of time spent preparing data anywhere from roughly forty-five to eighty percent, depending on who was asked and what they counted. The exact fraction is beside the point. The claim it makes is the durable part — the analysis everyone pictures rides on a mountain of tidying that no one talks about.
What real data looks like when it arrives
The first surprise of real data is that loading it is not the easy part. A CSV file that looks like a neat grid in a spreadsheet turns out to have a row with too few columns, a header that repeats halfway down, and cells padded with invisible spaces. A JSON file has a field that is sometimes a number and sometimes the string "N/A".
Every value you read starts life as text. The number 78 in a file is the two characters 7 and 8 until something converts it, and nothing converts it automatically. A column that should hold whole numbers will happily contain "78", " 78", "78.0", and "seventy-eight" all at once, and the file format has no opinion about that — a schema describes the shape a file should have, not the shape it actually does.
So loading is really the first act of wrangling, not a step before it. The goal of this stage is humble: get every record into memory as rows you can step through, and start a list of everything you already know is wrong. That list is the to-do for everything that follows.
The hole in the data
One column with a missing middle value handled three ways: drop the whole row, fill the gap with a stand-in average, or keep it absent and add a flag column marking it missing.
Sooner or later a cell is simply empty — a sensor that dropped a reading, a form field nobody filled in, a match that came up with nothing. A missing value is not a zero and not a blank string; it is the absence of information, and pretending otherwise is how quiet errors begin.
There are three honest responses, like a survey form left blank on one question: you can throw out the whole form, write in a reasonable guess, or mark it clearly as no answer. You can drop the whole record, which is safe when such records are few and scattered but throws away real data and can skew what remains if the missing ones have something in common. You can fill the gap with a stand-in — a default, or a typical value like the column's average — which keeps the row but invents a number that was never measured. Or you can flag it, leaving the value absent and adding a marker that says so, which is the most honest and the most work, because everything downstream must now handle the marker.
None of the three is correct in the abstract; the right choice depends on why the value is missing and what the data will be used for. What is never correct is silently letting a missing value become a 0 — that turns "we don't know" into a confident, wrong measurement.
Making the values agree
Two records can describe the same thing and still refuse to match, because the computer compares what is written, not what is meant. Austin and austin are different strings; 12/1/2024 and 2024-12-01 are different strings; 5 kg and 5000 g are different everything. Type coercion and normalization is the work of making equal things actually equal.
It has a few recurring chores. Text gets its stray whitespace trimmed and its casing made consistent, so a name is stored one way rather than five. Dates get parsed into a single canonical form — the international standard writes them as year, then month, then day, 2024-12-01, which has the happy side effect of sorting correctly as plain text. Numbers stored as text get converted to real numbers, and units get reconciled so a column means one thing all the way down. Categories that say the same thing in different words — USA, U.S.A., United States — get collapsed to one agreed spelling.
The reason to do this early is that every later step leans on it. Deduplication cannot tell that two rows are the same if their dates disagree on format; grouping scatters Austin and austin into separate buckets. Normalization is the boring foundation the interesting steps stand on.
The same thing twice
Four rows sharing an id key, two of them identical on id 1, collapse under deduplication on id into three distinct rows, one per key, with the repeated copy discarded.
The same real thing often appears in the data more than once — a record submitted twice, a log line written on a retry, two exports merged together. Duplicates inflate every count and skew every average, and the hard part is not removing them but deciding what "duplicate" means, like merging duplicate entries in a phone's contacts, where you first have to decide whether the name, the number, or both are what make two entries the same person.
Deduplication turns on a key: the field, or combination of fields, that defines identity. If a customer id is truly unique, two rows sharing one are the same customer, and you keep a single copy. But keys are often messier — two rows may match on a name and differ on a typo elsewhere, and only after normalization do near-duplicates line up enough to be caught. Once you have decided two rows are the same, you still have to choose which to keep: the earliest, the latest, or the most complete.
Done carefully, deduping collapses several rows into the one row that should have been there. Done carelessly, it either leaves duplicates that quietly double-count or deletes rows that only looked alike — which is why the key, and the rule for which copy survives, are decisions to make on purpose and write down.
Wide, long, and grouped
The same data in two layouts: a wide table with one row per city and a column per month, and a long table with one row per city-month-value combination; pivoting converts each into the other.
The same data can be laid out in more than one shape, and the shape decides what is easy to do next. Two layouts come up constantly. A wide table gives each subject one row and spreads its measurements across many columns — one column per month, say. A long table gives each measurement its own row, with columns that name the subject, the variable, and the value. The information is identical; only the arrangement differs.
Reshaping is pivoting between these. Going wide to long turns a forest of columns into a tall, uniform list that is easy to filter and combine; going long to wide turns that list back into a compact grid that is easy to read across. The other everyday reshaping move is grouping: gathering all the rows that share a key and collapsing each group into a single summary row — a count, a total, an average per category. That trades detail for a smaller table pitched at the level you actually need.
Choosing the shape is not busywork; it is setting up the next step so it can be done simply. A calculation that would take contortions in one layout falls out in a line from the other, which is why reshaping is a core wrangling skill rather than an afterthought.
Stitching tables on a key
Two tables joined on a shared id key: rows whose id matches on both sides combine into one wider row, while a customer whose id has no matching order is left out by an inner join.
Data you need is rarely all in one table. Orders live in one place and customers in another; sensor readings in one file and the calibration for each sensor in a second. Joining is combining two tables into one by matching rows on a shared key — the field they have in common, like a customer id that appears in both.
The interesting question is what happens to rows that do not match. An inner join keeps only rows that find a partner on both sides, dropping anything unmatched — the right choice when you want only complete pairings. A left join keeps every row from the first table and attaches partners where they exist, leaving blanks where they do not — the choice when the first table is the one you must not lose rows from. An outer join keeps everything from both sides, blanks and all. The join type is not a detail; it silently decides which rows live and which disappear.
That is also where joins go wrong. If the key is not unique on one side, matching rows multiply — one order against three duplicate customer rows becomes three orders — and a total that was right a moment ago is suddenly inflated. A join is only as trustworthy as the key it runs on, which is one more reason the cleaning comes first.
Do no harm to the data
Every wrangling step changes the data, and every change can quietly make it wrong. The discipline that separates careful work from damage is simple to state and easy to skip: never overwrite your only copy of the raw data, and write down every decision you made to get from it to the clean version. If you dropped the rows missing a temperature, say so; if you filled them instead, say that. A clean dataset with no record of how it was cleaned is a result no one can check.
The reason to be this careful is that wrangling comes before the part everyone remembers. The cleaned table is the input to every chart, model, and conclusion that follows — actually studying what the data says is a separate skill for later, and it can only ever be as trustworthy as the wrangling underneath it. A silent mistake here does not announce itself; it just shows up later as a confident answer that happens to be false.
This is easiest to feel on data you gathered yourself. Collect a few minutes of driving telemetry from a small robot car — timestamps, speeds, distance readings — and the mess is immediate and personal: frames that dropped, a sensor that glitched to an impossible value, the same reading logged twice. Cleaning your own dropped frames and duplicates, and keeping the raw log untouched beside the tidy one, teaches the habit better than any spotless example can, because you know exactly what really happened.