5
$\begingroup$

I am cleaning a database and I need to clean also the location field. The data is taken from a forum, so the entrance are self compiled. For this reason I have a lot of weird and different stuff going on here and I don't know how to get rid of them, especially of redundancies.

To give you an idea my list is really big: 4 millions entries, but for my purposes it can be also reduced a bit. This is the link to a short version with 10000 entries (https://ufile.io/y4akb) and this the one with all the 4 million entries (https://ufile.io/9ecxl). I already tried the function Interpreter or to work around it with GeoPosition, but apparently is no use, or at least I takes too much computational time and memory.

Can someone suggest a good approach to this? I would accept also answers which involves other softwares like R or/and SQLite. I hope someone from the communities can actually help me solving this. I am not uploading my codes because it is not really helpful and might be misleading because I am still a beginner in Mathematica.

My optimal result would be a list with only the name of the countries. Maybe also as strings, so it doesn't suck up so much memory.

Thank you in advance for your help!

--- EDIT TO CLARIFY DESIRED OUTPUT ---

Here it is an example of the Dataset I am working on: Initial Dataset to edit

And the final result I would like to achieve is the following: Final result aimed

POSSIBLE APPROACH

Using all the tips is the comments a possible approach I naively thought is to create a list with the locations and edit them with a nested If cycle like the following, and once I have the correct list, just to plug it in back as a column. In case someone has a better code, or a smarter way to deal with ALL the possible cases would be super happy and interested in his/her approach. Meanwhile, here it is the code: 

Location = Table[
   If[
    StringContainsQ[LongTableAnswerLocation[[i]], "Poland"], 
    "Poland",
    If[
     StringContainsQ[LongTableAnswerLocation[[i]], "Pakistan"], 
     "Pakistan",
     If[
      StringContainsQ[LongTableAnswerLocation[[i]], "Brazil"], 
      "Brazil",
      If[
       StringContainsQ[LongTableAnswerLocation[[i]], "Netherlands"], 
       "Netherlands",
       If[
        StringContainsQ[LongTableAnswerLocation[[i]], "Iran"], 
        "Iran",
        If[
         StringContainsQ[LongTableAnswerLocation[[i]], "Ireland"], 
         "Ireland",
         If[
          StringContainsQ[LongTableAnswerLocation[[i]], "Japan"], 
          "Japan",
          If[
           StringContainsQ[LongTableAnswerLocation[[i]], "Spain"], 
           "Spain",
           If[
            StringContainsQ[LongTableAnswerLocation[[i]], "Turkey"], 
            "Turkey",
            If[
             StringContainsQ[LongTableAnswerLocation[[i]], "Sweden"], 
             "Sweden",
             If[

              StringContainsQ[LongTableAnswerLocation[[i]], 
               "Bangladesh"], "Bangladesh",
              If[

               StringContainsQ[LongTableAnswerLocation[[i]], 
                "Australia"], "Australia",
               If[

                StringContainsQ[LongTableAnswerLocation[[i]], 
                 "France"], "France",
                If[

                 StringContainsQ[LongTableAnswerLocation[[i]], 
                  "China"], "China",
                 If[

                  StringContainsQ[LongTableAnswerLocation[[i]], 
                   "Russia"], "Russia",
                  If[

                   StringContainsQ[LongTableAnswerLocation[[i]], 
                    "Canada"], "Canada",
                   If[

                    StringContainsQ[LongTableAnswerLocation[[i]], 
                    "Germany"], "Germany",
                    If[

                    StringContainsQ[LongTableAnswerLocation[[i]], 
                    "United Kingdom" | "UK"], "United Kingdom",
                    If[

                    StringContainsQ[LongTableAnswerLocation[[i]], 
                    "India"], "India",
                    If[

                    StringContainsQ[LongTableAnswerLocation[[i]], 
                    "AL" | "AK" | "AZ" | "AR" | "CA" | "CO" | "CT" | 
                    "DE" | "FL" | "GA" | "HI" | "ID" | "IL" | "IN" | 
                    "IA" | "KS" | "KY" | "LA" | "ME" | "MD" | "MA" | 
                    "MI" | "MN" | "MS" | "MO" | "MT" | "NE" | "NV" | 
                    "NH" | "NJ" | "NM" | "NY" | "NC" | "ND" | "OH" | 
                    "OK" | "OR" | "PA" | "RI" | "SC" | "SD" | "TN" | 
                    "TX" | "UT" | "VT" | "VA" | "WA" | "WV" | "WI" | 
                    "WY" | "AS" | "DC" | "FM" | "GU" | "MH" | "MP" | 
                    "PW" | "PR" | "VI" | "New York" | "USA"], 
                    "United States"
                    , LongTableAnswerLocation[[i]]]
                    ]]]]]]]]]]]]]]]]]]],
   {i, Length@LongTableAnswerLocation}];
Improve this question
$\endgroup$
8
  • 1
    $\begingroup$ you can perfectly well post a dozen or so entries in plain text (along with desired output) to illustrate what you are talking about. $\endgroup$
    – george2079
    May 8, 2018 at 14:58
  • $\begingroup$ Sure, just a second $\endgroup$
    – Carmine Ragone
    May 8, 2018 at 15:14
  • $\begingroup$ I would begin by obtaining a list of countries and a list of us states and state codes, then developing a comparison algorithm. Beware any use of mathematicas geodata tools will be slow as molasses. (simply getting names as #["Name"] & /@ EntityList["Country"] ) takes like a half hour and times out half the time ) $\endgroup$
    – george2079
    May 8, 2018 at 15:58
  • $\begingroup$ Thanks for the hint. I hope that someone more experienced than me could help building this comparison algorithm and get rid also of non sense like dashes or symbols. $\endgroup$
    – Carmine Ragone
    May 8, 2018 at 16:38
  • $\begingroup$ @CarmineRagone Here is a quick hint. data = Sort[DeleteDuplicates[Flatten[Import["locationdatatoclean.csv", "CSV"]]]]; to view Dataset[data] reduces the cross-walk you wish to build from 4 million to 46,000. There is still junk in this so I would suggest, lowercasing and standardizing "comma no space" to "comma space" or vice-versa; and "period spaces". Remove numeric. I believe this list could be reduced even further. I hope you learned from my answer to your previous post. Did you use Merge? $\endgroup$
    – Hans
    May 8, 2018 at 17:02

2 Answers 2

Reset to default
1
$\begingroup$

just to get started something like this is I think will be the fastest approach

data = {"Scotland,United Kingdom", "United Kingdom", "Minnesota", 
   "San Francisco,CA", "Australia", "Australia", "United States", 
   "Layton,UT", "Madison,WI", "Jacksonville,FL", "Seattle,WA", 
   "Canada", "Chicago,IL", "Pittsburgh,PA", "Hamburg,Germany", 
   "Oregon", "Albany,NY"};

obviously incomplete lists..:

statecodes = {"CA", "UT", "WI", "FL", "WA", "IL", "PA", "NY"};
states = {"Minnesota", "Oregon"}
countries = {"United Kingdom", "Australia", "United States", 
  "Germany", "Canada"}

you will probably do best to just web search and find suitable lists.

SetAttributes[classify, Listable]
classify[s_String] := Which[
  Length@Intersection[
     StringCases[
      s, __ ~~ "," ~~ Whitespace ... ~~ 
        c : (LetterCharacter ~~ LetterCharacter) ~~ EndOfString :> c],
      statecodes] == 1 , "United States" ,
  Length[c = Select[states, StringContainsQ[s, #] &]] == 1, 
  "United States", 
  Length[c = Select[countries, StringContainsQ[s, #] &]] == 1, c[[1]],
   True , "(Unknown)" <> s]

classify[data]

{"United Kingdom", "United Kingdom", "United States", "United States", "Australia", "Australia", "United States", "United States", "United States", "United States", "United States", "Canada", "United States", "United States", "Germany", "United States", "United States"}

Improve this answer
$\endgroup$
0
$\begingroup$

Going by the title you chose and the ask for even an SQLite (SQL) solution or hint; I came up with the following:

dataraw = 
  Sort[DeleteDuplicates[
    Flatten[Import["locationdatatoclean.csv", 
      "CSV"]]]];
(* dataraw=Sort[DeleteDuplicates[Flatten[Import["locationdatatocleanshort.csv","CSV"]]]]; *)
data = DeleteCases[
   DeleteDuplicates[
    Map[If[Not[NumericQ[#]], 
       StringTrim[
        StringReplace[
         RemoveDiacritics[ToLowerCase[#]], {"  " -> " ", ", " -> ",", 
          "." -> " ", " ," -> ",", "\\" -> ",", "/" -> ",", 
          ", " -> ","}]]] &, dataraw]], Null];
iso31661world = 
  Quiet[AppendTo[
    AppendTo[
     MapAt[StringReplace[
        RemoveDiacritics[#], {", plurinational state of" -> "", 
         ", the democratic republic of the" -> "", 
         "czech republic" -> "czech", 
         ", democratic people's republic of" -> ", north", 
         "holy see (vatican city state)" -> "vatican", 
         ", islamic republic of" -> "", 
         "lao people's democratic republic" -> "laos", 
         ", federated states of" -> "", ", republic of" -> ", south", 
         ", state of" -> "", "russian federation" -> "russia", 
         " (french part)" -> "", " (dutch part)" -> "", 
         ", the former yugoslav republic of" -> "", 
         "syrian arab republic" -> "syria", 
         ", united republic of" -> "", ", province of china" -> "", 
         "viet nam" -> "vietnam", 
         "united states minor outlying islands" -> "outlying islands",
          "british indian ocean territory" -> "ocean territory", 
         "south georgia and the south sandwich islands" -> 
          "sandwich islands"}] &, 
      ToLowerCase[
       Part[Import[
         "http://www.unece.org/cefact/locode/countries.html", {"HTML",
           "Data"}], 3, 3, 2]], {All, 2}], {"ni", 
      "northern ireland"}], {"uk", "united kingdom"}]];
iso31662usraw = 
  ToLowerCase[{{"AL", "Alabama"}, {"AK", "Alaska"}, {"AZ", 
     "Arizona"}, {"AR", "Arkansas"}, {"CA", "California"}, {"CO", 
     "Colorado"}, {"CT", "Connecticut"}, {"DE", "Delaware"}, {"FL", 
     "Florida"}, {"GA", "Georgia"}, {"HI", "Hawaii"}, {"ID", 
     "Idaho"}, {"IL", "Illinois"}, {"IN", "Indiana"}, {"IA", 
     "Iowa"}, {"KS", "Kansas"}, {"KY", "Kentucky"}, {"LA", 
     "Louisiana"}, {"ME", "Maine"}, {"MD", "Maryland"}, {"MA", 
     "Massachusetts"}, {"MI", "Michigan"}, {"MN", "Minnesota"}, {"MS",
      "Mississippi"}, {"MO", "Missouri"}, {"MT", "Montana"}, {"NE", 
     "Nebraska"}, {"NV", "Nevada"}, {"NH", "New Hampshire"}, {"NJ", 
     "New Jersey"}, {"NM", "New Mexico"}, {"NY", "New York"}, {"NC", 
     "North Carolina"}, {"ND", "North Dakota"}, {"OH", "Ohio"}, {"OK",
      "Oklahoma"}, {"OR", "Oregon"}, {"PA", "Pennsylvania"}, {"RI", 
     "Rhode Island"}, {"SC", "South Carolina"}, {"SD", 
     "South Dakota"}, {"TN", "Tennessee"}, {"TX", "Texas"}, {"UT", 
     "Utah"}, {"VT", "Vermont"}, {"VA", "Virginia"}, {"WA", 
     "Washington"}, {"WV", "West Virginia"}, {"WI", 
     "Wisconsin"}, {"WY", "Wyoming"}, {"DC", "District of Columbia"}}];
iso31662caraw = 
  ToLowerCase[{{"AB", "Alberta"}, {"BC", "British Columbia"}, {"MB", 
     "Manitoba"}, {"NB", "New Brunswick"}, {"NL", 
     "Newfoundland and Labrador"}, {"NS", "Nova Scotia"}, {"ON", 
     "Ontario"}, {"PE", "Prince Edward Island"}, {"QC", 
     "Quebec"}, {"SK", "Saskatchewan"}, {"NT", 
     "Northwest Territories"}, {"NU", "Nunavut"}, {"YT", "Yukon"}}];

So we have the raw 4 million user entered data points. Can be quickly cleaned by using DeleteDuplicates[] does exactly what is in your title getting rid of redundancies. With a few key characters removed the list can be reduced to ~41,100. The lookup table generation is a bit bulky but I went with ISO3166(1 and 2) with the 2 letter codes. 

Clear[findLocation, findmatch, findmatchall, findbycode];

findmatch[z_] := 
  StringCases[
   z, {iso31662usraw -> "united states", iso31662caraw -> "canada", 
    w : iso31661world :> 
     iso31661world[[All, 2]][[Flatten[
        Position[StringContainsQ[iso31661world[[All, 1]], w], 
         True]]]]}, Overlaps -> All];
findbycode[u_] := Select[u, (StringLength[#] == 2) &];
findmatchall[a_] := 
  Module[{r, s}, 
   r = StringCases[
     a, {iso31662usraw[[All, 2]] -> "united states", 
      iso31662caraw[[All, 2]] -> "canada", 
      w : iso31661world[[All, 2]] :> 
       iso31661world[[All, 2]][[Flatten[
          Position[StringContainsQ[iso31661world[[All, 2]], w], 
           True]]]]}, Overlaps -> All];
   r = DeleteDuplicates[Flatten[r]];
   If[(Length[r] == 0 ), s = findbycode[a]; r = findmatch[s]];
   DeleteDuplicates[Flatten[r]]
   ];
findLocation[x_?StringQ] := Module[{wl}, wl = StringSplit[x, {","}];
   If[(StringLength[StringTrim[x]] > 1), 
    If[(StringLength[StringTrim[x]] == 2), findmatch[x], 
     findmatchall[wl]], "Junk"]];

Then just execute

Map[Function[{#, findLocation[#]}], data]

snip of result

{"cheshire,stockport,united kingdom", {"united kingdom"}}, \ {"cheshire,uk", {"united kingdom"}}, {"cheshire uk", {}}, \ {"cheshire,united kingdom", {"united kingdom"}}, {"cheshire,united \ kngdom", {}}, {"cheshunt,united kingdom", {"united kingdom"}}, \ {"chesire", {}}, {"chessington", {}}, {"chester", {}}, {"chester,ar", \ {"united states", "argentina"}}, {"chesterbrook,pa", {"united states", "panama"}}, {"chester,ct usa", {}}, {"chester,england,united \ kingdom", {"united kingdom"}}, {"chesterfield,england", {}}, \ {"chesterfield,mi", {"united states"}}, {"chesterfield,michigan,usa", \ {"united states"}}, {"chesterfield,mo", {"united states", "macao"}}, {"chesterfield,mo,united states", {"united states"}}, \ {"chesterfield uk", {}}, {"chesterfield,united kingdom", {"united \ kingdom"}}, {"chesterfield,va", {"united states", "vatican"}}, {"chester,md", {"united states", "moldova, south"}}, {"chestermere,canada", {"canada"}}, {"chester \ springs,pa", {"united states", "panama"}}, {"chestertown,md", {"united states", "moldova, south"}}, {"chester,uk", {"united kingdom"}}, {"chester \ uk", {}}, {"chester,united kingdom", {"united kingdom"}}, \ {"chester,va", {"united states", "vatican"}}, {"chestnut hill,ma", {"united states", "morocco"}}, {"chestnut ridge,ny", {"united states"}}, \ {"chetek,wi", {"united states"}}, {"chetpet,chennai,tamil \ nadu,india", {"india"}}, {"chetumal,mexico", {"mexico"}},

With lots of room for improvement you can feed the result and used near neighbor and Levenshtein algorithms to fill in the locations that are blank. Sometimes they are off by spelling, no comma, or just the city name. Some 2 letter codes overlap so which one should win? In SQL I would use "SELECT DISTINCT column_name FROM DataTable ..." does similar to DeleteDuplicates. As for the non-English characters I can't help there.

Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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