From Raw File to Trustworthy Report
Cleaning a data file is not simply making its entries look tidy. A defensible project must also preserve the original information, explain how each change was made, and protect the people whose data are represented. A reader should be able to understand how the cleaned dataset was produced and what the resulting summary does—and does not—say.
This tutorial follows a small, hypothetical shuttle survey from receipt of a spreadsheet to a brief report. The goal is not to prescribe one software package. A spreadsheet can work for a small project, while a script may be preferable when there are many records or when the same procedure must be rerun. In either case, the decisions and their order should be clear.
Plan Before Opening the Spreadsheet
Begin with the purpose and permissions for the data, not with a search for values to delete. Confirm that the data were collected and may be used for the intended purpose. Consent should make clear, in appropriate language, what will be collected, how it will be used, who may access it, and whether results will be shared. Consent does not replace any required institutional review, legal obligation, or data-protection requirement.
Collect only information needed for the stated purpose. Names, email addresses, exact addresses, and other direct identifiers generally do not belong in an analysis file unless they are genuinely necessary and appropriately protected. A study code can reduce exposure, but it is not a guarantee of anonymity: unusual combinations of details may still identify someone. Keep any code-to-identity key separate, restrict access to it, and do not include it in files intended for wider sharing.
Before cleaning, make a short plan: identify the source file, the intended cleaned output, the variables needed, and the rules for handling known issues. The earlier material on preserving raw data, missing values, duplicate records, category labels, valid values, units, and documented decisions provides the specific checks that a project may need. Here, the emphasis is on connecting those checks into one auditable workflow.
A Small Project, Step by Step
Suppose a hypothetical campus team conducts an opt-in survey about shuttle trip times. Respondents are told that coded responses will be used to describe the survey results. The team receives an export with a response code, travel mode, and reported trip time in minutes. The code is not a name, and the team stores the export in a restricted project folder.
Save the received export without editing it. Record its filename, receipt date, and a version or checksum if available. Keep it in a restricted location, separate from working files.
Write down the intended variables, units, accepted category labels, and procedures for reviewing missing, repeated, or unusual values. Do not invent a correction merely because a value looks inconvenient.
Use an ordered set of spreadsheet steps or a script. Keep a log that connects each operation to its reason, such as standardizing an agreed label or retaining a value after checking it against the source.
Confirm that the cleaned file has the expected fields and that transformations worked as intended. Describe the usable data, important decisions, and limitations without implying more than the data support.
A useful way to express the logic is to treat cleaning as a defined transformation. Let \(R\) denote the preserved raw file, \(C\) the documented cleaning rules, and \(f\) the ordered operations. Then the cleaned output is \(D=f(R,C)\). If the source, rules, and operations are unchanged, a deterministic workflow should produce the same \(D\). If the result differs, the difference signals that the input, rules, software behavior, or execution needs investigation; it should not be silently accepted as a new version.
Worked Example: Cleaning a Shuttle Survey Export
The following rows are hypothetical. The team has verified that these coded records are within the consented survey export. A repeated row appears because an export operation duplicated a response.
| Response code | Raw mode | Trip time (minutes) |
|---|---|---|
| R01 | walk | 12 |
| R02 | Bus | 15 |
| R03 | bus | blank |
| R04 | BUs | 18 |
| R05 | shuttle | 180 |
| R06 | walk | 14 |
| R06 | walk | 14 |
First, record the rules. The project’s category convention uses “walk,” “bus,” and “shuttle.” A verified exact duplicate export row will be removed from the analysis copy, while the raw file remains unchanged. A blank time will remain missing. A time of 180 minutes will be checked against the source before deciding whether it is an error.
Next, apply the rules. The two R06 rows match on the response code and recorded values, and the team confirms that the source contains one R06 response. It retains one row in the cleaned file and records the duplicate-export finding in the log. It standardizes “Bus,” “bus,” and “BUs” to “bus,” without altering any trip-time value.
Then, review rather than guess. The team checks R05 against the source response and confirms that 180 minutes was entered as reported. It therefore retains 180 and notes that it is an unusual value requiring context, not an invalid value merely because it is much larger than the others. R03 remains in the dataset with a missing time rather than being assigned zero.
The resulting file has six unique responses, with five recorded times and one missing time. For a descriptive summary of the recorded times, their sum is \(12+15+18+180+14=239\) minutes, so the mean is \(239/5=47.8\) minutes. The sorted recorded times are 12, 14, 15, 18, and 180 minutes, giving a median of 15 minutes. Reporting both helps show how strongly the unusual but verified value affects the mean. Neither summary describes a missing time as zero.
Make Spreadsheet Operations Checkable
A spreadsheet can preserve a reproducible workflow if its structure makes the operations visible. Keep the source sheet unchanged, use a separate cleaned sheet or output file, and label fields with clear names and units. Avoid overwriting raw cells with corrected values: otherwise, a later reviewer may not be able to distinguish an original entry from a cleaning decision.
For spreadsheet formulas, inspect the cell range and the treatment of blanks rather than trusting a plausible-looking result. A formula may omit blank cells, include an unintended row, or treat a text entry differently from a numeric value. Record the formula or operation, and check a small result by hand when feasible. The earlier material on auditing spreadsheet formulas and comparing manual steps with recorded workflows applies directly here.
Worked Example: Auditing a Spreadsheet Mean
In a separate hypothetical project, a spreadsheet contains four trip-time cells: 24, 30, blank, and 36 minutes. The project rule is to summarize recorded times and leave a genuinely missing entry missing. The formula in the summary cell is `=AVERAGE(C2:C5)`.
The specified range contains three numeric values and one blank. Under standard spreadsheet behavior, AVERAGE omits the blank, so the result should be checked against the hand calculation:
The output is a mean of 30 minutes among the three recorded times, not a mean based on all four rows. If someone replaces the blank with zero, the calculation becomes \((24+30+0+36)/4=22.5\) minutes, which answers a different and unjustified question. The analyst should therefore keep the missing value distinct from zero, state how many measurements contribute to the summary, and verify that the formula range includes exactly the intended cells.
Checks and Reporting Are Part of the Workflow
Before releasing a cleaned file, check that its columns have the expected names and units, that the recorded changes match the decision log, and that values needing review have not disappeared without explanation. Compare key fields between the source and output where practical. A check is not proof that every entry is correct; it is a way to catch inconsistencies between the stated rules and the produced file.
A concise report should identify the data source and purpose, describe the relevant cleaning decisions, make clear which observations contribute to each summary, and state important limitations. It should distinguish a verified correction from an assumption and avoid claiming that a small opt-in survey represents a broader population. The earlier material on reporting results without misleading readers is useful here: the report must give enough context for its numerical summary to be interpreted fairly.
Privacy remains important at the reporting stage. Removing names from a shared table does not necessarily prevent identification, particularly when the sample is small or a combination of attributes is unusual. Share only what is needed, restrict access to row-level data, and consider whether detailed examples or quotations could reveal a respondent. A report can be transparent about methods without exposing individual records.
Worked Example: Writing a Privacy-Conscious Summary
A hypothetical student group has consent to summarize coded trip-time responses from its opt-in survey. After applying its documented rules, six times are available: 8, 10, 11, 12, 13, and 15 minutes. The group also has contextual notes that could make one respondent recognizable if quoted. It will share a short report with classmates, not the row-level file.
Calculate the summary. The recorded times sum to \(8+10+11+12+13+15=69\) minutes. The mean is \(69/6=11.5\) minutes. The report must say that this is the mean among six recorded responses; it should not imply that it is the mean for all students or all shuttle riders.
Write the result with its scope. A suitable sentence is: “Among the six coded responses with recorded trip times in this opt-in survey, the mean reported trip time was 11.5 minutes. The result describes these responses and may not represent other students or shuttle riders.” If a missing response exists, the report should also identify that it was not included in this calculation.
Protect respondents. The group can describe its method and give the summary without publishing names, response codes, or the recognizable contextual note. Because the audience and sample are limited, it should not release a row-level spreadsheet just to demonstrate transparency. It can instead provide the cleaning rules and a suitably de-identified summary, while keeping access to the underlying data restricted according to the consent and project rules.
Common Mistakes
- Editing the only copy. “I can remember what I changed” is not an auditable record. Preserve the source and make changes in a separate working copy or generated output.
- Deleting every unusual value. An unusual value may be real. Check it against an appropriate source and document the decision; do not remove it solely because it changes a summary.
- Turning missing into zero. Zero is a value, while missing means no value was recorded. Replacing one with the other changes the data and may change the result.
- Calling coded data anonymous. A code removes a direct identifier but may not prevent identification from other details. Limit access and assess disclosure risk before sharing.
- Reporting only a polished number. A summary without its population, usable-data basis, and key limitations may suggest a broader or more certain conclusion than the project supports.
Check Your Understanding
Try these questions without looking back.
- Why should a cleaned dataset be saved separately from an unchanged raw file?
- A recorded value is much larger than the others but is confirmed in the source. What should the analyst do, and why is automatic deletion inappropriate?
- A spreadsheet formula calculates an average from three numbers and one blank cell. What does the result describe, and why should the blank not automatically be replaced by zero?
- What information should a cleaning log provide so another authorized analyst can understand how the output was produced?
- Why does replacing names with response codes not necessarily make a dataset safe to share?
- What limitation should be stated when a summary comes from a small opt-in survey?