Cannot get 'fill' to do what I want with geom_col

[drchinn]

I am trying to have the top of each bar be the value "Sum" for each year and the proportion of those numbers which belong to "Nights" be the fill. Is this not working because I summarized the data and there isn't a separate column for Business/Nights?

library(tidyverse)
#> Warning: package 'tibble' was built under R version 4.0.3
#> Warning: package 'readr' was built under R version 4.0.3
library(ggplot2)

df<-
tribble(
   ~Year,  ~Business, ~Nights,   ~Sum,
   
 "2019",        58,    123,   181,
 "2019",     50,    105,   155,
 "2019",     70,    129,   199,
 "2019",     70,    143,   213,
 "2019",     63,    120,  183,
 "2019",     71,    124,   195,
 "2019",     83,    131,   214,
 "2019",     65,    121,   186,
 "2019",     58,    123,   181,
 "2019",     56,   144,   200,
 "2019",     58,    129,   187,
 "2019",     62,    122,   184,
 "2021",     67,    102,   169)

df %>%
  group_by(Year) %>%
  summarise(Business = round(mean(Business)),
            Nights = round(mean(Nights)),
            Total = round(mean(Sum))) %>%
  ggplot() + 
  geom_col(mapping = aes(x = Year, y = Total, fill = Nights))
#> `summarise()` ungrouping output (override with `.groups` argument)```

[drchinn]

Nm, fixed it, I had to use the gather function like I thought

library(tidyverse)
#> Warning: package 'tibble' was built under R version 4.0.3
#> Warning: package 'readr' was built under R version 4.0.3
library(ggplot2)

df<-
tribble(
   ~Year,  ~Business, ~Nights,   ~Sum,
   
 "2019",        58,    123,   181,
 "2019",     50,    105,   155,
 "2019",     70,    129,   199,
 "2019",     70,    143,   213,
 "2019",     63,    120,  183,
 "2019",     71,    124,   195,
 "2019",     83,    131,   214,
 "2019",     65,    121,   186,
 "2019",     58,    123,   181,
 "2019",     56,   144,   200,
 "2019",     58,    129,   187,
 "2019",     62,    122,   184,
 "2021",     67,    102,   169)

df %>%
  group_by(Year)  %>%
  gather(key = "Time", value = "n", 2, 3) %>%
  select(Year,Time,n) %>%
  group_by(Year,Time) %>%
  summarise(Total = mean(n)) %>%
  ggplot() + 
  geom_col(mapping = aes(x = Year, y = Total, fill = Time))
#> `summarise()` regrouping output by 'Year' (override with `.groups` argument)