pattern matching, replacement and for loop optimization in R 0
down vote
favorite

I have two data frame loc_df and and city_df (city and country) now loc_df has 5 column but considering only 2 here (Organization.Location.1 and Organization.Location.2) with 35000 row and city_df has 2 column (city and country) with 1000 rows. Now I am taking one value from city cloumn and matching with organisation column using grepl (for text matching ) and for loop(for iteration). I also have to maintain a index that's why I am using for loop. But this is taking huge amount of time.

I am trying to replace each city, state, province name to their country name in organization columns.

Please help me to optimize this code. I am very new to R.

for(k in 1:2){
  if(k==1){

    for (i in 1:nrow(city_df)) {
      x1 &lt;- paste(" ", city_df$City[i], sep = "")
      x2 &lt;- paste(" ", city_df$City[i], " ", sep = "")
      x3 &lt;- paste(city_df$City[i], " ", sep = "")
      # print(x1)

      for (j in 1:nrow(loc_df)) {
        #print(loc_df$Organization.Location.1[j])


        if (grepl(x1, loc_df$Organization.Location.1[j]) |
            grepl(x2, loc_df$Organization.Location.1[j]) |
            grepl(x3, loc_df$Organization.Location.1[j])) {
            loc_df$org_new1[j] &lt;- city_df$Country[i]
          break

        }

      }
    }
  }
  if(k==2){

    for (i in 1:nrow(city_df)) {
      x1 &lt;- paste(" ", city_df$City[i], sep = "")
      x2 &lt;- paste(" ", city_df$City[i], " ", sep = "")
      x3 &lt;- paste(city_df$City[i], " ", sep = "")


      for (j in 1:nrow(loc_df)) {

        if (grepl(x1, loc_df$Organization.Location.2[j]) |
            grepl(x2, loc_df$Organization.Location.2[j]) |
            grepl(x3, loc_df$Organization.Location.3[j])) {
            loc_df$org_new1[j] &lt;- city_df$Country[i]
          break

        }

      }
    }
  }

}


this is sample data I have generated using dput of city_df

structure(list(City = c("zug", "canton of zug", "zimbabwe", 
                                  "zigong  chengdu", "zhuhai  guangdong  china", "zaragoza  spain"), Country = c("switzerland", 
                                                                                       "switzerland", "zimbabwe", "china", "china", "spain"
                                  )), .Names = c("City", "Country"), row.names = c(NA, 6L), class = "data.frame")


sample of loc_df

structure(list(Organization.Location.1 = c("zug  switzerland", 
"zug  canton of zug  switzerland", "zimbabwe", "zigong  chengdu  pr china", 
"zhuhai  guangdong  china", "zaragoza  spain"), Organization.Location.2 = c("", 
"san francisco bay area", "london  canada area", "beijing city  china", 
"greater atlanta area", "paris area  france")), .Names = c("Organization.Location.1", 
"Organization.Location.2"), row.names = c(NA, 6L), class = "data.frame")
You can try something like this:

# function for string preperation:
preperString &lt;- function(x) {
  require(stringr)
  x &lt;- str_to_lower(x)
  x &lt;- str_trim(x)
  x
}

setDT(loc_df) # convert data.frames to data.table
setDT(city_df)

loc_df &lt;- loc_df[, lapply(.SD, preperString)] # apply string preperation to all columns of loc_df
city_df[, City := preperString(City)]

loc_df &lt;- merge(loc_df, city_df, by.x = 'Organization.Location.1',
                by.y = 'City', all.x = T, sort = F)
loc_df &lt;- merge(loc_df, city_df, by.x = 'Organization.Location.2',
                by.y = 'City', all.x = T, sort = F)
loc_df
#    Organization.Location.2         Organization.Location.1 Country.x Country.y
# 1:                                        zug  switzerland        NA        NA
# 2:  san francisco bay area zug  canton of zug  switzerland        NA        NA
# 3:     london  canada area                        zimbabwe  zimbabwe        NA
# 4:     beijing city  china       zigong  chengdu  pr china        NA        NA
# 5:    greater atlanta area        zhuhai  guangdong  china     china        NA
# 6:      paris area  france                 zaragoza  spain     spain        NA

# and then you can write rule tu create org_new1, for example:
loc_df[, org_new1 := Country.x]
loc_df[is.na(org_new1), org_new1 := Country.y]
loc_df
#    Organization.Location.2         Organization.Location.1 Country.x Country.y org_new1
# 1:                                        zug  switzerland        NA        NA       NA
# 2:  san francisco bay area zug  canton of zug  switzerland        NA        NA       NA
# 3:     london  canada area                        zimbabwe  zimbabwe        NA zimbabwe
# 4:     beijing city  china       zigong  chengdu  pr china        NA        NA       NA
# 5:    greater atlanta area        zhuhai  guangdong  china     china        NA    china
# 6:      paris area  france                 zaragoza  spain     spain        NA    spain