Data Wrangling: A Practical Guide to Cleaning Real Data

Every dataset you load has been through 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 making that raw data ready for analysis. It's unglamorous work, and it decides whether every chart and model you build afterward can be trusted at all.

This guide works through one real, messy dataset from start to finish: a CSV of Melbourne property sales with 23,547 rows and 21 columns. It has the problems every analyst runs into: missing values scattered unevenly across columns, a row that appears twice, and a date column crammed into a single text field. You'll find diagrams and recorded outputs along the way, so you can see what each step does to the data.

What Is Data Wrangling?

Data as it's collected is almost never analysis-ready. It usually has some combination of these 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 with 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 only add noise
• Improper column names: inconsistent casing, spaces, or names that don't describe what the column holds

Wrangling means finding each of these problems and deciding, deliberately, what to do about it. This guide spends most of its time on the two that eat the most hours in practice, missing values and duplicates, and then covers reshaping a messy column into something usable.

Part 1: Understanding Missing Data

Data goes missing for different reasons: the observation never existed, it existed but wasn't recorded, or it was recorded and then lost or corrupted along the way. Before you can fix missing data, it helps to recognize the disguises it comes in.

How Missing Data Shows Up

Missing values aren't always a clean, empty cell. In real datasets they also show up as:

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

The last kind 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

Once you've found missing data, there are two broad responses:

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 decide whether to keep or drop outliers, depending on whether they're valid extremes or errors, and whether to remove duplicate records that shouldn't be counted twice.

The Toolkit

Pandas has 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
• Use KNN or Iterative imputation, which look at the other columns to make a smarter guess
• For time-series data: forward fill (ffill, carry the last known value forward) or backward fill (bfill, pull the next known value back)

Both deleting and filling cost you something. Delete too much and you lose real data, possibly leaving too little to draw reliable conclusions from. Fill too much and you're injecting values you assumed, dressed up as if they were observed. Every decision below is a trade-off between those two costs.

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 sort missingness into four types, and which one you're dealing with should change what you do next.

Structurally Missing Data

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

Missing Completely at Random (MCAR)

Data is MCAR when every data point has exactly the same probability of being missing, with no pattern at all. This is mostly a theoretical ideal: it holds only if every potential observation was equally likely to go missing, with no logic, outside force, or hidden behavior behind it. A sensor that drops one reading in a thousand because of a random hardware glitch, unrelated to the reading itself, is a reasonable real-world approximation.

Missing at Random (MAR)

MAR is probably the hardest of the four to get an intuition for. Data is MAR when the likelihood of being missing differs between groups, but is the same for every member within a group. It sits between "missing for a reason" and "missing completely at random".

A classic illustration (Heitjan, 1997, American Journal of Public Health) imagines tracking ambulance drivers' BMI over time. Drivers with a high BMI at the first visit are less likely to come back for the follow-up measurement, whether their weight went up or down in between. That's MAR, because the missingness is explained by something you already observed (the first BMI reading), not by the missing follow-up value itself. The practical consequence is that MAR data can often be imputed validly if you condition on the variable that explains the pattern, here the first BMI reading.

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 is an income survey where people with very high incomes are more likely to decline to answer. Another is a weight question that people skip because their weight is high. In both cases the missingness is driven directly by the unobserved number, not by some other variable you recorded, which is what separates MNAR from MAR. No conditioning trick fixes it. If you impute the missing incomes with the mean of the incomes 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, flag the bias instead of papering over it.

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 with the same shape as whatever you called it on, and notnull() is its inverse.

Treat missing values in each column separately instead of applying one blanket fix (like "replace every NaN with the column mean") 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 common pattern is to use one column's missingness to filter the rows of a whole DataFrame. It relies on positional column labels, which work when a DataFrame's columns default to 0, 1, 2..., typically because the file was loaded without a header row:

import pandas as pd

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

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

Every row in column 0 happens to be filled here, so this filter keeps everything. The pattern is what matters: df[N] pulls out a column, .notnull() turns it into a True/False mask, and passing that mask back into df[...] keeps only the matching rows.

Filtering Columns by a Row

The mirror-image pattern filters columns by which ones have a real value in a specific row. It comes up with messy spreadsheet exports where the header information landed in the first data row instead of the column names:

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

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

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

Both patterns lean on default labeling: df[0] grabs the first column only because the columns happen to be named 0, 1, 2..., and df.loc[0] grabs the first row only because the index happens to be labeled 0, 1, 2.... If either has been changed (say, by set_index()), use .iloc[0] instead, which always means "position zero" whatever the labels are.

The Standard Missing-Data Checklist

Here is the small set of checks that covers almost every situation, run against the Melbourne dataset we'll clean for the rest of this guide:

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

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

data.head()

       Suburb             Address  Rooms Type      Price Method SellerG  \
0  Abbotsford       68 Studley St      2    h        NaN     SS  Jellis   
1  Abbotsford        85 Turner St      2    h  1480000.0      S  Biggin   
2  Abbotsford     25 Bloomburg St      2    h  1035000.0      S  Biggin   
3  Abbotsford  18/659 Victoria St      3    u        NaN     VB  Rounds   
4  Abbotsford        5 Charles St      3    h  1465000.0     SP  Biggin   

         Date  Distance  Postcode  ...  Bathroom  Car  Landsize  BuildingArea  \
0  03-09-2016       2.5    3067.0  ...       1.0  1.0     126.0           NaN   
1  03-12-2016       2.5    3067.0  ...       1.0  1.0     202.0           NaN   
2  04-02-2016       2.5    3067.0  ...       1.0  0.0     156.0          79.0   
3  04-02-2016       2.5    3067.0  ...       2.0  1.0       0.0           NaN   
4  04-03-2017       2.5    3067.0  ...       2.0  0.0     134.0         150.0   

   YearBuilt  CouncilArea Lattitude  Longtitude             Regionname  \
0        NaN        Yarra  -37.8014    144.9958  Northern Metropolitan   
1        NaN        Yarra  -37.7996    144.9984  Northern Metropolitan   
2     1900.0        Yarra  -37.8079    144.9934  Northern Metropolitan   
3        NaN        Yarra  -37.8114    145.0116  Northern Metropolitan   
4     1900.0        Yarra  -37.8093    144.9944  Northern Metropolitan   

  Propertycount  
0        4019.0  
1        4019.0  
2        4019.0  
3        4019.0  
4        4019.0  

[5 rows x 21 columns]

Each row is one property sale. Notice the Date column: it holds values like 03-09-2016, which will matter in Part 8. First, how much is missing?

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

Suburb               0
Address              0
Rooms                0
Type                 0
Price             5151
Method               0
SellerG              0
Date                 0
Distance             1
Postcode             1
Bedroom2          4481
Bathroom          4484
Car               4626
Landsize          6137
BuildingArea     13529
YearBuilt        12007
CouncilArea       7891
Lattitude         4304
Longtitude        4304
Regionname           1
Propertycount        1
dtype: int64

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

14

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

Index(['Price', 'Distance', 'Postcode', 'Bedroom2', 'Bathroom', 'Car',
       'Landsize', 'BuildingArea', 'YearBuilt', 'CouncilArea', 'Lattitude',
       'Longtitude', 'Regionname', 'Propertycount'],
      dtype='object')

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

0         True
1         True
2        False
3         True
4        False
         ...  
23542     True
23543     True
23544     True
23545     True
23546     True
Length: 23547, dtype: bool

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

Empty DataFrame
Columns: [Suburb, Address, Rooms, Type, Price, Method, SellerG, Date, Distance, Postcode, Bedroom2, Bathroom, Car, Landsize, BuildingArea, YearBuilt, CouncilArea, Lattitude, Longtitude, Regionname, Propertycount]
Index: []

[0 rows x 21 columns]

Those five checks tell you how many values are missing per column, that 14 of the 21 columns have at least one gap, which columns they are, which rows have a gap, and that no row is missing everything (an empty result here is good news, since a fully empty row usually shouldn't exist at all). Now you can treat what you found.

Part 4: Cleaning a Real Dataset, Step by Step

Once you've found a missing value you can delete it or fill it. Deleting loses data outright. Filling risks manufacturing values that were never observed and quietly biasing the dataset toward whatever you assumed. The right call depends on three things: how important the column is to your task, how much of it is missing, and what the data will be used for.

Deciding Column by Column

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

Suburb            0.00
Address           0.00
Rooms             0.00
Type              0.00
Price            21.88
Method            0.00
SellerG           0.00
Date              0.00
Distance          0.00
Postcode          0.00
Bedroom2         19.03
Bathroom         19.04
Car              19.65
Landsize         26.06
BuildingArea     57.46
YearBuilt        50.99
CouncilArea      33.51
Lattitude        18.28
Longtitude       18.28
Regionname        0.00
Propertycount     0.00
dtype: float64

A handful of columns are missing a lot. Notice that Bedroom2, Bathroom, Car, Lattitude and Longtitude are all missing roughly 18 to 20 percent: a hint that the same rows may be blank in all of them, which Part 4 confirms below. To decide what to do with each column, weigh:

• How important the column is 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 is the most important column if this data is headed toward a predictive model, because it's the thing you'd be predicting, so it's not a candidate for deletion. BuildingArea (57.5% missing) and YearBuilt (51.0%) are each missing more than half their values. You can't fill half a column without heavy bias, and the observed half alone isn't enough to justify keeping them, so dropping the columns is the more defensible move.

CouncilArea (33.5%) is a closer call. It's a text column naming the local council, and the dataset already records where each property is through Suburb, Postcode, Regionname and the coordinates, so the column largely overlaps with information you keep. This pass uses a 30% cutoff, so all three go:

# 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)

Suburb            0.00
Address           0.00
Rooms             0.00
Type              0.00
Price            21.88
Method            0.00
SellerG           0.00
Date              0.00
Distance          0.00
Postcode          0.00
Bedroom2         19.03
Bathroom         19.04
Car              19.65
Landsize         26.06
Lattitude        18.28
Longtitude       18.28
Regionname        0.00
Propertycount     0.00
dtype: float64

data.shape

(23547, 18)

With those three columns gone, every row has 18 fields. If a row is missing more than five of the eighteen, that's a strong sign the whole record was collected badly rather than one field slipping through, and it makes the row's other values less trustworthy too.

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

Sorting by one of the geographic columns and plotting the missingness with missingno makes the pattern easy to see:

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

msno.matrix(sorted_data)

sort_values puts NaN last by default, even with ascending=False, so the rows with no latitude gather at the bottom. The plot shows that block is blank across six columns together: Bedroom2, Bathroom, Car, Landsize, Lattitude and Longtitude. Six gaps out of eighteen is exactly the "more than five" threshold from the filter above. When the same columns are blank row after row, the cause is a batch of records that was never collected properly, not coincidence. Records like that are rarely worth rescuing. How many are there?

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

4278

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

18.16791948018856

That's about 18% of the rows. Dropping them 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)

Suburb            0.00
Address           0.00
Rooms             0.00
Type              0.00
Price            21.71
Method            0.00
SellerG           0.00
Date              0.00
Distance          0.00
Postcode          0.00
Bedroom2          1.05
Bathroom          1.07
Car               1.81
Landsize          9.65
Lattitude         0.13
Longtitude        0.13
Regionname        0.00
Propertycount     0.00
dtype: float64

Most of the damage is gone: Bedroom2, Bathroom, Car and the coordinates dropped to a percent or two, and only Price (21.7%) and Landsize (9.7%) still have sizeable gaps.

The Price Column

Price still has a large share missing. As established above, it's the most important column if this data is heading toward predictive modelling. Filling it would mean training a model partly on prices you invented, which defeats the purpose. So for Price the rows with a missing value are dropped rather than filled:

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

data.shape

(15086, 18)

~np.isnan(...) works, but the more idiomatic Pandas spelling is data[data['Price'].notna()], or more directly data.dropna(subset=['Price']). All three do the same thing, and the Pandas-native versions read closer to plain English.

The Landsize Column

Whether it's safe to fill a numeric column instead of dropping its missing rows depends on how spread out its values are. A tightly clustered column can take its mean or median without much distortion. A column with wild variance can't, because one "typical" value doesn't represent much of anything.

data['Landsize'].describe()

count     13603.000000
mean        558.116371
std        3987.326586
min           0.000000
25%         176.500000
50%         440.000000
75%         651.000000
max      433014.000000
Name: Landsize, dtype: float64

The spread here is enormous. The standard deviation (about 3,987) is roughly seven times the mean (about 558), and the maximum (433,014) is hundreds of times the 75th percentile (651). Filling gaps with the mean would assert a "typical" land size that hardly any property has, so the safer choice here is to drop the missing rows. Part 5 looks at a smarter way to fill a column like this, in case you'd rather keep the rows.

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

Lattitude and Longtitude

(Both column names are misspelled in the source file, Lattitude and Longtitude, and they're kept exactly as they are because that's what the file calls them.) Together the two columns describe one physical location, so whenever one is missing the other probably 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 pairing confirmed, check how tightly clustered the coordinates are before choosing how to fill them:

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

          Lattitude    Longtitude
count  13581.000000  13581.000000
mean     -37.809204    144.995221
std        0.079257      0.103913
min      -38.182550    144.431810
25%      -37.856820    144.929600
50%      -37.802360    145.000100
75%      -37.756400    145.058320
max      -37.408530    145.526350

Unlike Landsize, coordinates for properties around a single city sit in a narrow range: the standard deviations are about 0.08 and 0.10 degrees. That makes the column mean a low-risk stand-in for the few 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)

Suburb           0.00
Address          0.00
Rooms            0.00
Type             0.00
Price            0.00
Method           0.00
SellerG          0.00
Date             0.00
Distance         0.00
Postcode         0.00
Bedroom2         0.00
Bathroom         0.01
Car              0.46
Landsize         0.00
Lattitude        0.00
Longtitude       0.00
Regionname       0.00
Propertycount    0.00
dtype: float64

Car and Bathroom: Treating Counts as Categories

data.info()

In the info() output, Car and Bathroom both show up as float64, which looks odd for a whole-number count: nobody has 1.3 bathrooms. The float type isn't hiding fractional values, though. It's what Pandas uses whenever a numeric column contains any missing value, because NaN is itself a floating-point value and a plain integer column can't hold it. The better reason to change these columns is that a car or bathroom count is a small set of discrete categories (0, 1, 2, 3...), not a continuous measurement. Casting to object lets you use tools built for categories, like value_counts() and filling with the most common value (the mode), instead of tools built for continuous numbers, like the mean:

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

data.info()

The cast alone doesn't change any values: value_counts() still shows 1.0, 2.0 and so on, since those are the underlying numbers. What changes is how you're allowed to treat the column from here on. Only 63 car values and 1 bathroom value are still missing at this point, so the fill is low-stakes either way.

data['Car'].value_counts()

2.0     5606
1.0     5515
0.0     1026
3.0      748
4.0      507
5.0       63
6.0       54
8.0        9
7.0        8
10.0       3
9.0        1
Name: Car, dtype: int64

Two cars is the most common value, though only narrowly ahead of one car (5,606 against 5,515). Fill the gaps in Car with it:

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

data['Bathroom'].value_counts()

1.0    7517
2.0    4987
3.0     921
4.0     106
0.0      34
5.0      28
6.0       5
7.0       2
8.0       2
Name: Bathroom, dtype: int64

One bathroom is the clear mode (7,517 properties), so use it for Bathroom:

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

data['Bathroom']

1        1.0
2        1.0
4        2.0
5        2.0
6        1.0
        ... 
23537    2.0
23538    2.0
23539    2.0
23541    1.0
23545    1.0
Name: Bathroom, Length: 13603, dtype: float64

What the Cleaning Pass Cost

Each deletion above had its own reason, but it helps to see the running total in one place:

That's 9,944 rows, about 42% of the file. It's well past the 5% guideline in Part 6. The deletions can each be defended: one was a batch that was never collected properly, one was a column we refuse to invent values for, and one was too spread out to fill with a single number. Even so, a total like this is a prompt to ask whether you can keep more of the data. Part 5 shows how.

dropna() in Practice

The steps above filtered rows with boolean masks, which is worth understanding in detail. Pandas' own dropna() covers the same ground more directly. Here it is applied to a fresh copy of the file:

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

# the Price step from above, in one call
df_raw.dropna(subset=['Price'])

# the row rule from above: with 18 columns left, "no more than 5 missing" means "at least 13 real values"
df_raw.drop(columns=['BuildingArea', 'YearBuilt', 'CouncilArea']).dropna(thresh=13)

# the blunt version: drop any row with even one missing value
df_raw.dropna()

subset restricts which columns count when deciding whether a row "has" a gap, and thresh sets the minimum number of non-null values a row needs to survive, instead of requiring all of them. Both are usually gentler and more explicit than chaining boolean masks. The last line is the one to be careful with: on this file almost every row has at least one gap (the checklist above showed 14 of 21 columns affected), so dropna() with no arguments would throw away most of the data.

Forward Fill and Backward Fill

For time-ordered data there's a strategy that doesn't use a statistic at all: carry the nearest known value across the gap. ffill() carries the last valid value forward through the gaps after it. bfill() does the reverse, pulling the next valid value back to fill the gaps before it.

prices = pd.Series([1017000, np.nan, np.nan, 1209000, 935000])

prices.ffill()   # carry the last valid value forward

0    1017000.0
1    1017000.0
2    1017000.0
3    1209000.0
4     935000.0
dtype: float64

prices.bfill()   # pull the next valid value backward

0    1017000.0
1    1209000.0
2    1209000.0
3    1209000.0
4     935000.0
dtype: float64

   prices:    1017000    NaN       NaN       1209000    935000

   ffill  →   1017000    1017000   1017000   1209000    935000    (copies the left value rightward)
   bfill  →   1017000    1209000   1209000   1209000    935000    (copies the right value leftward)

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 house prices. A house's price has no reason to resemble whichever house happened to be listed next in the file, which is why the example above uses a bare list of numbers and not the Melbourne data.

Part 5: Smarter Imputation: KNN and Iterative

Filling with one number like the mean or the mode is fast, but it's blind: every gap in the column gets the same fill, whatever else is true about that row. If a large five-bedroom property is missing its land size, giving it the dataset's average ignores everything you know about it. KNN and Iterative imputation both use the other columns to make a more informed guess.

The cleaned data frame from Part 4 has no gaps left in these columns, so there'd be nothing for an imputer to do. To see them work, rebuild the state of the data just before the Price and Landsize decisions: reload the file and repeat the first two cleaning steps, which leaves the gaps in place.

data_gaps = pd.read_csv('melbourne.csv')
data_gaps = data_gaps.drop(columns=['BuildingArea', 'YearBuilt', 'CouncilArea'])
data_gaps = data_gaps[~(data_gaps.isnull().sum(axis=1) > 5)]

KNN Imputation

K-Nearest Neighbors (KNN) imputation fills a gap by finding the rows most similar to the incomplete one, judged on the columns that aren't missing, and averaging their values for the column that is. Start with the simplest call, on Price alone:

from sklearn.impute import KNNImputer
knn_imputer = KNNImputer()

data_single = data_gaps.copy()
data_single['Price'] = knn_imputer.fit_transform(data_single[['Price']])

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

Run on one column like this, KNN isn't really using neighbors. With no other columns to measure similarity on, it falls back to the column mean, so every gap gets the same number (in the original run on this dataset, every missing price became 1056697.46). It's a fine way to learn the syntax, but it's just mean imputation in disguise.

KNN earns its keep when you give it several columns to judge similarity by. One thing to handle first: KNN works with distances, so a column measured in the hundreds (Landsize) can drown out one measured in single digits (Rooms), and the "nearest" rows end up being the ones with similar land size and little else. Scaling the columns first puts them on comparable footing. RobustScaler is a good fit here because it's based on the median and the interquartile range, so extreme values like the 433,014 land size don't distort it, and it leaves NaN alone:

from sklearn.preprocessing import RobustScaler

feature_cols = ['Rooms', 'Distance', 'Bedroom2', 'Bathroom', 'Car',
                'Landsize', 'Lattitude', 'Longtitude', 'Price']

scaler = RobustScaler()
scaled = scaler.fit_transform(data_gaps[feature_cols])

knn_imputer = KNNImputer(n_neighbors=5)
imputed = knn_imputer.fit_transform(scaled)

data_knn = data_gaps.copy()
data_knn[feature_cols] = scaler.inverse_transform(imputed)   # back to the original units

Now a missing price or land size is filled with the average of the five most similar properties (similar in rooms, distance from the city, location and so on) instead of the overall mean. Neighbor averages can land between whole numbers, so round the count columns, then confirm nothing is left:

count_cols = ['Rooms', 'Bedroom2', 'Bathroom', 'Car']
data_knn[count_cols] = data_knn[count_cols].round()      # an average of neighbors can land on 1.4 cars

data_knn[feature_cols].isnull().sum()                    # every gap in these columns is filled

Iterative Imputer

Iterative imputation goes a step further. Instead of comparing rows, it treats each column with gaps as something to predict from all the others using a regression model. It starts with a rough fill, then goes column by column, re-predicting each one from the current state of the rest, and repeats until the values stop changing much. It's the idea behind MICE (Multiple Imputation by Chained Equations) in statistics.

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

it_imputer = IterativeImputer(random_state=42, max_iter=10)

data_it = data_gaps.copy()
data_it[feature_cols] = it_imputer.fit_transform(data_gaps[feature_cols])
data_it[count_cols] = data_it[count_cols].round()

The enable_iterative_imputer import looks unused, but IterativeImputer is still flagged as experimental in scikit-learn, so that import is what makes it available at all. Iterative imputation tends to do better than KNN when the columns are linearly related to each other, as price is to rooms and distance here. It's slower, and because it fits real regression models it can overfit if you feed it many weakly related columns.

Choosing Between Them

There's no universal winner, so check on your own data: hide some values you actually know, impute them with each method, and compare the guesses to the truth. The method (or plain mean) that lands closest wins. Remember too that every imputed value is still a guess. Imputing shrinks the natural variation in a column, so a model trained on imputed data can look more certain than it should.

If this data is headed into a machine-learning model, fit the imputer on the training rows only, then use it to transform the test rows. Fitting it on everything lets information from the test set leak into the values you train on, which flatters your results.

For Price itself, the decision from Part 4 still stands: it's the target, so rows without one are dropped. The imputers are better suited to feature columns like Landsize, where they offer a way to keep rows you would otherwise delete.

Part 6: Key Takeaways for Missing Data

• Don't reach for deletion by default. Dropping data loses information, and past a certain point it leaves too little to trust your conclusions.
• Deletion is safest when the data is MCAR, because the rows you lose are a random subset. Under MAR it can still skew results, since the dropped rows differ systematically from the kept ones, so impute with the explaining variable in play. Even under MCAR, deleting a very large share leaves too little data to rely on.
• If a column has low correlation with your other features and isn't important to the task, it's usually safe to drop it outright.
• As a rule of thumb, keep the share of rows you delete under about 5%. The pass in Part 4 removed far more than that, which is a signal to double-check that every deletion is justified, not a reason to ignore the guideline.
• For columns, try to rescue a column before deleting it. Once more than roughly 60% is missing, dropping it is clearly the honest choice. Between about 30% and 60% it's a judgment call: Part 4 dropped CouncilArea at 33.5% because other columns already carry its information.

Part 7: Finding and Removing Duplicate Rows

Duplicate rows creep in from merged files, repeated exports, or a form 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 is.

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

data_2.head()

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

1

duplicated() compares every row with the ones before it and marks a row True as soon as an identical row has already appeared. The first occurrence stays False; only the repeats after it are marked. This file has exactly one: the Nunawading sale at 1/7 Lilian St.

# 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 a subset of columns, which is useful when two rows shouldn't both exist even though 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')

Look at which rows duplicated(subset='Price') flags first: rows 3, 7, 8, 9 and 12, and every one has a missing price. Pandas treats missing values as equal to each other, so after the first row with a missing price, every other row with a missing price counts as a "duplicate". Add genuine coincidences (two houses can sell for exactly the same price) and the last line would discard a large share of perfectly valid sales. It keeps the first row for each distinct price, nothing more. subset='Price' is here to demonstrate the parameter, not as a real rule for this dataset.

A subset that actually identifies a duplicate record is a combination of columns, such as the same property on the same sale date:

# a more meaningful key: the same address sold on the same date
data_2.duplicated(subset=['Address', 'Date']).sum()

Part 8: Engineering New Columns from Existing Ones

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

data_2.head()

Suppose we want three new columns: day, month and year. The first step, always, is to look at an actual value and not assume its format:

data_2['Date'].head()

In this file the dates look like 03-09-2016: day first, then month, then year, separated by hyphens. Check that before writing any split logic, because a wrong delimiter doesn't raise an error. It quietly hands you back garbage. Slashes are a very common format, so here's what a guess of '/' would do:

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

   Date value: "03-09-2016"     (no slash anywhere in it)

   .str.split('/')  →  ["03-09-2016"]       a single-item list: nothing to split
   .str.get(0)      →  "03-09-2016"         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" and "year" silently become entirely NaN.

That's a more dangerous bug than a crash: the code runs cleanly, produces three new columns, and gives no sign that two of them are worthless. Splitting on the real delimiter, the hyphen, works:

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

0        [03, 09, 2016]
1        [03, 12, 2016]
2        [04, 02, 2016]
3        [04, 02, 2016]
4        [04, 03, 2017]
              ...      
23542    [26, 08, 2017]
23543    [26, 08, 2017]
23544    [26, 08, 2017]
23545    [26, 08, 2017]
23546    [26, 08, 2017]
Name: Date, Length: 23546, dtype: object

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: 0 for the day, 1 for the month, 2 for the year, matching the day-month-year order the dates are written in. The lesson applies well beyond dates: before you split a text column, check a real sample value for the delimiter and the field order.

A Sturdier Option: Parse the Date

Splitting leaves day, month and year as text ('03', '09', '2016'), so you can't do arithmetic on them or sort the rows chronologically. Parsing the column as a real date solves both, and it validates the format as it goes: a value that doesn't match raises an error instead of quietly becoming NaN.

dates = pd.to_datetime(data_2['Date'], format='%d-%m-%Y')   # day first, hyphen-separated

data_2['day'] = dates.dt.day        # integers this time, not text
data_2['month'] = dates.dt.month
data_2['year'] = dates.dt.year

Spell out the format when you parse. A value like 03-09-2016 is ambiguous on its own (the 3rd of September or the 9th of March), and the explicit format settles it. Here the file's later rows, such as 26-08-2017, confirm that the first number is the day.

The Complete Data Wrangling Workflow at a Glance

Here is how the pieces from this guide fit into a typical cleaning pass:

Quick Reference Summary