CODEWITHSUNDEEP
rfor-looptexttmlevenshtein-distance
jvalenti
jvalenti

Reputation: 640

R - text mining over multiple columns with levenshtein distance

I would like to compare text strings multiple times across multiple columns, via levenshtein distance (adist function in R). What I would like to do is compare source1$name to source2$name. If there is no match (i.e. if an NA is returned for match.s1.s2$s2.i then perform same function using the addresses (source1$address and source2$address) listed in the two data frames. Basically I am looking for a way to refine my matches across multiple fields. An example is below. 

name <- c("holiday inn", "geico", "zgf", "morton phillips")
address <- c("400 lafayette pl tupelo ms", "227 geico plaza chevy chase md", 
         "811 quincy st washington dc", "1911 1st st rockville md")

source1 <- data.frame(name, address)

name <- c("williams sonoma", "mamas bbq", "davis polk", "hop a long diner",
      "joes crag shack", "mike lowry place", "holiday inn", "zummer")

name2 <- c(NA, NA, NA, NA, NA, NA, "hi express", "zummer gunsul frasca")
address <- c("2 reads way new castle de", "248 w 4th st newark de",
         "1100 21st st nw washington dc", "1804 w 5th st wilmington de",
         "1208 kenwood parkway holdridge nb", "4203 ocean drive miami fl",
         "400 lafayette pl tupelo ms", "811 quincy st washington dc")
source2 <- data.frame(name, name2, address)

removeSPE <- function(x) gsub("[[:punct:]]", " ", x)

cleanup <- function(x){
x <- as.character(x) # convert to character
x <- tolower(x) # make all lowercase
x <- sapply(x, removeSPE) # remove special characters
x <- trimws(x) # remove extra white space
#x <- sapply(x, removeStopWords) # remove stopwords, defined above
#x <- trimws(x) # since stopwords have been removed, there is extra white space left, this removes it
x <- gsub("^. .$", "", x)
return(x)
}

source1$name <- cleanup(source1$name)
source2$name <- cleanup(source2$name)
source2$name2 <- cleanup(source2$name2)

source1$address <- cleanup(source1$address)
source2$address <- cleanup(source2$address)

source1$name <- cleanup(source1$name)
source2$name <- cleanup(source2$name)
source2$name2 <- cleanup(source2$name2)

dist.name<- adist(source1$name,source2$name, partial = TRUE, ignore.case = TRUE)
dist.name2 <- adist(source1$name, source2$name2, partial = TRUE, ignore.case = TRUE)
dist.address <- adist(source1$address, source2$address, partial = TRUE, ignore.case = TRUE)

min.name<-apply(dist.name, 2, min)
min.name2 <- apply(dist.name2, 2, min)


match.s1.s2<-NULL  
for(i in 1:nrow(dist.address))
{
s2.i<-match(min.name[i],dist.name[i,])
s1.i<-i
match.s1.s2<-
rbind(data.frame(s2.i=s2.i,s1.i=s1.i,s2name=source2[s2.i,]$name, s1name=source1[s1.i,]$name, 
                            adist=min.name[i], s1.i.address = source1[s1.i,]$address,
                            s2.i.address = source2[s2.i,]$address),match.s1.s2)
}

match.s1.s2

The desired result is that row 3 in source1 matches with row 8 of source2 and row 4 of source1 matches with row 7 of source2. is there a way to incorporate dist.name2 and dist.address (defined above) in the above for-loop? Maybe a while statement? The actual data frames I will be using have 500 and 24,000 rows approximately.

Upvotes: 1

Views: 874

Answers (1)

AidanGawronski
AidanGawronski

Reputation: 2085

Cosine distance appears to do a pretty good job:

out_df <- c()
for(x in source1$name) {
  for(y in source2$full2) {
    if (is.na(source2[source2$full2 == y, "name2"])) {
      x2 <- source1[source1$name == x, "address"]
      y2 <- source2[source2$full2 == y, "address"]
      row <- data.frame(x, y2, stringdist(x, y, method="cosine", q = 1))
      names(row) <- c("name1", "full2", "distance")
      out_df <- rbind(out_df, row)
    } else {
      row <- data.frame(x, y, stringdist(x, y, method="cosine", q = 1))
      names(row) <- c("name1", "full2", "distance")
      out_df <- rbind(out_df, row)
    }
  }
}

names(out_df) <- c("name1", "full2", "distance")

grab <- aggregate(distance ~ name1, data = out_df, FUN = min)

merge(out_df, grab)

You will still have to figure out how to exclude results that you don't want.

Upvotes: 1

Related Questions