can someone know algorithm how to identify account names that are similar enough to be potentially merged and imported as one

Duplicates with different values: Geico val1 NaN =====>> Geico val1 val2 Geico NaN val2

Similar or almost exact Geico Gaico

  • $\begingroup$ Hmmm... I would create a custom distance map between the entries. Then, run over all pairs of indexes to determine the similar ones. $\endgroup$ – kate-melnykova Dec 3 '20 at 5:14
  • $\begingroup$ Thank you very much for your advice. What do you mean by a custom distance map? $\endgroup$ – miro_muras Dec 3 '20 at 13:21
  • $\begingroup$ Say, you have sample x and sample y, can you write down the function to determine if they are similar or not? $\endgroup$ – kate-melnykova Dec 3 '20 at 18:16

You specifically talk about account names, and so I assume they can be treated as strings.

One way to compare closeness of strings is the Levenshtein distance, defined as:

the minimum number of single-character edits (insertions, deletions or substitutions) required to change one word into the other.

It just so happens there is a nice library that implements this kind of fuzzy matching - fuzzywuzzy. They have some usage examples on the homepage.

Ideas for processing the data

In your case, if you know the correct account names, you could compute the similarity of just those correct ones to each of the actual entries, and use a threshold value to turn all close-matches into the correct account name.

Alternatively, you could compute pairwise similarities pair up the highest scores, reducing each pair to a single name. Iterate on this approach until you have no name-pairs with a similarity above a given threshold.

For the thresholds, in either case, you'd have to probably use a heuristic value.

  • $\begingroup$ Great! Thank you for your advice I will check that out. $\endgroup$ – miro_muras Dec 3 '20 at 13:23
  • $\begingroup$ Just note that Levenshtein distance is time-consuming. You may want to use regex to determine if two strings are different by a single symbol. $\endgroup$ – kate-melnykova Dec 3 '20 at 18:18
  • $\begingroup$ Can you please provide a simple example? Thank you! $\endgroup$ – miro_muras Dec 3 '20 at 20:17

Not the answer you're looking for? Browse other questions tagged or ask your own question.