Step 1: Install required libraries for this task¶

ONLY install once, next time, just run from Cell [2]¶

In [ ]:

## Run this cell to install the libraries needed
# you only need to install ONCE

!pip install fuzzywuzzy
!pip install python-Levenshtein

Step 2: Import necessary libraries¶

In [3]:

import pandas as pd
from fuzzywuzzy import fuzz

Step 3: before you start, please make sure the column is named 'company' in both Excel lists.¶

Edit the paths below, tell the programme which TWO excel lists you want to check against¶

In [2]:

## Bring in the 2 lists that you need to compare
## Turn the lists into 2 dataframes

system_list = pd.read_excel(r'C:\Users\hyyiu\Desktop\similar_names\companies_on_jb\test\system_companies.xlsx')
new_list = pd.read_excel(r'C:\Users\hyyiu\Desktop\similar_names\companies_on_jb\test\new_companies.xlsx')

In [3]:

## Preview the head rows of System List
system_list.head()

Out[3]:

	company
0	ALDI Services Asia Limited
1	Bank of China (Hong Kong) Limited
2	BASF East Asia Regional Headquarters Limited
3	CEI Conrad Electronic International (HK) Limited
4	Centre Testing International (Hong Kong) Compa...

In [4]:

## Preview the head rows of New List
new_list.head()

Out[4]:

	company
0	ALDI Sourcing Asia Limited
1	ALDI Sourcing Asia
2	ALDI Sourcing Asia Ltd
3	ALDI Sourcing Asia Ltd.
4	Bank Of China (Hong Kong) Ltd.

Step 4 - Edit the similarity ratio (out of 100%) you want below before running the cell. When it finishes, a new Excel file will be saved to the same folder path.¶

Please make sure the column name is always 'company' in both Excel lists.¶

In [5]:

## Please make sure the column name is always 'company' in both excel lists.

x = np.array(np.meshgrid(system_list['company'].values, new_list['company'].values)).T.reshape(-1,2)
checked_names_df = pd.DataFrame(x)

checked_names_df.columns = ['System List', 'New List']

checked_names_df['Ratio'] = [fuzz.ratio(*i) for i in map(tuple, x)]

print (checked_names_df[checked_names_df.Ratio > 80]) # <---- UPDATE THE RATIO YOU WANT HERE!

checked_names_df[checked_names_df.Ratio > 80].to_excel('results.xlsx') # <---- UPDATE THE RATIO YOU WANT HERE!

                                            System List  \
0                            ALDI Services Asia Limited   
271                   Bank of China (Hong Kong) Limited   
539        BASF East Asia Regional Headquarters Limited   
807    CEI Conrad Electronic International (HK) Limited   
1075  Centre Testing International (Hong Kong) Compa...   
1343                               CLP Holdings Limited   
1611                                  Cordis, Hong Kong   
1879                                           Deloitte   
2414                   Dezan Shira & Associates Limited   
2682                                                 EY   
3485         Freudenberg & Vilene International Limited   
3753                     HDI Global SE Hong Kong Branch   
4289                      Imtron Asia Hong Kong Limited   
5092                                     Kuehne & Nagel   
5627                Leighton Contractors (Asia) Limited   
5895                                 Mazars CPA Limited   
6163                    Mercedes-Benz Hong Kong Limited   
6431               OBI Group Sourcing Hong Kong Limited   
6698                      OBI Group Sourcing HK Limited   
6966                               SellerX Asia Limited   
7234                                    Siemens Limited   
7502                            Simba Dickie HK Limited   
7770              Techtronic Industries Company Limited   
8306                             TGI Technology Limited   
8574  The Hongkong and Shanghai Banking Corporation ...   
8842                    TÜV Rheinland Hong Kong Limited   

                                               New List  Ratio  
0                            ALDI Sourcing Asia Limited     81  
271                      Bank Of China (Hong Kong) Ltd.     89  
539           BASF East Asia Regional Headquarters Ltd.     94  
807   CEI Conrad Electronic International (Hong Kong...     88  
1075  Centre Testing International (Hong Kong) Co., ...     89  
1343                                  CLP Holdings Ltd.     86  
1611                                   Conrad Hong Kong     85  
1879                                           Deloitte    100  
2414                      Dezan Shira & Associates Ltd.     92  
2682                                                 EY    100  
3485            Freudenberg & Vilene International Ltd.     94  
3753                     HDI GLOBAL SE Hong Kong Branch     83  
4289                         Imtron Asia Hong Kong Ltd.     91  
5092                                Kuehne & Nagel Ltd.     85  
5627                Leighton Contractors (Asia) Limited    100  
5895                                    Mazars CPA Ltd.     85  
6163                       Mercedes-Benz Hong Kong Ltd.     92  
6431                  OBI Group Sourcing Hong Kong Ltd.     93  
6698                  OBI Group Sourcing Hong Kong Ltd.     81  
6966                               SellerX Asia Limited    100  
7234                                       Siemens Ltd.     81  
7502                               Simba Dickie HK Ltd.     88  
7770                     Techtronic Industries Co. Ltd.     84  
8306                                TGI Technology Ltd.     88  
8574  The Hongkong and Shanghai Banking Corporation ...     95  
8842                       TÜV Rheinland Hong Kong Ltd.     92

In [ ]: