Packages

library(tidyverse)
library(ggpubr)
library(gridExtra)
library(DescTools)

Dataset

df <- readxl::read_xlsx("Week9MM.xlsx")
df
## # A tibble: 13 x 3
##    Grade         `Hours Needed` `Hours Averaged`
##    <chr>                  <dbl>            <dbl>
##  1 Kindergarten             9.5              8.5
##  2 First grade              9.1              8.4
##  3 Second grade             9.3              8.3
##  4 Third grade              8.6              8.1
##  5 Fourth grade             8.9              7.9
##  6 Fifth grade              8.9              7.8
##  7 Sixth grade              8.6              7.6
##  8 Seventh grade            8.5              7.3
##  9 Eighth grade             8.5              7.4
## 10 Ninth grade              8.3              7.1
## 11 10th grade               8.4              6.8
## 12 11th grade               8.4              6.9
## 13 12th grade               8                6.6

Creating new dataset Df2 with added column for difference

df2 <- df %>% 
  mutate(index = 1:n(),
         Diff = `Hours Averaged` - `Hours Needed`)

Assigning plots to shortcut names

dotplot <- df %>% 
  mutate(index = 1:n()) %>% 
  ggplot(.,) +
  geom_segment(aes(x=reorder(Grade,-index), xend=reorder(Grade,-index), y=`Hours Averaged`, yend=`Hours Needed`), color="darkgrey",size=2) +
  geom_point(aes(x=reorder(Grade,-index), y=`Hours Averaged`),fill="#c2a5cf", color="#7b3294", size=13,shape = 21) +
  geom_point(aes(x=reorder(Grade,-index), y=`Hours Needed`),fill="#c7e9c0", color="#00441b", size=13,shape = 21) +
  geom_text(aes(x=8,y=7.2,label="Averaged"),color="#7b3294",size=7) +
  geom_text(aes(x=8,y=9.3,label="Needed"),color="#00441b",size=7) +
  coord_flip() +
  theme_bw() + 
  xlab("") +
  ylab("") +
  theme(axis.text.y = element_blank()) +
  ggtitle("Averaged Hours and Needed Hours")

diffplot <- ggplot(df2,aes(x=reorder(Grade,-index),y=Diff))+ 
  geom_rect(aes(xmin = Inf, xmax = -Inf, ymin = -Inf, ymax = -1.48), fill = "#d7191c", alpha = 0.3) +
  geom_rect(aes(xmin = Inf, xmax = -Inf, ymin = -1.48, ymax = -1.10), fill = "#fdae61", alpha = 0.3) +
  geom_rect(aes(xmin = Inf, xmax = -Inf, ymin = -1.10, ymax = -0.76), fill = "#ffffbf", alpha = 0.3) +
  geom_rect(aes(xmin = Inf, xmax = -Inf, ymin = -0.76, ymax = 0), fill = "#abd9e9", alpha = 0.3) +
  geom_segment(aes(x=reorder(Grade,-index), xend=reorder(Grade,-index), y=Diff, yend=0), color="black",size=2) +
  geom_point(aes(x=reorder(Grade,-index), y=Diff),fill="lightgrey", color="black",size=13,shape = 21) +
  #geom_text(aes(x=reorder(Grade,-index), y=Diff,label=round(Diff,1)),size=4) +
  coord_flip() +
  theme_pubr()+
  xlab("") +
  ylab("") +
  ggtitle("Difference between Averaged Hours and Needed Hours")

Merging plots together

grid.arrange(diffplot,dotplot,nrow=1)