Data Analysis for Engineers/Module 2

Module 2 · Section 5 of 12

Lesson 2.4 - Data Cleaning

Target: ~10 min read - 20 min hands-on

Overview

Real data has gaps. .isna() finds them, .dropna() removes rows (or columns) with missing values, and .fillna() replaces them - a fixed value, the column mean, or a forward-fill from the previous reading. We also cover duplicate detection with .duplicated() / .drop_duplicates(), and fixing an inconsistent-units column (a classic real-world problem: most rows in kWh, a handful of legacy rows in MWh).

The judgment call - drop vs. fill, and what to fill with - matters more than the syntax.

Why This Matters (Engineering Context)

Missing sensor readings are the norm, not the exception - a meter drops offline during a plant upset or a storm, exactly when its data matters most. Blindly dropping every row with any missing value can silently delete your most important abnormal-operation records.

Code-Along

# --- Missing data ---

# Count how many rows have a missing (NaN) energy_kwh value
print("Missing energy_kwh values:", energy_df["energy_kwh"].isna().sum())

# Option 1: drop rows with missing energy
# Simplest approach, but it throws away data — fine if missing rows are
# rare and you don't need every date represented
dropped_df = energy_df.dropna(subset=["energy_kwh"])
print("Rows after dropna:", len(dropped_df), "(was", len(energy_df), ")")

# Option 2: fill with 0 - defensible ONLY if "missing" reliably means "no consumption"
# Risky in general: a missing SENSOR READING is not the same as "zero energy used",
# so this would understate real consumption unless you know that's what NaN means here
filled_zero = energy_df.copy()
filled_zero["energy_kwh"] = filled_zero["energy_kwh"].fillna(0)

# Option 3: fill with the site's own mean (a gentler assumption)
# groupby("site") splits the data per site, .transform() applies the
# fillna to each group's own mean and returns a result the same shape
# as the original column (so it can be assigned straight back)
filled_mean = energy_df.copy()
filled_mean["energy_kwh"] = filled_mean.groupby("site")["energy_kwh"] \
    .transform(lambda s: s.fillna(s.mean()))

# Confirm both fill strategies actually eliminated the NaNs
print("\nMissing after fillna(0):", filled_zero["energy_kwh"].isna().sum())
print("Missing after group-mean fill:", filled_mean["energy_kwh"].isna().sum())

# --- Duplicates ---

# .duplicated() flags rows that are exact repeats of an earlier row (True = duplicate)
# .sum() counts how many True values there are (since True == 1, False == 0)
print("\nDuplicate rows:", energy_df.duplicated().sum())

# drop_duplicates() removes those repeated rows, keeping the first occurrence by default
deduped_df = energy_df.drop_duplicates()
print("Rows after drop_duplicates:", len(deduped_df), "(was", len(energy_df), ")")

# --- Inconsistent units: a few legacy rows logged in MWh, not kWh ---

# Grab a small 5-row sample to demonstrate a unit-mismatch problem
messy = energy_df.head(5).copy()

# Simulate 2 "legacy" rows that were mistakenly logged in MWh instead of kWh
# (e.g. 2.6 MWh, not 2.6 kWh — a 1000x difference!)
messy.loc[messy.index[:2], "energy_kwh"] = [2.6, 4.2]     # actually MWh!

# Add a column recording which unit each row was actually logged in
messy["unit"] = ["MWh", "MWh", "kWh", "kWh", "kWh"]

# Fix the inconsistency: wherever unit == "MWh", multiply by 1000 to convert
# to kWh; otherwise leave the value as-is (np.where is a vectorized if/else)
messy["energy_kwh_fixed"] = np.where(
    messy["unit"] == "MWh", messy["energy_kwh"] * 1000, messy["energy_kwh"])

# Show original value, its unit, and

Run it: dropna removes ~60 rows (the injected missing values); drop_duplicates removes 5. The messy table shows the "MWh" rows scaled up by 1000 while "kWh" rows pass through unchanged - the pattern for any mixed-unit column.

Practice Exercises

  1. Fill missing energy_kwh using forward-fill within each site (.groupby("site")["energy_kwh"].ffill()) and compare how many values differ from the mean-fill approach.
  2. Using deduped_df, confirm no duplicates remain with .duplicated().sum().
  3. Write a one-line boolean check: are there any negative values in energy_kwh? (There shouldn't be - a sanity check every dataset needs.)
# Try the practice exercises here

Knowledge Check

  1. What's the risk of always using .dropna() on operational sensor data?
  2. Which method finds exact duplicate rows in a DataFrame?
  3. When mixing units within one column, what must you do before treating the column as numeric?
Answer key
  1. You may disproportionately delete records from abnormal-operation periods, when meters are most likely to drop out
  2. .duplicated()
  3. Identify which rows use which unit and convert them all to one consistent unit

15 / 63 sections · Course home · Join the coaching cohort