Styjun

I have a large data frame (travel) containing many individual travel histories. In the travel$Travel 1 Destination column I want only country data, but numerous entries have region, city or other data entered.

I have tried to write a function (city_replace_with_country) with 2 arguments:

• city_name: the name of the city already in the travel$`Travel 1 Destination' column


• country_name: the name of the country it should be

I would like my function to find these errors in the data frame, paste them into another pre-existing column (travel$regions_visited), and replace the city/region name in the travel$`Travel 1 Destination' with the correct country name.

This code works fine for individual examples eg. bangalore and india

for (i in 1:nrow(travel) ) 
 if(grepl("bangalore", travel$`Travel 1 Destination`[i], ignore.case = TRUE))
 travel$regions_visited[i] <- paste(travel$regions_visited[i], "Bangalore", sep = " ")
 travel$`Travel 1 Destination`[i] <- gsub("bangalore", "india", travel$`Travel 1 Destination`[i], ignore.case = TRUE, perl = TRUE)
 

Ideally I would be able to make many corrections to the data frame using the function

city_replace_with_country <- function(city_name, country_name) 
 for (i in 1:nrow(travel)) 
 if(grepl(city_name, travel$`Travel 1 Destination`[i], ignore.case = TRUE))
 travel$regions_visited[i] <- paste(travel$regions_visited[i], city_name, sep = " ")
 travel$`Travel 1 Destination`[i] <- gsub(city_name, country_name, travel$`Travel 1 Destination`[i], ignore.case = TRUE, perl = TRUE)
 

When I try and use this function as follows:

city_replace_with_country("bangalore", "india")

The output of the function doesn't seem to write/store to the data frame.
If I try:

travel <- city_replace_with_country("bangalore", "india")

or

travel$`Travel 1 Destination` <- city_replace_with_country("bangalore", "india")

It returns a NULL value.

Any suggestions as to how to get this to work would be greatly appreciated. Many thanks.

Here is some example data. In this case for example, I would want to change Bogota for Colombia:

travel_example <- structure(list(X1 = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
13, 14, 15, 16, 17, 18, 19, 20, 21), `Travel 1 Dates` = c("03/09/18 to 02/10/18", 
NA, "15/08/18 - 24/08/18", "13/09/2018 to 19/09/2018", "15/07- 14/08", 
"21/9/18-29/9/18", "10/09/18-3/10/18", "28/5/18-31/7/18", "1/9/18-16/9/18", 
NA, NA, NA, "15/07/18-03/10/18", "15/09/18-30/09/18", "18/09/18-05/10/18", 
"7/9/18-23/9/18", "14/8/18-11/9/18", "25/7/18-13/8/18", "24/9/18-30/9/18", 
NA, "18/9/18-2/10/18"), `Travel 1 Destination` = c("Colombia", 
"salvador, Bogata, Honduras", "China,Cambodia", "Lagos, Nigeria", 
"Uganda", "Indonesia", "Kenya", "Dubai, Japan", "Sri Lanka", 
"Jakarta,Indonesia", "South Africa, Mozambique, Ethiopia", NA, 
"Ukraine, Slovakia, India", "Tanzania", "Ghana", "Sri Lanka", 
"Angola", "Tajikistan , Afghanistan", "Morocco", NA, "Tanzania"
), regions_visited = c("character(0)", "character(0)", "character(0)", 
"character(0)", "character(0)", "character(0)", "Nairobi", "character(0)", 
"character(0)", "character(0)", "character(0)", "character(0)", 
"character(0)", "character(0)", "character(0)", "character(0)", 
"Luanda only", "c("south", "north", "very rural")", "character(0)", 
"character(0)", "character(0)")), class = c("spec_tbl_df", "tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -21L), spec = structure(list(
 cols = list(X1 = structure(list(), class = c("collector_double", 
 "collector")), `Travel 1 Dates` = structure(list(), class = c("collector_character", 
 "collector")), `Travel 1 Destination` = structure(list(), class = c("collector_character", 
 "collector")), regions_visited = structure(list(), class = c("collector_character", 
 "collector"))), default = structure(list(), class = c("collector_guess", 
 "collector")), skip = 1), class = "col_spec"))

In your case, you could just extract the last word after comma using regex and get all the country names as below:

travel_example$`Travel 1 Destination` <- sub('.*,\s*', '', travel_example$`Travel 1 Destination`)
travel_example$`Travel 1 Destination`
 [1] "Colombia" "Honduras" "Cambodia" "Nigeria" "Uganda" 
 [6] "Indonesia" "Kenya" "Japan" "Sri Lanka" "Indonesia" 
[11] "Ethiopia" NA "India" "Tanzania" "Ghana" 
[16] "Sri Lanka" "Angola" "Afghanistan" "Morocco" NA 
[21] "Tanzania"