Packages

library(knitr)
library(readxl)
library(readr)
library(tidyverse)
## ── Attaching packages ─────────────────────────────────────── tidyverse 1.3.0 ──
## ✓ ggplot2 3.3.2     ✓ dplyr   1.0.2
## ✓ tibble  3.0.4     ✓ stringr 1.4.0
## ✓ tidyr   1.1.2     ✓ forcats 0.5.0
## ✓ purrr   0.3.4
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()

Data Import - College Scorecard Data (Excel File)

Download to local Excel file:

urlCollegeScorecard <- "http://jsuleiman.com/datasets/CollegeScorecard.xlsx"
destinationCollegeScorecard <- "CollegeScorecard.xlsx"
curl::curl_download(urlCollegeScorecard, destinationCollegeScorecard)

Read local Excel file into a tibble:

collegeScorecard <- read_excel(destinationCollegeScorecard)

Data Import - Crime Statistics by US City / Town (CSV File)

Read CSV via URL into a tibble:

urlCrimeStatistics <- "http://jsuleiman.com/datasets/Crime_2015.csv"
crimeStatistics <- read_csv(urlCrimeStatistics)
## 
## ── Column specification ────────────────────────────────────────────────────────
## cols(
##   MSA = col_character(),
##   ViolentCrime = col_number(),
##   Murder = col_double(),
##   Rape = col_double(),
##   Robbery = col_double(),
##   AggravatedAssault = col_double(),
##   PropertyCrime = col_number(),
##   Burglary = col_number(),
##   Theft = col_number(),
##   MotorVehicleTheft = col_double(),
##   State = col_character(),
##   City = col_character()
## )

Data of Interest - Consolidated

Create a new college scorecard tibble that only includes college Name, City, State (abbreviated), Accrediting Agency and Math SAT Midpoint score:

collegeScorecardMinimal <- collegeScorecard %>% select(INSTNM, CITY, STABBR, AccredAgency, SATMTMID)
collegeScorecardMinimal <- collegeScorecardMinimal %>% rename('Name' = INSTNM, 'City' = CITY, 'State' = STABBR, 'Accrediting Agency' = AccredAgency, 'Math SAT Midpoint' = SATMTMID)

Create a new crime statistics tibble that only includes City, State (abbreviated), and Violent Crime Rate:

crimeStatisticsMinimal <- crimeStatistics %>% select(City, State, ViolentCrime)
crimeStatisticsMinimal <- crimeStatisticsMinimal %>% rename('Violent Crime Rate' = ViolentCrime)

Combine both tibbles by City and State:

combinedMinimal <- collegeScorecardMinimal %>% inner_join(crimeStatisticsMinimal, by = c("City" = "City", "State" = "State"))

Data of Interest - Validated

Drop rows that contain NA values:

validatedCombinedMinimal <- combinedMinimal %>% filter(complete.cases(.))

Data of Interest - Filtered By Criteria

Calculate Violent Crime Rate Median of the validated data set and display:

medianViolentCrimeRate <- median(validatedCombinedMinimal$`Violent Crime Rate`)
medianViolentCrimeRate
## [1] 401

Calculate Math SAT Midpoint median of the validated data set and display:

medianMathSATMidpoint <- median(validatedCombinedMinimal$`Math SAT Midpoint`)
medianMathSATMidpoint
## [1] 530

Filter validated data set by Violent Crime Rate < Median, Accredited by Southern Association of Colleges and Schools Commission on Colleges and Math SAT Midpoint > Median

criteria <- validatedCombinedMinimal %>% filter(validatedCombinedMinimal$`Violent Crime Rate` < medianViolentCrimeRate, validatedCombinedMinimal$`Accrediting Agency`=="Southern Association of Colleges and Schools Commission on Colleges", validatedCombinedMinimal$`Math SAT Midpoint` > medianMathSATMidpoint)

Report

List of colleges showing the name, city, violent crime rate and accrediting agency - sorted by midpoint SAT math score from highest to lowest:

criteria <- criteria %>% arrange(desc(criteria$`Math SAT Midpoint`))
kable(criteria)
Name City State Accrediting Agency Math SAT Midpoint Violent Crime Rate
University of Virginia-Main Campus Charlottesville VA Southern Association of Colleges and Schools Commission on Colleges 685 164.6
Southern Methodist University Dallas TX Southern Association of Colleges and Schools Commission on Colleges 660 337.6
Southern Methodist University Dallas TX Southern Association of Colleges and Schools Commission on Colleges 660 330.6
The University of Texas at Austin Austin TX Southern Association of Colleges and Schools Commission on Colleges 650 287.7
University of Georgia Athens GA Southern Association of Colleges and Schools Commission on Colleges 625 325.7
Austin College Sherman TX Southern Association of Colleges and Schools Commission on Colleges 615 327.1
Texas A & M University-College Station College Station TX Southern Association of Colleges and Schools Commission on Colleges 605 315.5
Texas Christian University Fort Worth TX Southern Association of Colleges and Schools Commission on Colleges 600 351.2
University of South Florida-Main Campus Tampa FL Southern Association of Colleges and Schools Commission on Colleges 595 384.1
University of North Carolina at Asheville Asheville NC Southern Association of Colleges and Schools Commission on Colleges 595 202.0
Mercer University Macon GA Southern Association of Colleges and Schools Commission on Colleges 590 319.9
College of Charleston Charleston SC Southern Association of Colleges and Schools Commission on Colleges 585 399.0
James Madison University Harrisonburg VA Southern Association of Colleges and Schools Commission on Colleges 580 145.8
The University of Alabama Tuscaloosa AL Southern Association of Colleges and Schools Commission on Colleges 570 392.9
Transylvania University Lexington KY Southern Association of Colleges and Schools Commission on Colleges 570 256.3
Millsaps College Jackson MS Southern Association of Colleges and Schools Commission on Colleges 570 359.4
University of Kentucky Lexington KY Southern Association of Colleges and Schools Commission on Colleges 565 256.3
Dallas Baptist University Dallas TX Southern Association of Colleges and Schools Commission on Colleges 562 337.6
Dallas Baptist University Dallas TX Southern Association of Colleges and Schools Commission on Colleges 562 330.6
Saint Edward’s University Austin TX Southern Association of Colleges and Schools Commission on Colleges 560 287.7
Citadel Military College of South Carolina Charleston SC Southern Association of Colleges and Schools Commission on Colleges 550 399.0
Virginia Commonwealth University Richmond VA Southern Association of Colleges and Schools Commission on Colleges 550 236.5
Florida Southern College Lakeland FL Southern Association of Colleges and Schools Commission on Colleges 545 339.7
The University of Texas at Tyler Tyler TX Southern Association of Colleges and Schools Commission on Colleges 544 295.7
The University of Tampa Tampa FL Southern Association of Colleges and Schools Commission on Colleges 540 384.1
Randolph College Lynchburg VA Southern Association of Colleges and Schools Commission on Colleges 535 180.4

Total number of institutions meeting criteria:

count(criteria)
## # A tibble: 1 x 1
##       n
##   <int>
## 1    26