Blog

In trying to merge some data, I was confronted with a problem of matching up strings where the author had mutilated them. He had done so in two ways: cutting them off at the 8th character or using personal abbreviations. The first one is relatively easy to deal with. The second one is not.

So I looked around a bit and found that there are others who had similar problems:

• http://stats.stackexchange.com/questions/3425/how-to-quasi-match-two-vectors-of-strings-in-r
• http://stackoverflow.com/questions/2231993/merging-two-data-frames-using-fuzzy-approximate-string-matching-in-r

The large table below shows the matching results. The leftmost column has the strings I need to match. The list to be matched against is the list of country and regional names and their ISO 3 abbreviations here: https://osf.io/59dr7/

It looks like for most cases the shortened version worked fine. 165 of 193 matches were found all of which were correct.

The agrep (with max distance = .1, the default), found a match in 175 cases, so only a little improvement there. But it gets worse, in many cases, it disagrees with the stricter matching method and gets it wrong. There is no case where it is correct over the simpler method. Strangely, in all 10 cases where the simple method failed, agrep got it right. But it got it wrong in the easier cases. In some cases, it is truly bizarre: given the string “United S”, it goes for “United Republic of Tanzania” instead of the much easier “United States”. Strangely, a common error is preferring a subset/longer version over an exact match. No human would make this error. E.g. given “Moldova”, it prefers “Moldova, Republic” of over just “Moldova”.

There are a number of different errors it makes. In the comments below I have noted the type of error (my judgment).

For the moment, I would caution the use of this algorithm.

The R code:

gn = read.csv("genetic_distance.csv", encoding = "UTF-8", stringsAsFactors = F)
gn$abbrev = as_abbrev(gn$Country)

trans = read.csv("countrycodes.csv", sep=";", encoding = "UTF-8", stringsAsFactors = F)
trans$shorter = str_sub(trans$Names, 1, 8)

intersect(trans$shorter, gn$Country)

matches = data.frame(source_names = gn$Country,
                     to_short = pmatch (gn$Country, trans$shorter)
                     )

agrep(gn$Country[4], trans$shorter)

best_matches = matrix(nrow = nrow(gn))
for (idx in seq_along(gn$Country)) {
  match_idx = agrep(gn$Country[idx], trans$shorter, max.distance = .1, useBytes = T)
  #skip on no match
  if (length(match_idx) == 0) next
  #insert match
  best_matches[idx] = match_idx
}

matches$agrep = best_matches

matches$to_short_result = trans[matches$to_short, "Names"]
matches$agrep_result = trans[matches$agrep, "Names"]

for (idx in 1:nrow(matches)) {
  if (!is.na(matches[idx, "to_short_result"])) {
    matches[idx, "best_match"] = matches[idx, "to_short_result"]
  }
  if (is.na(matches[idx, "to_short_result"])) {
    matches[idx, "best_match"] = matches[idx, "agrep_result"]
  }
}

#output
write.table(matches, "clipboard", na = "", sep = "\t")