Why Document Cleaning Decisions?
Cleaning data involves choices: whether two labels mean the same category, how to represent a missing value, or what to do with a record that conflicts with an expected range. If those choices are not recorded, someone reviewing the data may not be able to tell what changed, why it changed, or whether the same rule was applied consistently. A cleaning log makes those decisions explicit.
A log is not a substitute for preserving the raw data. The raw data remain the reference for what was originally recorded; the log describes the changes made to a working copy. This distinction matters because a plausible-looking correction can still be wrong. The record should show both the action taken and the evidence or reasoning that supports it.
What Belongs in a Cleaning Log?
A useful entry is specific enough that another person can understand the decision without guessing. The following fields provide a practical starting point.
- Entry or decision identifier: A short label that distinguishes this decision from others in the log.
- Date or stage: When the decision was recorded or which stage of the cleaning work it belongs to.
- Variable and issue: The column involved and a concise description of the observed problem.
- Rule applied: The exact condition that determines what is changed and how. Avoid vague wording such as “fix inconsistent values.”
- Affected records: The record identifiers, or a clearly defined group of records, and the number affected. Use identifiers that do not disclose unnecessary personal information.
- Action and result: Whether values were changed, flagged, set to missing, or left as they were, with the resulting value where relevant.
- Reason: The evidence or definition supporting the decision, such as a data dictionary or a recorded unit convention.
- Reversibility information: Whether the original value was retained and how the cleaned value can be related back to it.
These fields distinguish observation from judgment. “Three values are outside the allowed range” describes an issue. “Replace them with the nearest allowed value” is a rule, but it is not automatically a defensible one. The reason should explain why that rule fits the data and the intended meaning of the variable. If there is no evidence for a correction, the appropriate decision may be to flag the values and seek clarification rather than alter them.
| Log field | Question it answers |
|---|---|
| Issue | What was noticed? |
| Rule | What condition determined the action? |
| Affected records | Which observations were involved, and how many? |
| Action and result | What was done to those observations? |
| Reason | Why does the action preserve or clarify the intended meaning? |
| Original-value information | Can the recorded value be recovered or checked? |
Worked Examples: Recording Decisions Clearly
Worked Example: Standardizing Category Labels
Suppose a hypothetical survey has 120 records and a column called visit_status. A review finds 46 values recorded as “Complete,” 18 as “complete,” and 6 as “COMPLETE.” The remaining 50 records have other values. The data dictionary defines “Complete” as the category for a finished visit. How should this decision be logged?
Issue. The same defined category appears with three capitalization patterns. The observed counts are \(46+18+6=70\) records.
Rule. For this variable, map the exact labels “complete” and “COMPLETE” to “Complete.” Leave all other labels unchanged. Writing the actual labels in the rule makes its scope clearer than saying “standardize capitalization.”
Affected records and action. Record the identifiers of the 24 affected records, or an adequately protected reference to those records, and state that their values were changed to “Complete.” The count is \(18+6=24\). Keep the 46 values that were already “Complete” as recorded.
Reason. The data dictionary defines one category, and these three labels differ only in capitalization. The rule makes the recorded values consistent with that definition without combining categories based on an unsupported guess.
The log should also say whether the original labels were retained in a separate field or preserved in the raw data. Without the originals, a reviewer can see the final category but cannot directly check which spelling each affected record had.
Worked Example: Converting Units Reversibly
In a hypothetical file, temperature is recorded in a column named temp_f, and the data dictionary states that the values are degrees Fahrenheit. An analysis copy needs temperatures in degrees Celsius. Three example records have original values of 68°F, 77°F, and 50°F. Show the calculation and the log entry.
Use the conversion rule \(C=(F-32)\times 5/9\), where \(F\) is the value in degrees Fahrenheit and \(C\) is the converted value in degrees Celsius.
Issue. The original temperature column uses Fahrenheit, while the analysis requires Celsius. This is a change of units, not a correction of an erroneous measurement.
Rule and action. Apply the stated formula to every non-missing value in temp_f, and store the result in a separate column named temp_c. For the three example records, the new values are 20°C, 25°C, and 10°C. The rule applies to all records with a recorded Fahrenheit value; the log should report the actual number affected after the conversion.
Reason and reversibility. The data dictionary establishes the original unit, and the conversion formula defines the new unit. Keeping temp_f unchanged means the original values remain available. The reverse conversion is \(F=C\times 9/5+32\), so the original measurements can also be recovered from unrounded Celsius values. If the new values are rounded, exact recovery may not be possible from the rounded values alone; retaining the original column avoids that problem.
Worked Example: Documenting an Uncertain Date
A hypothetical appointment file records dates in the format day-month-year. One record contains “04-05-2025.” A staff member suggests it might mean April 5, while another reading of the recorded format means May 4. What should the log say?
Issue. The value could be misread if the date format is not kept with the data. However, under the stated day-month-year format, the recorded value has a defined interpretation: 4 May 2025.
Rule and action. If the data dictionary or source documentation confirms day-month-year, record a rule to interpret the value as 4 May 2025 and, if needed, convert it to the chosen storage format. Identify the affected record and preserve the original text “04-05-2025.”
Reason. The format convention, not a guess about the appointment, supports the interpretation. If no reliable format convention can be found, the log should instead state that the date is ambiguous, identify the record, and leave it unchanged or flag it for clarification. It would be unjustified to choose one date merely because it seems more likely.
This example shows that documenting a decision can mean recording a decision not to alter a value. “No change” is informative when the reason is that the available evidence does not support a correction.
Why Retaining Originals Makes Changes Reversible
A change is reversible when the original value can be recovered from the cleaned data and the recorded rule. Keeping a separate original value is often the simplest way to achieve this. A rule may also be reversible on its own, but not every cleaning operation is.
For example, changing “complete” to “Complete” is reversible for an individual value only if its original capitalization was retained or can be recovered elsewhere. Once the original spelling is overwritten, several distinct spellings have collapsed to one value. The log may report that a change occurred, but it cannot reveal which of the original spellings belonged to a particular record unless that information was preserved.
A unit conversion illustrates the same idea. Converting an unrounded value between Fahrenheit and Celsius uses a formula with a unique inverse. Rounding, however, can map multiple original values to the same reported value. If an original 20.1°C and an original 20.4°C are both rounded to 20°C, the rounded result alone does not show which value came first. Keeping the original measurement prevents that information loss.
Deletion and replacement require particular care. If a record is removed from a working data set, the log should identify the record and explain the rule and reason for removal; the raw data should remain available. If an uncertain value is replaced with a guessed value, recording the guess does not make the replacement scientifically justified. Where information is insufficient, flagging the value or recording it as missing may be more faithful than inventing a correction.
A Practical Way to Write an Entry
Draft each entry by moving from what was observed to what was decided. The following sequence helps prevent the rule, action, and reason from being blurred together.
Name the variable and state what was observed, using counts or examples where useful.
Define the condition for applying the action and specify what happens to values that do not meet it.
Give record identifiers or a clear selection criterion, and report the number affected. Avoid including sensitive details that are not necessary.
State what changed, what did not change, and why the action follows from the data definition or other available evidence.
Say where the original value remains available and note any loss of precision or information.
The rule should be concrete enough to distinguish eligible records from ineligible ones. “Convert all recorded Fahrenheit values using the specified formula” is more precise than “fix the temperature column.” Similarly, “map ‘complete’ and ‘COMPLETE’ to ‘Complete’ in visit_status” states a bounded rule; “make labels consistent” leaves open which values should be combined.
A reason should connect the action to evidence, not merely restate it. “Changed the values because they were inconsistent” does not explain why the values should be treated as equivalent. “The data dictionary defines these capitalization variants as one category” supplies the missing justification. If the evidence is uncertain, record that uncertainty rather than presenting a tentative choice as established fact.
Common Mistakes
- Writing only “cleaned the data.” This does not identify the issue, rule, affected records, or result. Name each decision separately.
- Using a rule that cannot be applied consistently. “Fix unusual values” gives no condition. State the specific values or criteria covered and what happens to other values.
- Reporting a count without identifying the affected records. “Twenty-four rows changed” may be difficult to check if the rows cannot be located. Provide record identifiers or a precise way to select them.
- Treating a guess as a correction. A plausible value is not evidence. Preserve the recorded value and flag uncertainty when the intended value cannot be established.
- Overwriting the original without noting the loss. A log describing a change cannot recreate information that was discarded. Retain the original or clearly state when exact recovery is no longer possible.
- Recording personal details unnecessarily. The log should identify affected records in a way that supports review while respecting privacy and limiting sensitive information.
A well-kept cleaning log helps readers distinguish recorded data from decisions made during preparation. This is important because later summaries depend on those decisions: a category merge can change category counts, and a unit conversion changes the scale in which a measurement is expressed. Clear documentation lets someone interpret the prepared data without mistaking a cleaning choice for an original observation.
Check Your Understanding
Answer these questions using the principles in this tutorial.
- What four core elements should a cleaning log record for each decision?
- A rule says “standardize the labels.” What details would make that rule precise enough to apply consistently?
- Why is it useful to retain a temperature column in its original unit when creating a converted column?
- A date could have two interpretations and no documentation explains the format. What should be recorded, and why is choosing the more plausible date risky?
- Why is a count of affected records not always enough for someone to check a cleaning decision?