library(tidyverse)### Data retrevial
CollegeScorecard <- read_csv("http://jsuleiman.com/datasets/CollegeScorecard.csv")### Removal of NA and blank values
CleanedCollegeScorecardSAT <- CollegeScorecard[!(is.na(CollegeScorecard$SATMTMID) | CollegeScorecard$SATMTMID=="" ),] ### Removal of rows below 520 Median
CleanedCollegeScorecardSATmedian <- filter(CleanedCollegeScorecardSAT, SATMTMID > median(CleanedCollegeScorecardSAT$SATMTMID))###Removal of rows not including Southern Association of Colleges
CleanedCollegeScorecardSATAccred <- CleanedCollegeScorecardSATmedian[CleanedCollegeScorecardSATmedian$AccredAgency
== "Southern Association of Colleges and Schools Commission on Colleges",]### Load Crime Data
CrimeStats <- read_csv("http://jsuleiman.com/datasets/Crime_2015.csv")### Remove Blank and NA values
CrimeStatsNA <- CrimeStats[!(is.na(CrimeStats$ViolentCrime) | CrimeStats$ViolentCrime=="" ),] ###Remove rows above or equal to the median
CleanedCrimeStats <- filter(CrimeStatsNA, ViolentCrime < median(CrimeStatsNA$ViolentCrime))### Rename City to merge correctly
Crimestats1 <- CleanedCrimeStats %>% rename(CITY = City, STABBR = State)### Join Crime Data and CleanedCollegeScorecardSATAccred by CITY and State
Joineddata = Crimestats1 %>% inner_join(CleanedCollegeScorecardSATAccred, by = c("CITY", "STABBR"))### Removed NA values. Not sure if I needed to do this, but at one point I had some issues with NA
CleanedJoinedData <- Joineddata[!(is.na(Joineddata$INSTNM) | Joineddata$INSTNM=="" ),] ### Sorted data by Midpoint math SAT in decending order
Sort <- CleanedJoinedData %>% arrange(desc(SATMTMID))Cornelia, their are 18 institutions that meet your criteria.
### Reported list for Cornelia
Reportingdataclean <- Sort %>% select(College = INSTNM, City = CITY, "Violent Crime Rate" = ViolentCrime, "Accrediting Agency" = AccredAgency)
library(knitr)
kable(Reportingdataclean)| College | City | Violent Crime Rate | Accrediting Agency |
|---|---|---|---|
| University of Virginia-Main Campus | Charlottesville | 164.6 | Southern Association of Colleges and Schools Commission on Colleges |
| Southern Methodist University | Dallas | 330.6 | Southern Association of Colleges and Schools Commission on Colleges |
| The University of Texas at Austin | Austin | 287.7 | Southern Association of Colleges and Schools Commission on Colleges |
| University of Georgia | Athens | 325.7 | Southern Association of Colleges and Schools Commission on Colleges |
| Austin College | Sherman | 327.1 | Southern Association of Colleges and Schools Commission on Colleges |
| Texas A & M University-College Station | College Station | 315.5 | Southern Association of Colleges and Schools Commission on Colleges |
| University of North Carolina at Asheville | Asheville | 202.0 | Southern Association of Colleges and Schools Commission on Colleges |
| Mercer University | Macon | 319.9 | Southern Association of Colleges and Schools Commission on Colleges |
| James Madison University | Harrisonburg | 145.8 | Southern Association of Colleges and Schools Commission on Colleges |
| Transylvania University | Lexington | 256.3 | Southern Association of Colleges and Schools Commission on Colleges |
| University of Kentucky | Lexington | 256.3 | Southern Association of Colleges and Schools Commission on Colleges |
| Dallas Baptist University | Dallas | 330.6 | Southern Association of Colleges and Schools Commission on Colleges |
| Saint Edward’s University | Austin | 287.7 | Southern Association of Colleges and Schools Commission on Colleges |
| Virginia Commonwealth University | Richmond | 236.5 | Southern Association of Colleges and Schools Commission on Colleges |
| The University of Texas at Tyler | Tyler | 295.7 | Southern Association of Colleges and Schools Commission on Colleges |
| Randolph College | Lynchburg | 180.4 | Southern Association of Colleges and Schools Commission on Colleges |
| Concordia University-Texas | Austin | 287.7 | Southern Association of Colleges and Schools Commission on Colleges |
| Hollins University | Roanoke | 213.9 | Southern Association of Colleges and Schools Commission on Colleges |