Extracting cities from string vector in R

Question

I have a column in my dataset db, say db$affiliation, which looks like:

**db$affiliation**
[1] "[SCOTT, ALLEN J.] UNIV CALIF LOS ANGELES, DEPT GEOG, LOS ANGELES, CA 90095 USA"                               
[2] "[VAN DUINEN, RIANNE; VAN DER VEEN, ANNE] UNIV TWENTE, DEPT WATER ENGN & MANAGEMENT, DRIENERLOLAAN 5,POB 217, NL-7500 AE ENSCHEDE, NETHERLANDS."                                                
[3] "[ANANTSUKSOMSRI, SUTEE] CHULALONGKORN UNIV, FAC ARCHITECTURE, BANGKOK, THAILAND."   
[4] ...

I would like to create a column within the same dataset containing only the name of the city in db$affiliation, such as

 **db$cities**
 [1] LOS ANGELES
 [2] TWENTE
 [3] BANGKOK
 [4] ...

If multiple city names are available, I'd like the command to return only the last one, if no city names are available I'd like to have NA. How can I do that?

I thought that I could use world.cities$name in data(world.cities) in the maps package but I can not figure out how.

I even tried to split the db$affiliation column such as:

db$affiliation <- gsub("\\[[^\\]]*\\]", "", db$affiliation, perl=TRUE) # remove content within brackets 
db$affiliation[2] # check the separator
db <- cSplit(db, 'affiliation', sep=c(", "), type.convert=FALSE) # split after comma 

Which results (I've truncated it after affiliation_3) in:

    affiliation_1            affiliation_2                  affiliation_3 
[1] UNIV CALIF LOS ANGELES   DEPT GEOG                      LOS ANGELES  
[2] UNIV TWENTE              DEPT WATER ENGN & MANAGEMENT   DRIENERLOLAAN            
[3] CHULALONGKORN UNIV       FAC ARCHITECTURE               BANGKOK 

And then pass:

db$cities <- lapply(db$affiliation_1, function(x)x[which(x %in% world.cities$name)])

But I get an empty column.

Thanks for the help!

Answer 1

There are many cities in your sample string so you may need to think again if you still want to fetch the 'last city' in case multiple cities are found in affiliation column.

library(maps)
data(world.cities)

#sample data
df <- data.frame(affiliation = c("[SCOTT, ALLEN J.] UNIV CALIF LOS ANGELES, DEPT GEOG, LOS ANGELES, CA 90095 USA",
                                 "[VAN DUINEN, RIANNE; VAN DER VEEN, ANNE] UNIV TWENTE, DEPT WATER ENGN & MANAGEMENT, DRIENERLOLAAN 5,POB 217, NL-7500 AE ENSCHEDE, NETHERLANDS.",
                                 "[ANANTSUKSOMSRI, SUTEE] CHULALONGKORN UNIV, FAC ARCHITECTURE, BANGKOK, THAILAND.",
                                 "Prem"), stringsAsFactors = F)

#fetch city and it's respective country from 'affiliation' column
cities_country <- lapply(gsub("\\[|\\]|[,;]|\\.","",df$affiliation), function(x) 
  paste(as.character(world.cities$name[sapply(world.cities$name, grepl, x, ignore.case=T)]),
        as.character(world.cities$country.etc[sapply(world.cities$name, grepl, x, ignore.case=T)]),
        sep="_"))
df$cities_country <- lapply(cities_country, function(x) if(identical(x, character(0))) NA_character_ else x)
df

Output is:

affiliation
1                                                                 [SCOTT, ALLEN J.] UNIV CALIF LOS ANGELES, DEPT GEOG, LOS ANGELES, CA 90095 USA
2 [VAN DUINEN, RIANNE; VAN DER VEEN, ANNE] UNIV TWENTE, DEPT WATER ENGN & MANAGEMENT, DRIENERLOLAAN 5,POB 217, NL-7500 AE ENSCHEDE, NETHERLANDS.
3                                                               [ANANTSUKSOMSRI, SUTEE] CHULALONGKORN UNIV, FAC ARCHITECTURE, BANGKOK, THAILAND.
4                                                                                                                                           Prem
                                                                                                                                                                                                                                                                                            cities_country
1                                                                      Al_Norway, Alle_Switzerland, Allen_Philippines, Allen_USA, Angeles_Costa Rica, Angeles_Philippines, Cali_Colombia, Cot_Costa Rica, Li_Norway, Los Angeles_Chile, Los Angeles_USA, Os_Kyrgyzstan, Os_Norway, U_Micronesia, Usa_Japan
2 Ae_Marshall Islands, Ede_Netherlands, Ede_Nigeria, Enschede_Netherlands, Hede_China, Ine_Marshall Islands, Laa_Austria, Lola_Guinea, Man_Ivory Coast, Mana_French Guiana, Manage_Belgium, Nagem_Luxembourg, Ob_Russia, Ola_Panama, Po_Burkina Faso, U_Micronesia, Van_Turkey, Wa_Ghana, We_New Caledonia
3                                                                                                                                     Aila_Estonia, Al_Norway, Anan_Japan, Ba_Fiji, Bangkok_Thailand, Hit_Iraq, Ila_Nigeria, Ilan_Taiwan, Long_Thailand, Nan_Thailand, Tsu_Japan, U_Micronesia, Ula_Turkey
4                                                                                                                                                                                                                                                                                                       NA

(Note that in above output I have kept all occurrences of cities and for convenience also suffixed it with their respective countries)