• 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!

• is.na - is TRUE if the data is FALSE otherwise
• ! - negation (NOT)
  • if is.na(x) is TRUE, then !is.na(x) is FALSE
• all takes in a logical and will be TRUE if ALL are TRUE
  • all(!is.na(x)) - are all values of x NOT NA
• any will be TRUE if ANY are true
  • any(is.na(x)) - do we have any NA's in x?
• complete.cases - returns TRUE if EVERY value of a row is NOT NA
  • very stringent condition
  • FALSE missing one value (even if not important)

One of the most important aspects of data cleaning is missing values.

Types of "missing" data:

• NA - general missing data
• NaN - stands for "Not a Number", happens when you do 0/0.
• Inf and -Inf - Infinity, happens when you take a positive number (or negative number) by 0.

Each missing data type has a function that returns TRUE if the data is missing:

• NA - is.na
• NaN - is.nan
• Inf and -Inf - is.infinite
• is.finite returns FALSE for all missing data and TRUE for non-missing

One important aspect (esp with subsetting) is that logical operations return NA for NA values. Think about it, the data could be > 2 or not we don't know, so R says there is no TRUE or FALSE, so that is missing:

x = c(0, NA, 2, 3, 4)
x > 2

[1] FALSE    NA FALSE  TRUE  TRUE

What to do? What if we want if x > 2 and x isn't NA?
Don't do x != NA, do x > 2 and x is NOT NA:

x != NA

[1] NA NA NA NA NA

x > 2 & !is.na(x)

[1] FALSE FALSE FALSE  TRUE  TRUE

What about seeing if a value is equal to multiple values? You can do (x == 1 | x == 2) & !is.na(x), but that is not efficient.

(x == 0 | x == 2) # has NA

[1]  TRUE    NA  TRUE FALSE FALSE

(x == 0 | x == 2) & !is.na(x) # No NA

[1]  TRUE FALSE  TRUE FALSE FALSE

what to do?

Introduce the %in% operator:

x %in% c(0, 2) # NEVER has NA and returns logical

[1]  TRUE FALSE  TRUE FALSE FALSE

reads "return TRUE if x is in 0 or 2". (Like inlist in Stata).

NEVER has NA, even if you put it there (BUT DON'T DO THIS):

x %in% c(0, 2, NA) # NEVER has NA and returns logical

[1]  TRUE  TRUE  TRUE FALSE FALSE

x %in% c(0, 2) | is.na(x)

[1]  TRUE  TRUE  TRUE FALSE FALSE

Similarly with logicals, operations/arithmetic with NA will result in NAs:

x + 2

[1]  2 NA  4  5  6

x * 2

[1]  0 NA  4  6  8

• unique - gives you the unique values of a variable
• table(x) - will give a one-way table of x
  • table(x, useNA = "ifany") - will have row NA
• table(x, y) - will give a cross-tab of x and y

margin.table finds the marginal sums of the table. margin is 1 for rows, 2 for columns in general in R. Here is the column sums of the table:

margin.table(tab, 2)

   0    1    2    3    4 <NA> 
   1    1    2    4    2    0 

From https://data.baltimorecity.gov/City-Government/Baltimore-City-Employee-Salaries-FY2014/2j28-xzd7 http://www.aejaffe.com/summerR_2016/data/Baltimore_City_Employee_Salaries_FY2014.csv

Read the CSV into R Sal:

Sal = read.csv("http://www.aejaffe.com/summerR_2016/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 

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)

[1] "1" "2" "3" "4" "5"

paste(1:5, collapse = " ")

[1] "1 2 3 4 5"

Useful String functions

• toupper(), tolower() - uppercase or lowercase your data:
• str_trim() (in the stringr package) or trimws in base
  • will trim whitespace
• nchar - get the number of characters in a string
• paste() - paste strings together with a space
• paste0 - paste strings together with no space as default

Like dplyr, the stringr package:

• Makes some things more intuitive
• Is different than base R
• Is used on forums for answers
• Has a standard format for most functions
  • the first argument is a string like first argument is a data.frame in dplyr

• 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 - think "Find" on steroids

• 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
• ^ matches start of vector ^a - starts with "a"
• $ matches end of vector b$ - ends with "b"

Very similar:

Base R

• substr(x, start, stop) - substrings from position start to position stop
• strsplit(x, split) - splits strings up - returns list!

stringr

• str_sub(x, start, end) - substrings from position start to position end
• str_split(string, pattern) - splits strings up - returns list!

In base R, strsplit splits a vector on a string into a list

x <- c("I really", "like writing", "R code programs")
y <- strsplit(x, split = " ") # returns a list
y

[[1]]
[1] "I"      "really"

[[2]]
[1] "like"    "writing"

[[3]]
[1] "R"        "code"     "programs"

stringr::str_split do the same thing:

library(stringr)
y2 <- str_split(x, " ") # returns a list
y2

[[1]]
[1] "I"      "really"

[[2]]
[1] "like"    "writing"

[[3]]
[1] "R"        "code"     "programs"

One example case is when you want to split on a period ".". In regular expressions . means ANY character, so

str_split("I.like.strings", ".")

[[1]]
 [1] "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""

str_split("I.like.strings", fixed("."))

[[1]]
[1] "I"       "like"    "strings"

suppressPackageStartupMessages(library(dplyr)) # must be loaded AFTER plyr
y[[2]]

[1] "like"    "writing"

sapply(y, dplyr::first) # on the fly

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

sapply(y, nth, 2) # on the fly

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

sapply(y, last) # on the fly

[1] "really"   "writing"  "programs"

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)

str_detect, str_subset, str_replace, and str_replace_all 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.

• str_detect - returns TRUE if pattern is found
• str_subset - returns only the strings which pattern were detected
  • convenient wrapper around x[str_detect(x, pattern)]
• str_extract - returns only strings which pattern were detected, but ONLY the pattern
• str_replace - replaces pattern with replacement the first time
• str_replace_all - replaces pattern with replacement as many times matched

Base R does not use these functions. Here is a "translator" of the stringr function to base R functions

• str_detect - similar to grepl (return logical)
• grep(value = FALSE) is similar to which(str_detect())
• str_subset - similar to grep(value = TRUE) - return value of matched
• str_replace - similar to sub - replace one time
• str_replace_all - similar to gsub - replace many times

?modifiers

• fixed - match everything exactly
• regexp - default - uses regular expressions
• ignore_case is an option to not have to use tolower

Base R:

• Argument order is (pattern, x)
• Uses option (fixed = TRUE)

stringr

• Argument order is (string, pattern) aka (x, pattern)
• Uses function fixed(pattern)

These are the indices where the pattern match occurs:

grep("Rawlings",Sal$Name)

[1] 13832 13833 13834 13835

which(grepl("Rawlings", Sal$Name))

[1] 13832 13833 13834 13835

which(str_detect(Sal$Name, "Rawlings"))

[1] 13832 13833 13834 13835

These are the indices where the pattern match occurs:

head(grepl("Rawlings",Sal$Name))

[1] FALSE FALSE FALSE FALSE FALSE FALSE

head(str_detect(Sal$Name, "Rawlings"))

[1] FALSE FALSE FALSE FALSE FALSE FALSE

str_subset(Sal$Name, "Rawlings")

[1] "Rawlings,Kellye A"          "Rawlings,MarqWell D"       
[3] "Rawlings,Paula M"           "Rawlings-Blake,Stephanie C"

Sal %>% filter(str_detect(Name, "Rawlings"))

                        Name             JobTitle AgencyID
1          Rawlings,Kellye A EMERGENCY DISPATCHER   A40302
2        Rawlings,MarqWell D       AIDE BLUE CHIP   W02384
3           Rawlings,Paula M       COMMUNITY AIDE   A04015
4 Rawlings-Blake,Stephanie C                MAYOR   A01001
                Agency   HireDate AnnualSalary   GrossPay
1 M-R Info Technology  01/06/2003    $47980.00  $68426.73
2       Youth Summer   06/15/2012    $11310.00    $507.50
3      R&P-Recreation  12/10/2007    $19802.00   $8195.79
4       Mayors Office  12/07/1995   $163365.00 $161219.24

str_extract extracts just the matched string

ss = str_extract(Sal$Name, "Rawling")
head(ss)

[1] NA NA NA NA NA NA

ss[ !is.na(ss)]

[1] "Rawling" "Rawling" "Rawling" "Rawling"

str_extract_all extracts all the matched strings

head(str_extract(Sal$AgencyID, "\\d"))

[1] "0" "0" "2" "6" "9" "4"

head(str_extract_all(Sal$AgencyID, "\\d"), 2)

[[1]]
[1] "0" "2" "2" "0" "0"

[[2]]
[1] "0" "3" "0" "3" "1"

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

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

[1] "Payne El,Jackie"         "Payne Johnson,Nickole A"
[3] "Payne,Chanel"           

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

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

head(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"  

head(str_subset( Sal$Name, "^Payne.*"), 3)

[1] "Payne El,Jackie"         "Payne Johnson,Nickole A"
[3] "Payne,Chanel"           

head(str_subset( Sal$Name, "Leonard.?S"))

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

head(str_subset( Sal$Name, "Spence.*C.*"))

[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(Sal$AnnualSalary, 4)

[1] "$11310.00" "$53428.00" "$68300.00" "$62000.00"

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

Warning in head(as.numeric(Sal$AnnualSalary), 4): NAs introduced by
coercion

[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 in base R.

We can do the same thing (with 2 piping operations!) in dplyr

dplyr_sal = Sal
dplyr_sal = dplyr_sal %>% mutate( 
  AnnualSalary = AnnualSalary %>%
    str_replace(
      fixed("$"), 
      "") %>%
    as.numeric) %>%
  arrange(desc(AnnualSalary))
check_Sal = Sal
rownames(check_Sal) = NULL
all.equal(check_Sal, dplyr_sal)

[1] TRUE