Perform fuzzy lookup to partially match the address column

Question

I have two separate datasets (eg. dataset1.xlsx and dataset2.xlsx). Dataset1 has 2 columns, serial number and service address. Similarly dataset2 has 2 columns service address and customer number. The problem is that the address in both these datasets have spelling errors, for example one of the address in dataset 1 is 790 spring ln, york while the dataset 2 has the same address as 790 spring lane, york. So there is a difference in spelling in both columns. I am trying to match the address in dataset 1 with address in dataset 2. I want the above to addresses to match but because of the spelling error in the word lane it wouldn't match.

The dataset structure is as follows:
DATASET1:
S.NO| SERVICE ADDRESS
1| 101 SUTTON RD,ABBOTTSTOWN
2| 106 E KING ST,ABBOTTSTOWN
3| 430 W KING ST, YORK
4| 130 BEAVER AV,ALIQUIPPA
5| 2601 DUSS AV,AMBRIDGE

DATASET2
S.NO| SERVICE ADDRESS
1| 430 W KING STREET, YORK
2| 2601 DUSS AVENUE,AMBRIDGE
3| 130 BEAVER AV,ALIQUIPPA
4| 106 E KING ST,ABBOTTSTOWN
5| 101 SUTTON RD,ABBOTTSTOWN

When I match the service address columns from dataset 1 and dataset 2, I only get a match between row 4 of dataset 1 and row 3 of dataset 2. I am expecting to get a match between all the rows because they have similar addresses expect for some minute spelling errors.

Answer 1

This problem is called record linkage. This process usually involves comparing records using an approximate string similarity measure.

Here are a few related questions on the site that I'm aware of:

• About string similarity measures, you might find some info in these questions.
• About record linkage, I'm aware of these questions.

(Note that there are certainly many other questions/resources available, I just mentioned the ones I remember)

Answer 2

You can convert these strings into feature vectors using any regular NLP algorithm. You can then find the nearest-neighbour in dataset 2 for each address in dataset 1. You can define a cut-off beyond which the vectors are considered to be different.

Alternatively you can mix the unique addresses in both the datasets together and k-means-cluster them with k=half of the total number of records. This way you get a nearest neighbour for every unique address. You can use this map for further processing.

The first approach appears cleaner. Based on the vectorisation approach, you can try both Levenshtein or cosine similarities and see which one works better. Also bi-grams may work better here compared to unigrams for vectorisation

Answer 3

Python has a open source libarary known as fuzzywuzzy for approximate matching. Please refer to this blog for more details