# Cleaning data of inconsistent format in R?

I often deal with messy survey data which requires a lot of cleaning up before any statistics can be done. I used to do this "manually" in Excel, sometimes using Excel formulas, and sometimes checking entries one-by-one. I started doing more and more of these tasks by writing scripts to do them in R, which has been very beneficial (benefits include having a record of what was done, less chance of mistakes, and being able to reuse code if the data set is updated).

But there are still some types of data that I have trouble handling efficiently. For example:

> d <- data.frame(subject = c(1,2,3,4,5,6,7,8,9,10,11),
+   hours.per.day = c("1", "2 hours", "2 hr", "2hr", "3 hrs", "1-2", "15 min", "30 mins", "a few hours", "1 hr 30 min", "1 hr/week"))
> d
subject hours.per.day
1        1             1
2        2       2 hours
3        3          2 hr
4        4           2hr
5        5         3 hrs
6        6           1-2
7        7        15 min
8        8       30 mins
9        9   a few hours
10      10   1 hr 30 min
11      11     1 hr/week


hours.per.day is meant to be the average number of hours per day spent on a certain activity, but what we have is exactly what the subject wrote. Suppose I make some decisions on what to do with ambiguous responses, and I want the tidied variable hours.per.day2 as follows.

   subject hours.per.day hours.per.day2
1        1             1      1.0000000
2        2       2 hours      2.0000000
3        3          2 hr      2.0000000
4        4           2hr      2.0000000
5        5         3 hrs      3.0000000
6        6           1-2      1.5000000
7        7        15 min      0.2500000
8        8       30 mins      0.5000000
9        9   a few hours      3.0000000
10      10   1 hr 30 min      1.5000000
11      11     1 hr/week      0.1428571


Assuming that the number of cases is quite large (say 1000) and knowing that the subjects were free to write anything they liked, what is the best way to approach this?

I would use gsub() to identify the strings that I know and then perhaps do the rest by hand.

test <- c("15min", "15 min", "Maybe a few hours",
"4hr", "4hour", "3.5hr", "3-10", "3-10")
new_var <- rep(NA, length(test))

my_sub <- function(regex, new_var, test){
t2 <- gsub(regex, "\\1", test)
identified_vars <- which(test != t2)
new_var[identified_vars] <- as.double(t2[identified_vars])
return(new_var)
}

new_var <- my_sub("([0-9]+)[ ]*min", new_var, test)
new_var <- my_sub("([0-9]+)[ ]*(hour|hr)[s]{0,1}", new_var, test)


To get work with the ones that you need to change by hand I suggest something like this:

# Which have we not found
by.hand <- which(is.na(new_var))

unique(test[by.hand])
# Create a list with the ones
my_interpretation <- list("3-10"= 5, "Maybe a few hours"=3)
for(key_string in names(my_interpretation)){
new_var[test == key_string] <- unlist(my_interpretation[key_string])
}


This gives:

> new_var
[1] 15.0 15.0  3.0  4.0  4.0  3.5  5.0  5.0


Regex can be a little tricky, every time I'm doing anything with regex I run a few simple tests. Se ?regex for the manual. Here's some basic behavior:

> # Test some regex
> grep("[0-9]", "12")
[1] 1
> grep("[0-9]", "12a")
[1] 1
> grep("[0-9]$", "12a") integer(0) > grep("^[0-9]$", "12a")
integer(0)
> grep("^[0-9][0-9]", "12a")
[1] 1
> grep("^[0-9]{1,2}", "12a")
[1] 1
> grep("^[0-9]*", "a")
[1] 1
> grep("^[0-9]+", "a")
integer(0)
> grep("^[0-9]+", "12222a")
[1] 1
> grep("^(yes|no)$", "yes") [1] 1 > grep("^(yes|no)$", "no")
[1] 1
> grep("^(yes|no)$", "(yes|no)") integer(0) > # Test some gsub, the \\1 matches default or the found text within the () > gsub("^(yes|maybe) and no$", "\\1", "yes and no")
[1] "yes"

• Thanks for the answer Max. I'm not familiar with regular expressions so will have to learn about them. Would you mind giving a brief description of how you would go about doing the rest by hand? Is there a better way than just doing something like new_var[by.hand] <- c(2, 1, ...) with by.hand being TRUE for the cases which are done by hand? – mark999 May 6 '12 at 4:42
• @mark999: Added some examples and a suggestion of how you can do the ones by hand. – Max Gordon May 6 '12 at 6:54
• Regular expressions are super-important for any kind of data manipulation: cleaning up data as the OP has, or for extracting data from files, HTML, etc. (For proper HTML, there are libraries, like XML to help you extract data, but this doesn't work when the HTML is malformed.) – Wayne May 6 '12 at 15:06

@Max's suggestion is a good one. It seems that if you write an algorithm that recognizes numbers as well as common time-associated words/abbreviations, you'll get most of the way there. This will not be beautiful code, but it will work and you can improve it over time as you come across problem cases.

But for a more robust (and initially time-consuming) approach, try Googling "parsing a natural language time string." Some interesting findings are This open time API, a good Python module, and one of many germane threads like this one on Stack Overflow.

Basically, natural language parsing is a common problem and you should look for solutions in languages other than R. You can build tools in another language that you can access using R, or at the very least you can get good ideas for your own algorithm.

For something like that, if it was sufficiently long, I think I'd want a list of regular expressions and transformation rules, and take the new values to another column (so you always have the chance to double check without reloading the raw data); the RE's would be applied in order to the not-so-far-transformed data until all the data was transformed or all the rules were exhausted. It's probably best to also keep a list of logical values that indicate which rows haven't yet been transformed.

A few such rules are obvious of course and will probably handle 80-90% of cases, but the issue is that there will always be some you don't know will come up (people are very inventive).

Then you need a script that goes through and presents the originals of the not-yet-transformed-by-the-list-of-obvious-rules values to you one at a time, giving you a chance to make a regular expression (say) to identify those cases and give a new transformation for the cases that fit it, which it adds to the original list and applies to the not-yet-transformed rows of the original vector before checking if there are any cases left to present to you.

It might also be reasonable to have the option to skip a case (so that you can go on to easier ones), so you can pus the very hard cases right to the end.

Worst case, you do a few by hand.

You can then keep the full list of rules you generate, to apply again when the data grows or a new, similar data set comes along.

I don't know if it's remotely approaching best practice (I'd think something much more formal would be needed there), but in terms of processing large amounts of such data quickly, it might have some value.

• Thanks for the answer, Glen. That sounds very appealing. Do you see it as a big advantage to have the not-yet-transformed values presented one at a time, as opposed to just displaying all of them and looking at that output? I've never done anything like have things presented one at a time. – mark999 May 6 '12 at 9:14
• @mark999, I would think there are both advantages and disadvantages of one-at-a-time presentation. The advantage is simplicity -- using cat() to display an ambiguous time, and scan() to record your interpretation of that time is easy to implement. The disadvantage is that you may miss the big picture of lots of entries that you could correct en masse with a single line of regex code. You might have a think about what you're hoping to get: if you just want to solve this problem, do it by hand. If you want to learn more about R, try to code a solution. – Ash May 6 '12 at 12:45
• Sorry for the lack of reply; I broadly agree with Ash's comment – Glen_b -Reinstate Monica May 26 '12 at 3:46

R contains some standard functions for data manipulation, which can be used for data cleaning, in its base package (gsub, transform, etc.), as well as in various third-party packages, such as stringr, reshape, reshape2, and plyr. Examples and best practices of usage for these packages and their functions are described in the following paper: http://vita.had.co.nz/papers/tidy-data.pdf.

Additionally, R offers some packages specifically focused on data cleaning and transformation:

A comprehensive and coherent approach to data cleaning in R, including examples and use of editrules and deducorrect packages, 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.