Instructure Canvas outcome results R dplyr select mutate left_join

This is a second day of looking at using R to explore the Instructure Canvas admin outcome results file. In my earlier exploration I tackled using group_by and ggplot to display student learning outcome averages by course level learning outcomes. My intent in a second day of work was to perform some extract, transform, and load (ETL) operations up front on the actual CSV export file from Canvas, rename their space punctuated variables, rescale the outcome scores to all be on a five point scale, join the table to a dimension table on a left outer join, and then generate essentially the same graph as I had generated earlier. 

Canvas exports tidy data in the outcome results file which makes possible the use of readr and dplyr from the tidyverse package. That the data is tidy apparently saves weeks of potential cleaning work.

Beyond basic file loading and ggplot work, the following code uses dplyr select to both select and rename eight of the thirty variables in the original CSV export file from Canvas. Mutate is used to rescale the outcomes to all be on a five point scale in a new column called pentascale. This five point scale was explained in more detail in Learning outcomes assessment ratings scales and their interaction with rubrics in Canvas. The code produces two charts, which is awkward, but the code destination would be something like flexdashboard and the charts would both appear on the dashboard. The second chart is preceded by a left_join to a dimension table that maps course level learning outcomes to program learning outcomes.

library(tidyverse)
# Filename should be updated as needed
outcomes <- read_csv("outcome_results_csv_02_Jul_2021_13020210702-16780-19vemtl.csv")
# Load a custom prepared dimension file unique primary key IDNumber 
outcome_dimensions <- read_csv("outcome_dimensions.csv")
ungroup(outcomes) # Outcomes may be grouped from prior work. Remove as appropriate
# select 8 of the 30 columns and rename variables to remove need for backticks
# capitalization of IDNumber is driven by SIS variable
outcomes <- outcomes %>% select(IDNumber = `student sis id`,
                                type = `assessment type`,
                                cslo = `learning outcome name`,
                                score = `outcome score`,
                                course = `course name`,
                                term =`course sis id`,
                                section =`section name`,
                                possible = `learning outcome points possible`)
outcomes <- mutate(outcomes, pentascale = 5 * score / possible) # rescale on five point scale
# Group by assignment type and CSLO, find the means, display, on five point scale
outcomes %>% group_by(type, cslo) %>% summarize(avg_score = mean(pentascale, na.rm = TRUE)) %>% 
  ggplot() + 
  geom_bar(mapping = aes(x = cslo, y = avg_score, fill = type), stat = "identity", position=position_dodge()) +
  labs(title="Course learning outcome average performance",  x="Course student learning outcomes",  y = "Average") + 
  scale_fill_manual(values=c(hcl(220,100,50),hcl(220,100,70)))
ungroup(outcomes) # Return outcomes to ungrouped state
# Hello SQL: left join the dimension table the dump back into outcomes
outcomes <- outcomes %>% left_join(outcome_dimensions, by = "cslo") 
outcomes %>% group_by(pslos) %>% summarize(avg_score = mean(pentascale, na.rm = TRUE)) %>% 
  ggplot() + 
  geom_bar(mapping = aes(x = pslos, y = avg_score), stat = "identity", fill = hcl(220,100,50)) +
  labs(title="Program learning outcome average performance",  x="Program learning outcomes",  y = "Average")

The outcome results table after ETL work:

The dimension table structure:

The second chart displays general education program learning outcomes based on aggregated course learning outcomes against a five point scale.

Although I am using ggplot I like what I see over in ggvis and may also explore using that package as Shiny may be the eventual destination of the plots.