You probably won't spend as much time to get the data as you will in trying to get it into shape. Raw data is often inconsistent, duplicated, or full of holes. Addresses might be missing, years and dates might be formatted in a thousand different ways, or names might be entered into the wrong fields. You'll have to fix these issues before the data is usable.

This is often an iterative, interactive process. If it's a very large dataset, I might create a sample to work with at this stage. Generally, I start by examining the data files. Once I find a problem, I try to code a solution, which I run on the dataset. After each change, I archive the data, either using a ZIP file or, if the data files are small enough, a version control system. Using a version control system is a good option because I can track the code to transform the data along with the data itself and I can also include comments about what I'm doing. Then, I take a look at the data again, and the entire process starts...

Often, cleaning data involves text transformations. Some, such as adding or removing a set and static strings, are pretty simple. Others, such as parsing a complex data format such as JSON or XML, requires a complete parser. However, many fall within a middle range of complexity. These need more processing power than simple string manipulation, but full-fledged parsing is too much. For these tasks, regular expressions are often useful.

Probably, the most basic and pervasive tool to clean data of any kind is a regular expression. Although they're overused sometimes, regular expressions truly are the best tool for the job many times. Moreover, Clojure has a built-in syntax for compiled regular expressions, so they are convenient too.

In this example, we'll write a function that normalizes U.S. phone numbers.

(defproject cleaning-data "0.1.0-SNAPSHOT...

One common problem with data is inconsistency. Sometimes, a value is capitalized, while sometimes it is not. Sometimes it is abbreviated, and sometimes it is full. At times, there is a misspelling.

When it's an open domain, such as words in a free-text field, the problem can be quite difficult. However, when the data represents a limited vocabulary (such as US state names, for our example here) there's a simple trick that can help. While it's common to use full state names, standard postal codes are also often used. A mapping from common forms or mistakes to a normalized form is an easy way to fix variants in a field.

(defproject cleaning-data "0.1.0-SNAPSHOT"
  :dependencies [[org.clojure/clojure "1.6.0"]])

(use '[clojure.string :only (upper-case)])

One problem when cleaning up data is dealing with duplicates. How do we find them? What do we do with them once we have them? While a part of this process can be automated, often merging duplicated data is a manual task, because a person has to look at potential matches and determine whether they are duplicates or not and determining what needs to be done with the overlapping data. We can code heuristics, of course, but at some point, a person needs to make the final call.

The first question that needs to be answered is what constitutes identity for the data. If you have two items of data, which fields do you have to look at in order to determine whether they are duplicates? Then, you must determine how close they need to be.

For this recipe, we'll examine some data and decide on duplicates by doing a fuzzy comparison of the name fields. We'll simply return all of the pairs that appear to be duplicates.

If we need to read in numbers as strings, we have to worry about how they're formatted. However, we'll probably want the computer to deal with them as numbers, not as strings, and this can't happen if the string contains a comma or period to separate the thousands place. This allows the numbers to be sorted and to be available for mathematical functions.

In this recipe, we'll write a short function that takes a number string and returns the number. The function will strip out all of the extra punctuation inside the number and only leave the last separator. Hopefully, this will be the one that marks the decimal place.

Of course, the version of this function, which we'll see here, only works in locales that use commas to separate thousands and periods to separate decimals. However, it would be relatively easy to write versions that will work in any particular locale.

(defproject cleaning-data "0...

One way to normalize values is to scale frequencies by the sizes of their groups. For example, say the word truth appears three times in a document. This means one thing if the document has thirty words. It means something else if the document has 300 or 3,000 words. Moreover, if the dataset has documents of all these lengths, how do you compare the frequencies for words across documents?

One way to do this is to rescale the frequency counts. In some cases, we can just scale the terms by the length of the documents. Or, if we want better results, we might use something more complicated such as term frequency-inverse document frequency (TF-IDF).

For this recipe, we'll rescale some term frequencies by the total word count for their document.

(defproject cleaning-data "0.1.0-SNAPSHOT"
  :dependencies [[org.clojure/clojure "1.6.0"]])

One difficult issue when normalizing and cleaning up data is how to deal with time. People enter dates and times in a bewildering variety of formats; some of them are ambiguous, and some of them are vague. However, we have to do our best to interpret them and normalize them into a standard format.

In this recipe, we'll define a function that attempts to parse a date into a standard string format. We'll use the clj-time Clojure library, which is a wrapper around the Joda Java library (http://joda-time.sourceforge.net/).

(defproject cleaning-data "0.1.0-SNAPSHOT"
  :dependencies [[org.clojure/clojure "1.6.0"]
                 [clj-time "0.9.0-beta1"]])

(use '[clj-time.core :exclude (extend second)]
     '[clj-time.format])