I have two excel i want to find if row number 1 from one excel is present in my second exel or not.

pallav

I have two excel i want to find if row number 1 from first excel is present in my second excel or not. If not completely then by what percent it is matching . some thing like fuzzy matching in python .

Its is text "outlet name can be name of any outlet , address can be any address city state and zip . how we can see if that row is present in other excel or not . if not completely then by what percent it is matching

BalazsBarany

Hi@mschmitz,

the join operator in Database Envy supports inner joins on criteria that can be expressed between two values (column A from example set 1 and column B from example set 2) with an arbitrarily complex expression. If you can create one measure per example set, you can match them with a fuzzy expression, e. g.Math.abs(a - b) < 1.

In this case I would go with a similarity matrix, with character n-grams if there is a reason to assume that the words are not written in the same way.

Regards,
Balázs

sgenzer

嗨@pallavif you want a clear "is the row in the other sheet or not",I would simply do an inner join on the two tables on all the attributes and see if there are any examples coming out of the join. If you want a more fuzzy matching solution, I would concatenate all the text attributes into one attribute (using a space delimiter probably) and train a model to predict match/no match after text mining word vectorization.

MartinLiebig

Hi,

the other way would be to append the 2 data sets and use Remove Duplicates afterwards. Rows which are in both data sets are returned at the dup port.

Best,

Martin

yyhuang

Hi@pallav,

How about the similarity analytics? You can try the "cross distances" on separate data sets or "data to similarity" if you append two tables together. For strings like address/city/state/outlet name or zipcode, you may want to normalize all upper case letters to lower case and then apply "nominal to numerical" before calculating the cross-distance between the examples. Of course, we can do the nominal measurements. But you have more various formulas with numerical measurement to quantitatively define the difference.

operator doc
https://docs.www.turtlecreekpls.com/latest/studio/operators/modeling/similarities/cross_distances.html
previous discussions/knowledge base
https://community.www.turtlecreekpls.com/discussion/53879/cross-distance-how-is-it-calculated
https://community.www.turtlecreekpls.com/discussion/52140/two-documents-similarity-using-cross-distance

HTH!
YY

pallav

@yyhuang @mschmitz @sgenzer- The problem is the text might not match 100% in that case if i will do cross join remove duplicate and other technique it may not match .. In that case we have to do somethiing like how we do in python . Either tokenizing the word and later taking tfid vector of each word find the cosine similarity with other excel after doing same preprocessing , This we can do in python but how can we do same in rapidminer .

MartinLiebig

Hi@pallav,

i think we got three options here:
1. Use Process Documents to get TF/IDF, Cross Distances to get the cosine similarity, filter and join the original data.
2. Cross-Join the data, use Generate Levenshtein Distance
3. Use the DataBase Envy extension to do a non equal join. (I am not sure if this supports complex joins on fuzzy stuff).@BalazsBarany你能给一些建议吗?

Since you are also a customer, i would propose we do a quick call to walk you through? What times work better, European or East Coast work hours?

Best,
Martin

pallav

@mschmitz-I am good with European timing. We can arrange call now.