Data and spreadsheets
Write data cleaning rules before editing a file
Write one rule per field before touching the file. State the problem you see, the change you propose, an example before and after, and the cases the rule should skip. Work on a copy and keep the original column, so every edit can be traced and undone.
A text to start with
Here is a sample of a file I want to clean: [anonymous sample rows]. The fields are [field names and meanings]. I see these problems: [issues]. Propose a cleaning rule for each field, with a before and after example and any exceptions. Where a value is unknown, say how to record it without inventing data. Produce the rules only. Do not change the data yet.
Example request. Change the details to fit your situation.Data cleaning rule sheet
File name and copy location: [ ] Original retained at: [ ] Field: [ ] Current issue: [ ] Proposed rule: [ ] Example before: [ ] Example after: [ ] Exception (rows the rule should skip): [ ] How an unknown value is recorded: [ ] Rows expected to change: [ ] Rows actually changed: [ ] Rules approved by: [ ] Repeat the item block as needed.
Rules that lose information
A rule can quietly destroy meaning. Trimming a leading zero may turn a valid identifier into a different one. Replacing a blank with zero turns an unknown into a measurement. Merging Other and Unknown erases a distinction someone may need. Lowercasing names can break values where capitalization matters. For each rule, ask what the original value told you that the cleaned value no longer does. Keep the original column beside the cleaned one.
Ask another agent to check the result
Read these cleaning rules and flag any that discard meaning or turn an unknown into a value, such as a blank becoming zero or two categories being merged. For each flagged rule, give an example row it would damage and suggest a safer rule.
Apply to a small copy
Apply the rules to a small copy of the file and compare the changed rows with the originals. Count the rows each rule touched and check the count against what you expected. Resolve exceptions by hand and record each decision. Only then apply the rules to the full copy, and keep the rule sheet with the file.