Good practices for manual modifications of data

Question

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.)

Answer 1

1. Do not modify the original data

Having the original data source intact is important. You may find that updates you make to the data are not valid. You may also find a more efficient way to make updates and you will want to regression test those updates.

Always work with a copy of the data, and add columns/properties/metadata that includes any processed corrections.

Example, if your data is a .csv file that includes a city name that contains several misspellings:

1. Copy the .csv to a new file
2. Add a column for containing "adjusted_city_name"
3. Copy data from the city_name column to the 
   adjusted_city_name column and make corrections in that column.

2. Document proposed changes

Any changes you want to make to data should be documented so that they can be replicated moving forward.

Version control and timestamp the document every time you change it. That will help in troubleshooting at a later date.

Be explicit. Do not simply state "correct capitalization problems", state "ensure that the first letter of each city name begins with a capital letter and the remaining letters are lower-case."

Update the document with references to any automation routines that have been built to manage data cleansing.

3. Decide on a standard data cleansing technology

Whether you use perl, python, java, a particular utility, a manual process or something else is not the issue. The issue is that in the future you want to hand the data cleansing process to somebody else. If they have to know 12 different data cleansing technologies, delegating the cleansing procedure will be very difficult.

4. Standardize the workflow

There should be a standard way to handle new data. Ideally, it will be as simple as dropping a file in a specific location and a predictable automated process cleanses it and hands off a cleansed set of data to the next processing step.

5. Make as few changes as is absolutely necessary

It's always better to have a fault tolerant analysis than one that makes assumptions about the data.

6. Avoid manual updates

It's always tempting, but people are error-prone and again it makes delegation difficult.

Notes on manual processing

To more completely address the original question as to whether there's a "good" way to do manual processing, I would say no, there is not. My answer is based on experience and is not one that I make arbitrarily.

I have had more than one project lose days of time due to a client insisting that a manual data cleansing process was just fine and could be handled internally. You do not want your projects to be dependent on a single individual accomplishing a judgement based task of varying scale.

It's much better to have that individual build and document a rule set based on what they would do than to have them manually cleanse data. (And then automating that rule set)

If automation fails you in the end or is simply not possible, the ability to delegate that rule set to others without domain specific knowledge is vital.

In the end, routines to do something like correct city names can be applied to other data sets.

Answer 2

Even if you are effectively modifying certain records by hand, as in the city name example you give, I would recommend doing it in code. The reason to strongly prefer code over hand-tweaking records is that the code makes a result reproducible. You want to make sure that you can always go from raw data to final result without any human intervention.

Here's a quick example. Let's say I have a list of city names in a pandas dataframe and I am certain they should all be "omaha" (you need to be absolutely certain, because changing values by hand is fraught with danger). But instead I have the following strings:

pd.unique(data.city)
array(['omaha', 'omahd', 'imaha', 'omaka'], dtype=object)

You could make the change like this:

data.city.values[data.city.values == 'omahd'] = 'omaha'
data.city.values[data.city.values == 'imaha'] = 'omaha'
data.city.values[data.city.values == 'omaka'] = 'omaha'

That code is ugly, but if you run it on the same raw dataset, you will always get the same result.

Answer 3

In addition to excellent previous answers, I'd like to recommend two papers on data cleaning. They are not specific to manual data cleaning, but, considering the benefits and advice (which I completely agree with) of expressing even manual data transformations in code, these resources can be as valuable. Also, despite the fact that following papers are somewhat R-focused, I believe that general ideas and workflows for data cleaning can be easily extracted and are equally applicable to non-R environments, as well.

The first paper presents the concept of tidy data, as well as examples and best practices of use of standard and specific R packages in data cleaning: http://vita.had.co.nz/papers/tidy-data.pdf.

A comprehensive and coherent approach to data cleaning in R, including examples, as well as a description of workflow (framework) of data cleaning in R, is presented in the following paper, which I highly recommend: http://cran.r-project.org/doc/contrib/de_Jonge+van_der_Loo-Introduction_to_data_cleaning_with_R.pdf.

Answer 4

As many have answered, it is always best to avoid anything done manually as it is less reproducible/documentable. Your point about the overhead of writing a script vs. opening and fixing the file is valid, though.

Best practice is often to

• keep an untouched version of the data
• build a working copy of the data with errors fixed
• have a way to re-create a working copy from the original data

The last point can be done with a script. Then make sure to be as specific as needed to modify only the data you want to modify, and to write the script in such a way that adding a fix by modifying the script is as easy as modifying the data directly.

If your data lie in files, you can also use diffs/patches to store the original data along with the patches needed to produce the working data. To generate them, duplicate your working copy, perform the change, extract the diff/patch, save it, and delete the previous working copy.

Answer 5

The #1 most important thing is to explicitly document your process.

Different people working on different data make different choices. Any scientists who claim that their work is entirely objective and rational are in denial about the nature of science; every single model we create is informed by our biases and perspectives. The key is that we each have different biases and perspectives, which is one reason that science is the pursuit of a society, not of individuals, and underscores the importance of peer review.

You have already identified some trade-offs between an algorithmic solution and individual/case-by-case corrections. If we have training as scientists, we may be used to thinking that everything has to be applied as an overall rule, so we may bend over backwards to find correlations and fit curves to data that just won't reasonably accept it. Applying an algorithm to the entire data set can be powerful when it works, but it's not the only tool at your disposal. Sometimes you need to use your judgment as the scientist.

But, if you use your individual judgment to make exceptions and to manually correct your data, be prepared to defend your judgment and argue your case. Be prepared to show that you considered all the data. If you're going to manually correct 8 observations out of a set of 100,000, you need to do more than justify those 8 manual corrections - you also need to justify not correcting the other 99,992 observations.

You can still take a methodical approach, perhaps by classifying your data in a systematic way, and choosing which subset of the data to apply your cleaning algorithm to based on that system of classification. And when you do this, you document it, you make your case, and you respect the judgment of your colleagues in the field.

On the other hand, why do all this extra work before you know it's necessary? Plenty of "dirty" data sets will still produce useful results. Perhaps 0.5% of your data is "dirty" in a way that you know will have a bad influence on your end product. Well, any analysis is subject to error, and the more you obsess over that 0.5% the more you will start to think of it and treat it like it was 5%, or 25% - much more significant than it truly is. Try to apply your analysis while admitting you know there is some error, and only do extra work to clean the data once you can show for certain that your analysis fails or is not useful otherwise.

For example...

Say you have a set of test results from a survey of wells, showing the concentrations of certain dissolved substances, hourly over the course of a year. And you observe in this set certain spikes, of short duration, and orders of magnitude higher than the surrounding data. If they are few, and obvious, and you know that the sensors used to produce the data set occasionally malfunction, then there's no reason to apply an algorithmic solution to the entire data set. You have a choice between excluding some data or modifying it.

I recommend the exclusion route whenever possible, since you are making fewer assumptions when you don't have to additionally choose a "correct" value. But if your analysis will absolutely fail with a discontinuity, there are many options. You could choose to replace "bad" values via linear interpolation. You could hold constant the previous value. If there is some great previous literature to apply, and you really have a strong case that the "bad" data is purely due to equipment malfunction, perhaps there's an established model that you can apply to fill in those regions.

There are a great many approaches, which is why the most important thing is to document your process, explicitly and exhaustively. Arguing your case is important too, but not strictly necessary; the community is just more likely to ignore you if you don't make a full effort to engage the review process.

Answer 6

I am new to this forum. Data cleansing of address data is an area I work in. I agree with the other posters that you should not modify the original data, but add fields for corrected values. I developed a technique in our systems (opengeocode.org) we call 'reduced to common form'. In this method, addresses and geographic names are analyzed for reduction into an unambiguous short form, which is then used for record matching (vs. the original values). For example, the method I use for matching US postal addresses is based on the US Post Office's published method for matching addresses.

For geographic names, the method will reduce to short gazetteer form in Romanized script.

The link below is an article I wrote a couple of years ago that explains how the street address reduction works:

http://www.nwstartups.com/api/doc/middleware.php#streetR

Answer 7

In the case you mention, I recommend to keep the changes as a dictionary, for instance in a .csv file. Write a script that replaces the values in the original data based on the translation in your dictionary. That way, you separate the corrections from the script itself.