Search code examples
rcitystringdistfuzzyjoin

Standardize the City Name in R

I am new in R and coding world, pardon if i perhaps mispelled some or more jargon here (cmiiw).

I am facing a challenge to clean city name in a dataframe.

enter image description here

Tried to use GetCloseMatches, strdist_inner_join (with fuzzywuzzy i believe) with dplyr style but still haven't meet my needs.

1st attempt:

vec3 = unlist(world.cities$name)

str1 = c('Jakarta Utara')

GetCloseMatches(string = str1, sequence_strings = vec3, n = 1L, cutoff = 0.6)

but it can only "translate" one of city each time, do you know how to make it repeat for all of the dataframe? for loop or function?

2nd attempt:

df2 <- df[1:10,] %>%

stringdist_left_join(world.cities, by = c(cust_city = "name"), max_dist = 1)

it shows most of the city but missing the "Jakarta Utara"

I am using two database/dataframe(cmiiw) of the city to be checked with (If you see the "Look Up" table on the right side, it has hundreds of city name, not only 6), first is SHP files that i fortified, second is world.cities$name, both are doing great but somehow it only appear one city at a time. ie: if i am using SHP files, Jakarta Utara is appear but Karawang is not, vice versa.

My Goal is to replace the left word to the right word (1 to 2)

enter image description here

left > right

Karawang - to Karawang

Jakarta Utara to Jakarta

Jakarta to Jakarta, etc

Do you know the most efficient way to do it?

Thank you very much for your helps!

regards

Solution

  • if I understand your question right, you want to parse the City variable against a list of known City names and replace the longer City name with the version on the known City name list. Right? If yes, then hopefully this approach will work for you (no additional packages needed):

    # replicate your example data 1
d <-data.frame("No"=c(1,2,3,4,5),"Name"=c("Alex","Bambang","Charlie","Delta","Emily"),"Qty"=c(10,5,15,10,5),"City"=c("Jakarta Barat","Jakarta","Nagoya Batam","Bintaro Tangerang Selatan","Tendean Jakarta Selatan 11750"))
# replicate your vector of known city names
city_list <- c("Jakarta","Bandung","Batam","Surabaya","Tangerang Selatan")

# making a new placeholder variable to store the matched city names.
d$City_fix <- NA

# use a for loop, ifelse(), and grepl() to go through the vector of known cities, and replace the city name when a match is found.
for (i in 1:length(city_list)){
  d$City_fix <- ifelse(grepl(city_list[i], d$City), city_list[i], d$City_fix)
}

# view results
d

  No    Name Qty                          City          City_fix
1  1    Alex  10                 Jakarta Barat           Jakarta
2  2 Bambang   5                       Jakarta           Jakarta
3  3 Charlie  15                  Nagoya Batam             Batam
4  4   Delta  10     Bintaro Tangerang Selatan Tangerang Selatan
5  5   Emily   5 Tendean Jakarta Selatan 11750           Jakarta

    Using a vector of known cities will allow you to as many cities as you want for the loop. Just be wary if you have two cities that might share parts of the same city name, e.g. "Big City" and "New Big City".

    If your City names in the source dataframe or the list of cities have different lower & upper cases, you'll want to get that fixed first (e.g. tolower()from base R or str_to_title() from the stringr package).

    The solution above also requires the city names in the dataframe are spelled correctly. If you have spelling errors, e.g. Jakerta instead of Jakarta, then a more complex solution is needed.

    (edited to include mention ability to have a long list of known city names)