Selecting "Statistical Distances" for "Fuzzy Match"

Question

I am trying to better understand how to select "statistical distances" for "fuzzy matching".

To illustrate my problem, consider the following two datasets (I created this using the R programming language):

    table_1 = data.frame(id = c("123", "123", "125", "C125-B"), 
    date_1 = c("2010-01-31","2010-01-31", "2016-01-31", "2018-01-31" ))
    
    table_1$id = as.factor(table_1$id)
    table_1$date_1 = as.factor(table_1$date_1)
    
    table_2 = data.frame(id = c("5123", "123 A", "125", "125"), 
    date_2 = c("2009-01-31","2010-01-31", "2010-01-31", "2010-01-31" ),
    date_3 = c("2011-01-31","2010-01-31", "2020-01-31", "2020-01-31" ))
        
    table_2$id = as.factor(table_2$id)
    table_2$date_2 = as.factor(table_2$date_2)
    table_2$date_3 = as.factor(table_2$date_3)
    
        table_1
          id     date_1
    1    123 2010-01-31
    2    123 2010-01-31
    3    125 2016-01-31
    4 C125-B 2018-01-31
    
     table_2
         id     date_2     date_3
    1  5123 2009-01-31 2011-01-31
    2 123 A 2010-01-31 2010-01-31
    3   125 2010-01-31 2020-01-31
    4   125 2010-01-31 2020-01-31

I would like to perform an "inner join" on these two tables if both the following conditions are met:

1) if table_1$id fuzzy equals table_2$id

2) if table_1$date_1 between (table_2$date_2, table_2$date_3)

To achieve this task, I researched the following library in R called " : https://cran.r-project.org/web/packages/fuzzyjoin/index.html

I then attempted to perform the desired inner join:

    library(fuzzyjoin)
    library(dplyr)
    
    table_1$date_1 = as.Date(table_1$date_1)
    table_2$date_2 = as.Date(table_2$date_2)
    table_2$date_3 = as.Date(table_2$date_3)
    
    stringdist_inner_join(table_1, table_2, by = "id", max_dist = 2) %>%
      filter(date_1 >= date_2, date_1 <= date_3)

• The above code seemed to successfully compile and run - however, this is a simple example and I am not sure if the method I have used is ideal for larger datasets with "longer id variables".

• Suppose you have to match numerical ID's which sometimes might have random letters added in them, unnecessary spaces within the ID's, unwanted symbols between the numbers, the last digit missing, etc. Using a standard "inner join", these "corruptions" will prevent matches on these records, when in fact these records are the same.

• Obviously, one can try to manually identify all such possible "types of corruptions" and try to rectify these types of problems (e.g. using the "trim()" function https://www.rdocumentation.org/packages/gdata/versions/2.18.0/topics/trim), however there are simply too many types of possible corruptions that can potentially occur - it is impossible to identify them all. This is why I think that "fuzzy matching" might be a better approach to this kind of problem.

• When it comes to "fuzzy matching", there seems to be many different ways to perform "fuzzy matching" - based on different types of "statistical distances".

My Question: I am not familiar on which types of distances are better suited for certain types of problems - can someone please suggest a statistical distance and an R implementation for performing this task?

Answer 1

You could circumnavigate the problem by using N-gram analysis, which will break each numerical ID into all 1-grams of characters a,b,c,&,*,!,%, then 2-grams of characters, then all 3-grams of characters. I typically run up to 5 or 6-grams which has shown to be able to pick up similarities (clustering) between objects (words). Anything more than e.g. a 6-gram results in very large matrices.

If you knew e.g. that Microsoft uses N-gram analysis for spell checking and Google has a large library of N-grams on everything on the internet, you may be able to expand your horizons on ditching fuzzification. For example, in MS Word, the rule "i before e, except after c," can be computationaly derived because if a user types "ie", Word will look up a frequency table of e.g., 3-, 4-, and 5-grams containing the 2-gram "ie", and in part, make a recommendation. Obviously, this is a simple cartoon example of a complex issue, but I'm just trying to explain how to over come errors by looking up N-gram usage frequencies.