2.2 — Missing and Duplicated Values, Filtering, Subset Selection

Chapter 2 · DataFrames

Prof. Xuhu Wan

Section 2.2 · Chapter 2 · Learning Statistics with Python

Missing and Duplicated Values, Filtering, Subset Selection

DataFrames

Prof. Xuhu Wan

ISOM, HKUST Business School · 2026 Edition

Missing and Duplicated Values, Filtering, Subset Selection

Real data arrive with holes and repeats. You will audit the notebook’s original grid file (one column is a fifth empty, another 99.99 %), decide between filling and dropping — surgically — discover why one November hour appears twice every year, and then carve out exactly the rows and columns a model needs, predicting each row count before you run.

Audit first: count NaN per column

AEP_hourly.csv is the notebook’s energy(Clean=False): 178 258 hours, 12 utilities, lots of NaN. We keep four utilities and 2011–2012.

What does load.isna().sum().sum() / load.size measure?

  • The number of rows with at least one NaN
  • The fraction of all cells that are NaN
  • The fraction of columns that contain a NaN
  • The mean of the non-missing values

Four different diseases in one frame: AEP and COMED each missing one hour, FE missing 3 624 (the first five months of 2011), NI missing all but one of 17 540. df.isna().sum() is the first line you run on any new dataset.

Fill forward, fill backward, fill a constant

Zoom in on AEP’s single hole (04:00 on 6 December 2012). Four options — one of them is a crime in time series. Decide which before running.

Which fill is look-ahead bias when the rows are ordered in time?

  • fillna(0)
  • ffill() — carry the last value forward
  • bfill() — pull the next value backward
  • fillna(median)

Drop surgically, or fill and pay the price

dropna() removes any row with any NaN. With NI 99.99 % empty, predict how many rows survive.

How many rows does load.dropna() keep?

  • 0
  • 13 916
  • 17 539
  • All 17 540 — NaN are ignored

Rule of thumb from the lecture

When not too many values are missing: fillna(median) for numerical columns, fillna(mode) for categorical ones. Filling five months of FE with one number keeps the rows but shrinks the spread — a fifth of the column now sits exactly at the centre. When most of a column is missing (NI), drop the column: a filled column is a fiction.

Your turn: a surgical clean-up

Build clean from load in two moves: drop the hopeless NI column (drop(columns=…)), then drop the rows that still have a NaN. Result: no NaN, three columns, more than 13 000 rows.

Your turn: fill a column with its mean

The notebook asks you to fill COMED with its mean; here COMED has one hole and FE has 3 624, so fill FE.

Fill the NaN in load["FE"] with the column mean, into fe_mean. It must contain no NaN and its mean must equal the pre-fill mean (why is that automatic?).

A duplicated hour that nobody typed

Switch to the smaller pjm_hourly.csv (the notebook’s cleaned new.csv, 2014–2017). It has no NaN — but some timestamps appear twice. Predict how many, then look at which hours they are.

One duplicated timestamp per year, four years. What prints?

print(hours.index.duplicated().sum())

4

Every one is 02:00 on the first Sunday of November — the hour that repeats when US clocks fall back from daylight-saving time. Two different loads, one label. Nothing is “wrong” with the file; the calendar did it.

keep=“first”, keep=“last”, keep=False

drop_duplicates(subset=…) decides which copy survives. Predict the row counts.

hours has 4 duplicated timestamps. How many rows does keep=False remove?

  • 0 — nothing is exactly identical
  • 4 — one per duplicated label
  • 8 — both copies of every pair
  • 2

Your turn: drop both copies

Using the boolean mask form hours[~hours.index.duplicated(keep=…)], remove both copies of every duplicated hour, into nodup. Check: 8 rows fewer than hours.

Mask one column, slice the whole table

A mask built from one column selects rows of the entire frame, all columns intact. Two conditions need & / | and parentheses.

What happens with hours[hours.AEP > 15000 & hours.AEP < 16000]?

  • Works — returns the band
  • Raises — & binds before >; you need parentheses
  • Returns an empty frame silently
  • Returns every row

Same filter, more readable: query

.query() writes the filter as a string: and / or instead of & / |, and @name splices in a Python variable. Predict whether it matches the mask.

hours.query(“AEP > 15000 and AEP < 16000”) returns…

  • A SyntaxError — you cannot use and in pandas
  • Every row
  • The same rows as the boolean mask band
  • Only the first matching row

isin: membership filters

isin tests each value against a list. Combine with day_name() to split weekends from weekdays.

~ negates a boolean mask. The weekday/weekend gap (about 1 400 MW, 15 240 vs 13 795) is the industrial load that switches off on Saturday — a fact you will exploit with groupby in §2.3.

What type is a single column?

A DataFrame is a dict of Series sharing one index. Pull one column out and ask what it is.

What does hours[“AEP”] return?

  • A new one-column DataFrame
  • A Series
  • A NumPy array
  • A Python list

loc is right-inclusive, iloc is right-exclusive

.loc label slices include the end label; .iloc position slices stop before the end position. What two numbers print?

print(len(hours.loc["2017-01-01 22:00":"2017-01-02 00:00"]), len(hours.iloc[1:4]))

3 3

Filtering with loc: rows by condition, columns by name

loc[mask, columns] does both selections in one call.

Select the last two columns of hours, keeping only the rows where the last column (DEOK) is above its own mean, into sel.

Your turn: a time-ordered 80/20 split

For time series you never shuffle: the first 80 % of hours train, the last 20 % test.

Split hours by position: train = first 80 % of rows, test = the remaining rows, in time order.

What you discovered

  • isna().sum() per column is the first line on any dataset: in 2011–2012 the real file had 1 / 1 / 3 624 / 17 539 holes in four columns.
  • ffill looks back, bfill looks forward — a leak in any time-ordered frame. fillna(median) keeps rows but shrinks the spread; dropna() kept no rows because of NI — use subset=, thresh=, or drop the column.
  • The four duplicated timestamps are all 02:00 on the first Sunday of November: daylight-saving fall-back. keep="first"/"last" drops one copy, keep=False drops both.
  • A mask from one column slices the whole frame; parentheses around every comparison; query("… and …") reads naturally and @var injects variables; isin handles membership.
  • [] gives a Series, [[]] a DataFrame; loc slices are right-inclusive, iloc right-exclusive; loc[mask, cols] filters and selects at once.

Next: §2.3 — split the frame into groups, join two frames on a key, reshape long ↔︎ wide, and glue frames together.

Prof. Xuhu Wan · HKUST ISOM · Learning Statistics with Python