Data Wrangling: A Practical Guide to Cleaning Real Data

Every dataset you'll ever load has been touched by messy reality before it reaches you: a sensor that dropped a reading, a form field a customer skipped, a spreadsheet someone copy-pasted twice. Data wrangling — also called data cleaning or data munging — is the process of taking that raw data and making it ready for analysis. It's unglamorous work, but it's also the work that determines whether every chart and model you build afterward can be trusted at all.

This guide works through a real, messy dataset end to end: a Melbourne housing snapshot with the kind of problems every analyst runs into — missing values scattered unevenly across columns, rows that shouldn't be there twice, and a date column crammed into a single text field. Throughout, you'll find visual diagrams showing exactly what's happening to the data, because wrangling is a lot easier to reason about when you can see the shape of the problem.

What Is Data Wrangling?

Data as it's collected is almost never analysis-ready. It typically has some combination of the following problems:

• Missing values — empty cells, blanks, or placeholder codes standing in for data that was never recorded
• Incorrect format — numbers stored as text, dates stored as free-form strings, categories stored as inconsistent casing
• Duplicate values — the same record appearing more than once, often from merged exports or repeated form submissions
• Incorrect data — typos, impossible values (a negative age, a house with 40 bathrooms), or unit mismatches
• Unnecessary data — columns or rows that don't serve the task at hand and just add noise
• Improper column names — inconsistent casing, spaces, or names that don't describe what the column actually holds

Data wrangling is the job of finding each of these problems and deciding, deliberately, what to do about it. The rest of this guide focuses on the two issues that eat the most time in practice — missing values and duplicates — plus the common task of reshaping a messy column into something usable.

Part 1: Understanding Missing Data

Data goes missing for all kinds of reasons: the observation genuinely doesn't exist, it existed but was never recorded, or it was recorded and then lost or corrupted somewhere along the way. Before you can fix missing data, it helps to recognize the disguises it shows up in.

How Missing Data Shows Up

Missing values aren't always a clean, empty cell. In real datasets, they hide behind:

• Explicit markers like NA, N/A, or null
• A genuinely blank cell
• Dashes used as filler, like - or --
• Sentinel values that look like real numbers but mean "missing", like -1 or 999

That last category is the dangerous one. A blank cell announces itself. A 999 sitting in an "age" column looks like real data until you compute an average and wonder why it's 40 years too high.

What You Can Do About It

Broadly, you have two families of response once you've found missing data:

1. Delete the observations or columns with missing or invalid values.
2. Substitute them with a suitable filled-in value (imputation).

Alongside that, you'll also decide whether to keep or drop outliers depending on whether they represent real, valid extremes or errors, and whether to remove duplicate records that shouldn't be counted twice.

The Toolkit

Pandas gives you a small set of methods that cover almost every missing-data situation. For identifying what's missing:

For filling what's missing:

• Fill with a constant value
• Fill with the mean or median of the column, for numerical data
• Fill with the mode (the most frequent value), for categorical data
• Advanced imputation with KNN or Iterative imputers, which use other columns to make a smarter guess
• For time-series data specifically: forward fill (ffill, carry the last known value forward) or backward fill (bfill, pull the next known value backward)

Keep one thing in mind as you work through this: deleting and filling both cost you something. Delete too aggressively and you lose real data, possibly leaving too little to draw reliable conclusions from. Fill too aggressively and you're injecting assumed values into your dataset — values you made up, dressed as if they were observed. Every decision below is a trade-off between those two costs, not a search for a rule that avoids the trade-off entirely.

Part 2: The Four Types of Missing Data

Not all missing data behaves the same way, and treating it as if it does is one of the most common mistakes in data cleaning. Statisticians classify missingness into four types, and which one you're dealing with should genuinely change what you do next.

Structurally Missing Data

This is data that's missing for a completely logical reason — there was never anything to record. A survey column called number_of_pregnancies will be structurally missing for every male respondent. A field for spouse_name will be structurally missing for anyone who's unmarried. There's no mystery to solve here: the right move is usually to leave it missing (or encode it explicitly as "not applicable") rather than to impute a value that was never meant to exist.

Missing Completely at Random (MCAR)

Data is MCAR when the probability of any given data point being missing is exactly the same for every observation, with no pattern to it at all. This is mostly a theoretical ideal: data is genuinely MCAR only if every potential observation had an equal chance of going missing for that variable, with no logic, outside force, or hidden behavior driving it. A sensor that drops one reading in a thousand due to a random hardware glitch, unrelated to anything about the reading itself, is a reasonable real-world approximation.

Missing at Random (MAR)

MAR is probably the trickiest of the four to get an intuition for. Data is MAR when the likelihood of it being missing differs across groups, but is equally likely for every member within a group. It's a hybrid of "missing for a reason" and "missing completely at random".

Here's a real, documented case of it: in a study tracking ambulance drivers' BMI over time (Heitjan, 1997, American Journal of Public Health), participants with a high, embarrassing BMI at their first measurement were less likely to come back for the follow-up measurement later, regardless of whether their weight had actually gone up or down in the meantime. That's MAR, not MNAR, because the missingness is explained by something already observed — the earlier BMI reading — not by the follow-up value itself. The key practical implication is that MAR data can often be validly imputed if you condition on the variable that explains the pattern (here, the earlier BMI reading), because the missingness isn't secretly tied to the unobserved follow-up value once you've accounted for that.

Missing Not at Random (MNAR)

MNAR is the hardest case, and the one most likely to quietly bias your analysis. Data is MNAR when the probability of a value being missing depends on the value itself — a value you can't see, precisely because it's missing.

The classic example: in an income survey, people with very high incomes are more likely to decline to answer than people with average incomes. The missingness isn't random, and it isn't explained by some other observed variable the way MAR is — it's driven directly by the unobserved number. This matters because there's no clever conditioning trick that fixes it: if you impute the missing incomes using the mean of what people did report, you'll systematically underestimate the true average, because the highest earners are exactly the ones missing from that mean. Recognizing MNAR is often more valuable than trying to "solve" it with imputation — at minimum, it should make you flag the bias explicitly rather than paper over it.

   STRUCTURAL           MCAR                  MAR                     MNAR
   "was never there"    "pure chance"         "chance depends on      "chance depends on the
                                               something you CAN      value itself, which
                                               observe"               you CAN'T observe"

   pregnancies for    →  sensor glitch,     →  BMI group predicts   →  high earners refuse
   a male respondent     unrelated to           who skips the           to report income
                          the reading            weight question

   → leave as N/A      → safe to delete/     → impute, conditioning → flag the bias; naive
     or "not             impute, low risk      on the group that       imputation will
     applicable"                               drives the pattern      understate the truth

Part 3: Finding What (and Where) Is Missing

isnull() and notnull() are the two workhorses for locating missing data. isnull() returns a boolean (True/False) mask that's the same shape as whatever you called it on, and notnull() is simply its inverse.

It's important to treat missing values in each column separately, rather than reaching for one blanket fix (like "replace every NaN with the column mean") and applying it everywhere. A missing price and a missing council area call for very different treatment, as you'll see shortly.

Filtering Rows by a Column

A very common pattern is to use one column's missingness to filter the rows of an entire DataFrame. This relies on positional column indexing, which works cleanly when a DataFrame's columns default to 0, 1, 2... — typically because it was loaded without a header row:

raw = pd.DataFrame([
    ['Name', 'Age', None, 'City'],
    ['Alice', 25, None, 'NYC'],
    ['Bob', 30, None, 'LA'],
])
raw

raw[0]                   # the first column, by position
raw[0].notnull()         # True/False for each row: is column 0 non-null there?
raw[raw[0].notnull()]    # keep only the rows where column 0 has a real value

Here every row in column 0 happens to be filled, so this particular filter keeps everything. The pattern is the same regardless: df[N] pulls out a column by position, calling .notnull() on it turns that column into a True/False mask, and feeding that mask back into df[...] keeps only the matching rows.

Filtering Columns by a Row

The mirror-image pattern filters columns based on which ones have a real value in a specific row. This shows up constantly with messy spreadsheet exports where the real header information got dumped into the first data row instead of the column names:

raw.loc[0]                              # the first row, by label
raw.loc[0].notnull()                    # True/False for each column: is row 0 non-null there?
raw.loc[:, raw.loc[0].notnull()]        # keep only the columns where row 0 has a real value

   raw.loc[0]  →  Name   Age   None   City
                  True  True  False  True    ← raw.loc[0].notnull()

   raw.loc[:, raw.loc[0].notnull()]  keeps columns 0, 1, 3 — drops the one
   where row 0 itself was empty (the "None" column above)

Both patterns lean on the DataFrame's default labeling: df[0] only grabs "the first column" because the columns happen to be named 0, 1, 2..., and df.loc[0] only grabs "the first row" because the index happens to be labeled 0, 1, 2.... If either has been changed to something else (say, after a set_index() call), you'd reach for .iloc[0] instead, which always means "position zero" regardless of the labels.

The Standard Missing-Data Checklist

Put together, here's the small set of checks that covers almost every situation, run against a real dataset we'll now load and clean:

import pandas as pd
import numpy as np
import missingno as msno

data = pd.read_csv('melbourne.csv')

data.head()

This is the Melbourne Housing Snapshot dataset — a real, publicly available scrape of Melbourne property sales, with all the messiness that comes from real-world data collection. It's an excellent dataset for practicing wrangling precisely because nothing about it has been artificially cleaned up for you.

# Check the number of missing values in each column
data.isnull().sum()

# count of columns which have at least 1 missing value
data.isnull().any().sum()

# list of all the columns which have any missing values
data.columns[data.isnull().any()]

# a True/False flag for every row: does it have at least one missing value?
data.isnull().any(axis=1)

# select the rows in which every single value is missing
data[data.isnull().all(axis=1)]

With these five lines, you know exactly how bad the problem is: how many values are missing per column, how many columns are affected at all, which ones they are, which rows have at least one gap, and whether any row is missing everything (a strong signal that row shouldn't exist at all). Now you're ready to actually treat what you've found.

Part 4: Cleaning a Real Dataset, Step by Step

There are, broadly, two things you can do with a missing value once you've found it: delete it, or impute (fill) it. Deleting loses data outright; imputing risks manufacturing values that were never really observed and quietly biasing your dataset toward whatever assumption you filled in with. The right call always depends on three things: how important the column is to your task, how much of the column is actually missing, and what you're using the data for.

Deciding Column by Column

round(100*(data.isnull().sum()/len(data)), 2)

A handful of columns are missing a substantial share of their values. To decide what to do with each one, weigh:

• The importance of that column to the task at hand
• How much data is actually available once you account for the gap
• What the data will ultimately be used for

Price, for instance, is the single most important column if this data is headed toward a predictive model — it's the thing you'd be predicting — so it's not a candidate for deletion no matter how much of it were missing. BuildingArea and YearBuilt, on the other hand, are each missing more than half their values, and CouncilArea isn't far behind them. There's no good way to fill in half a column without introducing heavy bias, and there's not enough left to justify keeping just the observed half either, so the more defensible move is to drop all three columns entirely:

# dropping the columns with more than 30% missing values
data.drop(columns=['BuildingArea', 'YearBuilt', 'CouncilArea'], inplace=True)

Deciding Row by Row

round((data.isnull().sum()/len(data)) * 100, 2)

data.shape

After dropping those three columns, every remaining row has 18 fields. If a given row is missing more than five of those eighteen, that's a strong sign the whole record was poorly collected rather than just one field slipping through — and a record like that isn't very trustworthy for any of its other columns either.

data[data.isnull().sum(axis=1) > 5]

Sorting by one of the geographic columns and visualizing the missingness with missingno makes the pattern easy to spot at a glance:

sorted_data = data.sort_values('Lattitude', ascending=False)

msno.matrix(sorted_data)

If the rows with heavy missingness cluster together with the exact same columns blank each time, that's the signature of a batch of records that simply weren't collected properly — not a coincidence, but a systematic gap in how that slice of the data was gathered. Records like that are usually not worth trying to salvage.

len(data[data.isnull().sum(axis=1) > 5])

(len(data[data.isnull().sum(axis=1) > 5]) / len(data)) * 100

With roughly 18% of rows this badly incomplete, dropping them is a reasonable trade: it costs less than a fifth of the dataset, and what's left is far more trustworthy.

data = data[~(data.isnull().sum(axis=1) > 5)]

round((data.isnull().sum()/len(data)) * 100, 2)

The Price Column

Most of the damage is now cleaned up, but Price still has a meaningful chunk missing. As established above, it's the most important column here if this data is heading toward predictive modelling — filling it in would mean training a model partly on values you invented, which defeats the purpose. So for Price specifically, the rows with a missing value get dropped rather than filled:

data = data[~np.isnan(data['Price'])]

data.shape

~np.isnan(...) works fine here, but the more idiomatic (and more readable) Pandas way to say the same thing is data[data['Price'].notna()], or even more directly, data.dropna(subset=['Price']). All three do exactly the same thing; the Pandas-native versions just read closer to plain English.

The Landsize Column

Whether it's safe to impute a numeric column instead of dropping its missing rows comes down to how spread out its values are. A tightly clustered column can be filled with something like its mean or median without distorting things much; a column with wild variance can't, because a single "typical" value doesn't represent much of anything.

data['Landsize'].describe()

Here the spread is enormous — the standard deviation is close to the mean itself, and the maximum dwarfs the 75th percentile. Filling gaps with something like the mean would mean asserting a "typical" land size that most properties don't actually have, so the safer choice is to drop the missing rows instead:

data = data[~np.isnan(data['Landsize'])]

Lattitude and Longtitude

(Yes, both columns are misspelled in the source data — Lattitude and Longtitude — and that's worth keeping exactly as-is, since it's what the real column names are.) These two columns describe a single physical location together, so there's a good chance that whenever one is missing, the other is too:

# rows with Lattitude missing
data[np.isnan(data['Lattitude'])]   # confirms both Lattitude and Longtitude are missing together in the same rows

With the pattern confirmed, check how tightly clustered these coordinates are before deciding how to fill them:

data[['Lattitude', 'Longtitude']].describe()

Unlike Landsize, geographic coordinates for properties concentrated in and around one city sit in a narrow, tight range — the standard deviation is small relative to the range of plausible values. That makes the column mean a reasonable, low-risk stand-in for the handful of missing rows:

data['Lattitude'] = data['Lattitude'].fillna(data['Lattitude'].mean())
data['Longtitude'] = data['Longtitude'].fillna(data['Longtitude'].mean())

round((data.isnull().sum()/len(data)) * 100, 2)

Car and Bathroom: Treating Counts as Categories

data.info()

Car and Bathroom both show up as float64, which looks odd for something that's fundamentally a whole-number count — nobody has 1.3 bathrooms. The float dtype isn't hiding fractional bathrooms, though; it's simply what Pandas defaults to whenever a numeric column contains any missing values at all, since NaN itself is a floating-point value and integer columns can't hold it. The real, more useful reason to change these columns is different: a car count or bathroom count is a small set of discrete categories (0, 1, 2, 3...), not a continuous measurement, and it's more honest to treat it that way. Casting to object lets you reason about it with tools built for categories — value_counts(), and filling gaps with the single most common category (the mode) — rather than tools built for continuous numbers, like the mean:

data['Car'] = data['Car'].astype('object')
data['Bathroom'] = data['Bathroom'].astype('object')

data.info()

Note that the cast alone doesn't change any values — value_counts() will still show 1.0, 2.0, and so on, since that's what the underlying numbers actually are. What's changed is how you're allowed to treat the column from here on.

data['Car'].value_counts()

With the most common car count identified, fill the gaps in Car with it:

data['Car'] = data['Car'].fillna(2)

data['Bathroom'].value_counts()

And do the same for Bathroom:

data['Bathroom'] = data['Bathroom'].fillna(1.0)

data['Bathroom']

dropna() in Practice

Everything above filtered rows by hand with boolean masks, which is worth understanding at that level of detail — but Pandas' own dropna() covers the same ground more directly, with a few parameters worth knowing:

# drop any row that still has at least one missing value, anywhere
data.dropna()

# be more surgical: only require these specific columns to be non-null
data.dropna(subset=['Price', 'Landsize'])

# keep a row as long as it has at least this many real (non-missing) values
data.dropna(thresh=15)

subset restricts which columns are even considered when deciding whether a row "has" a missing value, and thresh sets a minimum number of non-null values a row needs to survive, rather than requiring all of them. Both are usually gentler and more explicit than chaining boolean masks by hand once you know exactly what condition you want.

Forward Fill and Backward Fill

For time-ordered data specifically, there's a filling strategy that doesn't reach for a statistic at all: just carry the nearest known value across the gap. ffill() propagates the last valid value forward through any following gaps; bfill() does the mirror image, pulling the next valid value backward to fill the gaps before it:

# Forward-fill: carry the last valid value forward
data['Price'].ffill()

# Backward-fill: pull the next valid value backward
data['Price'].bfill()

   Price:     1017000    NaN     NaN     1209000    935000

   ffill  →    1017000  1017000 1017000    1209000    935000   (carries left value right)
   bfill  →    1017000  1209000 1209000    1209000    935000   (carries right value left)

These make sense for a genuine time series where "the value probably didn't change much since the last reading" is a fair assumption — a stock price, a sensor log, a running inventory count. They make much less sense for something like property price, shown above only to illustrate the mechanics; a house's price has no reason to resemble the price of whichever house happened to be listed next in the file.

Part 5: Smarter Imputation — KNN and Iterative

Filling with a single number like the mean or the mode is fast, but it's also blind: every missing value in the column gets exactly the same fill, regardless of what else is true about that particular row. If a very large, five-bedroom property is missing its price, filling it with the dataset's overall average price ignores everything you actually know about that property. KNN and Iterative imputation both use the other columns to make a more informed guess.

KNN Imputation

K-Nearest Neighbors (KNN) imputation fills a missing value by finding the most similar rows — the "nearest neighbors" — based on the columns that aren't missing, and averaging their values for the column that is:

from sklearn.impute import KNNImputer
knn_imputer = KNNImputer()

data['Price'] = knn_imputer.fit_transform(data[['Price']])

data['Price'] = round(data['Price'], 2)

Fitting KNNImputer on a single column, as above, is a fine way to see the syntax, but it doesn't really use the "K nearest neighbors" idea to its potential — with only one feature to compare on, the "nearest" rows are just whichever prices happen to be numerically closest, which isn't so different from a rolling local average. KNN imputation earns its keep when you give it several relevant columns to judge similarity by, so that a missing price gets filled using genuinely comparable properties, not just nearby numbers:

knn_imputer = KNNImputer(n_neighbors=5)
feature_cols = ['Rooms', 'Distance', 'Landsize', 'Price']
data[feature_cols] = knn_imputer.fit_transform(data[feature_cols])

Now a missing price is filled using the average price of the five most similar properties — similar in room count, distance from the city, and land size — rather than the single closest price value in isolation.

Iterative Imputer

Iterative imputation goes a step further. Instead of comparing rows directly, it treats each column with missing values as something to predict from all the other columns, using a regression model. It fills in a first-pass guess for every gap, then goes column by column, re-predicting each one from the current (partly imputed) state of all the others, and repeats that cycle until the values stop changing much. This is the same idea behind the well-known MICE (Multiple Imputation by Chained Equations) method in statistics.

from sklearn.experimental import enable_iterative_imputer
from sklearn.impute import IterativeImputer

num_cols = ['Rooms', 'Price', 'Distance', 'Bedroom2', 'Landsize',
            'Lattitude', 'Longtitude', 'Propertycount']

it_imputer = IterativeImputer(random_state=42, max_iter=10)
data[num_cols] = it_imputer.fit_transform(data[num_cols])

The from sklearn.experimental import enable_iterative_imputer line looks unused, but it isn't — IterativeImputer is still flagged as experimental in scikit-learn, so that import is what makes it available at all. Iterative imputation tends to outperform KNN when your columns are linearly related to each other (price genuinely does scale with rooms, distance, and land size here), but it's slower and, because it's fitting real regression models under the hood, easier to overfit if you hand it too many weakly related columns. Both are worth having in your toolkit; which one wins is usually worth checking empirically rather than assuming.

Part 6: Key Takeaways for Missing Data

• Don't reach for deletion by default. Dropping data means losing information, and past a certain point it leaves you with too little to trust your own conclusions.
• Deletion is safest when the data is genuinely MAR or MCAR, since the loss isn't tied to any particular kind of value — but even then, if the missing share is too large, deleting all of it still isn't free.
• If a column has low correlation with your other features and isn't important to the task, it's usually safe to drop outright.
• As a rule of thumb, keep the share of rows you delete under about 5% wherever you can — much more than that and you're meaningfully reshaping the dataset, not just tidying it.
• For columns, try to rescue a column before deleting it — but once more than roughly 60% of it is missing, filling it convincingly gets very hard, and dropping it becomes the more honest choice.

Part 7: Finding and Removing Duplicate Rows

Duplicate rows creep in from merged files, repeated exports, or a form that got submitted twice. Left in place, they quietly double-count whatever they represent — inflating a total, or making one property look twice as common as it really is.

data_2 = pd.read_csv('melbourne.csv')

data_2.head()

# Identify the duplicate rows
data_2.duplicated().sum()

duplicated() compares every row against all the ones before it and flags a row True the moment an identical row has already appeared. The first occurrence of any repeated row stays False; only the repeats after it are marked.

# check the rows which are duplicates
data_2[data_2.duplicated()]

# drop all the duplicate rows from the data
data_2.drop_duplicates(inplace=True)

You can also narrow the definition of "duplicate" to just a subset of columns, which is useful when two rows shouldn't both exist even if they differ in some incidental field:

data_2.duplicated(subset='Price')

# only consider certain columns for identifying duplicates, using the subset parameter
data_2[data_2.duplicated(subset='Price')]

# keeps the first row for each unique price, and drops the rest
data_2.drop_duplicates(subset='Price')

Worth being precise about what that last line actually does: it doesn't remove every row that shares its price with another. It keeps the first row for each distinct price and drops the ones that come after — the same "first occurrence survives" rule as plain duplicated(), just scoped to one column. In a real dataset with thousands of independent properties, two houses can easily and legitimately sell for the exact same price by coincidence, so subset='Price' is shown here purely to demonstrate the parameter — treating it as a real duplicate rule for this dataset would throw away plenty of perfectly valid, distinct sales.

Part 8: Engineering New Columns from Existing Ones

Real datasets often cram several pieces of information into a single text column. Dates are the classic example: one Date field is really three separate, more useful pieces of information — day, month, and year — bundled together as text.

data_2.head()

Suppose we want to split the date column into three new columns: day, month, and year. The first move, always, is to actually look at a value rather than assume its format:

data_2['Date'].head()

The Melbourne dataset's Date column is formatted day/month/year, using a forward slash — something worth confirming before writing a single line of split logic, because the wrong delimiter won't throw an error at all. It'll just silently hand you back garbage:

# what happens if you guess the wrong delimiter?
data_2['Date'].str.split('-').str.get(1).head()

   Date value: "7/12/2017"     (no hyphen anywhere in it)

   .str.split('-')  →  ["7/12/2017"]        a single-item list — nothing to split
   .str.get(0)      →  "7/12/2017"          the WHOLE original string, unchanged
   .str.get(1)      →  NaN                  there's no second item to get
   .str.get(2)      →  NaN                  there's no third item either

   Nothing crashes. "day" silently becomes a copy of the whole date,
   and "month" / "year" silently become entirely NaN.

That's a much more dangerous kind of bug than a crash: the code runs cleanly, produces three new columns, and gives no indication that two of them are worthless. Splitting on the correct delimiter avoids it entirely:

data_2['Date'].str.split('/')

data_2['day'] = data_2['Date'].str.split('/').str.get(0)
data_2['month'] = data_2['Date'].str.split('/').str.get(1)
data_2['year'] = data_2['Date'].str.split('/').str.get(2)

data_2.head()

str.split('/') turns each date string into a list of its three pieces, and str.get(n) pulls out the piece at position n — position 0 for the day, 1 for the month, 2 for the year, matching the day/month/year order the dates were written in. The general lesson carries well beyond dates: whenever you're about to split a text column, check an actual sample value first, rather than assuming the delimiter or the field order.

The Complete Data Wrangling Workflow at a Glance

Here's how all the pieces from this guide fit into a typical cleaning pass, start to finish:

Quick Reference Summary

Data wrangling rarely feels like the interesting part of a project, but it's the part that everything else stands on. A model trained on unexamined missing-data assumptions, or an analysis built on rows that were secretly counted twice, produces a wrong answer that looks exactly like a right one. The habits in this guide (checking what's actually missing before touching it, matching your fix to the column's importance and spread, verifying a text column's real format before splitting it) are what keep that from happening.