Menu
I am trying to figure out the best precision for fuzzy string matching between two string names using agrep.
However, I will need to choose one precision "max.distance" to apply the same across all strings I am trying to match since the amount of string is huge. It won't be possible to choose the best precision value "max.distance" for every each string I am trying to match.
For instance, let's say I use the precision "max.distance" as "0.2", "0.1", and "0.05" for each "BANK OF AMERICA CORP" and "1st Capital Bank".
First, the below is for "BANK OF AMERICA CORP" for "max.distance" of "0.2","0.1", and "0.05":
> agrep("BANK OF AMERICA CORP",C1999_0[,2],ignore.case = TRUE, value = TRUE,fixed = TRUE,max.distance =0.2)
[1] "BANK OF AMERICA/PRIVATE BANK WEST" "BANK OF AMERICA SECURITIES"
[3] "BANK OF AMERICA SEC LLC" "BANK OF AMERICA SECURITIES LLC"
[5] "BANK OF AMERICA NT & SA" "BANK OF AMERICA CORP"
[7] "ALLIANZ OF AMERICA CORP" "Bank of America Securities/Vice Pre"
[9] "Bank of America Securities/Investme" "Bank of America/President"
[11] "Bank of America Securities LLC/Prin" "Bank of America Securities LLC/Mana"
[13] "Bank of America Securities LLC/Inve" "Bank of America Securities/Principa"
[15] "Bank of America Securities LLC/Bank" "Bank of America Sec/Investment Bank"
[17] "Bank Of America Securities/Managing" "Bank of America/Chairman--Midwest A"
[19] "Bank of America Securities LLC/Vice" "Bank of America Corporation/Sales C"
[21] "Bank of America Securities/Broker" "Bank of America Corporation/Banker"
[23] "Bank of America Corporation/Senior" "Bank of America Securities/Equity R"
[25] "Bank of America Corporation/Vice Ch" "BANK OF AMERICA CORPORATION"
[27] "BANK OF AMERICA HEADQUARTERS" "BANK OF AMERICA ADMINISTRATION"
[29] "BANK OF AMERICA N A" "Bank of America/Commercial Banking"
[31] "Bank of America Sec./Investment Ban"
>
> agrep("BANK OF AMERICA CORP",C1999_0[,2],ignore.case = TRUE, value = TRUE,fixed = TRUE,max.distance =0.1)
[1] "BANK OF AMERICA CORP" "ALLIANZ OF AMERICA CORP"
[3] "Bank of America Corporation/Sales C" "Bank of America Corporation/Banker"
[5] "Bank of America Corporation/Senior" "Bank of America Corporation/Vice Ch"
[7] "BANK OF AMERICA CORPORATION"
>
> agrep("BANK OF AMERICA CORP",C1999_0[,2],ignore.case = TRUE, value = TRUE,fixed = TRUE,max.distance =0.05)
[1] "BANK OF AMERICA CORP" "Bank of America Corporation/Sales C"
[3] "Bank of America Corporation/Banker" "Bank of America Corporation/Senior"
[5] "Bank of America Corporation/Vice Ch" "BANK OF AMERICA CORPORATION"
Then below is the "1st Capital Bank" for "max.distance" of "0.2", "0.1", and "0.05":
> agrep("1st Capital Bank",C1999_0[,2],ignore.case = TRUE, value = TRUE,fixed = TRUE,max.distance =0.2)
[1] "HURST CAPITAL PARTNERS"
[2] "SOY CAPITAL BANK"
[3] "FIRST CAPITOL BANK OF VICTOR"
[4] "OSTERWEIS CAPITAL MANAGEMENT"
[5] "1ST NATIONAL BANK"
[6] "FIRST CAPITAL BANK"
[7] "SEATTLE 1ST NAT'L BANK"
[8] "FIELD POINT CAPITAL MANAGEMENT"
[9] "SUMMERSET CAPITAL MANAGEMENT"
[10] "AMERIQUEST CAPITAL ASSOC"
[11] "BB&T CAPITAL MARKETS"
[12] "HUGHES CAPITAL MANAGEMENT"
[13] "WELLS CAPITAL MANAGEMENT"
[14] "SUPERIOR ST CAPITAL ADVISORS"
[15] "ORMES CAPITAL MARKETS INC"
[16] "1ST NAT'L BANK OF IL"
[17] "ADVENT CAPITAL MANAGEMENT"
[18] "1ST CAPITOL BANK"
[19] "BIONDI REISS CAPITAL MANAGEMENT"
[20] "CCYBYS CAPITAL MARKETS"
[21] "SEACOAST CAPITAL PARTNERS"
[22] "DOUGLAS CAPITAL MANAGEMENT"
[23] "HIGHFIELDS CAPITAL MANAGEMENT"
[24] "PRECEPT CAPITAL MANAGEMENT LP"
[25] "AUGUST CAPITAL MANAGEMENT"
[26] "SAKSA CAPITAL MANAGEMENT"
[27] "IMS CAPITAL MANAGEMENT"
[28] "TRENT CAPITAL MANAGEMENT"
[29] "Ormes Capital Management"
[30] "GARNET CAPITAL MANAGEMENT LLC"
[31] "INTERFASE CAPITAL MANAGERS"
[32] "RJS CAPITAL MANAGEMENT INC"
[33] "1ST NATIONAL BANK OF DE KALB"
[34] "1ST NAT'L BANK OF PHILLIPS CO"
[35] "1ST NAT'L BANK OF OKLAHOMA"
[36] "PROGRESS CAPITAL MANAGEMENT INC"
[37] "CAPITAL BANK & TRUST"
[38] "1ST NATL BANK"
[39] "ASB Capital Management/Real Estate"
[40] "Sears Capital Management"
[41] "Osterweis Capital Management/Invest"
[42] "Cerberus Capital Management LP/Asse"
[43] "LVS Capital Management/President"
[44] "1st Central Bank/Banker"
[45] "Summit Capital Management"
[46] "Orwes Capital Markets/Stockbroker"
[47] "Ormes Capital Management/Investment"
[48] "Nevis Capital Management/Investment"
[49] "Duncan Hurst Capital Management"
[50] "Progress Capital Management/Preside"
[51] "Cerberus Capital Management LP"
[52] "Wit Capital/Banker"
[53] "Ormes Capital Markets Inc."
[54] "Ormes Capital Markets/President & C"
[55] "Berents & Hess Capital Management"
[56] "Progress Capital Management/Venture"
[57] "First Capital Bank of KY"
[58] "Foothill Capital/Banker"
[59] "Pequot Capital Management/Equity Re"
[60] "First Dominion Capital/Banking"
[61] "Greenwhich Capital/Banker"
[62] "Veritas Capital Management/Banker"
[63] "Veritas Capital Management/Investme"
[64] "Lesese Capital Management/Investmen"
[65] "Douglas Capital Management/Investme"
[66] "FIRST NATINAL BANK OF AMARILLO"
[67] "NEVIS CAPITAL MANAGEMENT"
[68] "VERITAS CAPITAL MANAGEMENT"
[69] "SIEBERT CAPITAL MARKETS"
[70] "HOURGLASS CAPITAL MANAGEMENT"
[71] "1ST NATIONAL BANK DALHART"
[72] "TEXAS CAPITAL BANK"
[73] "NICHOLAS CAPITAL MANAGEMENT"
[74] "CERBUS CAPITAL MANAGEMENT"
[75] "CROESUS CAPITAL MANAGEMENT"
[76] "EAST WEST CAPITAL ASSOCIATES INC"
[77] "PRENDERGAST CAPITAL MANAGEMENT"
[78] "NANTUCKET CAPITAL MANAGEMENT"
[79] "1ST NATIONAL BANK TEMPLE"
[80] "ENTRUST CAPITAL INC"
[81] "1ST NATIONAL BANK OF IL"
[82] "SIMMS CAPITAL MANAGEMENT"
[83] "FIRST CAPITAL ADVISORS"
[84] "FIRST CAPITAL MANAGEMENT LTD"
[85] "1ST NATIONAL BANK & TRUST"
[86] "PENTECOST CAPITAL MANAGEMENT INC"
[87] "EAST-WEST CAPITAL ASSOCIATES"
[88] "1ST NAT'L BANK OF JOLIET"
[89] "FIRST CAPITOL BANK OF VICTO"
[90] "FIRST CAPITAL FINANCIAL"
[91] "PACIFIC COAST CAPITAL PARTNERS"
[92] "FIRST CAPITOL BANK"
[93] "FIRST CAPITAL ENGINEERING"
[94] "MIDWEST CAPITOL MANAGEMENT"
[95] "PEQUOT CAPITAL MANAGEMENT"
[96] "AGGOTT CAPITAL MANAGEMENT"
[97] "SIMMS CAPITAL MANAGEMENT INC"
[98] "PHILLIPS CAPITAL MANAGEMENT LLC"
[99] "1ST NATIONAL BANK OF COLD SP"
[100] "SOY CAPITOL BANK"
>
> agrep("1st Capital Bank",C1999_0[,2],ignore.case = TRUE, value = TRUE,fixed = TRUE,max.distance =0.1)
[1] "FIRST CAPITOL BANK OF VICTOR" "FIRST CAPITAL BANK"
[3] "1ST CAPITOL BANK" "First Capital Bank of KY"
[5] "TEXAS CAPITAL BANK" "FIRST CAPITOL BANK OF VICTO"
[7] "FIRST CAPITOL BANK"
>
> agrep("1st Capital Bank",C1999_0[,2],ignore.case = TRUE, value = TRUE,fixed = TRUE,max.distance =0.05)
[1] "FIRST CAPITAL BANK" "1ST CAPITOL BANK"
[3] "First Capital Bank of KY"
As you can see, it's really difficult to find the common precision value for "max.distance" to apply for every each string such as "BANK OF AMERICA CORP" and "1st Capital Bank". Also I have far much more firm names other than these two which is the reason why I am having difficulty in finding the common precision value and command for fuzzy string match.
The original data file for C1999_0 is too huge to attach so I think only using the output values as shown above would be sufficient to replicate.
I know there are several sub categories to manipulate such as cost, substitutions, insertion, etc. but they did not make much difference with only changing the "max.distance" value itself.
I would really appreciate if I can get help on this!
336
This topic describes how fuzzy scores are calculated when comparing two strings or two terms. The fuzzy search algorithm calculates a fuzzy score for each string comparison. The higher the score, the more similar the strings are. A score of 1.0 means the strings are identical.
The FuzzyWuzzy ratio raw score is a measure of the strings similarity as an int in the range [0, 100]. For two strings X and Y, the score is defined by int(round((2.0 * M / T) * 100)) where T is the total number of characters in both strings, and M is the number of matches in the two strings.
Fuzzy Matching (also called Approximate String Matching) is a technique that helps identify two elements of text, strings, or entries that are approximately similar but are not exactly the same. For example, let's take the case of hotels listing in New York as shown by Expedia and Priceline in the graphic below.
The Levenshtein Distance (LD) is one of the fuzzy matching techniques that measure between two strings, with the given number representing how far the two strings are from being an exact match. The higher the number of the Levenshtein edit distance, the further the two terms are from being identical.
An issue with agrep is that it is like grep as documented in help("grep")
Since someone who read the description carelessly even filed a bug report on it, do note that this matches substrings of each element of
x(just asgrepdoes) and not whole elements. See alsoadistin package utils, which optionally returns the offsets of the matched substrings.
This seem to be the issue in your latter example since of you have many names that contains "Capital" or "Bank" or both. What I would do, is to use to compute the Levenshtein distance (which is what agrep does or a generalized version and only for substrings) and take the ones with shortest distance. E.g.,
C1999 <- c("HURST CAPITAL PARTNERS", "SOY CAPITAL BANK", "FIRST CAPITOL BANK OF VICTOR", "OSTERWEIS CAPITAL MANAGEMENT", "1ST NATIONAL BANK", "FIRST CAPITAL BANK", "SEATTLE 1ST NAT'L BANK", "FIELD POINT CAPITAL MANAGEMENT", "SUMMERSET CAPITAL MANAGEMENT", "AMERIQUEST CAPITAL ASSOC", "BB&T CAPITAL MARKETS", "HUGHES CAPITAL MANAGEMENT", "WELLS CAPITAL MANAGEMENT", "SUPERIOR ST CAPITAL ADVISORS", "ORMES CAPITAL MARKETS INC", "1ST NAT'L BANK OF IL", "ADVENT CAPITAL MANAGEMENT", "1ST CAPITOL BANK", "BIONDI REISS CAPITAL MANAGEMENT", "CCYBYS CAPITAL MARKETS", "SEACOAST CAPITAL PARTNERS", "DOUGLAS CAPITAL MANAGEMENT", "HIGHFIELDS CAPITAL MANAGEMENT", "PRECEPT CAPITAL MANAGEMENT LP", "AUGUST CAPITAL MANAGEMENT", "SAKSA CAPITAL MANAGEMENT", "IMS CAPITAL MANAGEMENT", "TRENT CAPITAL MANAGEMENT", "Ormes Capital Management", "GARNET CAPITAL MANAGEMENT LLC", "INTERFASE CAPITAL MANAGERS", "RJS CAPITAL MANAGEMENT INC", "1ST NATIONAL BANK OF DE KALB", "1ST NAT'L BANK OF PHILLIPS CO", "1ST NAT'L BANK OF OKLAHOMA", "PROGRESS CAPITAL MANAGEMENT INC", "CAPITAL BANK & TRUST", "1ST NATL BANK", "ASB Capital Management/Real Estate", "Sears Capital Management", "Osterweis Capital Management/Invest", "Cerberus Capital Management LP/Asse", "LVS Capital Management/President", "1st Central Bank/Banker", "Summit Capital Management", "Orwes Capital Markets/Stockbroker", "Ormes Capital Management/Investment", "Nevis Capital Management/Investment", "Duncan Hurst Capital Management", "Progress Capital Management/Preside", "Cerberus Capital Management LP", "Wit Capital/Banker", "Ormes Capital Markets Inc.", "Ormes Capital Markets/President & C", "Berents & Hess Capital Management", "Progress Capital Management/Venture", "First Capital Bank of KY", "Foothill Capital/Banker", "Pequot Capital Management/Equity Re", "First Dominion Capital/Banking", "Greenwhich Capital/Banker", "Veritas Capital Management/Banker", "Veritas Capital Management/Investme", "Lesese Capital Management/Investmen", "Douglas Capital Management/Investme", "FIRST NATINAL BANK OF AMARILLO", "NEVIS CAPITAL MANAGEMENT", "VERITAS CAPITAL MANAGEMENT", "SIEBERT CAPITAL MARKETS", "HOURGLASS CAPITAL MANAGEMENT", "1ST NATIONAL BANK DALHART", "TEXAS CAPITAL BANK", "NICHOLAS CAPITAL MANAGEMENT", "CERBUS CAPITAL MANAGEMENT", "CROESUS CAPITAL MANAGEMENT", "EAST WEST CAPITAL ASSOCIATES INC", "PRENDERGAST CAPITAL MANAGEMENT", "NANTUCKET CAPITAL MANAGEMENT", "1ST NATIONAL BANK TEMPLE", "ENTRUST CAPITAL INC", "1ST NATIONAL BANK OF IL", "SIMMS CAPITAL MANAGEMENT", "FIRST CAPITAL ADVISORS", "FIRST CAPITAL MANAGEMENT LTD", "1ST NATIONAL BANK & TRUST", "PENTECOST CAPITAL MANAGEMENT INC", "EAST-WEST CAPITAL ASSOCIATES", "1ST NAT'L BANK OF JOLIET", "FIRST CAPITOL BANK OF VICTO", "FIRST CAPITAL FINANCIAL", "PACIFIC COAST CAPITAL PARTNERS", "FIRST CAPITOL BANK", "FIRST CAPITAL ENGINEERING", "MIDWEST CAPITOL MANAGEMENT", "PEQUOT CAPITAL MANAGEMENT", "AGGOTT CAPITAL MANAGEMENT", "SIMMS CAPITAL MANAGEMENT INC", "PHILLIPS CAPITAL MANAGEMENT LLC", "1ST NATIONAL BANK OF COLD SP", "SOY CAPITOL BANK")
func <- function(x, y, tol = 0L){
require(stringdist)
dista <- stringdist::stringdist(x, y, method = "lv")
min_dista <- min(dista)
y[dista <= min_dista + tol]
}
func("1st Capital Bank", C1999)
#R [1] "Wit Capital/Banker"
func("1st Capital Bank", C1999, 4L)
#R [1] "Wit Capital/Banker" "First Capital Bank of KY"
func("1st Capital Bank", C1999, 10L)
#R [1] "SOY CAPITAL BANK" "1ST NATIONAL BANK"
#R [3] "FIRST CAPITAL BANK" "1ST CAPITOL BANK"
#R [5] "Ormes Capital Management" "1ST NATL BANK"
#R [7] "Sears Capital Management" "1st Central Bank/Banker"
#R [9] "Summit Capital Management" "Wit Capital/Banker"
#R [11] "Ormes Capital Markets Inc." "First Capital Bank of KY"
#R [13] "Foothill Capital/Banker" "Greenwhich Capital/Banker"
#R [15] "TEXAS CAPITAL BANK" "FIRST CAPITOL BANK"
#R [17] "SOY CAPITOL BANK"
# ignoring cases
func <- function(x, y, tol = 0L){
require(stringdist)
dista <- stringdist::stringdist(tolower(x), tolower(y), method = "lv")
min_dista <- min(dista)
y[dista <= min_dista + tol]
}
func("1st Capital Bank", C1999, 0L)
#R [1] "1ST CAPITOL BANK"
The tol parameter in the func controls the if you want to include examples that are tol further away from the minimum Levenshtein distance. I see that I did not answer exactly what you asked for (How can I get the precise common “max.distance” value for fuzzy string matching using agrep?) but I think my answer might be what you are looking for.
I use stringdist::stringdist instead of adist since the former seems to be faster. It can still be a bit slow and I wish there where an R package where you could set a maximum distance but I have not come across such a package. This could make the computation of the (then capped) Levenshtein distance much faster.
110
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
