9

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".

  • 3
    The 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 Hunter Jun 26 '13 at 6:20
  • 2
    @DeerHunter : ah, the power of grad students and/or interns. – Joe Jun 26 '13 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 Kalin Jun 27 '13 at 5:43
  • 3
    Ian, 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 Hunter Jun 27 '13 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? – magdmartin Jun 28 '13 at 14:47
6
+50

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

  • Does OpenRefine offer any Geo Clustering options? For example my two data sets have roughly(but not exact) equal lat lon. – Dan Ciborowski - MSFT Jul 1 '13 at 13:40
  • 1
    @djc391 This comment would actually make an excellent new question. – Patrick Hoefler Jul 2 '13 at 16:06
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.