Merging two Data Frames using Fuzzy/Approximate String Matching in R
DESCRIPTION
I have two datasets with information that I need to merge. The only common fields that I have are strings that do not perfectly match and a numerical field that can be substantially different
The only way to explain the problem is to show you the data. Here is a.csv and b.csv. I am trying to merge B to A.
There are three fields in B and four in A. Company Name (File A Only), Fund Name, Asset Class, and Assets. So far, my focus has been on attempting to match the Fund Names by replacing words or parts of the strings to create exact matches and then using:
a <- read.table(file = "http://bertelsen.ca/R/a.csv",header=TRUE, sep=",", na.strings=F, strip.white=T, blank.lines.skip=F, stringsAsFactors=T)
b <- read.table(file = "http://bertelsen.ca/R/b.csv",header=TRUE, sep=",", na.strings=F, strip.white=T, blank.lines.skip=F, stringsAsFactors=T)
merge(a,b, by="Fund.Name")
However, this only brings me to about 30% matching. The rest I have to do by hand.
Assets is a numerical field that is not always correct in either and can vary wildly if the fund has low assets. Asset Class is a string field that is "generally" the same in both files, however, there are discrepancies.
Adding to the complication are the different series of funds, in File B. For example:
AGF Canadian Value
AGF Canadian Value-D
In these cases, I have to choose the one that is not seried, or choose the one that is called "A", "-A", or "Advisor" as the match.
QUESTION
What would you say is the best approach? This excercise is something that I have to do on a monthly basis and matching them manually is incredibly time consuming. Examples of code would be instrumental.
IDEAS
One method that I think may work is normalizing the strings based on the first capitalized letter of each word in the string. But I haven't been able to figure out how to pull that off using R.
Another method I considered was creating an index of matches based on a combination of assets, fund name, asset class and company. But again, I'm not sure how to do this with R. Or, for that matter, if it's even possible.
Examples of code, comments, thoughts and direction are greatly appreciated!
Answer
It's highly recommended to use the dgrtwo/fuzzyjoin package.
stringdist_inner_join(a,b, by="Fund.Name")