Python Foundation for Data Engineers

Chapter 14 — Cleaning Messy Data with Pandas

Lesson 14.1 — Finding & Handling Missing Values

Every dataset you've built so far in this course, you built by hand — clean, complete, no surprises. Real data is never that polite. Fields go unfilled. Systems export blanks where a value should be. This lesson is about finding those gaps properly, and Chapter 14 as a whole is about turning genuinely messy data into something you can trust — the actual, core, everyday work of a data engineer.

python
import pandas as pd
import numpy as np

df = pd.DataFrame({
    "order_id": [10432, 10433, 10434, 10435],
    "customer": ["Priya Shah", "Raj Kumar", None, "Neha Gupta"],
    "total": [149.50, np.nan, 24.00, 310.25],
})
print(df)
Output / Note

order_id customer total 0 10432 Priya Shah 149.50 1 10433 Raj Kumar NaN 2 10434 None 24.00 3 10435 Neha Gupta 310.25

Notice NaN — "Not a Number" — pandas's standard way of representing a missing numeric value, and None, Python's own way of representing "nothing here," showing up in the text column. Both mean essentially the same thing: this value is missing. np.isnan() from NumPy is one option to check for it, but pandas gives you a purpose-built tool that's cleaner and works consistently across every column type:

python
print(df.isna())
Output / Note

order_id customer total 0 False False False 1 False False True 2 False True False 3 False False False

.isna() checks the entire table at once, returning True wherever a value is missing. On its own, a full grid of True/False isn't that useful to look at — what you actually want, almost always, is a count:

python
print(df.isna().sum())
Output / Note

order_id 0 customer 1 total 1 dtype: int64

That's exactly the missing-value report you built by hand back in Week 1, Chapter 8's CSV profiler — except now it's one line, across every column, instantly. This is a genuinely good habit to build: run .isna().sum() on almost any new dataset the moment you load it, before you do anything else with it.

Once you've found the gaps, you have real choices about what to do with them. Dropping rows with any missing value:

python
clean_df = df.dropna()
print(clean_df)
Output / Note

order_id customer total 0 10432 Priya Shah 149.50 3 10435 Neha Gupta 310.25

Or filling them in with a sensible default, using .fillna():

python
filled_df = df.fillna({"customer": "Unknown", "total": 0})
print(filled_df)
Output / Note

order_id customer total 0 10432 Priya Shah 149.50 1 10433 Raj Kumar 0.00 2 10434 Unknown 24.00 3 10435 Neha Gupta 310.25

Here's the honest, important part of this lesson, worth remembering long after the syntax fades: which choice is correct depends entirely on the situation, and getting it wrong quietly corrupts your data. Dropping a row loses information — sometimes that's exactly right, sometimes it silently throws away a real order. Filling with 0 is reasonable for a missing row count, but filling a missing total with 0 could badly understate real revenue if that order actually happened. There's no single correct default — there's only the choice that's honest about what actually happened to that data, and a data engineer's job is making that call deliberately, not automatically.

Try It Yourself: Build a small DataFrame with at least one missing value in a numeric column and one in a text column. Print .isna().sum() to confirm you can see them. Then create two separate cleaned versions: one using .dropna(), and one using .fillna() with values you think are genuinely reasonable — and write one sentence explaining why you chose those particular fill values.

Lesson 14.2 — Removing Duplicates

Back in Week 1, Chapter 4, you deduplicated customer IDs across three source systems using Python sets. That was genuinely good, correct thinking — but sets only work cleanly on simple values, like a single ID. Real duplicate rows in a table are messier — sometimes an entire row repeats exactly, and sometimes just one field, like a customer ID, repeats with different details attached. Pandas gives you tools for both.

python
import pandas as pd

df = pd.DataFrame({
    "order_id": [10432, 10433, 10432, 10434],
    "customer": ["Priya Shah", "Raj Kumar", "Priya Shah", "Amit Verma"],
    "total": [149.50, 89.99, 149.50, 24.00],
})
print(df)
Output / Note

order_id customer total 0 10432 Priya Shah 149.50 1 10433 Raj Kumar 89.99 2 10432 Priya Shah 149.50 3 10434 Amit Verma 24.00

Row 0 and row 2 are identical, top to bottom — exactly the kind of duplicate that shows up when a source system accidentally re-sends the same record. Finding them:

python
print(df.duplicated())
Output / Note

0 False 1 False 2 True 3 False

.duplicated() marks every row that's an exact repeat of one that came before it — notice row 0 stays False, because it was the first appearance; row 2, the repeat, is True. Dropping them is one line:

python
clean_df = df.drop_duplicates()
print(clean_df)
Output / Note

order_id customer total 0 10432 Priya Shah 149.50 1 10433 Raj Kumar 89.99 3 10434 Amit Verma 24.00

Often, though, you don't care whether the entire row matches — you care whether a specific field, like order_id, has already shown up, even if some other column happens to differ slightly. That's exactly the kind of duplicate a source system produces when it resends an order with an updated total. You can target that with subset:

python
df2 = pd.DataFrame({
    "order_id": [10432, 10433, 10432, 10434],
    "customer": ["Priya Shah", "Raj Kumar", "Priya Shah", "Amit Verma"],
    "total": [149.50, 89.99, 155.00, 24.00],
})

clean_df2 = df2.drop_duplicates(subset=["order_id"], keep="last")
print(clean_df2)
Output / Note

order_id customer total 1 10433 Raj Kumar 89.99 2 10432 Priya Shah 155.00 3 10434 Amit Verma 24.00

Notice keep="last" — that tells pandas "when you find a duplicate order_id, keep the most recent one, not the first." That matters here, because the second 10432 row has an updated total, 155.00, which is probably the correct, current value. The default behavior, keep="first", would have quietly kept the older, outdated total instead — a genuinely easy mistake to make without thinking about which version of a duplicate actually matters.

Try It Yourself: Build a small DataFrame with a customer_id column where one ID appears twice, with a different email value each time. Use drop_duplicates(subset=["customer_id"], keep="last") to keep only the most recent email for each customer, and print the result.

Lesson 14.3 — Fixing Types & Cleaning Text Columns

Remember the type-conversion lesson from Week 1, Chapter 2 — the one about how everything from a file arrives as text, even things that look like numbers? That exact problem shows up constantly in pandas too, and this lesson is about fixing it properly, across a whole column at once.

python
import pandas as pd

df = pd.DataFrame({
    "order_id": [10432, 10433, 10434],
    "total": ["149.50", "89.99", "unknown"],
})
print(df.dtypes)
Output / Note

order_id int64 total object

Notice total came in as object — pandas's label for text, exactly the string type from Chapter 2, even though these values look numeric. This happens constantly with real CSV exports. Converting it properly:

python
df["total"] = pd.to_numeric(df["total"], errors="coerce")
print(df)
print(df.dtypes)
Output / Note

order_id total 0 10432 149.50 1 10433 89.99 2 10434 NaN total float64

pd.to_numeric() is the pandas version of the float() conversion from Chapter 2 — except it works on an entire column at once, vectorized, exactly the way Chapter 11 taught you to think. That errors="coerce" argument is genuinely important: instead of crashing the moment it hits "unknown", the way a plain float("unknown") would, it quietly turns anything it can't convert into NaN — a proper missing value, ready for the tools from Lesson 14.1. This is honest, visible failure, exactly the philosophy from Week 1, Chapter 7 — better a clearly flagged gap than a crash, or worse, a silently wrong number.

Cleaning text columns works through .str, which unlocks every string method from Chapter 3, applied across an entire column at once:

python
df2 = pd.DataFrame({
    "customer": ["  priya shah  ", "RAJ KUMAR", "amit verma "]
})

df2["customer"] = df2["customer"].str.strip().str.title()
print(df2)
Output / Note
  customer

0 Priya Shah 1 Raj Kumar 2 Amit Verma

Read .str.strip().str.title() exactly the way you'd read name.strip().title() back in Chapter 3 — same methods, same chaining, just with .str in front to say "apply this to every value in the column." Everything you already know about cleaning a single string transfers directly, at full column scale.

Try It Yourself: Build a DataFrame with a price column containing text values like ["24.99", "N/A", "15.50"]. Convert it to numeric using pd.to_numeric() with errors="coerce", then use .isna().sum() from Lesson 14.1 to confirm exactly how many values failed to convert.

Lesson 14.4 — Parsing Dates & Catching the Timezone Trap

Dates deserve their own lesson, because they cause more quiet, hard-to-notice bugs in real pipelines than almost anything else. Let's start with the basics, then walk through the specific trap that catches even experienced engineers.

python
import pandas as pd

df = pd.DataFrame({
    "order_id": [10432, 10433, 10434],
    "order_date": ["2026-01-14", "2026/01/15", "Jan 16, 2026"],
})

df["order_date"] = pd.to_datetime(df["order_date"])
print(df)
print(df.dtypes)
Output / Note

order_id order_date 0 10432 2026-01-14 1 10433 2026-01-15 2 10434 2026-01-16 order_date datetime64[ns]

Notice pd.to_datetime() correctly handled three genuinely different date formats in the same column — a real, common situation when data arrives from multiple source systems, each with its own export habits. Once a column is a proper datetime64 type, you unlock useful tools directly:

python
print(df["order_date"].dt.day_name())
print(df["order_date"].dt.month)
Output / Note

0 Wednesday 1 Thursday 2 Friday Name: order_date, dtype: object 0 1 1 1 2 1

.dt unlocks date-specific tools, the same way .str unlocked string tools — day name, month, year, and plenty more, all available directly once a column is properly typed as a date.

Now, the trap. Here's a situation that looks completely fine at first glance, and quietly isn't:

python
event_a = pd.to_datetime("2026-01-14 09:00:00")
event_b = pd.to_datetime("2026-01-14 14:30:00+05:30")

print(event_a)
print(event_b)
Output / Note

2026-01-14 09:00:00 2026-01-14 14:30:00+05:30

Both look like they happened on January 14th — but event_b carries a timezone offset, +05:30, and event_a carries none at all. That means event_a is what's called "timezone-naive" — Python has no idea what timezone it's actually in — while event_b is "timezone-aware." Try to compare or combine values like these directly in a real pipeline, and you'll either get an outright error, or — worse — a silently wrong answer, because Python has no honest way to know if these two timestamps are 5 hours 30 minutes apart, or exactly the same moment, or something else entirely.

The fix is making your intentions explicit, rather than leaving them ambiguous:

python
event_a_aware = event_a.tz_localize("UTC")
event_b_utc = event_b.tz_convert("UTC")

print(event_a_aware)
print(event_b_utc)
Output / Note

2026-01-14 09:00:00+00:00 2026-01-14 09:00:00+00:00

.tz_localize("UTC") says "this naive timestamp is actually meant to be UTC — label it as such." .tz_convert("UTC") takes an already timezone-aware timestamp and converts it to a different zone, UTC in this case. Once both are properly labeled and converted to the same zone, you can see the honest truth: these two timestamps were actually the exact same moment all along.

The real, practical habit worth taking from this lesson: the moment timestamps enter a pipeline from more than one source, get every single one of them onto the same, explicit timezone — UTC is the standard, sensible default — before you compare, sort, or group by them. This single habit prevents one of the most common, quietly damaging bugs in real data engineering work.

Try It Yourself: Create two timestamps: one naive, representing "2026-03-01 08:00:00", and one aware, representing "2026-03-01 12:00:00+04:00". Localize the naive one to "UTC", convert the aware one to "UTC", and print both to see whether they land on the same moment or genuinely different ones.

Lesson 14.5 — Hands-On: Clean a Messy Multi-Source Sales Dataset

This is the signature exercise of Week 2 — everything from this chapter, and quite a bit from the whole week so far, working together on one dataset that looks deliberately, realistically messy.

python
import pandas as pd
import numpy as np

sales = pd.DataFrame({
    "order_id": [10432, 10433, 10434, 10432, 10435, 10436],
    "customer": ["  priya shah  ", "RAJ KUMAR", "Amit Verma", "  priya shah  ", None, "neha gupta"],
    "total": ["149.50", "89.99", "unknown", "149.50", "310.25", "45.00"],
    "order_date": ["2026-01-14", "2026/01/15", "2026-01-16", "2026-01-14", "2026-01-17", "Jan 18, 2026"],
})
print(sales)
Output / Note

order_id customer total order_date 0 10432 priya shah 149.50 2026-01-14 1 10433 RAJ KUMAR 89.99 2026/01/15 2 10434 Amit Verma unknown 2026-01-16 3 10432 priya shah 149.50 2026-01-14 4 10435 None 310.25 2026-01-17 5 10436 neha gupta 45.00 Jan 18, 2026

Take a moment to actually list the problems, the way a real data engineer would before writing a single line of cleaning code: a duplicate order (10432 appears twice, identically), inconsistent name casing and spacing, one missing customer name, one total that isn't a real number, and three different date formats.

Your task: clean every one of these issues, using tools directly from this chapter.

python
sales_clean = sales.copy()

# 1. Remove exact duplicate rows
sales_clean = sales_clean.drop_duplicates()

# 2. Clean customer names — strip, title-case, fill missing with a clear placeholder
sales_clean["customer"] = sales_clean["customer"].str.strip().str.title()
sales_clean["customer"] = sales_clean["customer"].fillna("Unknown Customer")

# 3. Convert total to numeric, turning bad values into NaN
sales_clean["total"] = pd.to_numeric(sales_clean["total"], errors="coerce")

# 4. Parse all date formats into real dates
sales_clean["order_date"] = pd.to_datetime(sales_clean["order_date"])

print(sales_clean)
print("\nMissing values after cleaning:")
print(sales_clean.isna().sum())
Output / Note

order_id customer total order_date 0 10432 Priya Shah 149.50 2026-01-14 1 10433 Raj Kumar 89.99 2026-01-15 2 10434 Amit Verma NaN 2026-01-16 4 10435 Unknown Customer 310.25 2026-01-17 5 10436 Neha Gupta 45.00 2026-01-18

Missing values after cleaning: order_id 0 customer 0 total 1 order_date 0

Look at what just happened, one issue at a time: the duplicate row is gone, every name is properly capitalized with no stray spaces, the missing name got a clear, honest placeholder instead of silently vanishing, every date parsed correctly regardless of its original format — and the one genuinely bad total value didn't crash anything or get silently guessed at. It's sitting there, visibly, as NaN, exactly where you can see it and decide what to do about it next.

That last point matters more than it might seem. A clean-looking table that quietly dropped or guessed at a bad value would be worse than this one — because this one is honest about exactly what it doesn't know, and where. That honesty is genuinely the difference between data you can trust, and data that only looks trustworthy.

Try It Yourself: Decide what should happen to the remaining NaN in total — should it be dropped, or filled with something specific? Write one line of code implementing your choice, and one sentence explaining why you made that call, the same honest reasoning practiced back in Lesson 14.1.