Anyone have a good way of comparing two large and unstructured lists (~2k entries each) for commonalities between them?

Question

Let's say, two governments have separate lists of reports, and I'm trying to figure out the most common elements between them. Think of one government administrator reporting # of sick days per year and another government administrator reporting number of employees that take sick days in a year --> query results that both governments care about "sick days".

Answer 1

Following the discussion in the comment section, I suggest that you have a look at OpenRefine. For a 4,000 rows dataset (two set of 2000 rows each) Refine allow a mix of manual and script cleaning (using fuzzy match). Here is the steps I will follow (based on what I understood) to clean this dataset:

Prepare your data

1. In a separate tool, merge the two set in a single document with a column for each source
2. Load the file in OpenRefine
3. Using the transpose function merge the two fields in one, in the windows option remember to tick append column name (so you can track from which source your data come from) and separate
4. Using the split function, split your new column based on the pipe |

So now you have a field with your source name and an field with your value, now we will be able to start to clean those value:

1. Invoke a text facet to list all the value available, and click cluster to do fuzzymatch comparison and search for similar record
2. Play with the different clustering algo (including Levenshtein, metophone) ... You have to manually select the right matches, discard the other, you are in total control of the algorithm. Do not hesitate to explore the different algorithms available, some are more conservative than other or will match on different parameters.
3. Once your done with the semi automatic clean up, finish the work manually using the facet windows to list all value available in your list. When you want to correct a value click the edit option in the facet windows to update all matching rows.
4. Once done export your data, there is various format available.

This is a very high level process and hopefully you will find it useful. If you want more details, you can explore this tutorial for the split and clustering function, or dig through existing step by step doc.

disclaimer, I am part of OpenRefine team

Answer 2

I would like to recommend an alternative that I have found that I prefer to open refine.

https://github.com/datamade/dedupe

It is a very easy to use python program, that provides many custom options to merge your data sets. You are able to decide how each field is compared. I like this feature, because I have some fields where I only want matches to occur when they are not equal, this field being source, and the values being doc1 or doc2. It handles lat/lon, string, and custom compares. The author is very quick to reply to any questions you might have.

The greatest thing I like is it allows you to help train the program, by taking samples of your data and asking you, is this a match? Yes, No, Unsure, or Finished

So even if you don't want to get into the code and make up your custom comparators your able to quickly train the program what you feel is a match or not.

Very easy to install using Python's pip

pip install "numpy>=1.9"
pip install dedupe

There are examples for how to use dedupe