Using R to combine CSV files into a table & uploading to MySQL

After loading in the necessary package, a function named “uploadCSV” was used to read in all the CSV files and combining it into a table as instructed in the question.

The database details and folder path were first established. Then the list.files command was used to list out all the files that are in CSV format within the specified folder. This function is used instead of listing out all 4 file names, so that it’ll be more sustainable in the future when there’s new CSV files being added to the folder.

Moving on, all the CSV files were then combined into a dataframe called combined_data, breakdown of the command below:

• lapply(csv_files, read.csv, header = TRUE)
  – Within list apply, the function reads each file within the csv_files vector that stores all the csv files, including the header.
  – One dataframe is created for each CSV file.
  
• do.call(rbind,…)
  – do.call is a function that takes a function (in this case, rbind, which binds rows together) and a list of arguments to be passed to that function.
  – In this command, do.call applies the rbind function to the list of dataframes obtained from the previous step.
  – rbind combines these dataframes by stacking them vertically and merging them into a single dataframe.
  – All the separate dataframes from each CSV file will be combined into a single dataframe called combined_data.
  

The connection to the MySQL database was opened, so that a table called “course_status” created with the data from combined_data can be imported into MySQL. Lastly, the connection was closed.

Data Cleaning & Sanity Check in MySQL

1. Check if the rows and columns match the raw data
   
   
   

2. Remove the additional column
   
   

3. Check type of data
   
   

4. Clean up column names
   
   

Create a new table

Table containing:
(i) the number of courses ever offered,
(ii) percentage of active courses offered,
(iii) percentage of retired courses for each program and school

After refreshing MySQL, the table is created as shown below.

Visualise data in R

1. Read in course_summary table into R
   
   
   

2. Data Sanity Check
   
   • The number of rows and columns match the table in MySQL, indicating all the data has been fetched. (93 rows, 5 columns)
     
   • The structure of the data was checked using the str command. The data type observed is correct and ready for plotting.
     
3. Visualisation
   

The senior management team is often constrained by time, and only requires a quick overview of the data. Therefore, the visualisation is simplified to only showcase the total number of active and retired courses within each school. Notably, the School of Science & Technology exhibits the number of retired courses, surpassing active courses. In contrast, other schools demonstrate a higher number of active courses in comparison to their retired courses.

With the visualisation above, the senior management team can analyze and possibly take some of the next steps below and make informed decisions:

• Explore programs in School of Science & Technology, investigate factors that contribute to the high number of retired courses.
• Assess the demand for Science & Technology courses, explore opportunities to enhance program offerings.
• Optimize resource allocation based on ongoing courses.