Jour 405v, Jour 5003, Spring 2020

Advanced Reporting and Data Analysis

Reporting on Homelessness in Northwest Arkansas

Importing Data

Roster_March18 <- rio::import("https://docs.google.com/spreadsheets/d/1lsP37hPHXQ_KlBOsDJw5s7ehQlmeBqdX/edit#gid=814904708", which = "March18Roster")
Roster_March18 <- janitor::clean_names(Roster_March18)

Rename

glimpse(Roster_March18)
## Observations: 637
## Variables: 10
## $ placeholder     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ name_last_first <chr> "AGUILERA, AARON ", "AKERS, EARREONNA ", "ALARCO…
## $ age             <chr> "33 ", "21 ", "20 ", "20 ", "32 ", "31 ", "32 ",…
## $ race            <chr> "H ", "B ", "H ", "B ", "H ", "W ", "B ", "B ", …
## $ sex             <chr> "M ", "F ", "M ", "M ", "M ", "M ", "M ", "M ", …
## $ booking_date    <chr> "02/07/2020 ", "02/19/2020 ", "02/17/2020 ", "06…
## $ v7              <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ housing_address <chr> "22233 BUTLER FORD RD, SPRINGDALE, AR 72764-", "…
## $ charge          <chr> "Aggravated assault, terroristic threatening, fa…
## $ homeless        <chr> "No", "No", "No", "No", "No", "No", "No", "Yes",…
Homeless_March18 <- Roster_March18 %>%
  filter(homeless == "Yes")
glimpse(Homeless_March18)
## Observations: 73
## Variables: 10
## $ placeholder     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ name_last_first <chr> "ALLEN, QUINTTON ", "ALVARADO, COLTON ", "BARRON…
## $ age             <chr> "29 ", "26 ", "64 ", "51 ", "34 ", "42 ", "37 ",…
## $ race            <chr> "B ", "W ", "W ", "W ", "W ", "W ", "W ", "H ", …
## $ sex             <chr> "M ", "M ", "M ", "M ", "M ", "M ", "M ", "M ", …
## $ booking_date    <chr> "01/21/2020 ", "03/13/2020 ", "01/24/2020 ", "01…
## $ v7              <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ housing_address <chr> "HOMELESS, FAYETTEVILLE, AR 72701-", "SUPER 8, L…
## $ charge          <chr> "Assault, probation violation, failure to appear…
## $ homeless        <chr> "Yes", "Yes", "Yes", "Yes", "Yes", "Yes", "Yes",…

Graphic for percentage of homelessness

df <- Roster_March18 %>%
  count(homeless)
df
## # A tibble: 2 x 2
##   homeless     n
##   <chr>    <int>
## 1 No         564
## 2 Yes         73
count.data <- df %>% 
  data.frame(prop = c(88.7, 11.3))
count.data <- count.data %>%
  mutate(lab.ypos = cumsum(prop) - 0.5*prop)

theme_set(theme_classic(base_size = 18))

mycols <- c("#0073C2FF", "#EFC000FF", "#868686FF", "#CD534CFF")

ggplot(count.data, aes(x = "", y = prop, fill = homeless)) +
  geom_bar(width = 1, stat = "identity", color = "white") +
  coord_polar("y", start = 0)+
  geom_text(aes(y = lab.ypos, label = n), color = "white")+
  scale_fill_manual(values = mycols) +
  theme_void() +
  labs(title= "Homeless Detainees in Washington County Detention Center",
       subtitle="Data collected from detainee roster on March 28, 2020",
       caption= "Graphic by Michael Adkison",
       x= "Homeless Inmates",
       y= "Non-Homeless Inmates")

Charges Facing Inmates

ChargeStatistics <- rio::import("https://docs.google.com/spreadsheets/d/1lsP37hPHXQ_KlBOsDJw5s7ehQlmeBqdX/edit#gid=1846079051", which = "March18ChargeStats")
ChargeStatistics <- janitor::clean_names(ChargeStatistics)

Remove row 74 with totals

ChargeStatistics <- ChargeStatistics[-c(74),]

Summary of charges by category

df2 <- ChargeStatistics %>% 
  summarize_if(is.numeric, sum, na.rm=TRUE)

df3 <- add_rownames(df2) %>% 
  gather(Charge, n, -rowname) %>% 
  spread(rowname, n) 
## Warning: Deprecated, use tibble::rownames_to_column() instead.
colnames(df3)[2] <- "Total"

df3$Charge <- gsub("felony_y_n", "Felony", df3$Charge)
df3$Charge <- gsub("drug_dwi", "Drug or DWI", df3$Charge)
df3$Charge <- gsub("administrative", "Administrative", df3$Charge)
df3$Charge <- gsub("assault_violence", "Assault of Violence", df3$Charge)
df3$Charge <- gsub("family_related", "Family Related", df3$Charge)
df3
## # A tibble: 5 x 2
##   Charge              Total
##   <chr>               <int>
## 1 Administrative         64
## 2 Assault of Violence    13
## 3 Drug or DWI            25
## 4 Family Related          3
## 5 Felony                  5

Chart of charges

ggplot(df3, aes(x = Charge, y = Total, fill= Total)) +
  geom_col(position = "dodge", show.legend=FALSE) +
  coord_flip() +   
  geom_text(aes(label= Total, x= Charge, y= Total), hjust=-1, vjust=1) +
    scale_y_continuous(limits=c(0, 75)) +
  labs(title= "Charges for Homeless Detainees",
       subtitle= "Washington Co. Sheriff Detainee Roster, 3/28/2020",
       caption= "Graphic by Michael Adkison",
       x= "",
       y= "Number of occurrences")

Wells Analysis

Compare Roster https://drive.google.com/file/d/1lsP37hPHXQ_KlBOsDJw5s7ehQlmeBqdX/view?usp=sharing

March 18 Roster = 637 detainees, 73 homeless or 11%
March 27 Roster = 418 detainees, 42 homeless or 10%
Overall Jail population reduced by 219 as March 27 from March 18. That accounts for 20 new detainees over the period.
238 of the March 18 detainees were gone by March 27. Of this, 33 were homeless, or 14%.
Records show 33 fewer homeless people in jail comparing the 399 matching records
399 inmate names on March 27 remain, they match the March 18  roster
20 new inmates since March 18
3 of the new inmates are homeless

Clean names

Roster_March18$name2 <- gsub(",", "_", Roster_March18$name_last_first)
Roster_March18$name2 <- gsub("\\s", "", Roster_March18$name2)

File exam of March 18

#March 18 Roster: 637 detainees
nrow(Roster_March18)
## [1] 637
glimpse(Roster_March18)
## Observations: 637
## Variables: 11
## $ placeholder     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ name_last_first <chr> "AGUILERA, AARON ", "AKERS, EARREONNA ", "ALARCO…
## $ age             <chr> "33 ", "21 ", "20 ", "20 ", "32 ", "31 ", "32 ",…
## $ race            <chr> "H ", "B ", "H ", "B ", "H ", "W ", "B ", "B ", …
## $ sex             <chr> "M ", "F ", "M ", "M ", "M ", "M ", "M ", "M ", …
## $ booking_date    <chr> "02/07/2020 ", "02/19/2020 ", "02/17/2020 ", "06…
## $ v7              <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ housing_address <chr> "22233 BUTLER FORD RD, SPRINGDALE, AR 72764-", "…
## $ charge          <chr> "Aggravated assault, terroristic threatening, fa…
## $ homeless        <chr> "No", "No", "No", "No", "No", "No", "No", "Yes",…
## $ name2           <chr> "AGUILERA_AARON", "AKERS_EARREONNA", "ALARCON_BR…
Roster_March27 <- rio::import("https://docs.google.com/spreadsheets/d/1lsP37hPHXQ_KlBOsDJw5s7ehQlmeBqdX/edit#gid=1284726909", which = "March27Roster")
Roster_March27 <- janitor::clean_names(Roster_March27)
#Clean Names
Roster_March27$name2 <- gsub(",", "_", Roster_March27$name_last_first)
Roster_March27$name2 <- gsub("\\s", "", Roster_March27$name2)
#March 27 Roster: 418 detainees
nrow(Roster_March27)
## [1] 418
glimpse(Roster_March27)
## Observations: 418
## Variables: 9
## $ name_last_first <chr> "AGUILERA, AARON", "AKERS, EARREONNA", "ALEXANDE…
## $ age             <int> 33, 21, 20, 32, 32, 38, 26, 25, 27, 18, 38, 29, …
## $ race            <chr> "H", "B", "B", "H", "B", "B", "P", "H", "W", "M"…
## $ sex             <chr> "M", "F", "M", "M", "M", "M", "M", "M", "M", "M"…
## $ booking_date    <chr> "02/07/2020", "02/19/2020", "06/28/2019", "12/19…
## $ housing_address <chr> "22233 BUTLER FORD RD, SPRINGDALE, AR 72764-", "…
## $ charge          <chr> "Aggravated assault, terroristic threatening, fa…
## $ homeless        <chr> "No", "No", "No", "No", "No", "No", "No", "No", …
## $ name2           <chr> "AGUILERA_AARON", "AKERS_EARREONNA", "ALEXANDER_…

Number homeless on March 18: 73

bf <- Roster_March18 %>% 
  count(homeless) %>% 
  arrange(desc(n)) %>% 
  mutate(Pct = (100*(n/(sum(n)))),'%') 

bf$Pct <- round(bf$Pct, 2)
bf
## # A tibble: 2 x 4
##   homeless     n   Pct `"%"`
##   <chr>    <int> <dbl> <chr>
## 1 No         564  88.5 %    
## 2 Yes         73  11.5 %

Number homeless on March 27: 42

wtf <- Roster_March27 %>% 
  count(homeless) %>% 
  arrange(desc(n)) %>% 
    mutate(Pct = (100*(n/(sum(n)))),'%') 

wtf$Pct <- round(wtf$Pct, 2)
nrow(wtf)
## [1] 2
wtf
## # A tibble: 2 x 4
##   homeless     n   Pct `"%"`
##   <chr>    <int> <dbl> <chr>
## 1 No         376  90.0 %    
## 2 Yes         42  10.0 %

There are 31 fewer homeless people in jail since March 27. But this doesn’t account for the number of homeless people released and the number of homeless added

Who remains - 399 match

Remain <- Roster_March27 %>% 
  inner_join(Roster_March18, by="name2")
nrow(Remain)
## [1] 399
head(Remain)
##   name_last_first.x age.x race.x sex.x booking_date.x
## 1   AGUILERA, AARON    33      H     M     02/07/2020
## 2  AKERS, EARREONNA    21      B     F     02/19/2020
## 3 ALEXANDER, DAJOHN    20      B     M     06/28/2019
## 4    ALFARO, JOSEPH    32      H     M     12/19/2019
## 5        ALLEN, JOE    32      B     M     02/27/2020
## 6      ALLEN, RICKY    38      B     M     02/06/2020
##                                housing_address.x
## 1    22233 BUTLER FORD RD, SPRINGDALE, AR 72764-
## 2   250 N AQUA CROSSING, FAYETTEVILLE, AR 72701-
## 3        2401 E 115TH ST, LOS ANGELES, CA 90059-
## 4         7103 GREY DAWN LANE, DALLAS, TX 75227-
## 5         20331 W DEANE, FAYETTEVILLE, AR 72701-
## 6 742 N BETTY JOE DRIVE, FAYETTEVILLE, AR 72701-
##                                                                      charge.x
## 1              Aggravated assault, terroristic threatening, failure to appear
## 2                               Assault, possession, theft, failure to appear
## 3                                                        Hold for other dept.
## 4                                                      Violation of probation
## 5 Public intoxication, possession, fleeing, obstructing government operations
## 6                                         Drugs, possession, parole violation
##   homeless.x            name2 placeholder  name_last_first.y age.y race.y
## 1         No   AGUILERA_AARON          NA   AGUILERA, AARON    33      H 
## 2         No  AKERS_EARREONNA          NA  AKERS, EARREONNA    21      B 
## 3         No ALEXANDER_DAJOHN          NA ALEXANDER, DAJOHN    20      B 
## 4         No    ALFARO_JOSEPH          NA    ALFARO, JOSEPH    32      H 
## 5         No        ALLEN_JOE          NA        ALLEN, JOE    32      B 
## 6         No      ALLEN_RICKY          NA      ALLEN, RICKY    38      B 
##   sex.y booking_date.y v7                              housing_address.y
## 1    M     02/07/2020  NA    22233 BUTLER FORD RD, SPRINGDALE, AR 72764-
## 2    F     02/19/2020  NA   250 N AQUA CROSSING, FAYETTEVILLE, AR 72701-
## 3    M     06/28/2019  NA        2401 E 115TH ST, LOS ANGELES, CA 90059-
## 4    M     12/19/2019  NA         7103 GREY DAWN LANE, DALLAS, TX 75227-
## 5    M     02/27/2020  NA         20331 W DEANE, FAYETTEVILLE, AR 72701-
## 6    M     02/06/2020  NA 742 N BETTY JOE DRIVE, FAYETTEVILLE, AR 72701-
##                                                                      charge.y
## 1              Aggravated assault, terroristic threatening, failure to appear
## 2                               Assault, possession, theft, failure to appear
## 3                                                        Hold for other dept.
## 4                                                      Violation of probation
## 5 Public intoxication, possession, fleeing, obstructing government operations
## 6                                         Drugs, possession, parole violation
##   homeless.y
## 1         No
## 2         No
## 3         No
## 4         No
## 5         No
## 6         No

Who was added - use antiJoin - shows 20 additions since March 18

NewInmates <- Roster_March27 %>% 
  anti_join(Roster_March18, by="name2")
nrow(NewInmates)
## [1] 20
head(NewInmates)
##        name_last_first age race sex booking_date
## 1      BARRETT, THOMAS  46    W   M   03/24/2020
## 2        BATES, DONALD  29    W   M   03/27/2020
## 3     BINEJAL, ALBINTA  39    A   M   03/23/2020
## 4       BINGHAM, LUCAS  18    W   M   03/24/2020
## 5 COLLINSWORTH, ROBERT  41    W   M   03/22/2020
## 6      EDWARDS, ARCHIE  63    W   M   03/24/2020
##                               housing_address
## 1           HOMELESS, FAYETTEVILLE, AR 72701-
## 2       3300 DIANNA ST, SPRINGDALE, AR 72764-
## 3  3164 BRAXTON AVENUE, SPRINGDALE, AR 72764-
## 4 121 SOUTH PINE STREET, NASHVILLE, AR 71852-
## 5          420 MAINSTREET, WINSLOW, AR 72719-
## 6      1832 S SCHOOL, FAYETTEVILLE, AR 72701-
##                                                                                                            charge
## 1                                                     Public intoxication, parole violation, hold for other dept.
## 2                                              False imprisonment, assault on family member, failure to pay fines
## 3                                                                                     Negligent homicide, battery
## 4                                                                                               Failure to appear
## 5                                                                  Driving violations, fleeing, contempt of court
## 6 False imprisonment, assault on family member, battery, harassment, public intoxication, terroristic threatening
##   homeless               name2
## 1      Yes      BARRETT_THOMAS
## 2       No        BATES_DONALD
## 3       No     BINEJAL_ALBINTA
## 4       No       BINGHAM_LUCAS
## 5       No COLLINSWORTH_ROBERT
## 6      Yes      EDWARDS_ARCHIE

Who departed - flip the antiJoin, start with March 18. 238 people

Departed <- Roster_March18 %>% 
  anti_join(Roster_March27, by="name2")
head(Departed)
##   placeholder   name_last_first age race sex booking_date v7
## 1          NA   ALARCON, BRYAN  20    H   M   02/17/2020  NA
## 2          NA     ALLEN, DAVID  31    W   M   03/04/2020  NA
## 3          NA  ALLEN, QUINTTON  29    B   M   01/21/2020  NA
## 4          NA ALVARADO, COLTON  26    W   M   03/13/2020  NA
## 5          NA ARCEO, ALEXANDER  36    H   M   12/20/2019  NA
## 6          NA  BAILEY, RACHAEL  32    W   F   01/12/2020  NA
##                                   housing_address
## 1                  722 EMERALD, LOWELL, AR 72745-
## 2           8401 TRAILS IN LANE, OZARK, AR 72949-
## 3               HOMELESS, FAYETTEVILLE, AR 72701-
## 4                     SUPER 8, LINCOLN, AR 72744-
## 5             836 TAMARACK, SPRINGDALE, AR 72764-
## 6 97 DOUBLE SPRINGS ROAD, FAYETTEVILLE, AR 72701-
##                                                                   charge
## 1                                                      Failure to appear
## 2 Public intoxication, identify fraud, obstructing government operations
## 3                        Assault, probation violation, failure to appear
## 4                                    Drug possession, firearm possession
## 5                                                      Failure to appear
## 6                                                      Failure to appear
##   homeless           name2
## 1       No   ALARCON_BRYAN
## 2       No     ALLEN_DAVID
## 3      Yes  ALLEN_QUINTTON
## 4      Yes ALVARADO_COLTON
## 5       No ARCEO_ALEXANDER
## 6       No  BAILEY_RACHAEL
glimpse(Departed)
## Observations: 238
## Variables: 11
## $ placeholder     <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ name_last_first <chr> "ALARCON, BRYAN ", "ALLEN, DAVID ", "ALLEN, QUIN…
## $ age             <chr> "20 ", "31 ", "29 ", "26 ", "36 ", "32 ", "36 ",…
## $ race            <chr> "H ", "W ", "B ", "W ", "H ", "W ", "W ", "W ", …
## $ sex             <chr> "M ", "M ", "M ", "M ", "M ", "F ", "F ", "M ", …
## $ booking_date    <chr> "02/17/2020 ", "03/04/2020 ", "01/21/2020 ", "03…
## $ v7              <lgl> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, …
## $ housing_address <chr> "722 EMERALD, LOWELL, AR 72745-", "8401 TRAILS I…
## $ charge          <chr> "Failure to appear", "Public intoxication, ident…
## $ homeless        <chr> "No", "No", "Yes", "Yes", "No", "No", "No", "No"…
## $ name2           <chr> "ALARCON_BRYAN", "ALLEN_DAVID", "ALLEN_QUINTTON"…

Chart who was released

33 Homeless people

Departed %>% 
  count(homeless)
## # A tibble: 2 x 2
##   homeless     n
##   <chr>    <int>
## 1 No         205
## 2 Yes         33
Departed %>% 
  count(homeless) %>% 
ggplot() +
  geom_bar(mapping=aes(x= homeless, y= n, fill= n), stat = "identity", show.legend = FALSE) +
  coord_flip() +   
  geom_text(aes(label= n, x= homeless, y= n), hjust=-1, vjust=1) +
  scale_y_continuous(limits=c(0, 225)) +
  labs(title= "Housing Status for Released Detainees",
       subtitle= "Wash. Co. Sheriff Detainee Roster, 3/18 and 3/28/2020",
       caption= "Graphic by Michael Adkison",
       x= "Housing Status",
       y= "Number of released detainees")