Introduction

For this code-through I have chosen to explore a publicly available dataset called “College”. I work in higher education, so I wanted to choose a dataset that might have some of the same fields or topics that I encounter on a daily basis. I chose to download this dataset, manipulate it a little bit, create some basic visualizations that can be saved to local files, and then and then write separate dataframes to individual tabs on an excel sheet. This is a common process that I need to do in my job, and using R to help assist with this will be hugely beneficial for my workflow, standardization, and reproducibility.



Content Overview

Specifically, I will explain and demonstrate:

  • how to explore a dataset at a high level to gain a basic understanding
  • how to manipulate the data to tailor it to your needs
  • how to perform basic calculations on the data
  • how to create basic visualizations and save them to a png
  • how to write different dataframes to separate tabs on an excel sheet



Why You Should Care

This topic is valuable because it walks through a ‘complete’ process that one might encounter on a regular basis. From accessing a data source, to familiarizing yourself with it, asking questions about it, manipulating it to be able to conduct analysis to answer those questions, creating basic visualizations that can be saved, and being able to save the output in still one of the most commonly used formats in many businesses and industries. It shows a basic complete cycle and puts everything together in one place.







Obtaining the Dataset

The dataset can be obtained through the resources section of the first edition of the textbook called An Introduction to Statistical Learning (ISLR).



FIGURE 1 - ISLR Textbook



The resources section and data file “college.csv” can be found here.


Make sure you:

  • Download the appropriate file.

  • Save it in a location that is easy to access.





























Preparing your R environment

Install the necessary packages

You will need several packages to be able to read the Google Sheet, as well as manipulate the data. Install the following packages using the install.packages() function, if you have not previously done so. If you have previously installed them, just call them using the “library()” function:

library(tidyverse)  #this package contains the pivot_longer funtion for data manipulation
#library(googlesheets4) #reference on this package can be found here: https://googlesheets4.tidyverse.org/
library(dplyr)  #We will need this for data manipulation
library(ggplot2)  #we will need this for visualization
library(openxlsx) #we will need this for reading/writing Excel files



Load the Data from filepath

# import data:
mydata <- read.csv("C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/College.csv")

Of course you can and should name your dataset so that you’ll be able to easily call it moving forward. In this case, I’ve chosen to name it ‘mydata’.

Once your data is uploaded you should be able to ‘print’ your table like below:

mydata





Exploring the data

When working with a new dataset, it’s important to spend some time familiarizing yourself with it. The first thing I like to do is just print a tibble or dataframe like above to get an overall understanding of what the table looks like.

The first thing I noticed in this dataset is that the first vector appears to have a strange name. In order to confirm the name, you can use the names() function:

names(mydata)
##  [1] "X"           "Private"     "Apps"        "Accept"      "Enroll"     
##  [6] "Top10perc"   "Top25perc"   "F.Undergrad" "P.Undergrad" "Outstate"   
## [11] "Room.Board"  "Books"       "Personal"    "PhD"         "Terminal"   
## [16] "S.F.Ratio"   "perc.alumni" "Expend"      "Grad.Rate"


The name for the first vector is not explanatory. You can rename it like this:

names(mydata)[names(mydata) == "X"] <- "University"
names(mydata)
##  [1] "University"  "Private"     "Apps"        "Accept"      "Enroll"     
##  [6] "Top10perc"   "Top25perc"   "F.Undergrad" "P.Undergrad" "Outstate"   
## [11] "Room.Board"  "Books"       "Personal"    "PhD"         "Terminal"   
## [16] "S.F.Ratio"   "perc.alumni" "Expend"      "Grad.Rate"

The tibble shows there are 777 rows, or Universities, with data included in the dataset. There are also 19 columns that we can see from the dataset, and they are now named relatively meaningfully. Here are some questions that come to mind for possible analysis:

  • What is the total cost of attending each university as an out of state student?
  • What is each university’s acceptance rate?
  • What is the proportion of applicants who enroll?
  • What is each university’s enrollment yield (those who enroll after actually being accepted)?
  • Is there a difference in the above things between private and public universities?





Data manipulation and analysis

Create New Variables of Interest

We can first create some new variables to answer the first four questions above:

  • What is the total cost of attending each university as an out of state student?
  • What is each university’s acceptance rate?
  • What is the proportion of applicants who enroll?
  • What is each university’s enrollment yield (those who enroll after being accepted)?
mynewdata <- mydata%>%
  group_by(University)%>%
  mutate(total.cost=sum(Outstate,Room.Board,Books,Personal),accept.rate=Accept/Apps,enroll.rate=Enroll/Apps,enroll.yield=Enroll/Accept,priv.pub = case_when (Private == "Yes" ~ 'Private',Private == "No" ~ 'Public'))  #Creating priv.pub also for use in later analysis, explained in next section

mynewdata



Compare Private and Public University Metrics

Some universities in this list are private and some are public. This is recorded in the second variable “Private”. The values in this vector are “Yes”, meaning Private, or “No”, meaning public. To make it easier to understand, I also created a new variable “priv.pub” that properly labels each as Private or Public.

So now that our data contains the metrics that we are interested in, we can begin answering our final question: *Is there a difference in the above things between private and public universities?

mynewdatasummary<-mynewdata%>%
  group_by(priv.pub)%>%
  summarise(count=n(),avg.cost=mean(total.cost),avg.apps.recvd=mean(Apps),avg.accept.rate=mean(accept.rate),avg.enrolltoapps.rate=mean(enroll.rate),avg.enrolltoaccept.yield=mean(enroll.yield))
mynewdatasummary

While this is a fairly small dataset and it’s relatively easy to compare the numbers in the table above, especially since we’ve transformed some of the values into proportions, we can also visualize the data.





Data visualization

Comparing Private and Public University Counts

First we can compare the total number of public and private universities:

bardata<-mynewdatasummary%>%
  select(priv.pub,count)
bardata

And plot it into a bar graph:

#####If you want to save the output into a PNG, you must do step 1, step 3 and step 4#####
#####If you only want to create the visualization for your rmd/html and NOT save the viz, you only need to create the barplot and print

# Step 1: You must first open PNG graphics device (if you want to save it to PNG, if not then skip)
png(filename = "C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/appcountplot.png", width = 800, height = 600)

# Step 2: Create bar plot
appcountplot <- barplot(bardata$count,
                        names=bardata$priv.pub,
                        xlab='Institution', 
                        ylab='Applications', 
                        main="Total number of applications", 
                        col = "cadetblue2")

# Step 3: Close the graphics device (if you opened the graphics device in step 1 above, if not then skip)
dev.off()
## png 
##   2
# Step 4: Display the plot in R Markdown (if you did Steps 1 and 3, the plot will not display in rmd/html without the knitr option)
knitr::include_graphics("C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/appcountplot.png")

The code above also includes instructions for how to save the plot output into a PNG to be able to insert into your Excel file later.

Comparing Application Status Counts Private and Public Universities

We can also look at the numbers of applications received, accepted, and ultimately enrolled per institution type. In order to do this, we’ll need to go back to a previous version of the data that contains counts, not proportions:

mynewappdata<-mynewdata%>%
  group_by(priv.pub)%>%
  select(priv.pub,Apps,Accept,Enroll)%>%
summarise(count=n(),avg.1apps.recvd=mean(Apps),avg.2accept=mean(Accept),avg.3enroll=mean(Enroll))
mynewappdata


This format/structure will not allow us to visualize in a line graph, so we need to transform the dataset to a ‘long’ dataset. This means that we need to create more rows and less columns, basically. We can use the ‘pivot_longer’ function like below:

mynewappdatalong<-mynewappdata%>%
  select(!count)%>%  #we don't need the count of universities for the desired visualization, so we can exclude it
  pivot_longer(
    cols = !priv.pub, 
    names_to = "Status", #this changes the 'names' of each column in the previous dataset to a new variable that you name here
    values_to = "count")%>% # this moves the values from the previous dataset into a new variable/column that you specify here
  arrange(Status) #You can arrange the data differently so that it will plot correctly in your visualization.  In this instance, I couldn't figure out how to get it arranged correctly without numbering the columns from before.  Messy, but it worked!
mynewappdatalong


Then we are able to use ggplot to create a line graph showing the two different institution type groups and their application/enrollment statuses:

appstatusplot<-mynewappdatalong%>%
  ggplot()+
    geom_line(mapping=aes(x = Status, y = count, group = priv.pub,col=priv.pub))+
  scale_x_discrete(labels=c('Applications Received', 'Acceptance Letters Issued', 'Enrollments')) #to rename the x-axis labels

appstatusplot

######If we want to save this to a PNG file for inserting into our Excel later, this is how we do it:########

# Save ggplot to a PNG file
ggsave(filename = "C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/appstatusplot.png", 
       plot = appstatusplot,
       width = 8, height = 6)

From this basic summary, we can learn several interesting things. First, there are far more Private Institutions in this dataset than Public, and the cost of the Private institutions is higher (no surprise). The number of applications that Public Institutions receive on average is a lot higher than the private institutions (also not that surprising). It’s interesting when you compare Enrollments for both institution types - the difference between enrollments and applications received at public universities is very different.





Saving the new data to new csv

After manipulating the data, it is oftentimes useful to save the cleaned/manipulated datasets to a local file, either for sharing or for record-keeping purposes. This is a very common practice in my current position - certain colleagues are responsible for different populations of students and I often need to save filtered versions of a total dataset for each colleague to reference easily. I will now explain one way to do this.

Create filtered datasets

#first, I will create two filtered datasets to write to separate tabs on the new excel
privatedata <- mynewdata%>%
  filter(priv.pub == "Private")

publicdata <- mynewdata%>%
  filter(priv.pub == "Public")

Write individual csv files

Next, I need to write individual csv files for separate tabs that will be merged together.

#Total complete dataset
write.csv(mynewdata,"C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/CSVfiles/CompleteData.csv", row.names=FALSE)



#Private university dataset
write.csv(privatedata,"C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/CSVfiles/PrivateData.csv", row.names=FALSE)



#Public university dataset
write.csv(publicdata,"C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/CSVfiles/PublicData.csv", row.names=FALSE)



#Summary data
write.csv(mynewdatasummary,"C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/CSVfiles/SummaryData.csv", row.names=FALSE)

Write CSV files onto separate tabs of an Excel sheet

# Specify the directory containing CSV files
csv_directory <- "C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/CSVfiles/"

# List CSV files in the directory
csv_files <- list.files(path = csv_directory, pattern = "\\.csv$", full.names = TRUE)

# Define Excel file path to be saved to
excel_file <- "C:/Users/mlbrentl/Desktop/PEDAMS/Codethrough/FinalCollegeDataset.xlsx"

# Create a new Excel workbook
wb <- createWorkbook()

# Loop through each CSV file and add its data to a new sheet in the Excel workbook
for (i in seq_along(csv_files)) {
  # Read data from CSV file
  data <- read.csv(csv_files[i])
  
  # Sheet name based on file name
  sheet_name <- gsub(".csv", "", basename(csv_files[i]))
  
  # Add data to a new sheet in the workbook
  addWorksheet(wb, sheetName = sheet_name)
  writeData(wb, sheet = i, x = data)
}

# Save the Excel workbook
saveWorkbook(wb, excel_file)





Saving visualizations

In this process, we have also created a couple visualizations, and while I’m not sure of a way to get them directly embedded into the Excel file, we can save them as png files in the same filepath as the Excel sheet and then insert them manually. These steps were done above in the code chunks when creating the visualizations, so please reference how to do that above. Different plotting functions seem to have slightly different ways to get the images saved, so you might have to ask Google or ChatGPT exactly how to save your visualizations based on your specific plot function. As a reminder, if you want to include the visualizations in your excel sheet, you will need to manually insert them.





Conclusion

This code-through has shown how to use R to read a dataset from a local file, and shared some basic processes to explore, manipulate, and visualize a dataset to answer questions posed. Furthermore, it shows one way to save copies of the manipulated datasets into an Excel file, as well as a couple ways to save the visualizations to a local png file which can be manually embedded into the Excel sheet, if desired. While I’m sure there are much better ways to accomplish these things, I hope this shows a good example of a basis to work from moving forward.





Further Resources

Some extra resources used to assist in the making of this code-through: