sorting stacked bars by highest value of certain rating

I'm trying to sort a stacked barchart object created by ggplot by having the item with the highest "Excellent" value first. My current code doesn't seem to sort it that way:

R_POPS_testdataset_forstackedbars <- read_csv("R-POPS testdataset forstackedbars.csv") # dataset for testing purposes

RPOPS_ggchart01 <- ggplot(R_POPS_testdataset_forstackedbars, aes(x = Variable_name, y = Rating_prop, fill = factor(Rating, levels=c("Poor","Fair","Good","Very Good","Excellent")))) + 
    geom_bar(stat="identity", position ="fill", width=0.8) + coord_flip() + scale_fill_manual("legend", values = c("Excellent" = "#275E6B", "Very Good" = "#4AAAC4", "Good" = "#8CD4E5", "Fair" = "#F7963D", "Poor" = "#BE2327")) # color scheme for stacked bars
RPOPS_ggchart01 <- RPOPS_ggchart01 + geom_text(aes(label=paste0(sprintf("%1.f", Rating_prop*100),"%")),
                                               position=position_fill(vjust=0.5), color="white", size=9)

The above code gets me this:

My data is structured like this:

structure(list(Rating = c("Excellent", "Fair", "Good", "Poor", 
"Very Good", "Excellent", "Fair", "Good", "Poor", "Very Good"
), Variable_name = c("Overall effectiveness of adminstrative support for the program", 
"Overall effectiveness of adminstrative support for the program", 
"Overall effectiveness of adminstrative support for the program", 
"Overall effectiveness of adminstrative support for the program", 
"Overall effectiveness of adminstrative support for the program", 
"Overall effectiveness of your Program Director", "Overall effectiveness of your Program Director", 
"Overall effectiveness of your Program Director", "Overall effectiveness of your Program Director", 
"Overall effectiveness of your Program Director"), Rating_prop = c(0.13, 
0.35, 0.17, 0.13, 0.22, 0.39, 0.09, 0.26, 0.09, 0.17)), row.names = c(NA, 
-10L), class = c("tbl_df", "tbl", "data.frame"))

Solution

To answer your question and check the answer I had to expand your initial dataset, but it should work with no problem with your original data.

I've used reorder + ave to reorder the bars. Basically, with ave you want to associate the value of Excellent to each value of Variable_name (even when Rating isn't Excellent). So that reorder knows that the same Variable_name value should be treated equally. replace is necessary in case you have no Excellent value: it just replaces the NA you would have with a zero. Note that I had to setNames, that's because I had to select the Rating_prop associated with the Rating Excellent.

# data
set.seed(1)
df <- data.frame(Variable_name = rep(LETTERS[1:5], each = 5),
                 Rating = c("Poor","Fair","Good","Very Good","Excellent"),
                 Rating_prop = runif(25))
df$Rating_prop <- ave(df$Rating_prop, df$Variable_name, FUN = function(x) x / sum(x))

# library
library(ggplot2)

# solution
ggplot(df, 
       aes(x = Rating_prop,
           y = reorder(Variable_name, ave(setNames(Rating_prop, Rating), Variable_name, FUN = function(x) replace(x["Excellent"], is.na(x["Excellent"]), 0))), 
           fill = factor(Rating, levels=c("Poor","Fair","Good","Very Good","Excellent")))) + 
  geom_col(position ="fill", width = 0.8) +
  scale_fill_manual("legend", values = c("Excellent" = "#275E6B", "Very Good" = "#4AAAC4", "Good" = "#8CD4E5", "Fair" = "#F7963D", "Poor" = "#BE2327")) +
  geom_text(aes(label=scales::percent(Rating_prop, accuracy = 1)),
            position=position_fill(vjust=0.5), color="white", size=9) +
  labs(y = "Variable_name") +
  scale_x_continuous(labels = scales::percent) +
  theme_classic()

^{Created on 2021-11-11 by the reprex package (v2.0.0)}

I tried to simplify a bit your code:

I used scales::percent instead of paste
I used geom_col instead of geom_bar, with allows me to remove the stat = "identity" argument
I removed coord_flip and I simply switched x and y

Also, as final touches:

I added scale_x_continuous so to have percentages on the x axis
I added a theme