Relational Database Practice

Learning Objectives

• Merge datasets on a key
• Summarize merged dataset

Structure of FARS database

Prepare your project

1. Create a new R script file in your project folder
2. Add a heading
3. Load tidyverse
4. Download the datasets

Datasets we will use

• Accident
• Vehicle
• Person

Load datasets

accident <- read_csv("Data/fars2015nys_accident.csv", 
                     col_types="cnccnccnnnnnnncccccc") 
vehicle <- read_csv("Data/fars2015nys_vehicle.csv", 
                    col_types="ccccncnnccnccc") 
person <- read_csv("Data/fars2015nys_person.csv", 
                   col_types="cccncccccccccc") 

Reduce your variables

Remove the following extra variables before merging.

• Accident: latitude and longitude
• Vehicle: make, model, year, vehicle identification number, deformed, and travel speed
• Person: drinking, drugs, dead on arrival, and seat position

Code for removing variables

Hint: use select().

accident <- accident %>% 
  select( -LATITUDE, -LONGITUD )

The code below is a variation on what you've already learned.

vehicle <- vehicle %>% 
  select( -(MAKE:VIN), -(TRAV_SP:DEFORMED) )

person <- person %>% 
  select( -(DRINKING:DOA), -SEAT_POS )

Variation 1: Only people in cars

Suppose you only want vehicle occupants. How will you join or filter the datasets to exclude everyone else?

One possible solution is below.

my_peopleincars <- accident %>% 
  full_join(vehicle, by = "ST_CASE") %>%
  inner_join(person, by = c("ST_CASE", "VEH_NO", "ST_CO_FIPS"))

Variation 2: only pedestrians

Multiple possible methods:

1. Select people from the full dataset who were not in the vehicle dataset.
2. Select only non-drivers/non-passengers in person dataset, then left join to the accident dataset.
3. Or think of something else.

Code to isolate pedestrians

## method 1
my_pedestrians1 <- my_fulldata %>% 
  filter( is.na(NUMOCCS) )  # I picked a random variable 
                            # unique to the vehicle dataset.
                            # Why might this not work?

## method 2
my_pedestrians2 <- person %>% 
  filter( !PER_TYP %in% c("driver", "passenger") ) %>%
  left_join(accident, by = c("ST_CASE", "ST_CO_FIPS"))

What's going on?

What's up with the discrepancy?
Let's learn another useful function.

pedestrian_test <- my_pedestrians2 %>% 
  anti_join(my_pedestrians1, my_pedestrians2, by=c("ST_CASE", "PER_NO"))

anti_join() selects all cases that are in only one dataset.
Type ?anti_join to learn more.

Check your data

Check if your dataset only contains pedestrians. Which variable could you use?

I will provide two solutions on the next slide, one with base R and one with tidyverse.

Code to check your data

You could use base R,

table(my_pedestrians2$VEH_NO)

... or you could use tidyverse.

my_pedestrians2 %>% group_by(VEH_NO) %>% summarize(total = n())

Explore: why were pedestrians hit?

Let's explore time of day

The dataset includes hour and daylight. Your question would dictate which is more appropriate. My question is,
"Are pedestrians most likely to be hit during the day, at night, or in between?"

To answer this, evaluate the variable LGT_COND. Create a summary table with count and proportion of pedestrian deaths by time of day.

Code for time of day

my_pedestrians3 %>% group_by(LGT_COND) %>% 
  summarize(total = n(), percent = 100*total/nrow(.))

How could we change the code to look at time of day by whether the pedestrian died?

my_pedestrians3 %>% group_by(LGT_COND, died = INJ_SEV=="fatal") %>% 
  summarize(total = n(), percent = 100*total/nrow(.))

Your turn!

Investigate one or more of these variables.

• county
• location relative to the road
• type of intersection by whether on foot
• type of road
• weather by time of day

Recode:

• Was it an intersection? yes/no
• Was the weather clear? yes/no

Raise your hand if you have questions or need help.

And Now You Know!