Levenshtein distance in the Addresses

Hi,

I have a data set joined on the IDs. I need to compate 2 cities fields(from different sources) and create flag based on the Levenshtein distance for those fields. If the distance < 3 then flag = 1, if the distance > 3 then flag = 0

Example, "Athena" and "Athens", Levenshtein distance = 1, so flag = 1
'ZURIH' and 'MUNICH', Levenshtein distance = 3, so flag = 0

Could you, please, advice me, what is the best way to implement it?

Thank you in advance!

Datasets

Fuzzy Match

Text Mining

Accepted answers

BenMoss

Hi @svergtanya, there is a tool in Alteryx that allows you to create the Levenshtein distance between values (the fuzzy match tool) but unfortunately there is no way of extracting the values out of the tool, instead you are given a % match score, so this maybe worth looking into.

However, regardless of the above, you could make use of Alteryx's ability to interact with Python to solve this problem, specifically there is a package titled "Levenshtein" that allows us to get the information you wish fairly easy.

The solution I have shared is based on you passing in a two column table, one column titled 'Left' and one column titled 'Right'. The script will then compare the values on a line by line basis, passing the output to anchor ''#1" coming out of the python tool.

You'll also need to install the "Levenshtein" package in Alteryx's python environment

from ayx import Alteryx
from Levenshtein import distance as lev
import pandas as pd

data = Alteryx.read("#1")

result = []

Right = data['Right'].tolist()

Left = data['Left'].tolist()

for i,k in zip(Right, Left):
  result.append(lev(i, k))

result = pd.DataFrame({'Distance': result})

Alteryx.write(result,1)

As mentioned the example workflow is attached. It's then a case of using a formula tool to create your new flag field...

IF [Distance] >= 3 THEN 1 ELSE 0 ENDIF

Ben

Example Distance.yxmd

All comments

BenMoss

You'll also need to install the "Levenshtein" package in Alteryx's python environment

from ayx import Alteryx
from Levenshtein import distance as lev
import pandas as pd

data = Alteryx.read("#1")

result = []

Right = data['Right'].tolist()

Left = data['Left'].tolist()

for i,k in zip(Right, Left):
  result.append(lev(i, k))

result = pd.DataFrame({'Distance': result})

Alteryx.write(result,1)

As mentioned the example workflow is attached. It's then a case of using a formula tool to create your new flag field...

IF [Distance] >= 3 THEN 1 ELSE 0 ENDIF

Ben

Example Distance.yxmd

philipmannering

You can also do it like this,

from ayx import Alteryx
from Levenshtein import distance

# Read in dataframe
df = Alteryx.read("#1")

# Apply Levenshtein distance
df['Levenshtein'] = df.apply(lambda x: distance(x['Left'], x['Right']), axis=1)
df['Flag'] = df['Levenshtein'] >= 3

# Write out dataframe
Alteryx.write(df, 1)

svergtanya

Hi Ben,

Thank you for such detailed response.
I have not used Python before in Alteryx, so it took me quite some time to install the package, as had issues with SSL and python version.

Best regards, Tetiana

simonaubert_bd

Hello @svergtanya
Please note there is also an idea to make it native in formula

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Levenshtein-and-Jaro-Winkler-Distances-for-formula/idc-p/768853#M15659

Best regards,

Simon

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703