PMGM7023 Chap.11 Cleaning a Real Dataset With a Supervised Agent
Cleaning a Real Dataset With a Supervised Agent
Describe before you change
The fourth tutorial most resembles the project. It takes a deliberately messy file and works it into an analysable one, and the sequence is what makes the result defensible: nothing is changed before the file has been described, and nothing is accepted before the description has been produced again.
Two of the six positions in that loop belong to you and cannot be delegated, which are setting the rules and comparing the file before and after against what you expected.
The first description must report, for every numeric field, how many values are present and absent, the average, the spread, the middle value, the most common value and the extremes.
For every categorical field it reports each recorded category with its count and share, leaving differences of capitalisation and spacing visible rather than tidied away, since those differences are the defect being looked for. Identifiers are summarised separately by how many distinct values they hold.
The type of a variable decides which statistic is legal
Numeric variables answer how much and how spread out.
Categorical variables answer how many and how diverse, and averaging a category code produces a number with no referent. Ordered categories such as a satisfaction scale sit between the two, and the distances between their points are not guaranteed equal, so treating a move from one to two as identical to a move from four to five is an assumption rather than a fact.
The most common error in a first description is letting an identifier into the numeric summary.
Six defects, two kinds of response
Four defects resolve to a correct answer. A record stored twice inflates every count, so confirmed duplicates are removed. One category written several ways splits a group that should be whole, so equivalent labels are standardised.
A number stored as text sorts alphabetically and refuses arithmetic, so the type is corrected. Characters mangled by a reading mismatch are repaired at the source encoding rather than edited by hand, since hand-editing fixes the instances you noticed and leaves the rest.
Two resolve to a decision that must be written down.
A blank is neither a zero nor a category: deleting rows loses cases and can bias what remains, while filling them with an average preserves the count and compresses the spread. An unusually large value may be a keying error, a unit mismatch or a genuine case, and those call for correction, investigation and retention respectively.
Sentinel codes are the defect that survives a clean description, since a negative age or an income of minus one passes every type check while distorting every average.
Deciding what a gap means
Three patterns are named, each with a different treatment. A gap unrelated to anything recorded can be filled simply or the rows deleted where few are lost.
A gap predictable from something you did observe should be predicted from those fields rather than filled with one average, which would compress the spread precisely where the range is widest. A gap that may depend on the missing value itself has no automatic fix, so the analysis is reported both ways and the difference is stated as a limit.
Capping extreme values follows its own rule.
Fix the average and spread on the verified data first, set the limits once rather than recalculating after each pass, keep the raw column and add a capped copy, and report the original figures, the limits and how many values were changed at each end.
What this chapter covers
- 01
Profile, decide, execute, re-profile and validate as an owned loop
- 02
Variable type decides which summary statistic is meaningful
- 03
Four defects that are corrections and two that are decisions
- 04
Three missingness patterns and the treatment each one allows
A decision log for three columns with gaps
- 3Count the gaps and state their share of rows.
- 5Test whether each gap is predictable from observed fields.
- 4Choose a treatment and say what it does to the spread.
Key terms
- Sentinel Code
- A sentinel code is a value used to mark an unavailable entry, such as a negative number or an implausible extreme. It is a label rather than a measurement.
- Imputation
- Imputation is filling a missing value with an estimate derived from the data. Simple versions preserve the count while narrowing the spread.
- Duplicate Record
- A duplicate record is the same entity stored more than once, which inflates counts and totals without any single value being wrong.
- Ordered Category
- An ordered category is a variable whose values have a rank but not guaranteed equal spacing, such as a satisfaction scale point.
Cleaning a Real Dataset With a Supervised Agent FAQ
Why describe a messy file before cleaning it?
Because the description is the only evidence that the cleaning was correct. A first summary that has already been tidied cannot show what changed, and the defects you are looking for, such as one category written three ways or a negative placeholder in an age column, are visible only while the raw values are intact. The same summary is produced again afterwards so the two can be compared.
How do I choose between deleting rows and filling gaps?
By the pattern behind the gap. If missingness is unrelated to anything recorded, either approach is defensible when few rows are affected. If it is predictable from a field you do have, predict from that field rather than filling with a single average. If it may depend on the missing value itself, no automatic treatment fixes it, so report the analysis both ways and state the difference as a limit.
Exam move
Practise writing the first description from memory as a checklist covering both numeric and categorical fields. Keep a decision log from the first tutorial onward, since the project requires cleaning decisions to be documented and justified. Always retain the raw column beside any cleaned one so the change can be inspected.
Working through Cleaning a Real Dataset With a Supervised Agent in PMGM7023? Sia is AskSia’s AI Management tutor — ask any PMGM7023 Cleaning a Real Dataset With a Supervised Agent question and get a clear, step-by-step explanation grounded in how PMGM7023 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
