Duplicates, Tidy Structure, Outliers and Range Validation

Context

A duplicate row repeats a record the table already holds, a range rule states which values a column may hold at all, an outlier is a value far from the rest of its column, and a tidy table keeps one variable in each column and one record in each row.

These are the last checks before a table goes to a model or a report. An export that ran twice, two sources stitched together and a listing posted again all leave repeated rows. A rooms value of 99 is a typing slip that no type conversion catches, and a price of 9,900,000 may be a mansion or an extra zero. Renaming, sorting and renumbering decide whether the next script, and the next person, can read the table without guessing.

Each of these failures leaves the code running. A duplicated listing counts twice in every mean it enters and every row count by neighborhood, and a deduplication on the wrong columns removes real listings while removing exactly as many rows as it was told to. A z-score computed with the outlier still inside it lets the next outlier through, and a filtered table that was never renumbered hands a position-based lookup the wrong listing. The previous lesson fixed what each column means, this lesson checks the rows and the shape of the whole table.

Goal

• Detect and remove duplicate rows with duplicated and drop_duplicates, choose subset= and keep=, and reconcile what a key keeps against the number of distinct records.
• Write domain rules as boolean masks and report each rule's broken and unknown counts separately.
• Flag outliers with the 1.5 x IQR fences and with z-scores, and trace how one extreme value hides another from the z-score.
• Cap values with clip and count exactly how many values it changed.
• Rename columns, sort by several keys and reset the index so labels and positions agree again.

Prerequisites

Optional, and the lesson stands without either. They show the two tools it leans on most before you use them.

• YouTube - "How do I find and remove duplicate rows in pandas?" by Data School. The two methods that find and remove duplicate rows, and how their options change what counts as a duplicate.
• YouTube - "Quantiles and Percentiles, Clearly Explained!!!" by StatQuest with Josh Starmer. What quantiles and percentiles are, the numbers the IQR fences are built from.

Table of Contents

• Duplicate Rows
• Range Validation
• Outliers - IQR and z-score
• Tidy Structure

Theory

Nothing to install. Each subtitle starts a fresh script that loads housing.csv from ~/dev/bootcamp, and every subtitle after the first removes its exact repeats as it loads.

Duplicate Rows

What. duplicated() returns one boolean per row, True where the row repeats a row already seen, and drop_duplicates() returns the frame without those rows. subset= names the columns that decide what counts as the same, and keep= decides which copy is left unmarked. Why. A repeated row counts twice in every row count and every mean it enters, grouped or not, and a model trained on it sees one listing as two. Each copy is valid on its own, so no check that judges one row at a time notices it. When. Right after loading and normalizing the text columns, before any count, statistic or split, and again after combining two tables.

import pandas as pd

df = pd.read_csv("housing.csv")
print(len(df))                          # 209
later = df.duplicated()
print(df.index[later].tolist())         # [204, 205, 206, 207, 208]
earlier = df.duplicated(keep="last")
print(df.index[earlier].tolist())       # [0, 1, 2, 3, 4]
print(df.duplicated(keep=False).sum())  # 10

Rows 204 to 208 repeat rows 0 to 4, an export that ran twice. keep="first", the default, marks every copy after the first, so duplicated().sum() is 5, the extra rows, not the 10 rows involved. keep="last" marks the copies before the last, the one to use when a later row supersedes an earlier one, and keep=False marks every member, the set to read before deleting anything. Rows 3 and 207 both have no price, and before trusting that duplicated matched them, run it:

same_cells = (df.loc[3] == df.loc[207]).all()
print(same_cells)                # False
print(df.duplicated().loc[207])  # True

== compares cell by cell, and a missing price is unequal even to another missing price, Lesson 2.5.1's rule, so the two rows are unequal to ==. duplicated treats two missing values in the same column as equal and marks row 207 a copy of row 3, which is the right answer for a listing entered twice with no price. Removing the copies is one call, with a count before and after:

deduped = df.drop_duplicates()
print(len(df), len(deduped))  # 209 204

subset= decides which columns make two rows the same listing. Suppose a teammate decides that a listing is its neighborhood plus the day it went up:

key = ["neighborhood", "listing_date"]
flagged = df.duplicated(subset=key).sum()
by_key = df.drop_duplicates(subset=key)
print(flagged, len(df) - len(by_key))  # 10 10
print(len(by_key), len(deduped))       # 199 204

The deduplication removed exactly the 10 rows it was told to, and the table now holds 199 listings where it has 204 distinct records. Rows equal on the key are not equal everywhere:

on_key = df.duplicated(subset=key, keep=False)
whole_row = df.duplicated(keep=False)
cols = ["price", "rooms", "neighborhood", "listing_date"]
print(df.loc[on_key & ~whole_row, cols])

        price  rooms neighborhood listing_date
31   197000.0      4    Southgate   2024-01-09
39   245000.0      3     Downtown   2024-12-14
57   361000.0      5     Lakeview          NaN
63   155000.0      5     Northend   2024-04-09
100  254000.0      4     Downtown   2024-12-14
110  197000.0      4     Northend   2024-04-09
115  321000.0      6     Lakeview          NaN
136  180000.0      6    Southgate   2024-01-09
189  329000.0      2     Lakeview   2023-07-17
190  409000.0      3     Lakeview   2023-07-17

Five pairs of different listings share a neighborhood and a day. Four differ in price and rooms, and the fifth, rows 57 and 115 in Lakeview, shares the key only because both dates are missing and duplicated matched the two gaps. No error, a removed count that agrees with the flagged count, and 5 real listings gone. The check is the rows a key keeps against len(df.drop_duplicates()), the distinct records, which must agree unless you mean to merge listings that differ.

DON'T:

• DON'T read a deduplication on a key as complete because it removed as many rows as duplicated flagged. On ["neighborhood", "listing_date"] it removed 10 and left 199, where the table holds 204 distinct records, so compare the two counts before you trust the key.
• DON'T decide whether two rows repeat by comparing them with ==. A NaN is unequal to itself under ==, while duplicated treats two missing values as equal, the reason rows 3 and 207 match.
• DON'T report duplicated().sum() as the number of rows involved. It counts the extra copies, 5, and keep=False marks every member, 10.

Range Validation

What. A range rule says which values a column may hold at all: rooms from 1 to 20, an area above zero, a building finished no later than the year it was listed. Each rule becomes a boolean mask that marks the rows breaking it. Why. A value can have the right type, be present and still be impossible. 99 rooms passes every check from this section's earlier lessons, and only a rule written from what the column means calls it impossible. When. After the types are fixed and before any statistic, for every column whose legal values you can state.

import pandas as pd

df = pd.read_csv("housing.csv").drop_duplicates()
listed_year = pd.to_datetime(df["listing_date"]).dt.year
rooms = df["rooms"]
rooms_bad = (rooms < 1) | (rooms > 20)
rules = {
    "rooms outside 1-20": (rooms_bad, rooms.isna()),
    "area_sqm at or below 0": (df["area_sqm"] <= 0, df["area_sqm"].isna()),
    "built after listed": (df["year_built"] > listed_year, listed_year.isna()),
}
for rule, (broken, unknown) in rules.items():
    print(f"{rule}: {broken.sum()} broken, {unknown.sum()} unknown")

rooms outside 1-20: 1 broken, 0 unknown
area_sqm at or below 0: 0 broken, 15 unknown
built after listed: 0 broken, 3 unknown

Each rule is written as the condition that breaks it, so a NaN, which fails every comparison but !=, lands on the False side and is counted apart as unknown, while the <NA> of a nullable column such as Lesson 2.5.2's Int64 turns every comparison, != included, into <NA>, which sum() leaves out of the count as well. A rule written as "does not pass" counts every NaN as broken:

not_passing = ~(df["area_sqm"] > 0)
print(not_passing.sum())            # 15
print(df["area_sqm"].isna().sum())  # 15

All 15 are missing areas, none of them broken. between from Lesson 2.4.2 has the same edge, because it returns False for a NaN, so ~s.between(lo, hi) counts those gaps too. The one broken value is the 99-room listing at label 202.

clip(lower, upper) is the Series form of Lesson 2.2.3's np.clip, and it leaves missing values missing. It reports nothing, so count what it will change before it runs:

print(rooms_bad.sum())  # 1
capped = rooms.clip(1, 20)
print(capped.loc[202])  # 20

Counting after the fact with != goes wrong on a column with NaN gaps. Capping the area between 20 and 500 square meters changes 2 values:

area = df["area_sqm"]
outside = (area < 20) | (area > 500)
print(outside.sum())                # 2
capped_area = area.clip(20, 500)
print((capped_area != area).sum())  # 17

!= counts the 15 missing areas as changed, because a NaN is unequal to itself, so the count that holds is the one taken before the cap. A cap is also a claim about what a value means: 99 rooms is a typing slip, not an extreme listing, and clipping it to 20 invents a plausible listing that is still wrong.

DON'T:

• DON'T write a rule as the negation of its legal range when the column has gaps. ~(df["area_sqm"] > 0) counts all 15 missing areas as broken, where df["area_sqm"] <= 0 counts the 0 that are.
• DON'T clip a value you believe is an error. 99 rooms clipped to 20 becomes a 20-room listing that looks real and is still wrong, so set it to missing or drop the row, and say which.
• DON'T count what clip changed with != against the original. A NaN is unequal to itself, so (capped_area != area).sum() says 17 where 2 values changed.

Outliers - IQR and z-score

What. An outlier is a value far from the rest of its column. The IQR method puts a fence 1.5 IQR below the first quartile, the 25th percentile of Lesson 2.2.5, and another 1.5 IQR above the third, the 75th, where the IQR is the distance between the two quartiles. The z-score method, Lesson 2.2.5's standardization applied to one column, measures each value's distance from the mean in standard deviations, (x - mean) / std, and flags |z| > 3. Why. A few extreme values pull a mean, a standard deviation and a model fitted to the column toward them. Whether a 9,900,000 price is a mansion or a typing slip is a question for a person, and the fences decide which rows get asked. When. After deduplication and range validation, on every numeric column a model or a summary will use, before you trust its mean.

import pandas as pd

df = pd.read_csv("housing.csv").drop_duplicates()
price = df["price"]
q1 = price.quantile(0.25)
q3 = price.quantile(0.75)
iqr = q3 - q1
print(q1, q3, iqr)  # 197000.0 377000.0 180000.0
low = q1 - 1.5 * iqr
high = q3 + 1.5 * iqr
print(low, high)    # -73000.0 647000.0

quantile is the Series form of Lesson 2.2.5's np.quantile, and it skips missing values. A negative lower fence on a column that cannot go below zero only means that no price is a low outlier:

iqr_flag = (price < low) | (price > high)
print(iqr_flag.sum())  # 2
cols = ["price", "area_sqm", "rooms", "neighborhood"]
print(df.loc[iqr_flag, cols])

         price  area_sqm  rooms neighborhood
200  5200000.0      95.0      3     Downtown
203  9900000.0    2400.0     12     Eastgate

z = (price - price.mean()) / price.std()
print(z[iqr_flag].round(2).tolist())  # [6.06, 11.95]
print((z.abs() > 3).sum())            # 2
cutoff = price.mean() + 3 * price.std()
print(round(cutoff))                  # 2753481

Both methods flag the same two prices. std() is pandas' sample standard deviation, the ddof=1 of Lesson 2.2.5, which Lesson 2.7.1 sets beside NumPy's default. The last line shows the z-score's weakness: the two extreme prices sit inside the mean and the standard deviation it divides by, so a price has to pass 2,753,481 before its z reaches 3, where the upper fence sits at 647,000. rooms shows what that does to a smaller outlier. It holds the impossible 99 and a 12 that is possible and rare:

rooms = df["rooms"]
r1 = rooms.quantile(0.25)
r3 = rooms.quantile(0.75)
fence = r3 + 1.5 * (r3 - r1)
print(fence)                          # 9.5
print(rooms[rooms > fence].tolist())  # [99, 12]
z_rooms = (rooms - rooms.mean()) / rooms.std()
print(round(rooms.mean(), 2))         # 4.01
print(round(rooms.std(), 2))          # 6.93
z12_with = z_rooms[rooms == 12].round(2)
print(z12_with.tolist())              # [1.15]
print((z_rooms.abs() > 3).sum())      # 1

With the 99 counted, the mean is 4.01 and the standard deviation 6.93, so the 12 sits 1.15 standard deviations from the mean and passes. Take the 99 out and the same 12 is flagged:

rest = rooms[rooms != 99]
z_rest = (rest - rest.mean()) / rest.std()
print(round(rest.std(), 2))  # 1.82
z12_without = z_rest[rest == 12].round(2)
print(z12_without.tolist())  # [4.65]

One extreme value hid another. The fences held because a quartile depends on where values rank, not on how far the largest one sits.

An outlier is a question, not an error. Each flagged value gets one of three answers, decided column by column: drop the row, cap the value at the fence with clip, or keep it and record the doubt in a column of its own. Here the prices are kept with a flag and the areas are capped:

df["price_outlier"] = iqr_flag         # keep the rows, flag the doubt
area = df["area_sqm"]
a1 = area.quantile(0.25)
a3 = area.quantile(0.75)
area_high = a3 + 1.5 * (a3 - a1)
print(round(area_high, 2))             # 185.35
print((area > area_high).sum())        # 2
df["area_sqm"] = area.clip(upper=area_high)
print(round(df["area_sqm"].max(), 2))  # 185.35

DON'T:

• DON'T trust a z-score scan on a column that holds one extreme value. The 99 stretched the standard deviation of rooms to 6.93 and the 12-room listing scored 1.15, while the IQR fence at 9.5 caught both.
• DON'T flag with z > 3. A value far below the mean has a negative z, and the 1911 year_built sits at -3.54, which z > 3 misses and z.abs() > 3 catches.
• DON'T drop every flagged row by reflex. A 5,200,000 listing may be real and worth showing the model, so the answer is decided per column and counted.

Tidy Structure

What. A tidy table keeps one variable in each column, one observation in each row and one kind of thing in each table. rename, sort_values and reset_index are three methods that put a table into its final shape. Why. Every tool so far assumes that shape. A mask needs the year in a column of its own, a mean needs one listing per row, and code that finds a row by its label and reads it by its position needs the two to agree. When. Before the table leaves your hands, for a model, a file or a teammate, and after every filter or deduplication that leaves gaps in the index.

housing.csv already follows the three rules. Each column is one variable, each row one listing, and facts about a neighborhood, such as its distance to the center, live in a second file, regions.csv, which Lesson 2.6.3 joins on. The first break is two variables in one column, repaired with Lesson 2.5.3's .str.split(..., expand=True):

import pandas as pd

feed = pd.DataFrame({
    "listing_id": [101, 102, 103],
    "cond_year": ["good-1998", "fair-1978", "excellent-2005"],
})
pieces = feed["cond_year"].str.split("-", expand=True)
feed[["condition", "year_built"]] = pieces
feed["year_built"] = pd.to_numeric(feed["year_built"])
print(feed[["listing_id", "condition", "year_built"]])

   listing_id  condition  year_built
0         101       good        1998
1         102       fair        1978
2         103  excellent        2005

A list of names on the left assigns the pieces to two new columns in order, and the year comes out of the split as text until pd.to_numeric converts it. The second break is values used as column headers, a column per month, which Lesson 2.6.2's melt turns into rows. The third is a table that mixes listings with facts about their neighborhoods, the reason regions.csv is a file of its own.

rename(columns={old: new}) returns a new frame and leaves the original as it was:

df = pd.read_csv("housing.csv").drop_duplicates()
df.rename(columns={"area_sqm": "area_m2"})  # result thrown away
print("area_m2" in df.columns)              # False
df = df.rename(columns={"area_sqm": "area_m2"})
print("area_m2" in df.columns)              # True

sort_values takes a list of keys and a matching list for ascending, one direction per key, and na_position decides where the missing values go:

ranked = df.sort_values(
    ["neighborhood", "price"], ascending=[True, False], na_position="first"
)
print(ranked[["neighborhood", "price"]].head(4))

    neighborhood      price
129     Downtown        NaN
141     Downtown        NaN
200     Downtown  5200000.0
19      Downtown   295000.0

The neighborhoods run A to Z, the prices inside each run high to low, and na_position="first" puts Downtown's two unpriced listings at the top of the group. The labels on the left moved with their rows, so they no longer count 0, 1, 2. That is Lesson 2.4.2's split between labels and positions, and it bites hardest when a label found in one step is fed to iloc in the next:

clean = pd.read_csv("housing.csv").drop_duplicates(keep="last")
clean = clean[clean["rooms"].between(1, 20)]
print(clean.index[:3].tolist())  # [5, 6, 7]
big = clean.index[clean["price"] > 1_000_000]
print(big.tolist())              # [200, 203]
print(clean["price"].iloc[200])  # 268000.0

keep="last" kept the later copy of each repeated row, so labels 0 to 4 are gone, and the filter removed label 202. Label 200 is the 5,200,000 listing, position 200 is a 268,000 one, and clean.iloc[203] raises IndexError: single positional indexer is out-of-bounds. reset_index(drop=True) numbers the rows again from 0 and drops the old labels:

clean = clean.reset_index(drop=True)
big = clean.index[clean["price"] > 1_000_000]
print(big.tolist())              # [195, 197]
print(clean["price"].iloc[195])  # 5200000.0

Without drop=True the old labels come back as a column named index. Reset once, after the last step that removes rows, because every later filter opens new gaps.

DON'T:

• DON'T call rename without assigning the result. The next df["area_m2"] raises KeyError: 'area_m2', and a misspelled key renames nothing without a word unless you pass errors="raise".
• DON'T feed a label from a filtered or deduplicated frame to iloc. Label 200 held the 5,200,000 listing while position 200 held a 268,000 one, and a label past the end raises IndexError: single positional indexer is out-of-bounds.
• DON'T reset the index without drop=True when the old labels mean nothing. They come back as a column named index, which then travels into every file you write.

Lab Exercises

Lab Exercise 1: A Key That Removes Too Much, and a Rule Report

Load housing.csv.

(a) Print the number of rows, the number of extra copies duplicated finds, the number of rows involved in any repeat, and the number of rows drop_duplicates() leaves.

(b) A teammate deduplicates on ["price", "listing_date"]. Print how many rows that key flags and how many rows it leaves, then print the rows that are equal on the key but are not repeats of each other, with their price, neighborhood and listing date. Say in a comment why the count disagrees with (a).

(c) On the table drop_duplicates() returns, report two rules with their broken and unknown counts: rooms from 1 to 10, and year_built from 1950 to the listing's year. Print the rows that break either rule, with their rooms, year built and neighborhood.

Lab Exercise 2: Outliers, Then a Table Ready to Hand Over

Load housing.csv and remove its exact repeats.

(a) Compute the IQR fences of area_sqm, print them rounded to two decimals, and print how many areas the fences flag and how many |z| > 3 flags.

(b) Add a price_outlier column that marks prices outside their IQR fences, then cap area_sqm at its fences, printing how many values the cap changes, counted so that no missing area is included.

(c) Rename area_sqm to area_m2, sort by neighborhood A to Z and by price high to low with missing prices last, reset the index, and print the first five rows of neighborhood, price, area_m2 and price_outlier, then the first three labels and the last one.

import pandas as pd

df = pd.read_csv("housing.csv")

# (a) extra copies against rows involved
print(len(df))                              # 209
print(df.duplicated().sum())                # 5
print(df.duplicated(keep=False).sum())      # 10
deduped = df.drop_duplicates()
print(len(deduped))                         # 204

# (b) the key leaves fewer rows than there are distinct records
key = ["price", "listing_date"]
print(df.duplicated(subset=key).sum())      # 7
print(len(df.drop_duplicates(subset=key)))  # 202
on_key = df.duplicated(subset=key, keep=False)
distinct = on_key & ~df.duplicated(keep=False)
print(df.loc[distinct, ["price", "neighborhood", "listing_date"]])
# Two pairs of different listings share a price and a day, one pair
# because both prices are missing, so the key removes 2 real listings.

        price neighborhood listing_date
39   245000.0     Downtown   2024-12-14
43   245000.0    Southgate   2024-12-14
141       NaN     Downtown   2023-05-21
185       NaN    Riverside   2023-05-21

# (c) each rule as the condition that breaks it
listed_year = pd.to_datetime(deduped["listing_date"]).dt.year
rooms = deduped["rooms"]
built = deduped["year_built"]
rooms_bad = (rooms < 1) | (rooms > 10)
built_bad = (built < 1950) | (built > listed_year)
print(f"rooms 1-10: {rooms_bad.sum()} broken, {rooms.isna().sum()} unknown")
print(f"built 1950 to listed: {built_bad.sum()} broken, {listed_year.isna().sum()} unknown")
print(deduped.loc[rooms_bad | built_bad, ["rooms", "year_built", "neighborhood"]])

rooms 1-10: 2 broken, 0 unknown
built 1950 to listed: 1 broken, 3 unknown
     rooms  year_built neighborhood
202     99        1998    Hillcrest
203     12        1911     Eastgate

import pandas as pd

df = pd.read_csv("housing.csv").drop_duplicates()

# (a) fences and z-scores on the area
area = df["area_sqm"]
a1 = area.quantile(0.25)
a3 = area.quantile(0.75)
a_low = a1 - 1.5 * (a3 - a1)
a_high = a3 + 1.5 * (a3 - a1)
print(round(a_low, 2), round(a_high, 2))  # 24.15 185.35
area_out = (area < a_low) | (area > a_high)
print(area_out.sum())                     # 2
z_area = (area - area.mean()) / area.std()
print((z_area.abs() > 3).sum())           # 2

# (b) flag the prices, cap the areas, count before the cap
price = df["price"]
p1 = price.quantile(0.25)
p3 = price.quantile(0.75)
p_low = p1 - 1.5 * (p3 - p1)
p_high = p3 + 1.5 * (p3 - p1)
df["price_outlier"] = (price < p_low) | (price > p_high)
print(df["price_outlier"].sum())          # 2
changes = area_out.sum()
print(changes)                            # 2
df["area_sqm"] = area.clip(a_low, a_high)

# (c) rename, sort, renumber, in that order
df = df.rename(columns={"area_sqm": "area_m2"})
df = df.sort_values(
    ["neighborhood", "price"], ascending=[True, False], na_position="last"
)
df = df.reset_index(drop=True)
cols = ["neighborhood", "price", "area_m2", "price_outlier"]
print(df[cols].head(5))
print(df.index[:3].tolist(), df.index[-1])

  neighborhood      price  area_m2  price_outlier
0     Downtown  5200000.0     95.0           True
1     Downtown   295000.0    118.0          False
2     Downtown   291000.0     93.1          False
3     Downtown   271000.0      NaN          False
4     Downtown   269000.0     93.8          False
[0, 1, 2] 203

Theory Assessment

Q1: The Deduplication That Reported Success

A teammate deduplicates housing.csv on ["neighborhood", "listing_date"]. duplicated(subset=...) flags 10 rows, the result has exactly 10 fewer rows than the file, and the teammate reports 199 listings. Explain why both counts are right and the table is still wrong, what two missing dates had to do with it, and the comparison that would have caught it.

Hint: Ask what subset= tells duplicated to ignore, and how many rows drop_duplicates() with no argument leaves.

Q2: Fifteen Broken Areas That Are Not

A validation script reports 15 violations of the rule "area_sqm above 0", counted with ~(df["area_sqm"] > 0), and the smallest area in the file is 26.4. Explain the 15, write the mask that counts the real violations, and say what a correct report shows for this rule. Then explain why (capped_area != area).sum() reports 17 after area.clip(20, 500) changed 2 values.

Hint: A NaN fails every comparison but !=, and ~ turns a failure into a pass.

Q3: The 12-Room Listing

On the deduplicated table, the IQR fences flag both the 99-room and the 12-room listings, and |z| > 3 flags only the 99. Explain, with the numbers the lesson printed, why the z-score passed the 12, why the fences did not, and what a z-score scan that finds exactly one outlier does and does not tell you.

Hint: Follow the standard deviation in the z-score's denominator, with the 99 in the column and without it.

Q4: Four Readings of One Cleaning Script

A teammate runs this script, saved as quick_clean.py, on housing.csv:

import pandas as pd

df = pd.read_csv("housing.csv")
before = len(df)
df = df.drop_duplicates(subset=["price", "listing_date"])
print(before - len(df))
z = (df["price"] - df["price"].mean()) / df["price"].std()
kept = df[z.abs() <= 3]
print(len(df) - len(kept))
print(kept["price"].isna().sum())
rooms = kept["rooms"]
rooms_z = (rooms - rooms.mean()) / rooms.std()
print((rooms_z.abs() > 3).sum())

It prints 7, 20, 0 and 1, nothing raises, and the teammate's report reads: "7 duplicate listings removed, 20 price outliers removed, no missing prices left, 1 room outlier." Say which readings are true and which are false, and why, and rewrite the line that removed rows nobody decided to remove.

Hint: Count each removal by its reason, not its total. Ask what z.abs() <= 3 is for a listing with no price, and where the 12-room listing went.

Assignment

Assignment: A Cleaning Report a Teammate Can Check

Write clean_listings.py, which runs this lesson's checks on housing.csv and prints, at every step, the counts a teammate needs to check it.

First, the rows: how many came in, how many exact repeats were removed and how many are left. Treat ["neighborhood", "area_sqm", "rooms", "year_built"] as the identity of a property, print how many rows that key would keep, and print whether it agrees with the number of distinct records before you rely on it.

Second, the rules, each with its broken and unknown counts: rooms from 1 to 20, and year_built no later than the listing's year. A value that breaks a rule becomes missing in its own column, and the rest of its row stays. This needs one more call: s.mask(cond) returns a copy of s with a missing value wherever cond is True and every other value unchanged. Print each column's missing count before and after, and the dtype rooms ends with.

Third, the outliers: mark prices outside their IQR fences in a price_outlier column, and cap area_sqm at its IQR fences, printing how many values the cap changes.

Last, the shape: rename area_sqm to area_m2, sort by neighborhood and by price high to low with missing prices last, reset the index, and print the first three rows of neighborhood, price, rooms and price_outlier, the number of rows and the last label.

Hints:

• Write each rule as the condition that breaks it, so s.mask(broken) turns only real violations into missing values and leaves the existing gaps where they were.
• Compute every fence from the column before you change it, and count the cap's changes with the fences rather than with != afterwards.

import pandas as pd

raw = pd.read_csv("housing.csv")

# rows, repeats, and the key checked before anyone relies on it
distinct = raw.drop_duplicates()
repeats = len(raw) - len(distinct)
print(f"rows in {len(raw)}, repeats {repeats}, left {len(distinct)}")
key = ["neighborhood", "area_sqm", "rooms", "year_built"]
by_key = len(raw.drop_duplicates(subset=key))
print(f"property key keeps {by_key}, agrees: {by_key == len(distinct)}")
df = distinct

# rules as breaking conditions, then broken values become missing
listed_year = pd.to_datetime(df["listing_date"]).dt.year
rules = {
    "rooms": (df["rooms"] < 1) | (df["rooms"] > 20),
    "year_built": df["year_built"] > listed_year,
}
unknown = {"rooms": df["rooms"].isna(), "year_built": listed_year.isna()}
for col, broken in rules.items():
    print(f"{col}: {broken.sum()} broken, {unknown[col].sum()} unknown")
    before = df[col].isna().sum()
    df[col] = df[col].mask(broken)
    print(f"{col}: {before} missing before, {df[col].isna().sum()} after")
print(df["rooms"].dtype)

rows in 209, repeats 5, left 204
property key keeps 204, agrees: True
rooms: 1 broken, 0 unknown
rooms: 0 missing before, 1 after
year_built: 0 broken, 3 unknown
year_built: 0 missing before, 0 after
float64

# fences from the columns as they stand, before anything changes them
price = df["price"]
p1, p3 = price.quantile(0.25), price.quantile(0.75)
p_low, p_high = p1 - 1.5 * (p3 - p1), p3 + 1.5 * (p3 - p1)
df["price_outlier"] = (price < p_low) | (price > p_high)
area = df["area_sqm"]
a1, a3 = area.quantile(0.25), area.quantile(0.75)
a_low, a_high = a1 - 1.5 * (a3 - a1), a3 + 1.5 * (a3 - a1)
capped = ((area < a_low) | (area > a_high)).sum()
df["area_sqm"] = area.clip(a_low, a_high)
flagged = df["price_outlier"].sum()
print(f"price outliers flagged {flagged}, areas capped {capped}")

# the shape: rename, sort, then renumber once
df = df.rename(columns={"area_sqm": "area_m2"})
df = df.sort_values(
    ["neighborhood", "price"], ascending=[True, False], na_position="last"
)
df = df.reset_index(drop=True)
print(df[["neighborhood", "price", "rooms", "price_outlier"]].head(3))
print(len(df), df.index[-1])

price outliers flagged 2, areas capped 2
  neighborhood      price  rooms  price_outlier
0     Downtown  5200000.0    3.0           True
1     Downtown   295000.0    1.0          False
2     Downtown   291000.0    2.0          False
204 203

Additional Resources

Optional, and nothing later depends on either. Worth opening if you want the rules of a tidy table from their source, or the other kind of duplicate pandas can hold.

• Paper - Hadley Wickham, "Tidy Data", Journal of Statistical Software, 2014. The openly licensed paper behind the three rules, and the few operations that tidy a messy table.
• Official docs - pandas User Guide, "Duplicate Labels". Repeated index labels, which drop_duplicates never looks at, and how to make pandas refuse them.

Summary

Every check in this lesson asks one question from a different side: does the table still describe the listings it came from? A duplicate asks whether each listing appears once, a range rule asks whether a value could exist, an outlier fence asks which values a person should look at before a model learns from them, and the tidy rules ask whether the shape lets every later tool reach the facts at all. Each is a decision about the data, written as code that runs the same way every time.

The sharpest point is that a correct count can describe a wrong table. A deduplication removes exactly the rows it flagged and still deletes real listings, a z filter keeps exactly the rows inside its threshold and loses every unpriced listing along with the outliers, and clip changes exactly the values beyond its bounds while != reports more. In each case the code did what it said. The defense is reconciliation, two counts set side by side on purpose: the rows a key keeps against the distinct records, the broken values against the unknown ones, the changes counted before a cap against the changes counted after.

The z-score's blind spot reaches furthest. A ruler built from the data it measures can be bent by the very values it is meant to find, and the answer was a second ruler that ranks instead of averaging, an instinct that returns every time a number has to be trusted. The table that leaves this lesson has no repeats, no impossible value left unexplained, its extremes decided column by column and labels that match positions, the table Section 2.6 transforms and aggregates.

Cheat Sheet

Part A - Reference Table

Part B - Compact Code Block

import pandas as pd
df = pd.read_csv("housing.csv")

distinct = df.drop_duplicates()
key = ["neighborhood", "area_sqm", "rooms", "year_built"]
by_key = df.drop_duplicates(subset=key)
agree = len(by_key) == len(distinct)  # must be True

rooms = df["rooms"]
broken = (rooms < 1) | (rooms > 20)   # False if NaN
unknown = rooms.isna()                # report apart

p = df["price"]
q1, q3 = p.quantile(0.25), p.quantile(0.75)
low = q1 - 1.5 * (q3 - q1)
high = q3 + 1.5 * (q3 - q1)
flag = (p < low) | (p > high)         # ranks, robust
z = (p - p.mean()) / p.std()          # extremes mask it
changed = flag.sum()                  # count, then cap
capped = p.clip(low, high)

df = df.rename(columns={"area_sqm": "area_m2"})
df = df.sort_values(["neighborhood", "price"], ascending=[True, False])
df = df.reset_index(drop=True)        # labels = positions