NPMRDS Shapefiles

Texas Data

By County

setwd("H:/TX_NPMRDS_ByM_15_NonNA/Shapefile")
library(foreign)
library(data.table)
library(DT)
library(tidyverse)
library(dplyr)

tx17 <- read.dbf("Texas2017/Texas.dbf")
tx18 <- read.dbf("Texas2018/Texas.dbf")
tx19 <- read.dbf("Texas2019/Texas.dbf")

dim(tx17)
## [1] 34247    36
dim(tx18)
## [1] 32241    36
dim(tx19)
## [1] 36435    36
sum(tx17$Miles)
## [1] 36010.46
sum(tx18$Miles)
## [1] 35600.15
sum(tx19$Miles)
## [1] 37433.48
## distinct(tx19,County)


## TX18 has county names all Caps. Drat. Caution: will make both lower.
proper=function(s) sub("(.)", ("\\U\\1"), tolower(s), pe=TRUE)
## tx19$County= proper(tx19$County)


### stringi is better
library(stringi)
tx19$County= stri_trans_general(tx19$County, id="Title")

### Mcculloch and 3 others are now not matching. :( So, 
tx18$County= stri_trans_general(tx18$County, id="Title")
tx17$County= stri_trans_general(tx17$County, id="Title")


### now found that De Witt has a space in tx19. Grrrrr..
tx17_1 <- tx17 %>% group_by(County) %>% summarize(Len17=sum(Miles))
tx18_1 <- tx18 %>% group_by(County) %>% summarize(Len18=sum(Miles))
tx19_1 <- tx19 %>% group_by(County) %>% summarize(Len19=sum(Miles))


tx19_1$County <- replace(tx19_1$County, tx19_1$County=="De Witt", "Dewitt")


tx01 <- left_join(tx17_1, tx18_1, by="County")
byCounty <- left_join(tx01, tx19_1, by="County")


datatable(
  byCounty, extensions = c('Select', 'Buttons'), options = list(
    select = list(style = 'os', items = 'row'),
    dom = 'Blfrtip',
    rowId = 0,
    buttons = c('csv', 'excel')
  ),
  selection = 'none'
)

By TMC

### https://npmrds.ritis.org/analytics/shapefiles


tx17a <- tx17[,c("Tmc", "Tmc", "Miles")]
tx18a <- tx18[,c("Tmc", "Tmc", "Miles")]
tx19a <- tx19[,c("Tmc", "Tmc", "Miles")]

colnames(tx17a)[2] <- "Tmc17"
colnames(tx18a)[2] <- "Tmc18"
colnames(tx19a)[2] <- "Tmc19"

byTMC = Reduce(function(...) merge(..., by='Tmc', all.x=TRUE, all.y=TRUE), list(tx17a, tx18a, tx19a))
dim(byTMC)
## [1] 41539     7
colnames(byTMC)[3] <- "Miles17"; colnames(byTMC)[5] <- "Miles18"; colnames(byTMC)[7] <- "Miles19"

datatable(
  byTMC, extensions = c('Select', 'Buttons'), options = list(
    select = list(style = 'os', items = 'row'),
    dom = 'Blfrtip',
    rowId = 0,
    buttons = c('csv', 'excel')
  ),
  selection = 'none'
)