# Selecting "Statistical Distances" for "Fuzzy Match"

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?