Introduction:

Cornelia Thurza “Fly Rod” Crosby is a junior at Forest Hills Consolidated High School in Jackman, Maine. She is starting to think about college applications and, since it has been a particularly cold winter in Jackman, she has decided she wants to experience college in the Southeastern United States. Cornelia is a particularly good student and she wants you to provide her with a list of colleges that meet the criteria provided.

Cornelia has the following explicit criteria for her college:

You’ll need to drop any data that has NA values for the three criteria she provided.

Cornelia would like a list of colleges showing the name, city, violent crime rate, and accrediting agency, for colleges that meet her criteria, sorted by the midpoint SAT math score, from highest to lowest. She would also like a count of the number of institutions that meet her criteria.

Step by Step:

Load packages and data, dropping NA values for needed columns:

library(tidyverse)
library(knitr)
collegescore <- read_csv("http://jsuleiman.com/datasets/CollegeScorecard.csv")
collegescore <- collegescore %>% drop_na("INSTNM", "CITY", "AccredAgency", "SATMTMID")
crimerates <- read_csv("http://jsuleiman.com/datasets/Crime_2015.csv")
crimerates <- crimerates %>% drop_na("ViolentCrime", "City")

Calculate median midpoint Math SAT Score and median crime rate:

medianMathSATMid <- median(collegescore$SATMTMID)
medianViolentCrime <- median(crimerates$ViolentCrime)
head(medianMathSATMid)
## [1] 523
head(medianViolentCrime)
## [1] 333.8

Join data and extract needed columns:

collegestats <- crimerates %>% inner_join(collegescore, by = c("City" = "CITY")) 
collegestats <- collegestats %>% select(INSTNM, City, ViolentCrime, AccredAgency, SATMTMID)

Filter out colleges that meet qualifications, and arrange by Math SAT Midpoint:

collegestats <- collegestats %>% filter(ViolentCrime < medianViolentCrime) %>% filter(SATMTMID > medianMathSATMid) %>% filter(AccredAgency == "Southern Association of Colleges and Schools Commission on Colleges") %>% arrange(desc(SATMTMID))

Results

Finally, clean up column names and display full table:

collegestats <- collegestats %>% rename(College = INSTNM, MathSATMidpoint = SATMTMID)
kable(collegestats)
College City ViolentCrime AccredAgency MathSATMidpoint
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 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
University of North Carolina at Asheville Asheville 202.0 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
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
Transylvania University Lexington 256.3 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 330.6 Southern Association of Colleges and Schools Commission on Colleges 562
Saint Edward’s University Austin 287.7 Southern Association of Colleges and Schools Commission on Colleges 560
University of Louisiana at Monroe Monroe 236.3 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
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
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
Concordia University-Texas Austin 287.7 Southern Association of Colleges and Schools Commission on Colleges 525
Hollins University Roanoke 213.9 Southern Association of Colleges and Schools Commission on Colleges 525

Cornelia also requires a count of the number of colleges that meed the criteria specified:

numbercolleges <- count(collegestats)
head(numbercolleges)
## # A tibble: 1 x 1
##       n
##   <int>
## 1    29

There are 29 colleges that meet the criteria specified by Cornelia.