Handle messy values

What you will be able to do

Turn the profile into decisions, and the decisions into one script. Each problem gets an action that you can explain. The original values stay, and every input row ends in exactly one place.

NotebookDo this lesson in a notebookColab · Kaggle · GitHub

The notebook writes the full clean.py in the same parts as this page, and runs it. It has the tasks and checks of the lesson and the module practice. Its setup cells install pandas, build the database and make the extract, so it needs no local setup. Read the explanations here, and run the code there.

Open in ColabOpen in KaggleView on GitHub

Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• The problems that the profile found, and what each missing value means, from Profile before changing.
• Loading with dtype=, drop_duplicates(), the "string" type with fillna(False), and changing rows with .loc, from Transform with Python.

What you need on your computer (only for the local route; the notebook needs none of it)

• The folder ~/projects/ticket-data with the extract data/extract/tickets.csv (1,416 rows) and data/extract/outcomes.csv (1,869 rows), from Reproducible extraction: Guided practice. If they are missing, run python scripts/extract.py again.
• The environment .venv with pandas 3.0.6, from Profile before changing: The same profile in pandas. Activate it in a terminal in the project folder. The prompt starts with (.venv).
• scripts/clean.py with load_tickets(), from Transform with Python: Worked example. This lesson adds to it. If your script is missing or broken, copy scripts/clean.py from the course’s finished scripts. It already has the steps of this lesson: read each part below and compare.
• reports/profile.md, from Profile before changing: Guided practice. The decisions in this lesson start from it.

Tested on macOS with Python 3.14.6 and pandas 3.0.6. The notebook was tested in Jupyter.

Three actions: exclude, correct, impute

For each problem in the profile, you have three possible actions:

• Exclusion: you take the row out of the clean table. You do not delete it: it goes to a quarantine table, with a reject reason. A quarantine table is a separate file for rows that you cannot use with confidence. A person can check those rows later.
• Correction: you change a value to the value that you know is correct, because a rule explains the error. The original stays in a second column.
• Imputation: you replace a missing value with an estimate, for example the median of the column. The estimate is a guess, not a fact.

There is a fourth choice, which is often the best one: keep the value missing, and say why in a flag column.

Look at four made-up tickets:

ticket_id	channel	order_value	created_at	joined_on	Action	Why
T-1	Email	42.50	2026-03-02	2025-06-01	correct channel to email	The rule is clear: the same channel, another spelling.
T-2	chat	4250	2026-01-20	2025-11-05	correct to 42.50	Only if the rule fits: a web form ticket before 1 February is in cents. T-2 is a chat ticket, so this rule does not fit. Keep 4250, and ask.
T-3	phone	NULL	2026-03-03	2024-02-11	keep missing	T-3 is an account ticket: there is no order.
T-4	chat	64.50	2026-03-04	2026-03-10	quarantine	The ticket is older than the account. One of the two dates is wrong, and you cannot know which.

How to choose:

• Correct only when you know the true value.
• Keep missing when missing has a meaning.
• Quarantine when the row is wrong and you cannot repair it.
• Impute only when the model needs a number, and the value exists but is unknown.

The decisions for Larkfield

Mei brings the profile to Grace, the support lead. Together they decide each problem. The counts are for the extract after the 14 exact duplicates are removed (1,402 rows):

Problem	Rows	Action	New value	Original kept in
Exact duplicate rows	14	remove, and count	(row removed)	the count in reports/cleaning.json
Other spellings of channel	39	correct: strip, lower, webform → web_form	email, chat, web_form	channel_raw
channel is NULL	38	keep missing, as its own category	unknown	channel_raw (NULL)
segment is Trade	(customers)	correct: lower-case	trade	—
No customer_id (guest)	12	correct by meaning	segment guest	customer_id is still NULL
Customer not in customers	9	keep missing, as its own category	segment unknown	customer_id
region is NULL	139	keep missing, as its own category	unknown	—
order_value NULL, account team	188	keep missing	NULL, flag not_applicable	order_value_raw
order_value is -1	11	correct the sentinel to a real missing value	NULL, flag unknown	order_value_raw
Web form before 2026-02-01	24	correct: divide by 100	dollars, flag converted_from_cents	order_value_raw
Created before the customer joined	3	quarantine	—	data/clean/quarantine.csv
word_count above 10,000	1	quarantine	—	data/clean/quarantine.csv
word_count is 0	6	keep: valid (attachment only)	—	—

Why quarantine T-10361, the ticket with 48,210 words, and not correct it? The true word count of the customer’s own text is not known. A model that sees 48,210 next to values of about 100 is pulled by one row. Grace can check it by hand and return it later.

Where is the imputation? The 11 unknown order values exist, but nobody knows them. A model needs a number there. Mei decides now that they will get the median order value, with an extra column that says “this value was imputed”. But she does not fill them in the clean table. The median must come from the training rows only, so the imputation happens in Module 5, in Fit transformations correctly. If you compute the median on all rows, the test rows influence the training data.

The map from raw to clean

Every input row ends in one place: 1,416 = 1,398 clean + 4 quarantined + 14 duplicates removed. The counts on the arrows come from reports/cleaning.json.

Text description of the diagram

The extract, data/extract/tickets.csv, has 1,416 rows. The first step removes 14 exact duplicates and counts them, which leaves 1,402 rows. The second step corrects channel, segment, region and order_value, and keeps the originals in *_raw columns and a flag. It does not change the number of rows: 1,402. The third step applies the quarantine rules. 1,398 rows pass and go to data/clean/tickets.csv. 4 rows fail: 3 tickets created before the customer joined and 1 ticket with more than 10,000 words. They go to data/clean/quarantine.csv with a reject reason. The file reports/cleaning.json records all three counts.

Worked example: Mei writes the cleaning step

Mei adds three constants in scripts/clean.py: CHANNELS above TIME_FORMAT, and the other two below it. (TIME_FORMAT is already in the file; it is shown here so you can see where the new lines go.) Each constant holds a decision, with a name:

scripts/clean.py (constants)

CHANNELS = {"email", "chat", "phone", "web_form"}
TIME_FORMAT = "%Y-%m-%d %H:%M:%S"
WEB_FORM_FIXED = pd.Timestamp("2026-02-01")  # the web form sent cents before this day
MAX_WORDS = 10_000

Then she writes clean_tickets, below load_tickets. It returns the clean rows, the quarantined rows, and a dictionary of counts. Read it part by part.

scripts/clean.py (clean_tickets, part 1: duplicates and categories)

def clean_tickets(raw):
    counts = {"input_rows": len(raw)}
    df = raw.drop_duplicates()
    counts["exact_duplicates_removed"] = len(raw) - len(df)
    assert df["ticket_id"].is_unique, "a ticket ID appears twice with different values"

    # Channel: one spelling per value. A missing channel stays missing, and
    # becomes its own category, "unknown", so the model can still use the row.
    df["channel_raw"] = df["channel"]
    channel = df["channel"].str.strip().str.lower().replace({"webform": "web_form"})
    counts["channel_respelled"] = int((channel != df["channel"]).fillna(False).sum())
    df["channel"] = channel.fillna("unknown")
    assert set(df["channel"]) <= CHANNELS | {"unknown"}

    # Segment and region: the customer table spells "Trade" in two ways.
    # Guests have no customer; deleted customers have an ID but no row.
    df["segment"] = df["segment"].str.strip().str.lower()
    no_customer = df["customer_id"].isna()
    df.loc[no_customer, "segment"] = "guest"
    df["segment"] = df["segment"].fillna("unknown")
    df["region"] = df["region"].fillna("unknown")

• assert df["ticket_id"].is_unique checks the profile’s finding: after removing exact copies, no ID is left twice. If a new export has two different rows for one ticket, the script stops here, with a message, instead of keeping both.
• assert set(df["channel"]) <= CHANNELS | {"unknown"} checks that every channel is now in the allowed list. <= means “is a subset of”. A new spelling in a later export stops the script.
• The guests get guest first. Only the rows that are still missing after that get unknown: the 9 tickets of deleted customers.

scripts/clean.py (clean_tickets, part 2: order value)

    # Order value: keep the original, then correct or blank it, with a flag.
    df["order_value_raw"] = df["order_value"]
    df["order_value_flag"] = "ok"
    df.loc[df["team"] == "account", "order_value_flag"] = "not_applicable"
    unknown = df["order_value"] == -1
    df.loc[unknown, "order_value"] = pd.NA
    df.loc[unknown, "order_value_flag"] = "unknown"
    cents = (df["channel"] == "web_form") & (df["created_at"] < WEB_FORM_FIXED) & df["order_value"].notna()
    df.loc[cents, "order_value"] = (df.loc[cents, "order_value"] / 100).round(2)
    df.loc[cents, "order_value_flag"] = "converted_from_cents"
    counts.update({f"order_value_{k}": int(v) for k, v in df["order_value_flag"].value_counts().items()})

The order of the steps matters. The cents rule uses the corrected channel, so the ticket with webform (T-10013) is found too. The rule also skips missing values, so an account ticket from the web form stays missing. The last line counts each flag.

scripts/clean.py (clean_tickets, part 3: quarantine and the row check)

    # Rows we cannot correct with confidence go to quarantine, with a reason.
    df["reject_reason"] = pd.NA
    df.loc[df["created_at"] < df["joined_on"], "reject_reason"] = "created_before_customer_joined"
    df.loc[df["word_count"] > MAX_WORDS, "reject_reason"] = "word_count_above_10000"
    quarantine = df[df["reject_reason"].notna()]
    clean = df[df["reject_reason"].isna()].drop(columns="reject_reason")
    counts["quarantined"] = len(quarantine)
    counts["clean_rows"] = len(clean)
    assert counts["clean_rows"] + counts["quarantined"] + counts["exact_duplicates_removed"] == counts["input_rows"]
    return clean, quarantine, counts

The last assert is the row check: clean + quarantined + removed duplicates must equal the input. If a later change drops rows without a reason, the script stops.

The outcome events need two steps. The 30 repeated events (same ticket, time and status, but a new outcome_id) go: Mei keeps the one with the lowest outcome_id, the first one logged. The events of the 4 quarantined tickets go too, because their ticket is not in the clean table.

scripts/clean.py (clean_outcomes)

def clean_outcomes(tickets):
    df = pd.read_csv(IN / "outcomes.csv", dtype={"csat": "Int64"}, keep_default_na=False, na_values=[""])
    df["recorded_at"] = pd.to_datetime(df["recorded_at"], format=TIME_FORMAT)
    counts = {"input_rows": len(df)}
    # A retry logged some events twice: same ticket, time and status, new ID.
    # Keep the first one logged.
    df = df.sort_values("outcome_id").drop_duplicates(["ticket_id", "recorded_at", "status"], keep="first")
    counts["repeated_events_removed"] = counts["input_rows"] - len(df)
    df = df[df["ticket_id"].isin(tickets["ticket_id"])]
    counts["events_of_quarantined_tickets"] = counts["input_rows"] - counts["repeated_events_removed"] - len(df)
    counts["clean_rows"] = len(df)
    return df, counts

Last, Mei replaces the temporary main() from lesson 2. The new one writes three CSV files and the counts:

scripts/clean.py (main)

def main():
    OUT.mkdir(parents=True, exist_ok=True)
    REPORTS.mkdir(exist_ok=True)
    tickets, quarantine, ticket_counts = clean_tickets(load_tickets())
    outcomes, outcome_counts = clean_outcomes(tickets)
    for df in (tickets, quarantine):
        df["joined_on"] = df["joined_on"].dt.strftime("%Y-%m-%d")
    tickets.to_csv(OUT / "tickets.csv", index=False, date_format=TIME_FORMAT)
    quarantine.to_csv(OUT / "quarantine.csv", index=False, date_format=TIME_FORMAT)
    outcomes.to_csv(OUT / "outcomes.csv", index=False, date_format=TIME_FORMAT)
    summary = {"tickets": ticket_counts, "outcomes": outcome_counts}
    (REPORTS / "cleaning.json").write_text(json.dumps(summary, indent=2) + "\n", encoding="utf-8")
    print(json.dumps(summary, indent=2))

joined_on is a day without a time. The two strftime lines write it as 2026-04-05, not as 2026-04-05 00:00:00. Predict before you run: how many clean ticket rows?

Terminal

python scripts/clean.py

Output

{
  "tickets": {
    "input_rows": 1416,
    "exact_duplicates_removed": 14,
    "channel_respelled": 39,
    "order_value_ok": 1179,
    "order_value_not_applicable": 188,
    "order_value_converted_from_cents": 24,
    "order_value_unknown": 11,
    "quarantined": 4,
    "clean_rows": 1398
  },
  "outcomes": {
    "input_rows": 1869,
    "repeated_events_removed": 30,
    "events_of_quarantined_tickets": 5,
    "clean_rows": 1834
  }
}

Mei reconciles each group of numbers:

Check	Calculation	Result
Ticket rows	1,398 clean + 4 quarantined + 14 duplicates	1,416 = the extract
Order value flags	1,179 + 188 + 24 + 11	1,402: the flags are counted after the duplicates are removed and before the quarantine
Outcome events	1,834 clean + 30 repeated + 5 of quarantined tickets	1,869 = the extract

The originals are still there. Mei looks at the quarantine file and at the first clean row with each flag:

scripts/show_clean.py

import pandas as pd

quarantine = pd.read_csv("data/clean/quarantine.csv")
columns = ["ticket_id", "created_at", "joined_on", "word_count", "reject_reason"]
print(quarantine[columns].to_string(index=False))

clean = pd.read_csv("data/clean/tickets.csv", keep_default_na=False, na_values=[""])
columns = ["ticket_id", "team", "order_value_raw", "order_value", "order_value_flag"]
first_of_each = clean.drop_duplicates("order_value_flag")
print(first_of_each[columns].to_string(index=False))

Output

ticket_id          created_at  joined_on  word_count                  reject_reason
  T-10361 2026-02-20 07:23:19 2025-11-22       48210         word_count_above_10000
  T-11185 2026-03-25 17:00:00 2026-04-05          40 created_before_customer_joined
  T-11224 2026-05-17 23:00:00 2026-05-24          98 created_before_customer_joined
  T-11375 2026-03-23 23:00:00 2026-04-10          73 created_before_customer_joined
ticket_id     team  order_value_raw  order_value     order_value_flag
  T-10001  returns            47.25        47.25                   ok
  T-10013 warranty          3260.00        32.60 converted_from_cents
  T-10023  account              NaN          NaN       not_applicable
  T-10046  returns            -1.00          NaN              unknown

drop_duplicates("order_value_flag") keeps the first row of each flag value: a quick way to see one example of each. Anyone can now see what changed, check one row by hand, and undo a decision.

The mistake: filling missing values without knowing what they mean

order_value has 199 missing values in the clean table. A common reaction is “fill them with the mean”. Mei shows Omar, the product manager, what that does:

scripts/try_fill.py

import pandas as pd

clean = pd.read_csv("data/clean/tickets.csv", keep_default_na=False, na_values=[""])
value = clean["order_value"]
print(f"rows: {len(clean)}, with a value: {value.notna().sum()}, missing: {value.isna().sum()}")
print(clean.loc[value.isna(), "order_value_flag"].value_counts())

mean = round(value.mean(), 2)
filled = value.fillna(mean)
account = clean["team"] == "account"
print(f"mean of the real values: {mean}")
print(f"account tickets with an order after filling: {filled[account].notna().sum()}")
print(f"orders invented for account tickets: {filled[account].sum():.2f} dollars")

Output

rows: 1398, with a value: 1199, missing: 199
order_value_flag
not_applicable    188
unknown            11
Name: count, dtype: int64
mean of the real values: 123.08
account tickets with an order after filling: 188
orders invented for account tickets: 23139.04 dollars

188 of the 199 missing values are account tickets. They have no order. Filling them gives 188 password resets an order of 123.08 dollars each: 23,139.04 dollars of orders that do not exist. The column now says “every ticket has an order”, which is false, and the difference between account tickets and other tickets is gone. Only the 11 unknown values are real orders with an unknown amount. For them, an estimate is reasonable, with a flag, and computed on training data only.

Common mistake

The missing values are filled, and the data now says something false

Symptom: after a “fill the missing values” step, the column has no missing values, and its average looks normal. But every account ticket has an order of 123.08 dollars, and a report shows 23,139.04 dollars of orders that never existed. Nothing fails.

Cause: the code treated every missing value the same. Here, missing has two meanings: “there is no order” (188 account tickets) and “the amount is unknown” (11 tickets that had -1). The fill is correct for neither the first meaning nor a model that needs to know the difference.

Fix:

1. Before you fill a column, group its missing values by a flag or by a related column, such as team. Ask the data owner what each group means.
2. Keep “not applicable” values missing, and mark them with a flag such as order_value_flag = not_applicable.
3. Impute only values that exist but are unknown. Add an indicator column, so the model knows which values are estimates.
4. Learn the estimate (the mean or the median) from the training rows only. Module 5 does this inside the model’s preprocessing.

In AI work: the model learns from the filled values as if they were real. It cannot tell account tickets from small orders, and every number that you report from the column is wrong.

Guided practice: finish clean.py

1. Add the constants CHANNELS, WEB_FORM_FIXED and MAX_WORDS to scripts/clean.py, around TIME_FORMAT.
2. Add the function clean_tickets, all three parts, below load_tickets.
3. Add the function clean_outcomes.
4. Replace the temporary main() with the new one.
5. Run python scripts/clean.py. Check that clean_rows is 1398 for tickets and 1834 for outcomes.
6. Open reports/cleaning.json. It has the same counts as the output.
7. Make scripts/show_clean.py, and run it. Check that the quarantine has 4 rows, each with a reason.

If an assert stops the script, the message names the check that failed. Compare your code with the parts above. The most common cause is a missing line in part 1, so a channel spelling stays.

Your turn: one step in a different order

In part 2, the cents rule uses the corrected channel. Change it to use channel_raw, the original value:

cents = (df["channel_raw"] == "web_form") & (df["created_at"] < WEB_FORM_FIXED) & df["order_value"].notna()

Predict first: how many rows does the rule convert now? Which ticket is different? Run the script, compare the counts with the first run, and then change the line back.

Show the answer

order_value_converted_from_cents is 23, not 24, and order_value_ok is 1180, not 1179. T-10013 has channel_raw = webform, so the rule does not see it. Its order_value stays 3,260 dollars, with the flag ok. Every other count is the same, and the row check still passes: the row check counts rows, not values. This is why the order of the steps is a decision to write down: “correct the channel first, then the units”.

Module practice

Worked example: seven messy tickets

The outline’s task for this module: clean deliberately messy ticket records, and create a quality summary. Mei shows the method first on seven made-up rows, so every intermediate result is visible. Each row has one problem:

tiny_clean.py

import io

import pandas as pd

MESSY = """ticket_id,team,channel,created_at,joined_on,order_value,word_count
T-1,delivery,Email,2026-01-10 09:00:00,2025-06-01,42.5,80
T-2,account,chat,2026-03-02 11:30:00,2024-02-11,,45
T-3,returns,web_form,2026-03-03 14:00:00,2025-01-20,-1,120
T-3,returns,web_form,2026-03-03 14:00:00,2025-01-20,-1,120
T-4,payment,,2026-03-04 08:15:00,2026-03-10,64.5,60
T-5,warranty,phone,2026-03-05 16:45:00,2023-09-30,210,31000
T-6,delivery,web_form,2026-01-20 10:00:00,2025-11-05,4250,95
"""

raw = pd.read_csv(io.StringIO(MESSY), dtype={"channel": "string", "order_value": "Float64"},
                  keep_default_na=False, na_values=[""])
raw["created_at"] = pd.to_datetime(raw["created_at"], format="%Y-%m-%d %H:%M:%S")
raw["joined_on"] = pd.to_datetime(raw["joined_on"], format="%Y-%m-%d")
print(f"1. input: {len(raw)} rows")

df = raw.drop_duplicates()
print(f"2. exact duplicates removed: {len(raw) - len(df)} -> {len(df)} rows")

df["channel_raw"] = df["channel"]
df["channel"] = df["channel"].str.strip().str.lower().fillna("unknown")

df["order_value_raw"] = df["order_value"]
df["order_value_flag"] = "ok"
df.loc[df["team"] == "account", "order_value_flag"] = "not_applicable"
unknown = df["order_value"] == -1
df.loc[unknown, "order_value"] = pd.NA
df.loc[unknown, "order_value_flag"] = "unknown"
cents = (df["channel"] == "web_form") & (df["created_at"] < "2026-02-01") & df["order_value"].notna()
df.loc[cents, "order_value"] = (df.loc[cents, "order_value"] / 100).round(2)
df.loc[cents, "order_value_flag"] = "converted_from_cents"

df["reject_reason"] = pd.NA
df.loc[df["created_at"] < df["joined_on"], "reject_reason"] = "created_before_customer_joined"
df.loc[df["word_count"] > 10_000, "reject_reason"] = "word_count_above_10000"
print(df[["ticket_id", "channel_raw", "channel", "order_value_raw", "order_value", "order_value_flag", "reject_reason"]].to_string(index=False))

quarantine = df[df["reject_reason"].notna()]
clean = df[df["reject_reason"].isna()]
print(f"3. quarantined: {len(quarantine)}, clean: {len(clean)}")
print(f"4. check: {len(clean)} + {len(quarantine)} + {len(raw) - len(df)} = {len(raw)}")

Output

1. input: 7 rows
2. exact duplicates removed: 1 -> 6 rows
ticket_id channel_raw  channel  order_value_raw  order_value     order_value_flag                  reject_reason
      T-1       Email    email             42.5         42.5                   ok                           <NA>
      T-2        chat     chat             <NA>         <NA>       not_applicable                           <NA>
      T-3    web_form web_form             -1.0         <NA>              unknown                           <NA>
      T-4        <NA>  unknown             64.5         64.5                   ok created_before_customer_joined
      T-5       phone    phone            210.0        210.0                   ok         word_count_above_10000
      T-6    web_form web_form           4250.0         42.5 converted_from_cents                           <NA>
3. quarantined: 2, clean: 4
4. check: 4 + 2 + 1 = 7

Read the table row by row. T-1: the channel is corrected, the original stays. T-2: account, so the missing value stays missing. T-3: the copy is gone, and -1 became a real missing value with the flag unknown. T-4 and T-5 go to quarantine, each with its reason. T-6: a January web form value, divided by 100. The last line accounts for all 7 rows.

Your task: the quality summary

Run scripts/clean.py on the real extract, as in the guided practice. Then write reports/quality.md for Priya, who will train the model. It must answer these questions in short sections:

1. How many rows came in, and where did each one go?
2. Which values were corrected, how many, and where are the originals?
3. Which values are still missing, and what does each kind of missing value mean?
4. Which rows are in quarantine, and why?
5. What did you assume? Write each assumption that a correction depends on, and who confirmed it.
6. What is decided but not done yet?

Show a model answer

reports/quality.md

# Quality summary: Larkfield tickets after cleaning

Input: data/extract/tickets.csv (1,416 rows) and data/extract/outcomes.csv
(1,869 rows), snapshot 2026-07-01 06:00. Script: scripts/clean.py.
All counts: reports/cleaning.json.

## Where every row went
| Tickets                              | Rows  |
|--------------------------------------|-------|
| data/clean/tickets.csv               | 1,398 |
| data/clean/quarantine.csv            | 4     |
| exact duplicates removed (counted)   | 14    |
| total = input                        | 1,416 |

Outcome events: 1,834 clean + 30 repeated events removed (kept the lowest
outcome_id) + 5 events of quarantined tickets = 1,869.

## Corrections (originals kept)
- channel: 39 respelled (strip, lower, webform -> web_form). Original: channel_raw.
- order_value: 24 web form values before 2026-02-01 divided by 100
  (flag converted_from_cents). Original: order_value_raw.
- order_value: 11 values of -1 set to missing (flag unknown). Original: order_value_raw.
- segment: lower-cased (Trade -> trade).

## Missing values and their meaning
- order_value missing, flag not_applicable (188): account tickets, no order. Do not fill.
- order_value missing, flag unknown (11): an order exists, amount unknown.
- channel "unknown" (38): not recorded.
- segment "guest" (12): no account. segment "unknown" (9): deleted customer.
- region "unknown": the customer gave no region, or there is no customer row.

## Quarantine (4 rows, data/clean/quarantine.csv)
- created_before_customer_joined: T-11185, T-11224, T-11375. One date is
  wrong; we cannot tell which.
- word_count_above_10000: T-10361 (48,210 words, a pasted log file).

## Assumptions behind the corrections
- The old web form sent cents until 2026-02-01, and only the web form (Grace).
- -1 in order_value means "not found in the order system" (Grace).
- Email, EMAIL and " chat" are the same channels as email and chat.
- Exact duplicate rows are copies from an export page overlap, not two tickets.
- 0 words means an attachment only, and is valid (Grace).

## Decided, not done yet
- The 11 unknown order values get the median with an indicator column,
  learned from the training set only (Module 5).
- Grace checks the 4 quarantined tickets by hand.

Module check

Check your understanding

Name the three actions for a messy value, and give one Larkfield example of each.

Show the answer

Exclusion: take the row out and keep it in quarantine with a reason, for example T-10361 with 48,210 words. Correction: change a value to the value you know is correct, and keep the original. Example: a January web form value of 4,503 cents → 45.03 dollars. Imputation: replace a missing value with an estimate. Example: the median for the 11 unknown order values, learned in Module 5 from the training rows only. (Removing the 14 exact duplicates is also an exclusion, but it loses no information, so it is counted, not quarantined.)

Check your understanding

Predict: a colleague adds clean["order_value"] = clean["order_value"].fillna(clean["order_value"].mean()) just before the return in clean_tickets. Does the row check still pass? What is wrong with the result?

Show the answer

The row check passes: it counts rows, and no row is added or removed. But all 199 missing values now have the mean, about 123 dollars. The 188 account tickets get an order that does not exist: about 23,000 dollars of invented orders. The 11 unknown values get an estimate without an indicator column, and the mean includes the rows that will be the test set. The flag column still says not_applicable, so the file now contradicts itself.

Check your understanding

Completion check: account for every input row of the ticket extract, and name two assumptions behind your corrections.

Show the answer

1,416 input rows = 1,398 clean rows + 4 quarantined rows + 14 exact duplicates removed. The clean rows are in data/clean/tickets.csv. The quarantined rows are in data/clean/quarantine.csv: 3 created_before_customer_joined and 1 word_count_above_10000. The script checks this with an assert, and reports/cleaning.json records each number. Example assumptions: the web form sent cents only before 2026-02-01. -1 means “unknown” in the order system. The duplicate rows are export copies, not separate tickets. Each assumption is in reports/quality.md, with the person who confirmed it.

See it end to endPrepare a leak-free table for city repair requestsCase study

The repair-requests case study repairs mis-encoded text, quarantines impossible closing times and flags batch timestamps in a real city export.

See it end to endPlan overnight bike rebalancingCase study

The bike case study finds an export defect: the end-station name is the start-station name in every row.

Recap

In this module you went from a raw export to a clean table that you can explain:

• Profile first (lesson 1): five questions, every count in reports/profile.md, and the meaning of each missing value from the person who knows the business.
• Load with types (lesson 2): dtype=, keep_default_na=False, explicit date formats, and joins with validate= and indicator=True, so quiet mistakes become errors.
• Decide per problem (lesson 3): quarantine with a reason. Correct only with a known rule, and keep the original. Keep meaningful missing values missing, with a flag. Impute later, from training data only.
• Count everything: 1,416 = 1,398 + 4 + 14 tickets, and 1,869 = 1,834 + 30 + 5 events, in reports/cleaning.json and reports/quality.md.

Next, Module 5 asks which columns the model may use. Some columns in the clean table, such as priority_now, are known only after the ticket is created. You define what is known at prediction time, and build the target.

Next: Features and target timing