Similarity Check within a column

[Juliane]

Hi, I would like to find similar URLs within a column and mark them as duplicates (e.g. by creating a new column with “Duplicate”). I have tried string similarity, but here two columns are compared. I would like to have the check within one column.

A classic example would be two URLs once with / and without / at the end. Or the URL path was minimally adjusted with an additional pronoun and a duplicate was created.

Does anyone have any idea how I can solve this?

[qqilihq]

Hi Juliane,

If you have them within a single column, you’ll need to do a cross join first to build pairs which you can then compare:

Cross Joiner — NodePit

-Philipp

[mlauber71]

@Juliane welcome to the KNIME forum. You could try this example without a ground truth. You will have to see if it works for URLs.

Fuzzy Matching on One Column to then form Group IDs

@qdmt you could try and adapt this example: It does work without a ground truth but just will try to group the Term you create into distinctive groups based on the items you put into the string (called “Description”) … [image] … and then the threshold you set for matches (0.25). [image] In the end if possible each items will either be in a group or will be its own group: [image]

[Juliane]

@qqilihq thanks for the hint. I already tried something like this, but what would you do next (I tried Similarity Search, but then I can’t filter them out of the list…).

[Juliane]

Hi, I have downloaded the workflow, but unfortunately I still don’t understand how to apply it to my case… I have a list with over 1000 URLs and sometimes up to 3 or 4 similar URLs that I would like to filter out or possibly merge… I don’t quite understand which part of your workflow can help me.

[qqilihq]

I would do the following:

1. Build pairs as described above
2. Use e.g. String Similarity or Column Distance node
3. Determine suitable distance / similarity metric
4. Apply a threshold above which pairs are considered “duplicates”

[tomljh]

You can try clustering algorithms, such as DBScan. The distance used in the clustering algorithm can try the Edit Distance（ Levenshtein Distance）.

[mlauber71]

@Juliane it allows for a deduplication without a ground truth so it will group similar items within one list. I thought this might be similar to your case.