How to use NLP / string manipulation to recode multiple columns of state/city/foreign locations

Question

VERY appreciative of help!!!

I have some very dirty data I am trying to clean up. Looking for an elegant solution in R that will correctly identify if there is foreign travel or not (TRUE = foreign travel, FALSE = domestic/USA travel).

There are several issues with the data including: - states are both in abbreviate & not abbreviated format - misspellings - differing formats (i.e. just state, city comma state, city slash state, etc) - data under state/country may contain a city rather a state/country and visa versa for the city column.

Under the foreign travel column, the solution should overwrite such that if the state/country or city column indicate foreign travel it will be coded as TRUE, else FALSE.

   `State/Country`          `Foreign Travel`                   City         
    <chr>                            <lgl>                    <chr>        
   1 CA                                FALSE             San Francisco
   2 California                        FALSE             San Francisco
   3 British Columbia, Canada          TRUE              Vancouver    
   4 Florida                            NA               Hollywood    
   5 TX                                 NA               Dallas       
   6 Florda                             NA               Orlando 
   7 FL/CA                              NA               Orlando, Sacramennto 
   8 bufalo                             NA               NY
   9 d.c                               FALSE             washington dc
   10 frt wort, tx                     FALSE             texass
   11 frt wort, tx                     FALSE             texass
   12 japan                            NA                japan
   13 W?rzburg                         FALSE             german

Right now I have some very untidy code that looks at each column, gives a true/false if it finds it, if true (found a domestic item) for at least 1 column it recodes by foreign t/f column to False (no foreign travel):

 ##add some lines for nas
 no_entry <- c("na",".","","n/a","none")
  ##Maps package
 cities<- world.cities

 USAcities <- cities %>%
    filter(country.etc == 'USA')

   USAcities <- c(USAcities, 'williamsburg')

  USAcities <-tolower(USAcities$name)
 USA_fullState<- tolower(USA_fullState)
 USA_stateABR<- tolower(USA_stateABR)
 Travel_df_limited$State.Country<- tolower(Travel_df_limited$State.Country)


     Travel_df_limited$ForeignTravel_rc1 <- 
    c(rep(0,length(Travel_df_limited$Foreign.Travel)))

   i<-1
   for (i in 1:length(USA_fullState)){
   Travel_df_limited <- Travel_df_limited %>%
    mutate(ForeignTravel_rc1 = 
    ifelse(grepl(USA_fullState[i],Travel_df_limited$State.Country) == 
    "TRUE","FALSE",Travel_df_limited$ForeignTravel_rc1 ))
     i<- i+1}

  Travel_df_limited$ForeignTravel_rc1

   Travel_df_limited <- Travel_df_limited %>%
    mutate(ForeignTravel_rc2 = ifelse(Travel_df_limited$State.Country%in% 
    USA_stateABR== "TRUE","FALSE","TRUE"))


 Travel_df_limited$ForeignTravel_rc3 <- 
    c(rep(0,length(Travel_df_limited$Foreign.Travel)))

   i<-1
 for (i in 1:length(USAcities)){
   Travel_df_limited <- Travel_df_limited %>%
    mutate(ForeignTravel_rc3 = 
     ifelse(grepl(USAcities[i],Travel_df_limited$State.Country) == 
    "TRUE","FALSE",Travel_df_limited$ForeignTravel_rc3))
     i<- i+1}


     Travel_df_limited <- Travel_df_limited %>%
     mutate(ForeignTravel_rc = ifelse(Travel_df_limited$ForeignTravel_rc1 == 
      "FALSE" |   Travel_df_limited$ForeignTravel_rc2 == "FALSE"|
                                 Travel_df_limited$ForeignTravel_rc3 == 
          "FALSE" , "FALSE",
             ifelse(Travel_df_limited$State.Country%in% 
               c("na",".","","n/a","none") =="TRUE","FALSE", "TRUE")))



      Travel_df_limited<- subset(Travel_df_limited, select = - 
       c(ForeignTravel_rc1,ForeignTravel_rc2,ForeignTravel_rc3))