hatecrimes.Rmd

```{r}
library(tidyverse)
library(readxl)
library(janitor)
library(ggplot2)
```

## load variables

agencyname is there in case I need to join tables to get a closer picture of what department handled what incident
```{r}
hc2021 <- read_csv("~/Desktop/elliesacbeeR/data/Hate-2001-2021.csv")

hcp2021 <- read_csv("~/Desktop/elliesacbeeR/data/Hate-Prosecution-survey-2004-2021.csv")

agencyname <- read_xlsx("~/Desktop/elliesacbeeR/data/Agency Code Jurisdiction List_07072022.xlsx")

hc2022 <- read_csv("~/Desktop/elliesacbeeR/data/Hate-2001-2022f.csv")

hcp2022 <- read_csv("~/Desktop/elliesacbeeR/data/Hate Crime Prosecution Survey 2004-2022_Final.csv")
```

questions i have for past 19 years data: 
- trend of hate crimes against asian people?
- trend of hate crimes against LGBTQ people
  - this will involve grouping, as reported offenses are delineated by anti-gay, anti-lesbian, anti-trans, anti-homosexual, anti-bisexual
- trend of hate crimes based on citizenship status

notes on 2021 data (https://data-openjustice.doj.ca.gov/sites/default/files/2022-08/Hate%20Crime%20In%20CA%202021.pdf)
- most of the race/ethnicity/national origin bias was committed against Black (27%) and Asian (13.4%)
- most of the religious bias was anti-jewish (10.4%)
  - most of the anti-jewish hate crimes were committed against religious organizations, not individuals
- sexual orientation bias made up 16.7% of the total overall hate crimes
  - anti-gay male was 11.3%
  - anti-homosexual was 3.8%
  - anti-lesbian was 1.4%
- there were 43 anti-trans hate crimes in 2021

background the 2021 data
1. how much have hate crimes against black people changed over time? 
2. how much have hate crimes against asian people changed over time?
3. how much have hate crimes against jewish people changed over time?
4. how much have hate crimes against sexual orientation changed over time?
5. how much have hate crimes against trans-people changed over time?

##answer questions 1-5
```{r}
hc2022 %>% group_by(ClosedYear) %>% filter(MostSeriousBias == "Anti-Black or African American") %>% arrange(desc(TotalNumberOfIndividualVictims))

anti_black_over_time <- hc2022 %>% group_by(ClosedYear) %>% summarise(crimes_per_year = sum(TotalNumberOfVictims))

hc2022 %>% group_by(ClosedYear) %>% filter(MostSeriousBias == "Anti-Asian") %>% summarise(victims_per_year = sum(TotalNumberOfVictims))


antiasianfourcounty <- hc2022 %>% group_by(ClosedYear) %>% filter(County == 34 | County == 31 | County == 9 | County == 57) %>% filter(MostSeriousBias == "Anti-Asian") %>% summarise(anti_asian = sum(TotalNumberOfVictims))

antiblackfourcounty <- hc2022 %>% group_by(ClosedYear) %>% filter(County == 34 | County == 31 | County == 9 | County == 57) %>% filter(MostSeriousBias == "Anti-Black or African American") %>% summarise(anti_black = sum(TotalNumberOfVictims))

antijewishfourcounty <- hc2022 %>% group_by(ClosedYear) %>% filter(County == 34 | County == 31 | County == 9 | County == 57) %>% filter(MostSeriousBias == "Anti-Jewish") %>% summarise(anti_jewish = sum(TotalNumberOfVictims))

antilgbtqfourcounty <- hc2022 %>% group_by(ClosedYear) %>% filter(County == 34 | County == 31 | County == 9 | County == 57) %>% filter(MostSeriousBias == "Anti-Gay (Male)" | MostSeriousBias == "Anti-Lesbian" | MostSeriousBias == "Anti-Transgender" | MostSeriousBias == "Anti-Lesbian/Gay/Bisexual or Transgender (Mixed Group)")  %>% summarise(anti_lgbtq = sum(TotalNumberOfVictims))

antihispanicfourcounty <- hc2022 %>% group_by(ClosedYear) %>% filter(County == 34 | County == 31 | County == 9 | County == 57) %>% filter(MostSeriousBias == "Anti-Hispanic or Latino") %>% summarise(anti_hispanic = sum(TotalNumberOfVictims))

antitransfourcounty <- hc2022 %>% group_by(ClosedYear) %>% filter(County == 34 | County == 31 | County == 9 | County == 57) %>%filter(MostSeriousBias == "Anti-Transgender") %>% summarise(anti_trans = sum(TotalNumberOfVictims))

antitransstatewide <- hc2022 %>% group_by(ClosedYear) %>%filter(MostSeriousBias == "Anti-Transgender") %>% summarise(anti_trans = sum(TotalNumberOfVictims))

antiaasianstatewide <- hc2022 %>% group_by(ClosedYear) %>%filter(MostSeriousBias == "Anti-Asian") %>% summarise(anti_asian = sum(TotalNumberOfVictims))

hc2022 %>% group_by(ClosedYear) %>% filter(MostSeriousBias == "Anti-Asian") %>% summarise( n = n())

antiblackstatewide <- hc2022 %>% group_by(ClosedYear) %>%filter(MostSeriousBias == "Anti-Black or African American") %>% summarise(anti_black = sum(TotalNumberOfVictims))

antilgbtqstatewide <- hc2022 %>% group_by(ClosedYear) %>% filter(MostSeriousBias == "Anti-Gay (Male)" | MostSeriousBias == "Anti-Lesbian" | MostSeriousBias == "Anti-Transgender" | MostSeriousBias == "Anti-Lesbian/Gay/Bisexual or Transgender (Mixed Group)")  %>% summarise(anti_lgbtq = sum(TotalNumberOfVictims))

antijewishstatewide <- hc2022 %>% group_by(ClosedYear) %>% filter(MostSeriousBias == "Anti-Jewish") %>% summarise(anti_jewish = sum(TotalNumberOfVictims))

antihispanicstatewide <- hc2022 %>% group_by(ClosedYear) %>% filter(MostSeriousBias == "Anti-Hispanic or Latino") %>% summarise(anti_hispanic = sum(TotalNumberOfVictims))

```

##bycounty
```{r}
saccounty <- hc2022 %>% filter(County == 34)

eldoradocounty <- hc2022 %>% filter(County == 09)

eldoradocounty %>% filter(ClosedYear == 2022) %>%  summarise(number_of_offenders = sum(TotalNumberOfSuspects))
```

##printem
```{r}
write.csv(saccounty, "sacramentocounty2001-2022.csv")

write.csv(eldoradocounty, "eldoradocounty2001-2022.csv")

write.csv(hc2022, "hatecrimehistorical.csv")

write.csv(hcp2022, "hatecrimeprosecutorial.csv")

write.csv(agencyname, "agencynames.csv")

write.csv(antiaasianstatewide, "antiasiansw.csv")

write.csv(antiblackstatewide, "antiblacksw.csv")

write.csv(antijewishstatewide, "antijewishsw.csv")

write.csv(antilgbtqstatewide, "antilgbtqsw.csv")

write.csv(antihispanicstatewide, "antihispanicsw.csv")

write.csv(antitransfourcounty, "antitransfourcounty.csv")

write.csv(antitransstatewide, "antitranssw.csv")
```

additional samson questions
1. what does the suspects race group look like in california? how has that changed over time?