2
$\begingroup$

I have a SQL (MS SQL Server) database of ~30 million companies. For example:

+-----------------------+----------------+-----------+
|     company_name      |    country     | ID_number |
+-----------------------+----------------+-----------+
| Mercedes Benz Limited | Germany        |     12345 |
| Apple Corporation     | United States  |     67899 |
| Aunt Mary Butcher     | United Kingdom |     56789 |
+-----------------------+----------------+-----------+

Then, I have another list of companies and I would like to assign ID_number based on approximate company name match.

+--------------------+----------------+
|      company       |    country     |
+--------------------+----------------+
| Mercedes Benz Ltd. | Germany        |
| Apple Corp.        | United States  |
| Butcher Aunt Mary  | United Kingdom |
| Volkswagen Gmbh    | Germany        |
+--------------------+----------------+

My goal obviously is to limit the number of comparisons I have to make. So I approach it the following way:

  1. Normalize the names - remove Ltd. Corp. etc.
  2. Filter by country
  3. Filter by name length (I assume that strings of very different length can't be very similar)
  4. Filter by first n letters
  5. Calculate the similarity with Levenshtein or Jaccard agains all filtered companies
  6. Select the best match

So for Mercedes Benz I would only take companies from Germany, that start with ME and have length of Mercedes Benz +-7 letters. However for Aunt Mary Butcher this wouldn't work because Butcher Aunt Mary doesn't start with AU.

Apart from this being very naive method it's also very slow. I could put more hardware on it but I don't think the method is efficient itself. How do you usually tackle problems like that?

$\endgroup$
  • $\begingroup$ what is the DB that is storing the data ? $\endgroup$ – MaxouMask Feb 5 '18 at 15:48
  • $\begingroup$ @MaxouMask : good point. edited my question above. $\endgroup$ – pawelty Feb 5 '18 at 15:50
2
$\begingroup$

FYI This isn't really a data science question, it's really related more to SQL and should be asked on those forums. But I'm going to try and help you anyways. This is something where you need to do matching by pronunciation using something like SOUNDEX in MySQL (I'm not aware of other RDBMS that offer this).

Overall, it's really something that requires an iterative approach where the first pass you're doing straight matches, the next pass you're doing matches with wildcards and then the next pass you use SOUNDEX. In theory, that should leave you with very few (if any) unmatched items for you to do manually.

|improve this answer|||||
$\endgroup$
  • 1
    $\begingroup$ I totally forgot about SOUNDEX for that problem. I recall using it for MS SQL before. I will do some testing. $\endgroup$ – pawelty Feb 5 '18 at 17:13
  • 1
    $\begingroup$ I have decided to keep using my wildcards. Then used DIFFERENCE function an only compared those where diff = 4. Again, thanks for your help. $\endgroup$ – pawelty Feb 7 '18 at 11:55
1
$\begingroup$

Ok,

Disclaimer : I have no knowledge about MS Sql.

  • Clean the name as you say
  • split each name into words
  • order the result by alphabetical order
  • create a key where the original name is associated to its ordered key
  • newly created key should also linked to the country
  • do this on both tables
  • you should know have a common key to find the appropriate ID_number based on the name and the country.
|improve this answer|||||
$\endgroup$
0
$\begingroup$

A solution can be, doing your process, and extract not matched cases. Then, split names on their words, and try to find their distance by Jaccard distance on their word set.

In sum, found these cases as an exception, and try to handle them using methods such as mentioned.

|improve this answer|||||
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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