I have a data set of 700+ mil records with a feature that should yield good predictive power. The problem is that it has far more unique values than it should. The 10k+ unique values should map to about 150. I have that list of 150 values I want them to map to. Thinking about using a distance algorithm (levenshtein?) to map unique values from data to the desired set of values. What are some other ways to think about this problem?

Ex. 'Table', 'tab', 'tbl' should all map to 'table'. I'm not about to manually build a lookup table for this process given the volume of unique values. The unique values in the data are all derived from the desired values - they are acronyms or abbreviations.


I agree with the idea of using a similarity or distance measure (approximate string matching). I would try a bunch of them and test them on a sample: Levenshtein, Jaro, overlap coefficient or cosine (optionally with TF-IDF) over bi/tri-grams of characters.

I would also try to capture the most common abbreviations and have a lookup table for these common cases because:

  1. Computing similarity/distance measures takes time, so it's inefficient to compute the same result many times for the same string (and it's likely that some of these abbreviations are used many times).
  2. That gives you an opportunity to check that the mapping is correct (or to fix if it's not) at least for the most common cases, thus minimizing the overall amount of noise in the data.

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.