Merge by City and State in R

cities-coords.csv

cities-data.csv

merge.R

# import city coordinate data:
# source: http://geonames.usgs.gov/domestic/download_data.htm
coords <- read.csv("cities-coords.csv",
  header = TRUE,
  sep = ",")

# import population data:
# source: http://en.wikipedia.org/wiki/List_of_United_States_cities_by_population
data <- read.csv("cities-data.csv",
  header = TRUE,
  sep = ",")

# merge data & coords by city & state:
dataCoords <- merge(coords, data, 
  by.x = c("City", "State"),
  by.y = c("city", "state"),
  all.x = FALSE,
  all.y = TRUE)

# find cities without coordinates:
dataCoords[!complete.cases(dataCoords[,c(3,4)]),]

# tidy names:
names(dataCoords) <- c("City", "State", "Latitude", "Longitude", "Year", "Population")