# How to fix inconsistent (variable spelling) categorical data and “fill in” missing data

I am a newbie data science engr. My first challenge is to (1) normalize inconsistent values in categorical features and (2) fill any missing information.

To describe inconsistency lets say we have a country field and there can be multiple entries for USA. e.g. USA, United States of America, US, US-50, United States, America, etc.

How do I normalize all these entries and just say USA for all entries. I can think of some set of full text search rules to match against using lucene or something. Is there any specific technical term for this problem?

I'm also new to Data Science, so my approach may be laughably naive. But I work as a "terminology analyst" who prepares and maintains standardized vocabularies and code sets (which are meant to avoid the very problem you describe), so at least I'm describing things I've actually paid for. In fact, when my program was first established, a slide presentation called "The State of the State File" was prepared describing a problem very similar to yours: The file used to populate the data entry pick list of states contained hundreds of different ways the 50 US states could be represented in text.

To answer your question: I would call the name of the problem "unstructured data" or "unstandardized data." We call the process of fixing it "data standardization." The outcome of such standardization is a "reference terminology," which is a list of standardized terms as well as synonyms (aka "search terms") for each standard term. Sometimes alphanumeric codes are assigned to each standardized term, resulting in a "code set."

The approach my organization has taken to standardizing data is rudimentary and hardly qualifies as any sophisticated data science: we run a DISTINCT query to get all the different text strings used to represent concepts. Then that list needs to be reviewed to see what different text strings represent the same thing. If your list is too large to review each text string in the amount of time you have, then you need to get frequency data for the number of times each distinct text string occurs. Sort the list of terms from highest to lowest number of occurrences, and work your way down the list as far as you can in the time alotted.

Sounds simplistic, but sometimes you need to make judgement calls about equality or subsumption. For example, if one of your text strings is "the continental united states" (excluding Alaska and Hawaii) do you lump that under "USA" or does it get its own category?

Your result would be a reference terminology of a finite number of standardized country names, and all the synonyms for each country.

One place to start is ISO-3166, a set of codes "for the names of countries, dependent territories, special areas of geographical interest, and their principal subdivisions (e.g., provinces or states)." You may be able to match each of your text strings to one of these established codes.

• Thanks. I am going to start with building reference map as you suggested from static data analysis. May be later if required I will introduce fuzzy string matching on the fly based on @Brandon Loudermilk suggestion – nir May 31 '16 at 19:34