Raw Table

First, we read in our data and create a variable calculating the length of the felony in days.

Table 1. Reading in Dataset

felonies = readRDS(file = "./data/sex_drug_weapons.rds")


knitr::kable(head(felonies[1:5]))

knitr::kable(head(felonies[6:10])) 

knitr::kable(head(felonies[11:15]))

Note that not every observation has a value for cmplnt_to_dt. This could be due to several factors - perhaps the crime was never closed (i.e., it remained an ongoing crime) or perhaps the city was not able to record the value for that variable for whatever reason. To remedy this issue, we take on two approaches:

• We input the end date for the crime as the difference between the end of 2017 and the start date of the occurrence of the crime (cmplnt_fr_dt). This makes sense if the crime remained ongoing until the end of 2017 or beyond. Our resulting dataset is time_data.
• We exclude missing values for end dates (denoted by ‘NA’). This makes sense if the city was not able to record the end dates for the crime for whatever reason. Our resulting dataset is time_data2.
  

We will examine whether the average length of reported felonies differs when we use these two approaches.

Approach 1

In the table below, we input the end dates for any crime with an “NA” for the variable cmplnt_to_dt. The first few rows of the resulting dataset, time_data, is shown below.

Table 2. Input End Dates for Crime Occurrence

time_data = felonies %>%
  mutate(crime_group = forcats::fct_relevel(crime_group, "Drug-Related"),
         boro_nm = forcats::fct_relevel(boro_nm, "manhattan")) %>% 
  janitor::clean_names() %>% 
  mutate(time_diff2 = (as.numeric(cmplnt_to_dt - cmplnt_fr_dt, units = "days", 
                       na.rm = TRUE))) %>% 
  mutate(time_diff2 = if_else(is.na(time_diff2), as.Date("2017-12-31") 
         - as.Date(cmplnt_fr_dt), time_diff2)) %>%   
  select(time_diff2, boro_nm, crime_group)

knitr::kable(head(time_data))

Approach 2

Our second approach involves excluding any observations with missing end dates for crimes. The first few rows of the resulting dataset, time_data2, is shown in the table below.

Table 3. Exclude Missing End Dates for Crime Occurrence

time_data2 = felonies %>%
  mutate(crime_group = forcats::fct_relevel(crime_group, "Drug-Related"),
         boro_nm = forcats::fct_relevel(boro_nm, "manhattan")) %>% 
  janitor::clean_names() %>% 
  mutate(time_diff2 = (as.numeric(cmplnt_to_dt - cmplnt_fr_dt, units = "days", 
                       na.rm = FALSE))) %>% 
  select(time_diff2, boro_nm, crime_group) %>% 
  filter(!is.na(time_diff2))

knitr::kable(head(time_data2))

Average Length by Crime Group

We then create tables showing the average length of felonies in days by borough and crime group for both approaches. Notice the dramatic change in both the counts and average length of felonies between the approaches.

Table 4. Average Length of Felonies by Crime Group

tidy1 = time_data %>% 
  rename(`Crime Group` = crime_group) %>% 
  group_by(`Crime Group`) %>%
  summarise('Count with End Date' = n(), 
            `Avg. Length With End Date` = mean(time_diff2),
            `SD With End Date` = sd(time_diff2))

tidy2 = time_data2 %>% 
  rename(`Crime Group` = crime_group) %>% 
  group_by(`Crime Group`) %>%
  summarise('Count w/o End Date' = n(), 
            `Avg. Length Excluding NAs` = mean(time_diff2),
            `SD Excluding NAs` = sd(time_diff2))

merged_table <- merge(tidy1, tidy2, by = c("Crime Group"))
knitr::kable(merged_table)

Average Length by Borough

Table 5. Average Length of Felonies by Borough

tidy3 = time_data %>% 
  filter(!is.na(boro_nm)) %>% 
  rename(Borough = boro_nm) %>% 
  group_by(Borough) %>%
  summarise('Count with End Date' = n(), 
            `Avg. Length With End Date` = mean(time_diff2), 
            `SD With End Dates` = sd(time_diff2))

tidy4 = time_data2 %>% 
  filter(!is.na(boro_nm)) %>% 
  rename(Borough = boro_nm) %>% 
  group_by(Borough) %>%
  summarise('Count w/o End Date' = n(), 
            `Avg. Length Excluding NAs` = mean(time_diff2), 
            `SD Excluding NAs` = sd(time_diff2))

merged_table2 <- merge(tidy3, tidy4, by = c("Borough"))
knitr::kable(merged_table2)