Cleaning as a Sequence of Decisions
A data-cleaning script should do more than make a data set look tidy. It should make clear what was missing, which observations merited review, what evidence supported any correction, and how each edit changed the analysis data. These distinctions matter in predictive analytics: a convenient edit can silently change the population being described or the information available to a future prediction.
This tutorial follows a hypothetical portfolio of ten reported claims. The monetary amounts are stated in thousands of dollars. The records are deliberately small enough to inspect, but they include several issues that commonly occur together: missing fields, a very large amount, inconsistent text labels, and low-frequency channel labels.
| Record | Amount (thousands of dollars) | Exposure | Territory as recorded | Channel as recorded |
|---|---|---|---|---|
| A | 12 | 1.0 | North | Agent |
| B | 18 | 1.0 | north | Web |
| C | 15 | 0.5 | NORTH | web |
| D | Missing | 1.0 | South | Broker |
| E | 22 | 1.0 | South | Agent |
| F | 19 | 1.0 | Soth | |
| G | 17 | 1.0 | West | Web |
| H | 160 | 1.0 | West | Agent |
| I | Missing | Missing | West | Missing |
| J | 14 | 1.0 | Missing | Phone |
For a real project, the raw extract should be preserved. In R, text can be read without automatically converting it to factors using `stringsAsFactors = FALSE`; the cleaning steps can then create separate cleaned variables. For this compact case, assume the table is stored in a data frame named `claims`, with missing entries represented by `NA`.
Diagnose Missingness by Variable and Pattern
Begin by counting missing values in each field, then examine which fields are missing on the same records. A variable-level count alone cannot distinguish one row missing several fields from several rows each missing one field. In R, `colSums(is.na(claims[c("amount_k", "exposure", "territory", "channel")]))` gives counts by field, while `rowSums(is.na(...))` gives the number of missing fields per record.
For the table, amount is missing on D and I, so it has 2 missing values. Exposure is missing only on I, territory only on J, and channel only on I, giving 1 missing value in each of those fields. Seven records have no missing values. D is missing amount only; I is missing amount, exposure, and channel; J is missing territory only.
A concise way to tabulate complete missingness patterns is to convert each row’s missing-value indicators into a string key. In R, `pattern_key <- apply(is.na(claims[c("amount_k", "exposure", "territory", "channel")]), 1, paste, collapse = "")`, followed by `table(pattern_key)`, counts the distinct patterns. With variables in the stated order, `FALSE` indicates observed and `TRUE` indicates missing.
Here \(I(\cdot)\) equals 1 when its condition is true and 0 otherwise. The pattern counts must add to the number of records because every record has exactly one pattern of observed and missing fields. That simple check can catch an incorrect variable selection or a pattern-building error. For this case the frequencies are 7 complete records, 1 with amount missing only, 1 with amount, exposure, and channel missing, and 1 with territory missing only; \(7+1+1+1=10\).
These counts describe the data; they do not explain why values are missing. Do not infer that missingness is harmless, or that a missing field should be imputed, from the pattern table alone. First check the data source, collection process, and business meaning. In particular, I’s several missing fields could reflect a record-entry problem or a different process, but this small table does not establish which.
Worked Example: Checking Missing-Value Counts and Patterns
Method. Use field-wise counts and row-wise patterns, then verify that the pattern frequencies sum to the record count. This avoids treating the total number of missing cells as the number of incomplete records.
In the hypothetical claims table there are 10 records and 4 fields under review. The field-wise missing counts are amount \(=2\), exposure \(=1\), territory \(=1\), and channel \(=1\), for 5 missing cells altogether.
The record-level patterns are: 7 records with no missing fields; D with amount missing only; I with amount, exposure, and channel missing; and J with territory missing only. Thus 3 records are incomplete, not 5. The incomplete-record proportion is \(3/10=0.30\), while the proportion of missing cells among the \(10\times4=40\) possible cells is \(5/40=0.125\).
Check. Pattern counts total \(7+1+1+1=10\), and the three incomplete patterns contain \(1+3+1=5\) missing cells. The totals agree with the field-wise summary. The common mistake this method avoids is reporting five incomplete records because there are five missing cells.
Review an Extreme Value Before Editing It
An unusually large value is a reason to investigate, not a verdict that the value is wrong. The earlier material on flagging, retaining, deleting, capping, and sensitivity analysis distinguishes these decisions. Here the objective is to incorporate an outlier review into a broader cleaning case: calculate a transparent flag, check the source, and record the disposition.
For a numerical screening rule, use the interquartile-range fences. In R, `quantile(x, c(0.25, 0.75), na.rm = TRUE)` uses the default type 7 sample quantiles. For an ordered sample of size \(n\), type 7 locates quantile \(p\) at position \(1+(n-1)p\), interpolating linearly between adjacent ordered observations when the position is not an integer. The fences are \(Q_1-1.5\,\mathrm{IQR}\) and \(Q_3+1.5\,\mathrm{IQR}\), where \(\mathrm{IQR}=Q_3-Q_1\). Values outside the fences are flagged for review; the rule does not establish that a value is erroneous.
Worked Example: Flagging and Verifying a Large Claim
Method. Exclude missing amounts from the screening calculation, apply type 7 quartiles, and then review the flagged record against its source. This avoids deleting a valid large claim merely because it lies beyond a statistical fence.
The eight observed amounts, in thousands of dollars, sort to \(12,14,15,17,18,19,22,160\). With \(n=8\), the type 7 position for \(Q_1\) is \(1+7(0.25)=2.75\), between the second and third observations. Therefore \(Q_1=14+0.75(15-14)=14.75\). The position for \(Q_3\) is \(1+7(0.75)=6.25\), between the sixth and seventh observations, so \(Q_3=19+0.25(22-19)=19.75\).
The interquartile range is \(19.75-14.75=5.00\). The lower fence is \(14.75-1.5(5)=7.25\), and the upper fence is \(19.75+1.5(5)=27.25\). Thus 160 is flagged above the upper fence, and none of the other observed amounts is outside the fences.
For this hypothetical case, assume the source record is checked and confirms that 160 is a valid amount, recorded in the same units as the other observations. Retain it and preserve a review flag; do not cap or delete it based solely on the fence. The result is one flagged value out of eight observed amounts, or \(1/8=0.125\). This does not prove the value is influential in a model; the disposition is simply that source review found no recording error.
In R, the calculation can be reproduced with `q <- quantile(claims$amount_k, c(0.25, 0.75), na.rm = TRUE)`, `iqr <- q[2] - q[1]`, and `flag <- claims$amount_k < q[1] - 1.5 * iqr | claims$amount_k > q[2] + 1.5 * iqr`. Missing amounts produce an unknown flag, so retain missingness as missing rather than counting it as an unflagged value.
Common mistake and correction. Using the median of the lower four values as \(Q_1\) and the median of the upper four as \(Q_3\) gives 14.5 and 20.5 for this sample, which are not R’s default type 7 quartiles. The fence calculation must match the stated quantile convention. If a different convention is required, state it and use it consistently.
Clean Factor Labels With Evidence and a Record
A factor can contain labels that differ only in case or surrounding whitespace, as with `North`, ` north `, and `NORTH`. Normalize those mechanical differences consistently. A spelling correction is a different kind of edit: `Soth` should become `South` only if a source dictionary, data owner, or other reliable evidence confirms that interpretation. Similar-looking text alone is not enough to determine the intended category.
For the hypothetical case, assume the data owner verifies that `Soth` is a misspelling of `South`. A reproducible workflow first trims whitespace and standardizes case, then applies a documented exact recode. For example, `territory_clean <- tolower(trimws(claims$territory))` and `territory_clean[territory_clean == "soth"] <- "south"`. The three North records then share the label `north`; the South records, including F, share `south`; West remains `west`; J remains missing. Do not silently turn missing territory into a real territory label.
The channel field illustrates why standardizing labels and combining categories are separate decisions. After trimming and converting to lowercase, the observed levels are agent (3), web (3), broker (1), mail (1), and phone (1); one record is missing channel. Suppose the data owner confirms that the intended business distinction is self-service web versus assisted service, and that agent, broker, mail, and phone all belong to the assisted-service group. Then recoding those four labels as `assisted` is supported by the defined meaning of the variable, not just by their low counts. The cleaned counts are assisted (6), web (3), and missing (1).
In R, a compact implementation is `channel_clean <- tolower(trimws(claims$channel))`, followed by `channel_clean[channel_clean %in% c("agent", "broker", "mail", "phone")] <- "assisted"`. Check the result with `table(channel_clean, useNA = "ifany")`. The `useNA` argument makes missing values visible in the summary rather than hiding them.
Worked Example: Standardizing and Grouping Factor Levels
Method. Normalize purely textual variation first, correct a typo only with verification, and then group labels only when the variable’s documented meaning supports the grouping. This avoids mistaking a convenient frequency-based recode for a valid category definition.
For territory, trimming spaces and converting to lowercase maps A, B, and C to `north`, D and E to `south`, F from `soth` to `south` after the assumed verification, and G, H, and I to `west`. J remains missing. The cleaned territory frequencies are north \(=3\), south \(=3\), west \(=3\), and missing \(=1\).
For channel, the standardized observed counts are agent \(=3\) (A, E, H), web \(=3\) (B, C, G), broker \(=1\) (D), mail \(=1\) (F), and phone \(=1\) (J); I is missing. Under the stipulated business definition, agent, broker, mail, and phone are all assisted-service channels. Their combined count is \(3+1+1+1=6\). Web remains a separate self-service category with count 3, and the one missing value remains missing.
Check. The recoded channel counts plus missing are \(6+3+1=10\), matching the ten records. The result is defensible only under the stated business definition. Without that definition or equivalent evidence, preserve the distinct labels rather than merging them simply because some are rare.
Make the Cleaning Reproducible
Keep the raw fields and create cleaned fields alongside them. Record each transformation’s target variable, rule, reason, and evidence, as well as the number of records affected. For example, the case log could state: “Territory: trim whitespace and standardize case; verified `soth` to `south` using the source dictionary; one record corrected.” A separate entry could state: “Channel: map agent, broker, mail, and phone to assisted because the documented variable definition groups them as assisted service; six observed records in the resulting group.”
A useful audit checks both the cleaned values and the rows changed. In R, compare raw and cleaned fields with `claims$territory != territory_clean`, taking care that missing values need explicit handling because comparisons involving `NA` can themselves return `NA`. Check cleaned frequencies, confirm the expected record count, and retain the outlier flag and review outcome separately from the amount. Use assertions or deliberate checks after each step rather than relying on the absence of an error message.
Count missing values by field and by complete row-level pattern; check that the pattern frequencies sum to the number of records.
State the screening rule and quantile convention, then consult the source before deciding whether to retain, correct, cap, or exclude a value.
Normalize mechanical differences, use evidence for corrections, and group categories only when a documented meaning supports the new levels.
Keep raw fields, create cleaned fields and flags, document the reason and affected records, and verify final counts.
A frequent exam trap is to describe all data-quality work as “removing bad data.” The wrong operation might be to delete every row with any missing value, drop the 160 amount because it exceeds a fence, and collapse every low-count category into one arbitrary label. Those edits would remove records or alter meanings without establishing an error. The correction is to diagnose first, distinguish missingness from invalidity, investigate flagged observations, and make only evidence-supported edits. If an edit cannot be justified, retain the original value and document the unresolved issue.
This workflow is aligned with the SOA Exam PA (Predictive Analytics) syllabus. Its central discipline is to make every decision interpretable: a missing value stays distinct from an observed category, an outlier flag stays distinct from a deletion, and a factor recode has a stated rule that another analyst can reproduce.
Check Your Understanding
For each hypothetical case, identify the appropriate calculation or cleaning decision. The answers are included for checking.
- A file has 80 records. Sixty have all fields observed, 6 are missing amount only, 4 are missing both amount and territory, and 10 are missing territory only. Find the number of incomplete records, the number of missing amount values, and the complete-record proportion. (Answer: 20 incomplete records; 10 missing amount values; \(60/80=0.75\), or 75% complete.)
- Seven observed amounts, in thousands of dollars, are \(4,5,6,8,9,10,45\). Using R’s default type 7 quartiles, calculate the upper Tukey fence and identify any flagged amount. (Answer: \(Q_1=5.5\), \(Q_3=9.5\), \(\mathrm{IQR}=4\), upper fence \(=15.5\); 45 is flagged.)
- A verified raw territory field contains `East`, ` east `, `EAST`, `Eats`, `Coastal`, and `Inland`. The source dictionary confirms `Eats` is a typo for East but gives no rule relating Coastal and Inland to each other. State the cleaned counts and whether Coastal and Inland should be combined. (Answer: East \(=4\), Coastal \(=1\), Inland \(=1\); correct the verified typo and normalize case/space, but keep Coastal and Inland separate without a supported grouping.)
- A documented channel definition has web as self-service and agent, call-center, and mail as assisted service. The observed counts are web \(=8\), agent \(=2\), call-center \(=1\), mail \(=1\), with 2 missing. Give defensible recoded counts and explain how missingness is represented. (Answer: self-service \(=8\), assisted \(=4\), missing \(=2\); preserve the missing values rather than classifying them as either service type.)
- A numeric field has 50 records, with 47 observed values and 3 missing values. An IQR rule flags one observed value, and source review confirms it is valid. State the number of records to retain under that review and how to report the missing and flagged counts. (Answer: retain all 50 records; report 3 missing values and 1 valid observed value flagged and reviewed, with the flag separate from the value.)