• We will be using multiple data sets in this lecture:
  • Salary, Monument, Circulator, and Restaurant from OpenBaltimore: https://data.baltimorecity.gov/browse?limitTo=datasets
  • Gap Minder - very interesting way of viewing longitudinal data
    • Data is here - http://www.gapminder.org/data/
  • http://spreadsheets.google.com/pub?key=rMsQHawTObBb6_U2ESjKXYw&output=xls

In general, data cleaning is a process of investigating your data for inaccuracies, or recoding it in a way that makes it more manageable.

MOST IMPORTANT RULE - LOOK AT YOUR DATA!

Again - table, summarize, is.na, any, all are useful.

prop.table(tab)

##       
##          0   1   2   3   4 <NA>
##   0    0.1 0.0 0.0 0.0 0.0  0.0
##   1    0.0 0.1 0.0 0.0 0.0  0.0
##   2    0.0 0.0 0.2 0.0 0.2  0.0
##   3    0.0 0.0 0.0 0.4 0.0  0.0
##   <NA> 0.0 0.0 0.0 0.0 0.0  0.0

prop.table(tab,1)

##       
##          0   1   2   3   4 <NA>
##   0    1.0 0.0 0.0 0.0 0.0  0.0
##   1    0.0 1.0 0.0 0.0 0.0  0.0
##   2    0.0 0.0 0.5 0.0 0.5  0.0
##   3    0.0 0.0 0.0 1.0 0.0  0.0
##   <NA>

https://data.baltimorecity.gov/City-Government/Baltimore-City-Employee-Salaries-FY2014/2j28-xzd7

Download as a CSV and then read it into R as the variable Sal

Sal = read.csv("../data/Baltimore_City_Employee_Salaries_FY2014.csv",
                as.is=TRUE)

For example, let's say gender was coded as Male, M, m, Female, F, f. Using Excel to find all of these would be a matter of filtering and changing all by hand or using if statements.

In R, you can simply do something like:

data$gender[data$gender %in% 
    c("Male", "M", "m")] <- "Male"

Sometimes though, it's not so simple. That's where functions that find patterns come in very useful.

table(gender)

## gender
##      F FeMAle FEMALE     Fm      M     Ma   mAle   Male   MaLe   MALE 
##     75     82     74     89     89     79     87     89     88     95 
##    Man  Woman 
##     73     80

• R can do much more than find exact matches for a whole string
• Like Perl and other languages, it can use regular expressions.
• What are regular expressions?
• Ways to search for specific strings
• Can be very complicated or simple
• Highly Useful

grep: grep, grepl, regexpr and gregexpr search for matches to argument pattern within each element of a character vector: they differ in the format of and amount of detail in the results.

grep(pattern, x, fixed=FALSE), where:

• pattern = character string containing a regular expression to be matched in the given character vector.

• x = a character vector where matches are sought, or an object which can be coerced by as.character to a character vector.

• If fixed=TRUE, it will do exact matching for the phrase anywhere in the vector (regular find)

grep("Rawlings",Sal$Name)

## [1] 13832 13833 13834 13835

These are the indices/elements where the pattern match occurs

grep() returns something similar to which() on a logical statement

head(grep("Tajhgh",Sal$Name, value=TRUE))

## [1] "Reynold,Tajhgh J"

grep("Jaffe",Sal$Name)

## [1] 8603

length(grep("Jaffe",Sal$Name))

## [1] 1

• http://www.regular-expressions.info/reference.html
• They can use to match a large number of strings in one statement
• . matches any single character
• * means repeat as many (even if 0) more times the last character
• ? makes the last thing optional

• Look for any name that starts with:
  • Payne at the beginning,
  • Leonard and then an S
  • Spence then a capital C

grep("Payne.*", x=Sal$Name, value=TRUE)

##  [1] "Payne El,Jackie"         "Payne Johnson,Nickole A"
##  [3] "Payne,Chanel"            "Payne,Connie T"         
##  [5] "Payne,Denise I"          "Payne,Dominic R"        
##  [7] "Payne,James R"           "Payne,Jasman T"         
##  [9] "Payne,Joey D"            "Payne,Jordan A"         
## [11] "Payne,Karen V"           "Payne,Karen V"          
## [13] "Payne,Leonard S"         "Payne,Mary A"           
## [15] "Payne,Micah W"           "Payne,Michael C"        
## [17] "Payne,Michael N"         "Payne,Morag"            
## [19] "Payne,Nora M"            "Payne,Shelley F"

grep("Leonard.?S", x=Sal$Name, value=TRUE)

## [1] "Payne,Leonard S"      "Szumlanski,Leonard S"

grep("Spence.*C.*", x=Sal$Name, value=TRUE)

## [1] "Greene,Spencer C"    "Spencer,Charles A"   "Spencer,Christian O"
## [4] "Spencer,Clarence W"  "Spencer,Michael C"

Let's say we wanted to sort the data set by Annual Salary:

class(Sal$AnnualSalary)

## [1] "character"

sort(c("1", "2", "10")) #  not sort correctly (order simply ranks the data)

## [1] "1"  "10" "2"

order(c("1", "2", "10"))

## [1] 1 3 2

So we must change the annual pay into a numeric:

head(as.numeric(Sal$AnnualSalary), 4)

## [1] NA NA NA NA

R didn't like the $ so it thought turned them all to NA.

sub() and gsub() can do the replacing part.

Useful String functions

• toupper(), tolower() - uppercase or lowercase your data:
• str_trim() (in the stringr package) - will trim whitespace
• nchar - get the number of characters in a string
• substr(x, start, stop) - substrings from position start to position stop
• strsplit(x, split) - splits strings up - returns list!
• paste() - paste strings together - look at ?paste

Paste can be very useful for joining vectors together:

paste("Visit", 1:5, sep="_")

## [1] "Visit_1" "Visit_2" "Visit_3" "Visit_4" "Visit_5"

paste("Visit", 1:5, sep="_", collapse=" ")

## [1] "Visit_1 Visit_2 Visit_3 Visit_4 Visit_5"

paste("To", "is going be the ", "we go to the store!", sep="day ")

## [1] "Today is going be the day we go to the store!"

# and paste0 can be even simpler see ?paste0 
paste0("Visit",1:5)

## [1] "Visit1" "Visit2" "Visit3" "Visit4" "Visit5"

paste(1:5, letters[1:5], sep="_")

## [1] "1_a" "2_b" "3_c" "4_d" "5_e"

paste(6:10, 11:15, 2000:2005, sep="/")

## [1] "6/11/2000"  "7/12/2001"  "8/13/2002"  "9/14/2003"  "10/15/2004"
## [6] "6/11/2005"

paste(paste("x",1:5,sep=""),collapse="+")

## [1] "x1+x2+x3+x4+x5"

x <- c("I really", "like writing", "R code")
y <- strsplit(x, split=" ")
y[[2]]

## [1] "like"    "writing"

sapply(y, "[", 1) # on the fly

## [1] "I"    "like" "R"

sapply(y, "[", 2) # on the fly

## [1] "really"  "writing" "code"

• Merging - joining data sets together - usually on key variables, usually "id"
• merge() is the most common way to do this with data sets
• rbind/cbind - row/column bind, respectively
  • rbind is the equivalent of "appending" in Stata or "setting" in SAS
  • cbind allows you to add columns in addition to the previous ways
• reshape2 package also has a lot of information about different ways to reshape data (wide to long, etc) - but has a different (and sometimes more intuitive syntax)
• t() is a function that will transpose the data

merged.data <- merge(base, visits, by="id")
merged.data[1:5,]

##   id      Age visit  Outcome
## 1  1 55.00000     1 10.00000
## 2  1 55.00000     3 23.91304
## 3  1 55.00000     2 37.82609
## 4  2 55.55556     2 11.73913
## 5  2 55.55556     1 25.65217

dim(merged.data)

## [1] 24  4

all.data <- merge(base, visits, by="id", all=TRUE)
tail(all.data)

##    id      Age visit  Outcome
## 21  7 58.33333     2 48.26087
## 22  8 58.88889     2 22.17391
## 23  8 58.88889     1 36.08696
## 24  8 58.88889     3 50.00000
## 25  9 59.44444    NA       NA
## 26 10 60.00000    NA       NA

dim(all.data)

## [1] 26  4

Disclaimer: the reshape command in R is not remarkably intuitive.

• Wide - multiple measurements are variables / columns so that the data gets wider with more measurements
• Long - multiple measurements are rows so data gets longer with more measurements
• One example would be many ids with multiple visits

head(wide)

##   id visit1 visit2 visit3
## 1  1   Good   Good    Bad

head(long)

##   id visit Outcome
## 1  1     1    Good
## 2  1     2    Good
## 3  1     3     Bad

• Good resource: http://www.ats.ucla.edu/stat/r/faq/reshape.htm

head(Indometh) # this is long

## Grouped Data: conc ~ time | Subject
##   Subject time conc
## 1       1 0.25 1.50
## 2       1 0.50 0.94
## 3       1 0.75 0.78
## 4       1 1.00 0.48
## 5       1 1.25 0.37
## 6       1 2.00 0.19

• If you've reshaped a data set - to get it back, just reshape it again

reshape(wide, direction = "long")[1:10,]

##        Subject time conc
## 1.0.25       1 0.25 1.50
## 2.0.25       2 0.25 2.03
## 3.0.25       3 0.25 2.72
## 4.0.25       4 0.25 1.85
## 5.0.25       5 0.25 2.05
## 6.0.25       6 0.25 2.31
## 1.0.5        1 0.50 0.94
## 2.0.5        2 0.50 1.63
## 3.0.5        3 0.50 1.49
## 4.0.5        4 0.50 1.39

Note the row name change