nh_charge_type_analysis.Rmd

---
title: "Charge Analysis"
output: 
  distill::distill_article:
    toc: true
    toc_depth: 3
    self_contained: false
---

**Counties Included**: All New Hampshire counties except Grafton and Strafford. Strafford is excluded because their data does not include any charge information (neither charge code nor charge description). 

The following analysis uses jail administrative data submitted directly to CSG in addition to a NH offense look-up table with offense codes, descriptions, and degrees (e.g., Class A Misdemeanor, etc.). However, we will not be able to analyze severity/degree of charges with the data we've received. With only offense statute and description, we can’t accurately tell if a given charge is a misdemeanor or felony (or even class A misdemeanor versus class B misdemeanor). For this level of detail, we would need either the ‘Smart Code’ or the ‘ODDSY Code’ – which may not even be known at the time of booking. For instance, there are 8 entries for ‘criminal trespass’/’statute ‘635:2’ in the look-up file – which degrees ranging from violations to class A and B misdemeanors to class B felonies. 

It appears that the best option, at this point, is to categorize the charges into violent, public order, drugs/alcohol, property, and probation/parole violation. Drawing from approaches used by the NH Department of Safety (see https://crimestats.dos.nh.gov/public/Dim/dimension.aspx) as well as FBI definitions for both NIBRS and UCR data (see https://www.waspc.org/assets/CJIS/trainingmanualsandreference/nibrsvssummaryoverview.pdf), I categorized certain offenses into these categories. For full list of which offenses are grouped into each crime type, see table below.

**Business Rules**: When cleaning the charge data, I first de-duplicated by individual (id + inmate_id), booking (booking_id), and charge (either charge_code or charge_desc depending on data availability). There appear to be some duplicates across these fields, but we have no way of knowing if two identical charges for the same booking represents a duplicated entry or multiple counts of the same charge. Unfortunately, this will likely just be a limitation of this analysis. After joining the jail charge data to the updated look-up table with crime type data, I de-duplicated the file again by individual and booking -- keeping the most serious charge type. Here is the crime type hierarchy (in order of severity) I used for this decision: violent, property, drug/alcohol, public order, violation of probation/parole, Failure to Appear/Bail, Temporary Hold, and then other/missing categories. One limitation is that we are not able to account for degree/severity of charge (felony vs. misdemeanor) with this approach. 


**Remaining Questions for Superintendents**:

* There is missing charge code/charge description data across most counties. How should we interpret this? Belknap seems to have the highest rates of missingness with about 11% of non-PC hold booking have no charge code or charge description. In general, how should we interpret missing charge data? Why is it missing? Is it missing at random? 

* Is there consistency in when/how charges are entered as probation or parole violations? If there is not an additional charge linked to the booking, does this likely indicate a technical violation? Or does it vary by county?

<br>

```{r include=FALSE}
### call libraries
library(easypackages)
libraries("tidyverse","foreign","lubridate","reshape2", "ggplot2","RColorBrewer","knitr","forcats","openxlsx","statar","svDialogs","xlsx", "magrittr","stringr", "data.table","janitor","kableExtra","leaflet", "readr", "rmarkdown","rowr", "gganimate", "gifski","tidycensus","sf","htmltools","acs","tigris","mapview","rgeos", "ggrepel", "censusxy","gdata","lavaan","mclust","tmap","scales", "raster","rgeos","gmapsdistance","viridis","cowplot","lintr","leaflet.extras","censusapi","data.table","gridGraphics","readxl","haven","ggridges","extrafont","extrafontdb","datamodelr","Lahman","DiagrammeR","fs","readxl","rsvg","V8","ragg","ggtext","csgjcr","distill","gtsummary")

# Load functions, packages, and data
source("code/00_library.R")
source("code/01_functions.R")
source("code/rdas.R")
```

```{r setup, include=FALSE}
### set chunk output
knitr::opts_chunk$set(
  echo = TRUE,
  dev = "ragg_png",
  cache = FALSE
  )

### import cleaned charge analytic file (for all eight counties with charge data)
load(paste0(sp_data_path, "/Data/analysis/r_data/offenses_clean/nh_eight_county_charge_clean_final.Rda", sep = ""))

### import and clean raw charge codes look-up table
charge_codes_crime_type_lookup_for_table <- read_excel(paste0(sp_data_path, "/Data/Offense Information/CPI_DMV_COURT_crime_type_clean.xls"), 
                                sheet = "charge_lookup_crime_type_full") %>%
  clean_names() %>%
  mutate(descriptor_lookup = str_to_title(descriptor),
         statute_title_lookup = str_to_title(statute_title),
         charge_code_lookup = str_to_title(offense_statute)) %>% 
### Based on the jail charge data we've received, I don't think we can look at severity/degree of charges
### With only offense statute and description, we can't accurately tell if a given charge is a misdemeanor or felony (or even class A misdemeanor versus class B misdemeanor)
### For this level of detail, we'd need either the 'Smart Code' or the 'ODDSY Code' - which may not even be known at the time of booking
### For instance, there are 8 entries for 'criminal trespass'/'statute '635:2' in the lookup file - which degrees ranging from violations to class A and B misdemeanors to class B felonies
### I think the best option, at this point, is to just categorize the charges into violent, public order, drugs/alcohol, property, FTA/Bail, temporary hold
  distinct(statute_title_lookup,
           crime_type,
           .keep_all = TRUE) %>% ### de-dup by offense information and crime type 
  dplyr::select(charge_code_lookup, 
                descriptor_lookup, 
                statute_title_lookup, 
                crime_type_lookup=crime_type) 
```

# Raw Charges and Coded Charge Type

Here is a list of all charges that were categorized as either violent, property, drug/alcohol, public order, failure to appear, temporary hold, or probation/parole violations. These are not necessarily charges associated with jail bookings or individuals in the jail data files. 

Note: The charge descriptions still need to be cleaned/standardized in some cases (e.g., "Willful Concealment" and "Willful Concealment and Shoplifting")

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create table of unique statute titles and associated crime/charge type (only for charges with crime type value)
table_charge_codes_crime_type_lookup <- charge_codes_crime_type_lookup_for_table %>% 
  filter(!is.na(crime_type_lookup)) %>% 
  dplyr::group_by(statute_title_lookup,
                  crime_type_lookup) %>% 
  dplyr::summarise(`Unique Descriptions (N)` = n_distinct(descriptor_lookup)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description` = statute_title_lookup,
                `Charge Type` = crime_type_lookup) %>% 
  dplyr::select(-`Unique Descriptions (N)`)

### print table via kableextra
kable(table_charge_codes_crime_type_lookup, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l")
```

<br><br>

# Bookings by Charge Type (most serious) across New Hampshire

Note: I am considering a booking to be 'unique' if the following values are together unique: id, inmate_id, booking_id, county (including county as we can't verify that each county is using different IDs for individuals and bookings). The count reported in the following tables is the unique number of bookings for which the most serious charge type is a violent charge, property charge, etc.

```{r include=FALSE,message=FALSE,warning=FALSE}
nh_eight_county_charge_clean_final_id <- nh_eight_county_charge_clean_final %>%
  mutate(unique_booking_id_nh = paste0(id,
                                       inmate_id,
                                       booking_id,
                                       county)) %>% 
  mutate(descriptor_lookup = str_to_title(descriptor_lookup),
         statute_title_lookup = str_to_title(statute_title_lookup)) ### clean up descriptor and statute title for tables
```

<br>

### Most Common Charges across Charge Type, Statewide (top 20)

Note: This table excludes non-PC holds as well as charges that were not categorized into a specific charge type. The charge descriptions still need to be cleaned/standardized in some cases (e.g., "Willful Concealment" and "Willful Concealment and Shoplifting").

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample excluding pc-holds
unique_bookings_all_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$pc_hold=="Non-PC Hold" & nh_eight_county_charge_clean_final_id$statute_title_lookup!="Missing"])

### unique bookings per charge description
df_nh_all_offense_desc_count <- nh_eight_county_charge_clean_final_id %>% 
  filter(pc_hold=="Non-PC Hold",
         statute_title_lookup!="Missing") %>% 
  dplyr::group_by(statute_title_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_all_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  arrange(desc(`Unique Bookings (N)`)) %>% 
  head(20) %>% 
  dplyr::rename(`Charge Description` = statute_title_lookup) 

### print table via kableextra
kable(df_nh_all_offense_desc_count, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") 
```

<br>

### Violent Offenses, Statewide

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample
unique_bookings_violent_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$crime_type_lookup=="Violent"])

### unique bookings per charge description
df_nh_violent_offense_desc_count <- nh_eight_county_charge_clean_final_id %>% 
  filter(crime_type_lookup=="Violent") %>% 
  dplyr::group_by(statute_title_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_violent_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description` = statute_title_lookup) 

### overall
df_nh_violent_offense_count_overall <- nh_eight_county_charge_clean_final_id %>% 
 filter(crime_type_lookup=="Violent") %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_violent_denom,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description` = "Overall")

### combine tables for kable
table_nh_violent_offense_desc_count_final <- rbind(df_nh_violent_offense_desc_count,
                                                   df_nh_violent_offense_count_overall)

### print table via kableextra
kable(table_nh_violent_offense_desc_count_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(28, bold = TRUE)
```

<br>

### Property Offenses, Statewide

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample
unique_bookings_property_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$crime_type_lookup=="Property"])

### unique bookings per charge description
df_nh_property_offense_desc_count <- nh_eight_county_charge_clean_final_id %>% 
  filter(crime_type_lookup=="Property") %>% 
  dplyr::group_by(statute_title_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_property_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description` = statute_title_lookup) 

### overall
df_nh_property_offense_count_overall <- nh_eight_county_charge_clean_final_id %>% 
 filter(crime_type_lookup=="Property") %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_property_denom,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description` = "Overall")

### combine tables for kable
table_nh_property_offense_desc_count_final <- rbind(df_nh_property_offense_desc_count,
                                                    df_nh_property_offense_count_overall)

### print table via kableextra
kable(table_nh_property_offense_desc_count_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(28, bold = TRUE)
```

<br>

### Drug/Alcohol Offenses, Statewide

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample
unique_bookings_drug_alc_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$crime_type_lookup=="Drug/Alcohol"])

### unique bookings per charge description
df_nh_drug_alc_offense_desc_count <- nh_eight_county_charge_clean_final_id %>% 
  filter(crime_type_lookup=="Drug/Alcohol") %>% 
  dplyr::group_by(statute_title_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_drug_alc_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description` = statute_title_lookup) 

### overall
df_nh_drug_alc_offense_count_overall <- nh_eight_county_charge_clean_final_id %>% 
 filter(crime_type_lookup=="Drug/Alcohol") %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_drug_alc_denom,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description` = "Overall")

### combine tables for kable
table_nh_drug_alc_offense_desc_count_final <- rbind(df_nh_drug_alc_offense_desc_count,
                                                    df_nh_drug_alc_offense_count_overall)

### print table via kableextra
kable(table_nh_drug_alc_offense_desc_count_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(13, bold = TRUE)
```

<br>

### Drug/Alcohol Offenses, Statewide 

Note: This table is broken down further as the broader categories above don't say much for drug/alcohol charges

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample
unique_bookings_drug_alc_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$crime_type_lookup=="Drug/Alcohol"])

### unique bookings per charge description
df_nh_drug_alc_offense_desc_count_2 <- nh_eight_county_charge_clean_final_id %>% 
  filter(crime_type_lookup=="Drug/Alcohol") %>% 
  dplyr::group_by(descriptor_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_drug_alc_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description (Detailed)` = descriptor_lookup) 

### overall
df_nh_drug_alc_offense_count_overall_2 <- nh_eight_county_charge_clean_final_id %>% 
 filter(crime_type_lookup=="Drug/Alcohol") %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_drug_alc_denom,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description (Detailed)` = "Overall")

### combine tables for kable
table_nh_drug_alc_offense_desc_count_final_2 <- rbind(df_nh_drug_alc_offense_desc_count_2,
                                                      df_nh_drug_alc_offense_count_overall_2)

### print table via kableextra
kable(table_nh_drug_alc_offense_desc_count_final_2, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(57, bold = TRUE)
```

<br>

### Public Order Offenses, Statewide

Note: 'Transporting Alcoholic Beverages' appears to be the same as 'Open Container'

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample
unique_bookings_public_order_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$crime_type_lookup=="Public Order"])

### unique bookings per charge description
df_nh_public_order_offense_desc_count <- nh_eight_county_charge_clean_final_id %>% 
  filter(crime_type_lookup=="Public Order") %>% 
  dplyr::group_by(statute_title_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_public_order_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description` = statute_title_lookup) 

### overall
df_nh_public_order_offense_count_overall <- nh_eight_county_charge_clean_final_id %>% 
 filter(crime_type_lookup=="Public Order") %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_public_order_denom,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description` = "Overall")

### combine tables for kable
table_nh_public_order_offense_desc_count_final <- rbind(df_nh_public_order_offense_desc_count,
                                                        df_nh_public_order_offense_count_overall)

### print table via kableextra
kable(table_nh_public_order_offense_desc_count_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(15, bold = TRUE)
```

<br>

### Probation/Parole Violations, Statewide

Note: If charge description is missing, charge code or charge statute name was utlized for coding

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample
unique_bookings_vop_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$crime_type_lookup=="Probation/Parole Violation"])

### unique bookings per charge description
df_nh_vop_offense_desc_count <- nh_eight_county_charge_clean_final_id %>% 
  filter(crime_type_lookup=="Probation/Parole Violation") %>% 
  dplyr::group_by(descriptor_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_vop_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description` = descriptor_lookup) 

### overall
df_nh_vop_offense_count_overall <- nh_eight_county_charge_clean_final_id %>% 
 filter(crime_type_lookup=="Probation/Parole Violation") %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_vop_denom,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description` = "Overall")

### combine tables for kable
table_nh_vop_offense_desc_count_final <- rbind(df_nh_vop_offense_desc_count,
                                               df_nh_vop_offense_count_overall)

### print table via kableextra
kable(table_nh_vop_offense_desc_count_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(14, bold = TRUE)
```

<br>

### Failure to Appear/Bail, Statewide

Note: If charge description is missing, charge code or charge statute name was utilized for coding

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample
unique_bookings_fta_bail_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$crime_type_lookup=="FTA/Bail"])

### unique bookings per charge description
df_nh_fta_bail_offense_desc_count <- nh_eight_county_charge_clean_final_id %>% 
  filter(crime_type_lookup=="FTA/Bail") %>% 
  dplyr::group_by(descriptor_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_vop_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description` = descriptor_lookup) 

### overall
df_nh_fta_bail_offense_count_overall <- nh_eight_county_charge_clean_final_id %>% 
 filter(crime_type_lookup=="FTA/Bail") %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_vop_denom,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description` = "Overall")

### combine tables for kable
table_nh_fta_bail_offense_desc_count_final <- rbind(df_nh_fta_bail_offense_desc_count,
                                               df_nh_fta_bail_offense_count_overall)

### print table via kableextra
kable(table_nh_fta_bail_offense_desc_count_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(13, bold = TRUE)
```

<br>

### Temporary Hold, Statewide

Note: If charge description is missing, charge code or charge statute name was utilized for coding

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for sub-sample
unique_bookings_temp_hold_bail_denom <- n_distinct(nh_eight_county_charge_clean_final_id$unique_booking_id_nh[nh_eight_county_charge_clean_final_id$crime_type_lookup=="Temporary Hold"])

### unique bookings per charge description
df_nh_temp_hold_offense_desc_count <- nh_eight_county_charge_clean_final_id %>% 
  filter(crime_type_lookup=="Temporary Hold") %>% 
  dplyr::group_by(descriptor_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_vop_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description` = descriptor_lookup) 

### overall
df_nh_temp_hold_offense_count_overall <- nh_eight_county_charge_clean_final_id %>% 
 filter(crime_type_lookup=="Temporary Hold") %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_vop_denom,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description` = "Overall")

### combine tables for kable
table_nh_temp_hold_offense_desc_count_final <- rbind(df_nh_temp_hold_offense_desc_count,
                                               df_nh_temp_hold_offense_count_overall)

### print table via kableextra
kable(table_nh_temp_hold_offense_desc_count_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(13, bold = TRUE)
```

<br>

### Other/Uncoded Charges, Statewide (20 most common charges)

Note: These are charges that were not grouped into either violent, property, drug/alcohol, public order, FTA/bail, temporary hold, or probation/parole violation categories based on the resources we used as guides. We may want to group additional charges into one of these categories, though. Values of "NA" indicate the charge data provided by jails was missing.

```{r echo=FALSE,message=FALSE,warning=FALSE}
### create df without pc holds and recode missing charge types as other (for now)
nh_eight_county_charge_clean_final_crime_type_recode_non_pc <- nh_eight_county_charge_clean_final_id %>% 
  filter(pc_hold=="Non-PC Hold") %>% 
  mutate(crime_type_clean = ifelse(is.na(crime_type_lookup)==TRUE,
                                   "Other",
                                   crime_type_lookup))

### create denominator -- unique bookings for sub-sample
unique_bookings_other_denom <- n_distinct(nh_eight_county_charge_clean_final_crime_type_recode_non_pc$unique_booking_id_nh[nh_eight_county_charge_clean_final_crime_type_recode_non_pc$crime_type_clean=="Other"])

### unique bookings per charge description
df_nh_other_offense_desc_count_top_20 <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  filter(crime_type_clean=="Other") %>% 
  dplyr::group_by(descriptor_lookup) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_other_denom,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Description (Detailed)` = descriptor_lookup) %>% 
  arrange(desc(`Unique Bookings (N)`)) %>% 
  head(20) ### take top 20

### write out all offenses with 'other' flag to excel for review by policy team

# ### unique bookings per charge description
# df_nh_other_offense_desc_count_raw <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
#   filter(crime_type_clean=="Other") %>% 
#   dplyr::group_by(descriptor_lookup) %>% 
#   dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
#                    `Bookings, Percent of Sample (%)` = scales::percent(`Unique Bookings (N)`/unique_bookings_other_denom,
#                                                                        accuracy = .1)) %>% 
#   ungroup() %>% 
#   dplyr::rename(`Charge Description (Detailed)` = descriptor_lookup) %>% 
#   arrange(desc(`Unique Bookings (N)`)) 

# ### set sharepoint path using csgjcr::csg_sp_path function
# sharepoint_for_policy <- csg_sp_path("JR_NH/Data/for policy team/")
# 
# ### export
# write.xlsx(df_nh_other_offense_desc_count_raw,
#            file=file.path(sharepoint_for_policy,"nh_raw_offense_descriptions_not_categorized.xlsx"))


### print table via kableextra
kable(df_nh_other_offense_desc_count_top_20, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") 
```

<br><br>

# Analysis

<br>

## Charge Data Tables (most serious charge)


### Table 1. Charge Type by Jail (Non-PC Holds Only)

```{r echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings per charge type by county
df_county_level_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::group_by(county) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`County` = county) 

### overall bookings by charge type for nh
df_county_level_charge_type_summary_statewide <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  mutate(`County` = "Statewide")

### combine tables for kable
table_county_level_charge_type_summary_final <- rbind(df_county_level_charge_type_summary,
                                               df_county_level_charge_type_summary_statewide)

### print table via kableextra
kable(table_county_level_charge_type_summary_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(9, bold = TRUE)
```

<br><br>

### Table 2. Charge Type over Time (Non-PC Holds Only)

Note: These booking frequencies by charge type are grouped by fiscal year of booking

```{r echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings per charge type by fiscal year
df_fy_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::group_by(fy) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`Fiscal Year` = fy) 

### overall bookings by charge type for nh
df_fy_charge_type_summary_statewide <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  mutate(`Fiscal Year` = "Overall")

### combine tables for kable
table_fy_charge_type_summary_final <- rbind(df_fy_charge_type_summary,
                                               df_fy_charge_type_summary_statewide)

### print table via kableextra
kable(table_fy_charge_type_summary_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(4, bold = TRUE)
```


<br><br>

### Table 2.5. Charge Type by Race/Ethnicity (Non-PC Holds Only)

Note: These booking frequencies by charge type are grouped by fiscal year of booking

```{r echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings per charge type by fiscal year
df_race_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>%
  filter(!is.na(race)) %>% 
  dplyr::group_by(race) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`Race/Ethnicity` = race) 

### overall bookings by charge type for nh
df_race_charge_type_summary_statewide <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
    filter(!is.na(race)) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  mutate(`Race/Ethnicity` = "Overall")

### combine tables for kable
table_race_charge_type_summary_final <- rbind(df_race_charge_type_summary,
                                               df_race_charge_type_summary_statewide)

### print table via kableextra
kable(table_race_charge_type_summary_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(6, bold = TRUE)
```

<br><br>

### Table 3a. Charge Type for Individuals who are High Utilizers (based on all three years of data) 

Note: This output excludes charge type analysis for PC bookings, as no charge information exists, but the high utilizer percentile grouping (developed by Mari) does include PC bookings -- does this make sense?

Below is the preliminary grouping I am using to confirm with the team. With this approach, this is not overlap of groups -- one individual is in one of the four groups (i.e., someone in the top 1% of jail entrances is only included in the 1% grouping, not the 5% and 10% grouping). I'm thinking that this may help us better see any differences between groupings of high utilizers, if they exist. Open to suggestions, though!

* **Top 1%:** individual is in top 1% of jail entrances for all 3 years of sample

* **Top 5%:** individual is in top 5% of jail entrances for all 3 years of sample (really this is 1% to 5%)

* **Top 10%:** individual is in top 10% of jail entrances for all 3 years of sample (really this is 5% to 10%)

* **Non-HU:** individual is not in top 10% of jail entrances for all 3 years of sample


```{r echo=FALSE,message=FALSE,warning=FALSE}
### to recode high utlizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive = case_when( 
    high_utilizer_10_pct=="No" ~ 4,
    high_utilizer_10_pct=="Yes" & high_utilizer_5_pct=="No" & high_utilizer_1_pct=="No" ~ 3,
    high_utilizer_5_pct=="Yes" & high_utilizer_1_pct=="No" ~ 2,
    high_utilizer_1_pct=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive = factor(hu_group_exclusive,
         levels = c(1,2,3,4),
         labels = c("Top 1%", "Top 5%", "Top 10%", "Non-HU")))
    

### unique bookings per charge type by HU grouping
df_hu_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode %>% 
  dplyr::group_by(hu_group_exclusive) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`High Utilizer Percentiles` = hu_group_exclusive) 

### overall bookings by charge type for nh
df_hu_charge_type_summary_overall <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  mutate(`High Utilizer Percentiles` = "Overall")

### combine tables for kable
table_hu_charge_type_summary_final <- rbind(df_hu_charge_type_summary,
                                            df_hu_charge_type_summary_overall)

### print table via kableextra
kable(table_hu_charge_type_summary_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(5, bold = TRUE)
```

<br><br>

### Table 3b. Public Order Charge Description for Individuals who are High Utilizers (based on all three years of data) 

* **Top 1%:** individual is in top 1% of jail entrances for all 3 years of sample

* **Top 5%:** individual is in top 5% of jail entrances for all 3 years of sample (really this is 1% to 5%)

* **Top 10%:** individual is in top 10% of jail entrances for all 3 years of sample (really this is 5% to 10%)

* **Non-HU:** individual is not in top 10% of jail entrances for all 3 years of sample


```{r echo=FALSE,message=FALSE,warning=FALSE}
### create denominators -- unique bookings for sub-sample
unique_bookings_public_order_denom_top_1 <- n_distinct(nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$unique_booking_id_nh[nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$crime_type_lookup=="Public Order" & nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$hu_group_exclusive=="Top 1%"],
                                                       na.rm = TRUE)

unique_bookings_public_order_denom_top_5 <- n_distinct(nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$unique_booking_id_nh[nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$crime_type_lookup=="Public Order" & nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$hu_group_exclusive=="Top 5%"],
                                                       na.rm = TRUE)

unique_bookings_public_order_denom_top_10 <- n_distinct(nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$unique_booking_id_nh[nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$crime_type_lookup=="Public Order" & nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$hu_group_exclusive=="Top 10%"],
                                                       na.rm = TRUE)

unique_bookings_public_order_denom_non_hu <- n_distinct(nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$unique_booking_id_nh[nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$crime_type_lookup=="Public Order" & nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode$hu_group_exclusive=="Non-HU"],
                                                       na.rm = TRUE)

### unique bookings per charge type by HU grouping
df_hu_public_order_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode %>% 
  filter(crime_type_lookup=="Public Order") %>% 
  dplyr::group_by(statute_title_lookup) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Top 1% HU (Column %)` = scales::percent(n_distinct(unique_booking_id_nh[hu_group_exclusive=="Top 1%"])/unique_bookings_public_order_denom_top_1,
                                                                       accuracy = .1),
                   `Bookings for Top 5% HU (Column %)` = scales::percent(n_distinct(unique_booking_id_nh[hu_group_exclusive=="Top 5%"])/unique_bookings_public_order_denom_top_5,
                                                                       accuracy = .1),
                   `Bookings for Top 10% HU (Column %)` = scales::percent(n_distinct(unique_booking_id_nh[hu_group_exclusive=="Top 10%"])/unique_bookings_public_order_denom_top_10,
                                                                       accuracy = .1),
                   `Bookings for Top Non-HU (Column %)` = scales::percent(n_distinct(unique_booking_id_nh[hu_group_exclusive=="Non-HU"])/unique_bookings_public_order_denom_non_hu,
                                                                       accuracy = .1)) %>% 
  ungroup() %>%
  dplyr::rename(`Charge Description` = statute_title_lookup) 

### overall bookings by charge type for nh
df_hu_charge_type_summary_overall <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode %>% 
  filter(crime_type_lookup=="Public Order") %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Top 1% HU (Column %)` = scales::percent(n_distinct(unique_booking_id_nh[hu_group_exclusive=="Top 1%"])/unique_bookings_public_order_denom_top_1,
                                                                       accuracy = .1),
                   `Bookings for Top 5% HU (Column %)` = scales::percent(n_distinct(unique_booking_id_nh[hu_group_exclusive=="Top 5%"])/unique_bookings_public_order_denom_top_5,
                                                                       accuracy = .1),
                   `Bookings for Top 10% HU (Column %)` = scales::percent(n_distinct(unique_booking_id_nh[hu_group_exclusive=="Top 10%"])/unique_bookings_public_order_denom_top_10,
                                                                       accuracy = .1),
                   `Bookings for Top Non-HU (Column %)` = scales::percent(n_distinct(unique_booking_id_nh[hu_group_exclusive=="Non-HU"])/unique_bookings_public_order_denom_non_hu,
                                                                       accuracy = .1)) %>% 
  mutate(`Charge Description` = "Overall")

### combine tables for kable
df_hu_public_order_charge_type_summary_final <- rbind(df_hu_public_order_charge_type_summary,
                                            df_hu_charge_type_summary_overall)

### print table via kableextra
kable(df_hu_public_order_charge_type_summary_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(15, bold = TRUE)
```

<br><br>

```{r eval=FALSE,include=FALSE,message=FALSE,warning=FALSE}
### Table 4. Charge Type for Individuals who are High Utilizers (based on single fiscal year calculations) 

# Note: This output excludes charge type analysis for PC bookings, as no charge information exists, but the high utilizer percentile grouping (developed by Mari) does include PC bookings -- does this make sense?
# 
# Below is the preliminary grouping I am using to confirm with the team. With this approach, this is not overlap of groups -- one individual is in one of the four groups (i.e., someone in the top 1% of jail entrances is only included in the 1% grouping, not the 5% and 10% grouping). I'm thinking that this may help us better see any differences between groupings of high utilizers, if they exist. Open to suggestions, though!
# 
# * **Top 1%:** individual is in top 1% of jail entrances for the given fiscal year of booking
# 
# * **Top 5%:** individual is in top 5% of jail entrances for the given fiscal year of booking(really this is 1% to 5%)
# 
# * **Top 10%:** individual is in top 10% of jail entrances for the given fiscal year of booking (really this is 5% to 10%)
# 
# * **Non-HU:** individual is not in top 10% of jail entrances for the given fiscal year of booking

### to recode high utlizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode_fy <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive_fy = case_when( 
    high_utilizer_10_pct_fy=="No" ~ 4,
    high_utilizer_10_pct_fy=="Yes" & high_utilizer_5_pct_fy=="No" & high_utilizer_1_pct_fy=="No" ~ 3,
    high_utilizer_5_pct_fy=="Yes" & high_utilizer_1_pct_fy=="No" ~ 2,
    high_utilizer_1_pct_fy=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive_fy = factor(hu_group_exclusive_fy,
         levels = c(1,2,3,4),
         labels = c("Top 1%", "Top 5%", "Top 10%", "Non-HU")))
    

### unique bookings per charge type by HU grouping
df_hu_charge_type_summary_fy <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode_fy %>% 
  dplyr::group_by(hu_group_exclusive_fy) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`High Utilizer Percentiles (FY)` = hu_group_exclusive_fy) 

### overall bookings by charge type for nh
df_hu_charge_type_summary_overall_fy <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode_fy %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings for Violent Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Property Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Public Order Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                    `Bookings for Probation/Parole Violation (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for FTA/Bail (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="FTA/Bail"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Temporary Hold (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Temporary Hold"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1),
                   `Bookings for Other Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[crime_type_clean=="Other"])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  mutate(`High Utilizer Percentiles (FY)` = "Overall")

### combine tables for kable
table_hu_charge_type_summary_final_fy <- rbind(df_hu_charge_type_summary_fy,
                                            df_hu_charge_type_summary_overall_fy)

### print table via kableextra
kable(table_hu_charge_type_summary_final_fy, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(5, bold = TRUE)
```

<br><br>

## Charge Data Tables (% of bookings with a drug/alcohol charge)

Note: The following tables report on the percentage of bookings with at least one drug/alcohol charge, regardless of whether it was the most serious charge recorded.

<br>

### Table 5. Percent of Booking with any Drug/Alcohol Charge by Jail (Non-PC Holds Only)

```{r echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings with drug/alcohol charge by county
df_county_level_drug_alcohol_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::group_by(county) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings with any Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[booking_drug_alcohol_charge_present_flag==1])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`County` = county) 

### overall bookings with drug/alcohol charge  for nh
df_county_level_drug_alcohol_summary_statewide <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings with any Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[booking_drug_alcohol_charge_present_flag==1])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  mutate(`County` = "Statewide")

### combine tables for kable
table_county_level_drug_alcohol_summary_final <- rbind(df_county_level_drug_alcohol_summary,
                                               df_county_level_drug_alcohol_summary_statewide)

### print table via kableextra
kable(table_county_level_drug_alcohol_summary_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(9, bold = TRUE)
```

<br><br>

### Table 6. Percent of Bookings with any Drug/Alcohol Charge over Time (Non-PC Holds Only)

Note: These booking frequencies by charge type are grouped by fiscal year of booking

```{r echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings with drug/alcohol charge  by fiscal year
df_fy_drug_alcohol_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::group_by(fy) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings with any Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[booking_drug_alcohol_charge_present_flag==1])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`Fiscal Year` = fy) 

### overall bookings with drug/alcohol charge  for nh
df_fy_drug_alcohol_summary_statewide <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings with any Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[booking_drug_alcohol_charge_present_flag==1])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  mutate(`Fiscal Year` = "Overall")

### combine tables for kable
table_fy_drug_alcohol_summary_final <- rbind(df_fy_drug_alcohol_summary,
                                            df_fy_drug_alcohol_summary_statewide)

### print table via kableextra
kable(table_fy_drug_alcohol_summary_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(4, bold = TRUE)
```

<br><br>

### Table 7. Percent of Bookings with any Drug/Alcohol Charge for Individuals who are High Utilizers (based on all three years of data) 

Note: This output excludes charge type analysis for PC bookings, as no charge information exists, but the high utilizer percentile grouping (developed by Mari) does include PC bookings -- does this make sense?

Below is the preliminary grouping I am using to confirm with the team. With this approach, this is not overlap of groups -- one individual is in one of the four groups (i.e., someone in the top 1% of jail entrances is only included in the 1% grouping, not the 5% and 10% grouping). I'm thinking that this may help us better see any differences between groupings of high utilizers, if they exist. Open to suggestions, though!

* **Top 1%:** individual is in top 1% of jail entrances for all 3 years of sample

* **Top 5%:** individual is in top 5% of jail entrances for all 3 years of sample (really this is 1% to 5%)

* **Top 10%:** individual is in top 10% of jail entrances for all 3 years of sample (really this is 5% to 10%)

* **Non-HU:** individual is not in top 10% of jail entrances for all 3 years of sample


```{r echo=FALSE,message=FALSE,warning=FALSE}
### to recode high utlizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive = case_when( 
    high_utilizer_10_pct=="No" ~ 4,
    high_utilizer_10_pct=="Yes" & high_utilizer_5_pct=="No" & high_utilizer_1_pct=="No" ~ 3,
    high_utilizer_5_pct=="Yes" & high_utilizer_1_pct=="No" ~ 2,
    high_utilizer_1_pct=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive = factor(hu_group_exclusive,
         levels = c(1,2,3,4),
         labels = c("Top 1%", "Top 5%", "Top 10%", "Non-HU")))
    

### unique bookings with drug/alcohol charge by HU grouping
df_hu_drug_alcohol_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode %>% 
  dplyr::group_by(hu_group_exclusive) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings with any Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[booking_drug_alcohol_charge_present_flag==1])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`High Utilizer Percentiles` = hu_group_exclusive) 

### overall bookings with drug/alcohol charge  for nh
df_hu_drug_alcohol_summary_overall <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings with any Drug/Alcohol Charge (%)` = scales::percent(n_distinct(unique_booking_id_nh[booking_drug_alcohol_charge_present_flag==1])/`Unique Non-PC Bookings (N)`,
                                                                       accuracy = .1)) %>% 
  mutate(`High Utilizer Percentiles` = "Overall")

### combine tables for kable
table_hu_drug_alcohol_summary_final <- rbind(df_hu_drug_alcohol_summary,
                                            df_hu_drug_alcohol_summary_overall)

### print table via kableextra
kable(table_hu_drug_alcohol_summary_final, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
  kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") %>% 
  row_spec(5, bold = TRUE)
```

<br><br>

### Table 8. Average Length of Stay by Charge Type (Non-PC Holds Only)

Note: Table is ordered from largest to smallest median length of stay. **Should we only use median? A small number of very large length of stays (e.g. 2+ years) are pulling up the mean**

```{r echo=FALSE,message=FALSE,warning=FALSE}
### group by race and calculate both mean and median length of stays using los_max
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_los <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  group_by(crime_type_clean) %>% 
  summarise(`Overall Jail Bookings (N)` = n_distinct(unique_booking_id_nh),
            `Mean Length of Stay (Days)` = round(mean(los_max,
                                                na.rm = TRUE),
                                                digits = 1),
            `Median Length of Stay (Days)` = median(los_max,
                                                na.rm = TRUE)) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Type` = crime_type_clean) %>% 
  arrange(desc(`Median Length of Stay (Days)`), desc(`Mean Length of Stay (Days)`))
  
### print table via kableextra
kable(nh_eight_county_charge_clean_final_crime_type_recode_non_pc_los, 
      format.args = list(big.mark = ","), 
      align=rep('c')) %>%
   kable_styling(bootstrap_options = c("striped", 
                                       "hover", 
                                       "condensed", 
                                       "responsive"), 
                 row_label_position = "l") 
```

<br><br>

## Charge Data Visuals

Note: I am considering a booking to be 'unique' if the following values are together unique: id, inmate_id, booking_id, county (including county as we can't verify that each county is using different IDs for individuals and bookings).

<br>

### Figure 1. Charge Type across State (Non-PC Holds Only)

```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### create denominator -- unique bookings for entire df
unique_bookings_all_denom <- n_distinct(nh_eight_county_charge_clean_final_crime_type_recode_non_pc$unique_booking_id_nh)

### unique bookings per charge type
df_nh_charge_type_overall <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::group_by(crime_type_clean) %>% 
  dplyr::summarise(`Unique Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings, Percent of Sample (%)` = `Unique Bookings (N)`/unique_bookings_all_denom) %>% 
  ungroup() %>% 
  dplyr::rename(`Charge Type` = crime_type_clean) %>% 
  arrange(desc(`Unique Bookings (N)`)) 

### bar plot
ggplot(data = df_nh_charge_type_overall, aes(y = `Bookings, Percent of Sample (%)`, x = reorder(`Charge Type`,-`Unique Bookings (N)`), fill = forcats::fct_rev(`Charge Type`))) + 
  geom_bar(stat = "identity",  color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1),
                     limits = c(min(0), max(.3))) +
  geom_label(
    aes(
      y = `Bookings, Percent of Sample (%)`,
      label = scales::percent(`Bookings, Percent of Sample (%)`,
                              accuracy = 1),
      fontface = "bold"
    ),
    position = position_stack(vjust = .5),
    size = 5,
    colour = "white",
    show.legend = FALSE
  ) +
  scale_fill_manual(values = c("#167a9c","#167a9c","#167a9c","#167a9c","#167a9c","#167a9c","#167a9c","#167a9c","#167a9c")) +
  guides(fill = FALSE) +
  labs(x = NULL, y = NULL,
       fill = NULL,
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        # panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
    coord_flip() 

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_statewide_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 2. Charge Type by Jail (Non-PC Holds Only), Bar Plot

Note: These booking frequencies by charge type are grouped by county of booking and exclude FTA/Bail, temporary hold, and unclassified charges (i.e., "other")

```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings per charge type by county
df_county_level_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  filter(!crime_type_clean%in%c("Other",
                                "FTA/Bail",
                                "Temporary Hold")) %>% 
  dplyr::group_by(county) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Violent` = n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                   `Property` = n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                   `Drug/Alcohol` = n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                   `Public Order` = n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                   `Probation/Parole Violation` = n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`County` = county) 

### pivot df long for ggplot 
df_county_level_charge_type_summary_long <- df_county_level_charge_type_summary %>%
  gather(charge_type, percent, `Violent`:`Probation/Parole Violation`) %>% 
  mutate(percent_label = scales::percent(percent, 
                                         accuracy = 1)) %>% 
  mutate(charge_type_factor =  factor(charge_type,
                                      levels = c("Violent","Property","Drug/Alcohol","Public Order","Probation/Parole Violation")))


### ggplot
ggplot(data = df_county_level_charge_type_summary_long, 
       aes(y = percent, 
           x = `County`, 
           fill = forcats::fct_rev(charge_type_factor))) + 
  geom_bar(stat = "identity", 
           color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent) +
  scale_fill_manual(values = c("#167a9c",
                               "#293e5c",
                               "#b95826",
                               "#87C2D4",
                               "#557e39")) +
  guides(fill = guide_legend(reverse = TRUE,
                             title.position = "top",
                             label.position = "bottom",
                             keywidth = 3,
                             nrow = 1)) +
  geom_label(aes(y = percent,
      label = percent_label,
      fontface = "bold"),
    position = position_stack(vjust = .5),
    size = 5,
    colour = "white",
    show.legend = FALSE) +
  labs(x = NULL, y = NULL,
       fill = "Charge Type",
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
  coord_flip()

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_by_jail_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 3. Charge Type over Time (Non-PC Holds Only)

Note: These booking frequencies by charge type are grouped by fiscal year of booking and exclude FTA/Bail, temporary hold, and unclassified charges (i.e., "other")

```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings per charge type by fiscal year
df_fy_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  filter(!crime_type_clean%in%c("Other",
                                "FTA/Bail",
                                "Temporary Hold")) %>% 
  dplyr::group_by(fy) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Violent` = n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                   `Property` = n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                   `Drug/Alcohol` = n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                   `Public Order` = n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                   `Probation/Parole Violation` = n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`Fiscal Year` = fy) 


### pivot df long for ggplot 
df_fy_charge_type_summary_long <- df_fy_charge_type_summary %>%
  gather(charge_type, percent, `Violent`:`Probation/Parole Violation`) %>% 
  mutate(`Fiscal Year` = factor(`Fiscal Year`,
                                levels = c(2019,2020,2021))) %>% 
  mutate(percent_label = scales::percent(percent, 
                                         accuracy = 1))


### ggplot
ggplot(data = df_fy_charge_type_summary_long, 
       aes(y = percent, 
           x = `Fiscal Year`, 
           fill = forcats::fct_rev(charge_type))) + 
  geom_bar(stat = "identity", 
           color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent) +
  scale_fill_manual(values = c("#2F7084",
                               "#1F4A58",
                               "#E17619",
                               "#87C2D4",
                               "#61C280")) +
  guides(fill = guide_legend(reverse = TRUE,
                             title.position = "top",
                             label.position = "bottom",
                             keywidth = 3,
                             nrow = 1)) +
   geom_label(aes(y = percent,
      label = percent_label,
      fontface = "bold"),
    position = position_stack(vjust = .5),
    size = 5,
    colour = "white",
    show.legend = FALSE) +
  labs(x = NULL, y = NULL,
       fill = "Charge Type",
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
  coord_flip()

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_over_time_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 4. Percent of Bookings with any Drug/Alcohol Charge by Jail (Non-PC Holds Only), Bar Plot

```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings with drug/alcohol charge by county
df_county_level_drug_alcohol_plot <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::group_by(county) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Bookings with any Drug/Alcohol Charge (%)` = n_distinct(unique_booking_id_nh[booking_drug_alcohol_charge_present_flag==1])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`County` = county) 

### bar plot
ggplot(data = df_county_level_drug_alcohol_plot, aes(y = `Bookings with any Drug/Alcohol Charge (%)`, x = `County`, fill = forcats::fct_rev(`County`))) + 
  geom_bar(stat = "identity",  color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1),
                     limits = c(min(0), max(.4))) +
  geom_label(
    aes(
      y = `Bookings with any Drug/Alcohol Charge (%)`,
      label = scales::percent(`Bookings with any Drug/Alcohol Charge (%)`,
                              accuracy = 1),
      fontface = "bold"
    ),
    position = position_stack(vjust = .5),
    size = 5,
    colour = "white",
    show.legend = FALSE
  ) +
  scale_fill_manual(values = c("#2F7084","#2F7084","#2F7084","#2F7084","#2F7084","#2F7084","#2F7084","#2F7084")) +
  guides(fill = FALSE) +
  labs(x = NULL, y = NULL,
       fill = NULL,
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme_minimal() +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.ticks.length = unit(0, "cm"),
        strip.text = element_markdown(),
    strip.text.x = element_markdown(),
    strip.text.y = element_markdown()) + 
    coord_flip() 

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_percent_drug_alcohol_statewide_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 5. Percent of Bookings with Drug/Alcohol Charges by Jail (Non-PC Holds Only), Map

Note: This analysis of the % of bookings with drug/alcohol charges (regardless of most serious charge) are presented by county of booking. This map highlights county-level variation by grouping counties into four equally sized groups or quartiles.

```{r include=FALSE,message=FALSE,warning=FALSE}
# ### read in county-level nh data to create county,state, geoid crosswalk with acs/census data 
# ### this is the official nh county shapefile, obtained here: https://www.census.gov/geographies/mapping-files/time-series/geo/tiger-line-file.html 
# ### this is not used for mapping, however, hence we remove geography below; it's just a crosswalk across county name, geoid, and county fip code
# 
# ### read in nh county-level shape file    
# nh_county_shape <- read_sf("C:\\Users\\abyrum\\OneDrive - The Council of State Governments\\Geospatial_Mapping\\US County Geo Data\\tl_2019_us_county\\tl_2019_us_county.shp") %>%
#   clean_names() %>% 
#   filter(statefp=="33") %>% ### filter by state fip code 
#   dplyr::rename(county=name, 
#                 county_fip=countyfp)

### read in nh county-level shape file and keep geometry for mapping
### from kyle walker/tidycensus: specify cb = TRUE which defaults to the Census Bureau’s cartographic boundary shapefiles. Cartographic boundary shapefiles are preferred to the core TIGER/Line shapefiles in tidycensus as their smaller size speeds up processing and because they are pre-clipped to the US coastline.

library(tidycensus)

nh_county_shape <- get_acs(
  geography = "county",
  variables = "B19013_001",
  state = "NH",
  year = 2020,
  geometry = TRUE,
  cb = TRUE
  ) %>%
  mutate(county = word(NAME, 1)) %>% 
  group_by(county) %>% 
  summarize(geometry = st_union(geometry)) %>% 
  ungroup() %>% 
  clean_names() %>% 
  mutate(state = "NH")


### use group_by and st_union to create statewide polygon using the county-level geography
### we'll use this shapefile for a state border when mapping
nh_state_shape <- nh_county_shape %>%
  group_by(state) %>%
  summarize(geometry = st_union(geometry)) %>%
  ungroup()

```

```{r include=FALSE,message=FALSE,warning=FALSE}
### set constants for mapping
default_font_color <- "#4e4d47"
default_font_family <- "Franklin Gothic Book"
```

```{r include=FALSE,message=FALSE,warning=FALSE}
### create static map theme (based on https://timogrossenbacher.ch/2019/04/bivariate-maps-with-ggplot2-and-sf/)
theme_map <- function(...) {
  theme_minimal() +
  theme(
    text = element_text(family = default_font_family,
                        color = default_font_color),
    # remove all axes
    axis.line = element_blank(),
    axis.text.x = element_blank(),
    axis.text.y = element_blank(),
    axis.ticks = element_blank(),
    # add a subtle grid
    panel.grid.major = element_blank(),
    panel.grid.minor = element_blank(),
    # borders and margins
    plot.margin = unit(c(.5, .5, .2, .5), "cm"),
    panel.border = element_blank(),
    panel.spacing = unit(c(-.1, 0.2, .2, 0.2), "cm"),
    # titles
    legend.title = element_text(size = 11),
    legend.text = element_text(size = 9, hjust = 0,
                               color = default_font_color),
    plot.title = element_text(size = 15, hjust = 0.5,
                              color = default_font_color),
    # plot.subtitle = element_text(size = 10, hjust = 0.5,
    #                              color = default_font_color,
    #                              margin = margin(b = -0.1,
    #                                              t = -0.1,
    #                                              l = 2,
    #                                              unit = "cm"),
    #                              debug = F),
    # # captions
    # plot.caption = element_text(size = 7,
    #                             hjust = .5,
    #                             margin = margin(t = 0.2,
    #                                             b = 0,
    #                                             unit = "cm"),
    #                             color = default_font_color),
    ...
  )
}
```

```{r layout="l-page",echo=FALSE,message=FALSE,warning=FALSE}
# ### unique bookings per charge type by county
# df_county_level_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
#   dplyr::group_by(county) %>% 
#   dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
#                    `Drug/Alcohol` = n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`) %>% 
#   ungroup() %>% 
#   distinct() 

### merge new district shapefile to nh data to attach geography and render the file map-able
df_county_level_charge_type_summary_geo <- left_join(nh_county_shape,
                                                     df_county_level_drug_alcohol_plot,
                                                     by = c("county"="County"))

### create quantiles object
no_classes <- 3

### extract quantiles from var we're mapping
quantiles <- df_county_level_charge_type_summary_geo %>%
  pull(`Bookings with any Drug/Alcohol Charge (%)`) %>%
  quantile(probs = seq(0, 1, length.out = no_classes + 1), na.rm=TRUE) %>%
  as.vector() 

### make labels for map's legend
label_drug_alcohol_percent <- c("30% and Greater","15% to 30%","15% and Lower")

### create a new variable with the specified quantiles
df_county_level_charge_type_summary_geo_to_map <- df_county_level_charge_type_summary_geo %>%
  mutate(percent_drug_alcohol_quartile = cut(-`Bookings with any Drug/Alcohol Charge (%)`,
                               breaks = -quantiles,
                               right = TRUE,
                               labels = label_drug_alcohol_percent,
                               include.lowest = T)) 


### map via ggplot 
ggplot(
  # define main data source
  data = df_county_level_charge_type_summary_geo_to_map
) +
  scale_alpha(name = "",
              range = c(0.6, 0),
              guide = F) + # suppress legend
  # add main fill aesthetic
  # use thin white stroke for municipality borders
  geom_sf(
    mapping = aes(
      fill = percent_drug_alcohol_quartile
      ),
    color = "white",
    size = 0.1
  ) +
  #   scale_fill_distiller(
  #   labels = scales::percent_format(accuracy=1),
  #   direction = 1,
  #   name = "% Bookings with Drug/Alcohol\n Charges"
  # ) +
  ### use jr palette here
    # scale_colour_gradientn(colours = c("#D7EBF1","#AFD6E2","#87C2D4","#2F7084","#1F4A58")) +
 scale_fill_manual(
    values = c("#1F4A58","#2F7084","#87C2D4"),
    name = "% Bookings with Drug/Alcohol\n Charges",
    na.value = "grey50",
    guide = guide_legend(
     keyheight = unit(7.5, units = "mm"),
     title.position = "top",
     reverse = F # display highest on top
  )) +
  # use white stroke borders for county boundaries
  geom_sf(
    data = df_county_level_charge_type_summary_geo_to_map,
    fill = "transparent",
    color = "white",
    size = 1
  )  +
  # # use additional state-level shapefile for state border
  # geom_sf(
  #   data = nh_state_shape,
  #   fill = "transparent",
  #   color = "black",
  #   size = .3
  # )  +
 geom_label(
     data = df_county_level_charge_type_summary_geo_to_map, 
     aes(geometry = geometry, 
         label = paste0(county,"\n",scales::percent(`Bookings with any Drug/Alcohol Charge (%)`, accuracy = 1))),
     vjust = case_when(df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Hillsborough"] ~ 1.05,
                       df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Merrimack"] ~ .5,
                       df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Carroll"] ~ .4,
                       df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Strafford"] ~ .2,
                       df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Cheshire"] ~ .15,
                       df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Rockingham"] ~ .15,
                              TRUE ~ 0),
      # ifelse(df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Hillsborough"] | 
      #                 df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Merrimack"] | 
      #                 df_county_level_charge_type_summary_geo_to_map$geometry==df_county_level_charge_type_summary_geo_to_map$geometry[df_county_level_charge_type_summary_geo_to_map$county=="Carroll"], 1, 0),
    # hjust = ifelse(nh_acs_census_county_geo_to_map$geometry==nh_acs_census_county_geo_to_map$geometry[nh_acs_census_county_geo_to_map$county=="Hillsborough"], 1, .5),
    family = "Franklin Gothic Demi",
    color = "gray25",
    size = 3.15,
    fontface = "bold",
    stat = "sf_coordinates") +
  # guides(fill = guide_colorbar(barwidth = 8),
  #          direction = "horizontal",
  #     barheight = unit(2, units = "mm"),
  #     barwidth = unit(50, units = "mm"),
  #     draw.ulim = F,
  #     title.position = 'top',
  #     # some shifting around
  #     title.hjust = 0.5,
  #     label.hjust = 0.5) +
  labs(
    title = NULL,
    subtitle = NULL,
    x = NULL,
    y = NULL,
    fill = NULL,
    caption = NULL
  ) +
  # add theme
  theme_map() 
# +
#   theme(
#     # plot.margin = margin(),
#     panel.grid = element_blank(),
#     axis.text.x = element_blank(),
#     axis.text.y = element_blank(),
#     legend.position = "bottom",
#     legend.justification = c(0.5, 0.5),
#     legend.text = element_text(size = 11)) 


### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_percent_drug_alcohol_by_jail_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 6. Percent of Public Order Charges by Jail (Non-PC Holds Only), Map

Note: This analysis of the % of bookings where the most serious charge is a public order charge. This map highlights county-level variation by grouping counties into four equally sized groups or quartiles.

```{r layout="l-page",echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings per charge type by county
df_county_level_public_order_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  dplyr::group_by(county) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Public Order` = n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() 

### merge new district shapefile to nh data to attach geography and render the file map-able
df_county_level_public_order_summary_geo <- left_join(nh_county_shape, 
                                                 df_county_level_public_order_summary, 
                                                 by = "county")

### create quantiles object
no_classes <- 4

### extract quantiles from var we're mapping
quantiles <- df_county_level_public_order_summary_geo %>%
  pull(`Public Order`) %>%
  quantile(probs = seq(0, 1, length.out = no_classes + 1), na.rm=TRUE) %>%
  as.vector() 

### make labels for map's legend
label_public_order_percent <- c("7% and Greater","5% to 7%","4% to 5%","4% and Lower")

### create a new variable with the specified quantiles
df_county_level_public_order_summary_geo_to_map <- df_county_level_public_order_summary_geo %>%
  mutate(percent_public_order_quartile = cut(-`Public Order`,
                               breaks = -quantiles,
                               right = TRUE,
                               labels = label_public_order_percent,
                               include.lowest = T)) 


### map via ggplot 
ggplot(
  # define main data source
  data = df_county_level_public_order_summary_geo_to_map
) +
  scale_alpha(name = "",
              range = c(0.6, 0),
              guide = F) + # suppress legend
  # add main fill aesthetic
  # use thin white stroke for municipality borders
  geom_sf(
    mapping = aes(
      fill = percent_public_order_quartile
      ),
    color = "white",
    size = 0.1
  ) +
 scale_fill_manual(
    values = c("#1F4A58","#2F7084","#87C2D4","#AFD6E2"),
    name = "% Bookings for Public Order\n Charges",
    na.value = "grey50",
    guide = guide_legend(
     keyheight = unit(5, units = "mm"),
     title.position = "top",
     reverse = F # display highest on top
  )) +
  # use white stroke borders for county boundaries
  geom_sf(
    data = df_county_level_public_order_summary_geo_to_map,
    fill = "transparent",
    color = "white",
    size = 1
  )  +
  # # use additional state-level shapefile for state border
  # geom_sf(
  #   data = nh_state_shape,
  #   fill = "transparent",
  #   color = "black",
  #   size = .3
  # )  +
  geom_label(
     data = df_county_level_public_order_summary_geo_to_map, 
     aes(geometry = geometry, 
         label = county),
    family = "Franklin Gothic Demi",
    color = "gray25",
    size = 4,
    fontface = "bold",
    stat = "sf_coordinates") +
  labs(x = NULL,
       y = NULL,
       title = NULL,
       subtitle = NULL,
       caption = NULL) +
  theme_map()

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_percent_public_order_by_jail_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 7a. Charge Type by High Utilizer Grouping (Non-PC Holds Only), Bar Plot (based on all three years of data) 

Note: This output excludes charge type analysis for PC bookings, as no charge information exists, but the high utilizer percentile grouping (developed by Mari) does include PC bookings -- does this make sense? Analysis excludes FTA/Bail, temporary hold, and unclassified charges (i.e., "other").

Below is the preliminary grouping I am using to confirm with the team. With this approach, this is not overlap of groups -- one individual is in one of the four groups (i.e., someone in the top 1% of jail entrances is only included in the 1% grouping, not the 5% and 10% grouping). I'm thinking that this may help us better see any differences between groupings of high utilizers, if they exist. Open to suggestions, though!

* **Top 1%:** individual is in top 1% of jail entrances for all 3 years of sample

* **Top 5%:** individual is in top 5% of jail entrances for all 3 years of sample (really this is 1% to 5%)

* **Top 10%:** individual is in top 10% of jail entrances for all 3 years of sample (really this is 5% to 10%)

* **Non-HU:** individual is not in top 10% of jail entrances for all 3 years of sample

```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### to recode high utlizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  filter(!crime_type_clean%in%c("Other",
                                "FTA/Bail",
                                "Temporary Hold")) %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive = case_when( 
    high_utilizer_10_pct=="No" ~ 4,
    high_utilizer_10_pct=="Yes" & high_utilizer_5_pct=="No" & high_utilizer_1_pct=="No" ~ 3,
    high_utilizer_5_pct=="Yes" & high_utilizer_1_pct=="No" ~ 2,
    high_utilizer_1_pct=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive = factor(hu_group_exclusive,
         levels = c(1,2,3,4),
         labels = c("Tier 1 HU", "Tier 2 HU", "Tier 3 HU", "Non-HU"))) 

# %>% 
#   mutate(hu_group_exclusive_to_plot = paste0(
#       "<span style = 'font-size:12pt'>", hu_group_exclusive, "</span><br><br>",
#       "<span style = 'font-size:12pt'>", "High Utilizer ","</span>")) 
    

### unique bookings per charge type by HU grouping
df_hu_charge_type_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode %>% 
  filter(!crime_type_clean%in%c("Other",
                                "FTA/Bail",
                                "Temporary Hold")) %>% 
  dplyr::group_by(hu_group_exclusive) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Violent` = n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                   `Property` = n_distinct(unique_booking_id_nh[crime_type_clean=="Property"])/`Unique Non-PC Bookings (N)`,
                   `Drug/Alcohol` = n_distinct(unique_booking_id_nh[crime_type_clean=="Drug/Alcohol"])/`Unique Non-PC Bookings (N)`,
                   `Public Order` = n_distinct(unique_booking_id_nh[crime_type_clean=="Public Order"])/`Unique Non-PC Bookings (N)`,
                    `Probation/Parole Violation` = n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`High Utilizer Percentiles` = hu_group_exclusive) 

### pivot df long for ggplot 
df_hu_charge_type_summary_long <- df_hu_charge_type_summary %>%
  gather(charge_type, percent, `Violent`:`Probation/Parole Violation`) %>% 
  mutate(percent_label = scales::percent(percent, 
                                         accuracy = 1)) %>% 
  mutate(charge_type_factor =  factor(charge_type,
                                      levels = c("Violent","Property","Drug/Alcohol","Public Order","Probation/Parole Violation")))

### ggplot
ggplot(data = df_hu_charge_type_summary_long, 
       aes(y = percent, 
           x = `High Utilizer Percentiles`, 
           fill = forcats::fct_rev(charge_type_factor))) + 
  geom_bar(stat = "identity", 
           color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent) +
  scale_fill_manual(values = c("#2F7084",
                               "#1F4A58",
                               "#E17619",
                               "#87C2D4",
                               "#61C280")) +
  guides(fill = guide_legend(reverse = TRUE,
                             title.position = "top",
                             label.position = "bottom",
                             keywidth = 3,
                             nrow = 1)) +
  geom_label(aes(y = percent,
      label = percent_label,
      fontface = "bold"),
    position = position_stack(vjust = .5),
    size = 5,
    colour = "white",
    show.legend = FALSE) +
  labs(x = NULL, y = NULL,
       fill = "Charge Type",
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
  coord_flip()

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_by_hu_percentiles_statewide_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```


<br><br>

### Figure 7a (take two). Charge Type by High Utilizer Grouping (Non-PC Holds Only), Bar Plot (based on all three years of data) 

This visual focuses specifically on VOP


```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### to recode high utlizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_vop <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  # filter(!crime_type_clean%in%c("Other",
  #                               "FTA/Bail",
  #                               "Temporary Hold")) %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive = case_when( 
    high_utilizer_10_pct=="No" ~ 4,
    high_utilizer_10_pct=="Yes" & high_utilizer_5_pct=="No" & high_utilizer_1_pct=="No" ~ 3,
    high_utilizer_5_pct=="Yes" & high_utilizer_1_pct=="No" ~ 2,
    high_utilizer_1_pct=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive = factor(hu_group_exclusive,
         levels = c(1,2,3,4),
         labels = c("Tier 1 HU", "Tier 2 HU", "Tier 3 HU", "Non-HU"))) 

### unique bookings per charge type by HU grouping
df_hu_charge_type_summary_vop <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_vop %>% 
  dplyr::group_by(hu_group_exclusive) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Probation/Parole Violation` = n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`,
                    `Other` = n_distinct(unique_booking_id_nh[crime_type_clean!="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`High Utilizer Percentiles` = hu_group_exclusive) 

### pivot df long for ggplot 
df_hu_charge_type_summary_long_vop <- df_hu_charge_type_summary_vop %>%
  gather(charge_type, percent, `Probation/Parole Violation`:`Other`) %>% 
  mutate(percent_label = scales::percent(percent, 
                                         accuracy = 1)) %>% 
  mutate(charge_type_factor =  factor(charge_type,
                                      levels = c("Probation/Parole Violation","Other")))

### ggplot
ggplot(data = df_hu_charge_type_summary_long_vop, 
       aes(y = percent, 
           x = `High Utilizer Percentiles`, 
           fill = forcats::fct_rev(charge_type_factor))) + 
  geom_bar(stat = "identity", 
           color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent) +
  scale_fill_manual(values = rev(c("#167a9c",
                               "gray"))) +
  guides(fill = guide_legend(reverse = TRUE,
                             title.position = "top",
                             label.position = "bottom",
                             keywidth = 3,
                             nrow = 1)) +
  geom_label(aes(y = percent,
      label = percent_label,
      fontface = "bold"),
    position = position_stack(vjust = .5),
    size = 5,
    colour = case_when(df_hu_charge_type_summary_long_vop$charge_type_factor == "Other" ~ "black",
                              TRUE ~ "white"),
    show.legend = FALSE) +
  labs(x = NULL, y = NULL,
       fill = "Charge Type",
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
  coord_flip()

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_by_hu_percentiles_statewide_vop_highlight.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 7a (take three). Charge Type by High Utilizer Grouping (Non-PC Holds Only), Bar Plot (based on all three years of data) 

This visual focuses specifically on VOP


```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### to recode high utlizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_vop <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  # filter(!crime_type_clean%in%c("Other",
  #                               "FTA/Bail",
  #                               "Temporary Hold")) %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive = case_when( 
    high_utilizer_10_pct=="No" ~ 4,
    high_utilizer_10_pct=="Yes" & high_utilizer_5_pct=="No" & high_utilizer_1_pct=="No" ~ 3,
    high_utilizer_5_pct=="Yes" & high_utilizer_1_pct=="No" ~ 2,
    high_utilizer_1_pct=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive = factor(hu_group_exclusive,
         levels = c(1,2,3,4),
         labels = c("Tier 1 HU", "Tier 2 HU", "Tier 3 HU", "Non-HU"))) 

### unique bookings per charge type by HU grouping
df_hu_charge_type_summary_vop <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_vop %>% 
  dplyr::group_by(hu_group_exclusive) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Probation/Parole Violation` = n_distinct(unique_booking_id_nh[crime_type_clean=="Probation/Parole Violation"])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`High Utilizer Percentiles` = hu_group_exclusive) 

### pivot df long for ggplot 
df_hu_charge_type_summary_long_vop <- df_hu_charge_type_summary_vop %>%
  gather(charge_type, percent, `Probation/Parole Violation`) %>% 
  mutate(percent_label = scales::percent(percent, 
                                         accuracy = 1))

### bar plot
ggplot(data = df_hu_charge_type_summary_long_vop, aes(y = percent, x = `High Utilizer Percentiles`, fill = forcats::fct_rev(`High Utilizer Percentiles`))) + 
  geom_bar(stat = "identity",  color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1),
                     limits = c(min(0), max(.4))) +
  geom_label(
    aes(
      y = percent,
      label = scales::percent(percent,
                              accuracy = 1),
      fontface = "bold"
    ),
    position = position_stack(vjust = .5),
    size = 5,
    colour = "white",
    show.legend = FALSE
  ) +
  scale_fill_manual(values = c("#2F7084","#2F7084","#2F7084","#2F7084","#2F7084")) +
  guides(fill = FALSE) +
  labs(x = NULL, y = NULL,
       fill = NULL,
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme_minimal() +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        # panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
    coord_flip() 

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_by_hu_percentiles_statewide_vop_highlight_option_2.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 7a (take four). Charge Type by High Utilizer Grouping (Non-PC Holds Only), Bar Plot (based on all three years of data) 

This visual focuses specifically on violent offenses


```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### to recode high utlizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_vop <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  # filter(!crime_type_clean%in%c("Other",
  #                               "FTA/Bail",
  #                               "Temporary Hold")) %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive = case_when( 
    high_utilizer_10_pct=="No" ~ 4,
    high_utilizer_10_pct=="Yes" & high_utilizer_5_pct=="No" & high_utilizer_1_pct=="No" ~ 3,
    high_utilizer_5_pct=="Yes" & high_utilizer_1_pct=="No" ~ 2,
    high_utilizer_1_pct=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive = factor(hu_group_exclusive,
         levels = c(1,2,3,4),
         labels = c("Tier 1 HU", "Tier 2 HU", "Tier 3 HU", "Non-HU"))) 

### unique bookings per charge type by HU grouping
df_hu_charge_type_summary_vop <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_vop %>% 
  dplyr::group_by(hu_group_exclusive) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                  `Violent` = n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                    `Other` = n_distinct(unique_booking_id_nh[crime_type_clean!="Violent"])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`High Utilizer Percentiles` = hu_group_exclusive) 

### pivot df long for ggplot 
df_hu_charge_type_summary_long_vop <- df_hu_charge_type_summary_vop %>%
  gather(charge_type, percent, `Violent`:`Other`) %>% 
  mutate(percent_label = scales::percent(percent, 
                                         accuracy = 1)) %>% 
  mutate(charge_type_factor =  factor(charge_type,
                                      levels = c("Violent","Other")))

### ggplot
ggplot(data = df_hu_charge_type_summary_long_vop, 
       aes(y = percent, 
           x = `High Utilizer Percentiles`, 
           fill = forcats::fct_rev(charge_type_factor))) + 
  geom_bar(stat = "identity", 
           color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent) +
  scale_fill_manual(values = rev(c("#167a9c",
                               "gray"))) +
  guides(fill = guide_legend(reverse = TRUE,
                             title.position = "top",
                             label.position = "bottom",
                             keywidth = 3,
                             nrow = 1)) +
  geom_label(aes(y = percent,
      label = percent_label,
      fontface = "bold"),
    position = position_stack(vjust = .5),
    size = 5,
    colour = case_when(df_hu_charge_type_summary_long_vop$charge_type_factor == "Other" ~ "black",
                              TRUE ~ "white"),    show.legend = FALSE) +
  labs(x = NULL, y = NULL,
       fill = "Charge Type",
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
  coord_flip()

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_by_hu_percentiles_statewide_violent_highlight.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 7a (take five). Charge Type by High Utilizer Grouping (Non-PC Holds Only), Bar Plot (based on all three years of data) 

This visual focuses specifically on violent offenses


```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### to recode high utlizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_vop <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  # filter(!crime_type_clean%in%c("Other",
  #                               "FTA/Bail",
  #                               "Temporary Hold")) %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive = case_when( 
    high_utilizer_10_pct=="No" ~ 4,
    high_utilizer_10_pct=="Yes" & high_utilizer_5_pct=="No" & high_utilizer_1_pct=="No" ~ 3,
    high_utilizer_5_pct=="Yes" & high_utilizer_1_pct=="No" ~ 2,
    high_utilizer_1_pct=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive = factor(hu_group_exclusive,
         levels = c(1,2,3,4),
         labels = c("Tier 1 HU", "Tier 2 HU", "Tier 3 HU", "Non-HU"))) 

### unique bookings per charge type by HU grouping
df_hu_charge_type_summary_vop <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_vop %>% 
  dplyr::group_by(hu_group_exclusive) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                  `Violent` = n_distinct(unique_booking_id_nh[crime_type_clean=="Violent"])/`Unique Non-PC Bookings (N)`,
                    `Other` = n_distinct(unique_booking_id_nh[crime_type_clean!="Violent"])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  dplyr::rename(`High Utilizer Percentiles` = hu_group_exclusive) 

### pivot df long for ggplot 
df_hu_charge_type_summary_long_vop <- df_hu_charge_type_summary_vop %>%
  gather(charge_type, percent, `Violent`) %>% 
  mutate(percent_label = scales::percent(percent, 
                                         accuracy = 1))

### bar plot
ggplot(data = df_hu_charge_type_summary_long_vop, aes(y = percent, x = `High Utilizer Percentiles`, fill = forcats::fct_rev(`High Utilizer Percentiles`))) + 
  geom_bar(stat = "identity",  color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent_format(accuracy = 1),
                     limits = c(min(0), max(.4))) +
  geom_label(
    aes(
      y = percent,
      label = scales::percent(percent,
                              accuracy = 1),
      fontface = "bold"
    ),
    position = position_stack(vjust = .5),
    size = 5,
    colour = "white",
    show.legend = FALSE
  ) +
  scale_fill_manual(values = c("#2F7084","#2F7084","#2F7084","#2F7084","#2F7084")) +
  guides(fill = FALSE) +
  labs(x = NULL, y = NULL,
       fill = NULL,
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme_minimal() +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        # panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
    coord_flip() 

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_by_hu_percentiles_statewide_violent_highlight_option_2.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 7b. Public Order Charge Type by High Utilizer Grouping (Non-PC Holds Only), Bar Plot (based on all three years of data) 

Note: This output excludes charge type analysis for PC bookings, as no charge information exists, but the high utilizer percentile grouping (developed by Mari) does include PC bookings -- does this make sense? Analysis excludes FTA/Bail, temporary hold, and unclassified charges (i.e., "other").

Below is the preliminary grouping I am using to confirm with the team. With this approach, this is not overlap of groups -- one individual is in one of the four groups (i.e., someone in the top 1% of jail entrances is only included in the 1% grouping, not the 5% and 10% grouping). I'm thinking that this may help us better see any differences between groupings of high utilizers, if they exist. Open to suggestions, though!

* **Top 1%:** individual is in top 1% of jail entrances for all 3 years of sample

* **Top 5%:** individual is in top 5% of jail entrances for all 3 years of sample (really this is 1% to 5%)

* **Top 10%:** individual is in top 10% of jail entrances for all 3 years of sample (really this is 5% to 10%)

* **Non-HU:** individual is not in top 10% of jail entrances for all 3 years of sample

```{r layout="l-body-outset",echo=FALSE,message=FALSE,warning=FALSE}
### unique bookings per charge type by HU grouping
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode_public_order <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode %>% 
  filter(crime_type_lookup=="Public Order") %>% 
  mutate(public_order_charge_description = ifelse(statute_title_lookup%in%c("Criminal Mischief", "Criminal Trespass", "Disorderly Conduct", "Resisting Arrest Or Detention"),
                                            statute_title_lookup,"Other"))


### unique bookings per charge type by HU grouping
df_hu_public_order_charge_type_summary_to_plot <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode_public_order %>% 
  dplyr::group_by(hu_group_exclusive) %>% 
  dplyr::summarise(`Total Bookings` = n_distinct(unique_booking_id_nh),
                   `Criminal Mischief` = n_distinct(unique_booking_id_nh[public_order_charge_description=="Criminal Mischief"])/`Total Bookings`,
                   `Criminal Trespass` = n_distinct(unique_booking_id_nh[public_order_charge_description=="Criminal Trespass"])/`Total Bookings`,
                   `Disorderly Conduct` = n_distinct(unique_booking_id_nh[public_order_charge_description=="Disorderly Conduct"])/`Total Bookings`,
                   `Resisting Arrest Or Detention` = n_distinct(unique_booking_id_nh[public_order_charge_description=="Resisting Arrest Or Detention"])/`Total Bookings`,
                   `Other` = n_distinct(unique_booking_id_nh[public_order_charge_description=="Other"])/`Total Bookings`) %>% 
  ungroup() %>% 
  dplyr::select(-`Total Bookings`)


### pivot df long for ggplot 
df_hu_public_order_charge_type_summary_long <- df_hu_public_order_charge_type_summary_to_plot %>%
  gather(charge_type, percent, `Criminal Mischief`:`Other`) %>% 
  mutate(percent_label = scales::percent(percent, 
                                         accuracy = 1)) %>% 
  mutate(charge_type_factor =  factor(charge_type,
                                      levels = c("Criminal Trespass","Criminal Mischief", "Disorderly Conduct", "Resisting Arrest Or Detention","Other")))

 

### ggplot
ggplot(data = df_hu_public_order_charge_type_summary_long, 
       aes(y = percent, 
           x = hu_group_exclusive, 
           fill = forcats::fct_rev(charge_type_factor))) + 
  geom_bar(stat = "identity", 
           color = "gray80") +
  scale_x_discrete(limits = rev) +
  scale_y_continuous(labels = scales::percent) +
  scale_fill_manual(values = c("#167a9c",
                               "#293e5c",
                               "#b95826",
                               "#87C2D4",
                               "#557e39")) +
  guides(fill = guide_legend(reverse = TRUE,
                             title.position = "top",
                             label.position = "bottom",
                             keywidth = 3,
                             nrow = 1)) +
  geom_label(aes(y = percent,
      label = percent_label,
      fontface = "bold"),
    position = position_stack(vjust = .5),
    size = 5,
    colour = "white",
    show.legend = FALSE) +
  labs(x = NULL, y = NULL,
       fill = "Public Order Charges",
       caption = NULL,
       title = NULL,
       subtitle = NULL) +
  theme(legend.position = "top",
        legend.title.align=0.5,
        plot.title = element_text(hjust = 0.5),
        plot.subtitle = element_text(hjust = 0.5),
        plot.caption = element_text(hjust = 0.5),
        axis.text.y = element_markdown(face = "bold", hjust = 1, size = 12),
        axis.text.x = element_text(face = "bold", hjust = 1, size = 12),
        axis.ticks.length = unit(0, "cm"),
        panel.grid.major.y = element_blank(),
        panel.grid.major.x = element_blank(),
        panel.grid.minor = element_blank()) +
  coord_flip()

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "public_order_charge_type_by_hu_percentiles_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>

### Figure 8. Percent of Charges that are Probation/Parole Violation, FTA/Bail, and Public Order by Jail for High Utlizers (based on all three years of data), Map

Note: This analysis of the % of bookings where the most serious charge is either a probation/parole violation, failure to appear (FTA) or bail related, or public order for the top 10% of HU. Statewide, these three types of charges appear to disproportionality occur with bookings for high/frequent utilizers. This map explores county-level variation by grouping counties into four equally sized groups or quartiles.


```{r layout="l-page",echo=FALSE,message=FALSE,warning=FALSE}
### to recode high utilizer percentile grouping, we don't need to group_by as the percentile grouping is already grouped across individual from Mari's cleaning
nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode_pb_pa_violation_map <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc %>% 
  ### to ensure each individual/booking is in correct grouping, start with Non-HU, then 10% and move down to 1%
  mutate(hu_group_exclusive = case_when( 
    high_utilizer_10_pct=="No" ~ 2,
    high_utilizer_10_pct=="Yes" ~ 1,
    TRUE ~ as.numeric(NA)),
    hu_group_exclusive = factor(hu_group_exclusive,
         levels = c(1,2),
         labels = c("Top 10%", "Non-HU")))
    

### unique bookings per charge type by HU grouping
df_hu_charge_type_hu_map_summary <- nh_eight_county_charge_clean_final_crime_type_recode_non_pc_hu_recode_pb_pa_violation_map %>% 
  dplyr::group_by(county,hu_group_exclusive) %>% 
  dplyr::summarise(`Unique Non-PC Bookings (N)` = n_distinct(unique_booking_id_nh),
                   `Public Order/FTA/Bail/VOP` = n_distinct(unique_booking_id_nh[crime_type_clean%in%c("Public Order","FTA/Bail","Probation/Parole Violation")])/`Unique Non-PC Bookings (N)`) %>% 
  ungroup() %>% 
  distinct() %>% 
  filter(hu_group_exclusive!="Top 10%")

### merge new district shapefile to nh data to attach geography and render the file map-able
df_hu_charge_type_hu_map_summary_geo <- left_join(nh_county_shape, 
                                                 df_hu_charge_type_hu_map_summary, 
                                                 by = "county")

### create quantiles object
no_classes <- 4

### extract quantiles from var we're mapping
quantiles <- df_hu_charge_type_hu_map_summary_geo %>%
  pull(`Public Order/FTA/Bail/VOP`) %>%
  quantile(probs = seq(0, 1, length.out = no_classes + 1), 
           na.rm=TRUE) %>%
  as.vector() 

### make labels for map's legend
label_vop_fta_bail_public_percent <- c("26% and Greater","21% to 26%","20% to 21%","20% and Lower")

### create a new variable with the specified quantiles
df_hu_charge_type_hu_map_summary_geo_to_map <- df_hu_charge_type_hu_map_summary_geo %>%
  mutate(percent_vop_fta_bail_public_quartile = cut(-`Public Order/FTA/Bail/VOP`,
                               breaks = -quantiles,
                               right = TRUE,
                               labels = label_vop_fta_bail_public_percent,
                               include.lowest = T)) 


### map via ggplot 
ggplot(
  # define main data source
  data = df_hu_charge_type_hu_map_summary_geo_to_map
) +
  scale_alpha(name = "",
              range = c(0.6, 0),
              guide = F) + # suppress legend
  # add main fill aesthetic
  # use thin white stroke for municipality borders
  geom_sf(
    mapping = aes(
      fill = percent_vop_fta_bail_public_quartile
      ),
    color = "white",
    size = 0.1
  ) +
 scale_fill_manual(
    values = c("#1F4A58","#2F7084","#87C2D4","#AFD6E2"),
    name = "% Bookings for VOP, FTA/Bail, or Public Order\n(Top 10% of High Utilizers)",
    na.value = "grey50",
    guide = guide_legend(
     keyheight = unit(5, units = "mm"),
     title.position = "top",
     reverse = F # display highest on top
  )) +
  # use white stroke borders for county boundaries
  geom_sf(
    data = df_hu_charge_type_hu_map_summary_geo_to_map,
    fill = "transparent",
    color = "white",
    size = 1
  )  +
  # # use additional state-level shapefile for state border
  # geom_sf(
  #   data = nh_state_shape,
  #   fill = "transparent",
  #   color = "black",
  #   size = .3
  # )  +
  geom_label(
     data = df_hu_charge_type_hu_map_summary_geo_to_map, 
     aes(geometry = geometry, 
         label = county),
    family = "Franklin Gothic Demi",
    color = "gray25",
    size = 4,
    fontface = "bold",
    stat = "sf_coordinates") +
  labs(x = NULL,
       y = NULL,
       title = NULL,
       subtitle = NULL,
       caption = NULL) +
  theme_map()

### write out
ggsave(
    filename = file.path(sp_viz_output_path, "charge_type_percent_fta_public_vop_by_jail_non_pc.png"),
    plot = last_plot(),
    device = ragg::agg_png,
    dpi = 300,
    height = 5.25,
    width = 10,
    units = "in",
    bg = "transparent"
  )
```

<br><br>