R stacked bar plot with ordered columns and column elements

Question

I am trying to build a stacked bar plot, where bars "Company" are ordered by "Company_value", and elements of each bar "Investor" are ordered by "Investor_quota". Following is a simplified dataset.

structure(list(Company = structure(c(1L, 1L, 2L, 2L, 2L, 3L, 
3L, 3L), levels = c("Comp_A", "Comp_B", "Comp_C"), class = "factor"), 
    Company_value = c(100, 100, 200, 200, 200, 60, 60, 60), Investor = structure(c(1L, 
    2L, 1L, 3L, 4L, 2L, 3L, 5L), levels = c("Inv_1", "Inv_2", 
    "Inv_3", "Inv_4", "Inv_5"), class = "factor"), Investor_quota = c(10, 
    90, 100, 40, 60, 20, 30, 10)), row.names = c(NA, -8L), class = c("tbl_df", 
"tbl", "data.frame"))

Ordering columns, or "Company" by "Company_value" is pretty straightforward, as each factor has only one value:

df2 <- df1 %>% 
  mutate(
    Company = fct_reorder(Company, Company_value)
  ) 

However, the same approach does not work on ordering column elements, or "Investor" by "Investor_quota", as some investors participate to multiple "Company", with different "Investor_quota", thus creating more than one instance of "Investor" with different "Investor_quota" values. In other words, the levels of the factor should be different in each column.

I thus tried to use group_by() to apply independently fct_reorder to each "Company" element, but this does not work either

df3 <- df2 %>%
  group_by(Company) %>%
  mutate(
    Investor = fct_reorder(Investor, `Investor_quota`, .desc=TRUE)
  ) %>%
  ungroup()

I found some similar cases that were approached using an auxiliary variable that takes into consideration both the ranking on "Company" and the one on "Investor", but solutions are all pretty complex workarounds, and I have the feeling I am missing a very simple solution to this.

Many thanks for any help-Stefano

Answer 1

Not sure what you mean by pretty complex workarounds. The canonical approach to achieve your desired result is to create an auxiliary variable. To this end arrange your dataset in your desired order aka order by Company and Investor_quota. Then create the auxiliary interaction variable of Company and Investor for which you can set th order using e.g. forcats::fct_inorder. Finally you can create your stacked barchart using ggplot2 where you can map the auxiliary variable on the group aes:

library(ggplot2)
library(dplyr)
library(forcats)

df2 <- df1 |>
  arrange(Company, Investor_quota) |>
  mutate(
    order = paste(Company, Investor, sep = "."),
    order = forcats::fct_inorder(order)
  )

ggplot(
  df2,
  aes(Company, Company_value * Investor_quota / 100,
    fill = Investor,
    group = order
  )
) +
  geom_col()