library(tidyverse)
library(readr)
library(readxl)
library(ggplot2)
library(knitr)
crimerates <- read_csv("Crime_2015.csv")
CollegeScorecard <- read_csv("CollegeScorecard.csv") %>% rename(City=CITY)
CollegeScorecardDataDictionary <- read_excel("CollegeScorecardDataDictionary.xlsx")
library(dplyr)
crimerates <- crimerates %>% drop_na()
crimerates %>% select(MSA, ViolentCrime, State, City) %>% filter(ViolentCrime < median(ViolentCrime)) %>% arrange(ViolentCrime) %>% drop_na(ViolentCrime)
## # A tibble: 183 × 4
## MSA ViolentCrime State City
## <chr> <dbl> <chr> <chr>
## 1 Logan, UT-ID M.S.A. 61.6 UT Logan
## 2 Bangor, ME M.S.A. 67.3 ME Bang…
## 3 Provo-Orem, UT M.S.A. 71.7 UT Provo
## 4 San German, Puerto Rico M.S.A. 92.1 San German, Puerto … San …
## 5 Arecibo, Puerto Rico M.S.A. 99.3 Arecibo, Puerto Ric… Arec…
## 6 Wenatchee, WA M.S.A. 100. WA Wena…
## 7 State College, PA M.S.A. 103. PA Stat…
## 8 Elizabethtown-Fort Knox, KY M.S.A. 105. KY Eliz…
## 9 Columbus, IN M.S.A. 109. IN Colu…
## 10 Aguadilla-Isabela, Puerto Rico M.S.A. 109. Aguadilla-Isabela, … Agua…
## # … with 173 more rows
accred <- CollegeScorecard %>% drop_na()
CollegeScorecard %>% select(INSTNM, City, STABBR, AccredAgency) %>% drop_na(AccredAgency) %>% group_by(AccredAgency) %>% filter(AccredAgency == "Southern Association of Colleges and Schools Commission on Colleges")
## # A tibble: 740 × 4
## # Groups: AccredAgency [1]
## INSTNM City STABBR AccredAgency
## <chr> <chr> <chr> <chr>
## 1 Alabama A & M University Normal AL Southern Associ…
## 2 University of Alabama at Birmingham Birmingham AL Southern Associ…
## 3 Amridge University Montgomery AL Southern Associ…
## 4 University of Alabama in Huntsville Huntsville AL Southern Associ…
## 5 Alabama State University Montgomery AL Southern Associ…
## 6 The University of Alabama Tuscaloosa AL Southern Associ…
## 7 Central Alabama Community College Alexander City AL Southern Associ…
## 8 Athens State University Athens AL Southern Associ…
## 9 Auburn University at Montgomery Montgomery AL Southern Associ…
## 10 Auburn University Auburn University AL Southern Associ…
## # … with 730 more rows
satscores <- CollegeScorecard %>% drop_na()
CollegeScorecard %>% select(INSTNM, City, STABBR, SATMTMID) %>% drop_na(SATMTMID) %>% filter(SATMTMID > median(SATMTMID)) %>% arrange(desc(SATMTMID))
## # A tibble: 644 × 4
## INSTNM City STABBR SATMTMID
## <chr> <chr> <chr> <dbl>
## 1 California Institute of Technology Pasadena CA 785
## 2 Massachusetts Institute of Technology Cambridge MA 775
## 3 Franklin W Olin College of Engineering Needham MA 764
## 4 Harvey Mudd College Claremont CA 760
## 5 University of Chicago Chicago IL 755
## 6 Harvard University Cambridge MA 755
## 7 Washington University in St Louis Saint Louis MO 755
## 8 Princeton University Princeton NJ 755
## 9 Yale University New Haven CT 750
## 10 Vanderbilt University Nashville TN 750
## # … with 634 more rows
accredandsat <- CollegeScorecard %>% drop_na()
CollegeScorecard %>% select(INSTNM, City, STABBR, AccredAgency, SATMTMID) %>% drop_na(AccredAgency) %>% drop_na(SATMTMID) %>% filter(SATMTMID > median(SATMTMID)) %>% arrange(desc(SATMTMID))
## # A tibble: 620 × 5
## INSTNM City STABBR AccredAge…¹ SATMT…²
## <chr> <chr> <chr> <chr> <dbl>
## 1 California Institute of Technology Pasadena CA Western As… 785
## 2 Massachusetts Institute of Technology Cambridge MA New Englan… 775
## 3 Franklin W Olin College of Engineering Needham MA New Englan… 764
## 4 Harvey Mudd College Claremont CA Western As… 760
## 5 University of Chicago Chicago IL North Cent… 755
## 6 Harvard University Cambridge MA New Englan… 755
## 7 Washington University in St Louis Saint Louis MO North Cent… 755
## 8 Princeton University Princeton NJ Middle Sta… 755
## 9 Yale University New Haven CT New Englan… 750
## 10 Vanderbilt University Nashville TN Southern A… 750
## # … with 610 more rows, and abbreviated variable names ¹AccredAgency, ²SATMTMID
topcolleges <- crimerates %>% inner_join(CollegeScorecard, by="City")
toplist <- topcolleges %>% select(ViolentCrime, City, INSTNM, STABBR, AccredAgency, SATMTMID) %>% drop_na(AccredAgency) %>% drop_na(SATMTMID) %>% drop_na(ViolentCrime) %>% filter(SATMTMID > median(SATMTMID)) %>% arrange(desc(SATMTMID)) %>% filter(ViolentCrime < median(ViolentCrime)) %>% filter(AccredAgency == "Southern Association of Colleges and Schools Commission on Colleges")
toplist
## # A tibble: 33 × 6
## ViolentCrime City INSTNM STABBR Accre…¹ SATMT…²
## <dbl> <chr> <chr> <chr> <chr> <dbl>
## 1 256. Lexington Washington and Lee Unive… VA Southe… 690
## 2 165. Charlottesville University of Virginia-M… VA Southe… 685
## 3 338. Dallas Southern Methodist Unive… TX Southe… 660
## 4 331. Dallas Southern Methodist Unive… TX Southe… 660
## 5 288. Austin The University of Texas … TX Southe… 650
## 6 189. Gainesville University of Florida FL Southe… 640
## 7 326. Athens University of Georgia GA Southe… 625
## 8 327. Sherman Austin College TX Southe… 615
## 9 316. College Station Texas A & M University-C… TX Southe… 605
## 10 351. Fort Worth Texas Christian Universi… TX Southe… 600
## # … with 23 more rows, and abbreviated variable names ¹AccredAgency, ²SATMTMID
There are a total of 33 colleges that meet Cornelia Crosby’s requirements, which are shown in the table below with requested data, listed from highest median midpoint SAT score to lowest.
kable(toplist)
| ViolentCrime | City | INSTNM | STABBR | AccredAgency | SATMTMID |
|---|---|---|---|---|---|
| 256.3 | Lexington | Washington and Lee University | VA | Southern Association of Colleges and Schools Commission on Colleges | 690 |
| 164.6 | Charlottesville | University of Virginia-Main Campus | VA | Southern Association of Colleges and Schools Commission on Colleges | 685 |
| 337.6 | Dallas | Southern Methodist University | TX | Southern Association of Colleges and Schools Commission on Colleges | 660 |
| 330.6 | Dallas | Southern Methodist University | TX | Southern Association of Colleges and Schools Commission on Colleges | 660 |
| 287.7 | Austin | The University of Texas at Austin | TX | Southern Association of Colleges and Schools Commission on Colleges | 650 |
| 188.8 | Gainesville | University of Florida | FL | Southern Association of Colleges and Schools Commission on Colleges | 640 |
| 325.7 | Athens | University of Georgia | GA | Southern Association of Colleges and Schools Commission on Colleges | 625 |
| 327.1 | Sherman | Austin College | TX | Southern Association of Colleges and Schools Commission on Colleges | 615 |
| 315.5 | College Station | Texas A & M University-College Station | TX | Southern Association of Colleges and Schools Commission on Colleges | 605 |
| 351.2 | Fort Worth | Texas Christian University | TX | Southern Association of Colleges and Schools Commission on Colleges | 600 |
| 202.0 | Asheville | University of North Carolina at Asheville | NC | Southern Association of Colleges and Schools Commission on Colleges | 595 |
| 108.6 | Columbus | Mississippi University for Women | MS | Southern Association of Colleges and Schools Commission on Colleges | 595 |
| 298.0 | Columbus | Mississippi University for Women | MS | Southern Association of Colleges and Schools Commission on Colleges | 595 |
| 359.4 | Jackson | Union University | TN | Southern Association of Colleges and Schools Commission on Colleges | 595 |
| 301.6 | Longview | LeTourneau University | TX | Southern Association of Colleges and Schools Commission on Colleges | 595 |
| 319.9 | Macon | Mercer University | GA | Southern Association of Colleges and Schools Commission on Colleges | 590 |
| 145.8 | Harrisonburg | James Madison University | VA | Southern Association of Colleges and Schools Commission on Colleges | 580 |
| 256.3 | Lexington | Virginia Military Institute | VA | Southern Association of Colleges and Schools Commission on Colleges | 580 |
| 174.6 | Jacksonville | University of North Florida | FL | Southern Association of Colleges and Schools Commission on Colleges | 575 |
| 359.4 | Jackson | Millsaps College | MS | Southern Association of Colleges and Schools Commission on Colleges | 570 |
| 256.3 | Lexington | Transylvania University | KY | Southern Association of Colleges and Schools Commission on Colleges | 570 |
| 256.3 | Lexington | University of Kentucky | KY | Southern Association of Colleges and Schools Commission on Colleges | 565 |
| 337.6 | Dallas | Dallas Baptist University | TX | Southern Association of Colleges and Schools Commission on Colleges | 562 |
| 330.6 | Dallas | Dallas Baptist University | TX | Southern Association of Colleges and Schools Commission on Colleges | 562 |
| 287.7 | Austin | Saint Edward’s University | TX | Southern Association of Colleges and Schools Commission on Colleges | 560 |
| 236.3 | Monroe | University of Louisiana at Monroe | LA | Southern Association of Colleges and Schools Commission on Colleges | 560 |
| 236.5 | Richmond | Virginia Commonwealth University | VA | Southern Association of Colleges and Schools Commission on Colleges | 550 |
| 292.0 | Lafayette | University of Louisiana at Lafayette | LA | Southern Association of Colleges and Schools Commission on Colleges | 545 |
| 339.7 | Lakeland | Florida Southern College | FL | Southern Association of Colleges and Schools Commission on Colleges | 545 |
| 295.7 | Tyler | The University of Texas at Tyler | TX | Southern Association of Colleges and Schools Commission on Colleges | 544 |
| 240.4 | Salem | Roanoke College | VA | Southern Association of Colleges and Schools Commission on Colleges | 540 |
| 305.4 | Dayton | Bryan College-Dayton | TN | Southern Association of Colleges and Schools Commission on Colleges | 535 |
| 180.4 | Lynchburg | Randolph College | VA | Southern Association of Colleges and Schools Commission on Colleges | 535 |