Last active

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

Merge by City and State in R

View cities-coords.csv
1 2 3 4 5 6 7 8
City,State,Latitude,Longitude
San Francisco,CA,37.7782251,-122.4424955
New York,NY,40.7142691,-74.0059729
Los Angeles,CA,34.0522342,-118.2436849
Chicago,IL,41.850033,-87.6500523
Dallas,TX,32.7830556,-96.8066667
Columbus,GA,32.4609764,-84.9877094
Columbus,OH,39.9611755,-82.9987942
View cities-coords.csv
1 2 3 4 5 6 7 8 9 10 11
city,state,year,population
New York,NY,2012,8336697
New York,NY,2010,8175133
Los Angeles,CA,2012,3857799
Chicago,IL,2012,2714856
San Francisco,CA,2012,825863
San Francisco,CA,2010,805235
Houston,TX,2012,2160821
Columbus,GA,2012,198413
Columbus,OH,2012,809798
Columbus,OH,2010,787033
View cities-coords.csv
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
# 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")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.