Read in TxDoT Crash data

Mapping

Supporting Activism

Texas

Harris County

Read in the crash data from TxDoT for Harris county

Author

Alan Jackson

Published

April 12, 2025

Code

filenames <- list.files(path = inpath, pattern="*csv$")

filenames <- paste(inpath, filenames, sep="/")

DF <- read_csv(filenames[1], col_types = cols(.default = col_character()))

for (i in filenames[-1]) {
  print(i)
  df <- read_csv(i, col_types = cols(.default = col_character()))
  DF <- full_join(DF, df)
}

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2015_20250412162933_crash_20160101-20161231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2015_20250412163026_crash_20150101-20151231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2018_20250412162001_crash_20230101-20231231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2018_20250412162131_crash_20220101-20221231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2018_20250412162247_crash_20210101-20211231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2018_20250412162436_crash_20200101-20201231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2018_20250412162547_crash_20190101-20191231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2018_20250412162656_crash_20180101-20181231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2023_20250412161759_crash_20240101-20241231_HARRIS.csv"

[1] "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped/extract_public_2023_20250412162001_crash_20230401-20231231_HARRIS.csv"

Code

df <- DF %>% select(Crash_ID, Crash_Fatal_Fl, Crash_Date,   Crash_Time, Rpt_City_ID,
Rpt_Block_Num, Rpt_Street_Pfx,  Rpt_Street_Name, Rpt_Street_Sfx, At_Intrsct_Fl,
Latitude, Longitude, Street_Name, Street_Nbr, Tot_Injry_Cnt, Death_Cnt,
Harm_Evnt_ID, Crash_Sev_ID)

df <- left_join(df, City, by="Rpt_City_ID")
df <- left_join(df, Severity, by="Crash_Sev_ID")
df <- left_join(df, Harm, by="Harm_Evnt_ID")

#   Create datetime 

df <- df %>% 
  mutate(Date=mdy_hm(paste(Crash_Date, Crash_Time)))

# df <- df %>% 
#   rename(City=City.x, Severity=Severity.x) %>% 
#   select(-City.y, -Severity.y)
# df <- left_join(df, Charge, by="Charge_Cat_ID")

#   Save raw data

# saveRDS(DF, paste(path, "Raw_Crash_Data_2015_2024.rds", sep="/"))

Do some quality control checks

Code

# Look for NA's in the various fields

df %>%
    map_df(function(x) sum(is.na(x))) %>%
    gather(feature, num_nulls) %>%
    print(n = 100)

# A tibble: 22 × 2
   feature         num_nulls
   <chr>               <int>
 1 Crash_ID                0
 2 Crash_Fatal_Fl          0
 3 Crash_Date              0
 4 Crash_Time              0
 5 Rpt_City_ID             0
 6 Rpt_Block_Num       38057
 7 Rpt_Street_Pfx     852638
 8 Rpt_Street_Name         3
 9 Rpt_Street_Sfx     232054
10 At_Intrsct_Fl           0
11 Latitude           135457
12 Longitude          135457
13 Street_Name             2
14 Street_Nbr         875180
15 Tot_Injry_Cnt           0
16 Death_Cnt               0
17 Harm_Evnt_ID            0
18 Crash_Sev_ID            0
19 City               404369
20 Severity                0
21 Harmed                  0
22 Date                    0

Code

sort(unique(df$Crash_Fatal_Fl))

[1] "N" "Y"

Code

sort(unique(df$Crash_Date)) %>%  head()

[1] "01/01/2015" "01/01/2016" "01/01/2017" "01/01/2018" "01/01/2019"
[6] "01/01/2020"

Code

#  make it a date

df$Crash_Date <- mdy(df$Crash_Date)

df %>% 
  ggplot(aes(Crash_Date)) +
    geom_histogram()

Code

sort(unique(df$Crash_Time)) %>% head()

[1] "00:00 AM" "01:00 AM" "01:00 PM" "01:01 AM" "01:01 PM" "01:02 AM"

Code

#   make it a time

df <- df %>% 
  mutate(Crash_Time=ymd_hm(paste("2000/1/1",Crash_Time)))

df %>% 
  ggplot(aes(Crash_Time)) +
    geom_histogram()

Code

sort(unique(df$Rpt_City_ID))

  [1] "1013" "1061" "107"  "1073" "108"  "111"  "112"  "1159" "1185" "121" 
 [11] "1322" "1323" "1326" "1348" "135"  "1353" "136"  "1389" "14"   "141" 
 [21] "1444" "1506" "1532" "1535" "160"  "164"  "169"  "1926" "2071" "208" 
 [31] "211"  "212"  "218"  "219"  "22"   "223"  "228"  "243"  "244"  "254" 
 [41] "255"  "260"  "264"  "269"  "278"  "283"  "29"   "291"  "295"  "296" 
 [51] "298"  "30"   "304"  "311"  "315"  "317"  "325"  "326"  "33"   "3313"
 [61] "333"  "346"  "359"  "369"  "37"   "379"  "382"  "383"  "391"  "405" 
 [71] "408"  "409"  "413"  "414"  "422"  "430"  "434"  "435"  "441"  "442" 
 [81] "4461" "447"  "453"  "459"  "46"   "4728" "478"  "5006" "523"  "5644"
 [91] "5659" "57"   "59"   "6"    "625"  "647"  "672"  "6756" "843"  "851" 
[101] "855"  "8746" "899"  "92"   "952"  "9999"

Code

sort(unique(df$Rpt_Block_Num)) %>%  head()

[1] "#1"     "`10261" "`123"   "`12406" "`19200" "0"

Code

#   Show all non-numeric addresses
df$Rpt_Block_Num[grepl("\\D+", df$Rpt_Block_Num)] %>%  head(20)

 [1] "35OO"       "UNIT"       "UNIT BLOCK" "1400O"      "UNIT"      
 [6] "1-99"       "135OO"      "45OO"       "11OOO"      "123OO"     
[11] "5-99"       "18,000"     "O"          "2372F"      "1500`"     
[16] "UNIT BLOCK" "1400 BLK"   "UNIT BLOCK" "1100 BLOCK" "UNIT"

Code

#   Remove non-numeric stuff

df$Rpt_Block_Num <- str_replace(df$Rpt_Block_Num, " BLOCK","")
df$Rpt_Block_Num <- str_replace_all(df$Rpt_Block_Num, "O","0")
df$Rpt_Block_Num <- str_replace_all(df$Rpt_Block_Num, "^\\d+-","")
df$Rpt_Block_Num <- str_replace_all(df$Rpt_Block_Num, "PP","00")
df$Rpt_Block_Num <- str_replace_all(df$Rpt_Block_Num, " 1/2","")
df$Rpt_Block_Num <- str_replace(df$Rpt_Block_Num, "\\D+","")

#   Show all non-numeric addresses
df$Rpt_Block_Num[grepl("\\D+", df$Rpt_Block_Num)] %>%  head(20)

 [1] "0RE"       "0CK"       "0A"        "0NE"       "0WN"       "0B"       
 [7] "121000UTH" "0CK"       "0CK"       "0CK"       "0CK"       "0NE"      
[13] "0CK"       "176000E"   "00T"       "0CK"       "0CK"       "0CK"      
[19] "0CK"       "0CK"

Code

df$Rpt_Block_Num <- str_replace(df$Rpt_Block_Num, "0CK","")
df$Rpt_Block_Num <- str_replace(df$Rpt_Block_Num, "0\\D+","")

df$Rpt_Block_Num[grepl("\\D+", df$Rpt_Block_Num)] %>%  head(20)

character(0)

Code

sort(unique(df$Rpt_Street_Pfx))

[1] "E"  "N"  "NE" "NW" "S"  "SE" "SW" "W"

Code

sort(unique(df$Rpt_Street_Sfx))

 [1] "AVE"  "BLVD" "CIR"  "CT"   "CV"   "DR"   "EXPY" "FWY"  "HWY"  "LN"  
[11] "LOOP" "PARK" "PATH" "PKWY" "PL"   "PT"   "RD"   "ST"   "TRC"  "TRL" 
[21] "WAY"

Code

sort(unique(df$Severity))

[1] "FATAL"                    "NON-INCAPACITATING"      
[3] "NOT INJURED"              "POSSIBLE INJURY"         
[5] "SUSPECTED SERIOUS INJURY" "UNKNOWN"

Code

sort(unique(df$Harmed))

 [1] "Animal"                     "Fixed Object"              
 [3] "Motor Vehicle in Transport" "Other Non-Collision"       
 [5] "Other Object"               "Overturned"                
 [7] "Parked Car"                 "Pedal Cyclist"             
 [9] "Pedestrian"                 "Rail Road"

Code

df %>% 
  ggplot(aes(x=Harmed)) +
  geom_bar() +
  coord_flip()

Code

#     Save file out

# saveRDS(df, paste(path, "Cleaned_2015_2024.rds", sep="/"))

--- title: "Read in TxDoT Crash data" author: "Alan Jackson" format: html: code-fold: true code-tools: true description: "Read in the crash data from TxDoT for Harris county" date: "4/12/2025" image: "cover.png" categories: - Mapping - Supporting Activism - Texas - Harris County execute: freeze: auto # re-render only when source changes warning: false editor: source --- ```{r setup, include=FALSE} library(tidyverse) library(lubridate) # F7$rA4&T1Lq5UL!a inpath <- "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/Unzipped" path <- "/home/ajackson/Dropbox/Rprojects/Curated_Data_Files/Traffic_crashes/" # Crash Severity ID table Severity <- c( "0", "UNKNOWN", "1", "SUSPECTED SERIOUS INJURY", "2", "NON-INCAPACITATING", "3", "POSSIBLE INJURY", "4", "FATAL", "5", "NOT INJURED", "94", "REPORTED INVALID", "95", "NOT REPORTED" ) Severity <- cbind.data.frame(split(Severity, rep(1:2, times=length(Severity)/2)), stringsAsFactors=F) names(Severity) <- c("Crash_Sev_ID", "Severity") # Severity$Crash_Sev_ID <- as.integer(Severity$Crash_Sev_ID) City <- c( "29", "Baytown", "30", "Beaumont", "33", "Bellaire", "57", "Bunker Hill Village", "111", "Deer Park", "135", "El Lago", "160", "Friendswood", "164", "Galena Park", "208", "Houston", "211", "Humble", "212", "Hunters Creek Village", "219", "Jacinto City", "223", "Jersey Village", "228", "Katy", "244", "La Porte", "255", "League City", "260", "Liberty", "296", "Missouri City", "304", "Nassau Bay", "325", "Pasadena", "326", "Pearland", "333", "Piney Point Village", "359", "Richmond", "383", "San Marcos", "391", "Seabrook", "405", "South Houston", "408", "Spring Valley", "409", "Stafford", "422", "Taylor Lake Village", "430", "Tomball", "434", "Tyler", "447", "Webster", "453", "West University Place", "478", "Aldine", "625", "Channelview", "647", "Cloverleaf", "672", "Crosby", "843", "Hedwig Village", "851", "Highlands", "855", "Hilshire Village", "1061", "Mission Bend", "1073", "Morgans Point", "1326", "Shoreacres", "1348", "Southside Place", "1353", "Spring", "1444", "Waller", "1535", "Kingwood", "1585", "Rural Brazoria County", "1601", "Rural Chambers County", "1644", "Rural Fort Bend County", "1649", "Rural Galveston County", "1665", "Rural Hardin County", "1666", "Rural Harris County", "1667", "Rural Harrison County", "1668", "Rural Hartley County", "1678", "Rural Houston County", "1711", "Rural Liberty County", "1719", "Rural Madison County", "1735", "Rural Montgomery County", "1926", "Alief", "2071", "Atascosita", "3313", "Cypress", "4728", "Huffman", "5659", "Meadows Place", "6756", "Porter" ) City <- cbind.data.frame(split(City, rep(1:2, times=length(City)/2)), stringsAsFactors=F) names(City) <- c("Rpt_City_ID", "City") # City$Rpt_City_ID <- as.integer(City$Rpt_City_ID) # Crash Harm ID table Harm <- c( "1", "Pedestrian", "2", "Motor Vehicle in Transport", "3", "Rail Road", "4", "Parked Car", "5", "Pedal Cyclist", "6", "Animal", "7", "Fixed Object", "8", "Other Object", "9", "Other Non-Collision", "10", "Overturned", "11", "Not Reported", "93", "Undetermined", "94", "Reported Invalid" ) Harm <- cbind.data.frame(split(Harm, rep(1:2, times=length(Harm)/2)), stringsAsFactors=F) names(Harm) <- c("Harm_Evnt_ID", "Harmed") # Charge Category ID table Charge <- c( "10", "ALCOHOL/DRUGS", "11", "ASSAULT/MANSLAUGHTER WITH VEHICLE", "12", "BICYCLE OFFENSES", "13", "COMMERCIAL & MOTOR CARRIER VIOLATIONS", "14", "DRIVER LICENSE & REGISTRATION VIOLATIONS", "15", "DRIVER'S MISCELLANEOUS VIOLATIONS", "16", "INSURANCE AND INSPECTION", "17", "MOTORCYCLE VIOLATIONS", "18", "OVERTAKING (PASSING)", "19", "PARKING VIOLATIONS", "20", "REPORTING VIOLATIONS (TRAFFIC VIOLATIONS)", "21", "RIGHT OF WAY", "22", "SEATBELT / HELMET USAGE", "23", "SIGNAL INTENTION", "24", "SPEEDING", "25", "TRAFFIC SIGNS, SIGNALS, AND ROAD MARKINGS", "26", "TURNING VIOLATIONS", "27", "VEHICLE/EQUIPMENT DEFECTS", "28", "WRONG SIDE/WRONG WAY", "29", "OTHER", "30", "NONE", "93", "UNDETERMINED - FAIL BR", "94", "REPORTED INVALID", "95", "NOT REPORTED" ) Charge <- cbind.data.frame(split(Charge, rep(1:2, times=length(Charge)/2)), stringsAsFactors=F) names(Charge) <- c("Charge_Cat_ID", "Charge") knitr::opts_chunk$set(echo = TRUE) ``` ```{r Read files} filenames <- list.files(path = inpath, pattern="*csv$") filenames <- paste(inpath, filenames, sep="/") DF <- read_csv(filenames[1], col_types = cols(.default = col_character())) for (i in filenames[-1]) { print(i) df <- read_csv(i, col_types = cols(.default = col_character())) DF <- full_join(DF, df) } df <- DF %>% select(Crash_ID, Crash_Fatal_Fl, Crash_Date, Crash_Time, Rpt_City_ID, Rpt_Block_Num, Rpt_Street_Pfx, Rpt_Street_Name, Rpt_Street_Sfx, At_Intrsct_Fl, Latitude, Longitude, Street_Name, Street_Nbr, Tot_Injry_Cnt, Death_Cnt, Harm_Evnt_ID, Crash_Sev_ID) df <- left_join(df, City, by="Rpt_City_ID") df <- left_join(df, Severity, by="Crash_Sev_ID") df <- left_join(df, Harm, by="Harm_Evnt_ID") # Create datetime df <- df %>% mutate(Date=mdy_hm(paste(Crash_Date, Crash_Time))) # df <- df %>% # rename(City=City.x, Severity=Severity.x) %>% # select(-City.y, -Severity.y) # df <- left_join(df, Charge, by="Charge_Cat_ID") # Save raw data # saveRDS(DF, paste(path, "Raw_Crash_Data_2015_2024.rds", sep="/")) ``` Do some quality control checks ```{r Look for nulls} # Look for NA's in the various fields df %>% map_df(function(x) sum(is.na(x))) %>% gather(feature, num_nulls) %>% print(n = 100) ``` ```{r Look at Crash_Fatal_Fl} sort(unique(df$Crash_Fatal_Fl)) ``` ```{r Look at Crash_Date} sort(unique(df$Crash_Date)) %>% head() # make it a date df$Crash_Date <- mdy(df$Crash_Date) df %>% ggplot(aes(Crash_Date)) + geom_histogram() ``` ```{r Look at Crash_Time} sort(unique(df$Crash_Time)) %>% head() # make it a time df <- df %>% mutate(Crash_Time=ymd_hm(paste("2000/1/1",Crash_Time))) df %>% ggplot(aes(Crash_Time)) + geom_histogram() ``` ```{r Look at City ID} sort(unique(df$Rpt_City_ID)) ``` ```{r Look at Rpt_Blk_Num} sort(unique(df$Rpt_Block_Num)) %>% head() # Show all non-numeric addresses df$Rpt_Block_Num[grepl("\\D+", df$Rpt_Block_Num)] %>% head(20) # Remove non-numeric stuff df$Rpt_Block_Num <- str_replace(df$Rpt_Block_Num, " BLOCK","") df$Rpt_Block_Num <- str_replace_all(df$Rpt_Block_Num, "O","0") df$Rpt_Block_Num <- str_replace_all(df$Rpt_Block_Num, "^\\d+-","") df$Rpt_Block_Num <- str_replace_all(df$Rpt_Block_Num, "PP","00") df$Rpt_Block_Num <- str_replace_all(df$Rpt_Block_Num, " 1/2","") df$Rpt_Block_Num <- str_replace(df$Rpt_Block_Num, "\\D+","") # Show all non-numeric addresses df$Rpt_Block_Num[grepl("\\D+", df$Rpt_Block_Num)] %>% head(20) df$Rpt_Block_Num <- str_replace(df$Rpt_Block_Num, "0CK","") df$Rpt_Block_Num <- str_replace(df$Rpt_Block_Num, "0\\D+","") df$Rpt_Block_Num[grepl("\\D+", df$Rpt_Block_Num)] %>% head(20) ``` ```{r Look at Rpr_Street_Pfx} sort(unique(df$Rpt_Street_Pfx)) ``` ```{r Look at Rpt_Street_Sfx} sort(unique(df$Rpt_Street_Sfx)) ``` ```{r Look at Severity} sort(unique(df$Severity)) ``` ```{r Look at Harmed} sort(unique(df$Harmed)) df %>% ggplot(aes(x=Harmed)) + geom_bar() + coord_flip() ``` ```{r} # Save file out # saveRDS(df, paste(path, "Cleaned_2015_2024.rds", sep="/")) ```