Assignment 3

Data Import

library(tidyverse)
Crime_2015 <- read_csv("http://jsuleiman.com/datasets/Crime_2015.csv")
CollegeScorecard <-
  read_csv("http://jsuleiman.com/datasets/CollegeScorecard.csv")

Define Cornelia’s Requirements

Find median of Violent Crime excluding NA values

summarise(Crime_2015, median(ViolentCrime, na.rm = TRUE))

## # A tibble: 1 x 1
##   `median(ViolentCrime, na.rm = TRUE)`
##                                  <dbl>
## 1                                 334.

Find median of Midpoint Math SAT Score excluding NA value

summarise(CollegeScorecard, median(SATMTMID, na.rm = TRUE))

## # A tibble: 1 x 1
##   `median(SATMTMID, na.rm = TRUE)`
##                              <dbl>
## 1                              520

Combine datasets and filter by Cornelia’s requirements

Combine via left join CollegeScorecard and Crime_2015

Cornelia <- CollegeScorecard %>% left_join(Crime_2015, by=c("CITY" = "City", "STABBR" = "State"))

Filter for values meeting Cornelia’s criteria

Cornelia %>% filter(ViolentCrime < 334) %>% filter(SATMTMID > 520) %>% filter(AccredAgency == "Southern Association of Colleges and Schools Commission on Colleges") %>% select(INSTNM, CITY, ViolentCrime, SATMTMID,AccredAgency) %>% slice_max(
SATMTMID, n = 5)

## # A tibble: 5 x 5
##   INSTNM           CITY     ViolentCrime SATMTMID AccredAgency                  
##   <chr>            <chr>           <dbl>    <dbl> <chr>                         
## 1 University of V… Charlot…         165.      685 Southern Association of Colle…
## 2 Southern Method… Dallas           331.      660 Southern Association of Colle…
## 3 The University … Austin           288.      650 Southern Association of Colle…
## 4 University of G… Athens           326.      625 Southern Association of Colle…
## 5 Austin College   Sherman          327.      615 Southern Association of Colle…

Create output dataframe for Cornelia

Output <- (Cornelia %>% select(INSTNM, CITY, ViolentCrime, SATMTMID,AccredAgency)) %>% filter(ViolentCrime < 334) %>%  filter(SATMTMID > 520) %>% filter(AccredAgency == "Southern Association of Colleges and Schools Commission on Colleges")

Output <- aggregate(ViolentCrime ~ INSTNM+CITY+SATMTMID+AccredAgency, Output, mean)

Output <- Output %>% select(INSTNM, CITY, SATMTMID, ViolentCrime, AccredAgency) %>% arrange(desc(SATMTMID))

Output$row_num <- seq.int(nrow(Output))

Output <- Output %>% select(INSTNM, CITY, SATMTMID, ViolentCrime, AccredAgency, row_num) %>% arrange(desc(SATMTMID))

Create table for Cornelia

library(knitr) 
kable(Output, 
col.names = c('College Name', 'City', 'SAT Midpoint Math Score', 'Violent Crime Rate', 'Accreditation Agency', 'Count'), align = "cccccr", caption = "Cornelia's College Scorecard")