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")

Violent Crime Rates

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

Accredited Schools

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

SAT scores above median

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

Both Accredited and SAT Scores above median

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

List of Colleges that meet all criteria

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

Conclusion

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