Fuzzy string matching in r

Question

I have 2 datasets with more than 100K rows each. I would like to merge them based on fuzzy string matching one column('movie title') as well as using release date. I am providing a sample from both datasets below.

dataset-1

itemid userid rating       time                              title release_date
99991    1673    835      3 1998-03-27                             mirage         1995
99992    1674    840      4 1998-03-29                         mamma roma         1962
99993    1675    851      3 1998-01-08                     sunchaser, the         1996
99994    1676    851      2 1997-10-01                   war at home, the         1996
99995    1677    854      3 1997-12-22                      sweet nothing         1995
99996    1678    863      1 1998-03-07                         mat' i syn         1997
99997    1679    863      3 1998-03-07                          b. monkey         1998
99998    1680    863      2 1998-03-07                      sliding doors         1998
99999    1681    896      3 1998-02-11                       you so crazy         1994
100000   1682    916      3 1997-11-29 scream of stone (schrei aus stein)         1991

dataset - 2

itemid userid rating       time                                   title release_date
1    2844   4477      3 2013-03-09 fantã´mas - 〠l'ombre de la guillotine         1913
2    4936   8871      4 2013-05-05                                the bank         1915
3    4936  11628      3 2013-07-06                                the bank         1915
4    4972  16885      4 2013-08-19                   the birth of a nation         1915
5    5078  11628      2 2013-08-23                               the cheat         1915
6    6684   4222      3 2013-08-24                             the fireman         1916
7    6689   4222      3 2013-08-24                         the floorwalker         1916
8    7264   2092      4 2013-03-17                                the rink         1916
9    7264   5943      3 2013-05-12                                the rink         1916
10   7880  11628      4 2013-07-19                             easy street         1917

I have looked at 'agrep' but it only matches one string at a time. The 'stringdist' function is good but you need to run it in a loop, find the minimum distance and then go onto further precessing which is very time consuming given the size of the datasets. The strings can have typo's and special characters due to which fuzzy matching is required. I have looked around and found 'Lenenshtein' and 'Jaro-Winkler' methods. The later I read is good for when you have typo's in strings.

In this scenario, only fuzzy matching may not provide good results e.g., A movie title 'toy story' in one dataset can be matched to 'toy story 2' in the other which is not right. So I need to consider the release date to make sure the movies that are matched are unique.

I want to know if there is a way to achieve this task without using a loop? worse case scenario if I have to use a loop, how can I make it work efficiently and as fast as possible.

I have tried the following code but it has taken an awful amount of time to process.

for(i in 1:nrow(test))
  for(j in 1:nrow(test1))
  {

    test$title.match <- ifelse(jarowinkler(test$x[i], test1$x[j]) > 0.85,
                      test$title, NA)
  }

test - contains 1682 unique movie names converted to lower case test1 - contains 11451 unique movie names converted to lower case

Is there a way to avoid the for loops and make it work faster?

Answer 1

What about this approach to move you forward? You can adjust the degree of match from 0.85 after you see the results. You could then use dplyr to group by the matched title and summarise by subtracting release dates. Any zeros would mean the same release date.

dataset-1$title.match <- ifelse(jarowinkler(dataset-1$title, dataset_2$title) > 0.85, dataset-1$title, NA)