INSS615 Homework 5

Morgan State University

Department of Information Science & Systems

Fall 2024

INSS 615: Data Wrangling for Visualization

Name: Frenandez Lawrence

Due: Dec 1, 2024 (Sunday)

Questions

A. Scrape the College Ranked by Acceptance Rate dataset available at this link: https://www.oedb.org/rankings/acceptance-rate/#table-rankings and select the first 9 columns [Rank, School, Student to Faculty Ratio, Graduation Rate, Retention Rate, Acceptance Rate, Enrollment Rate, Institutional Aid Rate, and Default Rate] as the dataset for this assignment. [20 Points]

Hint: There are 6 pages of data, so you may want to use a for loop to automate the scraping process and combine the data from all 6 pages. This is just a suggestion—you are free to create the dataset without automating the web scrapping process.

Solution:

file_path <- "~/Downloads/college_acceptance_data.csv"

# Save the dataset as a CSV file
write.csv(dataset, file = file_path, row.names = FALSE)

# Message to confirm saving
cat("Dataset saved as:", file_path)

Dataset saved as: ~/Downloads/college_acceptance_data.csv

B. You are going to need the dataset created in Question A to answer the following questions. There are 16 questions each carrying 5 points:

1. Replace the missing values “N/A” in the dataset with NA.

Solution:

path = "/Users/frenandezlawrence/Downloads/college_acceptance_data.csv"
data <- read.csv(path)
head(data, n=5)

# Replace "N/A" with NA in the entire dataframe
data_cleaned <- data
data_cleaned[data_cleaned == "N/A"] <- NA


View(data_cleaned)

2. Convert percentage columns (e.g., Graduation Rate) to numeric format.

Solution:

# Load necessary library
library(dplyr)

# Convert percentage columns to numeric
data_cleaned <- data_cleaned %>%
  mutate(across(
    c(`Graduation.Rate`, `Retention.Rate`, `Acceptance.Rate`, `Enrollment.Rate`, `Institutional.Aid.Rate`, `Default.Rate`), 
    ~ as.numeric(gsub("%", "", .))
  ))

View(data_cleaned)

3. Transform the “Student to Faculty Ratio” column into two separate numeric columns: Students and Faculty.

Solution:

# Load necessary library
library(dplyr)
library(stringr)

# Extract "Students" and "Faculty" into new columns
data_cleaned <- data_cleaned %>%
  mutate(
    Students = as.numeric(str_extract(`Student.to.Faculty.Ratio`, "^[0-9]+")),
    Faculty = as.numeric(str_extract(`Student.to.Faculty.Ratio`, "[0-9]+$"))
  )
View(data_cleaned)

4. What is the count of missing values in the “Default Rate” column? Impute the missing values in the “Default Rate” column with the median value.

Solution:

missing_count <- sum(is.na(data_cleaned$'Default.Rate'))
print(missing_count)

[1] 0

5. Find the average graduation rate for universities ranked in the top 50.

Solution:

# Filter the dataset for top 50 universities
top_50_universities <- data_cleaned %>% filter(Rank <= 50)

# Calculate the average graduation rate
average_graduation_rate <- mean(top_50_universities$`Graduation.Rate`, na.rm = TRUE)

# Display the result
print(average_graduation_rate)

[1] 79.2

6. Filter universities with a retention rate above 90% and find the count of rows in the subset.

Solution:

# Load dplyr
library(dplyr)

# Filter universities with Retention Rate > 90%
high_retention <- data_cleaned %>% filter(`Retention.Rate` > 90)

# Count the rows in the subset
high_retention_count <- nrow(high_retention)

# Display the count
print(high_retention_count)

[1] 300

7. Rank universities by enrollment rate in descending order and display the last 6 rows.

Solution:

# Load dplyr
library(dplyr)

# Rank universities by Enrollment Rate in descending order
ranked_data <- data_cleaned %>% arrange(desc(`Enrollment.Rate`))

# Display the last 6 rows
tail(ranked_data, 6)

NA

8. Create a histogram of graduation rates using ggplot2 library.

Solution:

# Load ggplot2
library(ggplot2)

# Create a histogram of Graduation Rates
ggplot(data_cleaned, aes(x = `Graduation.Rate`)) +
  geom_histogram(binwidth = 5, fill = "blue", color = "black", alpha = 0.7) +
  labs(title = "Histogram of Graduation Rates",
       x = "Graduation Rate (%)",
       y = "Count") +
  theme_minimal()

NA
NA

9. Plot a scatterplot between acceptance rate and enrollment rate using ggplot2 library.

Solution:

# Load ggplot2
library(ggplot2)

# Create a scatterplot
ggplot(data_cleaned, aes(x = `Acceptance.Rate`, y = `Enrollment.Rate`)) +
  geom_point(color = "blue", size = 2, alpha = 0.7) +
  labs(title = "Scatterplot of Acceptance Rate vs Enrollment Rate",
       x = "Acceptance Rate (%)",
       y = "Enrollment Rate (%)") +
  theme_minimal()

NA
NA

10. Calculate the average default rate by aid rate category (e.g., grouped into ranges like 0-20%, 20-40%). Display the categories.

Solution:

# Create Aid Rate categories
data_cleaned$AidRateCategory <- cut(
  data_cleaned$`Institutional.Aid.Rate`,
  breaks = c(0, 20, 40, 60, 80, 100),
  labels = c("0-20%", "20-40%", "40-60%", "60-80%", "80-100%"),
  include.lowest = TRUE
)

# Load dplyr
library(dplyr)

# Calculate average default rate by aid rate category
average_default_rate <- data_cleaned %>%
  group_by(AidRateCategory) %>%
  summarize(AverageDefaultRate = mean(`Default.Rate`, na.rm = TRUE))

# Display the result
print(average_default_rate)

NA
NA

11. Normalize the acceptance rate to a scale of 0-1 and save in a new column “Acceptance Rate Normalized”. Display the first 6 values.

Solution:

library(scales)

Attaching package: ‘scales’

The following object is masked from ‘package:purrr’:

    discard

The following object is masked from ‘package:readr’:

    col_factor

# Normalize Acceptance Rate to 0-1 scale
data_cleaned$`Acceptance Rate Normalized` <- (data_cleaned$`Acceptance.Rate` - min(data_cleaned$`Acceptance.Rate`, na.rm = TRUE)) / 
                                             (max(data_cleaned$`Acceptance.Rate`, na.rm = TRUE) - min(data_cleaned$`Acceptance.Rate`, na.rm = TRUE))

# Display the first 6 values of the normalized column
head(data_cleaned$`Acceptance Rate Normalized`)

[1] 0.0000 0.0238 0.0952 0.1905 0.2143 0.2381

12. What is the count of the duplicate entries in the “School” column? Remove duplicate university entries.

Solution:

# Count duplicate entries in the "School" column
duplicate_count <- sum(duplicated(data_cleaned$School))
print(duplicate_count)

[1] 500

# Load dplyr
library(dplyr)

# Remove duplicate entries based on "School"
data_cleaned <- data_cleaned %>% distinct(School, .keep_all = TRUE)

13. Find the correlation between graduation rate and retention rate (exclude the NAs in both columns).

Solution:

# Calculate the correlation between Graduation Rate and Retention Rate
correlation <- cor(data_cleaned$`Graduation.Rate`, data_cleaned$'Retention.Rate', use = "complete.obs")

# Display the result
print(correlation)

[1] 0.805

14. Extract the values in School column into a new variable without “University” in the string. For example “Rowan University” becomes “Rowan”

Solution:

# Remove "University" from the School column
#school_names <- gsub(" University", "", data_cleaned$School)
#school_names <- gsub("University of ", "", data_cleaned$School)
# Display the result
#print(head(school_names))

# Load stringr
library(stringr)

# Remove "University" from the School column
school_names <- str_replace(data_cleaned$School, " University", "")


# Display the result
print(head(school_names))

[1] "Harvard"                    "Yale"                       "University of Pennsylvania" "Johns Hopkins"              "Cornell"                    "Tufts"                     

15. Count how many universities have “Institute” in their name.

Solution:

# Load stringr
library(stringr)

# Count universities with "Institute" in their name
institute_count <- sum(str_detect(data_cleaned$School, "Institute"))

# Display the count
print(institute_count)

[1] 5

16. Export the cleaned and processed dataset to a CSV file.

Solution:

file_path <- "~/Downloads/cleaned_college_acceptance_data.csv"

# Save the dataset as a CSV file
write.csv(dataset, file = file_path, row.names = FALSE)