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".
Anyone have a good way of comparing two large and unstructured lists (~2k entries each) for commonalities between them?
3The quickest way is to have an intern compare the lists. In general, compiling a semantic concordance between two lists is not an easily automated task.– Deer HunterJun 26, 2013 at 6:20
2@DeerHunter : ah, the power of grad students and/or interns.– JoeJun 26, 2013 at 13:02
Thanks for the feedback. I also received two mail messages that had good ideas. One was to use the Fuzzy Lookup Macro within Microsoft Excel:microsoft.com/en-us/download/details.aspx?id=15011. Another idea was to use Hadoop's MapReduce.– Ian KalinJun 27, 2013 at 5:43
3Ian, you have to understand the math behind the code. Levenshtein (or any other) similarity measure between pairs of terms is all nice and may work in the case of different spellings, but it won't catch what a human will - mistranslations, variants in terminology, descriptive phrases etc. YMMV.– Deer HunterJun 27, 2013 at 15:44
how large are the string you want to compare and how are they stored? Are we talking about few words / tag, file name (like reportsickdays.pdf) or a free text field that describe the report?– magdmartinJun 28, 2013 at 14:47
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
- In a separate tool, merge the two set in a single document with a column for each source
- Load the file in OpenRefine
- 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
- 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:
- Invoke a text facet to list all the value available, and click cluster to do fuzzymatch comparison and search for similar record
- 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.
- 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.
- 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
Does OpenRefine offer any Geo Clustering options? For example my two data sets have roughly(but not exact) equal lat lon. Jul 1, 2013 at 13:40
1@djc391 This comment would actually make an excellent new question. Jul 2, 2013 at 16:06
I would like to recommend an alternative that I have found that I prefer to open refine.
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
This suite uses Affine Gap Distance metric. Jul 6, 2013 at 4:16
@DeerHunter, I am one of the authors of the library and we have added a number of other field comparators, including geographic distance, and jaccard set distance. github.com/open-city/dedupe/wiki/API-documentation#wiki-init– fgreggJul 16, 2013 at 21:27