County Centroids
# data wrangling & plotting
library(tidyverse) # dplyr and ggplot2
library(reshape2)
# spatial analyses - for later
library(sf) # working with vectors - polygons, lines, etc
library(raster) # raster
library(USAboundaries)
library(USAboundariesData)
# color scales
library(viridis)
library(ggrepel)
theme_set(theme_bw())# set plotting theme for session
library(rjson)
library(DT)
doc1<-fromJSON(file="https://raw.githubusercontent.com/CDCgov/MicrobeTrace/master/data/counties.json", method="C")
county_centroid <- do.call(rbind, lapply(doc1$features, function(x) data.frame(x$properties)))
datatable(
county_centroid, extensions = c('Select', 'Buttons'), options = list(
select = list(style = 'os', items = 'row'),
dom = 'Blfrtip',
rowId = 0,
buttons = c('csv', 'excel')
),
selection = 'none'
)
County Distances
### https://data.nber.org/data/county-distance-database.html
setwd("L:/00_Spatial/county_distances")
list.files()
## [1] "county_fips_master.csv" "fips"
## [3] "sf12010countydistance100miles.csv" "sf12010countydistance25miles.csv"
## [5] "sf12010countydistance500miles.csv" "sf12010countydistance50miles.csv"
## [7] "sf12010countydistancemiles.csv" "sf12010countyname.csv"
library(data.table)
library(DT)
county <- fread("fips/county_fips_master.csv")
county1 <- fread("sf12010countyname.csv")
names(county)
## [1] "fips" "county_name" "state_abbr" "state_name"
## [5] "long_name" "sumlev" "region" "division"
## [9] "state" "county" "crosswalk" "region_name"
## [13] "division_name"
## [1] "county" "countyname"
## [1] 3146 13
## [1] 3221 2
county2= left_join(county1, county[,c(1, 4)], by = c("county" = "fips"))
head(county2)
## county countyname state_name
## 1 1001 Autauga County Alabama
## 2 1003 Baldwin County Alabama
## 3 1005 Barbour County Alabama
## 4 1007 Bibb County Alabama
## 5 1009 Blount County Alabama
## 6 1011 Bullock County Alabama
dist25 <- fread("sf12010countydistance25miles.csv")
county_25a= left_join(dist25, county2[,c(1, 3)], by = c("county1" = "county"))
county_25b= left_join(county_25a, county2[,c(1, 3)], by = c("county1" = "county"))
head(county_25b)
## county1 countyname1 mi_to_county county2 countyname2 state_name.x
## 1 1001 Autauga County 22.46299 1021 Chilton County Alabama
## 2 1003 Baldwin County 24.41967 12033 Escambia County Alabama
## 3 1005 Barbour County 23.25404 13239 Quitman County Alabama
## 4 1005 Barbour County 24.26580 1011 Bullock County Alabama
## 5 1009 Blount County 20.31800 1043 Cullman County Alabama
## 6 1009 Blount County 23.28895 1115 St. Clair County Alabama
## state_name.y
## 1 Alabama
## 2 Alabama
## 3 Alabama
## 4 Alabama
## 5 Alabama
## 6 Alabama
## [1] 8384 5
## [1] 8408 7
datatable(
county_25b, extensions = c('Select', 'Buttons'), options = list(
select = list(style = 'os', items = 'row'),
dom = 'Blfrtip',
rowId = 0,
buttons = c('csv', 'excel')
),
selection = 'none'
)
dist50 <- fread("sf12010countydistance50miles.csv")
county_50a= left_join(dist50, county2, by = c("county1" = "county"))
county_50b= left_join(county_50a, county2, by = c("county1" = "county"))
head(county_50b)
## county1 mi_to_county county2 countyname.x state_name.x countyname.y
## 1 1001 22.46299 1021 Autauga County Alabama Autauga County
## 2 1001 26.84469 1085 Autauga County Alabama Autauga County
## 3 1001 29.51758 1051 Autauga County Alabama Autauga County
## 4 1001 30.77637 1047 Autauga County Alabama Autauga County
## 5 1001 34.49344 1101 Autauga County Alabama Autauga County
## 6 1001 35.89276 1037 Autauga County Alabama Autauga County
## state_name.y
## 1 Alabama
## 2 Alabama
## 3 Alabama
## 4 Alabama
## 5 Alabama
## 6 Alabama
## [1] 38792 3
## [1] 38864 7
dist100 <- fread("sf12010countydistance100miles.csv")
county_100a= left_join(dist100, county2, by = c("county1" = "county"))
county_100b= left_join(county_100a, county2, by = c("county1" = "county"))
head(county_100b)
## county1 mi_to_county county2 countyname.x state_name.x countyname.y
## 1 1001 22.46299 1021 Autauga County Alabama Autauga County
## 2 1001 26.84469 1085 Autauga County Alabama Autauga County
## 3 1001 29.51758 1051 Autauga County Alabama Autauga County
## 4 1001 30.77637 1047 Autauga County Alabama Autauga County
## 5 1001 34.49344 1101 Autauga County Alabama Autauga County
## 6 1001 35.89276 1037 Autauga County Alabama Autauga County
## state_name.y
## 1 Alabama
## 2 Alabama
## 3 Alabama
## 4 Alabama
## 5 Alabama
## 6 Alabama
## [1] 147156 3
## [1] 147381 7
dist500 <- fread("sf12010countydistance500miles.csv")
county_500a= left_join(dist500, county2, by = c("county1" = "county"))
county_500b= left_join(county_500a, county2, by = c("county1" = "county"))
head(county_500b)
## county1 mi_to_county county2 countyname.x state_name.x countyname.y
## 1 1001 22.46299 1021 Autauga County Alabama Autauga County
## 2 1001 26.84469 1085 Autauga County Alabama Autauga County
## 3 1001 29.51758 1051 Autauga County Alabama Autauga County
## 4 1001 30.77637 1047 Autauga County Alabama Autauga County
## 5 1001 34.49344 1101 Autauga County Alabama Autauga County
## 6 1001 35.89276 1037 Autauga County Alabama Autauga County
## state_name.y
## 1 Alabama
## 2 Alabama
## 3 Alabama
## 4 Alabama
## 5 Alabama
## 6 Alabama
## [1] 2637350 3
## [1] 2639963 7