Make bar graph that counts levels of different columns

Hi I have a table with 1 category 'Pilar' with different topics eg Budget Schedule, scope, risk and ressources. each rows mentions green, yellow or red. I am completely stuck as I want a bar graph grouped per pillar so 1 seperate from 2 and 3 for each of the topics counting the times green occured, yellow and red. table

See worst drawn paint image, I hope this gives in idea of what i need. paint drawing of what I need

I tried working with aggerate but my brain couldn't figure it out. Next trying to a long format but as there are no numbers involved I think this cannot work either.

Thanks in advance as my brain is dead :D

This is what I tried:

projects_sum <-as.data.frame(projects_sum) projects_sum <- gather(projects_sum,measure,Budget:Ressources)

Error in ensym(): ! Can't convert to a symbol. Run rlang::last_trace() to see where the error occurred.

Solution

My made up data:

structure(list(pillar = c("1", "1", "1", "1", "1", "1", "1", 
"1", "1", "1", "2", "2", "2", "2", "2", "2", "2", "2", "2", "2", 
"3", "3", "3", "3", "3", "3", "3", "3", "3", "3"), budget = c("red", 
"red", "red", "red", "red", "red", "green", "green", "red", "green", 
"red", "yellow", "red", "red", "yellow", "yellow", "red", "red", 
"yellow", "red", "green", "red", "red", "red", "red", "yellow", 
"red", "red", "red", "red"), schedule = c("red", "green", "red", 
"yellow", "red", "yellow", "yellow", "red", "red", "yellow", 
"yellow", "green", "red", "green", "red", "red", "yellow", "yellow", 
"red", "yellow", "green", "yellow", "red", "green", "yellow", 
"yellow", "green", "green", "green", "red"), scope = c("green", 
"red", "green", "red", "yellow", "green", "green", "green", "red", 
"red", "red", "yellow", "green", "red", "red", "red", "yellow", 
"yellow", "red", "red", "green", "red", "red", "green", "red", 
"red", "red", "green", "green", "red"), risk = c("yellow", "red", 
"green", "yellow", "yellow", "yellow", "green", "green", "green", 
"green", "green", "green", "red", "yellow", "yellow", "yellow", 
"green", "green", "red", "green", "red", "yellow", "yellow", 
"green", "yellow", "yellow", "red", "yellow", "green", "yellow"
), resources = c("red", "green", "green", "green", "yellow", 
"green", "green", "green", "red", "green", "green", "green", 
"green", "yellow", "green", "green", "yellow", "green", "green", 
"green", "red", "green", "green", "red", "green", "green", "red", 
"green", "yellow", "green")), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -30L))

This tidyverse approach first pivots to a longer data frame structure, then counts the occurrences of each color by pillar and variable. The complete step fills in empty combinations, to allow for empty columns in the plot.

library(tidyverse)


df %>%
  pivot_longer(-pillar) %>%
  count(pillar, name, value) %>%
  complete(pillar, name, value, fill = list(n = 0)) %>%
  ggplot(aes(name, n, fill = factor(value, levels = c('green','yellow','red')))) +
  geom_col(position = position_dodge2(0.7, preserve = 'single'), width = 0.6, ) +
  facet_wrap(~ pillar,
             labeller = label_both) +
  scale_fill_manual(values = c('green','yellow','red')) +
  labs(x = '', y = 'Count') +
  theme(legend.position = 'none')

^{Created on 2023-04-21 with reprex v2.0.2}