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:stringName 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 americathe leland stanford junior university - stanford - ca - united states of americaleland stanford jr. university - stanford - ca - united states of americastanford university graduate school of business - stanford - ca - united states of americathe leland stanford junior university (stanford university) - stanford - ca - united states of americaleland stanford junior university - stanford - ca - united states of americastanford university - stanford -  -leland stanford jr. university, graduate school of business - stanford - ca - united states of americastanford law school - stanford - ca - united states of americastanford - stanford - ca - united states of americastanford university, graduate school of business - stanford - ca - united states of americastanford graduate school of business - stanford - ca - united states of americastanford univerity - stanford - ca - united states of americastanford university (the leland stanford junior university) - stanford - ca - united states of americathe leland stanford jr. university - palo alto - ca - united states of americaleland stanford junior university, school of law - stanford - ca / n/a - united states of americastanford universit - stanford - ca - united states of americathe leland stanford university - stanford - ca - united states of americaleland standford stanford junior university - stanford - ca - united states of americastanford university - cambridge - ma - united states of americathe leland stanford junior university 'stanford university' - stanford - ca - united states of americastanford university school of law - stanford - ca - united states of americastanford univresity - stanford - ca - united states of americathe leland stanford jr. university (stanford university) - stanford - ca - united states of americaleeland stanford junior university - stanford - ca - united states of americaleland stanford junion university -  - ca - united states of americaleland stanford junior university (stanford university) - stanford - ca - united states of americathe leland stanford junior university - stanford -  -stanford university - graduate school of business - stanford - ca - united states of americagraduate school of business, stanford university - stanford - ca - united states of americastanford universoty - stanford - ca - united states of americaleland stanford junior university - stanford -  -stanford univeristy - palo alto - ca - united states of americaleland stanford university - palo alto - ca - united states of americastanford university - stanford - ca / n/a - united states of americathe leland stanford junior university, stanford university - stanford - ca - united states of americathe leland stanford junior university graduate school of business - stanford - ca - united states of americastanford universtiy - stanford - ca - united states of americastanford univerisity - stanford - ca - united states of americastanford university - stanford - ct - united states of americastanford law scool - stanford - ca - united states of americamba: stanford university - stanford - ca - united states of americaThey 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?
Fixing data inconsistencies
data cleaning;similarity;distance
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, countrystanford law school - stanford - ca - united states of americaYou 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.