# Fuzzy match rows in one column **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [November 17, 2023, 8:34pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366 "2023-11-17T20:34:15Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![robvp](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/r/3bc359/32.png) [@robvp](https://forum.knime.com/u/robvp)\ **Post date:** [November 17, 2023, 8:34pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/1 "2023-11-17T20:34:15Z")
Hi, I have attached some example data and the expected outcome (second worksheet). I want to compare the string values in one column to see whether there’s similarities among them. Please note my original file has thousands of lines and a multitude or descriptions that I’m not really aware of. My goal is a first attempt to categorize/group similar lines together for me to further analyze the content of the file. What would be the best way for handle this? I already tried the STRING MATCHER and SIMILARITY RESEARCH whereby the source and comparing column are identical. But what happens is that it’s only picking up the exact same values. I want to check on similar things and not exact matches. [Recharge examples.xlsx](https://forum.knime.com/uploads/short-url/pyFCfCcD6ZSy5AJ1hbSnCJQteRK.xlsx) (10.6 KB) ---
**Author:** ![izaychik63](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/i/e9a140/32.png) [@izaychik63](https://forum.knime.com/u/izaychik63)\ **Post date:** [November 17, 2023, 9:20pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/2 "2023-11-17T21:20:52Z")
You can try > **[String Similarity — NodePit](https://nodepit.com/node/ws.palladian.nodes.helper.nlp.StringSimilarityNodeFactory)** > > Calculate similarities between strings. node. ---
**Author:** ![robvp](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/r/3bc359/32.png) [@robvp](https://forum.knime.com/u/robvp)\ **Post date:** [November 22, 2023, 2:33pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/3 "2023-11-22T14:33:02Z")
I tried that one, but I don’t know how this would help. I only have one column with data and I want to have that grouped somehow. The String similarity is comparing two columns, which I don’t have ☹ ---
**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\ **Post date:** [November 22, 2023, 4:26pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/4 "2023-11-22T16:26:42Z")
Hi @robvp You could take @izaychik63 idea and send the same data into string similarity and increase the neighbor count. Then you get more then just the same as matching br ---
**Author:** ![robvp](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/r/3bc359/32.png) [@robvp](https://forum.knime.com/u/robvp)\ **Post date:** [December 5, 2023, 7:56pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/5 "2023-12-05T19:56:36Z")
I can’t get it to work. I always get 1.0 as you can see in the screenshot ☹ ![image](https://forum-cdn.knime.com/uploads/default/original/3X/1/b/1bf1ca1e61e74c2c90f081467d6cef83ea12214d.png) ---
**Author:** ![Daniel\_Weikert](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/d/e47c2d/32.png) [@Daniel\_Weikert](https://forum.knime.com/u/Daniel_Weikert)\ **Post date:** [December 6, 2023, 4:34pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/6 "2023-12-06T16:34:39Z")
If you feed the same data twice you would always get 100% similarity for the same record. If you take more neighbors into account then you could filter out the 100% and take the second one br ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [December 6, 2023, 7:07pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/7 "2023-12-06T19:07:04Z")
@robvp I once created this workflow that would group addresses without a ground truth against which to match. Maybe you can adapt that. > [@How can I define and list the duplication in an adress data set sucessfully, with using String Distances node and Similarity Search node?](https://forum.knime.com/t/how-can-i-define-and-list-the-duplication-in-an-adress-data-set-sucessfully-with-using-string-distances-node-and-similarity-search-node/42568/12): > > @DAmei I had another look and I built a slightly over engineered workflow to try and get what you want combining some of the approached already mentioned. In your case there might not be a ground truth against wich to match but rather a lot of possible combinations. So configuration will be necessary and maybe exclude some obvious detaches in the first place. What the workflow does: the address is turned into a standardised string. The composition of the string will influence the matching. So… If you apply this it looks something like this: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/b/e/beb85d0728d489ea30736cc9d210d6a181af3142.png) You can edit the threshold which would constitute a similarity ([Similarity Search – KNIME Community Hub](https://hub.knime.com/knime/extensions/org.knime.features.distmatrix/latest/org.knime.distmatrix.similaritysearch.SimilaritySearchNodeFactory/)) and maybe also configure the method. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/a/0af77890ae9fa13495174f8b55f89060b9c6acc2.jpeg) If you set the threshold to 0.33 (instead of 0.25) the result would be this: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/a/d/ad66bce384209833f0e7ca8e7dfddeab7362b39d.png) What you could do would be to try change the order of the words so that similar words would have other positions. > **[String Deduplication without Ground Truth - KNIME Forum (75366) – mlauber71](https://hub.knime.com/mlauber71/spaces/Forum/2023/String%20Deduplication%20without%20Ground%20Truth%20-%20KNIME%20Forum%20(75366)~BjpzeM4_4SlKnA-u/current-state)** > > Match similar addresses from one list together into similar groups KNIME Forum (75366) https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/7?u=mlaub… [String Deduplication without Ground Truth - KNIME Forum (75366).knwf](https://forum.knime.com/uploads/short-url/bVMc5d6XdTdDNlT6n4NbusCpHt3.knwf) (192.9 KB) ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [January 8, 2024, 9:04am UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/8 "2024-01-08T09:04:56Z")
@robvp any thoughts on my suggestion/workflow? ---
**Author:** ![robvp](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/r/3bc359/32.png) [@robvp](https://forum.knime.com/u/robvp)\ **Post date:** [January 8, 2024, 9:19am UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/9 "2024-01-08T09:19:30Z")
Honestly I was overwhelmed by your workflow. I didn’t know where to start…My skills are not that advanced ☹ ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [January 9, 2024, 8:30pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/10 "2024-01-09T20:30:15Z")
@robvp I had hoped that the descriptions given in the workflow might have helped. If you want you can ask further questions or I can try to add more information. My impression was that this approach might be able to help you - if the task still is relevant. The examples could also be adapted for future tasks involving the matching of similar strings. ---
**Author:** ![izaychik63](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/i/e9a140/32.png) [@izaychik63](https://forum.knime.com/u/izaychik63)\ **Post date:** [January 10, 2024, 9:13pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/11 "2024-01-10T21:13:19Z")
I would recommend also to look on topics extraction lines of conversation, ---
**Author:** ![robvp](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/r/3bc359/32.png) [@robvp](https://forum.knime.com/u/robvp)\ **Post date:** [January 11, 2024, 2:00pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/12 "2024-01-11T14:00:53Z")
The task is obsolete in the mean time, but that doesn’t mean I don’t want to understand because it can benefit me in future situations. I’m always getting an empty table at the rule based row filter in the workflow. Logic in there is TRUE=\>FALSE, so it makes sense. Am I missing something? ![image](https://forum-cdn.knime.com/uploads/default/original/3X/8/9/891dc62855e6fe3797962f9b6c57133c06fba741.png) ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [January 11, 2024, 4:00pm UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/13 "2024-01-11T16:00:55Z")
> [@robvp](#): > > Logic in there is TRUE=\>FALSE, so it makes sense. Am I missing something? This is deliberate to create an empty table before the loop that would then store the intermediate results. ---
**Author:** ![robvp](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/r/3bc359/32.png) [@robvp](https://forum.knime.com/u/robvp)\ **Post date:** [January 12, 2024, 9:17am UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/14 "2024-01-12T09:17:42Z")
Ah, clear! I’m running it now, but in total it’s over 200.000 lines. Would there be a possiblity to speed things up because looking at the speed it might take more than a day… ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [January 12, 2024, 9:26am UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/15 "2024-01-12T09:26:06Z")
@robvp the problem could be that since this is an approach without a ground truth it will have to search all possible matches in each iteration (the ones not yet taken) so it will always need the whole pool of (remaining) options. If you would have a list of ground truths against which to match you could try to parallelise it. You might want to check general options how to speed up your KNIME experience: > **[Mastering KNIME: Unlocking Peak Performance with Expert Tips and Smart Settings](https://medium.com/low-code-for-advanced-data-science/mastering-knime-unlocking-peak-performance-with-expert-tips-and-smart-settings-49e2c0dab9cc)** > > The low-code tool KNIME is great and a lot of guides are out there to help you master it. You are encouraged to read the official ones… ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [April 11, 2024, 9:26am UTC](https://forum.knime.com/t/fuzzy-match-rows-in-one-column/75366/16 "2024-04-11T09:26:44Z")
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.