Fixing data inconsistencies

Question

I'm trying to analyze some data I have but there is a lot of inconsistencies in my data.

I have a SQL table that I'm trying to analyze.

The table is a table of universities with the following structure: name:string, city:string, state:string, country:string Name is always present however city, state, country can be missing. My main issue is that there are a ton of typos and different declination of a university name. For example here are the declination of Standford Unversity I find when I do SELECT "universities".* FROM "perm_universities" WHERE (name like '%stanford%'):

stanford university - stanford - ca - united states of america
the leland stanford junior university - stanford - ca - united states of america
leland stanford jr. university - stanford - ca - united states of america
stanford university graduate school of business - stanford - ca - united states of america
the leland stanford junior university (stanford university) - stanford - ca - united states of america
leland stanford junior university - stanford - ca - united states of america
stanford university - stanford -  -
leland stanford jr. university, graduate school of business - stanford - ca - united states of america
stanford law school - stanford - ca - united states of america
stanford - stanford - ca - united states of america
stanford university, graduate school of business - stanford - ca - united states of america
stanford graduate school of business - stanford - ca - united states of america
stanford univerity - stanford - ca - united states of america
stanford university (the leland stanford junior university) - stanford - ca - united states of america
the leland stanford jr. university - palo alto - ca - united states of america
leland stanford junior university, school of law - stanford - ca / n/a - united states of america
stanford universit - stanford - ca - united states of america
the leland stanford university - stanford - ca - united states of america
leland standford stanford junior university - stanford - ca - united states of america
stanford university - cambridge - ma - united states of america
the leland stanford junior university 'stanford university' - stanford - ca - united states of america
stanford university school of law - stanford - ca - united states of america
stanford univresity - stanford - ca - united states of america
the leland stanford jr. university (stanford university) - stanford - ca - united states of america
leeland stanford junior university - stanford - ca - united states of america
leland stanford junion university -  - ca - united states of america
leland stanford junior university (stanford university) - stanford - ca - united states of america
the leland stanford junior university - stanford -  -
stanford university - graduate school of business - stanford - ca - united states of america
graduate school of business, stanford university - stanford - ca - united states of america
stanford universoty - stanford - ca - united states of america
leland stanford junior university - stanford -  -
stanford univeristy - palo alto - ca - united states of america
leland stanford university - palo alto - ca - united states of america
stanford university - stanford - ca / n/a - united states of america
the leland stanford junior university, stanford university - stanford - ca - united states of america
the leland stanford junior university graduate school of business - stanford - ca - united states of america
stanford universtiy - stanford - ca - united states of america
stanford univerisity - stanford - ca - united states of america
stanford university - stanford - ct - united states of america
stanford law scool - stanford - ca - united states of america
mba: stanford university - stanford - ca - united states of america

They are all the same university, but some have typos, some have different names, some have no cities, some have the wrong cities, ... The data isn't great.

So I'm trying to fix it. How can I consolidate this data?

Look up [record linkage](https://en.wikipedia.org/wiki/Record_linkage) to learn about how you can deal with partially overlapping records. You can handle spelling mistakes by [similarity searching](https://en.wikipedia.org/wiki/Similarity_search) the [n-gram](https://en.wikipedia.org/wiki/N-gram) bitstrings. — Emre, Apr 08 '16 at 01:54
I've voted to close as "too broad" since there are literally hundreds of possible approaches and you've not even told us you've tried one. Fuzzy matching, keyword matching, clustering, machine learning... — Spacedman, Apr 08 '16 at 06:52
@Spacedman I haven't tried anything yet to fix it since I don't know what are the best options. — bl0b, Apr 08 '16 at 12:29
I think this is a legitimate question; the problem is which approach (out of many existing ones) would be best to handle this problem? Given the number of potential matches, it may be better to try clustering than any type of fuzzy/approximate matching. However, I'd like to hear other's opinions. — Antonio, Apr 11 '16 at 20:35

score 3 · Accepted Answer · answered May 09 '16 at 06:27

Since this dataset is already organised in a table, you can leverage standard SQL functions to perform a large part of the cleanup. A record seems to be composed of 4 fields, for example:

university name, city, state, country
stanford law school - stanford - ca - united states of america

You could follow these steps to get a cleaner representation of this dataset:

Starting with the highest level (country), find unique values use it to identify all similar sounding words by listing soundex matches with itself (build a join query of the table with itself).
Use these suggested similar matches to fix all mistakes by updating the names.
Continue in this manner till you've fixed all four fields.
Identify missing states by using the city name to query the correct state from the rest of the table; if state and country are missing for "leland stanford junior university", then use the city name "stanford" to join it with itself and get the state/country name from the other records in the table.
For the university name, identify all abbreviations using grep to search for words ending in a dot character. Replace them with full expansions.
Next, breakup the university name into individual words and dump these into a temporary table in a single column. De-duplicate the values in the column so it only contains unique values.
Run the same soundex matching join query as in step 1 to identify similar sounding names, append these suggested similar names in a second column of the temp table.
Manually do a quick sanity check of what you've obtained as suggestions and delete any invalid matches.
Write a procedure to replace the words in each university name with suggested replacements, and you will have a much cleaner dataset.

score 1 · Answer 2 · answered Apr 11 '16 at 23:03

This is quite difficult to do without first structuring your dataset. There's a reason cleaned datasets cost thousands of dollars because they try to clean these issues for you.

What you can try is first creating a taxonomy system. First you give the general "Stanford university" an ID of "1". Something like the "Stanford graduate school of business" would get ID "1.2.5", where the new "2" refers to the graduate school division, "5" refers to the business school category. It really depends on what your ultimate goal is. In short, set up a list of ID's for possible subdivisions, "Graduate, undergraduate, etc." and then further subdivisions.

For locations, usually you can define a "primary" and "secondary" location, i.e. "Palo Alto" and "Stanford" , which you can determine by histogramming counts for each and selecting the top two.

For correcting misspellings, you could use google search API and exploit the "Showing results for..." which would give you the correct spelling.

Fixing data inconsistencies

2 Answers2