Introduction

Background

Cornelia Thurza “Fly Rod” Crosby is a junior at Forest Hills Consolidated High School in Jackman, Maine. She is thinking about college applications and, since it has been an especially cold winter in Jackman, she has decided she wants to go to school in the Southeastern United States.

Problem

Cornelia is a good student and wants a list of colleges that meet the following three criteria:

  1. A violent crime rate below the median of the dataset.
  2. Accredited by Southern Association of Colleges and Schools Commission on Colleges.
  3. The midpoint of SAT scores at the institution (math) above the median of the dataset.

Data Import

#install.packages('readr')
#install.packages('rsconnect')
library(rsconnect)
library(tidyverse)
library(knitr)
CollegeScorecard <- read_csv("CollegeScorecard.csv")
CollegeScorecard <- CollegeScorecard %>% rename(City = CITY)
Crime_2015 <- read_csv("Crime_2015.csv") 
#CollegeScorecard <- read_csv("http://jsuleiman.com/datasets/CollegeScorecard.csv)
#Crime_2015 <- read_csv(" http://jsuleiman.com/datasets/Crime_2015.csv")

Data

Joining Data

Below is a new tibble called ‘CollegeAndCrime’, which combines both datasets by city with an inner join. Then, all NA values were removed from the 3 relevant columns of information that Cornelia is basing her decision off of.

CollegeAndCrime <- CollegeScorecard %>% inner_join(Crime_2015, by="City")
CollegeAndCrime <- CollegeAndCrime  %>% drop_na(any_of(c("ViolentCrime", "AccredAgency", "SATMTMID")))
head(CollegeAndCrime)
## # A tibble: 6 x 1,740
##   UNITID  OPEID opeid6 INSTNM City  STABBR ZIP   AccredAgency INSTURL NPCURL
##    <dbl>  <dbl>  <dbl> <chr>  <chr> <chr>  <chr> <chr>        <chr>   <chr> 
## 1 100663 105200   1052 Unive… Birm… AL     3529… Southern As… www.ua… www.c…
## 2 100706 105500   1055 Unive… Hunt… AL     35899 Southern As… www.ua… finai…
## 3 100724 100500   1005 Alaba… Mont… AL     3610… Southern As… www.al… www.a…
## 4 100724 100500   1005 Alaba… Mont… AL     3610… Southern As… www.al… www.a…
## 5 100751 105100   1051 The U… Tusc… AL     3548… Southern As… www.ua… oira.…
## 6 100937 101200   1012 Birmi… Birm… AL     35254 Southern As… www.bs… www.b…
## # … with 1,730 more variables: sch_deg <lgl>, HCM2 <dbl>, main <dbl>,
## #   NUMBRANCH <dbl>, PREDDEG <dbl>, HIGHDEG <dbl>, CONTROL <dbl>,
## #   st_fips <dbl>, region <dbl>, LOCALE <dbl>, locale2 <lgl>, LATITUDE <dbl>,
## #   LONGITUDE <dbl>, CCBASIC <dbl>, CCUGPROF <dbl>, CCSIZSET <dbl>, HBCU <dbl>,
## #   PBI <dbl>, ANNHI <dbl>, TRIBAL <dbl>, AANAPII <dbl>, HSI <dbl>,
## #   NANTI <dbl>, MENONLY <dbl>, WOMENONLY <dbl>, RELAFFIL <dbl>,
## #   ADM_RATE <dbl>, ADM_RATE_ALL <dbl>, SATVR25 <dbl>, SATVR75 <dbl>,
## #   SATMT25 <dbl>, SATMT75 <dbl>, SATWR25 <dbl>, SATWR75 <dbl>, SATVRMID <dbl>,
## #   SATMTMID <dbl>, SATWRMID <dbl>, ACTCM25 <dbl>, ACTCM75 <dbl>,
## #   ACTEN25 <dbl>, ACTEN75 <dbl>, ACTMT25 <dbl>, ACTMT75 <dbl>, ACTWR25 <dbl>,
## #   ACTWR75 <dbl>, ACTCMMID <dbl>, ACTENMID <dbl>, ACTMTMID <dbl>,
## #   ACTWRMID <dbl>, SAT_AVG <dbl>, SAT_AVG_ALL <dbl>, PCIP01 <dbl>,
## #   PCIP03 <dbl>, PCIP04 <dbl>, PCIP05 <dbl>, PCIP09 <dbl>, PCIP10 <dbl>,
## #   PCIP11 <dbl>, PCIP12 <dbl>, PCIP13 <dbl>, PCIP14 <dbl>, PCIP15 <dbl>,
## #   PCIP16 <dbl>, PCIP19 <dbl>, PCIP22 <dbl>, PCIP23 <dbl>, PCIP24 <dbl>,
## #   PCIP25 <dbl>, PCIP26 <dbl>, PCIP27 <dbl>, PCIP29 <dbl>, PCIP30 <dbl>,
## #   PCIP31 <dbl>, PCIP38 <dbl>, PCIP39 <dbl>, PCIP40 <dbl>, PCIP41 <dbl>,
## #   PCIP42 <dbl>, PCIP43 <dbl>, PCIP44 <dbl>, PCIP45 <dbl>, PCIP46 <dbl>,
## #   PCIP47 <dbl>, PCIP48 <dbl>, PCIP49 <dbl>, PCIP50 <dbl>, PCIP51 <dbl>,
## #   PCIP52 <dbl>, PCIP54 <dbl>, CIP01CERT1 <dbl>, CIP01CERT2 <dbl>,
## #   CIP01ASSOC <dbl>, CIP01CERT4 <dbl>, CIP01BACHL <dbl>, CIP03CERT1 <dbl>,
## #   CIP03CERT2 <dbl>, CIP03ASSOC <dbl>, CIP03CERT4 <dbl>, CIP03BACHL <dbl>,
## #   CIP04CERT1 <dbl>, …

Calculations

median(CollegeAndCrime$ViolentCrime)
## [1] 395.3
median(CollegeAndCrime$SATMTMID)
## [1] 530

Results

Below is a list of colleges showing the name, city, violent crime rate, accrediting agency, and midpoint SAT math score for colleges that meet Cornelia’s criteria. It is sorted by the midpoint SAT math score, from highest to lowest. Hopefully this will aid Cornelia is choosing which of these 36 schools is the right fit for her!

IdealColleges_data <- CollegeAndCrime %>%
  filter(ViolentCrime < 395, AccredAgency =="Southern Association of Colleges and Schools Commission on Colleges", SATMTMID > 530)
IdealColleges<- IdealColleges_data %>% select(INSTNM, City, ViolentCrime, AccredAgency, SATMTMID)

IdealColleges <- IdealColleges %>% arrange(desc(SATMTMID))
IdealColleges <- IdealColleges %>% rename(Institution = INSTNM, Violent_Crime = ViolentCrime, Accreditation_Agency = AccredAgency, SAT_Math_Midpoint= SATMTMID)
kable(IdealColleges)
Institution City Violent_Crime Accreditation_Agency SAT_Math_Midpoint
Washington and Lee University Lexington 256.3 Southern Association of Colleges and Schools Commission on Colleges 690
University of Virginia-Main Campus Charlottesville 164.6 Southern Association of Colleges and Schools Commission on Colleges 685
Southern Methodist University Dallas 337.6 Southern Association of Colleges and Schools Commission on Colleges 660
Southern Methodist University Dallas 330.6 Southern Association of Colleges and Schools Commission on Colleges 660
The University of Texas at Austin Austin 287.7 Southern Association of Colleges and Schools Commission on Colleges 650
University of Florida Gainesville 188.8 Southern Association of Colleges and Schools Commission on Colleges 640
University of Georgia Athens 325.7 Southern Association of Colleges and Schools Commission on Colleges 625
Austin College Sherman 327.1 Southern Association of Colleges and Schools Commission on Colleges 615
Texas A & M University-College Station College Station 315.5 Southern Association of Colleges and Schools Commission on Colleges 605
Texas Christian University Fort Worth 351.2 Southern Association of Colleges and Schools Commission on Colleges 600
University of South Florida-Main Campus Tampa 384.1 Southern Association of Colleges and Schools Commission on Colleges 595
Mississippi University for Women Columbus 108.6 Southern Association of Colleges and Schools Commission on Colleges 595
Mississippi University for Women Columbus 298.0 Southern Association of Colleges and Schools Commission on Colleges 595
University of North Carolina at Asheville Asheville 202.0 Southern Association of Colleges and Schools Commission on Colleges 595
Union University Jackson 359.4 Southern Association of Colleges and Schools Commission on Colleges 595
LeTourneau University Longview 301.6 Southern Association of Colleges and Schools Commission on Colleges 595
Mercer University Macon 319.9 Southern Association of Colleges and Schools Commission on Colleges 590
James Madison University Harrisonburg 145.8 Southern Association of Colleges and Schools Commission on Colleges 580
Virginia Military Institute Lexington 256.3 Southern Association of Colleges and Schools Commission on Colleges 580
University of North Florida Jacksonville 174.6 Southern Association of Colleges and Schools Commission on Colleges 575
The University of Alabama Tuscaloosa 392.9 Southern Association of Colleges and Schools Commission on Colleges 570
Transylvania University Lexington 256.3 Southern Association of Colleges and Schools Commission on Colleges 570
Millsaps College Jackson 359.4 Southern Association of Colleges and Schools Commission on Colleges 570
University of Kentucky Lexington 256.3 Southern Association of Colleges and Schools Commission on Colleges 565
Dallas Baptist University Dallas 337.6 Southern Association of Colleges and Schools Commission on Colleges 562
Dallas Baptist University Dallas 330.6 Southern Association of Colleges and Schools Commission on Colleges 562
University of Louisiana at Monroe Monroe 236.3 Southern Association of Colleges and Schools Commission on Colleges 560
Saint Edward’s University Austin 287.7 Southern Association of Colleges and Schools Commission on Colleges 560
Virginia Commonwealth University Richmond 236.5 Southern Association of Colleges and Schools Commission on Colleges 550
Florida Southern College Lakeland 339.7 Southern Association of Colleges and Schools Commission on Colleges 545
University of Louisiana at Lafayette Lafayette 292.0 Southern Association of Colleges and Schools Commission on Colleges 545
The University of Texas at Tyler Tyler 295.7 Southern Association of Colleges and Schools Commission on Colleges 544
The University of Tampa Tampa 384.1 Southern Association of Colleges and Schools Commission on Colleges 540
Roanoke College Salem 240.4 Southern Association of Colleges and Schools Commission on Colleges 540
Bryan College-Dayton Dayton 305.4 Southern Association of Colleges and Schools Commission on Colleges 535
Randolph College Lynchburg 180.4 Southern Association of Colleges and Schools Commission on Colleges 535