Filling colors of a stacked bar chart according to count instead of category (R/Excel)

Question

I want to create a stacked bar chart but I want the colors to be decided based on count. For instance, if I have 5 bars and each bar has 3 categories, the default way to color the bar chart is by category. (for instance, category 1 is red, category 2 is blue and category 3 is green). However, what I want is: if for bar 1, category 1 has the highest count, that would be red. Similarly, if for bar 2, category 3 has the highest count, I would want category 3 of the second bar to be colored red. Is there any way for me to do this? I can use R/Excel/Numbers/any other software.

This is what I currently have, and I want the color to be dictated by frequency(almost like a heatmap)

Answer 1

This is straightforward in R.

If we have some data like this:

set.seed(69)
df <- data.frame(category = sample(LETTERS, 200, TRUE))

We can do:

library(ggplot2)

ggplot(df, aes(x = category)) + 
  geom_bar(aes(fill = ..count..), color = "black") +
  scale_fill_gradientn(colours = c("forestgreen", "gold", "red"))

---

EDIT

It seems from the OP's supplementary information that the data is something like this:

set.seed(69)
data <- data.frame(Question = rep(LETTERS[1:5], each = 5),
                   Response = rep(c("Strongly disagree", "Disagree", "Neutral",
                                  "Agree", "Strongly agree"), 5),
                   Frequency = as.vector(replicate(5, {
                                x <- sample(10:30, 5, TRUE); x / sum(x)})))

In which case, we could do something like this:

ggplot(data = data, aes(y = Question, x = Frequency)) + 
  geom_col(aes(fill = Frequency)) +
  geom_text(aes(label = stringr::str_wrap(Response, 10)), 
            position = position_stack(vjust = 0.5)) +
  scale_fill_gradientn(colours = c("forestgreen", "gold", "red"))

---

EDIT

A further request by the OP...

ggplot(data = data, aes(y = Question, x = Frequency)) + 
  geom_col(aes(fill = Question, alpha = Frequency)) +
  geom_text(aes(label = stringr::str_wrap(Response, 10)), 
            position = position_stack(vjust = 0.5)) +
  scale_fill_manual(values = c("deepskyblue4", "forestgreen", "gold", 
                                "orange", "red"),
                    name = "Question") +
  scale_alpha_continuous(guide = "none") +
  theme_minimal() +
  theme(panel.grid = element_blank())

^{Created on 2020-08-14 by the reprex package (v0.3.0)}