More often than not, data I am working with is not 100% clean. Even if it is reasonably clean, still there are portions that need to be fixed.
When a fraction of data needs it, I write a script and incorporate it in data processing.
But what to do if only a few entries needs to be fixed (e.g. misspelled city or zip code)? Let's focus on "small data", such as that in CSV files or a relational database.
The practical problems I encountered:
- Writing a general script trying solve all similar errors may give unintended consequences (e.g. matching cities that are different but happen to have similar names).
- Copying and modifying data may make a mess, as:
- Generating it again will destroy all fixes.
- When there are more errors of different kinds, too many copies of the same file result, and it is hard to keep track of them all.
- Writing a script to modify particular entries seems the best, but there is overhead in comparison to opening CSV and fixing it (but still, seems to be the best); and either we need to create more copies of data (as in the previous point) or run the script every time we load data.
What are the best practices in such a case as this?
EDIT: The question is on the workflow, not whether to use it or not.
(In my particular case I don't want the end-user to see misspelled cities and, even worse, see two points of data, for the same city but with different spelling; the data is small, ~500 different cities, so manual corrections do make sense.)