Show columns as percentage in R ggplot

Question

I need help with a graph I am trying to built in R.

This is the data:

Location	Total Number of Employees	Local Number	Remote Number
L1	150	50	100
L2	355	148	207
L3	477	106	371
L4	234	82	152
L5	987	523	464
L6	4564	2504	2060
L7	2342	1425	917
L8	754	415	339

And this is what I am aiming for [1]: https://i.sstatic.net/eoVxL.jpg

So, basically I want to present the "Total Number of Employees" column in a 0-100% range and since L6 has the highest number of employees, 4564 should be 100%. The legend should show the local and remote number, where the "Local" column should be shown in the positive grid and the "Remote" column in the negative one. The locations should be ordered from min to max.

Answer 1

Something like this?

library(dplyr)
library(tidyr)
library(ggplot2)

df %>% 
  mutate(across(Local.Number:Remote.Number, ~ .x / max(Total.Number.of.Employees)),
         Remote.Number = -Remote.Number) %>% 
  pivot_longer(-c(Location, Total.Number.of.Employees)) %>% 
  ggplot() +
  aes(x = Location, y = value, fill = name) +
  geom_col() +
  scale_y_continuous(labels = scales::label_percent()) +
  theme_bw()